void GenerateColumns(IXlSheet sheet)
{
// Create the column "A" and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 18;
// Create the column "B" and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 166;
XlNumberFormat numberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
// Begin to group worksheet columns starting from the column "C" to the column "F".
sheet.BeginGroup(false);
// Create four successive columns ("C", "D", "E" and "F") and set the specific number format for their cells.
for (int i = 0; i < 4; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 117;
column.ApplyFormatting(numberFormat);
}
}
// Finalize the group creation.
sheet.EndGroup();
// Create the summary column "G", adjust its width and set the specific number format for its cells.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 117;
column.ApplyFormatting(numberFormat);
}
}
void GenerateData(IXlSheet sheet, string nameOfState)
{
// Create the header row for the state sales.
GenerateHeaderRow(sheet, nameOfState);
int firstDataRowIndex = sheet.CurrentRowIndex;
// Begin to group worksheet rows (create the inner group of rows containing sales data for the specific state).
sheet.BeginGroup(false);
// Create the query expression to retrieve sales data for the specified State. Then, sort data by the Product key in ascending order.
var salesQuery = from data in sales
where data.State == nameOfState
orderby data.Product
select data;
// Create the data row to display sales information for each product.
foreach (SalesData data in salesQuery)
{
GenerateDataRow(sheet, data);
}
// Finalize the group creation.
sheet.EndGroup();
// Create the summary row for the group.
GenerateTotalRow(sheet, firstDataRowIndex);
}
void GenerateDocument(IXlDocument document)
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Sales Report";
// Specify print settings for the worksheet.
SetupPageParameters(sheet);
// Specify the summary row and summary column location for the grouped data.
sheet.OutlineProperties.SummaryBelow = true;
sheet.OutlineProperties.SummaryRight = true;
// Generate worksheet columns.
GenerateColumns(sheet);
// Add the document title.
GenerateTitle(sheet);
// Begin to group worksheet rows (create the outer group of rows).
sheet.BeginGroup(false);
// Create the query expression to retrieve data from the sales list and group data by the State.
// Query variable is an IEnumerable<IGrouping<string, SalesData>>.
var statesQuery = from data in sales
group data by data.State into dataGroup
orderby dataGroup.Key
select dataGroup.Key;
// Create data rows to display sales for each state.
foreach (string state in statesQuery)
{
GenerateData(sheet, state);
}
// Finalize the group creation.
sheet.EndGroup();
// Create the grand total row.
GenerateGrandTotalRow(sheet);
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
}
}