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;
}
}
}
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());
}
}
}
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);
public SCSpreadsheetContext()
{
Workbook = SpreadsheetUtils.CreateWorkbook();
}
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 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 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 SCSpreadsheet() : base()
{
Workbook = SpreadsheetUtils.CreateWorkbook();
}