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 = Constants.WindowsBaseDate;
break;
case 1:
sharedResource.BaseDate = Constants.MacintoshBaseDate;
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);
}
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.LABELSST:
LABELSST label = record as LABELSST;
Cell cell = cells.CreateCell(label.RowIndex, label.ColIndex, sharedResource.GetStringFromSST(label.SSTIndex), label.XFIndex);
cell.Style.RichTextFormat = sharedResource.SharedStringTable.RichTextFormatting[label.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 CellValue EncodeCell(Cell cell, SharedResource sharedResource)
{
object value = cell.Value;
if (value is int)
{
RK rk = new RK();
rk.Value = (uint)((int)value << 2 | 2);
return(rk);
}
else if (value is decimal)
{
RK rk = new RK();
rk.Value = (uint)((decimal)value * 100) << 2 | 3; // integer and mul
return(rk);
}
else if (value is double)
{
//RK rk = new RK();
//Int64 data = BitConverter.DoubleToInt64Bits((double)value);
//rk.Value = (uint)(data >> 32) & 0xFFFFFFFC;
//return rk;
NUMBER number = new NUMBER();
number.Value = (double)value;
return(number);
}
else if (value is string)
{
LABELSST label = new LABELSST();
label.SSTIndex = sharedResource.GetSSTIndex((string)value);
return(label);
}
else if (value is DateTime)
{
NUMBER number = new NUMBER();
number.Value = sharedResource.EncodeDateTime((DateTime)value);
return(number);
}
else if (value is bool)
{
BOOLERR boolerr = new BOOLERR();
boolerr.ValueType = 0;
boolerr.Value = Convert.ToByte((bool)value);
return(boolerr);
}
else if (value is ErrorCode)
{
BOOLERR boolerr = new BOOLERR();
boolerr.ValueType = 1;
boolerr.Value = ((ErrorCode)value).Code;
return(boolerr);
}
else
{
throw new Exception("Invalid cell value.");
}
}
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;
}
public static Worksheet Decode(Workbook book, Stream stream, SharedResource sharedResource)
{
Worksheet worksheet = new Worksheet();
worksheet.Book = book;
List <Record> list = WorksheetDecoder.ReadRecords(stream, out worksheet.Drawing);
worksheet.Cells = WorksheetDecoder.PopulateCells(list, sharedResource);
worksheet.Book.Records.AddRange(list);
return(worksheet);
}
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 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.FormatStrings.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);
// TODO: fix this
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);
}
}
/*
* 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<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.FONT:
FONT f = record as FONT;
sharedResource.Fonts.Add(f);
break;
}
}
return boundSheets;
}
private static FONT getFontRecord(SharedResource sharedResource, ushort index)
{
FONT result;
if (index >= 0 && index <= 3)
{
result = sharedResource.Fonts[(int)index];
}
else
{
if (index >= 5)
{
result = sharedResource.Fonts[(int)checked (index - 1)];
}
else
{
result = null;
}
}
return(result);
}
public static List <Record> Encode(Worksheet worksheet, SharedResource sharedResource)
{
List <Record> list = new List <Record>();
list.Add(new BOF
{
BIFFversion = 1536,
StreamType = 16,
BuildID = 3515,
BuildYear = 1996,
RequiredExcelVersion = 6u
});
foreach (KeyValuePair <Pair <ushort, ushort>, ushort> current in worksheet.Cells.ColumnWidth)
{
list.Add(new COLINFO
{
FirstColIndex = current.Key.Left,
LastColIndex = current.Key.Right,
Width = current.Value
});
}
DIMENSIONS dIMENSIONS = new DIMENSIONS();
checked
{
if (worksheet.Cells.Rows.Count > 0)
{
dIMENSIONS.FirstRow = worksheet.Cells.FirstRowIndex;
dIMENSIONS.FirstColumn = (short)worksheet.Cells.FirstColIndex;
dIMENSIONS.LastRow = worksheet.Cells.LastRowIndex + 1;
dIMENSIONS.LastColumn = (short)(worksheet.Cells.LastColIndex + 1);
}
list.Add(dIMENSIONS);
List <Record> list2 = new List <Record>(32);
List <Record> list3 = new List <Record>();
for (int i = dIMENSIONS.FirstRow; i < dIMENSIONS.LastRow; i++)
{
if (worksheet.Cells.Rows.ContainsKey(i))
{
Row row = worksheet.Cells.Rows[i];
list2.Add(new ROW
{
RowIndex = (ushort)i,
FirstColIndex = (ushort)row.FirstColIndex,
LastColIndex = (ushort)(row.LastColIndex + 1),
RowHeight = row.Height,
Flags = 983296u
});
for (int j = row.FirstColIndex; j <= row.LastColIndex; j++)
{
Cell cell = row.GetCell(j);
if (cell != Cell.EmptyCell && cell.Value != null)
{
CellValue cellValue = WorkSheetEncoder.EncodeCell(cell, sharedResource);
cellValue.RowIndex = (ushort)i;
cellValue.ColIndex = (ushort)j;
cellValue.XFIndex = (ushort)sharedResource.GetXFIndex(cell.Format);
list3.Add(cellValue);
}
}
if (list2.Count == 32)
{
list.AddRange(list2);
list.AddRange(list3);
list2.Clear();
list3.Clear();
}
}
}
if (list2.Count > 0)
{
list.AddRange(list2);
list.AddRange(list3);
}
if (worksheet.Pictures.Count > 0)
{
list.Add(WorkSheetEncoder.EncodePictures(worksheet.Pictures, sharedResource, worksheet));
ushort num = 1;
while ((int)num <= worksheet.Pictures.Count)
{
OBJ oBJ = new OBJ();
CommonObjectData commonObjectData = new CommonObjectData();
commonObjectData.ObjectID = num;
commonObjectData.ObjectType = 8;
commonObjectData.OptionFlags = 24593;
oBJ.SubRecords.Add(commonObjectData);
oBJ.SubRecords.Add(new End());
list.Add(oBJ);
num += 1;
}
}
EOF item = new EOF();
list.Add(item);
return(list);
}
}
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;
}
private static CellValue EncodeCell(Cell cell, SharedResource sharedResource)
{
object value = cell.Value;
CellValue result;
if (value is int)
{
result = new RK
{
Value = checked ((uint)((int)value << 2 | 2))
};
}
else
{
if (value is decimal)
{
result = new RK
{
Value = (uint)((decimal)value * 100m) << 2 | 3u
};
}
else
{
if (value is double)
{
result = new NUMBER
{
Value = (double)value
};
}
else
{
if (value is string)
{
result = new LABELSST
{
SSTIndex = sharedResource.GetSSTIndex((string)value)
};
}
else
{
if (value is DateTime)
{
result = new NUMBER
{
Value = sharedResource.EncodeDateTime((DateTime)value)
};
}
else
{
if (value is bool)
{
result = new BOOLERR
{
ValueType = 0,
Value = Convert.ToByte((bool)value)
};
}
else
{
if (!(value is ErrorCode))
{
throw new Exception("Invalid cell value.");
}
result = new BOOLERR
{
ValueType = 1,
Value = ((ErrorCode)value).Code
};
}
}
}
}
}
}
return(result);
}
private static Record EncodePictures(Dictionary <Pair <int, int>, Picture> pictures, SharedResource sharedResource, Worksheet worksheet)
{
MSODRAWING mSODRAWING = new MSODRAWING();
MsofbtDgContainer msofbtDgContainer = new MsofbtDgContainer();
mSODRAWING.EscherRecords.Add(msofbtDgContainer);
MsofbtDg msofbtDg = new MsofbtDg();
msofbtDg.Instance = 1;
checked
{
msofbtDg.NumShapes = pictures.Count + 1;
msofbtDg.LastShapeID = 1024 + pictures.Count;
msofbtDgContainer.EscherRecords.Add(msofbtDg);
MsofbtSpgrContainer msofbtSpgrContainer = new MsofbtSpgrContainer();
msofbtDgContainer.EscherRecords.Add(msofbtSpgrContainer);
MsofbtSpContainer msofbtSpContainer = new MsofbtSpContainer();
msofbtSpContainer.EscherRecords.Add(new MsofbtSpgr());
MsofbtSp msofbtSp = new MsofbtSp();
msofbtSp.ShapeId = 1024;
msofbtSp.Flags = 5;
msofbtSp.Version = 2;
msofbtSpContainer.EscherRecords.Add(msofbtSp);
msofbtSpgrContainer.EscherRecords.Add(msofbtSpContainer);
foreach (Picture current in pictures.Values)
{
if (!sharedResource.Images.Contains(current.Image))
{
sharedResource.Images.Add(current.Image);
}
MsofbtSpContainer msofbtSpContainer2 = new MsofbtSpContainer();
MsofbtSp msofbtSp2 = new MsofbtSp();
msofbtSp2.Version = 2;
msofbtSp2.ShapeType = ShapeType.PictureFrame;
msofbtSp2.ShapeId = 1024 + msofbtSpgrContainer.EscherRecords.Count;
msofbtSp2.Flags = 2560;
msofbtSpContainer2.EscherRecords.Add(msofbtSp2);
MsofbtOPT msofbtOPT = new MsofbtOPT();
msofbtOPT.Add(PropertyIDs.LockAgainstGrouping, 33226880u);
msofbtOPT.Add(PropertyIDs.FitTextToShape, 262148u);
msofbtOPT.Add(PropertyIDs.BlipId, (uint)sharedResource.Images.IndexOf(current.Image) + 1u);
msofbtSpContainer2.EscherRecords.Add(msofbtOPT);
MsofbtClientAnchor msofbtClientAnchor = new MsofbtClientAnchor();
msofbtClientAnchor.Row1 = current.TopLeftCorner.RowIndex;
msofbtClientAnchor.Col1 = current.TopLeftCorner.ColIndex;
msofbtClientAnchor.DX1 = current.TopLeftCorner.DX;
msofbtClientAnchor.DY1 = current.TopLeftCorner.DY;
msofbtClientAnchor.Row2 = current.BottomRightCorner.RowIndex;
msofbtClientAnchor.Col2 = current.BottomRightCorner.ColIndex;
msofbtClientAnchor.DX2 = current.BottomRightCorner.DX;
msofbtClientAnchor.DY2 = current.BottomRightCorner.DY;
msofbtClientAnchor.ExtraData = new byte[0];
msofbtSpContainer2.EscherRecords.Add(msofbtClientAnchor);
msofbtSpContainer2.EscherRecords.Add(new MsofbtClientData());
msofbtSpgrContainer.EscherRecords.Add(msofbtSpContainer2);
}
return(mSODRAWING);
}
}
private static Record EncodePictures(Dictionary<Pair<int, int>, Picture> pictures, SharedResource sharedResource, Worksheet worksheet)
{
MSODRAWING msoDrawing = new MSODRAWING();
MsofbtDgContainer dgContainer = new MsofbtDgContainer();
msoDrawing.EscherRecords.Add(dgContainer);
MsofbtDg dg = new MsofbtDg();
dg.Instance = 1;
dg.NumShapes = pictures.Count + 1;
dg.LastShapeID = 1024 + pictures.Count;
dgContainer.EscherRecords.Add(dg);
MsofbtSpgrContainer spgrContainer = new MsofbtSpgrContainer();
dgContainer.EscherRecords.Add(spgrContainer);
MsofbtSpContainer spContainer0 = new MsofbtSpContainer();
spContainer0.EscherRecords.Add(new MsofbtSpgr());
MsofbtSp shape0 = new MsofbtSp();
shape0.ShapeId = 1024;
shape0.Flags = ShapeFlag.Group | ShapeFlag.Patriarch;
shape0.Version = 2;
spContainer0.EscherRecords.Add(shape0);
spgrContainer.EscherRecords.Add(spContainer0);
foreach (Picture pic in pictures.Values)
{
if (!sharedResource.Images.Contains(pic.Image))
{
sharedResource.Images.Add(pic.Image);
}
MsofbtSpContainer spContainer = new MsofbtSpContainer();
MsofbtSp shape = new MsofbtSp();
shape.Version = 2;
shape.ShapeType = ShapeType.PictureFrame;
shape.ShapeId = 1024 + spgrContainer.EscherRecords.Count;
shape.Flags = ShapeFlag.Haveanchor | ShapeFlag.Hasshapetype;
spContainer.EscherRecords.Add(shape);
MsofbtOPT opt = new MsofbtOPT();
opt.Add(PropertyIDs.LockAgainstGrouping, 33226880);
opt.Add(PropertyIDs.FitTextToShape, 262148);
opt.Add(PropertyIDs.BlipId, (uint)sharedResource.Images.IndexOf(pic.Image) + 1);
spContainer.EscherRecords.Add(opt);
MsofbtClientAnchor anchor = new MsofbtClientAnchor();
anchor.Row1 = pic.TopLeftCorner.RowIndex;
anchor.Col1 = pic.TopLeftCorner.ColIndex;
anchor.DX1 = pic.TopLeftCorner.DX;
anchor.DY1 = pic.TopLeftCorner.DY;
anchor.Row2 = pic.BottomRightCorner.RowIndex;
anchor.Col2 = pic.BottomRightCorner.ColIndex;
anchor.DX2 = pic.BottomRightCorner.DX;
anchor.DY2 = pic.BottomRightCorner.DY;
anchor.ExtraData = new byte[0];
spContainer.EscherRecords.Add(anchor);
spContainer.EscherRecords.Add(new MsofbtClientData());
spgrContainer.EscherRecords.Add(spContainer);
}
return msoDrawing;
}
private static List <Record> EncodeWorkbook(Workbook workbook)
{
SharedResource sharedResource = new SharedResource(true);
List <Record> list = new List <Record>();
list.Add(new BOF
{
BIFFversion = 1536,
StreamType = 5,
BuildID = 3515,
BuildYear = 1996,
RequiredExcelVersion = 6u
});
checked
{
list.Add(new CODEPAGE
{
CodePageIdentifier = (ushort)Encoding.Unicode.CodePage
});
list.Add(new WINDOW1
{
WindowWidth = 16384,
WindowHeight = 8192,
SelecteWorksheets = 1,
TabBarWidth = 600,
OptionFlags = 56
});
DATEMODE dATEMODE = new DATEMODE();
dATEMODE.Mode = 1;
sharedResource.BaseDate = DateTime.Parse("1904-01-01");
list.Add(dATEMODE);
List <List <Record> > list2 = new List <List <Record> >();
foreach (Worksheet current in workbook.Worksheets)
{
List <Record> list3 = WorkSheetEncoder.Encode(current, sharedResource);
Record.EncodeRecords(list3);
list2.Add(list3);
}
list.AddRange(sharedResource.FormatRecords.ToArray());
list.AddRange(sharedResource.ExtendedFormats.ToArray());
List <BOUNDSHEET> list4 = new List <BOUNDSHEET>();
foreach (Worksheet current in workbook.Worksheets)
{
BOUNDSHEET bOUNDSHEET = new BOUNDSHEET();
bOUNDSHEET.Visibility = 0;
bOUNDSHEET.SheetType = 0;
bOUNDSHEET.SheetName = current.Name;
bOUNDSHEET.StreamPosition = 0u;
list4.Add(bOUNDSHEET);
list.Add(bOUNDSHEET);
}
if (sharedResource.Images.Count > 0)
{
list.Add(WorkbookEncoder.EncodeImages(sharedResource.Images));
}
Record.EncodeRecords(list);
int sstOffset = Record.CountDataLength(list);
list.Add(sharedResource.SharedStringTable);
list.Add(WorkbookEncoder.CreateEXTSST(sharedResource.SharedStringTable, sstOffset));
EOF item = new EOF();
list.Add(item);
Record.EncodeRecords(list);
int num = Record.CountDataLength(list);
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
list4[i].StreamPosition = (uint)num;
list4[i].Encode();
int num2 = Record.CountDataLength(list2[i]);
num += num2;
}
List <Record> list5 = new List <Record>();
list5.AddRange(list);
foreach (List <Record> list3 in list2)
{
list5.AddRange(list3);
}
return(list5);
}
}
private static CellValue EncodeCell(Cell cell, SharedResource sharedResource)
{
object value = cell.Value;
if (value is int || value is short || value is uint || value is byte)
{
RK rk = new RK();
rk.Value = (uint)(Convert.ToInt32(value) << 2 | 2);
return rk;
}
else if (value is decimal)
{
if (Math.Abs((decimal)value) <= (decimal)5368709.11)
{
RK rk = new RK();
rk.Value = (uint)((int)((decimal)value * 100) << 2 | 3); // integer and mul
return rk;
}
else
{
NUMBER number = new NUMBER();
number.Value = (double)(decimal)value;
return number;
}
}
else if (value is double)
{
//RK rk = new RK();
//Int64 data = BitConverter.DoubleToInt64Bits((double)value);
//rk.Value = (uint)(data >> 32) & 0xFFFFFFFC;
//return rk;
NUMBER number = new NUMBER();
number.Value = (double)value;
return number;
}
else if (value is string)
{
LABELSST label = new LABELSST();
label.SSTIndex = sharedResource.GetSSTIndex((string)value);
return label;
}
else if (value is DateTime)
{
NUMBER number = new NUMBER();
number.Value = sharedResource.EncodeDateTime((DateTime)value);
return number;
}
else if (value is bool)
{
BOOLERR boolerr = new BOOLERR();
boolerr.ValueType = 0;
boolerr.Value = Convert.ToByte((bool)value);
return boolerr;
}
else if (value is ErrorCode)
{
BOOLERR boolerr = new BOOLERR();
boolerr.ValueType = 1;
boolerr.Value = ((ErrorCode)value).Code;
return boolerr;
}
else
{
throw new Exception("Invalid cell value.");
}
}
public static List<Record> Encode(Worksheet worksheet, SharedResource sharedResource)
{
List<Record> records = new List<Record>();
BOF bof = new BOF();
bof.BIFFversion = 0x0600; //0600H = BIFF8
bof.StreamType = StreamType.Worksheet;
bof.BuildID = 3515;
bof.BuildYear = 1996;
bof.RequiredExcelVersion = 6;
records.Add(bof);
foreach (KeyValuePair<Pair<UInt16, UInt16>, UInt16> colWidth in worksheet.Cells.ColumnWidth)
{
COLINFO colInfo = new COLINFO();
colInfo.FirstColIndex = colWidth.Key.Left;
colInfo.LastColIndex = colWidth.Key.Right;
colInfo.Width = colWidth.Value;
records.Add(colInfo);
}
DIMENSIONS dimensions = new DIMENSIONS();
if (worksheet.Cells.Rows.Count > 0)
{
dimensions.FirstRow = worksheet.Cells.FirstRowIndex;
dimensions.FirstColumn = (Int16)worksheet.Cells.FirstColIndex;
dimensions.LastRow = worksheet.Cells.LastRowIndex + 1;
dimensions.LastColumn = (Int16)(worksheet.Cells.LastColIndex + 1);
}
records.Add(dimensions);
// each Row Block contains 32 consecutive rows
List<Record> rowblock = new List<Record>(32);
List<Record> cellblock = new List<Record>();
for (int rowIndex = dimensions.FirstRow; rowIndex < dimensions.LastRow; rowIndex++)
{
if (worksheet.Cells.Rows.ContainsKey(rowIndex))
{
Row sheetRow = worksheet.Cells.Rows[rowIndex];
ROW biffRow = new ROW();
biffRow.RowIndex = (UInt16)rowIndex;
biffRow.FirstColIndex = (UInt16)sheetRow.FirstColIndex;
biffRow.LastColIndex = (UInt16)(sheetRow.LastColIndex + 1);
biffRow.RowHeight = sheetRow.Height;
biffRow.Flags = 0x0F0100; // defaul value 0x0100
rowblock.Add(biffRow);
for (int colIndex = sheetRow.FirstColIndex; colIndex <= sheetRow.LastColIndex; colIndex++)
{
Cell cell = sheetRow.GetCell(colIndex);
if (cell != Cell.EmptyCell && cell.Value != null)
{
CellValue cellRecord = EncodeCell(cell, sharedResource);
cellRecord.RowIndex = (UInt16)rowIndex;
cellRecord.ColIndex = (UInt16)colIndex;
cellRecord.XFIndex = (UInt16)sharedResource.GetXFIndex(cell.CellFormat);
cellblock.Add(cellRecord);
}
}
if (rowblock.Count == 32)
{
records.AddRange(rowblock);
records.AddRange(cellblock);
rowblock.Clear();
cellblock.Clear();
}
}
}
if (rowblock.Count > 0)
{
records.AddRange(rowblock);
records.AddRange(cellblock);
}
if (worksheet.Pictures.Count > 0)
{
records.Add(EncodePictures(worksheet.Pictures, sharedResource, worksheet));
for (ushort id = 1; id <= worksheet.Pictures.Count; id++)
{
OBJ obj = new OBJ();
CommonObjectData objData = new CommonObjectData();
objData.ObjectID = id;
objData.ObjectType = 8;
objData.OptionFlags = 24593;
obj.SubRecords.Add(objData);
obj.SubRecords.Add(new End());
records.Add(obj);
}
}
EOF eof = new EOF();
records.Add(eof);
return records;
}
public static List <Record> Encode(Worksheet worksheet, SharedResource sharedResource)
{
List <Record> records = new List <Record>();
BOF bof = new BOF();
bof.BIFFversion = 0x0600; //0600H = BIFF8
bof.StreamType = StreamType.Worksheet;
bof.BuildID = 3515;
bof.BuildYear = 1996;
bof.RequiredExcelVersion = 6;
records.Add(bof);
foreach (KeyValuePair <Pair <UInt16, UInt16>, UInt16> colWidth in worksheet.Cells.ColumnWidth)
{
COLINFO colInfo = new COLINFO();
colInfo.FirstColIndex = colWidth.Key.Left;
colInfo.LastColIndex = colWidth.Key.Right;
colInfo.Width = colWidth.Value;
records.Add(colInfo);
}
DIMENSIONS dimensions = new DIMENSIONS();
if (worksheet.Cells.Rows.Count > 0)
{
dimensions.FirstRow = worksheet.Cells.FirstRowIndex;
dimensions.FirstColumn = (Int16)worksheet.Cells.FirstColIndex;
dimensions.LastRow = worksheet.Cells.LastRowIndex + 1;
dimensions.LastColumn = (Int16)(worksheet.Cells.LastColIndex + 1);
}
records.Add(dimensions);
// each Row Block contains 32 consecutive rows
List <Record> rowblock = new List <Record>(32);
List <Record> cellblock = new List <Record>();
for (int rowIndex = dimensions.FirstRow; rowIndex < dimensions.LastRow; rowIndex++)
{
if (worksheet.Cells.Rows.ContainsKey(rowIndex))
{
Row sheetRow = worksheet.Cells.Rows[rowIndex];
ROW biffRow = new ROW();
biffRow.RowIndex = (UInt16)rowIndex;
biffRow.FirstColIndex = (UInt16)sheetRow.FirstColIndex;
biffRow.LastColIndex = (UInt16)(sheetRow.LastColIndex + 1);
biffRow.RowHeight = sheetRow.Height;
biffRow.Flags = 0x0F0100; // defaul value 0x0100
rowblock.Add(biffRow);
for (int colIndex = sheetRow.FirstColIndex; colIndex <= sheetRow.LastColIndex; colIndex++)
{
Cell cell = sheetRow.GetCell(colIndex);
if (cell != Cell.EmptyCell && cell.Value != null)
{
CellValue cellRecord = EncodeCell(cell, sharedResource);
cellRecord.RowIndex = (UInt16)rowIndex;
cellRecord.ColIndex = (UInt16)colIndex;
cellRecord.XFIndex = (UInt16)sharedResource.GetXFIndex(cell.Format);
cellblock.Add(cellRecord);
}
}
if (rowblock.Count == 32)
{
records.AddRange(rowblock);
records.AddRange(cellblock);
rowblock.Clear();
cellblock.Clear();
}
}
}
if (rowblock.Count > 0)
{
records.AddRange(rowblock);
records.AddRange(cellblock);
}
if (worksheet.Pictures.Count > 0)
{
records.Add(EncodePictures(worksheet.Pictures, sharedResource, worksheet));
for (ushort id = 1; id <= worksheet.Pictures.Count; id++)
{
OBJ obj = new OBJ();
CommonObjectData objData = new CommonObjectData();
objData.ObjectID = id;
objData.ObjectType = 8;
objData.OptionFlags = 24593;
obj.SubRecords.Add(objData);
obj.SubRecords.Add(new End());
records.Add(obj);
}
}
EOF eof = new EOF();
records.Add(eof);
return(records);
}
private static CellCollection PopulateCells(List <Record> records, SharedResource sharedResource)
{
CellCollection cellCollection = new CellCollection();
cellCollection.SharedResource = sharedResource;
checked
{
foreach (Record current in records)
{
current.Decode();
ushort type = current.Type;
if (type <= 189)
{
if (type != 6)
{
if (type == 189)
{
MULRK mULRK = current as MULRK;
int rowIndex = (int)mULRK.RowIndex;
for (int i = (int)mULRK.FirstColIndex; i <= (int)mULRK.LastColIndex; i++)
{
int index = i - (int)mULRK.FirstColIndex;
object value = Record.DecodeRK(mULRK.RKList[index]);
int xFindex = (int)mULRK.XFList[index];
cellCollection.CreateCell(rowIndex, i, value, xFindex);
}
}
}
else
{
FORMULA fORMULA = current as FORMULA;
cellCollection.CreateCell((int)fORMULA.RowIndex, (int)fORMULA.ColIndex, fORMULA.DecodeResult(), (int)fORMULA.XFIndex);
}
}
else
{
if (type != 253)
{
switch (type)
{
case 515:
{
NUMBER nUMBER = current as NUMBER;
cellCollection.CreateCell((int)nUMBER.RowIndex, (int)nUMBER.ColIndex, nUMBER.Value, (int)nUMBER.XFIndex);
break;
}
case 516:
break;
case 517:
{
BOOLERR bOOLERR = current as BOOLERR;
cellCollection.CreateCell((int)bOOLERR.RowIndex, (int)bOOLERR.ColIndex, bOOLERR.GetValue(), (int)bOOLERR.XFIndex);
break;
}
default:
if (type == 638)
{
RK rK = current as RK;
cellCollection.CreateCell((int)rK.RowIndex, (int)rK.ColIndex, Record.DecodeRK(rK.Value), (int)rK.XFIndex);
}
break;
}
}
else
{
LABELSST lABELSST = current as LABELSST;
Cell cell = cellCollection.CreateCell((int)lABELSST.RowIndex, (int)lABELSST.ColIndex, sharedResource.GetStringFromSST(lABELSST.SSTIndex), (int)lABELSST.XFIndex);
cell.Style.RichTextFormat = sharedResource.SharedStringTable.RichTextFormatting[lABELSST.SSTIndex];
}
}
}
return(cellCollection);
}
}
private static List <BOUNDSHEET> DecodeRecords(List <Record> records, out SharedResource sharedResource)
{
sharedResource = new SharedResource();
List <BOUNDSHEET> list = new List <BOUNDSHEET>();
checked
{
foreach (Record current in records)
{
current.Decode();
ushort type = current.Type;
if (type <= 133)
{
if (type != 34)
{
if (type != 49)
{
if (type == 133)
{
list.Add(current as BOUNDSHEET);
}
}
else
{
FONT item = current as FONT;
sharedResource.Fonts.Add(item);
}
}
else
{
DATEMODE dATEMODE = current 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;
}
}
}
else
{
if (type <= 224)
{
if (type != 146)
{
if (type == 224)
{
sharedResource.ExtendedFormats.Add(current as XF);
}
}
else
{
PALETTE pALETTE = current as PALETTE;
int num = 8;
foreach (int current2 in pALETTE.Colors)
{
sharedResource.ColorPalette[num] = Color.FromArgb(current2);
num++;
}
}
}
else
{
if (type != 252)
{
if (type == 1054)
{
sharedResource.CellFormats.Add(current as FORMAT);
}
}
else
{
sharedResource.SharedStringTable = (current as SST);
}
}
}
}
return(list);
}
}
private static Record EncodePictures(Dictionary <Pair <int, int>, Picture> pictures, SharedResource sharedResource, Worksheet worksheet)
{
MSODRAWING msoDrawing = new MSODRAWING();
MsofbtDgContainer dgContainer = new MsofbtDgContainer();
msoDrawing.EscherRecords.Add(dgContainer);
MsofbtDg dg = new MsofbtDg();
dg.Instance = 1;
dg.NumShapes = pictures.Count + 1;
dg.LastShapeID = 1024 + pictures.Count;
dgContainer.EscherRecords.Add(dg);
MsofbtSpgrContainer spgrContainer = new MsofbtSpgrContainer();
dgContainer.EscherRecords.Add(spgrContainer);
MsofbtSpContainer spContainer0 = new MsofbtSpContainer();
spContainer0.EscherRecords.Add(new MsofbtSpgr());
MsofbtSp shape0 = new MsofbtSp();
shape0.ShapeId = 1024;
shape0.Flags = ShapeFlag.Group | ShapeFlag.Patriarch;
shape0.Version = 2;
spContainer0.EscherRecords.Add(shape0);
spgrContainer.EscherRecords.Add(spContainer0);
foreach (Picture pic in pictures.Values)
{
if (!sharedResource.Images.Contains(pic.Image))
{
sharedResource.Images.Add(pic.Image);
}
MsofbtSpContainer spContainer = new MsofbtSpContainer();
MsofbtSp shape = new MsofbtSp();
shape.Version = 2;
shape.ShapeType = ShapeType.PictureFrame;
shape.ShapeId = 1024 + spgrContainer.EscherRecords.Count;
shape.Flags = ShapeFlag.Haveanchor | ShapeFlag.Hasshapetype;
spContainer.EscherRecords.Add(shape);
MsofbtOPT opt = new MsofbtOPT();
opt.Add(PropertyIDs.LockAgainstGrouping, 33226880);
opt.Add(PropertyIDs.FitTextToShape, 262148);
opt.Add(PropertyIDs.BlipId, (uint)sharedResource.Images.IndexOf(pic.Image) + 1);
spContainer.EscherRecords.Add(opt);
MsofbtClientAnchor anchor = new MsofbtClientAnchor();
anchor.Row1 = pic.TopLeftCorner.RowIndex;
anchor.Col1 = pic.TopLeftCorner.ColIndex;
anchor.DX1 = pic.TopLeftCorner.DX;
anchor.DY1 = pic.TopLeftCorner.DY;
anchor.Row2 = pic.BottomRightCorner.RowIndex;
anchor.Col2 = pic.BottomRightCorner.ColIndex;
anchor.DX2 = pic.BottomRightCorner.DX;
anchor.DY2 = pic.BottomRightCorner.DY;
anchor.ExtraData = new byte[0];
spContainer.EscherRecords.Add(anchor);
spContainer.EscherRecords.Add(new MsofbtClientData());
spgrContainer.EscherRecords.Add(spContainer);
}
return(msoDrawing);
}
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 = (Int16)workbook.DateMode;
if (workbook.DateMode == WorkbookDateMode.Windows)
{
sharedResource.BaseDate = Constants.WindowsBaseDate;
}
if (workbook.DateMode == WorkbookDateMode.Macintosh)
{
sharedResource.BaseDate = Constants.MacintoshBaseDate;
}
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);
}
