xlsgen > overview > Excel 2007/2010 file format generation

xlsgen supports native Excel 2007/2010/2013/2016 file format generation. The following file format extensions are supported :

• Excel 2007 spreadsheet (.xlsx)
• Excel 2007 macro-enabled spreadsheet (.xlsm)
• Excel 2007 template (.xltx)
• Excel 2007 macro-enabled template (.xltm)
• Excel 2007 binary spreadsheet (.xlsb)
• Excel 2010 spreadsheet (.xlsx)
• Excel 2010 macro-enabled spreadsheet (.xlsm)
• Excel 2010 template (.xltx)
• Excel 2010 macro-enabled template (.xltm)
• Excel 2010 binary spreadsheet (.xlsb)
• Excel 2013 spreadsheet (.xlsx)
• Excel 2013 macro-enabled spreadsheet (.xlsm)
• Excel 2013 template (.xltx)
• Excel 2013 macro-enabled template (.xltm)
• Excel 2013 binary spreadsheet (.xlsb)
• Excel 2016 spreadsheet (.xlsx)
• Excel 2016 macro-enabled spreadsheet (.xlsm)
• Excel 2016 template (.xltx)
• Excel 2016 macro-enabled template (.xltm)
• Excel 2016 binary spreadsheet (.xlsb)

Those files are made of a number of XML and binary parts in a regular zip file. If you rename the file, you can look inside it. Microsoft provides partial document specifications for those file formats.

From a feature perspective, the new file formats support existing Excel capabilities, and introduce new capabilities. One of the most welcome, and greatest incentive to use the new file format, is that the spreadsheet grid is extended to 1 million rows and 16,384 columns, compared to 65,536 rows and 256 columns (or 16 times more rows, and 64 times more columns). In addition, limitations with colors are alleviated, meaning that the individual elements can use the entire 24-bit RGB space. That's two limitations among a number of others documented here.

xlsgen continues to work as usual, and adds new scenarios for Excel 2007 file formats :

• generation : you can create Excel 2007 files from scratch using the xlsgen programming model, using objects not XML markup

  // here follows a piece of code in C/C++
  // we generate an Excel 2007 file from scratch
  
  xlsgen::IXlsWorkbookPtr wbk;
  
  wbk = engine->New( L"unittest_251_styles.xlsx" );
  
  xlsgen::IXlsWorksheetPtr wksht1 = wbk->AddWorksheet( L"Sheet1" );
  
  xlsgen::IXlsStylePtr s0 = wksht1->NewStyle();
  
  xlsgen::IXlsStylePtr s1 = wksht1->NewStyle();
  s1->Pattern->BackgroundColor = 0x00FF0000;
  s1->Pattern->Pattern = xlsgen::pattern_solid;
  s1->Apply();
  wksht1->Number[2][4] = 2;
  wksht1->Number[3][4] = -3;
  
  s0->Apply();
  wksht1->Float[4][4] = 3.1415926536;
  wksht1->Number[5][4] = 2;
  wksht1->Number[6][4] = 3;
  wksht1->Float[7][4] = 3.1415;
  
  wksht1->Label[3][2] = L"SUM";
  wksht1->Formula[4][2] = L"=SUM(D2:D4;D5:D7)";
  
  wksht1->Select(2,4,7,4);
  
  wbk->Close();

• migration : you can migrate an Excel 97/2000/XP/2003 file (or Excel 97-2003 in short) to the Excel 2007 file format

  // here follows a piece of code in C/C++
  // we generate an Excel 2007 file from an existing Excel 97-2003 file
  
  xlsgen::IXlsWorkbookPtr wbk;
  
  wbk = engine->Open( L"unittest_251_styles.xls", L"unittest_251_styles.xlsx" );
  
  wbk->Close();

• template/update : you can make changes to existing Excel 97/2000/XP/2003 files and save the resulting file as an Excel 2007 file.

  // here follows a piece of code in C/C++
  // we open an existing Excel 97-2003, make changes and save as 
  // an Excel 2007 file 
  
  xlsgen::IXlsWorkbookPtr wbk;
  
  wbk = engine->Open( L"unittest_251_styles.xls", L"unittest_251_styles.xlsx" );
  
  xlsgen::IXlsWorksheetPtr wksht1 = wbk->WorksheetByIndex[1];
  
  wksht1->Label[3][2] = L"SUM";
  wksht1->Formula[4][2] = L"=SUM(D2:D4;D5:D7)";
  
  wbk->Close();

• read/update : you can make changes to existing Excel 2007 files and save the resulting file as an Excel 2007 file.

  // here follows a piece of code in C/C++
  // we open an existing Excel 2007, make changes and save as 
  // an Excel 2007 file 
  
  xlsgen::IXlsWorkbookPtr wbk;
  
  wbk = engine->Open( L"unittest_251_styles.xlsx", L"unittest_251_styles.xlsx" );
  
  xlsgen::IXlsWorksheetPtr wksht1 = wbk->WorksheetByIndex[1];
  
  wksht1->Label[3][2] = L"SUM";
  wksht1->Formula[4][2] = L"=SUM(D2:D4;D5:D7)";
  
  wbk->Close();

Notice the x above in the target filenames. This is the only difference with regular xlsgen source code. This is great news since you can leverage your existing source code, and can easily switch to the Excel file format version that meets your needs. In addition, since existing code can be used to create Excel 2007 files, it means you can use the source code generation tool in order to produce the source code from an arbitrary Excel 97-2003 file, using the programming language of your choice. That certainly makes it easy to work with xlsgen without learning the object model, a huge time saver.

What are Excel features preserved and converted over ?

• cells, formattings, formulas, merged cells
• column and row settings (width, hidden, style, outline)
• sheet properties, protection, defined names, external references
• page setup, header/footer, frozen/split panes
• conditional formattings, pictures, charts
• document summary, data validation rules
• VBA macros (make sure to use the m file extension name instead of x)

In addition to the above, you can work with :

• hyperlinks
• conditional formattings
• data validation rules
• pictures
• auto-filters, custom filters
• comments, text boxes
• charts

The install has samples to get you started in a number of programming languages.

Here is, to get an idea, a illustration of the spreadsheet grid size changes :