locked
Importing excel data with formulas RRS feed

  • Question

  • I need to import data from an Excel sheet into a table. I am using the following code to do that. My problem is, when I import it, only the data comes in not the column formulas. Is there any way to get the formulas too? I will be saving them in a different column in my table.

                dim sConnExcel as String= "[Excel 8.0;DATABASE=" & mdlSystemSettings.TempFileLoc & "TempRep.xls;HDR=Yes;IMEX=1]"
                'Import Exported Crystal report from Excel
                sSql = "Select * into Detail  From " & sConnExcel & ".[" & sWorkSheetName & "$];"
                cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
                cmdAccess.ExecuteScalar()


    donsls

    Thursday, October 2, 2014 6:15 PM

Answers

  • Hello,

    What you are attempting can not be done with OleDb data provider, instead you need to use Excel Automation. Using automation traverse cells, check to see if a cell has a formula, assign the formula to a variable is a rough outline. Note Formula for a cell is of type Object so you need to cast it to a string. I didn't have simple sample to share but can direct you too this article with source. OpenWorkSheets.vb, search for formula and you will see how I am setting it, the important thing is how I create objects. Even though this is a write operation the same path is needed to read the formula. Hope this is of some assistance 


    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.

    • Marked as answer by Carl Cai Monday, October 20, 2014 2:04 AM
    Thursday, October 2, 2014 7:40 PM
  • Hello,

    Follow the instructions below. The project was created in VS2010 then moved to VS2012 so all code is compatible with VS2010.

    1. Download the source code
    2. Unzip
    3. Create a new project in VS2010
    4. You will be using Framework 4 Client profile
    5. Add a reference to Microsoft.Office.Interop.Excel via Reference tab (under project properties), Reference tab, Add button, select the assembly from Framework, not COM tab.
    6. Add in the files you unzipped into the new project.
    7. Set frmMainForm as the startup form.
    8. Optional remove form1 but there real is no reason for this.
    9. From the zipped files, copy from bin\Debug MyFile.xlsx and Setting.xml to the new project bin\debug folder.
    10. Compile, run.


    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.

    • Marked as answer by Carl Cai Monday, October 20, 2014 2:04 AM
    Friday, October 3, 2014 3:11 PM

All replies

  • Hello,

    What you are attempting can not be done with OleDb data provider, instead you need to use Excel Automation. Using automation traverse cells, check to see if a cell has a formula, assign the formula to a variable is a rough outline. Note Formula for a cell is of type Object so you need to cast it to a string. I didn't have simple sample to share but can direct you too this article with source. OpenWorkSheets.vb, search for formula and you will see how I am setting it, the important thing is how I create objects. Even though this is a write operation the same path is needed to read the formula. Hope this is of some assistance 


    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.

    • Marked as answer by Carl Cai Monday, October 20, 2014 2:04 AM
    Thursday, October 2, 2014 7:40 PM
  • Thanks for the reply. Much appreciated. I don't have VS2012. Need to find a way to copy and paste the code into VS2010/

    donsls

    Friday, October 3, 2014 2:59 PM
  • Hello,

    Follow the instructions below. The project was created in VS2010 then moved to VS2012 so all code is compatible with VS2010.

    1. Download the source code
    2. Unzip
    3. Create a new project in VS2010
    4. You will be using Framework 4 Client profile
    5. Add a reference to Microsoft.Office.Interop.Excel via Reference tab (under project properties), Reference tab, Add button, select the assembly from Framework, not COM tab.
    6. Add in the files you unzipped into the new project.
    7. Set frmMainForm as the startup form.
    8. Optional remove form1 but there real is no reason for this.
    9. From the zipped files, copy from bin\Debug MyFile.xlsx and Setting.xml to the new project bin\debug folder.
    10. Compile, run.


    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.

    • Marked as answer by Carl Cai Monday, October 20, 2014 2:04 AM
    Friday, October 3, 2014 3:11 PM