/// <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()); }