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