This project has moved. For the latest updates, please go here.

Worksheet.Style.Border.OutsideBorder = XLBorderStyleValues.Thin too slow

Oct 17, 2012 at 6:18 PM

I have a huge worksheet, of around 7000 rows by 200 cells. It generates quickly, but when I go and format the borders it takes too much time.

Is there any other way to do this? I've been trying to set it as a style to the workbook before and after writing the cells, but it does not seem to work, cells end up without borders.

Coordinator
Oct 17, 2012 at 7:38 PM

You're setting the outside border to the entire worksheet. Set it to the range:

worksheet.RangeUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
Oct 17, 2012 at 8:16 PM

This not what I want to do at all.


What I want to do is put borders to each cell in the excel file, however I also get a huge performance dip if I add the borders when I am adding the cells to the sheets. Is there a way I can set a default style for cells or quickly add the borders to each cell? I have not been able to find a way to do this quickly.

Oct 17, 2012 at 8:29 PM
Edited Oct 17, 2012 at 9:07 PM

Just as an update, I just tried

Worksheet.CellsUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

It is still painfully slow.

Edit:

      Worksheet.RangeUsed().Style.Border.InsideBorder = XLBorderStyleValues.Thin;
      Worksheet.RangeUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

Does what I want but it is very slow still.

Coordinator
Oct 17, 2012 at 9:15 PM

Set the borders individually. Later on I'll see what I can do about the outside border method when it's a worksheet.

            worksheet.Style
                .Border.SetBottomBorder(XLBorderStyleValues.Thin)
                .Border.SetTopBorder(XLBorderStyleValues.Thin)
                .Border.SetLeftBorder(XLBorderStyleValues.Thin)
                .Border.SetRightBorder(XLBorderStyleValues.Thin);
Oct 17, 2012 at 9:58 PM

That did the trick. The only issue I am seeing right now is that cells below the table have the borders. I'll see if I can get them removed.

Thanks for all the help!

Coordinator
Oct 17, 2012 at 10:02 PM

Pick up the latest source code and set the outside border before filling the worksheet.

worksheet.Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
Oct 17, 2012 at 10:04 PM

Is there any way to set the cell default style, or do a style for a cell and just assign it, similar to OpenXML?

Oct 17, 2012 at 10:30 PM
Edited Oct 17, 2012 at 10:32 PM
MDeLeon wrote:

Pick up the latest source code and set the outside border before filling the worksheet.

worksheet.Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin);

Thanks for the update! However, when I set it this way, my cells come out without borders, and all the cells below those have the borders set (all the empty ones below the data).

And going back, sticking with the code you provided before is not working either.

    worksheet.Style
                .Border.SetBottomBorder(XLBorderStyleValues.Thin)
                .Border.SetTopBorder(XLBorderStyleValues.Thin)
                .Border.SetLeftBorder(XLBorderStyleValues.Thin)
                .Border.SetRightBorder(XLBorderStyleValues.Thin);

Coordinator
Oct 17, 2012 at 10:33 PM
Edited Oct 17, 2012 at 10:35 PM

"Is there any way to set the cell default style, or do a style for a cell and just assign it, similar to OpenXML?"

That's what you're doing when you set the style of the worksheet. That's why cells bellow and right of your range have the formatting you're setting.

If you don't want all the cells in the worksheet to have the borders you can cut the time in half by setting up just the left and bottom borders and then setting up the top and right borders of the range. For example:

            var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("Sheet1");
 
            var start = DateTime.Now;

            for (int ro = 1; ro <= 7000; ro++)             {                 for (int co = 1; co <= 200; co++)                 {                     ws.Cell(ro, co)                         .SetValue(String.Format("({0},{1})", ro, co))                         .Style.Border.SetLeftBorder(XLBorderStyleValues.Thin)                               .Border.SetBottomBorder(XLBorderStyleValues.Thin);                 }             }
            ws.RangeUsed().Style                 .Border.SetTopBorder(XLBorderStyleValues.Thin)                 .Border.SetRightBorder(XLBorderStyleValues.Thin);
            var end = DateTime.Now;             Console.WriteLine("Time to create: {0}s", (end - start).TotalSeconds);

Time to create: 19.3344564s

Not exactly lightning fast but it's the only workaround I think of if you only want the cells in the range to have borders.

Coordinator
Oct 17, 2012 at 10:38 PM

As for the code not working, the following works fine:

            var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("Sheet1");
 
            var start = DateTime.Now;
            ws.Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
            for (int ro = 1; ro <= 7000; ro++)
            {
                for (int co = 1; co <= 200; co++)
                {
                    ws.Cell(ro, co)
                        .SetValue(String.Format("({0},{1})", ro, co));
                }
            }
            var end = DateTime.Now;
            Console.WriteLine("Time to create: {0}s", (end - start).TotalSeconds);

It creates the worksheet in 5 secs.