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);
                                }
                            }
                        }
                    }
                }
            }
        }
Example #3
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 ExportDocumentTitleRow(IWorksheetExporter worksheetExporter)
 {
     using (IRowExporter documentTitleRowExporter = worksheetExporter.CreateRowExporter())
     {
         using (ICellExporter cellExporter = documentTitleRowExporter.CreateCellExporter())
         {
             cellExporter.SetValue(DocumentTitle);
             cellExporter.SetFormat(new SpreadCellFormat
             {
                 Fill                = SpreadPatternFill.CreateSolidFill(new SpreadColor(10, 144, 208)),
                 ForeColor           = new SpreadThemableColor(new SpreadColor(255, 255, 255)),
                 FontSize            = 16,
                 FontFamily          = new SpreadThemableFontFamily("Arial"),
                 HorizontalAlignment = SpreadHorizontalAlignment.Center
             });
         }
     }
 }
        private static void ExportDocumentHeaderRow(IWorksheetExporter worksheetExporter)
        {
            using (IRowExporter headerRowExporter = worksheetExporter.CreateRowExporter())
            {
                SpreadCellFormat format = new SpreadCellFormat
                {
                    Fill       = SpreadPatternFill.CreateSolidFill(new SpreadColor(220, 220, 220)),
                    FontSize   = 14,
                    FontFamily = new SpreadThemableFontFamily("Arial"),
                };

                SpreadCellFormat lastCellFormat = new SpreadCellFormat
                {
                    Fill                = format.Fill,
                    FontSize            = format.FontSize,
                    FontFamily          = format.FontFamily,
                    HorizontalAlignment = SpreadHorizontalAlignment.Right
                };

                using (ICellExporter cellExporter = headerRowExporter.CreateCellExporter())
                {
                    cellExporter.SetValue(TitleColumnHeader);
                    cellExporter.SetFormat(format);
                }

                using (ICellExporter cellExporter = headerRowExporter.CreateCellExporter())
                {
                    cellExporter.SetValue(UniversityColumnHeader);
                    cellExporter.SetFormat(format);
                }

                using (ICellExporter cellExporter = headerRowExporter.CreateCellExporter())
                {
                    cellExporter.SetValue(ProgressColumnHeader);
                    cellExporter.SetFormat(lastCellFormat);
                }
            }
        }
        private void ExportHeaderRows(IWorksheetExporter worksheetExporter, IEnumerable <dynamic> data, string[] columnHeaders, bool isDetailGrid)
        {
            using IRowExporter rowExporter = worksheetExporter.CreateRowExporter();
            rowExporter.SetHeightInPoints(20 /*you can change this to suite your needs*/);

            //Add Column Formatting
            SpreadCellFormat format = new SpreadCellFormat
            {
                IsBold              = true,
                Fill                = SpreadPatternFill.CreateSolidFill(new SpreadColor(142, 196, 65)),
                ForeColor           = new SpreadThemableColor(new SpreadColor(255, 255, 255)),
                HorizontalAlignment = SpreadHorizontalAlignment.Center,
                VerticalAlignment   = SpreadVerticalAlignment.Center
            };

            //If the current exported grid is a detail grid leave a blank cell
            if (isDetailGrid)
            {
                using ICellExporter cellExporter = rowExporter.CreateCellExporter();
                cellExporter.SetValue(string.Empty);
            }

            var columnName = GetDetailGridColumn(data);

            //Add Columns to Excel
            for (int i = 0; i < columnHeaders.Length; i++)
            {
                //Ignore the column that is our detail grid
                if (columnHeaders[i] == columnName)
                {
                    continue;
                }

                using ICellExporter cellExporter = rowExporter.CreateCellExporter();
                cellExporter.SetFormat(format);
                cellExporter.SetValue(columnHeaders[i]);
            }
        }
        private int AddGroupRow(IWorksheetExporter worksheet, int outlineLevel, int rowIndex, int numberOfIndentCells,
                                QueryableCollectionViewGroup group, IList <GridViewBoundColumnBase> columns)
        {
            int startColumnIndex = this.GetGroupLevel(group);

            this.mergedCells.Add(new CellRange(rowIndex, startColumnIndex, rowIndex, numberOfIndentCells + columns.Count - 1));

            SpreadCellFormat format = new SpreadCellFormat();

            format.Fill = SpreadPatternFill.CreateSolidFill(ColorToSpreadColor(this.GroupHeaderRowColor));
            format.HorizontalAlignment = SpreadHorizontalAlignment.Left;

            using (IRowExporter row = worksheet.CreateRowExporter())
            {
                row.SetOutlineLevel(outlineLevel - 1);

                row.SkipCells(startColumnIndex);

                for (int i = startColumnIndex; i < numberOfIndentCells + columns.Count - 1; i++)
                {
                    using (ICellExporter cell = row.CreateCellExporter())
                    {
                        cell.SetFormat(format);

                        if (group.Key is int)
                        {
                            cell.SetValue((int)group.Key);
                        }
                        else if (group.Key is double)
                        {
                            cell.SetValue((double)group.Key);
                        }
                        else
                        {
                            string cellValue = group.Key != null?group.Key.ToString() : string.Empty;

                            cell.SetValue(cellValue);
                        }
                    }
                }
            }

            rowIndex++;
            startColumnIndex++;

            if (group.HasSubgroups)
            {
                foreach (IGroup subGroup in group.Subgroups)
                {
                    int newRowIndex = this.AddGroupRow(worksheet, outlineLevel + 1, rowIndex, numberOfIndentCells, subGroup as QueryableCollectionViewGroup, columns);
                    rowIndex = newRowIndex;
                }
            }
            else
            {
                this.AddDataRows(worksheet, outlineLevel, startColumnIndex, group.Items, columns);
                rowIndex += group.Items.Count;
            }

            return(rowIndex);
        }
        private void ExportBodyRows(IWorksheetExporter worksheetExporter, IEnumerable <dynamic> data, string[] columnHeaders, bool isDetailGrid)
        {
            //Add Cell Formatting
            SpreadCellFormat format = new SpreadCellFormat
            {
                FontSize            = 10,
                VerticalAlignment   = SpreadVerticalAlignment.Center,
                HorizontalAlignment = SpreadHorizontalAlignment.Center,
                Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(50, 190, 255)),
            };

            SpreadCellFormat detailFormat = new SpreadCellFormat
            {
                FontSize            = 10,
                VerticalAlignment   = SpreadVerticalAlignment.Center,
                HorizontalAlignment = SpreadHorizontalAlignment.Center
            };

            //Loop through data rows
            foreach (var item in data)
            {
                //Create a new row
                using IRowExporter rowExporter = worksheetExporter.CreateRowExporter();
                rowExporter.SetHeightInPoints(20 /*you can change this to suite your needs*/);

                //If the current exported grid is a detail grid leave a blank cell
                if (isDetailGrid)
                {
                    using ICellExporter cellExporter = rowExporter.CreateCellExporter();
                    cellExporter.SetValue(string.Empty);
                    cellExporter.SetFormat(detailFormat);
                }

                //Add value to each column key
                foreach (var key in columnHeaders)
                {
                    try
                    {
                        //Get Type of current datasource
                        Type type = data.FirstOrDefault().GetType();
                        var  prop = type.GetProperty(key);

                        var cellValue = prop.GetValue(item, null);
                        if (cellValue is null)
                        {
                            continue;
                        }
                        //check if the model has a List, which means it has a detail grid attached to it.
                        else if (cellValue is IList detailGrid)
                        {
                            //Dispose current rowExporter instance
                            rowExporter.Dispose();
                            ExportGrid(worksheetExporter, detailGrid.Cast <dynamic>(), true);
                        }

                        //Add value to Excel cell
                        using ICellExporter cellExporter = rowExporter.CreateCellExporter();
                        cellExporter.SetValue(cellValue.ToString());
                        cellExporter.SetFormat(isDetailGrid ? detailFormat : format);
                    }
                    catch (NullReferenceException exception)
                    {
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }