Hello Elin,
Thanks for your inquiry!
I guess the issue you met may be caused by the data of your data table is too much. When you insert it to the worksheet in one time, the progress will take lots of memory of your computer, and this will lead the code just stops there.
Here are two methods to avoid this. First, you can
change the platform of your project to X64, this can make the project use more memory when you run it. Or you can
split the data table to many little tables, and then insert them in a loop, like the following code shows.
- Code: Select all
DataTable dataTable = new DataTable("table");
object[] objects = new object[91];
for (int i =0; i < 91; i++)
{
dataTable.Columns.Add(i + "column", Type.GetType("System.String"));
objects[i] = i + "data";
}
for(int i = 0; i < 944000; i++)
{
dataTable.Rows.Add(objects);
}
DataSet set = SplitDataTable(dataTable, 1000);
Workbook workbook = new Workbook();
workbook.Version = ExcelVersion.Version2016;
Worksheet worksheet = workbook.Worksheets[0];
int rowindexi = 1;
foreach(DataTable dt in set.Tables)
{
if (rowindexi == 1)
{
worksheet.InsertDataTable(dt, true, rowindexi, 1);
rowindexi += dt.Rows.Count + 1;
}
else
{
worksheet.InsertDataTable(dt, false, rowindexi, 1);
rowindexi += dt.Rows.Count;
}
dt.Dispose();
}
workbook.SaveToFile("many.xlsx", ExcelVersion.Version2016);
public static DataSet SplitDataTable(DataTable originalTab, int rowsNum)
{
int tableNum = originalTab.Rows.Count / rowsNum;
int remainder = originalTab.Rows.Count % rowsNum;
DataSet ds = new DataSet();
if (tableNum == 0)
{
ds.Tables.Add(originalTab);
}
else
{
DataTable[] tableSlice = new DataTable[tableNum];
//Save orginal columns into new table.
for (int c = 0; c < tableNum; c++)
{
tableSlice[c] = new DataTable();
foreach (DataColumn dc in originalTab.Columns)
{
tableSlice[c].Columns.Add(dc.ColumnName, dc.DataType);
}
}
//Import Rows
for (int i = 0; i < tableNum; i++)
{
// if the current table is not the last one
if (i != tableNum - 1)
{
for (int j = i * rowsNum; j < ((i + 1) * rowsNum); j++)
{
tableSlice[i].ImportRow(originalTab.Rows[j]);
}
}
else
{
for (int k = i * rowsNum; k < ((i + 1) * rowsNum + remainder); k++)
{
tableSlice[i].ImportRow(originalTab.Rows[k]);
}
}
}
//add all tables into a dataset
foreach (DataTable dt in tableSlice)
{
ds.Tables.Add(dt);
}
}
return ds;
}
Sincerely,
Marcia
E-iceblue support team