Recently I was looking for an Advance tool through which I can generate complex Excel Reports. And after going through many tools I found EP Plus. For further details see this link. Through this tool we can easily create reports with charts, graphs and other drawing objects. I have planned to shared few samples with the community, so if any one is interested in using this library he will get a good kick start.
In this sample application I have tried to explain that how you can generate a report using DataTable. I also demonstrated how to use different formatting options and formulas.
So, here is the code:-
Here is the snapshot of Excel File which is created through above code:-
Hopefully this will be useful for you, keep posting your comments.For further details and download see EP Plus home page.
In this sample application I have tried to explain that how you can generate a report using DataTable. I also demonstrated how to use different formatting options and formulas.
So, here is the code:-
/// <summary>
/// Creates the data table.
/// </summary>
/// <returns>DataTable</returns>
private static DataTable CreateDataTable()
{DataTable dt = new DataTable();
for (int i = 0; i < 10; i++)
{dt.Columns.Add(i.ToString());
}
for (int i = 0; i < 10; i++)
{ DataRow dr = dt.NewRow();foreach (DataColumn dc in dt.Columns)
{dr[dc.ToString()] = i;
}
dt.Rows.Add(dr);
}
return dt;}
private void button1_Click(object sender, EventArgs e)
{using (ExcelPackage p = new ExcelPackage())
{ //Here setting some document properties p.Workbook.Properties.Author = "Zeeshan Umar"; p.Workbook.Properties.Title = "Office Open XML Sample"; //Create a sheet p.Workbook.Worksheets.Add("Sample WorkSheet"); ExcelWorksheet ws = p.Workbook.Worksheets[1];ws.Name = "Sample Worksheet"; //Setting Sheet's name
ws.Cells.Style.Font.Size= 11; //Default font size for whole sheetws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
DataTable dt = CreateDataTable(); //My Function which generates DataTable
//Merging cells and create a center heading for out table ws.Cells[1, 1].Value = "Sample DataTable Export"; ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true; ws.Cells[1, 1, 1, dt.Columns.Count].Style.Font.Bold = true; ws.Cells[1, 1, 1, dt.Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; int colIndex = 1; int rowIndex = 2;foreach (DataColumn dc in dt.Columns) //Creating Headings
{ var cell = ws.Cells[rowIndex, colIndex]; //Setting the background color of header cells to Gray var fill = cell.Style.Fill; fill.PatternType = ExcelFillStyle.Solid; fill.BackgroundColor.SetColor(Color.Gray); //Setting Top/left,right/bottom borders. var border = cell.Style.Border;border.Bottom.Style =
border.Top.Style =
border.Left.Style =
border.Right.Style = ExcelBorderStyle.Thin; //Setting Value in cell cell.Value = "Heading " + dc.ColumnName;colIndex++;
}
foreach (DataRow dr in dt.Rows) // Adding Data into rows
{colIndex = 1;
rowIndex++;
foreach (DataColumn dc in dt.Columns)
{ var cell = ws.Cells[rowIndex, colIndex]; //Setting Value in cell cell.Value = Convert.ToInt32(dr[dc.ColumnName]); //Setting borders of cell var border = cell.Style.Border;border.Left.Style =
border.Right.Style = ExcelBorderStyle.Thin;colIndex++;
}
}
colIndex = 0;
foreach (DataColumn dc in dt.Columns) //Creating Headings
{colIndex++;
var cell = ws.Cells[rowIndex, colIndex]; //Setting Sum Formula cell.Formula = "Sum("+ ws.Cells[3, colIndex].Address+
":"+ws.Cells[rowIndex-1, colIndex].Address+
")"; //Setting Background fill color to Gray cell.Style.Fill.PatternType = ExcelFillStyle.Solid; cell.Style.Fill.BackgroundColor.SetColor(Color.Gray);}
//Generate A File with Random name Byte[] bin = p.GetAsByteArray();string file = "d:\\" + Guid.NewGuid().ToString() + ".xlsx";
File.WriteAllBytes(file, bin);}
}
Here is the snapshot of Excel File which is created through above code:-
Hopefully this will be useful for you, keep posting your comments.For further details and download see EP Plus home page.
