Reading an Excel file in C# using EPPlus

I had written a post about using EPPlus for writing Excel formatted reports in a previous blog article that you can find here: 

Creating an Excel report with C# using a MySql data source

What if we want to read an Excel file source? Can we still use EPPlus? Yes!

First, add the EPPlus nuget package to your Visual Studio project.

Next, create an ExcelPackage handle and open your source Excel file like this. You can access worksheets in your workbook like the below code.

                ExcelPackage package = new ExcelPackage(new FileInfo(sourceExcelFile));
                ExcelWorksheet workSheet = package.Workbook.Worksheets[1];

Note that most things in EPPlus are indexed as 1 to N rather than 0 to N-1. Accessing Worksheets[1] is the first worksheet. Accessing column[1] is the first column, and row[1] is the first row.

Now, we want to iterate and access our worksheet cell data. We can iterate and access our worksheet cell data like this:

for (int rowIndex = workSheet.Dimension.Start.Row; rowIndex <= workSheet.Dimension.End.Row; rowIndex++)
{
for (int colIndex = workSheet.Dimension.Start.Column; colIndex <= workSheet.Dimension.End.Column; colIndex++)
{
// You can access cells directly like this:
string myCellText = workSheet.Cells[rowIndex, colIndex].Text;
}
}

Warning: The workSheet.Dimension.End.Row value is sometimes not what you expect. Your source Excel file could have 100 rows, and workSheet.Dimension.End.Row may still be a value of 1 million. You will probably want to add some code to validate your cells and break the loop when your records are done being processed.

Another way to work with the cells is to get its value with a .Value.ToString() call like this:

		string myCellText = workSheet.Cells[rowIndex, colIndex].Text;
string myCellText = workSheet.Cells[rowIndex, colIndex].Value.ToString();

Most of the time the .Text and .Value.ToString() will be the same result, but not always. On case I encountered was with date columns. The .Text resulted in a nice “5/16/2017” output, and the .Value.ToString() was giving me an unexpected number like “43340”.

Creating an Excel report with C# using a MySql data source

This blog article will share how to create a multi-worksheet Excel workbook file using C# that fetches report data from a MySql data source.

First, create a Visual Studio C# project and install the EPPlus and MySql.Data Nuget packages.

Next, get yourself a MySqlConnection object connected to your MySql server. If you have a specific database, you’ll want to add that to your connection string. My case didn’t need to do that. Obviously, change your user, password, and server values to whatever makes sense for your environment. 

                string connstring = string.Format("Server={0}; UID={1}; password={2};default command timeout={3};", serverName, userName, Password, commandTimeout);
                MySqlConnection connection = new MySqlConnection(connstring);
                connection.Open();
Next, you set up an ExcelPackage object and create worksheets as the code below shows. Notice how the column headers are set and styled.
Then, I fetch my Sql query from a helper class and execute that using a MySqlCommand. The results of my query are fetched using the data reader. As it iterates through the data reader, the code sets the query result values in to specific Excel cell locations using the row and column index values.

                string eppOutput = @"C:\myEppOutput.xlsx";
                FileInfo fi = new FileInfo(eppOutput);
                using (ExcelPackage package = new ExcelPackage(fi))
                {
                    // I two queries that run. Results are put on separate worksheets.
                    for (int queryIdx = 1; queryIdx <= 2; queryIdx++)
                    {
                        string worksheetName = "Worksheet" + queryIdx;
                        ExcelWorksheet ws = package.Workbook.Worksheets.Add(worksheetName);
                        // Each page has this four column headers:
                        // Set worksheet's column headers
                        ws.Cells["A1"].Value = "ColumnA";
                        ws.Cells["B1"].Value = "ColumnB";
                        ws.Cells["C1"].Value = "ColumnC";
                        ws.Cells["D1"].Value = "ColumnD";
                        // Make column headers bold
                        ws.Cells["A1:D1"].Style.Font.Bold = true;
                        query = Utils.GetDatabaseQueryString(queryIdx);
                        cmd = new MySqlCommand(query, dbCon.Connection);
                        int row = 2;
                        int col = 1;
                        using (var dataReader = cmd.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                col = 1;
                                ws.SetValue(row, col++, dataReader.GetString(0));
// Column B and C are always integer values:
                                ws.SetValue(row, col++, int.Parse(dataReader.GetString(1)));
                                ws.SetValue(row, col++, int.Parse(dataReader.GetString(2)));
                                ws.SetValue(row, col++, dataReader.GetString(3));
                                row++;
                            }
                        }
                    }
                    package.Save();
                }

Note that the col index resets each pass through the dataReader.Read, and the row index resets each time I create a new worksheet.
Finally, we can call package.Save to write out my ExcelPackage object to a file specified in the argument string that we passed to its constructor.