I use Spreadsheetgear to export the results of custom SQL queries as excel files.
Now I want to improve this system: The user will be able to upload an excel template file into the database (currently as varbinary). For example, it could have one worksheet with calculations, then when exporting data into that template it'll fill a different worksheet with the datatable from the query.
Can spreadsheetgear do this? If so, how does it work - mainly how can I load an existing excel file as a Spreadsheetgear workbook/workbookset? I could not find anything in their documentation (though I am still looking).
Edit: Solved. I create the workbook manually, load the template from the database as a byte[], then open said template with the OpenFromMemory function:
// Create workbookSet
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
// Create a new empty workbook in the workbookSet.
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Add();
if(TemplateID != -1) // If this case requires a template
{
// Get template from SQL database (.xlsx stored as varbinary(max))
byte[] template = GetTemplateByID(VorlagenID);
workbook = workbookSet.Workbooks.OpenFromMemory(template);
}
// Create export worksheet
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Export";
[...]
Templates always use the Worksheet[1] in my case, but it should be easy to create a Worksheet[1] for the export.