public void TestLongRecords()
{
int[] length = { 1024, 2048, 4096, 8192, 16384 }; //test against strings of different length
for (int i = 0; i < length.Length; i++)
{
StringBuilder buff = new StringBuilder(length[i]);
for (int j = 0; j < length[i]; j++)
{
buff.Append("x");
}
NPOI.SS.UserModel.RichTextString str = new HSSFRichTextString(buff.ToString());
TextObjectRecord obj = new TextObjectRecord();
obj.Str = (str);
byte[] data = obj.Serialize();
RecordInputStream is1 = new RecordInputStream(new MemoryStream(data));
is1.NextRecord();
TextObjectRecord record = new TextObjectRecord(is1);
str = record.Str;
Assert.AreEqual(buff.Length, str.Length);
Assert.AreEqual(buff.ToString(), str.String);
}
}
/// <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);
}
}
}
}
/// <summary>
///First 4 bytes of data are assumed to be record identifier and length. The supplied
///data can contain multiple records (sequentially encoded in the same way)
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static RecordInputStream Create(byte[] data)
{
Stream inputStream = new MemoryStream(data);
RecordInputStream result = new RecordInputStream(inputStream);
result.NextRecord();
return(result);
}
public void TestRead()
{
RecordInputStream is1 = new RecordInputStream(new MemoryStream(data));
is1.NextRecord();
TextObjectRecord record = new TextObjectRecord(is1);
Assert.AreEqual(TextObjectRecord.sid, record.Sid);
Assert.AreEqual(TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_LEFT_ALIGNED, record.HorizontalTextAlignment);
Assert.AreEqual(TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_TOP, record.VerticalTextAlignment);
Assert.AreEqual(TextObjectRecord.TEXT_ORIENTATION_NONE, record.TextOrientation);
Assert.AreEqual("Hello, World!", record.Str.String);
}
public void TestLinkFormula()
{
RecordInputStream is1 = new RecordInputStream(new MemoryStream(linkData));
is1.NextRecord();
TextObjectRecord rec = new TextObjectRecord(is1);
Ptg ptg = rec.LinkRefPtg;
Assert.IsNotNull(ptg);
Assert.AreEqual(typeof(RefPtg), ptg.GetType());
RefPtg rptg = (RefPtg)ptg;
Assert.AreEqual("T2", rptg.ToFormulaString());
byte[] data2 = rec.Serialize();
Assert.AreEqual(linkData.Length, data2.Length);
Assert.IsTrue(Arrays.Equals(linkData, data2));
}
public void TestWithConcat()
{
// =CHOOSE(2,A2,A3,A4)
byte[] data =
{
6, 0, 68, 0,
1, 0, 1, 0, 15, 0, 0, 0, 0, 0, 0, 0, 57,
64, 0, 0, 12, 0, 12, unchecked ((byte)-4), 46, 0,
30, 2, 0, // Int - 2
25, 4, 3, 0, // Attr
8, 0, 17, 0, 26, 0, // jumpTable
35, 0, // chooseOffSet
36, 1, 0, 0,unchecked ((byte)-64), // Ref - A2
25, 8, 21, 0, // Attr
36, 2, 0, 0,unchecked ((byte)-64), // Ref - A3
25, 8, 12, 0, // Attr
36, 3, 0, 0,unchecked ((byte)-64), // Ref - A4
25, 8, 3, 0, // Attr
66, 4, 100, 0 // CHOOSE
};
RecordInputStream inp = new RecordInputStream(new MemoryStream(data));
inp.NextRecord();
FormulaRecord fr = new FormulaRecord(inp);
Ptg[] ptgs = fr.ParsedExpression;
Assert.AreEqual(9, ptgs.Length);
Assert.AreEqual(typeof(IntPtg), ptgs[0].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual(typeof(RefPtg), ptgs[2].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[3].GetType());
Assert.AreEqual(typeof(RefPtg), ptgs[4].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[5].GetType());
Assert.AreEqual(typeof(RefPtg), ptgs[6].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[7].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[8].GetType());
FuncVarPtg choose = (FuncVarPtg)ptgs[8];
Assert.AreEqual("CHOOSE", choose.Name);
}
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 TestWriteEmpty()
{
HSSFRichTextString str = new HSSFRichTextString("");
TextObjectRecord record = new TextObjectRecord();
record.Str = (str);
byte[] ser = record.Serialize();
int formatDataLen = NPOI.Util.LittleEndian.GetUShort(ser, 16);
Assert.AreEqual(0, formatDataLen, "formatDataLength");
Assert.AreEqual(22, ser.Length); // just the TXO record
//read again
RecordInputStream is1 = new RecordInputStream(new MemoryStream(ser));
is1.NextRecord();
record = new TextObjectRecord(is1);
Assert.AreEqual(0, record.Str.Length);
}
public void TestWrite()
{
HSSFRichTextString str = new HSSFRichTextString("Hello, World!");
TextObjectRecord record = new TextObjectRecord();
record.Str = (str);
record.HorizontalTextAlignment = (TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_LEFT_ALIGNED);
record.VerticalTextAlignment = (TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_TOP);
record.IsTextLocked = (true);
record.TextOrientation = (TextObjectRecord.TEXT_ORIENTATION_NONE);
byte[] ser = record.Serialize();
//Assert.AreEqual(ser.Length , data.Length);
//Assert.IsTrue(Arrays.Equals(data, ser));
//Read again
RecordInputStream is1 = new RecordInputStream(new MemoryStream(data));
is1.NextRecord();
record = new TextObjectRecord(is1);
}
/// <summary>
/// Returns the next available record, or null if
/// this pass didn't return a record that's
/// suitable for returning (eg was a continue record).
/// </summary>
/// <returns></returns>
private Record GetNextRecord()
{
Record toReturn = null;
if (in1.HasNextRecord)
{
// Grab our next record
in1.NextRecord();
short sid = in1.Sid;
//
// for some reasons we have to make the workbook to be at least 4096 bytes
// but if we have such workbook we Fill the end of it with zeros (many zeros)
//
// it Is not good:
// if the Length( all zero records ) % 4 = 1
// e.g.: any zero record would be Readed as 4 bytes at once ( 2 - id and 2 - size ).
// And the last 1 byte will be Readed WRONG ( the id must be 2 bytes )
//
// So we should better to Check if the sid Is zero and not to Read more data
// The zero sid shows us that rest of the stream data Is a fake to make workbook
// certain size
//
if (sid == 0)
{
return(null);
}
// If we had a last record, and this one
// Isn't a continue record, then pass
// it on to the listener
if ((rec != null) && (sid != ContinueRecord.sid))
{
// This last record ought to be returned
toReturn = rec;
}
// If this record Isn't a continue record,
// then build it up
if (sid != ContinueRecord.sid)
{
//Console.WriteLine("creating "+sid);
Record[] recs = RecordFactory.CreateRecord(in1);
// We know that the multiple record situations
// don't contain continue records, so just
// pass those on to the listener now
if (recs.Length > 1)
{
bonusRecords = new ArrayList(recs.Length - 1);
for (int k = 0; k < (recs.Length - 1); k++)
{
bonusRecords.Add(recs[k]);
}
}
// Regardless of the number we Created, always hold
// onto the last record to be Processed on the next
// loop, in case it has any continue records
rec = recs[recs.Length - 1];
// Don't return it just yet though, as we probably have
// a record from the last round to return
}
else
{
// Normally, ContinueRecords are handled internally
// However, in a few cases, there Is a gap between a record at
// its Continue, so we have to handle them specially
// This logic Is much like in RecordFactory.CreateRecords()
Record[] recs = RecordFactory.CreateRecord(in1);
ContinueRecord crec = (ContinueRecord)recs[0];
if ((lastRec is ObjRecord) || (lastRec is TextObjectRecord))
{
// You can have Obj records between a DrawingRecord
// and its continue!
lastDrawingRecord.ProcessContinueRecord(crec.Data);
// Trigger them on the drawing record, now it's complete
rec = lastDrawingRecord;
}
else if ((lastRec is DrawingGroupRecord))
{
((DrawingGroupRecord)lastRec).ProcessContinueRecord(crec.Data);
// Trigger them on the drawing record, now it's complete
rec = lastRec;
}
else
{
if (rec is UnknownRecord)
{
;//silently skip records we don't know about
}
else
{
throw new RecordFormatException("Records should handle ContinueRecord internally. Should not see this exception");
}
}
}
// Update our tracking of the last record
lastRec = rec;
if (rec is DrawingRecord)
{
lastDrawingRecord = (DrawingRecord)rec;
}
}
else
{
// No more records
hitEOS = true;
}
// If we've hit the end-of-stream, then
// finish off the last record and be done
if (hitEOS)
{
complete = true;
// Return the last record if there was
// one, otherwise null
if (rec != null)
{
toReturn = rec;
rec = null;
}
}
return(toReturn);
}
public void TestHugeStrings()
{
SSTRecord record = new SSTRecord();
byte[][] bstrings =
{
new byte[9000], new byte[7433], new byte[9002],
new byte[16998]
};
UnicodeString[] strings = new UnicodeString[bstrings.Length];
int total_length = 0;
for (int k = 0; k < bstrings.Length; k++)
{
Arrays.Fill(bstrings[k], (byte)(Convert.ToInt32('a') + k));
strings[k] = new UnicodeString(ConvertByteArrayToString(bstrings[k]));
record.AddString(strings[k]);
total_length += 3 + bstrings[k].Length;
}
// add overhead of SST record
total_length += 8;
// add overhead of broken strings
total_length += 4;
// add overhead of six records
total_length += (6 * 4);
byte[] content = new byte[record.RecordSize];
record.Serialize(0, content);
Assert.AreEqual(total_length, content.Length);
//DeSerialize the record.
RecordInputStream recStream = new RecordInputStream(new MemoryStream(content));
recStream.NextRecord();
record = new SSTRecord(recStream);
Assert.AreEqual(strings.Length, record.NumStrings);
Assert.AreEqual(strings.Length, record.NumUniqueStrings);
Assert.AreEqual(strings.Length, record.CountStrings);
for (int k = 0; k < strings.Length; k++)
{
Assert.AreEqual(strings[k], record.GetString(k));
}
record = new SSTRecord();
bstrings[1] = new byte[bstrings[1].Length - 1];
for (int k = 0; k < bstrings.Length; k++)
{
if ((bstrings[k].Length % 2) == 1)
{
Arrays.Fill(bstrings[k], (byte)('a' + k));
strings[k] = new UnicodeString(ConvertByteArrayToString(bstrings[k]));
}
else
{
char[] data = new char[bstrings[k].Length / 2];
Arrays.Fill(data, (char)(Convert.ToInt32('\u2122') + k));
strings[k] = new UnicodeString(new String(data));
}
record.AddString(strings[k]);
}
content = new byte[record.RecordSize];
record.Serialize(0, content);
total_length--;
Assert.AreEqual(total_length, content.Length);
recStream = new RecordInputStream(new MemoryStream(content));
recStream.NextRecord();
record = new SSTRecord(recStream);
Assert.AreEqual(strings.Length, record.NumStrings);
Assert.AreEqual(strings.Length, record.NumUniqueStrings);
Assert.AreEqual(strings.Length, record.CountStrings);
for (int k = 0; k < strings.Length; k++)
{
Assert.AreEqual(strings[k], record.GetString(k));
}
}
/// <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);
}
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);
}
