none
Read Data form complex excel and insert into SQL database RRS feed

  • Question

  • Hi,

    As per client requirement, I have to create app/ web service in Visual studio which will read the data from excel and insert it into SQL DB tables. This excel file contains complex data, Refer below screenshot 


    Now as per requirement the text in Bold should be treated as columns and the next cell is the value for the columns so this values need to be inserted in 3 different sql tables like below.

    I can not Use OLEDB provider to read the data as this file is not in a tabular format. please suggest me how to do this using .NET




    Saturday, March 5, 2016 11:17 AM

Answers

  • >>>I can not Use OLEDB provider to read the data as this file is not in a tabular format. please suggest me how to do this using .NET

    According to your description, I suggest that you could use .NET Framework COM interop capability to read an Excel file or use Open XML SDK to retrieve the values of cells in a spreadsheet document.

    References->Add Reference, then choose Microsoft.Office.Interop.Excel

    Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
    
    ExcelApp.Visible = true;
    
    Microsoft.Office.Interop.Excel.Workbook wb = ExcelApp.Workbooks.Open("D:\\sample.xlsx");
    
    Microsoft.Office.Interop.Excel.Worksheet sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets["Sheet1"];
    
    Microsoft.Office.Interop.Excel.Range xlRng = sh.get_Range("A1");
    
    Console.WriteLine(xlRng.Value);
    

    For more information, click here to refer about How to: Retrieve the values of cells in a spreadsheet document (Open XML SDK)

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:02 PM
    Monday, March 7, 2016 6:45 AM