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 AddWorksheetToExistingDocument(string filePath)
{
using (FileStream stream = File.Open(filePath, FileMode.Open))
{
// Pass SpreadExportMode.Append parameter, and the created workbook exporter will preserve all of the existing worksheets.
using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream, SpreadExportMode.Append))
{
string sheetName = "Sheet name here";
var importedSheetsNames = workbook.GetSheetInfos().Select(sheetInfo => sheetInfo.Name);
if (importedSheetsNames.Contains(sheetName))
{
Console.WriteLine("Sheet with that name already exists in the workbook.");
return;
}
using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter(sheetName))
{
using (IRowExporter row = worksheet.CreateRowExporter())
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("value 1");
cell.SetValue("value 2");
cell.SetValue("value 3");
}
}
}
}
}
Console.WriteLine("Document modified.");
Process.Start(filePath);
}
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 ExportColumnWidths(IWorksheetExporter worksheetExporter)
{
int firstColumnCharactersCount = 0;
int secondColumnCharactersCount = 0;
int thirdColumnCharactersCount = 15;
foreach (CourseViewModel course in this.Courses)
{
firstColumnCharactersCount = Math.Max(firstColumnCharactersCount, course.CourseName.Length);
secondColumnCharactersCount = Math.Max(secondColumnCharactersCount, course.University.Length);
}
using (IColumnExporter columnExporter = worksheetExporter.CreateColumnExporter())
{
columnExporter.SetWidthInCharacters(firstColumnCharactersCount);
}
using (IColumnExporter columnExporter = worksheetExporter.CreateColumnExporter())
{
columnExporter.SetWidthInCharacters(secondColumnCharactersCount);
}
using (IColumnExporter columnExporter = worksheetExporter.CreateColumnExporter())
{
columnExporter.SetWidthInCharacters(thirdColumnCharactersCount);
}
}
private void ExportData(IWorksheetExporter worksheetExporter)
{
foreach (CourseViewModel course in this.Courses)
{
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(course.CourseName);
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(course.University);
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue((double)course.Progress / 100);
cellExporter.SetFormat(new SpreadCellFormat
{
NumberFormat = "0 %",
HorizontalAlignment = SpreadHorizontalAlignment.Right
});
}
}
}
}
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 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, List <CsvGlucoseMeasure> output)
{
using (FileStream stream = File.Open(filePath, FileMode.OpenOrCreate))
{
using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Csv, stream))
{
// Creating a style which would be used later in the code.
//SpreadCellStyle style = workbook.CellStyles.Add("MyStyle");
//style.Underline = SpreadUnderlineType.None;
//style.VerticalAlignment = SpreadVerticalAlignment.Center;
using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter("Data"))
{
//Entête de colonne
using (IRowExporter row = worksheet.CreateRowExporter())
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("Date");
}
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("Taux de glucose");
}
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue("Dans de la zone de confort");
}
}
//Contenu
foreach (var data in output)
{
using (IRowExporter row = worksheet.CreateRowExporter())
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(data.RealDateTimeOffset);
}
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(data.GlucoseLevelMGDL);
}
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(data.InTheMedicalZone == true ? "Oui" : "Non");
}
}
}
}
}
}
App.Current.MainPage.DisplayAlert("Export réussi !", "Fichier exporté avec succès vers le répertoire de téléchargements.", "OK");
}
private byte[] GenerateDocument()
{
MemoryStream documentStream = new MemoryStream();
var documentFormat = _format is FileFormats.Formats.XLSX ? SpreadDocumentFormat.Xlsx : SpreadDocumentFormat.Csv;
using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(documentFormat, documentStream))
{
using IWorksheetExporter worksheetExporter = workbookExporter.CreateWorksheetExporter("Sheet Name");
//Export data to excel
ExportGrid(worksheetExporter, _data, false);
}
return(documentStream.ToArray());
}
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");
}
}
public byte[] Export(IEnumerable <Attendee> source)
{
MemoryStream stream = new MemoryStream();
double[] columnWidths = new double[] { 4.22, 28.56, 16.11, 17.22, 39.11, 11.67, 9.56, 14.22 };
using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream))
{
SpreadCellStyle titleStyle = workbookExporter.CellStyles["Heading 1"];
SpreadCellFormat titleFormat = new SpreadCellFormat()
{
CellStyle = titleStyle
};
SpreadCellStyle rowStyle = workbookExporter.CellStyles.Where(p => p.Name.Contains("Accent1")).FirstOrDefault();
SpreadCellFormat evenRowFormat = new SpreadCellFormat()
{
CellStyle = rowStyle
};
using (IWorksheetExporter worksheetExporter = workbookExporter.CreateWorksheetExporter("Attendee information"))
{
for (int i = 0; i < columnWidths.Length; i++)
{
using (IColumnExporter columnExporter = worksheetExporter.CreateColumnExporter())
{
columnExporter.SetWidthInCharacters(columnWidths[i]);
}
}
ExportRow(worksheetExporter, "Id", "CompanyName", "ContactName", "ContactTitle", "Address", "Country", "Phone", "City", titleFormat);
int rowIndex = 1;
foreach (Attendee attendee in source.Take(10000))
{
SpreadCellFormat currentRowFormat = null;
if (rowIndex++ % 2 == 0)
{
currentRowFormat = evenRowFormat;
}
ExportRow(worksheetExporter, attendee.Id.ToString(), attendee.CompanyName, attendee.ContactName,
attendee.ContactTitle, attendee.Address, attendee.Country, attendee.Phone, attendee.City, currentRowFormat);
}
}
}
return(stream.ToArray());
}
private void ExportGrid(IWorksheetExporter worksheetExporter, IEnumerable <dynamic> data, bool isDetailGrid)
{
//Get Columns From Class
string[] columnHeaders = GetColumnHeaders(data);
if (columnHeaders.Length <= 0)
{
return;
}
//Add Columns to WorkBook
ExportHeaderRows(worksheetExporter, data, columnHeaders, isDetailGrid);
//Add Rows to WorkBook
ExportBodyRows(worksheetExporter, data, columnHeaders, isDetailGrid);
}
public JsonResult Export(string model, string data, string format, string title)
{
var modelObject = JsonConvert.DeserializeObject <IList <ColumnSettings> >(model);
var dataObject = JsonConvert.DeserializeObject <dynamic>(data);
SpreadDocumentFormat exportFormat = format == "CSV" ? SpreadDocumentFormat.Csv : SpreadDocumentFormat.Xlsx;
using (MemoryStream stream = new MemoryStream())
{
using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(exportFormat, stream))
{
using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter(title))
{
using (IRowExporter row = worksheet.CreateRowExporter())
{
for (int idx = 0; idx < modelObject.Count; idx++)
{
var modelCol = modelObject[idx];
string columnName = modelCol.Title ?? modelCol.Field;
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(columnName);
}
}
}
for (int rowIdx = 0; rowIdx < dataObject.Count; rowIdx++)
{
using (IRowExporter row = worksheet.CreateRowExporter())
{
for (int colIdx = 0; colIdx < modelObject.Count; colIdx++)
{
using (ICellExporter cell = row.CreateCellExporter())
{
cell.SetValue(dataObject[rowIdx][modelObject[colIdx].Field].Value);
}
}
}
}
}
}
Session[title] = stream.ToArray();
}
return(Json(new { success = true }, JsonRequestBehavior.AllowGet));
}
private void SetWidthOfColumns(IWorksheetExporter worksheet, int numberOfIndentColumns, IList <GridViewBoundColumnBase> columns)
{
for (int i = 0; i < numberOfIndentColumns; i++)
{
using (IColumnExporter column = worksheet.CreateColumnExporter())
{
column.SetWidthInPixels(WidthOfIndentColumns);
}
}
for (int i = 0; i < columns.Count; i++)
{
using (IColumnExporter column = worksheet.CreateColumnExporter())
{
column.SetWidthInPixels(columns[i].Width.DisplayValue);
}
}
}
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
});
}
}
}
/// <summary>
/// Creazione riga Header
/// </summary>
/// <param name="worksheetExporter">Exporter (per stream)</param>
/// <param name="Headers">Colonne Header aggiuntive (da definizione tabelle)</param>
/// <param name="settings">Impostazioni esportazione - per future configurazioni</param>
private static void ExportHeaderRows(
IWorksheetExporter worksheetExporter,
string[] Headers,
dto.dtoEcoTableExportSettings settings,
string TableName)
{
// Nome tabella
if (!string.IsNullOrWhiteSpace(TableName))
{
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
rowExporter.SetHeightInPoints(settings.HeaderRowHeight);
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(settings.HeaderFormat);
cellExporter.SetValue(TableName.Replace(" ", " "));
}
}
}
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
rowExporter.SetHeightInPoints(settings.HeaderRowHeight);
foreach (string hval in Headers)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(settings.HeaderFormat);
cellExporter.SetValue(hval.Replace(" ", " "));
}
}
for (int i = 0; i < settings.HeadStrings.Length; i++)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(settings.HeaderFormat);
cellExporter.SetValue(settings.HeadStrings[i]);
}
}
}
}
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 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 void SetWidthOfColumns(IWorksheetExporter worksheet, int numberOfIndentColumns, IList<GridViewBoundColumnBase> columns)
{
for (int i = 0; i < numberOfIndentColumns; i++)
{
using (IColumnExporter column = worksheet.CreateColumnExporter())
{
column.SetWidthInPixels(WidthOfIndentColumns);
}
}
for (int i = 0; i < columns.Count; i++)
{
using (IColumnExporter column = worksheet.CreateColumnExporter())
{
column.SetWidthInPixels(columns[i].Width.DisplayValue);
}
}
}
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 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);
}
}
}
}
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");
}
}
private void ExportRow(IWorksheetExporter worksheetExporter, string id, string companyName, string contactName,
string contactTitle, string address, string country, string phone, string city, SpreadCellFormat titleFormat = null)
{
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(id);
if (titleFormat != null)
{
cellExporter.SetFormat(titleFormat);
}
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(companyName);
if (titleFormat != null)
{
cellExporter.SetFormat(titleFormat);
}
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(contactName);
if (titleFormat != null)
{
cellExporter.SetFormat(titleFormat);
}
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(contactTitle);
if (titleFormat != null)
{
cellExporter.SetFormat(titleFormat);
}
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(address);
if (titleFormat != null)
{
cellExporter.SetFormat(titleFormat);
}
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(city);
if (titleFormat != null)
{
cellExporter.SetFormat(titleFormat);
}
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(country);
if (titleFormat != null)
{
cellExporter.SetFormat(titleFormat);
}
}
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(phone);
if (titleFormat != null)
{
cellExporter.SetFormat(titleFormat);
}
}
}
}
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 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());
}
}
}
}
/// <summary>
/// Crea via stream un documento Excel (csv non previsto per via di fogli multipli) con tutte le tabelle economiche di una valutazione
/// </summary>
/// <param name="Eval">Dati valutazione (definizioni, tabelle, ammissioni, importi)</param>
/// <param name="documentStream">Stream output</param>
/// <param name="DocFormat">Formato - Solo XLSX</param>
/// <param name="settings">Impostazioni di esportazione (caratteri, larghezze, colori) - Per personalizzazioni future.</param>
public static void EcoEvalTableExportStream(
Eco.Domain.EconomicEvaluation Eval,
Stream documentStream,
SpreadDocumentFormat DocFormat = SpreadDocumentFormat.Xlsx,
dto.dtoEcoTableExportSettings settings = null)
{
if (settings == null)
{
settings = new dto.dtoEcoTableExportSettings();
}
int exportedCellsCount = 0;
SpreadDocumentFormat selectedDocumentFormat;
int totalCellsCount;
DateTime exportStarted;
bool canExport;
if (Eval == null || Eval.Tables == null || !Eval.Tables.Any())
{
return;
}
using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(DocFormat, documentStream))
{
int SheetNumber = 0;
foreach (Eco.Domain.EconomicTable table in Eval.Tables)
{
if (table != null && table.FieldDefinition != null)
{
SheetNumber++;
int headCols = table.HeaderValues.Count();
int totalCols = headCols + 7;
string sheetName = string.Format("{0}-{1}", SheetNumber, table.FieldDefinition.Name);
if (sheetName.Length > 25)
{
sheetName = string.Format("{0}...", sheetName.Substring(0, 20));
}
sheetName = CleanFileName(sheetName);
using (IWorksheetExporter worksheetExporter = workbookExporter.CreateWorksheetExporter(sheetName))
{
for (int i = 0; i < totalCols; i++)
{
using (IColumnExporter columnExporter = worksheetExporter.CreateColumnExporter())
{
if (i >= headCols)
{
columnExporter.SetWidthInCharacters(settings.ColumnWidths[i - headCols]);
}
else
{
columnExporter.SetWidthInCharacters(settings.ColumnAddWidth);
}
}
}
ExportHeaderRows(worksheetExporter, table.HeaderValues, settings, table.FieldDefinition.Name);
foreach (AdvEconomic.Domain.EconomicItem itm in table.Items)
{
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
rowExporter.SetHeightInPoints(settings.RowHeight);
int columnIndex = 0;
int iv = 0;
foreach (string value in itm.InfoValues)
{
iv++;
if (iv <= headCols)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.NormalFormat, itm.IsAdmit));
cellExporter.SetValue(value.Replace(" ", " "));
}
}
}
if (iv < headCols)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.NormalFormat, itm.IsAdmit));
cellExporter.SetValue("");
}
}
//Quantità
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleFormat, itm.IsAdmit));
cellExporter.SetValue(itm.RequestQuantity);
}
//Prezzo unitario
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleEcoFormat, itm.IsAdmit));
cellExporter.SetValue(itm.RequestUnitPrice);
}
//Totale richiesto
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleEcoFormat, itm.IsAdmit));
cellExporter.SetValue(itm.RequestTotal);
}
//Approvato
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.NormalFormat, itm.IsAdmit));
cellExporter.SetValue(itm.IsAdmit ? settings.BoolValue[1] : settings.BoolValue[0]);
}
//Quantità ammessa
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleFormat, itm.IsAdmit));
cellExporter.SetValue(itm.AdmitQuantity);
}
//Totale ammesso
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.DoubleEcoFormat, itm.IsAdmit));
cellExporter.SetValue(itm.AdmitTotal);
}
//Commenti
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetFormat(dto.dtoEcoTableExportSettings.InvalidCellFormat(settings.NormalFormat, itm.IsAdmit));
if (itm.Comment != null)
{
cellExporter.SetValue(itm.Comment.Replace(" ", " ").Replace("<br>", "\r\n"));
}
else
{
cellExporter.SetValue("");
}
}
}
}
}
}
}
}
}
