public void WriteRequestData(ExcelWriter writer, int startRow, int hoursStartColumn, List <int> data) { var lastColumn = hoursStartColumn + NumberOfDays + 1; writer.FreezePanes(1, lastColumn); foreach (var d in data) { // empty merged cells writer.WriteMergedCell( new CellRange(startRow, 1, hoursStartColumn - 1), new Cell() { BackgroundColor = Color.LightGray, AllBorders = true } ); // days of month for (int i = 0; i <= NumberOfDays; ++i) { writer.WriteCell(startRow, hoursStartColumn + i, Days[i]); } // empty summary column stores excel SUM formulas in later rows writer.WriteCell( startRow, lastColumn, new Cell() { AllBorders = true, BackgroundColor = Color.LightGray } ); ++startRow; // merged data cells for (int i = 1; i < ColumnShiftName; ++i) { var cell = i != ColumnShiftName - 1 ? new Cell() { AllBorders = true, Value = "test" } : new Cell() { AllBorders = true, Value = _hours.Dequeue(), NumberFormat = Cell.FORMAT_TWO_DECIMAL }; writer.WriteMergedCell( new CellRange(startRow, i, startRow + 2, i), cell ); } for (int i = 0; i < ShiftNames.Length; ++i) { var currentRow = startRow + i; writer.WriteCell( currentRow, ColumnShiftName, new Cell() { AllBorders = true, Value = ShiftNames[i] } ); var days = new int[NumberOfDays]; var hoursCell = new Cell() { AllBorders = true, NumberFormat = Cell.FORMAT_WHOLE_NUMBER }; for (int j = 0; j <= NumberOfDays; ++j) { var value = _people.Dequeue(); hoursCell.Value = value > 0 ? value : null; writer.WriteCell(currentRow, ColumnShiftName + 1 + j, hoursCell); } // hours subtotal var totalCell = new Cell() { AllBorders = true, Formula = string.Format( "{0}*{1}", writer.GetRowSum(hoursStartColumn, lastColumn - 1, currentRow), writer.GetAddress(currentRow, hoursStartColumn - 2) ), NumberFormat = Cell.FORMAT_TWO_DECIMAL }; writer.WriteCell(currentRow, lastColumn, totalCell); } startRow += 3; } /* --------------------------------------------------------------- * worksheet subtotals * -------------------------------------------------------------- */ // people subtotals writer.WriteMergedCell( new CellRange(startRow, 1, ColumnShiftName), new Cell() { AllBorders = true, BackgroundColor = Color.LightGray, Bold = true, HorizontalAlignment = CellAlignment.HorizontalRight, Value = "Total People" } ); var hourSumCell = new Cell() { AllBorders = true, BackgroundColor = Color.LightGray, Bold = true, NumberFormat = Cell.FORMAT_WHOLE_NUMBER }; for (int i = 0; i <= NumberOfDays; ++i) { var index = ColumnShiftName + 1 + i; hourSumCell.Formula = writer.GetColumnSum(1, startRow - 1, index); writer.WriteCell(startRow, index, hourSumCell); } writer.WriteCell( startRow, lastColumn, new Cell() { BackgroundColor = Color.LightGray, AllBorders = true } ); ++startRow; // work hour sum writer.WriteMergedCell( new CellRange(startRow, 1, lastColumn - 1), new Cell() { AllBorders = true, BackgroundColor = Color.LightGray, Bold = true, HorizontalAlignment = CellAlignment.HorizontalRight, Value = "Total Work Hours" } ); writer.WriteCell( startRow, lastColumn, new Cell() { AllBorders = true, BackgroundColor = Color.LightGray, Bold = true, Formula = writer.GetColumnSum(1, startRow - 1, lastColumn), NumberFormat = Cell.FORMAT_TWO_DECIMAL } ); }
static void CreateSimpleReport() { // [1] create writer using (var writer = new ExcelWriter()) { var sheetCount = 0; var cellValue = 0; writer.FormatAsTable = true; do { // [2] add worksheet to workbook w/optional parameters. put code from // here to step [5], **BEFORE** the writer.GetAllBytes() call in a // repeating block to write more than one sheet writer.AddSheet( string.Format("Sheet-{0}", sheetCount), 8D ); // [3] setup worksheet (ALL CALLS OPTIONAL, AND IN ANY ORDER) // set default font size writer.SetWorkSheetStyles(9); writer.SetHeaderText( writer.GetHeaderFooterText(10, "Left"), writer.GetHeaderFooterText(20, "Center", "red"), "Right" ); writer.SetFooterText( null, writer.GetPageNumOfTotalText(8), null ); writer.SetMargins(0.25M, 0.75M); // [4] write to current worksheet: 1-based index row and column // coordinates in **ANY** order. // header var stop = 10; var cell = new Cell() { AllBorders = true, Bold = true, HorizontalAlignment = CellAlignment.HorizontalCenter }; for (var i = 1; i < stop; ++i) { cell.Value = string.Format("H{0}", i); writer.WriteCell(1, i, cell); } // data cell = new Cell() { AllBorders = true, Bold = true }; for (int i = 2; i < stop; ++i) { for (int j = 1; j < stop; ++j) { cell.Value = ++cellValue; writer.WriteCell(i, j, cell); } } } while (++sheetCount < 4); // [5] write workbook File.WriteAllBytes( Path.Combine(BASE_DIRECTORY, "epplus-test-simple.xlsx"), writer.GetAllBytes() ); } }