none
C# Programmatically excel file population taking long time for formulas RRS feed

  • Question

  • I am using c#, VS2013 and excel interop to generate a excel file dynamically where no of column will be 150 and rows will be 4000. my issue is it is taking long time because i am inserting many formula in excel cell.

    My request please see my code and give me best suggestion how to minimize excel file creation time with many formula like =SUM(A1:A2).

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Diagnostics;
    using Excel;
    
    
            private void button1_Click(object sender, EventArgs e)
            {
                string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet = null;
    
                object misValue = System.Reflection.Missing.Value;
    
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
    
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            xlWorkSheet.Cells[row+1, col+1] = new Random().Next(1, NumRows).ToString();
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                xlWorkSheet.Cells[row+1, col+1] = strSum;
                            }
                            else
                            {
                                xlWorkSheet.Cells[row + 1, col + 1] = new Random().Next(1, NumRows).ToString();
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                if (System.IO.File.Exists(@"d:\pop.xls"))
                {
                    System.IO.File.Delete(@"d:\pop.xls");
                }
    
                xlWorkBook.SaveAs(@"d:\pop.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue,
                    misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
    
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                xlApp = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
    
            }
    
            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch
                {
                    obj = null;
                    //MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
                }
            }
    
            private string GenerateColumnText(int num)
            {
                string str = "";
                char achar;
                int mod;
                while (true)
                {
                    mod = (num % 26) + 65;
                    num = (int)(num / 26);
                    achar = (char)mod;
                    str = achar + str;
                    if (num > 0) num--;
                    else if (num == 0) break;
                }
                return str;
            }

    Looking for best guide line. Thanks



    • Edited by Sudip_inn Wednesday, November 21, 2018 9:18 PM
    Wednesday, November 21, 2018 9:17 PM

Answers

  • Hello,

    With that much data you are much better off with

    Both options are very fast.

    Advantage to OpenXML and SpreadSheetLight (and similar libraries is Excel not not be installed).

    Disadvantage to OpenXML/Excel is it's not easy to learn.

    Formula methods and properties for SpreadSheetLight

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Sudip_inn Thursday, November 22, 2018 1:34 PM
    Thursday, November 22, 2018 12:00 AM
    Moderator
  • I don't have any formula code samples, no time to write up one as it's Thanksgiving and have a full day out of the house. There are some simple examples here. I've not done much with formula in SpreadSheetLight so I would need to dig in no different than you.

    In regards to binding to a grid, this library does not do this, the opposite can be done e.g. take a DataTable bound to a grid and push it to a Excel sheet with the option to append to current cell data as shown here.

    For setting values, here is a simple example

    using System;
    using SpreadsheetLight;
    using System.Data;
    
    namespace ForumQuestion
    {
        public class Operations
        {
            string fileName = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Karen.xlsx");
            public void AppendNames(string pFirstName, string pLastName)
            {
                //  open excel file to a specific worksheet
                using (SLDocument doc = new SLDocument(fileName,"Customers"))
                {
                    // get statistics, in this case we want the last used row
                    var stats = doc.GetWorksheetStatistics();
                    SLTextImportOptions tio = new SLTextImportOptions();
    
                    tio.DataFieldType = SLTextImportDataFieldTypeValues.Delimited;
    
                    // increment the last used row, first first and last name
                    doc.SetCellValue(SLConvert.ToCellReference(stats.EndRowIndex + 1, 1), pFirstName);
                    doc.SetCellValue(SLConvert.ToCellReference(stats.EndRowIndex +1, 2), pLastName);
                    // save Excel file
                    doc.Save();
                }
    
            }
        }
    }
    

    Simple example for reading and casting when there are mix data types in a column.

    private void ReadDemo(string pFileName, string pSheetName)
    {
        using (var doc = new SLDocument(pFileName, pSheetName))
        {
            var lastRow = doc.GetWorksheetStatistics().EndRowIndex;
            lastRow = lastRow == -1 ? 1 : lastRow + 1;
            for (int index = 0; index < lastRow; index++)
            {
                // read each row get column A
                doc.GetCellValueAsString(index, 0);
                // read each row get column B
                var val2 = doc.GetCellValueAsString(index, 1);
                if (int.TryParse(val2, out var intVal))
                {
                    // we have a int
                }
            }
        }
    }
    Bottom line you will need to experiment and work through these operations, the reward is faster time to complete then with Excel automation.

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Sudip_inn Friday, November 23, 2018 8:07 AM
    Thursday, November 22, 2018 2:44 PM
    Moderator

All replies

  • I think a better forum for this issue might be:

    Excel for Developers
    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=exceldev&filter=alllanguages

    - Wayne

    Wednesday, November 21, 2018 9:45 PM
  • Hello,

    With that much data you are much better off with

    Both options are very fast.

    Advantage to OpenXML and SpreadSheetLight (and similar libraries is Excel not not be installed).

    Disadvantage to OpenXML/Excel is it's not easy to learn.

    Formula methods and properties for SpreadSheetLight

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Sudip_inn Thursday, November 22, 2018 1:34 PM
    Thursday, November 22, 2018 12:00 AM
    Moderator
  • please guide me how to bind SpreadSheetLight with data from datatable ?

    in ReoGrid we can bind datatable.....so what function is available here to bind data table data with SpreadSheetLight control.

    please share some code.

    also tell me how could i populate cell in loop. suppose my datatable has 100 rows and few columns. then how can i iterate in datatable data and populate cell ?

    how to do SuspendFormula when populating cell because in my case there will be many formula which need to be inserted in cell.

    how to do SuspendFormula before populate cell and how to do ResumeFormula after data population in cell ?

    please guide me with code. thanks




    • Edited by Sudip_inn Thursday, November 22, 2018 1:54 PM
    Thursday, November 22, 2018 1:35 PM
  • I don't have any formula code samples, no time to write up one as it's Thanksgiving and have a full day out of the house. There are some simple examples here. I've not done much with formula in SpreadSheetLight so I would need to dig in no different than you.

    In regards to binding to a grid, this library does not do this, the opposite can be done e.g. take a DataTable bound to a grid and push it to a Excel sheet with the option to append to current cell data as shown here.

    For setting values, here is a simple example

    using System;
    using SpreadsheetLight;
    using System.Data;
    
    namespace ForumQuestion
    {
        public class Operations
        {
            string fileName = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Karen.xlsx");
            public void AppendNames(string pFirstName, string pLastName)
            {
                //  open excel file to a specific worksheet
                using (SLDocument doc = new SLDocument(fileName,"Customers"))
                {
                    // get statistics, in this case we want the last used row
                    var stats = doc.GetWorksheetStatistics();
                    SLTextImportOptions tio = new SLTextImportOptions();
    
                    tio.DataFieldType = SLTextImportDataFieldTypeValues.Delimited;
    
                    // increment the last used row, first first and last name
                    doc.SetCellValue(SLConvert.ToCellReference(stats.EndRowIndex + 1, 1), pFirstName);
                    doc.SetCellValue(SLConvert.ToCellReference(stats.EndRowIndex +1, 2), pLastName);
                    // save Excel file
                    doc.Save();
                }
    
            }
        }
    }
    

    Simple example for reading and casting when there are mix data types in a column.

    private void ReadDemo(string pFileName, string pSheetName)
    {
        using (var doc = new SLDocument(pFileName, pSheetName))
        {
            var lastRow = doc.GetWorksheetStatistics().EndRowIndex;
            lastRow = lastRow == -1 ? 1 : lastRow + 1;
            for (int index = 0; index < lastRow; index++)
            {
                // read each row get column A
                doc.GetCellValueAsString(index, 0);
                // read each row get column B
                var val2 = doc.GetCellValueAsString(index, 1);
                if (int.TryParse(val2, out var intVal))
                {
                    // we have a int
                }
            }
        }
    }
    Bottom line you will need to experiment and work through these operations, the reward is faster time to complete then with Excel automation.

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Sudip_inn Friday, November 23, 2018 8:07 AM
    Thursday, November 22, 2018 2:44 PM
    Moderator
  • HI Karen

    can you please have a look at this post where i asked few questions about Spreadsheetlight

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/41568340-fcc3-46a5-b05a-67452b439302/spreadsheetlight-how-to-set-foregroundbackground-color-amp-other-issues?forum=Offtopic

    My questions regarding Spreadsheetlight as follows

    1) i am using SpreadsheetLight library and i like to know how could i set row color red or yellow ?

    2) also tell me how could i set color range wise say Range["A1:Z1"] ?

    3) how to apply format cell range wise ?

    sheet.Range[DataRangeCoordinate].NumberFormat = "#,##0.000;[Red](-#,##0.000);#,##0.000";
    the above code is devexpress spreadsheet related. so how to do the same when working with SpreadsheetLight ?

    4) how to iterate in all cell value with in For loop ?

    when i am using dev express spreadsheet grid then i use below code to set back & fore color

    sheet.Range["A1:Z1"].Font.Color = Color.IndianRed;
    sheet.Range["A1:Z1"].Fill.BackgroundColor = Color.LightGray;
    sheet.Range["A1:Z1"].Style.Font.Bold = true;

    5) How to set column width for all column ?

    6) How to set autofit all columns ?

    7) i am getting error when i am trying to create CreateStyle my code as follows

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using SpreadsheetLight;
    
    SLStyle style1 = sl.CreateStyle();
    style.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.IndianRed, System.Drawing.Color.LightGray);
    sl.SetCellStyle(1, 0, style1);

    i have installed latest version of OpenXml from Nuget.

    please help me with code sample. thanks


    Friday, December 28, 2018 7:48 AM