/// <summary>
/// Creates a stub Workbook from the supplied records,
/// suitable for use with the {@link HSSFFormulaParser}
/// </summary>
/// <param name="externs">The ExternSheetRecords in your file</param>
/// <param name="bounds">The BoundSheetRecords in your file</param>
/// <param name="sst">TThe SSTRecord in your file.</param>
/// <returns>A stub Workbook suitable for use with HSSFFormulaParser</returns>
public static InternalWorkbook CreateStubWorkbook(ExternSheetRecord[] externs,
BoundSheetRecord[] bounds, SSTRecord sst)
{
List<Record> wbRecords = new List<Record>();
// Core Workbook records go first
if (bounds != null)
{
for (int i = 0; i < bounds.Length; i++)
{
wbRecords.Add(bounds[i]);
}
}
if (sst != null)
{
wbRecords.Add(sst);
}
// Now we can have the ExternSheetRecords,
// preceded by a SupBookRecord
if (externs != null)
{
wbRecords.Add(SupBookRecord.CreateInternalReferences(
(short)externs.Length));
for (int i = 0; i < externs.Length; i++)
{
wbRecords.Add(externs[i]);
}
}
// Finally we need an EoF record
wbRecords.Add(EOFRecord.instance);
return InternalWorkbook.CreateWorkbook(wbRecords);
}
public void TestWideRecordLength()
{
BoundSheetRecord record = new BoundSheetRecord("Sheet\u20ac");
record.Sheetname = ("Sheet\u20ac");
Assert.AreEqual(24, record.RecordSize, " 2 + 2 + 4 + 2 + 1 + 1 + len(str) * 2");
}
public void TestName()
{
BoundSheetRecord record = new BoundSheetRecord("1234567890223456789032345678904");
try
{
record.Sheetname = ("s//*s");
Assert.IsTrue(false, "Should have thrown ArgumentException, but didnt");
}
catch (ArgumentException)
{
}
}
public void TestOrdering()
{
BoundSheetRecord bs1 = new BoundSheetRecord("SheetB");
BoundSheetRecord bs2 = new BoundSheetRecord("SheetC");
BoundSheetRecord bs3 = new BoundSheetRecord("SheetA");
bs1.PositionOfBof = (/*setter*/ 11);
bs2.PositionOfBof = (/*setter*/ 33);
bs3.PositionOfBof = (/*setter*/ 22);
List <BoundSheetRecord> l = new List <BoundSheetRecord>();
l.Add(bs1);
l.Add(bs2);
l.Add(bs3);
BoundSheetRecord[] r = BoundSheetRecord.OrderByBofPosition(l);
Assert.AreEqual(3, r.Length);
Assert.AreEqual(bs1, r[0]);
Assert.AreEqual(bs3, r[1]);
Assert.AreEqual(bs2, r[2]);
}
public void TestDeSerializeUnicode()
{
byte[] data = HexRead.ReadFromString(""
+ "85 00 1A 00" // sid, length
+ "3C 09 00 00" // bof
+ "00 00" // flags
+ "09 01" // str-len. unicode flag
// string data
+ "21 04 42 04 40 04"
+ "30 04 3D 04 38 04"
+ "47 04 3A 04 30 04"
);
RecordInputStream in1 = TestcaseRecordInputStream.Create(data);
BoundSheetRecord bsr = new BoundSheetRecord(in1);
// sheet name is unicode Russian for 'minor page'
Assert.AreEqual("\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430", bsr.Sheetname);
byte[] data2 = bsr.Serialize();
Assert.IsTrue(Arrays.Equals(data, data2));
}
/// <summary>
/// Creates a stub workbook from the supplied records,
/// suitable for use with the HSSFFormulaParser
/// </summary>
/// <param name="externs">The ExternSheetRecords in your file</param>
/// <param name="bounds">A stub Workbook suitable for use with HSSFFormulaParser</param>
/// <returns>A stub Workbook suitable for use with {@link HSSFFormulaParser}</returns>
public static InternalWorkbook CreateStubWorkbook(ExternSheetRecord[] externs,
BoundSheetRecord[] bounds)
{
return CreateStubWorkbook(externs, bounds, null);
}
public void TestRecordLength()
{
BoundSheetRecord record = new BoundSheetRecord("Sheet1");
Assert.AreEqual(18, record.RecordSize, " 2 + 2 + 4 + 2 + 1 + 1 + len(str)");
}
/// <summary>
/// Process an HSSF Record. Called when a record occurs in an HSSF file.
/// </summary>
/// <param name="record"></param>
public void ProcessRecord(Record record)
{
String thisText = null;
int thisRow = -1;
switch (record.Sid)
{
case BoundSheetRecord.sid:
BoundSheetRecord sr = (BoundSheetRecord)record;
sheetNames.Add(sr.Sheetname);
break;
case BOFRecord.sid:
BOFRecord bof = (BOFRecord)record;
if (bof.Type == BOFRecordType.Worksheet)
{
sheetNum++;
rowNum = -1;
if (includeSheetNames)
{
if (text.Length > 0)
{
text.Append("\n");
}
text.Append(sheetNames[sheetNum]);
}
}
break;
case SSTRecord.sid:
sstRecord = (SSTRecord)record;
break;
case FormulaRecord.sid:
FormulaRecord frec = (FormulaRecord)record;
thisRow = frec.Row;
if (formulasNotResults)
{
thisText = HSSFFormulaParser.ToFormulaString((HSSFWorkbook)null, frec.ParsedExpression);
}
else
{
if (frec.HasCachedResultString)
{
// Formula result is a string
// This is stored in the next record
outputNextStringValue = true;
nextRow = frec.Row;
}
else
{
thisText = FormatNumberDateCell(frec, frec.Value);
}
}
break;
case StringRecord.sid:
if (outputNextStringValue)
{
// String for formula
StringRecord srec = (StringRecord)record;
thisText = srec.String;
thisRow = nextRow;
outputNextStringValue = false;
}
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord)record;
thisRow = lrec.Row;
thisText = lrec.Value;
break;
case LabelSSTRecord.sid:
LabelSSTRecord lsrec = (LabelSSTRecord)record;
thisRow = lsrec.Row;
if (sstRecord == null)
{
throw new Exception("No SST record found");
}
thisText = sstRecord.GetString(lsrec.SSTIndex).ToString();
break;
case NoteRecord.sid:
NoteRecord nrec = (NoteRecord)record;
thisRow = nrec.Row;
// TODO: Find object to match nrec.GetShapeId()
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord)record;
thisRow = numrec.Row;
thisText = FormatNumberDateCell(numrec, numrec.Value);
break;
default:
break;
}
if (thisText != null)
{
if (thisRow != rowNum)
{
rowNum = thisRow;
if (text.Length > 0)
{
text.Append("\n");
}
}
else
{
text.Append("\t");
}
text.Append(thisText);
}
}
/// <summary>
/// Main HSSFListener method, processes events, and outputs the
/// strings as the file is processed
/// </summary>
public void ProcessRecord(Record record)
{
var thisRow = -1;
string thisStr = null;
switch (record.Sid)
{
case BoundSheetRecord.sid:
_boundSheetRecords.Add(record);
break;
case BOFRecord.sid:
var br = (BOFRecord)record;
if (br.Type == BOFRecord.TYPE_WORKSHEET)
{
// Create sub workbook if required
if (_workbookBuildingListener != null && _stubWorkbook == null)
{
_stubWorkbook = _workbookBuildingListener.GetStubHSSFWorkbook();
}
// Output the worksheet name
// Works by ordering the BSRs by the location of
// their BOFRecords, and then knowing that we
// process BOFRecords in byte offset order
if (_orderedBsRs == null)
{
_orderedBsRs = BoundSheetRecord.OrderByBofPosition(_boundSheetRecords);
}
}
break;
case SSTRecord.sid:
_sstRecord = (SSTRecord)record;
break;
case BlankRecord.sid:
var brec = (BlankRecord)record;
thisRow = brec.Row;
thisStr = "";
break;
case BoolErrRecord.sid:
var berec = (BoolErrRecord)record;
thisRow = berec.Row;
thisStr = "";
break;
case FormulaRecord.sid:
var frec = (FormulaRecord)record;
thisRow = frec.Row;
if (OutputFormulaValues)
{
if (double.IsNaN(frec.Value))
{
// Formula result is a string
// This is stored in the next record
_outputNextStringRecord = true;
_nextRow = frec.Row;
}
else
{
thisStr = _formatListener.FormatNumberDateCell(frec);
}
}
else
{
thisStr = HSSFFormulaParser.ToFormulaString(_stubWorkbook, frec.ParsedExpression);
}
break;
case StringRecord.sid:
if (_outputNextStringRecord)
{
// String for formula
var srec = (StringRecord)record;
thisStr = srec.String;
thisRow = _nextRow;
_outputNextStringRecord = false;
}
break;
case LabelRecord.sid:
var lrec = (LabelRecord)record;
thisRow = lrec.Row;
thisStr = lrec.Value;
break;
case LabelSSTRecord.sid:
var lsrec = (LabelSSTRecord)record;
thisRow = lsrec.Row;
if (_sstRecord == null)
{
thisStr = "(No SST Record, can't identify string)";
}
else
{
thisStr = _sstRecord.GetString(lsrec.SSTIndex).ToString();
}
break;
case NoteRecord.sid:
var nrec = (NoteRecord)record;
thisRow = nrec.Row;
thisStr = "";
break;
case NumberRecord.sid:
var numrec = (NumberRecord)record;
thisRow = numrec.Row;
// Format
thisStr = _formatListener.FormatNumberDateCell(numrec);
break;
case RKRecord.sid:
var rkrec = (RKRecord)record;
thisRow = rkrec.Row;
thisStr = "";
break;
}
// Handle new row
if (thisRow != -1 && thisRow != _lastRowNumber)
{
_row = new List <string>();
}
// Handle missing column
if (record is MissingCellDummyRecord)
{
var mc = (MissingCellDummyRecord)record;
thisRow = mc.Row;
thisStr = "";
}
// If we got something to print out, do so
if (thisStr != null)
{
_row.Add(thisStr);
}
// Update column and row count
if (thisRow > -1)
{
_lastRowNumber = thisRow;
}
// Handle end of row
if (record is LastCellOfRowDummyRecord)
{
// We're onto a new row
Output.Add(_row);
}
}
private Biff GetCorrectRecord(GenericBiff record, Stream stream, SstRecord sst)
{
Biff ret = record;
switch (record.Id)
{
case (ushort)RecordType.Bof:
BofRecord bof = new BofRecord(record);
if (bof.Version < 0x0600)
{
throw new Exception("Versions below Excel 97/2000 are currently not supported.");
}
ret = bof;
break;
case (ushort)RecordType.Boundsheet:
ret = new BoundSheetRecord(record);
break;
case (ushort)RecordType.Index:
ret = new IndexRecord(record);
break;
case (ushort)RecordType.DbCell:
ret = new DbCellRecord(record);
break;
case (ushort)RecordType.Row:
ret = new RowRecord(record);
break;
case (ushort)RecordType.Continue:
ret = new ContinueRecord(record);
break;
case (ushort)RecordType.Blank:
ret = new BlankRecord(record);
break;
case (ushort)RecordType.BoolErr:
ret = new BoolErrRecord(record);
break;
case (ushort)RecordType.Formula:
ret = new FormulaRecord(record, stream);
break;
case (ushort)RecordType.Label:
ret = new LabelRecord(record);
break;
case (ushort)RecordType.LabelSst:
ret = new LabelSstRecord(record, sst);
break;
case (ushort)RecordType.MulBlank:
ret = new MulBlankRecord(record);
break;
case (ushort)RecordType.MulRk:
ret = new MulRkRecord(record);
break;
case (ushort)RecordType.String:
ret = new StringValueRecord(record);
break;
case (ushort)RecordType.Xf:
ret = new XfRecord(record);
break;
case (ushort)RecordType.Rk:
ret = new RkRecord(record);
break;
case (ushort)RecordType.Number:
ret = new NumberRecord(record);
break;
case (ushort)RecordType.Array:
ret = new ArrayRecord(record);
break;
case (ushort)RecordType.ShrFmla:
ret = new SharedFormulaRecord(record);
break;
case (ushort)RecordType.Table:
ret = new TableRecord(record);
break;
case (ushort)RecordType.Sst:
ret = new SstRecord(record, stream);
break;
case (ushort)RecordType.Eof:
ret = new EofRecord(record);
break;
case (ushort)RecordType.Font:
ret = new FontRecord(record);
break;
case (ushort)RecordType.Format:
ret = new Net.SourceForge.Koogra.Excel.Records.FormatRecord(record);
break;
case (ushort)RecordType.Palette:
ret = new PaletteRecord(record);
break;
case (ushort)RecordType.Hyperlink:
ret = new HyperLinkRecord(record);
break;
}
return(ret);
}
internal Worksheet(Workbook wb, BoundSheetRecord sheet, SortedList <long, Biff> records)
: base(wb)
{
_name = sheet.Name;
int idx = records.IndexOfKey((long)sheet.BofPos);
_hyperlinks = new HyperLinkCollection(wb);
for (int i = idx + 1; i < records.Count; ++i)
{
Biff biff = records.Values[i];
if (biff is HyperLinkRecord)
{
_hyperlinks.Add((HyperLinkRecord)biff);
}
else if (biff is EofRecord)
{
break;
}
}
BofRecord bof = (BofRecord)records.Values[idx++];
Biff seeker = records.Values[idx++];
while (!(seeker is IndexRecord))
{
seeker = records.Values[idx++];
}
IndexRecord index = (IndexRecord)seeker;
_rows = new RowCollection(wb);
foreach (uint indexPos in index.Rows)
{
long dbCellPos = indexPos;
int dbCellIdx = records.IndexOfKey(dbCellPos);
DbCellRecord dbCell = (DbCellRecord)records[dbCellPos];
if (dbCell.RowOffset > 0)
{
long rowPos = dbCellPos - dbCell.RowOffset;
int recIndex = records.IndexOfKey(rowPos);
Debug.Assert(recIndex != -1);
Biff record = records.Values[recIndex++];
while (record is RowRecord)
{
RowRecord row = (RowRecord)record;
Row currentRow = new Row(Workbook, row);
_rows.Add(row.RowNumber, currentRow);
record = records.Values[recIndex++];
}
while (recIndex <= dbCellIdx)
{
if (!(record is CellRecord))
{
record = records.Values[recIndex++];
continue;
}
CellRecord thecell = (CellRecord)record;
Row currentRow = _rows[thecell.Row];
if (thecell is SingleColCellRecord)
{
SingleColCellRecord cell = (SingleColCellRecord)thecell;
object val = cell.Value;
Cell newCell = new Cell(Workbook, val);
if (cell is RowColXfCellRecord)
{
RowColXfCellRecord xfCell = (RowColXfCellRecord)cell;
Style style = Workbook.Styles[xfCell.Xf];
Debug.Assert(style != null);
newCell.Style = style;
}
currentRow.Cells.Add((byte)cell.Col, newCell);
}
else
{
MultipleColCellRecord cells = (MultipleColCellRecord)thecell;
for (ushort i = cells.FirstCol; i <= cells.LastCol; ++i)
{
object val = cells.GetValue(i);
if (val != null)
{
Cell newCell = null;
if (val is RkRec)
{
RkRec rk = (RkRec)val;
newCell = new Cell(Workbook, rk.Value);
Style style = Workbook.Styles[rk.Xf];
Debug.Assert(style != null);
newCell.Style = style;
}
else
{
newCell = new Cell(Workbook, val);
}
currentRow.Cells.Add((byte)i, newCell);
}
}
}
record = records.Values[recIndex++];
}
}
}
}
