public void TestMovingMergedRegion()
{
List <Record> records = new List <Record>();
CellRangeAddress[] cras =
{
new CellRangeAddress(0, 1, 0, 2),
};
MergeCellsRecord merged = new MergeCellsRecord(cras, 0, cras.Length);
records.Add(BOFRecord.CreateSheetBOF());
records.Add(new DimensionsRecord());
records.Add(new RowRecord(0));
records.Add(new RowRecord(1));
records.Add(new RowRecord(2));
records.Add(CreateWindow2Record());
records.Add(EOFRecord.instance);
records.Add(merged);
InternalSheet sheet = CreateSheet(records);
sheet.Records.RemoveAt(0);
//stub object to throw off list INDEX operations
sheet.RemoveMergedRegion(0);
Assert.AreEqual(0, sheet.NumMergedRegions, "Should be no more merged regions");
}
public void TestProcessRecords()
{
bool[] wascalled = { false, }; // hack to pass boolean by ref into inner class
ERFListener listener = new ERFListener1(ref wascalled);
ArrayList param = new ArrayList();
param.Add(BOFRecord.sid);
EventRecordFactory factory = new EventRecordFactory(listener, param);
BOFRecord bof = new BOFRecord();
bof.Build = ((short)0);
bof.BuildYear = ((short)1999);
bof.RequiredVersion = (123);
bof.Type = (BOFRecord.TYPE_WORKBOOK);
bof.Version = ((short)0x06);
bof.HistoryBitMask = (BOFRecord.HISTORY_MASK);
EOFRecord eof = EOFRecord.instance;
byte[] bytes = new byte[bof.RecordSize + eof.RecordSize];
int offset = 0;
offset = bof.Serialize(offset, bytes);
offset = eof.Serialize(offset, bytes);
factory.ProcessRecords(new MemoryStream(bytes));
Assert.IsTrue(wascalled[0], "The record listener must be called");
}
public ChartSubstreamRecordAggregate(RecordStream rs)
{
_bofRec = (BOFRecord)rs.GetNext();
List <RecordBase> temp = new List <RecordBase>();
while (rs.PeekNextClass() != typeof(EOFRecord))
{
Type a = rs.PeekNextClass();
if (PageSettingsBlock.IsComponentRecord(rs.PeekNextSid()))
{
if (_psBlock != null)
{
if (rs.PeekNextSid() == HeaderFooterRecord.sid)
{
// test samples: 45538_classic_Footer.xls, 45538_classic_Header.xls
_psBlock.AddLateHeaderFooter((HeaderFooterRecord)rs.GetNext());
continue;
}
throw new InvalidDataException(
"Found more than one PageSettingsBlock in chart sub-stream");
}
_psBlock = new PageSettingsBlock(rs);
temp.Add(_psBlock);
continue;
}
temp.Add(rs.GetNext());
}
_recs = temp;
Record eof = rs.GetNext(); // no need to save EOF in field
if (!(eof is EOFRecord))
{
throw new InvalidOperationException("Bad chart EOF");
}
}
/// <summary>
/// 获取Excel文件版本
/// </summary>
/// <param name="fullfilename"></param>
/// <returns></returns>
public static int GetExcelFileVersion(string fullfilename)
{
using (var stream = new FileStream(fullfilename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
try
{
using (var ris = new RecordInputStream(stream))
{
BOFRecordType fileType;
int version;
int sid = ris.GetNextSid();
ris.NextRecord();
BOFRecord record = new BOFRecord(ris);
fileType = record.Type;
version = record.Version;
return(version);
}
}
catch
{
stream.Seek(0, SeekOrigin.Begin);
var workbook = WorkbookFactory.Create(stream);
if (workbook is HSSFWorkbook)
{
return(8);
}
else
{
return(10);
}
}
}
}
public ChartSubstreamRecordAggregate(RecordStream rs)
{
_bofRec = (BOFRecord)rs.GetNext();
List<RecordBase> temp = new List<RecordBase>();
while (rs.PeekNextClass() != typeof(EOFRecord))
{
Type a = rs.PeekNextClass();
if (PageSettingsBlock.IsComponentRecord(rs.PeekNextSid()))
{
if (_psBlock != null)
{
if (rs.PeekNextSid() == HeaderFooterRecord.sid)
{
// test samples: 45538_classic_Footer.xls, 45538_classic_Header.xls
_psBlock.AddLateHeaderFooter((HeaderFooterRecord)rs.GetNext());
continue;
}
throw new InvalidDataException(
"Found more than one PageSettingsBlock in chart sub-stream");
}
_psBlock = new PageSettingsBlock(rs);
temp.Add(_psBlock);
continue;
}
temp.Add(rs.GetNext());
}
_recs = temp;
Record eof = rs.GetNext(); // no need to save EOF in field
if (!(eof is EOFRecord))
{
throw new InvalidOperationException("Bad chart EOF");
}
}
public void ProcessRecord(Record record)
{
_records.Add(record);
if (record is MissingRowDummyRecord)
{
MissingRowDummyRecord mr = (MissingRowDummyRecord)record;
log("Got dummy row " + mr.RowNumber);
}
if (record is MissingCellDummyRecord)
{
MissingCellDummyRecord mc = (MissingCellDummyRecord)record;
log("Got dummy cell " + mc.Row + " " + mc.Column);
}
if (record is LastCellOfRowDummyRecord)
{
LastCellOfRowDummyRecord lc = (LastCellOfRowDummyRecord)record;
log("Got end-of row, row was " + lc.Row + ", last column was " + lc.LastColumnNumber);
}
if (record is BOFRecord)
{
BOFRecord r = (BOFRecord)record;
if (r.Type == BOFRecord.TYPE_WORKSHEET)
{
log("On new sheet");
}
}
if (record is RowRecord)
{
RowRecord rr = (RowRecord)record;
log("Starting row #" + rr.RowNumber);
}
}
private static BOFRecord CreateBOFRecord()
{
BOFRecord retval = new BOFRecord();
retval.Version = ((short)600);
retval.Type = BOFRecordType.Chart;
retval.Build = ((short)0x1CFE);
retval.BuildYear = ((short)1997);
retval.HistoryBitMask = (0x40C9);
retval.RequiredVersion = (106);
return(retval);
}
public void TestNonZeroPadding_bug46987()
{
Record[] recs =
{
new BOFRecord(),
new WriteAccessRecord(), // need *something* between BOF and EOF
EOFRecord.instance,
BOFRecord.CreateSheetBOF(),
EOFRecord.instance,
};
MemoryStream baos = new MemoryStream();
for (int i = 0; i < recs.Length; i++)
{
byte[] data = recs[i].Serialize();
baos.Write(data, 0, data.Length);
}
//simulate the bad padding at the end of the workbook stream in attachment 23483 of bug 46987
baos.WriteByte(0x00);
baos.WriteByte(0x11);
baos.WriteByte(0x00);
baos.WriteByte(0x02);
for (int i = 0; i < 192; i++)
{
baos.WriteByte(0x00);
}
POIFSFileSystem fs = new POIFSFileSystem();
Stream is1;
fs.CreateDocument(new MemoryStream(baos.ToArray()), "dummy");
is1 = fs.Root.CreatePOIFSDocumentReader("dummy");
List <Record> outRecs;
try
{
outRecs = RecordFactory.CreateRecords(is1);
}
catch (Exception e)
{
if (e.Message.Equals("Buffer underrun - requested 512 bytes but 192 was available"))
{
throw new AssertionException("Identified bug 46987");
}
throw e;
}
Assert.AreEqual(5, outRecs.Count);
}
public ChartSheetAggregate(RecordStream rs, ChartRecordAggregate container)
: base(RuleName_CHARTSHEET, container)
{
_bofRec = (BOFRecord)rs.GetNext();
List <RecordBase> temp = new List <RecordBase>();
while (rs.PeekNextClass() != typeof(EOFRecord))
{
Type a = rs.PeekNextClass();
if (PageSettingsBlock.IsComponentRecord(rs.PeekNextChartSid()))
{
if (_psBlock != null)
{
if (rs.PeekNextChartSid() == HeaderFooterRecord.sid)
{
// test samples: 45538_classic_Footer.xls, 45538_classic_Header.xls
_psBlock.AddLateHeaderFooter((HeaderFooterRecord)rs.GetNext());
continue;
}
throw new InvalidDataException(
"Found more than one PageSettingsBlock in chart sub-stream");
}
_psBlock = new PageSettingsBlock(rs);
temp.Add(_psBlock);
continue;
}
if (rs.PeekNextChartSid() == ChartRecord.sid)
{
chartFormats = new ChartFormatsAggregate(rs, this);
temp.Add(chartFormats);
continue;
}
if (rs.PeekNextChartSid() == DimensionsRecord.sid)
{
seriesData = new SeriesDataAggregate(rs);
temp.Add(seriesData);
continue;
}
temp.Add(rs.GetNext());
}
_recs = temp;
Record eof = rs.GetNext(); // no need to save EOF in field
if (!(eof is EOFRecord))
{
throw new InvalidOperationException("Bad chart EOF");
}
}
public void TestHeaderFooter_bug46840()
{
int rowIx = 5;
int colIx = 6;
NumberRecord nr = new NumberRecord();
nr.Row = (rowIx);
nr.Column = ((short)colIx);
nr.Value = (3.0);
NPOI.HSSF.Record.Record[] recs =
{
BOFRecord.CreateSheetBOF(),
new HeaderRecord("&LSales Figures"),
new FooterRecord("&LJanuary"),
new HeaderFooterRecord(HexRead.ReadFromString("9C 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 C4 60 00 00 00 00 00 00 00 00")),
new DimensionsRecord(),
new WindowTwoRecord(),
new UserSViewBegin(HexRead.ReadFromString("ED 77 3B 86 BC 3F 37 4C A9 58 60 23 43 68 54 4B 01 00 00 00 64 00 00 00 40 00 00 00 02 00 00 00 3D 80 04 00 00 00 00 00 00 00 0C 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 F0 3F FF FF 01 00")),
new HeaderRecord("&LSales Figures"),
new FooterRecord("&LJanuary"),
new HeaderFooterRecord(HexRead.ReadFromString("9C 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 C4 60 00 00 00 00 00 00 00 00")),
new UserSViewEnd(HexRead.ReadFromString("01, 00")),
EOFRecord.instance,
};
RecordStream rs = new RecordStream(Arrays.AsList(recs), 0);
InternalSheet sheet;
try
{
sheet = InternalSheet.CreateSheet(rs);
}
catch (Exception e)
{
if (e.Message.Equals("two Page Settings Blocks found in the same sheet"))
{
throw new AssertionException("Identified bug 46480");
}
throw e;
}
TestCases.HSSF.UserModel.RecordInspector.RecordCollector rv = new TestCases.HSSF.UserModel.RecordInspector.RecordCollector();
sheet.VisitContainedRecords(rv, rowIx);
NPOI.HSSF.Record.Record[] outRecs = rv.Records;
Assert.AreEqual(13, outRecs.Length);
}
public ChartSheetAggregate(RecordStream rs, ChartRecordAggregate container)
: base(RuleName_CHARTSHEET, container)
{
_bofRec = (BOFRecord)rs.GetNext();
List<RecordBase> temp = new List<RecordBase>();
while (rs.PeekNextClass() != typeof(EOFRecord))
{
Type a = rs.PeekNextClass();
if (PageSettingsBlock.IsComponentRecord(rs.PeekNextChartSid()))
{
if (_psBlock != null)
{
if (rs.PeekNextChartSid() == HeaderFooterRecord.sid)
{
// test samples: 45538_classic_Footer.xls, 45538_classic_Header.xls
_psBlock.AddLateHeaderFooter((HeaderFooterRecord)rs.GetNext());
continue;
}
throw new InvalidDataException(
"Found more than one PageSettingsBlock in chart sub-stream");
}
_psBlock = new PageSettingsBlock(rs);
temp.Add(_psBlock);
continue;
}
if (rs.PeekNextChartSid() == ChartRecord.sid)
{
chartFormats = new ChartFormatsAggregate(rs, this);
temp.Add(chartFormats);
continue;
}
if (rs.PeekNextChartSid() == DimensionsRecord.sid)
{
seriesData = new SeriesDataAggregate(rs);
temp.Add(seriesData);
continue;
}
temp.Add(rs.GetNext());
}
_recs = temp;
Record eof = rs.GetNext(); // no need to save EOF in field
if (!(eof is EOFRecord))
{
throw new InvalidOperationException("Bad chart EOF");
}
}
public void TestCreateRecord()
{
BOFRecord bof = new BOFRecord();
bof.Build = ((short)0);
bof.BuildYear = ((short)1999);
bof.RequiredVersion = (123);
bof.Type = (BOFRecord.TYPE_WORKBOOK);
bof.Version = ((short)0x06);
bof.HistoryBitMask = (BOFRecord.HISTORY_MASK);
byte[] bytes = bof.Serialize();
Record[] records = RecordFactory.CreateRecord(TestcaseRecordInputStream.Create(bytes));
Assert.IsTrue(records.Length == 1, "record.Length must be 1, was =" + records.Length);
Assert.IsTrue(CompareRec(bof, records[0]), "record is the same");
}
public void TestUncalcSize_bug45066()
{
List <Record> records = new List <Record>();
records.Add(BOFRecord.CreateSheetBOF());
records.Add(new UncalcedRecord());
records.Add(new DimensionsRecord());
records.Add(CreateWindow2Record());
records.Add(new EOFRecord());
InternalSheet sheet = CreateSheet(records);
// The original bug was due to different logic for collecting records for sizing and
// serialization. The code has since been refactored into a single method for visiting
// all contained records. Now this Test is much less interesting
SizeCheckingRecordVisitor scrv = new SizeCheckingRecordVisitor();
sheet.VisitContainedRecords(scrv, 0);
Assert.AreEqual(90, scrv.TotalSize);
}
public void TestMissingDims()
{
int rowIx = 5;
int colIx = 6;
NumberRecord nr = new NumberRecord();
nr.Row = (rowIx);
nr.Column = ((short)colIx);
nr.Value = (3.0);
List <Record> inRecs = new List <Record>();
inRecs.Add(BOFRecord.CreateSheetBOF());
inRecs.Add(new RowRecord(rowIx));
inRecs.Add(nr);
inRecs.Add(CreateWindow2Record());
inRecs.Add(EOFRecord.instance);
InternalSheet sheet;
try
{
sheet = CreateSheet(inRecs);
}
catch (Exception e)
{
if ("DimensionsRecord was not found".Equals(e.Message))
{
throw new AssertionException("Identified bug 46206");
}
throw e;
}
RecordInspector.RecordCollector rv = new RecordInspector.RecordCollector();
sheet.VisitContainedRecords(rv, rowIx);
Record[] outRecs = rv.Records;
Assert.AreEqual(8, outRecs.Length);
DimensionsRecord dims = (DimensionsRecord)outRecs[5];
Assert.AreEqual(rowIx, dims.FirstRow);
Assert.AreEqual(rowIx, dims.LastRow);
Assert.AreEqual(colIx, dims.FirstCol);
Assert.AreEqual(colIx, dims.LastCol);
}
public void TestLateMargins_bug47199()
{
NPOI.HSSF.Record.Record[] recs =
{
BOFRecord.CreateSheetBOF(),
new HeaderRecord("&LSales Figures"),
new FooterRecord("&LJanuary"),
new DimensionsRecord(),
CreateBottomMargin(0.787F),
new WindowTwoRecord(),
EOFRecord.instance,
};
RecordStream rs = new RecordStream(Arrays.AsList(recs), 0);
InternalSheet sheet;
try
{
sheet = InternalSheet.CreateSheet(rs);
}
catch (Exception e)
{
if (e.Message.Equals("two Page Settings Blocks found in the same sheet"))
{
throw new AssertionException("Identified bug 47199a - failed to process late margings records");
}
throw e;
}
TestCases.HSSF.UserModel.RecordInspector.RecordCollector rv = new TestCases.HSSF.UserModel.RecordInspector.RecordCollector();
sheet.VisitContainedRecords(rv, 0);
NPOI.HSSF.Record.Record[] outRecs = rv.Records;
Assert.AreEqual(recs.Length + 1, outRecs.Length); // +1 for index record
Assert.AreEqual(typeof(BOFRecord), outRecs[0].GetType());
Assert.AreEqual(typeof(IndexRecord), outRecs[1].GetType());
Assert.AreEqual(typeof(HeaderRecord), outRecs[2].GetType());
Assert.AreEqual(typeof(FooterRecord), outRecs[3].GetType());
Assert.AreEqual(typeof(DimensionsRecord), outRecs[5].GetType());
Assert.AreEqual(typeof(WindowTwoRecord), outRecs[6].GetType());
Assert.AreEqual(typeof(EOFRecord), outRecs[7].GetType());
}
public void TestLateHeaderFooter_bug46953()
{
int rowIx = 5;
int colIx = 6;
NumberRecord nr = new NumberRecord();
nr.Row = (rowIx);
nr.Column = ((short)colIx);
nr.Value = (3.0);
NPOI.HSSF.Record.Record[] recs =
{
BOFRecord.CreateSheetBOF(),
new HeaderRecord("&LSales Figures"),
new FooterRecord("&LJanuary"),
new DimensionsRecord(),
new WindowTwoRecord(),
new HeaderFooterRecord(HexRead.ReadFromString("9C 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 C4 60 00 00 00 00 00 00 00 00")),
EOFRecord.instance,
};
RecordStream rs = new RecordStream(Arrays.AsList(recs), 0);
InternalSheet sheet = InternalSheet.CreateSheet(rs);
RecordInspector.RecordCollector rv = new RecordInspector.RecordCollector();
sheet.VisitContainedRecords(rv, 0);
NPOI.HSSF.Record.Record[] outRecs = rv.Records;
if (outRecs[4] == EOFRecord.instance)
{
throw new AssertionException("Identified bug 46953 - EOF incorrectly Appended to PSB");
}
Assert.AreEqual(recs.Length + 1, outRecs.Length); // +1 for index record
Assert.AreEqual(typeof(BOFRecord), outRecs[0].GetType());
Assert.AreEqual(typeof(IndexRecord), outRecs[1].GetType());
Assert.AreEqual(typeof(HeaderRecord), outRecs[2].GetType());
Assert.AreEqual(typeof(FooterRecord), outRecs[3].GetType());
Assert.AreEqual(typeof(HeaderFooterRecord), outRecs[4].GetType());
Assert.AreEqual(typeof(DimensionsRecord), outRecs[5].GetType());
Assert.AreEqual(typeof(WindowTwoRecord), outRecs[6].GetType());
Assert.AreEqual(typeof(EOFRecord), outRecs[7].GetType());
}
public void TestCreateSheet()
{
// Check we're Adding row and cell aggregates
List <Record> records = new List <Record>();
records.Add(BOFRecord.CreateSheetBOF());
records.Add(new DimensionsRecord());
records.Add(CreateWindow2Record());
records.Add(new EOFRecord());
InternalSheet sheet = CreateSheet(records);
Record[] outRecs = GetSheetRecords(sheet, 0);
int pos = 0;
Assert.IsTrue(outRecs[pos++] is BOFRecord);
Assert.IsTrue(outRecs[pos++] is IndexRecord);
Assert.IsTrue(outRecs[pos++] is DimensionsRecord);
Assert.IsTrue(outRecs[pos++] is WindowTwoRecord);
Assert.IsTrue(outRecs[pos++] is EOFRecord);
}
//ERFListener
public bool ProcessRecord(Record rec)
{
if (rec.Sid == BOFRecord.sid)
{
if (lastEOF != true)
{
throw new Exception("Not yet handled embedded models");
}
else
{
BOFRecord bof = (BOFRecord)rec;
switch (bof.Type)
{
case BOFRecord.TYPE_WORKBOOK:
currentmodel = new InternalWorkbook();
break;
case BOFRecord.TYPE_WORKSHEET:
currentmodel = InternalSheet.CreateSheet();
break;
default:
throw new Exception("Unsupported model type " + bof.GetType());
}
}
}
if (rec.Sid == EOFRecord.sid)
{
lastEOF = true;
ThrowEvent(currentmodel);
}
else
{
lastEOF = false;
}
return(true);
}
private void Prepare()
{
if (!ris.HasNextRecord)
{
throw new ArgumentException("File Contains no records!");
}
ris.NextRecord();
// Work out what version we're dealing with
int bofSid = ris.Sid;
switch (bofSid)
{
case BOFRecord.biff2_sid:
biffVersion = 2;
break;
case BOFRecord.biff3_sid:
biffVersion = 3;
break;
case BOFRecord.biff4_sid:
biffVersion = 4;
break;
case BOFRecord.biff5_sid:
biffVersion = 5;
break;
default:
throw new ArgumentException("File does not begin with a BOF, found sid of " + bofSid);
}
// Get the type
BOFRecord bof = new BOFRecord(ris);
fileType = (int)bof.Type;
}
public void TestBasicRecordConstruction()
{
short recType = BOFRecord.sid;
byte[] data =
{
0, 6, 5, 0, unchecked ((byte)-2), 28, unchecked ((byte)-51), 7, unchecked ((byte)-55), 64, 0, 0, 6, 1, 0, 0
};
Record[] record = RecordFactory.CreateRecord(TestcaseRecordInputStream.Create(recType, data));
Assert.AreEqual(typeof(BOFRecord).Name,
record[0].GetType().Name);
BOFRecord bofRecord = (BOFRecord)record[0];
Assert.AreEqual(7422, bofRecord.Build);
Assert.AreEqual(1997, bofRecord.BuildYear);
Assert.AreEqual(16585, bofRecord.HistoryBitMask);
Assert.AreEqual(20, bofRecord.RecordSize);
Assert.AreEqual(262, bofRecord.RequiredVersion);
Assert.AreEqual(2057, bofRecord.Sid);
Assert.AreEqual(5, bofRecord.Type);
Assert.AreEqual(1536, bofRecord.Version);
recType = MMSRecord.sid;
//size = 2;
data = new byte[]
{
0, 0
};
record = RecordFactory.CreateRecord(TestcaseRecordInputStream.Create(recType, data));
Assert.AreEqual(typeof(MMSRecord).Name,
record[0].GetType().Name);
MMSRecord mmsRecord = (MMSRecord)record[0];
Assert.AreEqual(0, mmsRecord.GetAddMenuCount());
Assert.AreEqual(0, mmsRecord.GetDelMenuCount());
Assert.AreEqual(6, mmsRecord.RecordSize);
Assert.AreEqual(193, mmsRecord.Sid);
}
/// <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);
}
}
public void TestDuplicateHeaderFooter_bug48026()
{
NPOI.HSSF.Record.Record[] recs =
{
BOFRecord.CreateSheetBOF(),
new IndexRecord(),
//PageSettingsBlock
new HeaderRecord("&LDecember"),
new FooterRecord("&LJanuary"),
new DimensionsRecord(),
new WindowTwoRecord(),
//CustomViewSettingsRecordAggregate
new UserSViewBegin(HexRead.ReadFromString("53 CE BD CC DE 38 44 45 97 C1 5C 89 F9 37 32 1B 01 00 00 00 64 00 00 00 40 00 00 00 03 00 00 00 7D 00 00 20 00 00 34 00 00 00 18 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 FF FF FF FF")),
new SelectionRecord(0, 0),
new UserSViewEnd(HexRead.ReadFromString("01 00")),
// two HeaderFooterRecord records, the first one has zero GUID (16 bytes at offset 12) and belongs to the PSB,
// the other is matched with a CustomViewSettingsRecordAggregate having UserSViewBegin with the same GUID
new HeaderFooterRecord(HexRead.ReadFromString("9C 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 34 33 00 00 00 00 00 00 00 00")),
new HeaderFooterRecord(HexRead.ReadFromString("9C 08 00 00 00 00 00 00 00 00 00 00 53 CE BD CC DE 38 44 45 97 C1 5C 89 F9 37 32 1B 34 33 00 00 00 00 00 00 00 00")),
EOFRecord.instance,
};
RecordStream rs = new RecordStream(Arrays.AsList(recs), 0);
InternalSheet sheet;
try
{
sheet = InternalSheet.CreateSheet(rs);
}
catch (Exception e)
{
if (e.Message.Equals("Duplicate PageSettingsBlock record (sid=0x89c)"))
{
throw new AssertionException("Identified bug 48026");
}
throw e;
}
RecordInspector.RecordCollector rv = new RecordInspector.RecordCollector();
sheet.VisitContainedRecords(rv, 0);
NPOI.HSSF.Record.Record[] outRecs = rv.Records;
Assert.AreEqual(recs.Length, outRecs.Length);
//expected order of records:
NPOI.HSSF.Record.Record[] expectedRecs =
{
recs[0], //BOFRecord
recs[1], //IndexRecord
//PageSettingsBlock
recs[2], //HeaderRecord
recs[3], //FooterRecord
recs[9], //HeaderFooterRecord
recs[4], // DimensionsRecord
recs[5], // WindowTwoRecord
//CustomViewSettingsRecordAggregate
recs[6], // UserSViewBegin
recs[7], // SelectionRecord
recs[10], // HeaderFooterRecord
recs[8], // UserSViewEnd
recs[11], //EOFRecord
};
for (int i = 0; i < expectedRecs.Length; i++)
{
Assert.AreEqual(expectedRecs[i].GetType(), outRecs[i].GetType(), "Record mismatch at index " + i);
}
HeaderFooterRecord hd1 = (HeaderFooterRecord)expectedRecs[4];
//GUID is zero
Assert.IsTrue(Arrays.Equals(new byte[16], hd1.Guid));
Assert.IsTrue(hd1.IsCurrentSheet);
UserSViewBegin svb = (UserSViewBegin)expectedRecs[7];
HeaderFooterRecord hd2 = (HeaderFooterRecord)expectedRecs[9];
Assert.IsFalse(hd2.IsCurrentSheet);
//GUIDs of HeaderFooterRecord and UserSViewBegin must be the same
Assert.IsTrue(Arrays.Equals(svb.Guid, hd2.Guid));
}
public InternalChart(RecordStream rs)
{
_plsRecords = new List <PLSAggregate>();
records = new List <RecordBase>(128);
if (rs.PeekNextSid() != BOFRecord.sid)
{
throw new Exception("BOF record expected");
}
BOFRecord bof = (BOFRecord)rs.GetNext();
if (bof.Type != BOFRecordType.Chart)
{
throw new RuntimeException("Bad BOF record type");
}
records.Add(bof);
while (rs.HasNext())
{
int recSid = rs.PeekNextSid();
Record.Record rec = rs.GetNext();
if (recSid == EOFRecord.sid)
{
records.Add(rec);
break;
}
if (recSid == ChartRecord.sid)
{
continue;
}
if (recSid == ChartFRTInfoRecord.sid)
{
_chartFrtInfo = (ChartFRTInfoRecord)rec;
}
else if (recSid == HeaderRecord.sid)
{
header = (HeaderRecord)rec;
}
else if (recSid == FooterRecord.sid)
{
footer = (FooterRecord)rec;
}
else if (recSid == HCenterRecord.sid)
{
_hCenter = (HCenterRecord)rec;
}
else if (recSid == VCenterRecord.sid)
{
_vCenter = (VCenterRecord)rec;
}
else if (recSid == LeftMarginRecord.sid)
{
_leftMargin = (LeftMarginRecord)rec;
}
else if (recSid == RightMarginRecord.sid)
{
_rightMargin = (RightMarginRecord)rec;
}
else if (recSid == TopMarginRecord.sid)
{
_topMargin = (TopMarginRecord)rec;
}
else if (recSid == BottomMarginRecord.sid)
{
_bottomMargin = (BottomMarginRecord)rec;
}
else if (recSid == UnknownRecord.PLS_004D) // PLS
{
PLSAggregate pls = new PLSAggregate(rs);
PLSAggregateVisitor rv = new PLSAggregateVisitor(records);
pls.VisitContainedRecords(rv);
_plsRecords.Add(pls);
continue;
}
else if (recSid == PrintSetupRecord.sid)
{
printSetup = (PrintSetupRecord)rec;
}
else if (recSid == PrintSizeRecord.sid)
{
_printSize = (PrintSizeRecord)rec;
}
else if (recSid == HeaderFooterRecord.sid)
{
HeaderFooterRecord hf = (HeaderFooterRecord)rec;
if (hf.IsCurrentSheet)
{
_headerFooter = hf;
}
else
{
_sviewHeaderFooters.Add(hf);
}
}
else if (recSid == ProtectRecord.sid)
{
_protect = (ProtectRecord)rec;
}
records.Add(rec);
}
}
/// <summary>
/// Process an HSSF Record. Called when a record occurs in an HSSF file.
/// </summary>
/// <param name="record"></param>
public void ProcessRecord(Record record)
{
int thisRow;
int thisColumn;
CellValueRecordInterface[] expandedRecords = null;
if (record is CellValueRecordInterface)
{
CellValueRecordInterface valueRec = (CellValueRecordInterface)record;
thisRow = valueRec.Row;
thisColumn = valueRec.Column;
}
else
{
thisRow = -1;
thisColumn = -1;
switch (record.Sid)
{
// the BOFRecord can represent either the beginning of a sheet or the workbook
case BOFRecord.sid:
BOFRecord bof = (BOFRecord)record;
if (bof.Type == BOFRecord.TYPE_WORKBOOK || bof.Type == BOFRecord.TYPE_WORKSHEET)
{
// Reset the row and column counts - new workbook / worksheet
ResetCounts();
}
break;
case RowRecord.sid:
RowRecord rowrec = (RowRecord)record;
//Console.WriteLine("Row " + rowrec.RowNumber + " found, first column at "
// + rowrec.GetFirstCol() + " last column at " + rowrec.GetLastCol());
// If there's a jump in rows, fire off missing row records
if (lastRowRow + 1 < rowrec.RowNumber)
{
for (int i = (lastRowRow + 1); i < rowrec.RowNumber; i++)
{
MissingRowDummyRecord dr = new MissingRowDummyRecord(i);
childListener.ProcessRecord(dr);
}
}
// Record this as the last row we saw
lastRowRow = rowrec.RowNumber;
break;
case SharedFormulaRecord.sid:
// SharedFormulaRecord occurs after the first FormulaRecord of the cell range.
// There are probably (but not always) more cell records after this
// - so don't fire off the LastCellOfRowDummyRecord yet
childListener.ProcessRecord(record);
return;
case MulBlankRecord.sid:
// These appear in the middle of the cell records, to
// specify that the next bunch are empty but styled
// Expand this out into multiple blank cells
MulBlankRecord mbr = (MulBlankRecord)record;
expandedRecords = RecordFactory.ConvertBlankRecords(mbr);
break;
case MulRKRecord.sid:
// This is multiple consecutive number cells in one record
// Exand this out into multiple regular number cells
MulRKRecord mrk = (MulRKRecord)record;
expandedRecords = RecordFactory.ConvertRKRecords(mrk);
break;
case NoteRecord.sid:
NoteRecord nrec = (NoteRecord)record;
thisRow = nrec.Row;
thisColumn = nrec.Column;
break;
default:
//Console.WriteLine(record.GetClass());
break;
}
}
// First part of expanded record handling
if (expandedRecords != null && expandedRecords.Length > 0)
{
thisRow = expandedRecords[0].Row;
thisColumn = expandedRecords[0].Column;
}
// If we're on cells, and this cell isn't in the same
// row as the last one, then fire the
// dummy end-of-row records
if (thisRow != lastCellRow && lastCellRow > -1)
{
for (int i = lastCellRow; i < thisRow; i++)
{
int cols = -1;
if (i == lastCellRow)
{
cols = lastCellColumn;
}
childListener.ProcessRecord(new LastCellOfRowDummyRecord(i, cols));
}
}
// If we've just finished with the cells, then fire the
// final dummy end-of-row record
if (lastCellRow != -1 && lastCellColumn != -1 && thisRow == -1)
{
childListener.ProcessRecord(new LastCellOfRowDummyRecord(lastCellRow, lastCellColumn));
lastCellRow = -1;
lastCellColumn = -1;
}
// If we've moved onto a new row, the ensure we re-set
// the column counter
if (thisRow != lastCellRow)
{
lastCellColumn = -1;
}
// If there's a gap in the cells, then fire
// the dummy cell records
if (lastCellColumn != thisColumn - 1)
{
for (int i = lastCellColumn + 1; i < thisColumn; i++)
{
childListener.ProcessRecord(new MissingCellDummyRecord(thisRow, i));
}
}
// Next part of expanded record handling
if (expandedRecords != null && expandedRecords.Length > 0)
{
thisColumn = expandedRecords[expandedRecords.Length - 1].Column;
}
// Update cell and row counts as needed
if (thisColumn != -1)
{
lastCellColumn = thisColumn;
lastCellRow = thisRow;
}
// Pass along the record(s)
if (expandedRecords != null && expandedRecords.Length > 0)
{
foreach (CellValueRecordInterface r in expandedRecords)
{
childListener.ProcessRecord((Record)r);
}
}
else
{
childListener.ProcessRecord(record);
}
}
public static List <T> GetObjDataOnSheet <T>(string fullfilename, int headerRowIndex)
{
List <T> dt = new List <T>();
PropertyInfo[] props = typeof(T).GetProperties();
Dictionary <string, string> pairs = new Dictionary <string, string>();
foreach (var item in props)
{
var ignoreattrs = item.GetCustomAttributes(typeof(ColumnIgnoreAttribute), false).Cast <ColumnIgnoreAttribute>();
if (ignoreattrs.Count() > 0)
{
continue;
}
string key = item.Name;
string value = key;
var attrs = item.GetCustomAttributes(typeof(ColumnHeaderAttribute), false).Cast <ColumnHeaderAttribute>();
if (attrs.Count() > 0)
{
value = attrs.First().Name;
}
pairs.Add(key, value);
}
Dictionary <int, string> indexes = new Dictionary <int, string>();
using (var stream = new FileStream(fullfilename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (var ris = new RecordInputStream(stream))
{
BOFRecordType fileType;
int version;
while (ris.HasNextRecord)
{
int sid = ris.GetNextSid();
ris.NextRecord();
switch (sid)
{
case BOFRecord.biff2_sid:
//文件头
{
BOFRecord record = new BOFRecord(ris);
fileType = record.Type;
version = record.Version;
}
break;
case EOFRecord.sid:
//文件尾
{
EOFRecord record = new EOFRecord(ris);
}
break;
case 30: //0x1E
{
//FORMAT —— 仅biff2和biff3中有效
//FormatRecord
ris.ReadFully(new byte[ris.Remaining]);
}
break;
case ColumnWidthRecord.biff2_sid: // 36://0x24
{
//COLWIDTH —— 仅biff2中有效
ColumnWidthRecord record = new ColumnWidthRecord(ris);
//ris.ReadFully(new byte[ris.Remaining]);
//int columnCount = record.LastColumnIndex - record.FirstColumnIndex;
//for (int i = 0; i < columnCount; i++)
//{
// DataColumn dc = new DataColumn();
// dt.Columns.Add(dc);
//}
}
break;
case FontRecord.sid: //0x31
{
//FontRecord biff2中与biff5之后版本不一样
ris.ReadFully(new byte[ris.Remaining]);
}
break;
// 0x36 TABLEOP —— 仅biff2中有效
//Top left cell of a multiple operations table
case PrintGridlinesRecord.sid:
//行结束 - 换行 [todo 确认，只有列头结束才有]
{
PrintGridlinesRecord record = new PrintGridlinesRecord(ris);
}
break;
case OldNumberRecord.biff2_sid:
{
OldNumberRecord record = new OldNumberRecord(ris);
if (!indexes.ContainsKey(record.Column))
{
break;
}
int dtRowIndex = record.Row - headerRowIndex - 1;
if (dt.Count() > dtRowIndex)
{
//dt[dtRowIndex][record.Column] = record.Value;
T dataRow = dt[dtRowIndex];
var propsel = from c in props where c.Name.ToLower().Equals(indexes[record.Column].ToLower()) select c;
if (propsel == null || propsel.Count() <= 0)
{
continue;
}
PropertyInfo prop = propsel.FirstOrDefault();
prop.SetValue(dataRow, record.Value, null);
}
else
{
T dataRow = System.Activator.CreateInstance <T>();
dt.Add(dataRow);
//DataRow dr = dt.NewRow();
//dt.Rows.Add(dr);
//dr[record.Column] = record.Value;
var propsel = from c in props where c.Name.ToLower().Equals(indexes[record.Column].ToLower()) select c;
if (propsel == null || propsel.Count() <= 0)
{
continue;
}
PropertyInfo prop = propsel.FirstOrDefault();
prop.SetValue(dataRow, record.Value, null);
}
}
break;
case OldLabelRecord.biff2_sid:
{
OldLabelRecord record = new OldLabelRecord(ris);
record.SetCodePage(new CodepageRecord()
{
Codepage = (short)CodePageUtil.CP_GBK
});
if (record.Row == headerRowIndex)
{
//列头
//DataColumn dc = new DataColumn(record.Value);
//dt.Columns.Add(dc);
//dt.Columns[record.Column].ColumnName = ;
string hearName = record.Value;
var pair = pairs.Where(p => p.Value == hearName);
if (pair.Count() > 0)
{
indexes.Add(record.Column, pair.First().Key);
}
}
else if (record.Row > headerRowIndex)
{
if (!indexes.ContainsKey(record.Column))
{
break;
}
int dtRowIndex = record.Row - headerRowIndex - 1;
if (dt.Count > dtRowIndex)
{
//dt.Rows[dtRowIndex][record.Column] = record.Value;
T dataRow = dt[dtRowIndex];
var propsel = from c in props where c.Name.ToLower().Equals(indexes[record.Column].ToLower()) select c;
if (propsel == null || propsel.Count() <= 0)
{
continue;
}
PropertyInfo prop = propsel.FirstOrDefault();
prop.SetValue(dataRow, record.Value, null);
}
else
{
T dataRow = System.Activator.CreateInstance <T>();
dt.Add(dataRow);
//DataRow dr = dt.NewRow();
//dt.Rows.Add(dr);
//dr[record.Column] = record.Value;
var propsel = from c in props where c.Name.ToLower().Equals(indexes[record.Column].ToLower()) select c;
if (propsel == null || propsel.Count() <= 0)
{
continue;
}
PropertyInfo prop = propsel.FirstOrDefault();
prop.SetValue(dataRow, record.Value, null);
}
}
}
break;
case OldFormulaRecord.biff2_sid:
{
OldFormulaRecord record = new OldFormulaRecord(ris);
var value = record.Value;
}
break;
case OldStringRecord.biff2_sid:
{
OldStringRecord record = new OldStringRecord(ris);
record.SetCodePage(new CodepageRecord()
{
Codepage = (short)CodePageUtil.CP_GBK
});
var value = record.GetString();
}
break;
default:
ris.ReadFully(new byte[ris.Remaining]);
break;
}
}
}
}
return(dt);
}
//static void LoadDocumentPart(HWPFDocument hwpf,TreeNode parent,string partName,FieldsDocumentPart part)
//{
// TreeNode mainNode = new TreeNode(partName);
// mainNode.ImageKey = "Folder";
// mainNode.SelectedImageKey = "Folder";
// parent.Nodes.Add(mainNode);
// Fields fields = hwpf.GetFields();
// foreach (Field field in fields.GetFields(part))
// {
// TreeNode tn = new TreeNode("Field");
// mainNode.Nodes.Add(tn);
// }
//}
static void HandleWorkbook(TreeNode treeNode, HSSFWorkbook hssfworkbook)
{
if (hssfworkbook.NumberOfSheets > 0)
{
treeNode.ImageKey = "Folder";
treeNode.SelectedImageKey = "Folder";
for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
{
string sheettext = string.Format("Sheet {0}", i + 1);
TreeNode sheetnode =
treeNode.Nodes.Add(sheettext, sheettext, "Folder", "Folder");
HSSFSheet hssfsheet = ((HSSFSheet)hssfworkbook.GetSheetAt(i));
EscherAggregate ea = hssfsheet.DrawingEscherAggregate;
IEnumerator iterator1 = hssfsheet.Sheet.Records.GetEnumerator();
int chartIndex = 1;
while (iterator1.MoveNext())
{
if (iterator1.Current is BOFRecord)
{
BOFRecord bof = (BOFRecord)iterator1.Current;
if (bof.Type == BOFRecordType.Chart)
{
string chartTitle = string.Format("Chart {0}", chartIndex);
TreeNode chartnode = sheetnode.Nodes.Add(chartTitle, chartTitle, "Folder", "Folder");
chartnode.Nodes.Add(new RecordTreeNode(bof));
while (iterator1.MoveNext())
{
if (iterator1.Current is RecordAggregate)
{
RecordAggregate record = (RecordAggregate)iterator1.Current;
chartnode.Nodes.Add(new RecordAggregateTreeNode(record));
}
else /*if(iterator1.Current is Record)*/
{
chartnode.Nodes.Add(new RecordTreeNode((Record)iterator1.Current));
}
if (iterator1.Current is EOFRecord)
{
break;
}
}
chartIndex++;
}
}
else if (iterator1.Current is Record)
{
Record record = (Record)iterator1.Current;
sheetnode.Nodes.Add(new RecordTreeNode(record));
}
else if (iterator1.Current is RecordAggregate)
{
RecordAggregate record = (RecordAggregate)iterator1.Current;
sheetnode.Nodes.Add(new RecordAggregateTreeNode(record));
}
}
//RecordTreeNode rtn = new DirectoryTreeNode();
if (ea != null)
{
foreach (EscherRecord er in ea.EscherRecords)
{
sheetnode.Nodes.Add(new EscherRecordTreeNode(er));
}
}
}
}
else
{
treeNode.ImageKey = "Binary";
}
IEnumerator iterator2 = hssfworkbook.Workbook.Records.GetEnumerator();
while (iterator2.MoveNext())
{
if (iterator2.Current is Record) //&& !(iterator2.Current is UnknownRecord))
{
Record record = (Record)iterator2.Current;
if (record is DrawingGroupRecord)
{
hssfworkbook.GetAllPictures();
}
treeNode.Nodes.Add(new RecordTreeNode(record));
}
else if (iterator2.Current is RecordBase)
{
RecordBase record = (RecordBase)iterator2.Current;
treeNode.Nodes.Add(record.GetType().Name);
}
}
}
/// <summary>
/// 获取旧版excel数据至datatable中
/// </summary>
/// <param name="fullfilename"></param>
/// <param name="headerRowIndex"></param>
/// <returns></returns>
public static DataTable GetDataOnSheet(string fullfilename, int headerRowIndex)
{
DataTable dt = new DataTable();
dt.TableName = Path.GetFileNameWithoutExtension(fullfilename);
using (var stream = new FileStream(fullfilename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (var ris = new RecordInputStream(stream))
{
BOFRecordType fileType;
int version;
while (ris.HasNextRecord)
{
int sid = ris.GetNextSid();
ris.NextRecord();
switch (sid)
{
case BOFRecord.biff2_sid:
//文件头
{
BOFRecord record = new BOFRecord(ris);
fileType = record.Type;
version = record.Version;
}
break;
case EOFRecord.sid:
//文件尾
{
EOFRecord record = new EOFRecord(ris);
}
break;
case 30: //0x1E
{
//FORMAT —— 仅biff2和biff3中有效
//FormatRecord
ris.ReadFully(new byte[ris.Remaining]);
}
break;
case ColumnWidthRecord.biff2_sid: // 36://0x24
{
//COLWIDTH —— 仅biff2中有效
ColumnWidthRecord record = new ColumnWidthRecord(ris);
//ris.ReadFully(new byte[ris.Remaining]);
int columnCount = record.LastColumnIndex - record.FirstColumnIndex;
for (int i = 0; i < columnCount; i++)
{
DataColumn dc = new DataColumn();
dt.Columns.Add(dc);
}
}
break;
case FontRecord.sid: //0x31
{
//FontRecord biff2中与biff5之后版本不一样
ris.ReadFully(new byte[ris.Remaining]);
}
break;
// 0x36 TABLEOP —— 仅biff2中有效
//Top left cell of a multiple operations table
case PrintGridlinesRecord.sid:
//行结束 - 换行 [todo 确认，只有列头结束才有]
{
PrintGridlinesRecord record = new PrintGridlinesRecord(ris);
}
break;
case OldNumberRecord.biff2_sid:
{
OldNumberRecord record = new OldNumberRecord(ris);
int dtRowIndex = record.Row - headerRowIndex - 1;
if (dt.Rows.Count > dtRowIndex)
{
dt.Rows[dtRowIndex][record.Column] = record.Value;
}
else
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
dr[record.Column] = record.Value;
}
}
break;
case OldLabelRecord.biff2_sid:
{
OldLabelRecord record = new OldLabelRecord(ris);
record.SetCodePage(new CodepageRecord()
{
Codepage = (short)CodePageUtil.CP_GBK
});
if (record.Row == headerRowIndex)
{
//列头
//DataColumn dc = new DataColumn(record.Value);
//dt.Columns.Add(dc);
dt.Columns[record.Column].ColumnName = record.Value;
}
else if (record.Row > headerRowIndex)
{
int dtRowIndex = record.Row - headerRowIndex - 1;
if (dt.Rows.Count > dtRowIndex)
{
dt.Rows[dtRowIndex][record.Column] = record.Value;
}
else
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
dr[record.Column] = record.Value;
}
}
}
break;
case OldFormulaRecord.biff2_sid:
{
OldFormulaRecord record = new OldFormulaRecord(ris);
var value = record.Value;
}
break;
case OldStringRecord.biff2_sid:
{
OldStringRecord record = new OldStringRecord(ris);
record.SetCodePage(new CodepageRecord()
{
Codepage = (short)CodePageUtil.CP_GBK
});
var value = record.GetString();
}
break;
default:
ris.ReadFully(new byte[ris.Remaining]);
break;
}
}
}
}
return(dt);
}
