Click here to Skip to main content
11,584,208 members (44,939 online)
Click here to Skip to main content

Saving Excel 2.1 Workbook

, 12 Sep 2002 291.2K 6.3K 112
Rate this:
Please Sign up or sign in to vote.
Classes used to save data in Excel 2.1 Workbook format

Sample Image

Introduction

Recently I worked on a project that required exporting some reports containing various file statistics. The goal was having one format that would look good immediately and one easy accessible format for the user to import and build custom reports. For the first export type, plain HTML was used. But for importing, HTML is not really easy to use, so Excel file creation seemed a good choice.

Requirements

Since the Excel exported file is just for the user to have easy access to data, only a small part of the Excel file format features were needed. Also, there was no guarantee that Excel would even be installed on the computer where the reports are generated and since a port to Linux was imminent, it would be nice for the class to be platform independent. The goal was to be able to write something as this:

CMiniExcel miniexcel;

miniexcel(0,0) = "FileName";
miniexcel(0,1) = "Size (MB)";

miniexcel(1,0) = "c:\\bigfile.txt";
miniexcel(1,1) = 123.45;

miniexcel.SaveAs("Report.xls")

There are no real memory constraints, since Excel cannot handle over ~65K rows anyway. (Actually we also needed a single big report with more that 65K rows, and we had to save it as a "Comma separated values" file). So the whole excel worksheet could be kept in memory.

Excel file format

After a little bit of searching on the net, I decided on the Excel 2.1 Workbook format (description of the format found here). Even though this format is old, it can be imported by Excel or by other programs and it seemed suitable for our simple needs.

From the documentation of the Excel 2.1 format it turns out that an excel document is stored using a format called Binary File Format (BIFF). This format is a sequence of BIFF records, each record containing a certain property/value of the workgroup. Each BIFF record starts with a 2 byte number containing it's type and another 2 bytes for the size of the record. After this header, record-specific data will follow:

XX XX

 XX XX

 ....

Type  Length  Specific Data

From the several record types needed, only a few were really important for simple excel exporting. BOF and EOF records were unavoidable and also text strings (LABEL record) and numbers (NUMBER record) needed to be saved.

Implementation

First of all, to take care of the platform independence and also to make life easier when saving the BIFF records, I implemented a writer class (CLittleEndianWriter) that should be able to write 1 byte, 2 byte and 4 byte values in little endian byte order. Also, it needed to be able to save double values in IEEE format, since this is the format used to store real numbers (excel has a BIFF record fr storing a 16 bit unsigned integer, but it is not enough):

class LittleEndianWriter{
  ...
public:
  ...
  void Write1 (char v);
  void Write2 (int v);
  void Write4 (long v);
  void WriteFloatIEEE (float v);
  void WriteDoubleIEEE (double v); 
};

After this, it also seems pretty clear that since every single record is saved in a biff file format, it would be nice have a abstract BIFFRecord class and that every single record should be derived from it.

class BIFFRecord{
  ...

  /* Write a BIFF header for the opcode nRecno of length nRecLen */
  void Write (LittleEndianWriter *pWriter, int nRecNo, int nRecLen);

public:

  /* We should be able to write every type of BIFF records */
  virtual void Write (LittleEndianWriter *pWriter) = 0; 
}; 

This being done, implementing excel file saving is only adding the required BIFF records. I added in this demo project BOF, EOF, NUMBER and LABEL (others should be easy to add). We also need a generic container class, so that Excel file creation is easy. The container class is needed because BIFF records have to be in a specific order in the XLS file and it's nice if the container class does this for us:

class CMiniExcel{
  ...
public:
  ... 
  /* Access the columns in the excel document */
  ExcelCell &operator() (unsigned row, unsigned column);

  /* Write into a file. */
  void Write (FILE *dest);
};

Note that the operator () returns an ExcelCell. This is just a simple container for either numbers or strings and it makes like a lot easier when coding by not having to worry about cell types in the Excel Workbook.

Final notes

This is by no means a complete project. The saving is basic and error checking is almost none existing. Adding other BIFF records to the generic format should be pretty easy and if somebody finds it useful, I might add a few more into this demo source code.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Andrei Litvin
Web Developer
Canada Canada
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralChange colour text and background Pin
dariusz197913-Jun-05 0:54
memberdariusz197913-Jun-05 0:54 
GeneralAutofilter Pin
AccessViolation2-Jun-05 5:30
memberAccessViolation2-Jun-05 5:30 
GeneralRe: Autofilter Pin
vikas chablani18-Sep-07 2:27
membervikas chablani18-Sep-07 2:27 
GeneralCreating Excel-File with Autofilter Pin
AccessViolation2-Jun-05 5:22
memberAccessViolation2-Jun-05 5:22 
GeneralRe: Creating Excel-File with Autofilter Pin
wdaliu24-Apr-07 3:02
memberwdaliu24-Apr-07 3:02 
Generaladd a few lines of summary before cells Pin
bangwo12-May-05 14:04
sussbangwo12-May-05 14:04 
Generalappend to an .xls file Pin
chenggong12-May-05 5:45
memberchenggong12-May-05 5:45 
GeneralRe: append to an .xls file Pin
Andrei Litvin12-May-05 15:11
sussAndrei Litvin12-May-05 15:11 
GeneralWell done Andrei! Pin
PerpetuaFX12-Apr-05 9:53
sussPerpetuaFX12-Apr-05 9:53 
GeneralMerging Cells Pin
UOzkan31-Mar-05 0:18
memberUOzkan31-Mar-05 0:18 
Hi there,

First of all thanks for those classes, helped me a lot.
What I need is code for merging two or more cells together.
Is there a way to do that? and How?

Thank you very much in advance.

Ugur
GeneralMerging Cells Pin
UOzkan31-Mar-05 0:16
memberUOzkan31-Mar-05 0:16 
Generala couple of doubts!!! Pin
Piyush Peshwani15-Dec-04 18:53
memberPiyush Peshwani15-Dec-04 18:53 
GeneralBUG found with SetFontNum (bit shift incorrect) Pin
Anonymous12-Oct-04 3:52
sussAnonymous12-Oct-04 3:52 
GeneralRe: BUG found with SetFontNum (bit shift incorrect) Pin
chenggong16-May-05 9:20
memberchenggong16-May-05 9:20 
GeneralRe: BUG found with SetFontNum (bit shift incorrect) Pin
hughesr14-Jun-05 3:00
memberhughesr14-Jun-05 3:00 
GeneralRe: BUG found with SetFontNum (bit shift incorrect) Pin
Anonymous18-Jun-05 19:01
sussAnonymous18-Jun-05 19:01 
GeneralRe: BUG found with SetFontNum (bit shift incorrect) Pin
lshe7-Dec-05 10:49
memberlshe7-Dec-05 10:49 
GeneralHi..Do u know How to change Width of Col. Pin
Sumit Kapoor27-Aug-04 0:54
memberSumit Kapoor27-Aug-04 0:54 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
Anonymous12-Oct-04 3:41
sussAnonymous12-Oct-04 3:41 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
hughesr12-Oct-04 3:56
memberhughesr12-Oct-04 3:56 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
Murka1-Feb-05 1:14
memberMurka1-Feb-05 1:14 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
hughesr1-Feb-05 2:00
memberhughesr1-Feb-05 2:00 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
Murka1-Feb-05 4:02
memberMurka1-Feb-05 4:02 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
FayezElFar4-Mar-05 9:38
sussFayezElFar4-Mar-05 9:38 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
hughesr4-Mar-05 16:22
memberhughesr4-Mar-05 16:22 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
hw7704118-Jun-05 19:16
memberhw7704118-Jun-05 19:16 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
ahm12332121-Jul-05 4:44
memberahm12332121-Jul-05 4:44 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
yshingo8-Mar-06 7:40
memberyshingo8-Mar-06 7:40 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
dadatong23-Jul-07 21:02
memberdadatong23-Jul-07 21:02 
GeneralRe: Hi..Do u know How to change Width of Col. Pin
lshe7-Dec-05 10:45
memberlshe7-Dec-05 10:45 
GeneralPlease help! Pin
Puiu M.17-Aug-04 13:38
memberPuiu M.17-Aug-04 13:38 
GeneralChart Pin
Rahuraman22-Jun-04 21:50
memberRahuraman22-Jun-04 21:50 
GeneralChenage Font to BOLD Pin
Wsufka21-Jun-04 20:14
memberWsufka21-Jun-04 20:14 
GeneralRe: Chenage Font to BOLD Pin
Piyush Peshwani15-Dec-04 18:40
memberPiyush Peshwani15-Dec-04 18:40 
Generalnewbie problem here pls help Pin
andi_mihai15-May-04 4:14
memberandi_mihai15-May-04 4:14 
Generalappend data into excel sheet from vc++ Pin
shiva shankar20-Apr-04 18:44
membershiva shankar20-Apr-04 18:44 
GeneralUpgrading class to v2000 Pin
oneleg20-Apr-04 4:51
memberoneleg20-Apr-04 4:51 
Generalerror C1010 Pin
ASG_Jim5-Apr-04 10:12
memberASG_Jim5-Apr-04 10:12 
GeneralRe: error C1010 Pin
tobberik6-Apr-04 2:27
membertobberik6-Apr-04 2:27 
GeneralRe: error C1010 Pin
mirr200111-May-04 9:06
membermirr200111-May-04 9:06 
GeneralRe: error C1010 Pin
Doug Joseph20-May-05 5:13
memberDoug Joseph20-May-05 5:13 
Question.net version? Pin
chenhuisheng1-Apr-04 1:26
memberchenhuisheng1-Apr-04 1:26 
AnswerRe: .net version? Pin
Colin Angus Mackay1-Apr-04 1:38
memberColin Angus Mackay1-Apr-04 1:38 
GeneralSome errors and info Pin
knn4-Mar-04 4:47
memberknn4-Mar-04 4:47 
Questionchange font style and colors in spreadsheet? Pin
xxhimanshu23-Feb-04 19:14
sussxxhimanshu23-Feb-04 19:14 
Generalreading excel records-> problem Pin
ksnehal20-Feb-04 9:39
memberksnehal20-Feb-04 9:39 
GeneralExample on adding a formular Pin
cpede22-Sep-03 4:51
membercpede22-Sep-03 4:51 
GeneralRe: Example on adding a formular Pin
carola.malavenda@katamail.com26-Feb-05 6:16
membercarola.malavenda@katamail.com26-Feb-05 6:16 
GeneralRussian language Pin
Wax2-Sep-03 6:38
memberWax2-Sep-03 6:38 
GeneralRe: Russian language Pin
bfadi17-Dec-03 8:09
memberbfadi17-Dec-03 8:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 13 Sep 2002
Article Copyright 2002 by Andrei Litvin
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid