예제 #1
0
        protected virtual void DoOutTable(object dataSource, TableOptions options)
        {
            options ??= new TableOptions();

            var tableDataSource = GetDataSource(dataSource,
                                                new DataSourceParameters()
            {
                IgnoreErrors = options.IgnoreErrors, Columns = options.SelectColumns, SkipColumns = options.SkipColumns
            });

            var       spread    = options?.Spreadsheet?.Workbook ?? Workbook;
            Worksheet worksheet = null;

            options ??= new TableOptions();

            using (new UsingProcessor(() => spread.BeginUpdate(), () => spread.EndUpdate()))
            {
                if (!string.IsNullOrWhiteSpace(options.SheetName))
                {
                    var sheet = spread.Worksheets.Where(s => string.Compare(s.Name, options.SheetName, true) == 0).FirstOrDefault();
                    if (sheet != null)
                    {
                        if (!options.Replace)
                        {
                            throw new Exception($"Cannot create spreadsheet table: sheet '{options.SheetName}' already exists.");
                        }

                        var sheetName = sheet.Name;

                        worksheet = spread.Worksheets.Insert(sheet.Index);
                        spread.Worksheets.Remove(sheet);

                        worksheet.Name = sheetName;
                    }
                }

                if (worksheet == null)
                {
                    var newSheetName = !string.IsNullOrWhiteSpace(options.SheetName) ? options.SheetName :
                                       Utils.AddUniqueString(spread.Worksheets.Select(sheet => sheet.Name).ToList(),
                                                             "Sheet1", StringComparison.CurrentCultureIgnoreCase, false);

                    if (spread.Worksheets.Count == 1 && IsRangeEmpty(spread.Worksheets[0].GetUsedRange()))
                    {
                        worksheet      = spread.Worksheets[0];
                        worksheet.Name = newSheetName;
                    }
                    else
                    {
                        worksheet = spread.Worksheets.Add(newSheetName);
                    }
                }

                var table = SpreadsheetUtils.AppendDataSource(worksheet, tableDataSource, true, Math.Max(options.FirstRowIndex, 0), Math.Max(options.FirstColumnIndex, 0));
                if (!string.IsNullOrWhiteSpace(options.TableName))
                {
                    table.Name = options.TableName;
                }

                if (options.FreezeTopRow)
                {
                    worksheet.FreezeRows(0);
                }

                if (table.Range != null)
                {
                    AddComments(table.Range, options.Comment);
                }

                if (!string.IsNullOrWhiteSpace(options.Formatting))
                {
                    var scanner = new Scanner();
                    var parser  = new Parser(scanner);

                    var tree = parser.Parse(options.Formatting);
                    if (tree.Errors.Count > 0)
                    {
                        var strErrors = new StringBuilder();

                        foreach (var error in tree.Errors)
                        {
                            if (strErrors.Length > 0)
                            {
                                strErrors.AppendLine();
                            }
                            strErrors.Append(error.Message);
                        }

                        throw new Exception(strErrors.ToString());
                    }

                    List <BaseCommand> commands = null;
                    try
                    {
                        commands = tree.Eval() as List <BaseCommand>;
                    }
                    catch (Exception)
                    {
                        //Do nothing, skip invalid commands
                    }

                    if (commands != null)
                    {
                        var gridData = new GridData();
                        gridData.ApplyGridFormatConditions(commands);
                        SpreadsheetUtils.ApplyGridFormatting(table, gridData, false);
                    }
                }

                if (options.CalculatedColumns != null && options.CalculatedColumns.Length > 0)
                {
                    for (int i = 0; i < options.CalculatedColumns.Length; i++)
                    {
                        var calculatedColumn = options.CalculatedColumns[i];
                        if (string.IsNullOrWhiteSpace(calculatedColumn))
                        {
                            continue;
                        }

                        var p = calculatedColumn.IndexOf('=');
                        if (p < 0)
                        {
                            ReportError("Cannot add calculated column: cannot get column name. Calculated columns have format '[CalcColumn1]=[Column1]*[Column2]'.");
                            continue;
                        }

                        var columnName    = calculatedColumn[..(p - 1)].Trim();
예제 #2
0
        protected void DoWriteDataTable(object dataSource, DataTableOptions options)
        {
            options ??= new DataTableOptions();

            var book = options.Book?.Document ?? Document;

            options ??= new DataTableOptions();

            var tableDataSource = GetDataSource(dataSource,
                                                new DataSourceParameters()
            {
                IgnoreErrors = options.IgnoreErrors, Columns = options.SelectColumns, SkipColumns = options.SkipColumns
            });

            string htmlTable;

            using (var spreadsheet = SpreadsheetUtils.CreateWorkbook())
            {
                Worksheet worksheet;

                using (new UsingProcessor(
                           () => spreadsheet.BeginUpdate(), () => spreadsheet.EndUpdate()))
                {
                    worksheet = spreadsheet.Worksheets[0];

                    var table = SpreadsheetUtils.AppendDataSource(worksheet, tableDataSource, true);

                    if (!string.IsNullOrWhiteSpace(options.Formatting))
                    {
                        var scanner = new Scanner();
                        var parser  = new Parser(scanner);

                        var tree = parser.Parse(options.Formatting);
                        if (tree.Errors.Count > 0)
                        {
                            var strErrors = new StringBuilder();

                            foreach (var error in tree.Errors)
                            {
                                if (strErrors.Length > 0)
                                {
                                    strErrors.AppendLine();
                                }
                                strErrors.Append(error.Message);
                            }

                            throw new Exception(strErrors.ToString());
                        }

                        List <BaseCommand> commands = null;
                        try
                        {
                            commands = tree.Eval() as List <BaseCommand>;
                        }
                        catch (Exception)
                        {
                            //Do nothing, skip invalid commands
                        }

                        if (commands != null)
                        {
                            var gridData = new GridData();
                            gridData.ApplyGridFormatConditions(commands);
                            SpreadsheetUtils.ApplyGridFormatting(table, gridData, false);
                        }
                    }

                    if (options.CalculatedColumns != null && options.CalculatedColumns.Length > 0)
                    {
                        for (int i = 0; i < options.CalculatedColumns.Length; i++)
                        {
                            var calculatedColumn = options.CalculatedColumns[i];
                            if (string.IsNullOrWhiteSpace(calculatedColumn))
                            {
                                continue;
                            }

                            var p = calculatedColumn.IndexOf('=');
                            if (p < 0)
                            {
                                ReportError("Cannot add calculated column: cannot get column name. Calculated columns have format '[CalcColumn1]=[Column1]*[Column2]'.");
                                continue;
                            }

                            var columnName    = calculatedColumn[..(p - 1)].Trim();