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); }
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); }
/* * 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); }
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 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 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.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); }