C# ClosedXML WorkSheets Add's 1048576 empty rows

Question

I've been trying to find a solution for this but haven't been able to find any yet. So what is happening is that when I do:

IXLWorksheet sheetSave = wb.Worksheets.Add("SheetName");

I get an Excel sheet that as 1048576 rows and 20Columns. If I send a datatable same thing happens, it places the table but the sheet still as 1048576 rows, This makes the file have a size of 5MB with about 200x20 cells filled.

So does anyone know how to solve this? A way to force the range to a certain size, like the size o rows from the datatable.

Thank you, any extra information just ask.

Added by request in answer:

Here is the method used to export the datatable to excel

private void exportExcel(DataTable data)
    {
        XLWorkbook wb = new XLWorkbook();

        IXLWorksheet sheetSave = wb.Worksheets.Add(data, "list"); 

        DialogResult saveDialogResult = saveFileDialog1.ShowDialog();

        if ((saveDialogResult == DialogResult.OK) && (saveFileDialog1.FileName != null))
        {
            wb.SaveAs(saveFileDialog1.FileName);
        }
    }

Can you add the rest of the code around creating the worksheet. It looks like you are taking an existing worksheet (wb ?) and saving it to a new IXLWorksheet — MikeS159, Jan 18 '16 at 16:36
@Mike159 I edited the question with the method to export the datatable. — Morce, Jan 18 '16 at 17:04
Are you intending to use sheetSave? writing `wb.Worksheets.Add(data, "list");` should get the same result — MikeS159, Jan 18 '16 at 17:22
@Mike159 first of all thank you for replying. Yes I will use the sheetSave in other calls, and when you asked that I went searching for the error in other parts of the code and found the problem and then solution after some new search. I was setting the datatype, in another method, for the column and not for the cells used in that column. The solution is here [link](http://stackoverflow.com/questions/33775423/how-to-set-a-data-type-for-a-column-with-closedxml) How do I close this? And do a duplicate to the link I added? — Morce, Jan 18 '16 at 18:01
You can mark the question as a duplicate, or put the answer here yourself and accept it. — MikeS159, Jan 18 '16 at 18:26

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

After further searching\debugging I found I was setting the datatype in another method for the column and not for the cells used. So all the cells from row(1) to row(1058576) were being filled. Got the solution here. Thank you all.

edited May 23 '17 at 12:06

Community

1
1

answered Jan 19 '16 at 10:23

Morce

51
1
10

C# ClosedXML WorkSheets Add's 1048576 empty rows

1 Answers1