0

I am working on a web based app the needs to pull data from an excel 97-2003(.xls) spreadsheet that has several small tables on a single worksheet.

I am currently experimenting with the following code to open and read the worksheet:

            string properties = String.Format(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Users\Macy\Desktop\lbr_ems.xls; Extended Properties = 'Excel 8.0;'");

        using (OleDbConnection conn = new OleDbConnection(properties))
        {
            string worksheet = "LbrRpt";
            conn.Open();
            DataSet ds = new DataSet();
            //string columns = String.Join(",", columnNames.ToArray());
            using (OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + worksheet + "$]", properties))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                ds.Tables.Add(dt);

                grdComponent.DataSource = dt;
                grdComponent.DataBind();
            }

The issue is that it pulls the ENTIRE worksheet and all the numerical values are blank. I'm thinking that this is not the code that i need for the application i am attempting to build.

I have been looking at this code:

    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
var cell = (Excel.Range)excelWorksheet.Cells[10, 2];

as per How to read single Excel cell value, however i am lead to believe that it may not be for a web based use due to no mention of .net, or I am missing a using clause somewhere.

most other solutions i have seen generally indicate using third party items.

am i missing something stupid-easy or just not looking in the right spots?

Ingram Yates
  • 89
  • 1
  • 10
  • If you decide to go the route of your second example you have to add a reference to `Microsoft.Office.Interop.Excel` and also a using statement to it! – Suppe Aug 01 '18 at 17:13
  • Possibly related issue [here](https://stackoverflow.com/a/51639002/111794) -- if the fields are actually text and Excel is expecting a number. – Zev Spitz Aug 01 '18 at 17:27
  • You can modify your SQL statement to return a specific column: `"SELECT * FROM [" + worksheet + "$A]"` or a specific range: `"SELECT * FROM [" + worksheet + "$A1:C172]"`. – Zev Spitz Aug 01 '18 at 17:29
  • @Suppe, I tried adding that, but it threw an error at me. Office does not exist in Microsoft. I guess something is not installed in he project to allow that to work. Since it's an existing project and i'm the Junior developer, I'll have to dig into that with the Lead. – Ingram Yates Aug 01 '18 at 18:30
  • @ZevSpitz That worked Perfectly! your second comment fixed both problems! Please Post that as an answer so i can mark it as such and give you credit! – Ingram Yates Aug 01 '18 at 18:33
  • @Ingram Yates, Did you add the reference to the project? If not then Right click on references -> add reference -> find microsoft.office.interop.excel -> check the box and add it to the project. Just adding the using statement will not be enough! – Suppe Aug 01 '18 at 18:36
  • Out of curiosity, why are you using the Microsoft Jet provider, instead of the Microsoft ACE provider? Both providers support older Excel files (`.xls`) but the ACE provider supports the 2007 formats (`xlsx`, `xlsm`, `xlsb`) as well. – Zev Spitz Aug 01 '18 at 19:29
  • primarily because i don't know what i'm doing and this an example of what my boss gave me to work with and what my googling came up with. My Associates degree is in Network administration, not programming, so my course load never got past Visual Basic and Basic Logic. Everything i know about programming has been learnt on the job. Personally, i'm happy with how much I've learned, but i know i have barely scratched the surface. – Ingram Yates Aug 02 '18 at 21:43

1 Answers1

1

When reading data from Excel using SQL, you aren't strictly limited to using the worksheets as subjects of the FROM clause:

SELECT *
FROM [Sheet1$]

you can use just about any addressing scheme supported by Excel. For example, you can refer to a range of cells:

SELECT *
FROM [Sheet1$A1:C172]

and you can set the start and end of the range to the same cell, to get back a single cell:

SELECT *
FROM [Sheet1$A1:A1]

You can also refer to a range of columns:

SELECT *
FROM [Sheet1$A:B]

or a range of rows:

SELECT *
FROM [Sheet1$1:5]

or to a named range:

SELECT *
FROM NamedRange

(Note that if HDR=Yes in the connection string, then the first row will be treated as the column headers.)

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
  • For sake of clarity and completeness, in that last example, is "NamedRange" intended to be a string that could include any of the previous examples within the brackets? – Ingram Yates Aug 02 '18 at 21:46
  • @IngramYates Excel allows defining something called a "named range" -- a range of cells with a name -- and you can treat that specific range by referring to the name in SQL. I've just tested, and you can't further limit a named range by columns / rows / cell addresses. WRT the brackets -- brackets are used in SQL to refer to table names or field names with special characters, such as `$` or `:`. If the named range would have a special character or an SQL keyword, it would also need brackets. – Zev Spitz Aug 02 '18 at 21:51
  • so for example, if i'm setting up a list of cities in Excel to be used in a DropDownList on one of the sheets, I can give it a name, and then just call it in my SQL by that name instead of having to use the specific cell range? and that would carry over if i had to add a row or column causing the named range to move? – Ingram Yates Aug 02 '18 at 22:16
  • @IngramYates Yes. I've just tested, and the named range shifts / expands when a new row is inserted. – Zev Spitz Aug 02 '18 at 22:36