CODEWITHSUNDEEP
c#performanceexceloptimization
nickfinity
nickfinity

Reputation: 1119

Excel - Getting cell formatting is slow

I'm using C# to pull data from an Excel file. I need to get the text and some minor formatting data from a sheet. My test sheet has 115 rows and 10 columns. The performance seems sluggish. If I only pull out the text using the code below it takes about 2 seconds to run. If I check the font (in the if(c.Font.Bold==null..... line) it goes up to 8 seconds. If I get the borders info then it goes up to 17 seconds.

The problem is that I'll have many, many sheets I need to pull data from and speed will become an issue. Any suggestions on what I can do to speed this up? I really appreciate any help.

foreach (Range c in oSheet.UsedRange.Cells)
{
    var txt = c.Text;
    if (c.Font.Bold == null || c.Font.Italic == null || Convert.ToInt32(c.Font.Underline) > 0 || Convert.ToBoolean(c.Font.Bold) || Convert.ToBoolean(c.Font.Italic))
        txt = "";

    var borderBottom = c.Borders.Item[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle;
    var borderTop = c.Borders.Item[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle;
    var borderLeft = c.Borders.Item[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle;
    var borderRight = c.Borders.Item[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle;
}

Upvotes: 0

Views: 2803

Answers (3)

Sarath Subramanian
Sarath Subramanian

Reputation: 21401

You could use the below steps. This is very fast and one line code ( no need of loops and all). I am taking a simple excel to explain here :

Before

enter image description here

I managed to store the range as A1:C4 in a variable dynamically in exRange and used the below code to give border

((Range)excelSheet.get_Range(exRange)).Cells.Borders.LineStyle = XlLineStyle.xlContinuous;


After

enter image description here

Upvotes: 1

Moha Dehghan
Moha Dehghan

Reputation: 18472

If your Excel file is a Excel 2007/2010 file (.xlsx), you can use ExcelPackage or EPPlus components to read the file. They are mush faster that office interop.

I used EPPlus and it iterated over 2000 cell almost instantly!

ExcelPackage ep = new ExcelPackage(new FileStream(path, FileMode.Open, FileAccess.Read));
var sheet = ep.Workbook.Worksheets[1];
foreach (var cell in sheet.Cells[sheet.Dimension.Address])
{
    var txt = cell.Text;
    var font = cell.Style.Font;
    if (!font.Bold || font.Italic || font.UnderLine)
        txt = "";
    var borderBottom = cell.Style.Border.Bottom.Style;
    var borderTop = cell.Style.Border.Top.Style;
    var borderLeft = cell.Style.Border.Left.Style;
    var borderRight = cell.Style.Border.Right.Style;
    // ...
}

Upvotes: 3

Peter L.
Peter L.

Reputation: 7304

I'm not at all familiar with C#, but in VBA I use Application.ScreenUpdating property set to false on the start and set back to true when finished. In general case this dramatically increases speed, especially if macro performs any visible sheets updates.

I'm pretty sure such property should be available in C# as well. Hope that was helpful)

Upvotes: 1

Related Questions