You must Sign In to post a response.
  • Category: Windows 8

    Transfering bulk of excel data to sqlserver table in windows application

    Hi

    I need to insert the more then one sheet of data of a workbook into sqlserver databse table using windows application in c#
  • #769339
    There are two ways:

    1.) If you have knowledge of SSIS package in sql server, then this is highly recommended approach.
    2.) You can read excel file in c# and place the data in dataset with multiple tables corresponding to multiple sheets and use sqlBulkInsert.

    Thanks!
    Anjali Bansal

    ~Give your best and lead the world

  • #769340
    You can use Spire.XLS (https://www.nuget.org/packages/Spire.XLS/) to easily export the data of each worksheet to a datatable, the code is straightforward as following:

    Workbook workbook = new Workbook();
    workbook.LoadFromFile(@"Input.xlsx");
    Worksheet sheet = workbook.Worksheets[0];
    //Export the data of the first worksheet to a datatable
    DataTable dt = sheet.ExportDataTable();

    After that you can add the datatables to a dataset and then use SqlBulkCopy to quickly insert dataset records to database.


  • Sign In to post your comments