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 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);
                                }
                            }
                        }
                    }
                }
            }
        }
Beispiel #4
0
        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");
        }
Beispiel #6
0
        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 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);
        }
        /// <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("&nbsp;", " "));
                    }
                }
            }


            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("&nbsp;", " "));
                    }
                }

                for (int i = 0; i < settings.HeadStrings.Length; i++)
                {
                    using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
                    {
                        cellExporter.SetFormat(settings.HeaderFormat);
                        cellExporter.SetValue(settings.HeadStrings[i]);
                    }
                }
            }
        }
        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]);
            }
        }
Beispiel #11
0
        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 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 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);
                    }
                }
            }
        }
Beispiel #14
0
        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");
            }
        }
Beispiel #16
0
        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);
                    }
                }
            }
        }
        /// <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("&nbsp;", " "));
                                            }
                                        }
                                    }

                                    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("&nbsp;", " ").Replace("<br>", "\r\n"));
                                        }
                                        else
                                        {
                                            cellExporter.SetValue("");
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        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);
        }