none
Dataset to Excel bulk upload using Microsoft.Office.Interop.Excel

    Question

  • Hello,

        I am generating a dataset which is having more than 10,000 record rows and each row is having 25 columns.

        I am using Microsoft.Office.Interop.Excel API (12.0.0.0), and the Excel file is generating from a template (.xltx).

        So my requirement is to do a Bulk upload from the dataset to the Excel sheet instead of cell by cell data filling. Its taking long time to fill these data.

        Please help me on this.

    • Moved by CoolDadTxMVP Monday, September 23, 2013 3:06 PM Office related
    Monday, September 23, 2013 5:22 AM

Answers

  • Thanks for your reply.

    I found another way to implement this by copying the datatable values to an object array and inserting that array to the WorkSheets specified range.

    Object array (Two dimensional)  = DataTable Values

    Worksheet.get_Range(ExcelStartRange, ExcelEndRange).Value2 = Object array

    Save Worksheet

    This works fine for me.

    Monday, September 23, 2013 12:18 PM

All replies

  • There some alternatives that you may consider:

    - One alternative is to use the OleDb driver for Excel. From the point of view of C# you would be treating the spreadsheet as a database, and inserting rows into the table. This is much faster than using COM/Interop to write cell by cell.

    - Another alternative is to write a temporary CSV file into disk from C#. Then use COM/Interop to tell Excel to read the CSV file. This is very fast, and also simple to program. But it will not respect whatever template you were using for the sheet. You may need to read the file into a separate sheet and then run an Excel macro to transport the data from that sheet into the template. Sorry, I'm not experienced enough in Excel to be able to tell you how fast the latter may be able to run.

    - If you are writing an .xslx (versus .xsl), then you can use the Microsoft OpenXML SDK to directly produce the file without using the Excel application. The SDK provides a DLL that you Reference from your project. It can open an existing .xlsx (which can serve as a template) and then you can call the methods in the DLL to insert values for the cells. This runs fast, but is complex to program because first you need to learn how to use the OpenXML library, which has a steep learning curve.

    Monday, September 23, 2013 6:47 AM
  • Thanks for your reply.

    I found another way to implement this by copying the datatable values to an object array and inserting that array to the WorkSheets specified range.

    Object array (Two dimensional)  = DataTable Values

    Worksheet.get_Range(ExcelStartRange, ExcelEndRange).Value2 = Object array

    Save Worksheet

    This works fine for me.

    Monday, September 23, 2013 12:18 PM