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