none
dumping data into excel RRS feed

  • Question

  • Hi there,

    I have used interop Excel functionality to transfer from datatable to excel sheet. In my code I use foreach loop to copy from datatable 'cell' to excel cell. It takes long time especially with larger data sets. 

    Is there a faster way of transfering data into excel template? 

    Thanks!

    Jonas

    Friday, November 15, 2019 9:15 PM

Answers

  • As you have already learned, Excel automation with large amounts of data is time consuming. How to get around this, don't us Excel automation, instead look at a solution which uses OpenXml.

    https://nugetmusthaves.com/Tag/OpenXml

    From the link above, if willing to truly get fast write operations without spending $$$'s look at SpreadSheetLight while willing to pay look at GemBox.SpreadSheet. Some of the others may be worth looking at but I've not used them.

    Lastly, if this was not a template then SpreadSheetLight totally free has a method to export to a sheet with options to append to existing data and SpreadSheetLight is based on OpenXml (as is GemBox).


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by JonasLub Monday, November 18, 2019 9:11 AM
    Friday, November 15, 2019 9:49 PM
    Moderator

All replies

  • As you have already learned, Excel automation with large amounts of data is time consuming. How to get around this, don't us Excel automation, instead look at a solution which uses OpenXml.

    https://nugetmusthaves.com/Tag/OpenXml

    From the link above, if willing to truly get fast write operations without spending $$$'s look at SpreadSheetLight while willing to pay look at GemBox.SpreadSheet. Some of the others may be worth looking at but I've not used them.

    Lastly, if this was not a template then SpreadSheetLight totally free has a method to export to a sheet with options to append to existing data and SpreadSheetLight is based on OpenXml (as is GemBox).


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by JonasLub Monday, November 18, 2019 9:11 AM
    Friday, November 15, 2019 9:49 PM
    Moderator
  • you are amazing, thanks!
    Monday, November 18, 2019 9:11 AM
  • Hi, here is an another alternative solution by using a free Excel library.

    //connect database
    OleDbConnection connection = new OleDbConnection();
    connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb";
    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("ExportToExcel.xlsx",ExcelVersion.Version2013);

    Thursday, November 28, 2019 1:47 AM