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