/// <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 sheet
ws.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);
}
}
