xlsgen > overview > Templates

The ability to use an existing Excel workbook as a template is a very powerful feature of xlsgen. Not only existing content and formatting in cells are preserved, existing charts, pivot tables, filters, and even macros are also preserved.

For a demo, see here.

Since all rich objects are preserved, it lets one prepare an Excel workbook by hand with Excel, and then finish or just make small changes by code using xlsgen. This way of doing things greatly enhances the developer experience and saves time by an order of magnitude.

How does this work? Just use the appropriate Open method and pass it the name of the existing Excel workbook. Then use another set of appropriate properties to get the existing worksheet and the existing styles. Then, just do as usual, use styles to create content, formulas and so on...

The following code shows a sample of how to reuse an existing workbook made of 3 worksheets :

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\tmp\Book1_condform.xls", "myfirstfile.xls")

' show how many worksheets we have
MsgBox "There are " & wbk.WorksheetCount & " worksheets"

' add a label in sheet 2
Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(2)

wksht.Label(8, 1) = "Another Label in sheet2"

' reuse a style
Dim style As IXlsStyle
Set style = wksht.StyleFromLocation(4, 2)
style.Apply

wksht.Label(3, 2) = "Another label"

wbk.Close

Set engine = Nothing

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"C:\tmp\Book1_condform.xls", @"myfirstfile.xls" );

// show how many worksheets we have
int nbworksheets = wbk.get_WorksheetCount;
MessageBox.Show(nbworksheets.ToString());

// add a label in sheet 2
IXlsWorksheet wksht_existing = wbk.get_WorksheetByIndex(2);
wksht_existing.set_Label(8, 1, "Another Label in sheet2");

// reuse a style
IXlsStyle style = wksht.get_StyleFromLocation(4,2);
style.Apply();

wksht.set_Label(3, 2, "Another label");

wbk.Close();
excel = null;

{
xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk;
wbk = engine->Open(L"C:\\tmp\\Book1_condform.xls", L"myfile.xls" );

// show how many worksheets we have
char s[32];
int nbworksheets = wbk->WorksheetCount;
sprintf(s,"There are %d worksheets",nbworksheets);
::MessageBox(NULL,s,"info",MB_OK);

// add a label in sheet 2
xlsgen::IXlsWorksheetPtr wksht_existing;
wksht_existing = wbk->WorksheetByIndex[2];
wksht_existing->Label[8][1] = L"Another Label in sheet2";

// reuse a style
xlsgen::IXlsStylePtr style = wksht->StyleFromLocation[4][2];
style->Apply();

wksht->Label[3][2] = "Another label";

wbk->Close();
}

Using templates gets you more productive. For instance, consider the following scenario : following the corporate practice guidelines you prepare one or more Excel workbooks with the corporate logo, page margins, table formatting, all ready for printing. Those workbooks are to be filled automatically by some process with content coming from a database based on a user or group profile, and then each workbook must be sent to a particular user or group. Let's assume we come up with this template :

For each user, we'd like to use xlsgen in order to produce the following :

But if you write the couple lines of code that fill the Sales table with figures :

' fill the Excel workbook with Sales figures
worksheet.Number(11,3) = 45
worksheet.Number(12,3) = 40
worksheet.Number(13,3) = 40

you'll notice you actually get this instead :

Oops, something is wrong here. The formatting is broken, the background of cells is white, borders are lost, and the figures are not displayed in italic anymore.

xlsgen actually writes formatting styles at the same time it writes content in Excel workbooks, and it sets whatever user-defined style declared in the code. When you declare no style in the code, it uses the default style, which is exactly what we see above : black characters on white background, no italic, no borders.

Fortunately, xlsgen has a style option available in the workbook object which lets one define how styles should be applied. One of those options lets you tell that the cells being filled with content should preserve the formatting in the templates. So if you add this to the code prior to adding the figures :

' tell xlsgen that the formatting styles from the template should be preserved
workbook.StyleOption = styleoption_preserve

' fill the Excel workbook with Sales figures
worksheet.Number(11,3) = 45
worksheet.Number(12,3) = 40
worksheet.Number(13,3) = 40

Executing the code now leads to the expected Excel workbook.

With that said, you can take advantage of the flexible xlsgen object model by doing the opposite of using templates : you could start with a new Excel workbook, fill it with the figures without taking care of the formatting at all, and then use range styling in order to apply a formatting across an entire range of cells. For more information, see ranges.