Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Nov 24, 2021 9:09 am

Hi, we are using Spire.Office (Spire.xls) to save a datatable as an excel file.
We have tried wit excelversion 2020xlsb and version2016

The problem is that the datatable consists of 944.000 rows and 91 columns but when we call the line
spireSheet.InsertDataTable(dt, True, 1, 1) the code just stops there. No error and no crash that we can detect.
It works when we have < 500k rows so what we need to know is if there is a limitation to spire worksheet different from that of .xlsx (1 million rows)
And if so if you have a workaround or suggestion as to what we can do to get it to work.



Dim wb = New Workbook()
Dim spireSheet = wb.Worksheets(0)
spireSheet.InsertDataTable(dt, True, 1, 1)
wb.SaveToFile(savePath & filename)
dt.Reset()
wb.Dispose()
spireSheet.Clear()
spireSheet.Dispose()


Best,

Elin

elinarctic
 
Posts: 27
Joined: Thu Oct 21, 2021 11:39 am

Wed Nov 24, 2021 10:52 am

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
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Fri Nov 26, 2021 9:14 am

Thank you so much for your reply and the example code that you provided for us. I think we managed to get it to work now :)

Best,

Elin

elinarctic
 
Posts: 27
Joined: Thu Oct 21, 2021 11:39 am

Fri Nov 26, 2021 9:56 am

Hello Elin,

You are welcome!

Sorry that I ignored you were using VB last time, here I also convert the code to VB for your reference easier. Just feel free to contact us if you have any other question.

Code: Select all
    Sub Main()

        Dim dataTable As DataTable = New DataTable("table")
        Dim objects As Object() = New Object(90) {}

        For i As Integer = 0 To 91 - 1
            dataTable.Columns.Add(i & "column", Type.[GetType]("System.String"))
            objects(i) = i & "data"
        Next

        For j As Integer = 0 To 944000 - 1
            objects(0) = (j + 1).ToString + "row"
            dataTable.Rows.Add(objects)
        Next

        Dim [set] As DataSet = SplitDataTable(dataTable, 1000)
        Dim workbook As Workbook = New Workbook()
        workbook.Version = ExcelVersion.Version2016
        Dim worksheet As Worksheet = workbook.Worksheets(0)
        Dim rowindexi As Integer = 1

        For Each dt As DataTable In [set].Tables

            If rowindexi = 1 Then
                worksheet.InsertDataTable(dt, True, rowindexi, 1)
                rowindexi += dt.Rows.Count + 1
            Else
                worksheet.InsertDataTable(dt, False, rowindexi, 1)
                rowindexi += dt.Rows.Count
            End If

            dt.Dispose()
        Next

        workbook.SaveToFile("many.xlsx", ExcelVersion.Version2016)

    End Sub

    Public Function SplitDataTable(ByVal originalTab As DataTable, ByVal rowsNum As Integer) As DataSet
        Dim tableNum As Integer = originalTab.Rows.Count / rowsNum
        Dim remainder As Integer = originalTab.Rows.Count Mod rowsNum
        Dim ds As DataSet = New DataSet()

        If tableNum = 0 Then
            ds.Tables.Add(originalTab)
        Else
            Dim tableSlice As DataTable() = New DataTable(tableNum - 1) {}

            For c As Integer = 0 To tableNum - 1
                tableSlice(c) = New DataTable()

                For Each dc As DataColumn In originalTab.Columns
                    tableSlice(c).Columns.Add(dc.ColumnName, dc.DataType)
                Next
            Next

            For i As Integer = 0 To tableNum - 1

                If i <> tableNum - 1 Then

                    For j As Integer = i * rowsNum To ((i + 1) * rowsNum) - 1
                        tableSlice(i).ImportRow(originalTab.Rows(j))
                    Next
                Else

                    For k As Integer = i * rowsNum To ((i + 1) * rowsNum + remainder) - 1
                        tableSlice(i).ImportRow(originalTab.Rows(k))
                    Next
                End If
            Next

            For Each dt As DataTable In tableSlice
                ds.Tables.Add(dt)
            Next
        End If

        Return ds
    End Function


Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Return to Spire.XLS