Cannot Read Worksheet Data

maxvt0 commented 7 years ago

Hi, I was trying to Use this library for Reading data from xlsx files and storing them into database. I faced issue that any kind of Excell file that i tried to read returned empty Count of Worksheets (Worksheet position out of range).

My code is simple Console application:

using System;
 using System.IO;
 using System.Text;
 using OfficeOpenXml;

 namespace Mb.XlsxParseConsole
 {
    class Program
    {
        static void Main(string[] args)
        {
            string sFilename = "../../Data/_test_data.xls";
            FileInfo file = new FileInfo(sFilename);

            try {
                using (ExcelPackage package = new ExcelPackage(file)) 
                {
                    StringBuilder sb = new StringBuilder();
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                    int rowCount = worksheet.Dimension.Rows;
                    int colCount = worksheet.Dimension.Columns;
                    bool bHeaderRow = true;

                    for (int row = 1; row <= rowCount; row++) {
                        for (int col = 1; col <= colCount; col++) {
                            if (bHeaderRow) {
                                sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                            } else {
                                sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                            }
                        }

                        sb.Append(Environment.NewLine);
                    }

                    System.Console.Write(sb.ToString());
                }
            } catch (Exception ex) {
                System.Console.WriteLine(ex.Message);
            }

        }
    }
 }

I tried with multiple Excell files but i was unable to read any of them. Any suggestions ?

TGills commented 7 years ago

Out of curiosity, try:

sb.Append((worksheet.Cells[row, col].Value).ToString() + "\t");

Just added brackets around where you are getting the value. Other than that, that's how I've been reading data from cells.

Also, I have my worksheet set to 1 instead of 0:

// get the first worksheet in the workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];

Here's one line from my project where I look at column 5, which holds the item type, and then assign the value in the cell to a variable: itemType = (worksheet.Cells[i, 5].Value).ToString(); //Column 5 = itemType

VahidN commented 7 years ago

As @TGills mentioned, This is how you should access worksheets in EPPlus and Excel

var firstWorksheet = package.Workbook.Worksheets[1]; // This should be 1 not 0
// OR
var firstWorksheet = package.Workbook.Worksheets.First();
// OR
var someWorksheet = package.Workbook.Worksheets["Sheet1"];

maxvt0 commented 7 years ago

@VahidN @TGills

Thanks for response. But my issue is that package.Workbook.Worksheets is empty on each file i loaded. I can see it through debugger. And I tried with .xls / .xlsx files created with Open office as well as one created with microsoft Excell

example Here. _test_data.xlsx

I am using netcoreapp1.1

VahidN commented 7 years ago

Your file cannot be opened with MS Excel 2016.

VahidN commented 7 years ago

This is the structure of your file (open it with 7-zip) xl02 And this is the structure of the new .xlsx format xl01