public static void InitializeSpreadsheet(this SpreadsheetControl spreadsheetControl) { SpreadsheetUtils.InitializeWorkbook(spreadsheetControl.Document); spreadsheetControl.Options.Behavior.FunctionNameCulture = FunctionNameCulture.English; spreadsheetControl.DocumentLoaded += (s, e) => SpreadsheetUtils.InitializeWorkbook(((SpreadsheetControl)s).Document); spreadsheetControl.EmptyDocumentCreated += (s, e) => SpreadsheetUtils.InitializeWorkbook(((SpreadsheetControl)s).Document); spreadsheetControl.CellBeginEdit += SpreadRichTextEditForm.SpreadsheetControl_CellBeginEdit; spreadsheetControl.PopupMenuShowing += SpreadRichTextEditForm.SpreadsheetControl_PopupMenuShowing; spreadsheetControl.KeyDown += SpreadsheetControl_KeyDown; spreadsheetControl.BeforeDragRange += SpreadsheetControl_BeforeDragRange; spreadsheetControl.DragOver += SpreadsheetControl_DragOver; spreadsheetControl.DragDrop += SpreadsheetControl_DragDrop; var oldMouseHandler = (IMouseHandlerService)spreadsheetControl.GetService(typeof(IMouseHandlerService)); if (oldMouseHandler != null) { spreadsheetControl.RemoveService(typeof(IMouseHandlerService)); } var newMouseHandler = new SCMouseHandlerService(spreadsheetControl, oldMouseHandler); spreadsheetControl.AddService(typeof(IMouseHandlerService), newMouseHandler); }
protected virtual void DoGetSpreadTable(SpreadsheetWithCopyToBookOptions options, string tableName, out DataTable result) { var spread = options?.Spreadsheet?.Workbook ?? Workbook; var range = SpreadsheetUtils.GetWorkbookRange(spread, tableName); result = SpreadsheetUtils.GetDataTable(range); CopyRangeToBook(range, options); AddComments(range, options.Comment); }
protected void DoCreateDataTable(out DataTable dataTable) { var workbook = GetCmdletSpreadsheet(); var range = SpreadsheetUtils.GetWorkbookRange(workbook, TableName); dataTable = SpreadsheetUtils.GetDataTable(range); CopyRangeToBook(range); AddComments(range); }
public static DataTable ExportToDataTable(this Table table) { if (table == null) { throw new ArgumentNullException(nameof(table)); } var result = SpreadsheetUtils.GetDataTable(table); return(result); }
private void Spreadsheet_DocumentLoaded(object sender, EventArgs e) { SpreadsheetUtils.InitializeWorkbook(Spreadsheet.Document); Spreadsheet.Modified = false; var fluent = mvvmContext.OfType <SpreadsheetDocumentViewModel>(); fluent.ViewModel.FileName = Spreadsheet.Document.Options.Save.CurrentFileName; fluent.ViewModel.Modified = false; }
private static void SpreadsheetControl_DragDrop(object sender, DragEventArgs e) { var spreadsheet = sender as SpreadsheetControl; var point = spreadsheet.PointToClient(new Point(e.X, e.Y)); var cell = spreadsheet.GetCellFromPoint(new PointF(point.X, point.Y)); if (cell == null || spreadsheet.ActiveWorksheet == null) { return; } if (e.Data.GetDataPresent(DataFormats.UnicodeText) || e.Data.GetDataPresent(DataFormats.Text)) { string text; if (e.Data.GetDataPresent(DataFormats.UnicodeText)) { text = Convert.ToString(e.Data.GetData(DataFormats.UnicodeText)); } else if (e.Data.GetDataPresent(DataFormats.Text)) { text = Convert.ToString(e.Data.GetData(DataFormats.Text)); } else { return; } if (string.IsNullOrEmpty(text)) { return; } using var workbook = SpreadsheetUtils.CreateWorkbook(); workbook.LoadDocument(workbook.Options.Import.Txt.Encoding.GetBytes(text), DocumentFormat.Text); cell.CopyFrom(workbook.Worksheets[0].GetDataRange()); } else if (e.Data.GetDataPresent(DataFormats.CommaSeparatedValue)) { #pragma warning disable IDE0019 // Use pattern matching using var csv = e.Data.GetData(DataFormats.CommaSeparatedValue) as MemoryStream; #pragma warning restore IDE0019 // Use pattern matching if (csv != null) { using var workbook = SpreadsheetUtils.CreateWorkbook(); workbook.LoadDocument(csv, DocumentFormat.Csv); cell.CopyFrom(workbook.Worksheets[0].GetDataRange()); } } }
private async void SpreadsheetTableSelectorControl_Load(object sender, EventArgs e) { if (string.IsNullOrWhiteSpace(_Filename)) { return; } var listTables = new List <TableData>(); try { await Task.Run(() => { using var workbook = SpreadsheetUtils.CreateWorkbook(); if (!workbook.LoadDocument(_Filename)) { return; } foreach (Worksheet sheet in workbook.Sheets.Where(s => s is Worksheet)) { foreach (var table in sheet.Tables) { var tableData = new TableData() { SheetName = sheet.Name, TableName = table.Name, Range = table.Range.GetReferenceA1() }; listTables.Add(tableData); } } }).ConfigureAwait(true); } catch (Exception) { throw; } using (new UsingProcessor(() => gridTables.BeginUpdate(), () => gridTables.EndUpdate())) { foreach (var tblData in listTables) { bindingTables.Add(tblData); } if (bindingTables.Count > 0) { bindingTables.Position = 0; } } }
static void LoadTemplate(Workbook template, string templateFile, string templateSheet) { if (string.IsNullOrWhiteSpace(templateSheet)) { template.LoadDocument(templateFile); } else { using var workbook = SpreadsheetUtils.CreateWorkbook(); workbook.LoadDocument(templateFile); template.Worksheets[0].CopyFrom(workbook.Worksheets[templateSheet]); } }
public SCSpreadsheet OutTemplate(object dataSource, string templateFileName, TemplateOptions options = null) { var tableDataSource = GetDataSource(dataSource, new DataSourceParameters() { IgnoreErrors = options.IgnoreErrors, Columns = options.SelectColumns, SkipColumns = options.SkipColumns }); options ??= new TemplateOptions(); var spread = options.Spreadsheet?.Workbook ?? Workbook; if (string.IsNullOrWhiteSpace(templateFileName)) { throw new Exception("Template file is not specified."); } var templateFile = Project.Current.MapPath(templateFileName); if (!File.Exists(templateFile)) { throw new Exception($"Template file '{templateFileName}' does not exist."); } IList <IWorkbook> workbooks; using (var template = SpreadsheetUtils.CreateWorkbook()) { ExecuteLocked(() => LoadTemplate(template, templateFile, options.TemplateSheetName), options.LockFiles ? LockObject : null); template.MailMergeDataSource = tableDataSource; template.MailMergeDataMember = null; template.MailMergeOptions.UseTemplateSheetNames = true; template.MailMergeParameters.Clear(); if (options.Parameters != null) { foreach (DictionaryEntry keyValuePair in options.Parameters) { template.MailMergeParameters.AddParameter(Convert.ToString(keyValuePair.Key), keyValuePair.Value); } } workbooks = template.GenerateMailMergeDocuments(); } ExecuteSynchronized(() => DoWriteWorkbooks(spread, workbooks, options)); return(this);
protected virtual void DoWriteTable(IWorkbook spreadsheet) { PivotTable pivotTable; using (new UsingProcessor(() => spreadsheet.BeginUpdate(), () => spreadsheet.EndUpdate())) { //Disable automatic show of pivot table field list. spreadsheet.DocumentSettings.ShowPivotTableFieldList = false; var table = FindDataTable(); Worksheet pivotSheet = null; if (!string.IsNullOrWhiteSpace(PivotSheetName)) { for (int i = 0; i < spreadsheet.Worksheets.Count; i++) { var sheet = spreadsheet.Worksheets[i]; if (string.Compare(sheet.Name, PivotSheetName, true) == 0) { if (Replace) { spreadsheet.Worksheets.RemoveAt(i); var newSheet = spreadsheet.Worksheets.Insert(i, PivotSheetName); pivotSheet = newSheet; } else { throw new Exception($"Cannot create pivot sheet table: sheet '{PivotSheetName}' already exists."); } break; } } } if (pivotSheet == null) { pivotSheet = spreadsheet.Worksheets.Add(PivotSheetName); } pivotTable = pivotSheet.PivotTables.Add(table.Range, pivotSheet[0, 0], PivotTableName); using (new UsingProcessor(() => pivotTable.BeginUpdate(), () => pivotTable.EndUpdate())) { if (RowFields != null && RowFields.Length > 0) { foreach (var fieldName in RowFields) { pivotTable.RowFields.Add(pivotTable.Fields[fieldName]); } } if (ColumnFields != null && ColumnFields.Length > 0) { foreach (var fieldName in ColumnFields) { pivotTable.ColumnFields.Add(pivotTable.Fields[fieldName]); } } if (PageFields != null && PageFields.Length > 0) { foreach (var fieldName in PageFields) { pivotTable.PageFields.Add(pivotTable.Fields[fieldName]); } } if (DataFields != null && DataFields.Length > 0) { foreach (var fieldName in DataFields) { pivotTable.DataFields.Add(pivotTable.Fields[fieldName]); } } if (CalculatedRowFields != null && CalculatedRowFields.Length > 0) { foreach (var fieldDefinition in CalculatedRowFields) { var calcField = AddCalculatedField(pivotTable, fieldDefinition); pivotTable.RowFields.Add(calcField); } } if (CalculatedColumnFields != null && CalculatedColumnFields.Length > 0) { foreach (var fieldDefinition in CalculatedColumnFields) { var calcField = AddCalculatedField(pivotTable, fieldDefinition); pivotTable.ColumnFields.Add(calcField); } } if (CalculatedPageFields != null && CalculatedPageFields.Length > 0) { foreach (var fieldDefinition in CalculatedPageFields) { var calcField = AddCalculatedField(pivotTable, fieldDefinition); pivotTable.PageFields.Add(calcField); } } if (CalculatedDataFields != null && CalculatedDataFields.Length > 0) { foreach (var fieldDefinition in CalculatedDataFields) { var calcField = AddCalculatedField(pivotTable, fieldDefinition); pivotTable.DataFields.Add(calcField); } } if (RowFieldNumberFormats != null && RowFieldNumberFormats.Length > 0) { for (int i = 0; i < Math.Min(RowFieldNumberFormats.Length, pivotTable.RowFields.Count); i++) { pivotTable.RowFields[i].Field.NumberFormat = RowFieldNumberFormats[i]; } } if (ColumnFieldNumberFormats != null && ColumnFieldNumberFormats.Length > 0) { for (int i = 0; i < Math.Min(ColumnFieldNumberFormats.Length, pivotTable.ColumnFields.Count); i++) { pivotTable.ColumnFields[i].Field.NumberFormat = ColumnFieldNumberFormats[i]; } } if (PageFieldNumberFormats != null && PageFieldNumberFormats.Length > 0) { for (int i = 0; i < Math.Min(PageFieldNumberFormats.Length, pivotTable.PageFields.Count); i++) { pivotTable.PageFields[i].Field.NumberFormat = PageFieldNumberFormats[i]; } } if (DataFieldNumberFormats != null && DataFieldNumberFormats.Length > 0) { for (int i = 0; i < Math.Min(DataFieldNumberFormats.Length, pivotTable.DataFields.Count); i++) { pivotTable.DataFields[i].Field.NumberFormat = DataFieldNumberFormats[i]; } } if (ShowValuesAs != null && ShowValuesAs.Length > 0) { for (int i = 0; i < Math.Min(ShowValuesAs.Length, pivotTable.DataFields.Count); i++) { var baseFieldName = ShowValuesAsBaseFields != null && i < ShowValuesAsBaseFields.Length ? ShowValuesAsBaseFields[i] : null; var baseType = ShowValuesAsBaseTypes != null && i < ShowValuesAsBaseTypes.Length ? ShowValuesAsBaseTypes[i] : PivotShowValueAsItemType.Previous; var baseField = !string.IsNullOrWhiteSpace(baseFieldName) ? pivotTable.Fields[baseFieldName] : null; if (!string.IsNullOrWhiteSpace(baseFieldName) && baseField == null) { throw new Exception($"Cannot configure pivot table: cannot find base field '{baseFieldName}'."); } var dataField = pivotTable.DataFields[i]; dataField.ShowValuesWithCalculation(ShowValuesAs[i], baseField, (PivotBaseItemType)(int)baseType); } } if (SummarizeValuesBy != null && SummarizeValuesBy.Length > 0) { for (int i = 0; i < Math.Min(SummarizeValuesBy.Length, pivotTable.DataFields.Count); i++) { pivotTable.DataFields[i].SummarizeValuesBy = SummarizeValuesBy[i]; } } pivotTable.Layout.SetReportLayout(Layout ?? PivotReportLayout.Compact); if (CompactNewFields) { pivotTable.Layout.CompactNewFields = true; } if (DataOnRows) { pivotTable.Layout.DataOnRows = true; } if (IndentInCompactForm.HasValue) { pivotTable.Layout.IndentInCompactForm = IndentInCompactForm.Value; } if (MergeTitles) { pivotTable.Layout.MergeTitles = true; } if (OutlineNewFields) { pivotTable.Layout.OutlineNewFields = true; } if (PageOrder.HasValue) { pivotTable.Layout.PageOrder = PageOrder.Value; } if (PageWrap.HasValue) { pivotTable.Layout.PageWrap = PageWrap.Value; } if (HideAllSubtotals) { pivotTable.Layout.HideAllSubtotals(); pivotTable.Layout.ShowColumnGrandTotals = false; pivotTable.Layout.ShowRowGrandTotals = false; } if (HideColumnGrandTotals) { pivotTable.Layout.ShowColumnGrandTotals = false; } if (HideRowGrandTotals) { pivotTable.Layout.ShowRowGrandTotals = false; } if (SubtotalIncludeHiddenItems) { pivotTable.Layout.SubtotalIncludeHiddenItems = true; } if (HideColumnHeaders) { pivotTable.ShowColumnHeaders = false; } if (HideRowHeaders) { pivotTable.ShowRowHeaders = false; } if (Style != PivotTableStyleId.None) { pivotTable.Style = spreadsheet.TableStyles[(BuiltInPivotStyleId)Style]; } if (AltTextDescription != null) { pivotTable.View.AltTextDescription = AltTextDescription; } if (AltTextTitle != null) { pivotTable.View.AltTextTitle = AltTextTitle; } if (ColumnHeaderCaption != null) { pivotTable.View.ColumnHeaderCaption = ColumnHeaderCaption; } if (DataCaption != null) { pivotTable.View.DataCaption = DataCaption; } if (ErrorCaption != null) { pivotTable.View.ErrorCaption = ErrorCaption; } if (GrandTotalCaption != null) { pivotTable.View.GrandTotalCaption = GrandTotalCaption; } if (MissingCaption != null) { pivotTable.View.MissingCaption = MissingCaption; } if (RowHeaderCaption != null) { pivotTable.View.RowHeaderCaption = RowHeaderCaption; } if (HideDrillIndicators) { pivotTable.View.ShowDrillIndicators = false; } if (ShowError) { pivotTable.View.ShowError = true; } if (HideFieldHeaders) { pivotTable.View.ShowFieldHeaders = false; } if (HideMissing) { pivotTable.View.ShowMissing = false; } if (HideMultipleLabels) { pivotTable.View.ShowMultipleLabels = false; } if (HideValuesRow) { pivotTable.View.ShowValuesRow = false; } if (AllowMultipleFieldFilters) { pivotTable.Behavior.AllowMultipleFieldFilters = true; } if (DontAutoFitColumns) { pivotTable.Behavior.AutoFitColumns = false; } } if (!string.IsNullOrWhiteSpace(Formatting)) { var scanner = new Scanner(); var parser = new Parser(scanner); var tree = parser.Parse(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 formatRange = pivotTable.Location.DataRange; var gridFormatConditions = GridData.LoadGridFormatConditions(commands); foreach (var gridFormatCondition in gridFormatConditions) { SpreadsheetUtils.ApplyGridFormatCondition(pivotSheet, gridFormatCondition, formatRange); } } } } CopyRangeToBook(CopyToBookDataOnly ? pivotTable.Location.DataRange : pivotTable.Location.Range); AddComments(pivotTable.Location.WholeRange); DevExpress.Spreadsheet.Table FindDataTable() { Worksheet sheet = null; DevExpress.Spreadsheet.Table table = null; if (!string.IsNullOrWhiteSpace(DataSheetName)) { sheet = spreadsheet.Worksheets[DataSheetName]; if (sheet == null) { throw new Exception($"Cannot find sheet '{DataSheetName}'."); } } if (!string.IsNullOrWhiteSpace(DataTableName)) { if (sheet != null) { table = sheet.Tables.Where(t => string.Compare(t.Name, DataTableName, true) == 0).FirstOrDefault(); } else { foreach (var worksheet in spreadsheet.Worksheets) { table = worksheet.Tables.Where(t => string.Compare(t.Name, DataTableName, true) == 0).FirstOrDefault(); if (table != null) { break; } } } } if (table == null) { throw new Exception($"Cannot find table '{DataTableName}'."); } return(table); }
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();
protected virtual string GenerateTableHtml(SpreadTableOptions options) { string htmlTable; IWorkbook workbook = null; bool disposeWorkbook = false; try { if (options._Spreadsheet != null) { workbook = options._Spreadsheet.Workbook; } else if (!string.IsNullOrWhiteSpace(options._FileName)) { disposeWorkbook = true; string fileName = Project.Current.MapPath(options._FileName); if (!File.Exists(fileName)) { throw new Exception($"File '{fileName}' does not exist."); } workbook = SpreadsheetUtils.CreateWorkbook(); workbook.LoadDocument(fileName); } else { workbook = Host?.Spreadsheet?.Workbook; } if (workbook == null) { throw new Exception("Spreadsheet is not specified"); } var range = SpreadsheetUtils.GetWorkbookRange(workbook, options._TableName); var optionsHtml = new DevExpress.XtraSpreadsheet.Export.HtmlDocumentExporterOptions() { SheetIndex = workbook.Sheets.IndexOf(range.Worksheet), Range = range.GetReferenceA1(), Encoding = Encoding.Unicode }; using var stream = new MemoryStream(); workbook.ExportToHtml(stream, optionsHtml); stream.Seek(0, SeekOrigin.Begin); using var reader = new StreamReader(stream, Encoding.UTF8); htmlTable = reader.ReadToEnd(); } finally { if (disposeWorkbook) { workbook?.Dispose(); } } return(htmlTable); }
public void LoadFromStream(Stream stream) { Spreadsheet.LoadDocument(stream); SpreadsheetUtils.InitializeWorkbook(Spreadsheet.Document); }
public SCSpreadsheetContext() { Workbook = SpreadsheetUtils.CreateWorkbook(); }
protected IRichEditDocumentServer AddSpreadTable(ArgumentCollection arguments) { if (arguments.Count <= 0) { throw new Exception("'DOCVARIABLE SPREADTABLE' requires filename as first argument."); } if (arguments.Count <= 1) { throw new Exception("'DOCVARIABLE SPREADTABLE' requires table or range as second argument."); } var fileName = arguments[0].Value; if (string.IsNullOrWhiteSpace(fileName)) { throw new Exception("DOCVARIABLE SPREADTABLE does not contain valid filename."); } object snippet = null; if (Snippets?.ContainsKey(fileName) ?? false) { snippet = Snippets[fileName]; if (snippet is SCSpreadsheetContext) { } else { throw new Exception($"Specified snippet '{fileName}' is not supported. Snippet shall be Spreadsheet."); } } else if (string.Compare(fileName, "$SPREAD", true) == 0) { //Do nothing } else { fileName = Project.Current.MapPath(fileName); } if (snippet == null && string.Compare(fileName, "$SPREAD", true) != 0 && !File.Exists(fileName)) { throw new Exception($"File '{fileName}' does not exist."); } var tableName = arguments[1].Value; bool rebuild = false; if (arguments.Count > 2) { var properties = Utils.SplitNameValueString(arguments[2].Value, ';'); foreach (var prop in properties) { if (string.IsNullOrWhiteSpace(prop.Key)) { continue; } switch (prop.Key.ToLower()) { case "rebuild": case "recalculate": case "recalc": var valueRebuild = prop.Value; if (string.IsNullOrWhiteSpace(valueRebuild)) { valueRebuild = bool.TrueString; } rebuild = bool.Parse(valueRebuild); break; } } } bool needDispose = true; IWorkbook workbook = null; try { //using var workbook = SpreadsheetUtils.CreateWorkbook(); if (snippet is SCSpreadsheetContext spreadsheetContext) { workbook = SpreadsheetUtils.CreateWorkbook(); workbook.Append(spreadsheetContext.Workbook); } else if (string.Compare(fileName, "$SPREAD", true) == 0) { needDispose = false; workbook = this.DefaultSpreadsheet; if (workbook == null) { throw new Exception("Current script does not support default (Host) spreadsheet."); } } else { workbook = SpreadsheetUtils.CreateWorkbook(); workbook.LoadDocument(fileName); } IRichEditDocumentServer result = null; if (workbook == this.DefaultSpreadsheet && NeedSynchronizeDefaultSpreadsheet) { SCDispatcherService.UIDispatcherServer.Invoke(() => ProcessWorkbook(workbook, tableName, rebuild, out result)); } else { ProcessWorkbook(workbook, tableName, rebuild, out result); } return(result); } finally { if (needDispose && workbook != null) { workbook.Dispose(); } }
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();
public SCSpreadsheet() : base() { Workbook = SpreadsheetUtils.CreateWorkbook(); }