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 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); } } } }
/// <summary> /// Formato celle economiche (2 decimali con simbolo Euro) /// </summary> public static SpreadCellFormat InvalidCellFormat(SpreadCellFormat format, bool isValid) { if (isValid) { format.ForeColor = new SpreadThemableColor(new SpreadColor(0, 0, 0)); } else { format.ForeColor = new SpreadThemableColor(new SpreadColor(128, 128, 128)); } return(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"); } }
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 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 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 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 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 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 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()); } } } }