Dealing With Excel Sheets

This article explains how to read from an Excel sheet, create an Excel sheet and write into an Excel sheet (both .xls and .xlsx files) using C#.

.xls vs .xlsx

• The default format for a spreadsheet is .xls
• Microsoft Excel has .xls as the default format from the beginning. Microsoft 2007 changed the default fromat for Microsoft Excel to .xlsx.
• Xlsx is a XML based file format and the information is stored in XML format. Xls is based on the Binary interchange File Format (BIFF) and the information is stored in binary format.

There are some packages available, using which we can deal with Excel sheets. Some of them are:

• ExcelLibrary: Only .xls files can be read. ExcelLibrary 1.3.3 is commercial.
• ExcelPackage: Only .xlsx files can be read. It's performance is slow.
• Microsoft.Office.Interop.Excel: Microsoft office should be installed on the server.
• GemBox.Spreadsheet: It has a limitation on the number of sheets per workbook and rows per sheet. The maximum number of rows allowed per sheet is 150 and the number of sheets allowed per workbook is 5.
• Bytescout.Spreadsheet: Paid DLL, the free one is a trial version.

We will use the “ExcelDataReader” package for reading the Excel sheet and ExcelLibrary.dll for creating the Excel sheet. Both of these are free.

Install ExcelDataReader using the Package Manager Console.

Click on Tools then select Library Package Manager -> Package Manager Console (if you don't have Nuget Package Manager installed, download from https://visualstudiogallery.msdn.microsoft.com/27077b70-9dad-4c64-adcf-c7cf6bc9970c & install).

Run the following command in the Package Manager Console to install ExcelDataReader.

PM> Install-Package ExcelDataReader

Here our logic will be: Create a binary reader (for .xls) or XML reader (for .xlsx) based on the file format. For reading from a single sheet, read from the reader and to read from multiple sheets, convert this reader to a DataSet and read the data.

Let's see a demo app

Create an Empty web application. Add a web form with an ASP Upload control (to upload an Excel workbook), a textarea (for sheet names) and a button control.

<div>
    <asp:FileUpload runat="server" ID="FileUploader"/><br/>
        <textarea id="txtSheetNames" runat="server"><br>
        <asp:button runat="server" onclick="ProcessExcel" text="Continue">

</asp:button>

Now, in code behind:

// Save Uploaded file on to server
            const string FilePath = @"E:\DemoProjects\Excel\UploadedFiles\";
            var FileName = FileUploader.PostedFile.FileName + "_" +
                           DateTime.Now.ToString("yyyyMMddTHHmmssZ") + FileExtension;
            var FullPath = Path.Combine(FilePath, FileName);
            Request.Files[0].SaveAs(FullPath);

// Initialize an instance of FileStream
            var Stream = new FileStream(new FileInfo(FullPath).ToString(), FileMode.Open,
                        FileAccess.Read);

// Create an instance of type IExcelDataReader
var ExcelReader = string.Equals(FileExtension,".xlsx")
                //Reading from a OpenXml Excel file (2007 format; *.xlsx)
                ? ExcelReaderFactory.CreateOpenXmlReader(Stream)
                //Reading from a binary Excel file (97-2003 format; *.xls)
                : ExcelReaderFactory.CreateBinaryReader(Stream);

To read from single sheet,
//Traverse through the reader
while (ExcelReader.Read())
                {
                    //Operate on each row & create list for response excel sheet
                }


To read from multiple sheets by sheet name,
//Convert Reader to DataSet
var MembersDataSet = ExcelReader.AsDataSet();

var TempWorkSheet = MembersDataSet.Tables[SheetName];

                    if (TempWorkSheet != null &&
                        TempWorkSheet.Columns.Count >= DataColumnsCount)
                    {
                        var TempSheetRows = from DataRow TempRow in TempWorkSheet.Rows
                            select TempRow;
                // Operate on each row & create list for response from current sheet
            }

Now, let's create a spreadsheet.

If you are reading from a single sheet (by default the first one in the workbook), create a list with response data. Convert this list to a table (you can rename the table also). Add this table to a DataSet. Convert this DataSet to an Excel workbook.

If you are reading from multiple Excel sheets in a workbook and want to show a response in multiple sheets (a separete response sheet for each uploaded sheet), create a list for each sheet data and convert the list to a table. Add these tables to the DataSet and convert this DataSet to an Excel workbook.

For converting a list to a DataTable:

private static DataTable ToDataTable<T>(IEnumerable<T> LstItems)
        {
            var ObjDataTable = new DataTable(typeof(T).Name);

            //Get all the properties
            var PropertyInfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var Prop in PropertyInfos)
            {
                //Setting column names as Property names
                ObjDataTable.Columns.Add(Prop.Name);
            }
            foreach (T Item in LstItems)
            {
                var Values = new object[PropertyInfos.Length];
                for (var I = 0; I < PropertyInfos.Length; I++)
                {
                    //inserting property values to datatable rows
                    Values[I] = PropertyInfos[I].GetValue(Item, null);
                }
                ObjDataTable.Rows.Add(Values);
            }
            //put a breakpoint here and check datatable
            return ObjDataTable;
        }

For converting a DataSet to a workbook, use ExcelLibrary. You can download ExcelLibrary.dll from https://code.google.com/p/excellibrary/downloads/detail?name=ExcelLibrary.dll&can=2&q=

private static Workbook DataSetToExcel(DataSet ObjDataSet)
        {
            var ObjWorkBook = new Workbook();
            foreach (DataTable ObjDataTable in ObjDataSet.Tables)
            {
                var ResponseRowCount = 0;
                var ObjWorkSheet = new Worksheet(ObjDataTable.TableName);
                PopulateHeader(ObjWorkSheet);
                foreach (DataRow ObjDataRow in ObjDataTable.Rows)
                {
                    ResponseRowCount++;
                    ObjWorkSheet.Cells[ResponseRowCount, 0] =
                        new Cell(ObjDataRow["FirstName"].ToString());
                    ObjWorkSheet.Cells[ResponseRowCount, 1] =
                        new Cell(ObjDataRow["LastName"].ToString());
                    ObjWorkSheet.Cells[ResponseRowCount, 2] =
                        new Cell(ObjDataRow["Address"].ToString());
                    ObjWorkSheet.Cells[ResponseRowCount, 3] =
                        new Cell(ObjDataRow["Comment"].ToString());
                }
                ObjWorkBook.Worksheets.Add(ObjWorkSheet);
            }
            return ObjWorkBook;
        }

Check the uploaded code for a better understanding.