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;
}
public static void Encode(Workbook workbook, Stream stream)
{
List<Record> records = EncodeWorkbook(workbook);
BinaryWriter writer = new BinaryWriter(stream);
foreach (Record record in records)
{
record.Write(writer);
}
writer.Close();
}
public static Workbook Decode(Stream stream)
{
Workbook book = new Workbook();
SharedResource sharedResource;
List<Record> records = ReadRecords(stream, out book.DrawingGroup);
book.Records = records;
List<BOUNDSHEET> boundSheets = DecodeRecords(records, out sharedResource);
foreach (BOUNDSHEET boundSheet in boundSheets)
{
stream.Position = boundSheet.StreamPosition;
Worksheet sheet = WorksheetDecoder.Decode(book, stream, sharedResource);
sheet.Book = book;
sheet.Name = boundSheet.SheetName;
sheet.SheetType = (SheetType)boundSheet.SheetType;
book.Worksheets.Add(sheet);
}
return book;
}
/// <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 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;
}