/// <summary>
/// Putting Data into Excel Cells
/// </summary>
/// <param name="oSheet">The ExcelWorksheet object</param>
/// <param name="rowIndex">The row number from where data will put</param>
/// <param name="dt">The DataTable object from where data will come</param>
private void CreateData(ExcelWorksheet oSheet, Lfx.Data.RowCollection dt, List <ExcelReg> campos)
{
int colIndex = 0;
foreach (Lfx.Data.Row dr in dt)
{
colIndex = 1;
rowIndex++;
foreach (ExcelReg dc in campos)
{
var cell = oSheet.Cells[rowIndex, colIndex];
// Setting value in the cell
cell.Value = dr[dc.name];
// Setting border of the cell
var border = cell.Style.Border;
border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
colIndex++;
}
}
}
public byte[] StartExcel(string Titulo, string titleSheet, string titleGrid, List <ExcelReg> campos, Lfx.Data.RowCollection rows, List <HeaderLeo> createHeader = null)
{
ExcelPackage excelPkg = new ExcelPackage();
excelPkg.Workbook.Properties.Author = "Excelencia Soluciones Informáticas S.R.L.";
excelPkg.Workbook.Properties.Title = Titulo;
ExcelWorksheet oSheet = CreateSheet(excelPkg, titleSheet);
if (createHeader != null)
{
foreach (HeaderLeo hl in createHeader)
{
CreateHeader(oSheet, campos.Count, hl);
}
}
// 3. Setting Excel Cell Backgournd Color during Header Creation
// 4. Setting Excel Cell Border during Header Creation
// Creating Header
CreateHeader(oSheet, campos);
// Putting Data into Cells
CreateData(oSheet, rows, campos);
// 5. Setting Excel Formula during Footer Creation
// Creating Footer
//CreateFooter(oSheet, ref rowIndex, dt);
// 6. Add Comments in Excel Cell
//AddComment(oSheet, 5, 5, "Sample Comment", "Debopam Pal");
// 7. Add Image in Excel Sheet
//string imagePath = Path.Combine(Path.GetDirectoryName(Path.GetDirectoryName(Application.StartupPath)), "debopam.jpg");
//AddImage(oSheet, 1, 10, imagePath);
// 8. Add Custom Objects in Excel Sheet
//AddCustomObject(oSheet, 7, 10, eShapeStyle.Ellipse, "Text inside Ellipse");
// Writting bytes by bytes in Excel File
//byte[] content = excelPkg.GetAsByteArray();
//string fileName = "Sample Excel using EPPlus.xlsx";
//string filePath = Path.Combine(Path.GetDirectoryName(Path.GetDirectoryName(Application.StartupPath)), fileName);
//File.WriteAllBytes(filePath, content);
// Openning the created excel file using MS Excel Application
//ProcessStartInfo pi = new ProcessStartInfo(filePath);
//Process.Start(pi);
return(excelPkg.GetAsByteArray());
}
