private async void GenerateSpreadsheet(object obj)
{
using (MemoryStream stream = new MemoryStream())
{
using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream))
{
using (IWorksheetExporter worksheetExporter = workbookExporter.CreateWorksheetExporter("Courses"))
{
ExportColumnWidths(worksheetExporter);
ExportDocumentTitleRow(worksheetExporter);
ExportDocumentHeaderRow(worksheetExporter);
ExportData(worksheetExporter);
worksheetExporter.MergeCells(0, 0, 0, 2);
}
}
bool success = await DependencyService.Get <IFileViewerService>().View(stream, "my_courses.xlsx");
if (!success)
{
MessagingCenter.Send(this, Messages.CreatingFileFailed);
}
}
}
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 void ExportWorkbook(RadGridView grid, Stream stream)
{
IList <GridViewBoundColumnBase> columns = (from c in grid.Columns.OfType <GridViewBoundColumnBase>()
orderby c.DisplayIndex
select c).ToList();
using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(this.selectedExportFormat, stream))
{
using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter("Sheet1"))
{
this.SetWidthOfColumns(worksheet, grid.GroupDescriptors.Count, columns);
int rowIndex = 0;
if (grid.ShowColumnHeaders)
{
this.AddHeaderRow(worksheet, grid.GroupDescriptors.Count, columns);
rowIndex = 1;
}
if (grid.Items.Groups != null)
{
for (int i = 0; i < grid.Items.Groups.Count; i++)
{
QueryableCollectionViewGroup group = (QueryableCollectionViewGroup)grid.Items.Groups[i];
rowIndex = this.AddGroupRow(worksheet, 1, rowIndex, grid.GroupDescriptors.Count, group, columns);
}
}
else
{
this.AddDataRows(worksheet, 0, 0, grid.Items, columns);
}
foreach (CellRange range in this.mergedCells)
{
worksheet.MergeCells(range.FromRowIndex, range.FromColumnIndex, range.ToRowIndex, range.ToColumnIndex);
}
}
}
}
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 async void GenerateDocument()
{
var maxTitleCharCount = this.Source.Max(p => p.Title.Length);
var maxAuthorCharCount = this.Source.Max(p => p.Author.Length);
using (MemoryStream stream = new MemoryStream())
{
using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream))
{
using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter("Sheet1"))
{
using (IWorksheetViewExporter viewExporter = worksheet.CreateWorksheetViewExporter())
{
// just moving the selection so the bottom border of the header row is visible
viewExporter.AddSelectionRange(0, 3, 0, 3);
}
using (IColumnExporter titleColumn = worksheet.CreateColumnExporter())
{
titleColumn.SetWidthInCharacters(maxTitleCharCount);
}
using (IColumnExporter authorColumn = worksheet.CreateColumnExporter())
{
authorColumn.SetWidthInCharacters(maxAuthorCharCount);
}
using (IRowExporter row = worksheet.CreateRowExporter())
{
SpreadCellFormat headerformat = new SpreadCellFormat();
headerformat.CellStyle = workbook.CellStyles["Heading 1"];
headerformat.HorizontalAlignment = SpreadHorizontalAlignment.Center;
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("Books");
cell.SetFormat(headerformat);
}
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetFormat(headerformat);
}
}
using (IRowExporter row = worksheet.CreateRowExporter())
{
SpreadCellFormat subHeaderformat = new SpreadCellFormat();
subHeaderformat.CellStyle = workbook.CellStyles["Heading 2"];
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("Title");
cell.SetFormat(subHeaderformat);
}
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("Author");
cell.SetFormat(subHeaderformat);
}
}
for (int i = 0; i < this.Source.Count; i++)
{
Book book = this.Source[i];
string styleName = i % 2 == 0 ? "20% - Accent1" : "20% - Accent2";
SpreadCellFormat format = new SpreadCellFormat();
format.CellStyle = workbook.CellStyles[styleName];
using (IRowExporter row = worksheet.CreateRowExporter())
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(book.Title);
cell.SetFormat(format);
}
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(book.Author);
cell.SetFormat(format);
}
}
}
worksheet.MergeCells(0, 0, 0, 1);
}
}
await DependencyService.Get <IXlsxFileViewer>().View(stream, "GettingStarted.xlsx");
}
}
