public WorkbookStream SetMacroSheetContent(List <string> macroStrings, byte[] binaryPayload = null)
{
List <BoundSheet8> sheets = WbStream.GetAllRecordsByType <BoundSheet8>();
int macroSheetIndex = sheets.TakeWhile(sheet => sheet.dt != BoundSheet8.SheetType.Macrosheet).Count();
string macroSheetName = sheets.Skip(macroSheetIndex).First().stName.Value;
BOF macroBof = WbStream.GetAllRecordsByType <BOF>().Skip(macroSheetIndex + 1).First();
List <BiffRecord> macroRecords = WbStream.GetRecordsForBOFRecord(macroBof);
WorkbookStream macroStream = new WorkbookStream(macroRecords);
List <Formula> macroFormulas = macroStream.GetAllRecordsByType <Formula>();
int numRecordsInSheet = macroFormulas.Count;
List <BiffRecord> formulasToAdd = FormulaHelper.ConvertStringsToRecords(macroStrings, numRecordsInSheet - 1, 0, 0, 1);
if (binaryPayload != null)
{
List <string> payload = FormulaHelper.BuildPayloadMacros(binaryPayload);
formulasToAdd.AddRange(FormulaHelper.ConvertStringsToRecords(payload, numRecordsInSheet - 1 + formulasToAdd.Count, 0, 0, 2));
}
Formula haltFormula = macroFormulas.Last();
Formula modifiedHaltFormula = ((BiffRecord)haltFormula.Clone()).AsRecordType <Formula>();
modifiedHaltFormula.rw = (ushort)(numRecordsInSheet - 1 + formulasToAdd.Count);
Formula gotoFormula = FormulaHelper.GetGotoFormulaForCell(modifiedHaltFormula.rw, modifiedHaltFormula.col, 0, 1);
WorkbookStream modifiedStream = WbStream.InsertRecords(formulasToAdd, haltFormula);
modifiedStream = modifiedStream.ReplaceRecord(haltFormula, gotoFormula);
WbStream = modifiedStream;
return(WbStream);
}
public WorkbookStream SetMacroSheetContent(List <string> macroStrings, int rwStart = 0, int colStart = 0, int dstRwStart = 0, int dstColStart = 0, byte[] binaryPayload = null)
{
List <BoundSheet8> sheets = WbStream.GetAllRecordsByType <BoundSheet8>();
int macroSheetIndex = sheets.TakeWhile(sheet => sheet.dt != BoundSheet8.SheetType.Macrosheet).Count();
string macroSheetName = sheets.Skip(macroSheetIndex).First().stName.Value;
BOF macroBof = WbStream.GetAllRecordsByType <BOF>().Skip(macroSheetIndex + 1).First();
List <BiffRecord> macroRecords = WbStream.GetRecordsForBOFRecord(macroBof);
WorkbookStream macroStream = new WorkbookStream(macroRecords);
//The macro sheet template contains a single formula record to replace
Formula replaceMeFormula = macroStream.GetAllRecordsByType <Formula>().Last();
List <BiffRecord> formulasToAdd = FormulaHelper.ConvertStringsToRecords(macroStrings, rwStart, colStart, dstRwStart, dstColStart);
if (binaryPayload != null)
{
List <string> payload = FormulaHelper.BuildPayloadMacros(binaryPayload);
formulasToAdd.AddRange(FormulaHelper.ConvertStringsToRecords(payload, formulasToAdd.Count, colStart, dstRwStart, dstColStart + 1));
}
Formula gotoFormula = FormulaHelper.GetGotoFormulaForCell(formulasToAdd.Count, colStart, dstRwStart, dstColStart);
WorkbookStream modifiedStream = WbStream.ReplaceRecord(replaceMeFormula, gotoFormula);
modifiedStream = modifiedStream.InsertRecords(formulasToAdd, gotoFormula);
WbStream = modifiedStream;
return(WbStream);
}
public List <BiffRecord> GetRecordsForBOFRecord(BOF sheetBeginRecord)
{
var sheetRecords = _biffRecords.SkipWhile(r => r.Equals(sheetBeginRecord) == false).ToList();
int sheetSize = sheetRecords.TakeWhile(r => r.Id != RecordType.EOF).Count() + 1;
return(sheetRecords.Take(sheetSize).ToList());
}
private WorkbookStream GetMacroStream()
{
List <BoundSheet8> sheets = WbStream.GetAllRecordsByType <BoundSheet8>();
int macroSheetIndex = sheets.TakeWhile(sheet => sheet.dt != BoundSheet8.SheetType.Macrosheet).Count();
string macroSheetName = sheets.Skip(macroSheetIndex).First().stName.Value;
BOF macroBof = WbStream.GetAllRecordsByType <BOF>().Skip(macroSheetIndex + 1).First();
List <BiffRecord> macroRecords = WbStream.GetRecordsForBOFRecord(macroBof);
WorkbookStream macroStream = new WorkbookStream(macroRecords);
return(macroStream);
}
public ChartSheetSequence(IStreamReader reader) : base(reader)
{
//BOF
this.BOF = (BOF)BiffRecord.ReadRecord(reader);
// [ChartFrtInfo] (not specified)
if (BiffRecord.GetNextRecordType(reader) == RecordType.ChartFrtInfo)
{
this.ChartFrtInfo = (ChartFrtInfo)BiffRecord.ReadRecord(reader);
}
//CHARTSHEETCONTENT
this.ChartSheetContentSequence = new ChartSheetContentSequence(reader);
}
public void TestAddMacroSheet()
{
byte[] wbBytes = TestHelpers.GetTemplateMacroBytes();
WorkbookStream wbs = new WorkbookStream(wbBytes);
byte[] mtBytes = TestHelpers.GetMacroTestBytes();
WorkbookStream macroWorkbookStream = new WorkbookStream(mtBytes);
BoundSheet8 macroSheet = new BoundSheet8(BoundSheet8.HiddenState.Visible, BoundSheet8.SheetType.Macrosheet, "MacroSheet");
List <BOF> macroWorkbookBofs = macroWorkbookStream.GetAllRecordsByType <BOF>();
BOF LastBofRecord = macroWorkbookBofs.Last();
List <BiffRecord> sheetRecords = macroWorkbookStream.GetRecordsForBOFRecord(LastBofRecord);
byte[] sheetBytes = RecordHelper.ConvertBiffRecordsToBytes(sheetRecords);
wbs = wbs.AddSheet(macroSheet, sheetBytes);
ExcelDocWriter writer = new ExcelDocWriter();
writer.WriteDocument(TestHelpers.AssemblyDirectory + Path.DirectorySeparatorChar + "addedsheet.xls", wbs.ToBytes());
}
public void TestAddingSheetRecord()
{
byte[] wbBytes = TestHelpers.GetTemplateMacroBytes();
WorkbookStream wbs = new WorkbookStream(wbBytes);
BoundSheet8 bs8 = new BoundSheet8(BoundSheet8.HiddenState.Visible, BoundSheet8.SheetType.Macrosheet, "MyMacroSheet");
BoundSheet8 correctOffsetBs8 = ((BiffRecord)bs8.Clone()).AsRecordType <BoundSheet8>();
BoundSheet8 oldSheetRecord = wbs.GetAllRecordsByType <BoundSheet8>().First();
BoundSheet8 newSheetRecord = ((BiffRecord)oldSheetRecord.Clone()).AsRecordType <BoundSheet8>();
// bs8.lbPlyPos = (uint) (oldSheetRecord.lbPlyPos + bs8.GetBytes().Length);
List <BOF> bofRecords = wbs.GetAllRecordsByType <BOF>();
BOF spreadSheetBOF = bofRecords.Last();
// newSheetRecord.lbPlyPos = bs8.lbPlyPos;
long offset = wbs.GetRecordByteOffset(spreadSheetBOF);
bs8.lbPlyPos = oldSheetRecord.lbPlyPos;
wbs = wbs.InsertRecord(bs8, oldSheetRecord);
offset = wbs.GetRecordByteOffset(spreadSheetBOF);
correctOffsetBs8.lbPlyPos = (uint)offset;
newSheetRecord.lbPlyPos = (uint)offset;
wbs = wbs.ReplaceRecord(bs8, correctOffsetBs8);
wbs = wbs.ReplaceRecord(oldSheetRecord, newSheetRecord);
ExcelDocWriter writer = new ExcelDocWriter();
writer.WriteDocument(TestHelpers.AssemblyDirectory + Path.DirectorySeparatorChar + "testbook.xls", wbs.ToBytes());
}
/// <summary>
/// Extracting the data from the stream
/// </summary>
public override void extractData()
{
BiffHeader bh, latestbiff;
BOF firstBOF = null;
//try
//{
while (this.StreamReader.BaseStream.Position < this.StreamReader.BaseStream.Length)
{
bh.id = (RecordType)this.StreamReader.ReadUInt16();
bh.length = this.StreamReader.ReadUInt16();
// TraceLogger.DebugInternal("BIFF {0}\t{1}\t", bh.id, bh.length);
Console.WriteLine("WORKSHEET-BIFF {0}\t{1}\t", bh.id, bh.length);
switch (bh.id)
{
case RecordType.EOF:
{
this.StreamReader.BaseStream.Seek(0, SeekOrigin.End);
}
break;
case RecordType.BOF:
{
var bof = new BOF(this.StreamReader, bh.id, bh.length);
switch (bof.docType)
{
case BOF.DocumentType.WorkbookGlobals:
case BOF.DocumentType.Worksheet:
firstBOF = bof;
break;
case BOF.DocumentType.Chart:
// parse chart
break;
default:
this.readUnknownFile();
break;
}
}
break;
case RecordType.LabelSst:
{
var labelsst = new LabelSst(this.StreamReader, bh.id, bh.length);
this.bsd.addLabelSST(labelsst);
}
break;
case RecordType.MulRk:
{
var mulrk = new MulRk(this.StreamReader, bh.id, bh.length);
this.bsd.addMULRK(mulrk);
}
break;
case RecordType.Number:
{
var number = new Number(this.StreamReader, bh.id, bh.length);
this.bsd.addNUMBER(number);
}
break;
case RecordType.RK:
{
var rk = new RK(this.StreamReader, bh.id, bh.length);
this.bsd.addRK(rk);
}
break;
case RecordType.MergeCells:
{
var mergecells = new MergeCells(this.StreamReader, bh.id, bh.length);
this.bsd.MERGECELLSData = mergecells;
}
break;
case RecordType.Blank:
{
var blankcell = new Blank(this.StreamReader, bh.id, bh.length);
this.bsd.addBLANK(blankcell);
} break;
case RecordType.MulBlank:
{
var mulblank = new MulBlank(this.StreamReader, bh.id, bh.length);
this.bsd.addMULBLANK(mulblank);
}
break;
case RecordType.Formula:
{
var formula = new Formula(this.StreamReader, bh.id, bh.length);
this.bsd.addFORMULA(formula);
TraceLogger.DebugInternal(formula.ToString());
}
break;
case RecordType.Array:
{
var array = new ARRAY(this.StreamReader, bh.id, bh.length);
this.bsd.addARRAY(array);
}
break;
case RecordType.ShrFmla:
{
var shrfmla = new ShrFmla(this.StreamReader, bh.id, bh.length);
this.bsd.addSharedFormula(shrfmla);
}
break;
case RecordType.String:
{
var formulaString = new STRING(this.StreamReader, bh.id, bh.length);
this.bsd.addFormulaString(formulaString.value);
}
break;
case RecordType.Row:
{
var row = new Row(this.StreamReader, bh.id, bh.length);
this.bsd.addRowData(row);
}
break;
case RecordType.ColInfo:
{
var colinfo = new ColInfo(this.StreamReader, bh.id, bh.length);
this.bsd.addColData(colinfo);
}
break;
case RecordType.DefColWidth:
{
var defcolwidth = new DefColWidth(this.StreamReader, bh.id, bh.length);
this.bsd.addDefaultColWidth(defcolwidth.cchdefColWidth);
}
break;
case RecordType.DefaultRowHeight:
{
var defrowheigth = new DefaultRowHeight(this.StreamReader, bh.id, bh.length);
this.bsd.addDefaultRowData(defrowheigth);
}
break;
case RecordType.LeftMargin:
{
var leftm = new LeftMargin(this.StreamReader, bh.id, bh.length);
this.bsd.leftMargin = leftm.value;
}
break;
case RecordType.RightMargin:
{
var rightm = new RightMargin(this.StreamReader, bh.id, bh.length);
this.bsd.rightMargin = rightm.value;
}
break;
case RecordType.TopMargin:
{
var topm = new TopMargin(this.StreamReader, bh.id, bh.length);
this.bsd.topMargin = topm.value;
}
break;
case RecordType.BottomMargin:
{
var bottomm = new BottomMargin(this.StreamReader, bh.id, bh.length);
this.bsd.bottomMargin = bottomm.value;
}
break;
case RecordType.Setup:
{
var setup = new Setup(this.StreamReader, bh.id, bh.length);
this.bsd.addSetupData(setup);
}
break;
case RecordType.HLink:
{
long oldStreamPos = this.StreamReader.BaseStream.Position;
try
{
var hlink = new HLink(this.StreamReader, bh.id, bh.length);
this.bsd.addHyperLinkData(hlink);
}
catch (Exception ex)
{
this.StreamReader.BaseStream.Seek(oldStreamPos, System.IO.SeekOrigin.Begin);
this.StreamReader.BaseStream.Seek(bh.length, System.IO.SeekOrigin.Current);
TraceLogger.Debug("Link parse error");
TraceLogger.Error(ex.StackTrace);
}
}
break;
case RecordType.MsoDrawing:
{
// Record header has already been read. Reset position to record beginning.
this.StreamReader.BaseStream.Position -= 2 * sizeof(ushort);
this.bsd.ObjectsSequence = new ObjectsSequence(this.StreamReader);
}
break;
default:
{
// this else statement is used to read BiffRecords which aren't implemented
var buffer = new byte[bh.length];
buffer = this.StreamReader.ReadBytes(bh.length);
}
break;
}
latestbiff = bh;
}
//}
//catch (Exception ex)
//{
// TraceLogger.Error(ex.Message);
// TraceLogger.Error(ex.StackTrace);
// TraceLogger.Debug(ex.ToString());
//}
}
protected void PrintHtml(StreamWriter sw, IStreamReader workbookReader)
{
BiffHeader bh = new BiffHeader();
BiffHeader prevHeader;
Stack <BiffHeader> blocks = new Stack <BiffHeader>();
int indentLevel = 0;
Uri baseUrl = new Uri(new FileInfo(Assembly.GetExecutingAssembly().Location).DirectoryName);
sw.WriteLine("<html>");
sw.WriteLine("<head>");
sw.WriteLine("<title>" + this.Options.InputDocument + "</title>");
sw.WriteLine("<link href=\"style.css\" rel=\"stylesheet\" type=\"text/css\">");
sw.WriteLine("<style>");
sw.WriteLine(" td { font-family: Monospace, Courier; vertical-align: top; border-top: 1px solid black; padding-left: 2px; padding-right: 2px }");
sw.WriteLine(" table { border: 1px solid black; empty-cells:show; border-collapse:collapse}");
sw.WriteLine("</style>");
sw.WriteLine("</head>");
sw.WriteLine("<body>");
sw.WriteLine("<table>");
try
{
while (workbookReader.BaseStream.Position < workbookReader.BaseStream.Length)
{
long offset = workbookReader.BaseStream.Position;
prevHeader = bh;
bh.id = (RecordType)workbookReader.ReadUInt16();
bh.length = workbookReader.ReadUInt16();
// check if the record is the BOF record
string documentType = "";
if (bh.id == RecordType.BOF)
{
BOF bof = new BOF(workbookReader, (XlsFileFormat.RecordType)bh.id, bh.length);
documentType = bof.docType.ToString();
// seek back
workbookReader.BaseStream.Seek(-bh.length, System.IO.SeekOrigin.Current);
}
string strId = ((XlsFileFormat.RecordType)bh.id).ToString();
string strCurrentBlock = "";
int indent = 4 * indentLevel;
if (bh.id == RecordType.Begin)
//|| bh.id == RecordType.StartObject
//|| bh.id == RecordType.StartBlock)
{
indent += 2;
indentLevel++;
blocks.Push(prevHeader);
strCurrentBlock = " " + prevHeader.id;
}
else if (bh.id == RecordType.End)
//|| bh.id == RecordType.EndObject
//|| bh.id == RecordType.EndBlock)
{
indent -= 2;
strCurrentBlock = " " + blocks.Pop().id;
}
sw.WriteLine("<tr>");
{
byte[] buffer = workbookReader.ReadBytes(bh.length);
sw.WriteLine("<td>");
{
string url = string.Format("{0}/xlsspec/{1}.html", baseUrl, strId);
Uri uri = new Uri(url);
if (!File.Exists(uri.LocalPath))
{
// unspecified record id
url = string.Format("{0}/xlsspec/404.html", baseUrl);
}
// write record type
sw.Write("BIFF ");
for (int i = 0; i < indent; i++)
{
sw.Write(" ");
}
sw.WriteLine("<a href=\"{0}\">{1}</a> {2}{3} (0x{4:X02})", url, strId, strCurrentBlock, documentType, (int)bh.id);
}
sw.WriteLine("</td>");
// offset
sw.WriteLine("<td>");
{
sw.WriteLine("0x{0:X04}", offset);
}
sw.WriteLine("</td>");
// record length
sw.WriteLine("<td>");
{
sw.WriteLine("0x{0:X02}", bh.length);
}
sw.WriteLine("</td>");
// raw data
sw.WriteLine("<td>");
{
//Dump(buffer);
int count = 0;
foreach (byte b in buffer)
{
sw.Write("{0:X02} ", b);
count++;
if (count % 16 == 0 && count < buffer.Length)
{
sw.Write("</br>");
}
else if (count % 8 == 0 && count < buffer.Length)
{
sw.Write(" ");
}
}
}
sw.Write("</td>");
}
sw.Write("</tr>");
if (bh.id == RecordType.End)
//|| bh.id == RecordType.EndObject
//|| bh.id == RecordType.EndBlock)
{
indentLevel--;
}
if (_backgroundWorker != null)
{
int progress = 100;
if (sw.BaseStream.Length != 0)
{
progress = (int)(100 * workbookReader.BaseStream.Position / workbookReader.BaseStream.Length);
}
_backgroundWorker.ReportProgress(progress);
if (_backgroundWorker.CancellationPending)
{
_isCancelled = true;
break;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
sw.WriteLine("</table>");
sw.WriteLine("</body>");
sw.WriteLine("</html>");
}
