public static Worksheet Decode(Workbook book, Stream stream, SharedResource sharedResource)
{
Worksheet sheet = new Worksheet();
sheet.Book = book;
List<Record> records = ReadRecords(stream, out sheet.Drawing);
sheet.Cells = PopulateCells(records, sharedResource);
sheet.Book.Records.AddRange(records);
return sheet;
}
private static CellCollection PopulateCells(List<Record> records, SharedResource sharedResource)
{
CellCollection cells = new CellCollection();
cells.SharedResource = sharedResource;
foreach (Record record in records)
{
record.Decode();
switch (record.Type)
{
//case RecordType.DIMENSIONS:
// DIMENSIONS dimensions = record as DIMENSIONS;
// cells.FirstRowIndex = dimensions.FirstRow;
// cells.FirstColIndex = dimensions.FirstColumn;
// cells.LastRowIndex = dimensions.LastRow-1;
// cells.LastColIndex = dimensions.LastColumn-1;
// break;
case RecordType.BOOLERR:
BOOLERR boolerr = record as BOOLERR;
cells.CreateCell(boolerr.RowIndex, boolerr.ColIndex, boolerr.GetValue(), boolerr.XFIndex);
break;
case RecordType.LABEL:
LABEL label = record as LABEL;
cells.CreateCell(label.RowIndex, label.ColIndex, label.Value, label.XFIndex);
break;
case RecordType.LABELSST:
LABELSST labelsst = record as LABELSST;
Cell cell = cells.CreateCell(labelsst.RowIndex, labelsst.ColIndex, sharedResource.GetStringFromSST(labelsst.SSTIndex), labelsst.XFIndex);
cell.Style.RichTextFormat = sharedResource.SharedStringTable.RichTextFormatting[labelsst.SSTIndex];
break;
case RecordType.NUMBER:
NUMBER number = record as NUMBER;
cells.CreateCell(number.RowIndex, number.ColIndex, number.Value, number.XFIndex);
break;
case RecordType.RK:
RK rk = record as RK;
cells.CreateCell(rk.RowIndex, rk.ColIndex, Record.DecodeRK(rk.Value), rk.XFIndex);
break;
case RecordType.MULRK:
MULRK mulrk = record as MULRK;
int row = mulrk.RowIndex;
for (int col = mulrk.FirstColIndex; col <= mulrk.LastColIndex; col++)
{
int index = col - mulrk.FirstColIndex;
object value = Record.DecodeRK(mulrk.RKList[index]);
int XFindex = mulrk.XFList[index];
cells.CreateCell(row, col, value, XFindex);
}
break;
case RecordType.FORMULA:
FORMULA formula = record as FORMULA;
cells.CreateCell(formula.RowIndex, formula.ColIndex, formula.DecodeResult(), formula.XFIndex);
break;
}
}
return cells;
}
private static List<BOUNDSHEET> DecodeRecords(List<Record> records, out SharedResource sharedResource)
{
sharedResource = new SharedResource();
List<BOUNDSHEET> boundSheets = new List<BOUNDSHEET>();
foreach (Record record in records)
{
record.Decode();
switch (record.Type)
{
case RecordType.BOUNDSHEET:
boundSheets.Add(record as BOUNDSHEET);
break;
case RecordType.XF:
sharedResource.ExtendedFormats.Add(record as XF);
break;
case RecordType.FORMAT:
sharedResource.CellFormats.Add(record as FORMAT);
break;
case RecordType.SST:
sharedResource.SharedStringTable = record as SST;
break;
case RecordType.DATEMODE:
DATEMODE dateMode = record as DATEMODE;
switch (dateMode.Mode)
{
case 0:
sharedResource.BaseDate = DateTime.Parse("1899-12-31");
break;
case 1:
sharedResource.BaseDate = DateTime.Parse("1904-01-01");
break;
}
break;
case RecordType.PALETTE:
PALETTE palette = record as PALETTE;
int colorIndex = 8;
foreach (int color in palette.Colors)
{
sharedResource.ColorPalette[colorIndex] = Color.FromArgb(color);
colorIndex++;
}
break;
case RecordType.FONT:
FONT f = record as FONT;
sharedResource.Fonts.Add(f);
break;
}
}
return boundSheets;
}
/*
* Page 171 of the OpenOffice documentation of the Excel File Format
*
* The font with index 4 is omitted in all BIFF versions. This means the first four fonts have zero-based indexes,
* and the fifth font and all following fonts are referenced with one-based indexes.
*/
//public FONT getFontRecord(int index)
private static FONT getFontRecord(SharedResource sharedResource, UInt16 index)
{
if (index >= 0 && index <= 3)
{
return sharedResource.Fonts[index];
}
else if (index >= 5)
{
return sharedResource.Fonts[index - 1];
}
else // index == 4 -> error
{
return null;
}
}
private static List<Record> EncodeWorkbook(Workbook workbook)
{
SharedResource sharedResource = new SharedResource(true);
List<Record> book_records = new List<Record>();
BOF bof = new BOF();
bof.BIFFversion = 0x0600; //0600H = BIFF8
bof.StreamType = StreamType.WorkbookGlobals;
bof.BuildID = 3515;
bof.BuildYear = 1996;
bof.RequiredExcelVersion = 6;
book_records.Add(bof);
CODEPAGE codepage = new CODEPAGE();
codepage.CodePageIdentifier = (ushort)Encoding.Unicode.CodePage;
book_records.Add(codepage);
WINDOW1 window = new WINDOW1();
window.WindowWidth = 16384;
window.WindowHeight = 8192;
window.SelecteWorksheets = 1;
window.TabBarWidth = 600;
window.OptionFlags = 56;
book_records.Add(window);
DATEMODE dateMode = new DATEMODE();
dateMode.Mode = 1;
sharedResource.BaseDate = DateTime.Parse("1904-01-01");
book_records.Add(dateMode);
List<List<Record>> all_sheet_records = new List<List<Record>>();
foreach (Worksheet worksheet in workbook.Worksheets)
{
List<Record> sheet_records = WorkSheetEncoder.Encode(worksheet, sharedResource);
Record.EncodeRecords(sheet_records);
all_sheet_records.Add(sheet_records);
}
book_records.AddRange(sharedResource.FormatRecords.ToArray());
book_records.AddRange(sharedResource.ExtendedFormats.ToArray());
List<BOUNDSHEET> boundSheets = new List<BOUNDSHEET>();
foreach (Worksheet worksheet in workbook.Worksheets)
{
BOUNDSHEET boundSheet = new BOUNDSHEET();
boundSheet.Visibility = 0; // 00H = Visible
boundSheet.SheetType = (byte)SheetType.Worksheet;
boundSheet.SheetName = worksheet.Name;
boundSheet.StreamPosition = 0;
boundSheets.Add(boundSheet);
book_records.Add(boundSheet);
}
if (sharedResource.Images.Count > 0)
{
book_records.Add(EncodeImages(sharedResource.Images));
}
Record.EncodeRecords(book_records);
int sstOffset = Record.CountDataLength(book_records);
book_records.Add(sharedResource.SharedStringTable);
book_records.Add(CreateEXTSST(sharedResource.SharedStringTable, sstOffset));
EOF eof = new EOF();
book_records.Add(eof);
Record.EncodeRecords(book_records);
int dataLength = Record.CountDataLength(book_records);
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
boundSheets[i].StreamPosition = (uint)dataLength;
boundSheets[i].Encode();
int sheet_length = Record.CountDataLength(all_sheet_records[i]);
dataLength += sheet_length;
}
List<Record> all_records = new List<Record>();
all_records.AddRange(book_records);
foreach (List<Record> sheet_records in all_sheet_records)
{
all_records.AddRange(sheet_records);
}
return all_records;
}
