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 DataTable PopulateDataTable(Worksheet ws)
{
CellCollection Cells = ws.Cells;
// Creates DataTable from a Worksheet
// All values will be treated as Strings
DataTable dt = new DataTable(ws.Name);
// Extract columns
for (int i = 0; i <= Cells.LastColIndex; i++)
dt.Columns.Add(Cells[0, i].StringValue, typeof(String));
// Extract data
for (int currentRowIndex = 1; currentRowIndex <= Cells.LastRowIndex; currentRowIndex++)
{
DataRow dr = dt.NewRow();
for (int currentColumnIndex = 0; currentColumnIndex <= Cells.LastColIndex; currentColumnIndex++)
dr[currentColumnIndex] = Cells[currentRowIndex, currentColumnIndex].StringValue;
dt.Rows.Add(dr);
}
return dt;
}
/// <summary>
/// Populate all data from the given DataSet into a new Excel workbook
/// </summary>
/// <param name="filePath">File path to new Excel workbook to be created</param>
/// <param name="dataset">Source DataSet</param>
public static void CreateWorkbook(String filePath, DataSet dataset)
{
if (dataset.Tables.Count == 0)
throw new ArgumentException("DataSet needs to have at least one DataTable", "dataset");
Workbook workbook = new Workbook();
foreach (DataTable dt in dataset.Tables)
{
Worksheet worksheet = new Worksheet(dt.TableName);
for (int i = 0; i < dt.Columns.Count; i++)
{
// Add column header
worksheet.Cells[0, i] = new Cell(dt.Columns[i].ColumnName);
// Populate row data
for (int j = 0; j < dt.Rows.Count; j++)
worksheet.Cells[j + 1, i] = new Cell(dt.Rows[j][i]);
}
workbook.Worksheets.Add(worksheet);
}
workbook.Save(filePath);
}
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;
}
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;
}
