none
Create excel from C# datatable morethan 70,000 records without looping. RRS feed

  • Question

  • I am trying to create excel file from datatable without looping and it has morethan 70,000 records and 150 columns.

    How can I create excel file without looping datatable.

    I have tried the following methods.

    ===============================

    dt.WriteXml(@"E:\test_output_T00044.xlsx"); -- Which gives error message while opening the file (not able to open the file).

    dt.WriteXml(@"E:\test_output_T00044.xls"); -- loss of data / taking long time to open.

    xlWorkBook.Worksheets.Add(ds); - Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

    xlWorkBook.Worksheets.Add(dt); - Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))


    Saravanan.M

    Friday, October 20, 2017 4:27 AM

Answers


  • Hi Saravanan Marappan M P,

    Because you want to create an excel file without looping datatable. But the ClosedXML can not meet your needs. So, You may can try finding some Third-party extension library to exporting data.

    Or you use the traditional way to loop table record, and then inserted into the excel object.


    Best Regards,

    Yohann Lu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 31, 2017 2:55 AM
    Moderator

All replies

  • Your first two examples would only produce XML files. That's why the methods are called WriteXML.

    Merely naming the files as '.xls' will not magically turn them into Excel files! (Actually Excel files are XML, but not all XML is an Excel file, if you see what I mean). Excel might be able to open them, but it may not be in the format you want.

    Your last two examples will not work because the Worksheets object simply does not have an Add method that directly accepts a datatable.

    I strongly suspect you copied your last example from a source that also specified the requirement for the ClosedXml library (which you don't have), which adds in this functionality

    So my advice would be to investigate ClosedXML, which should enable you to use the "xlWorkBook.Worksheets.Add(dt)" command. (I should point out that I am not affiliated with ClosedXML and have never tried it myself).



    • Edited by RJP1973 Friday, October 20, 2017 8:46 AM
    Friday, October 20, 2017 8:36 AM
  • By default, Excel associates with .xml files, with that (and this may be a longshot) try the following.

    dt.WriteXml(@"E:\test_output_T00044.xml");
    Also, if by chance the DataTable originally came say from SQL-Server, consider exporting from SQL-Server. I have an example on MSDN Export Excel from SQL-Server.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, October 20, 2017 8:39 AM
    Moderator
  • Unfortunately there are no direct methods to copy from a DataTable to an Excel Worksheet in .NET. The fastest method is to use an ADO (or DAO) Recordset and Excel automation with the CopyFromRecordset method. You may want to consider using the Recordset instead of the DataTable is you are exporting that much data and don't want to loop through all of the rows.

    How to transfer data to an Excel workbook by using Visual C# .NET

    Also, depending upon the source of the data it may also be possible to export directly to an Excel Workbook using SQL and ADO.NET.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, October 20, 2017 2:04 PM

  • Hi Saravanan,

    As far as I know,  there are no direct methods to copy from a DataTable to an Excel Worksheet in .NET.  You may can try some Third-party extension library to exporting data and don't want to loop through all of the rows.

    ClosedXML: ClosedXML makes it easier for developers to create Excel 2007+ (.xlsx, .xlsm, etc) files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and VisualBasic.NET.

    I try the following code.

    To install ClosedXML, run the following command in the Package Manager Console:

    PM> Install-Package ClosedXML
                //using ClosedXML.Excel;
                XLWorkbook wb = new XLWorkbook();
                DataTable dt = GetTable();
                wb.Worksheets.Add(dt, "WorksheetName");
                wb.SaveAs(@"D:\HelloWorld.xlsx");


    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Yohann Lu




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, October 23, 2017 2:28 AM
    Moderator
  • If you use Spire.XLS, you can import data from datatable to excel like this

            private void button1_Click(object sender, EventArgs e)
            {
                //connect database
                OleDbConnection connection = new OleDbConnection();
                connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
                OleDbCommand command = new OleDbCommand();
                command.CommandText = "select * from parts";
                DataSet dataSet = new System.Data.DataSet();
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
                dataAdapter.Fill(dataSet);
                DataTable t = dataSet.Tables[0];
                //export datatable to excel
                Workbook book = new Workbook();
                Worksheet sheet = book.Worksheets[0];
                sheet.InsertDataTable(t, true, 1, 1);
                book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003);
                System.Diagnostics.Process.Start("insertTableToExcel.xls");
            }

    Monday, October 30, 2017 6:05 AM

  • Thanks for your reply,

    Actually this code works fine for 22000 records. But in the case for more than 70000 records it gives an error message as 'Out of memory exception'

    XLWorkbook wb = new XLWorkbook();
    wb.Worksheets.Add(dt1); <---Error

    Exception of type 'System.OutOfMemoryException' was thrown.


    Saravanan.M


    Monday, October 30, 2017 7:40 AM

  • Hi Saravanan Marappan M P,

    Because you want to create an excel file without looping datatable. But the ClosedXML can not meet your needs. So, You may can try finding some Third-party extension library to exporting data.

    Or you use the traditional way to loop table record, and then inserted into the excel object.


    Best Regards,

    Yohann Lu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 31, 2017 2:55 AM
    Moderator
  • You can create a datagridview on the form. Populate it with the DataTable.  Then, copy the DataGridView to the clipboard and paste into Excel.

    dg.DataSource = dt;
    dg.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
    dg.SelectAll();
    string str = Convert.ToString(dgResults.GetClipboardContent().GetData(DataFormats.UnicodeText));
    Clipboard.SetText(str, TextDataFormat.UnicodeText);
    
    xlWorkSheet.Paste();
    

    Thursday, February 22, 2018 10:07 PM
  • Why use .Paste() when you can just pass array of array via .SetValue() ?

    The size limit is imposed to Array of formulas only.

    Friday, February 23, 2018 3:31 AM
    Answerer