Click here to Skip to main content
15,920,030 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to create a function that will read the data from specific excel sheets that will then be uploaded into their respective tables in a database. I'm using EPPlus in C# for this.

The current working code is something like this:
public static DataTable GetDataTableFromExcel(string path,bool hasHeader = true)
        {
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(path))
                {
                    pck.Load(stream);
                }
                var ws = pck.Workbook.Worksheets.First();
                DataTable tbl = new DataTable();
                foreach (var firstRowCell in ws.Cells[1, 1, 1, 
                ws.Dimension.End.Column])
                {
                 tbl.Columns.Add(hasHeader ? firstRowCell.Text : 
                 string.Format("Column {0}", firstRowCell.Start.Column));
                }
                var startRow = hasHeader ? 2 : 1;
                for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    DataRow row = tbl.Rows.Add();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                }
                return tbl;
            }


I tried passing off the filename as a string into the function but I got an error saying it expected an identifier. Can anyone give me ideas on how to fix this?

What I have tried:

public static DataTable GetDataTableFromExcel(string path, string fileN, bool hasHeader = true)
        {
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(path))
                {
                    pck.Load(stream);
                }
                //var ws = pck.Workbook.Worksheets.First();

                var ws = pck.Workbook.Worksheets.(fileN);
                DataTable tbl = new DataTable();
Posted
Updated 25-Jun-18 18:58pm

You can try something like

C#
pck.Workbook.Worksheets["Sheet1"];
 
Share this answer
 
Comments
Maciej Los 26-Jun-18 2:07am    
5ed!
n-hanz 27-Jun-18 3:30am    
Thanks for the help :)
I would rather retrieve the worksheet using index,
string FilePath = @"D:\...\path\sample.xlsx";
FileInfo existingFile = new FileInfo(FilePath);
List<Heading> Headings = new List<Heading>();

using (ExcelPackage package = new ExcelPackage(existingFile))
{
   //Get number of sheets, 
    int iSheetsCount = package.Workbook.Worksheets.Count;
    if (iSheetsCount > 0)
    {
           // Get the sheet by index
           ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
           Console.WriteLine("Worksheet name : " + worksheet.Name);
    }
}
 
Share this answer
 
Comments
n-hanz 27-Jun-18 3:30am    
Thanks for the info. But unfortunately, I need to be using the sheet name as the index of the sheet I need may keep on changing. The only constant would be the name.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900