private void AddHeaderRow(IWorksheetExporter worksheet, int numberOfGroupDescriptors, IList <GridViewBoundColumnBase> columns)
{
int headerColumnStartIndex = numberOfGroupDescriptors;
using (IRowExporter row = worksheet.CreateRowExporter())
{
SpreadCellFormat format = new SpreadCellFormat();
format.Fill = SpreadPatternFill.CreateSolidFill(ColorToSpreadColor(this.HeaderRowColor));
for (int i = 0; i < headerColumnStartIndex; i++)
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetFormat(format);
}
}
for (int i = 0; i < columns.Count; i++)
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetFormat(format);
cell.SetValue(columns[i].Header.ToString());
}
}
}
}
private void AddDataRows(IWorksheetExporter worksheet, int outlineLevel, int startColumnIndex, IList items, IList <GridViewBoundColumnBase> columns)
{
SpreadCellFormat format = new SpreadCellFormat();
format.Fill = SpreadPatternFill.CreateSolidFill(ColorToSpreadColor(this.DataRowColor));
SpreadCellFormat currencyFormat = new SpreadCellFormat();
currencyFormat.Fill = format.Fill;
currencyFormat.NumberFormat = "$#,##0.00";
for (int rowIndex = 0; rowIndex < items.Count; rowIndex++)
{
using (IRowExporter row = worksheet.CreateRowExporter())
{
row.SetOutlineLevel(outlineLevel);
for (int i = 0; i < startColumnIndex; i++)
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetFormat(format);
}
}
for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
{
using (ICellExporter cell = row.CreateCellExporter())
{
object value = columns[columnIndex].GetValueForItem(items[rowIndex]);
if (value is int)
{
cell.SetValue((int)value);
cell.SetFormat(format);
}
else
{
string stringValue = value.ToString();
if (stringValue.Contains('$'))
{
stringValue = stringValue.Replace("$", string.Empty);
double doubleValue = double.Parse(stringValue);
cell.SetValue(doubleValue);
cell.SetFormat(currencyFormat);
}
else
{
cell.SetValue(stringValue);
cell.SetFormat(format);
}
}
}
}
}
}
}
private async void GenerateDocument()
{
SpreadCellFormat format = new SpreadCellFormat();
format.FontFamily = new SpreadThemableFontFamily(this.FontFamiliesItemsSource[this.SelectedFontFamilyIndex]);
format.FontSize = int.Parse(this.FontSizesItemsSource[this.SelectedFontSizeIndex]);
format.IsBold = this.IsBold;
format.IsItalic = this.IsItalic;
PredefinedColors selectedFillColor = (PredefinedColors)this.SelectedFillColorIndex;
PredefinedColors selectedTextColor = (PredefinedColors)this.SelectedTextColorIndex;
if (selectedFillColor != PredefinedColors.NoColor)
{
format.Fill = SpreadPatternFill.CreateSolidFill(ToSpreadColor(selectedFillColor));
}
if (selectedTextColor != PredefinedColors.NoColor)
{
format.ForeColor = ToSpreadColor(selectedTextColor);
}
format.Underline = (SpreadUnderlineType)this.SelectedUnderlineIndex;
using (MemoryStream stream = new MemoryStream())
{
using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream))
{
using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter("Sheet1"))
{
using (IRowExporter row = worksheet.CreateRowExporter())
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(this.FirstCellValue ?? "Sample text");
cell.SetFormat(format);
}
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetFormula(this.SecondCellValue ?? "=1+2");
cell.SetFormat(format);
}
}
}
}
await DependencyService.Get <IXlsxFileViewer>().View(stream, "GettingStarted.xlsx");
}
}
private static void ExportDocumentTitleRow(IWorksheetExporter worksheetExporter)
{
using (IRowExporter documentTitleRowExporter = worksheetExporter.CreateRowExporter())
{
using (ICellExporter cellExporter = documentTitleRowExporter.CreateCellExporter())
{
cellExporter.SetValue(DocumentTitle);
cellExporter.SetFormat(new SpreadCellFormat
{
Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(10, 144, 208)),
ForeColor = new SpreadThemableColor(new SpreadColor(255, 255, 255)),
FontSize = 16,
FontFamily = new SpreadThemableFontFamily("Arial"),
HorizontalAlignment = SpreadHorizontalAlignment.Center
});
}
}
}
private static void ExportDocumentHeaderRow(IWorksheetExporter worksheetExporter)
{
using (IRowExporter headerRowExporter = worksheetExporter.CreateRowExporter())
{
SpreadCellFormat format = new SpreadCellFormat
{
Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(220, 220, 220)),
FontSize = 14,
FontFamily = new SpreadThemableFontFamily("Arial"),
};
SpreadCellFormat lastCellFormat = new SpreadCellFormat
{
Fill = format.Fill,
FontSize = format.FontSize,
FontFamily = format.FontFamily,
HorizontalAlignment = SpreadHorizontalAlignment.Right
};
using (ICellExporter cellExporter = headerRowExporter.CreateCellExporter())
{
cellExporter.SetValue(TitleColumnHeader);
cellExporter.SetFormat(format);
}
using (ICellExporter cellExporter = headerRowExporter.CreateCellExporter())
{
cellExporter.SetValue(UniversityColumnHeader);
cellExporter.SetFormat(format);
}
using (ICellExporter cellExporter = headerRowExporter.CreateCellExporter())
{
cellExporter.SetValue(ProgressColumnHeader);
cellExporter.SetFormat(lastCellFormat);
}
}
}
private void ExportHeaderRows(IWorksheetExporter worksheetExporter, IEnumerable <dynamic> data, string[] columnHeaders, bool isDetailGrid)
{
using IRowExporter rowExporter = worksheetExporter.CreateRowExporter();
rowExporter.SetHeightInPoints(20 /*you can change this to suite your needs*/);
//Add Column Formatting
SpreadCellFormat format = new SpreadCellFormat
{
IsBold = true,
Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(142, 196, 65)),
ForeColor = new SpreadThemableColor(new SpreadColor(255, 255, 255)),
HorizontalAlignment = SpreadHorizontalAlignment.Center,
VerticalAlignment = SpreadVerticalAlignment.Center
};
//If the current exported grid is a detail grid leave a blank cell
if (isDetailGrid)
{
using ICellExporter cellExporter = rowExporter.CreateCellExporter();
cellExporter.SetValue(string.Empty);
}
var columnName = GetDetailGridColumn(data);
//Add Columns to Excel
for (int i = 0; i < columnHeaders.Length; i++)
{
//Ignore the column that is our detail grid
if (columnHeaders[i] == columnName)
{
continue;
}
using ICellExporter cellExporter = rowExporter.CreateCellExporter();
cellExporter.SetFormat(format);
cellExporter.SetValue(columnHeaders[i]);
}
}
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);
}
private void ExportBodyRows(IWorksheetExporter worksheetExporter, IEnumerable <dynamic> data, string[] columnHeaders, bool isDetailGrid)
{
//Add Cell Formatting
SpreadCellFormat format = new SpreadCellFormat
{
FontSize = 10,
VerticalAlignment = SpreadVerticalAlignment.Center,
HorizontalAlignment = SpreadHorizontalAlignment.Center,
Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(50, 190, 255)),
};
SpreadCellFormat detailFormat = new SpreadCellFormat
{
FontSize = 10,
VerticalAlignment = SpreadVerticalAlignment.Center,
HorizontalAlignment = SpreadHorizontalAlignment.Center
};
//Loop through data rows
foreach (var item in data)
{
//Create a new row
using IRowExporter rowExporter = worksheetExporter.CreateRowExporter();
rowExporter.SetHeightInPoints(20 /*you can change this to suite your needs*/);
//If the current exported grid is a detail grid leave a blank cell
if (isDetailGrid)
{
using ICellExporter cellExporter = rowExporter.CreateCellExporter();
cellExporter.SetValue(string.Empty);
cellExporter.SetFormat(detailFormat);
}
//Add value to each column key
foreach (var key in columnHeaders)
{
try
{
//Get Type of current datasource
Type type = data.FirstOrDefault().GetType();
var prop = type.GetProperty(key);
var cellValue = prop.GetValue(item, null);
if (cellValue is null)
{
continue;
}
//check if the model has a List, which means it has a detail grid attached to it.
else if (cellValue is IList detailGrid)
{
//Dispose current rowExporter instance
rowExporter.Dispose();
ExportGrid(worksheetExporter, detailGrid.Cast <dynamic>(), true);
}
//Add value to Excel cell
using ICellExporter cellExporter = rowExporter.CreateCellExporter();
cellExporter.SetValue(cellValue.ToString());
cellExporter.SetFormat(isDetailGrid ? detailFormat : format);
}
catch (NullReferenceException exception)
{
Console.WriteLine(exception.Message);
}
}
}
}
