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); }