Introduction
In this blog, I would like to share some of my interesting findings to generate the spreadsheet dynamically without using Microsoft interop excel dll. The main disadvantages of using Microsoft interop excel are:
- It is a COM library, so you need to be very careful when using in .NET, as it's hard to find memory leaks and if you don't dispose their objects properly, it leaves the Excel.exe process opened. You need to change COM permissions using run command DCOMCONFIG and usually clients will not allow touching these settings.
- As in many cases we have observed that the client machine needs to have Microsoft office installed
- Performance will be very slow
- Number of lines code will be more in case of updating document using the dynamic data.
EPPlus is a .NET library, which reads and writes Excel 2007/2010 or higher files, using Open Office XML format. It supports .XLSX, .XLSM Excel file format.
Now, we will see how to implement the OPENXML/ EPPlus. First download the OPENXML .dll from the below sites and include in the solution.
Now add the below references in project solution
using OfficeOpenXml;
using OfficeOpenXml.Style;
private void GenerateSpreadSheet()
{
ExcelPackage package = new ExcelPackage();
try
{
DataSet dsemp = new DataSet();
DataTable dtemp = new DataTable("MyTable");
dtemp.Columns.Add(new DataColumn("id", typeof(int)));
dtemp.Columns.Add(new DataColumn("name", typeof(string)));
dtemp.Columns.Add(new DataColumn("address", typeof(string)));
DataRow dr = dtemp.NewRow();
dr["id"] = 1;
dr["name"] = "John";
dr["address"] = "India";
dtemp.Rows.Add(dr);
dr = dtemp.NewRow();
dr["id"] = 2;
dr["name"] = "Jill";
dr["address"] = "United States of America";
dtemp.Rows.Add(dr);
dr = dtemp.NewRow();
dr["id"] = 3;
dr["name"] = "Jack";
dr["address"] = "UK";
dtemp.Rows.Add(dr);
dr = dtemp.NewRow();
dr["id"] = 4;
dr["name"] = "Jerry";
dr["address"] = "France";
dtemp.Rows.Add(dr);
dsemp.Tables.Add(dtemp);
ExcelWorksheet ExcelWorkSheet = package.Workbook.Worksheets.Add("sample.xlxs");
if (dsemp.Tables.Count > 0)
{
/* If you want the header with some styling the ass below */
using (ExcelRange HeaderCell = ExcelWorkSheet.Cells["A1:C1"])
{
HeaderCell.Value = "Sample Excel Sheet";
HeaderCell.Style.Font.Bold = true;
HeaderCell.Style.Font.Size = 14;
HeaderCell.Merge = true;
HeaderCell.Style.WrapText = true;
HeaderCell.Style.Fill.PatternType = ExcelFillStyle.None;
HeaderCell.Style.Font.Color.SetColor(Color.Blue);
}
//Writing Columns Name in Excel Sheet
int r = 3; // Initialize Excel Row Start Position = 4
int d = 0; // Initialize Data Start Position = 4
for (int col = 1; col <= dsemp.Tables[0].Columns.Count; col++)
{
ExcelWorkSheet.Cells[r, col].Value = dsemp.Tables[0].Columns[d].ColumnName;
d++;
}
r++;
for (int row = 0; row < dsemp.Tables[0].Rows.Count; row++)
{
d = 0; // Initialize Data Start Position = 4
// Excel row and column start positions for writing Row=1 and Col=1
for (int col = 1; col <= dsemp.Tables[0].Columns.Count; col++)
{
ExcelWorkSheet.Cells[r, col].Value = dsemp.Tables[0].Rows[row][d];
ExcelWorkSheet.Cells[r, col].AutoFitColumns();
d++;
}
r++;
}
/* If you want the header with some styling the ass below */
using (ExcelRange CellColor = ExcelWorkSheet.Cells["A3:C3"])
{
CellColor.Style.Fill.PatternType = ExcelFillStyle.Solid;
CellColor.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
CellColor.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
}
int rowcount = dsemp.Tables[0].Rows.Count + 5;
// Set the borders
string RightCell = ("A3") + ":" + ("C" + (rowcount));
ExcelWorkSheet.Cells[RightCell].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
// Freeze the Rows and Columns
ExcelWorkSheet.View.FreezePanes(4, 2);
string HeaderCell3 = "A" + (rowcount + 2);
ExcelWorkSheet.Cells[HeaderCell3].Value = "Report generated.";
ExcelWorkSheet.Cells[HeaderCell3].Style.Font.Size = 9;
string MergeCell3 = ("A" + (rowcount + 2)) + ":" + ("C" + (rowcount + 2));
ExcelWorkSheet.Cells[MergeCell3].Merge = true;
string HeaderCell4 = "A" + (rowcount + 3);
ExcelWorkSheet.Cells[HeaderCell4].Value = "Report design copyright © " + DateTime.Now.ToString("yyyy") + "All rights reserved.";
ExcelWorkSheet.Cells[HeaderCell4].Style.Font.Size = 9;
string MergeCell4 = ("A" + (rowcount + 3)) + ":" + ("C" + (rowcount + 3));
ExcelWorkSheet.Cells[MergeCell4].Merge = true;
// Set the Width if first cell
ExcelWorkSheet.Cells[1, 1, rowcount, 3].AutoFitColumns();
}
if (dsemp.Tables[0].Rows.Count > 0 || dsemp.Tables[1].Rows.Count > 0)
{
// Save the Excel File
string ExcelFileName = "D:\\" + "sample.xlsx";
FileInfo templateFile = new FileInfo(ExcelFileName);
package.Workbook.Properties.Title = "Sample";
package.SaveAs(templateFile);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
package.Dispose();
}
}
}
How to attach EPPlus Library
• Option 1- Download it from : http://epplus.codeplex.com/
• Option 2- To install EPPlus, run the command given below in Package Manager Console.
PM>: Install-Package EPPlus.
Please find various excelsheet properties that can be used while generating the spreadsheet,
If you want to format the cell then you need to select the range and assign the format string to the property as show below:
- ExcelWorkSheet.Cells[“A1”].Style.Numberformat.Format = "d-mmm-yy";
- ExcelWorkSheet.Cells[“B2”].Style.Numberformat.Format = "+#,##0;[Red]-#,##0";
- ExcelWorkSheet.Cells[“C3”].Style.Numberformat.Format = "#,##0.00";
using (ExcelRange CellColor = ExcelWorkSheet.Cells["A1:C1"])
{
CellColor.Style.Fill.PatternType = ExcelFillStyle.Solid;
CellColor.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
CellColor.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
}
If you want to set the border for the column for excel sheet then use below code,
ExcelWorkSheet.Cells[“A1:C1”].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
If you want to align the Header column for data, numbers to right for excel sheet then use below code,
ExcelWorkSheet.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ExcelWorkSheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
If you want to set font style for Header column in excel sheet then use below code,
using (ExcelRange HeaderCell1 = ExcelWorkSheet.Cells["A1:C1"])
{
// The below code to set the font size
HeaderCell1.Style.Font.Size = 14;
HeaderCell1.Style.Font.Bold = true;
// The below code to set the font color
HeaderCell1.Style.Font.Color.SetColor(Color.Red);
// The below code is to merge the cells
HeaderCell1.Merge = true;
// The below code is to wrap the cell text
HeaderCell1.Style.WrapText = true;
}
If you want to set Formula in excel sheet then use below code,
ExcelWorkSheet.Cells["B15"].Formula = "SUM(B2:B7)";
If you want to assign Auto fit column width property in excel sheet then use below code,
ExcelWorkSheet.Cells[1, 1, 15, 15].AutoFitColumns();
If you want to Freeze column in excel sheet then use below code,
ExcelWorkSheet.View.FreezePanes(4, 2); (assign the row and column number)
Please refer the below Output on an Excel sheet generated from the above code,
So friends, I hope this information will help you to implement it in very easier way. There are many more, however, I really feel that these are the most critical parts that we need to know in order to implement the dynamic spreadsheet.
