private static void GenerateDocument(string filePath)
{
using (FileStream stream = File.OpenWrite(filePath))
{
using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream))
{
using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter("My sheet"))
{
worksheet.SkipColumns(1);
using (IColumnExporter column = worksheet.CreateColumnExporter())
{
column.SetWidthInPixels(80);
}
worksheet.SkipRows(3);
using (IRowExporter row = worksheet.CreateRowExporter())
{
row.SkipCells(3);
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("Merged cell.");
cell.SetFormat(new SpreadCellFormat()
{
HorizontalAlignment = SpreadHorizontalAlignment.Center,
VerticalAlignment = SpreadVerticalAlignment.Center
});
}
}
using (IRowExporter row = worksheet.CreateRowExporter())
{
row.SetHeightInPixels(200);
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(123.456);
}
using (ICellExporter cell = row.CreateCellExporter())
{
SpreadCellFormat format = new SpreadCellFormat()
{
NumberFormat = "dd/mm/yyyy",
IsBold = true
};
cell.SetFormat(format);
cell.SetValue(42370);
}
}
worksheet.MergeCells(3, 3, 6, 6);
}
}
}
}
private static void GenerateDocument(string filePath)
{
using (FileStream stream = File.OpenWrite(filePath))
{
using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream))
{
// Creating a style which would be used later in the code.
SpreadCellStyle style = workbook.CellStyles.Add("MyStyle");
style.Underline = SpreadUnderlineType.DoubleAccounting;
style.VerticalAlignment = SpreadVerticalAlignment.Top;
using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter("My sheet"))
{
// It is mandatory to export the worksheet view state before filling the worksheet with data.
using (IWorksheetViewExporter worksheetView = worksheet.CreateWorksheetViewExporter())
{
worksheetView.SetFirstVisibleCell(3, 0);
worksheetView.AddSelectionRange(9, 0, 13, 6);
worksheetView.SetActiveSelectionCell(11, 3);
}
// It is mandatory to export the column setting before exporting the row and cell data.
worksheet.SkipColumns(1);
using (IColumnExporter column = worksheet.CreateColumnExporter())
{
column.SetWidthInPixels(80);
}
worksheet.SkipRows(3);
using (IRowExporter row = worksheet.CreateRowExporter())
{
row.SkipCells(3);
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("Merged cell.");
cell.SetFormat(new SpreadCellFormat()
{
CellStyle = style,
HorizontalAlignment = SpreadHorizontalAlignment.Center,
VerticalAlignment = SpreadVerticalAlignment.Center
});
}
}
using (IRowExporter row = worksheet.CreateRowExporter())
{
row.SetHeightInPixels(200);
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(123.456);
}
using (ICellExporter cell = row.CreateCellExporter())
{
SpreadCellFormat format = new SpreadCellFormat()
{
NumberFormat = "dd/mm/yyyy",
IsBold = true
};
cell.SetFormat(format);
cell.SetValue(42370);
}
}
worksheet.MergeCells(3, 3, 6, 6);
}
}
}
Console.WriteLine("Document generated.");
ProcessStartInfo psi = new ProcessStartInfo()
{
FileName = filePath,
UseShellExecute = true
};
Process.Start(psi);
}
private int AddGroupRow(IWorksheetExporter worksheet, int outlineLevel, int rowIndex, int numberOfIndentCells,
QueryableCollectionViewGroup group, IList <GridViewBoundColumnBase> columns)
{
int startColumnIndex = this.GetGroupLevel(group);
this.mergedCells.Add(new CellRange(rowIndex, startColumnIndex, rowIndex, numberOfIndentCells + columns.Count - 1));
SpreadCellFormat format = new SpreadCellFormat();
format.Fill = SpreadPatternFill.CreateSolidFill(ColorToSpreadColor(this.GroupHeaderRowColor));
format.HorizontalAlignment = SpreadHorizontalAlignment.Left;
using (IRowExporter row = worksheet.CreateRowExporter())
{
row.SetOutlineLevel(outlineLevel - 1);
row.SkipCells(startColumnIndex);
for (int i = startColumnIndex; i < numberOfIndentCells + columns.Count - 1; i++)
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetFormat(format);
if (group.Key is int)
{
cell.SetValue((int)group.Key);
}
else if (group.Key is double)
{
cell.SetValue((double)group.Key);
}
else
{
string cellValue = group.Key != null?group.Key.ToString() : string.Empty;
cell.SetValue(cellValue);
}
}
}
}
rowIndex++;
startColumnIndex++;
if (group.HasSubgroups)
{
foreach (IGroup subGroup in group.Subgroups)
{
int newRowIndex = this.AddGroupRow(worksheet, outlineLevel + 1, rowIndex, numberOfIndentCells, subGroup as QueryableCollectionViewGroup, columns);
rowIndex = newRowIndex;
}
}
else
{
this.AddDataRows(worksheet, outlineLevel, startColumnIndex, group.Items, columns);
rowIndex += group.Items.Count;
}
return(rowIndex);
}
