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 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); } } } }
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 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 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"); } }
/// <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(""); } } } } } } } } }