public SpreadsheetService(ISpreadsheetExporter exporter, ISpreadsheetImporter importer, ISpreadsheetValidator validator, ISpreadsheetTemplate template)
{
_exporter = exporter;
_importer = importer;
_validator = validator;
_template = template;
}
public void ExportSpreadsheet(ExportData data, ISpreadsheetTemplate template, Stream outputStream)
{
var package = template.GetTemplate();
package.Version = ExcelVersion.Version2013;
var sheet = package.Worksheets[template.DataSheetName];
sheet[template.GuidCell].Value = data.SheetGuid.ToString();
int headerRow = template.HeaderRow;
foreach (var entry in template.ExportColumnMap)
{
if (!data.Table.Columns.Contains(entry.Key))
{
continue;
}
var view = new DataView(data.Table);
var table = view.ToTable(false, entry.Key); // create a one-column table that will be inserted
sheet.InsertDataTable(table, false, template.FirstDataRow, entry.Value);
}
// last step, give the user the opportunity to change the result.
_postProcessingStep?.Invoke(package); // .Invoke to take advantage of the Elvis operator
package.SaveToStream(outputStream);
}
public void Init()
{
_workbook = new Workbook();
_workbook.Worksheets["Sheet1"]["A1"].Value = "column1";
_workbook.Worksheets["Sheet1"]["A2"].Value = "column2";
_workbook.Worksheets["Sheet1"]["A3"].Value = "column3";
_template = new StubSpreadsheetTemplate(_workbook, null, new Dictionary <int, string>
{
[1] = "column1",
[2] = "column2",
[3] = "column3"
});
_connectionProvider = new DefaultSqlConnectionProvider("main");
_importer = new DefaultSpreadsheetImporter(_connectionProvider, "dummy");
_table = _importer.StripExcelData(_workbook, _template);
}
public DataTable StripExcelData(Workbook workbook, ISpreadsheetTemplate template)
{
DataTable ret = new DataTable();
var sheet = workbook.Worksheets[template.DataSheetName];
int lastRow = template.FindLastRowOfData(sheet);
template.ImportColumnMap.Values.ToList().ForEach(col => ret.Columns.Add(col));
for (int i = template.FirstDataRow; i <= lastRow; i++)
{
var row = ret.NewRow();
foreach (var entry in template.ImportColumnMap)
{
row[entry.Value] = GetCellValue(sheet, i, entry.Key);
}
ret.Rows.Add(row);
}
return(ret);
}
public ImportData(DataTable table, Guid?guid, ISpreadsheetTemplate template)
{
Table = table;
Guid = guid;
Template = template;
}
