How to add a new worksheet for every 10000 records in datatable while export to excel using closedxml

Question

my datatable has 100,000 records, i would like to create a new worksheet for every 10,000 records in datatable. how to iterate through datatable to do this.

int sheetcount = 1;
 using (XLWorkbook wb = new XLWorkbook())
{
 var ws = wb.Worksheets.Add(comboBox1.Text.ToString() + sheetcount.ToString());
 //ws.Cells().Style.Border.DiagonalBorderColor = XLColor.Black;
                        ws.Row(1).Height=50;
                        //ws.FirstRow().Merge();

                        ws.Range(1, 2, 1,18).Merge();
                        //ws.Row(1).Merge();
                        //ws.Row(1).Value = comboBox1.Text.ToString();
                        //ws.Row(1).Cell(1).im

                        ws.Row(1).Cell(2).Value = comboBox1.Text.ToString().ToUpper();
                        ws.Row(1).Cell(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        ws.Row(1).Cell(2).Style.Alignment.Vertical=XLAlignmentVerticalValues.Center;
                        ws.Row(1).Cell(2).Style.Fill.BackgroundColor = XLColor.Red;
                        ws.Row(1).Cell(2).Style.Font.FontColor = XLColor.White;
                        ws.Row(1).Cell(2).Style.Font.FontSize = 21;
                        ws.Row(1).Cell(2).Style.Font.Bold = true;

                        ws.Column(1).Merge();
                        ws.Column(1).Style.Fill.BackgroundColor = XLColor.Red;

                        ws.Cell(2, 2).InsertTable(dt);
                        ws.Row(2).Style.Fill.BackgroundColor = XLColor.Orange;
                        ws.Tables.FirstOrDefault().ShowAutoFilter = false;

                        ws.Columns().AdjustToContents();


                        wb.SaveAs(fi.ToString());
}

Answer 1

The easiest way to do this using a foreach loop and read and load 10,000 rows in a new DataTable with the method ImportRow. When the new DataTable contains 10,000 rows I call a helper method to actually create the worksheet and Insert the table in it.

Your main method will look like this:

using (XLWorkbook wb = new XLWorkbook())
{
     //  same structure of datatable
    var copyTable = dt.Clone();
    foreach(DataRow row in dt.Rows)
    {
        // copy over row
        copyTable.ImportRow(row);
        // check if we reached 10000
        if (copyTable.Rows.Count == 10000)
        {
            // store the datatable
            CreateSheetForTable(wb, copyTable);
            // reset datatabble to initial state ....
            copyTable = dt.Clone();
            // ... so the next 10000 rows can be loaded
        }
    }
    // don't forget to copy the last bit
    if (copyTable.Rows.Count>0)
    {
        CreateSheetForTable(copyTable);
    }

    wb.SaveAs(fi.ToString());
}

And the CreateSheetForTable method, our helper will create the workbook with all your fancy setting and insert the datatable:

private void CreateSheetForTable(XLWorkBook wb, DataTable table)
{
    var ws = wb.Worksheets.Add(String.Format("{0}{1}",comboBox1.Text,wb.Worksheets.Count+1));
    // all other fluff removed
    ws.Cell(2, 2).InsertTable(table);
}