Read multiple Excel worksheets inside a single xlsx using c#

Question

I'm trying to develop a piece of code that will grab data from 3 worksheets within a single xlsx and upload it to 3 different tables on SQL Server. Each worksheets is supposed to upload its content to a single table (worksheet 1 upload to table Customers, worksheet 2 upload to table Sales,...)

I managed to get it to upload all data from worksheet 1 to a table... Here is the code for it:

public partial class LoggedHome : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    if (IsPostBack && Upload.HasFile)
    {
        if (Path.GetExtension(Upload.FileName).Equals(".xlsx")) 
        {
            var excel = new ExcelPackage(Upload.FileContent); 
            var dt = excel.ToDataTable(); 
            var table = "MenuPrice";
            using (var conn = new SqlConnection("Removed for security purpouses"))
            {
                var bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.DestinationTableName = table;
                conn.Open();
                var schema = conn.GetSchema("Columns", new[] { null, null, table, null });
                foreach (DataColumn sourceColumn in dt.Columns)
                {
                    foreach (DataRow row in schema.Rows)
                    {
                        if (string.Equals(sourceColumn.ColumnName, (string)row["COLUMN_NAME"], StringComparison.OrdinalIgnoreCase))
                        {
                            bulkCopy.ColumnMappings.Add(sourceColumn.ColumnName, (string)row["COLUMN_NAME"]);
                            break;
                        }
                    }
                }
                bulkCopy.WriteToServer(dt);
            }
        }
        else
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('This is not an Excel file. Please upload a proper file.')", true);
        }
    }
}
}

public static class ExcelPackageExtensions
{
public static DataTable ToDataTable(this ExcelPackage package)
{
    ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
    DataTable table = new DataTable();
    foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
    {
        table.Columns.Add(firstRowCell.Text);
    }
    for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
    {
        var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
        var newRow = table.NewRow();
        foreach (var cell in row)
        {
            newRow[cell.Start.Column - 1] = cell.Text;
        }
        table.Rows.Add(newRow);
    }
    return table;
}
}

Any help is much appreciated! Thank you

Answer 1

This line is only retrieving the first worksheet

ExcelWorksheet workSheet = package.Workbook.Worksheets.First();

Change it to be a for loop, as you have done for cells and rows

DataTable table = new DataTable();
foreach (var workSheet in package.Workbook.Worksheets)
{
    // Existing code
}
return table;