Shulou(Shulou.com)06/03 Report--

This article shows you how to use .NET to operate the Excel component NPOI, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

NPOI can generate Microsoft Office reports that are not installed on your server, and it is more efficient to call Microsoft Excel ActiveX in the background; extract text from Office documents to help you achieve full-text indexing functionality (most of the time, this feature is used to create search engines); extract images from Office documents; generate Excel worksheets that contain formulas.

I. Overview of NPOI components:

NPOI is completely free to use; covers most of the features of Excel (cell styles, data formats, formulas, etc.); supports xls,xlsx,docx; design for interface-oriented (see NPOI.SS namespace); supports not only export but also import; .net 2.0 even for xlsx and docx (although we also support .NET 4.0); success stories from all over the world; huge basic examples; no dependence on isolated storage.

The above are the advantages of NPOI, and some other advantages can be ignored. It is estimated that many people are a little surprised by the feature of "supporting xls,xlsx,docx", because in the impression of many people, NPOI is related to Excel, but here suddenly see that it can also be operated on docx. This feature may make many people happy, because NPOI is indeed a very good component, and people who have used it say yes. I am no exception.

The running requirements of NPOI are: VS2010 and .NET 4.0 runtime; VS2005 or VS2008 and .NET 2.0 runtime (SP1); vs2003 and .NET 1.1 and the medium trust environment in ASP. Net.

II. Parsing of NPOI core classes and methods:

The above is a brief introduction to the relevant background and environment of NPOI, and then I take a specific look at some core classes and methods of NPOI, because I download the DLL file, or use the .NET Reflector to decompile the DLL file, so as to view the source code.

If you need to know more about NPOI, you can visit directly: http://npoi.codeplex.com/SourceControl/latest, providing the source code of NPOI and some demo. Since Benxi focuses on the operation of NPOI on Excel, the following classes and examples are mainly an introduction to the operation of Excel. If you need an operation on docx, you can check the corresponding class demo.

The 1.XSSFWorkbook class CreateSheet (): creates a table.

Public ISheet CreateSheet (string sheetname) {if (sheetname = = null) {throw new ArgumentException ("sheetName must not be null");} if (this.ContainsSheet (sheetname, this.sheets.Count)) {throw new ArgumentException ("The workbook already contains a sheet of this name");} if (sheetname.Length > 0x1f) {sheetname = sheetname.Substring (0, 0x1f);} WorkbookUtil.ValidateSheetName (sheetname); CT_Sheet sheet = this.AddSheet (sheetname); int index = 1 Foreach (XSSFSheet sheet2 in this.sheets) {index = (int) Math.Max ((long) (sheet2.sheet.sheetId + 1), (long) index);} Label_0099: foreach (XSSFSheet sheet3 in this.sheets) {index = (int) Math.Max ((sheet3.sheet.sheetId + 1), (long) index);} string fileName = XSSFRelation.WORKSHEET.GetFileName (index) Foreach (POIXMLDocumentPart part in base.GetRelations ()) {if ((part.GetPackagePart ()! = null) & & fileName.Equals (part.GetPackagePart () .Partname.Name)) {index++; goto Label_0099;}} XSSFSheet item = (XSSFSheet) base.CreateRelationship (XSSFRelation.WORKSHEET, XSSFFactory.GetInstance (), index); item.sheet = sheet; sheet.id = item.GetPackageRelationship (). Id; sheet.sheetId = (uint) index If (this.sheets.Count = = 0) {item.IsSelected = true;} this.sheets.Add (item); return item;}

The 2.XSSFSheet class Write (): writes a file stream to excel.

Internal virtual void Write (Stream stream) {bool flag = false; if (this.worksheet.sizeOfColsArray () = = 1) {CT_Cols colsArray = this.worksheet.GetColsArray (0); if (colsArray.sizeOfColArray () = = 0) {flag = true; this.worksheet.SetColsArray (null);} else {this.SetColWidthAttribute (colsArray) }} if (this.hyperlinks.Count > 0) {if (this.worksheet.hyperlinks = = null) {this.worksheet.AddNewHyperlinks ();} CT_Hyperlink [] array = new CT_ Hyperlink [this.hyperlinks.Count]; for (int I = 0; I

< array.Length; i++) { XSSFHyperlink hyperlink = this.hyperlinks[i]; hyperlink.GenerateRelationIfNeeded(base.GetPackagePart()); array[i] = hyperlink.GetCTHyperlink(); } this.worksheet.hyperlinks.SetHyperlinkArray(array); } foreach (XSSFRow row in this._rows.Values) { row.OnDocumentWrite(); } Dictionary dictionary = new Dictionary(); dictionary[ST_RelationshipId.NamespaceURI] = "r"; new WorksheetDocument(this.worksheet).Save(stream); if (flag) { this.worksheet.AddNewCols(); }} 3.XSSFSheet类CreateRow()：创建行。 public virtual IRow CreateRow(int rownum){ CT_Row cTRow; XSSFRow row2 = this._rows.ContainsKey(rownum) ? this._rows[rownum] : null; if (row2 != null) { cTRow = row2.GetCTRow(); cTRow.Set(new CT_Row()); } else if ((this._rows.Count == 0) || (rownum >

This.GetLastKey (this._rows.Keys)) {cTRow = this.worksheet.sheetData.AddNewRow ();} else {int count = this.HeadMap (this._rows, rownum) .Count; cTRow = this.worksheet.sheetData.InsertNewRow (count);} XSSFRow row3 = new XSSFRow (cTRow, this) {RowNum = rownum}; this._ rows [rownum] = row3; return row3;}

4.XSSFWorkbook class GetSheet: get the table.

Public ISheet GetSheet (string name) {foreach (XSSFSheet sheet in this.sheets) {if (name.Equals (sheet.SheetName, StringComparison.InvariantCultureIgnoreCase)) {return sheet;}} return null;}

5.WorkbookFactory class:

Public class PropertySetFactory {public static PropertySet Create (DirectoryEntry dir, string name); public static PropertySet Create (Stream stream); public static SummaryInformation CreateSummaryInformation (); public static DocumentSummaryInformation CreateDocumentSummaryInformation ();}

6.DocumentSummaryInformation:

[Serializable] public class DocumentSummaryInformation: SpecialPropertySet {/ / Fields public const string DEFAULT_STREAM_NAME = "\ x0005DocumentSummaryInformation"; / / Methods public DocumentSummaryInformation (PropertySet ps); private void EnsureSection2 (); public void RemoveByteCount (); public void RemoveCategory (); public void RemoveCompany (); public void RemoveCustomProperties (); public void RemoveDocparts (); public void RemoveHeadingPair (); public void RemoveHiddenCount (); public void RemoveLineCount (); public void RemoveLinksDirty (); public void RemoveManager (); public void RemoveMMClipCount (); public void RemoveNoteCount () Public void RemoveParCount (); public void RemovePresentationFormat (); public void RemoveScale (); public void RemoveSlideCount (); / Properties public int ByteCount {get; set;} public string Category {get; set;} public string Company {get; set;} public CustomProperties CustomProperties {get; set;} public byte [] Docparts {get; set;} public byte [] HeadingPair {get; set;} public int HiddenCount {get; set;} public int LineCount {get; set;} public bool LinksDirty {get; set } public string Manager {get; set;} public int MMClipCount {get; set;} public int NoteCount {get; set;} public int ParCount {get; set;} public string PresentationFormat {get; set;} public override PropertyIDMap PropertySetIDMap {get;} public bool Scale {get; set;} public int SlideCount {get; set;}}

Specific methods:

Private void EnsureSection2 () {if (this.SectionCount

< 2) { MutableSection section = new MutableSection(); section.SetFormatID(SectionIDMap.DOCUMENT_SUMMARY_INFORMATION_ID2); this.AddSection(section); }} 以上只是对部分的类和方法做了简单的说明，需要了解更多的内容，可以进入官网下载源码，或者使用软件查看DLL文件。 三.NPOI操作实例： 1.枚举(Excel单元格数据类型)： /// /// 枚举(Excel单元格数据类型) /// public enum NpoiDataType { /// /// 字符串类型-值为1 /// String, /// /// 布尔类型-值为2 /// Bool, /// /// 时间类型-值为3 /// Datetime, /// /// 数字类型-值为4 /// Numeric, /// /// 复杂文本类型-值为5 /// Richtext, /// /// 空白 /// Blank, /// /// 错误 /// Error } 2. 将DataTable数据导入到excel中： /// /// 将DataTable数据导入到excel中 /// /// 要导入的数据 /// DataTable的列名是否要导入 /// 要导入的excel的sheet的名称 /// 文件夹路径 /// 导入数据行数(包含列名那一行) public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string fileName) { if (data == null) { throw new ArgumentNullException("data"); } if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException(sheetName); } if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } IWorkbook workbook = null; if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) >

0) {workbook = new XSSFWorkbook ();} else if (fileName.IndexOf (".xls", StringComparison.Ordinal) > 0) {workbook = new HSSFWorkbook ();} FileStream fs = null; try {fs = new FileStream (fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); ISheet sheet If (workbook! = null) {sheet = workbook.CreateSheet (sheetName);} else {return-1;} int j; int count; / / write the column name of DataTable, write if (isColumnWritten) {var row = sheet.CreateRow (0) in the cell; for (j = 0; j)

< data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } //遍历循环datatable具体数据项 int i; for (i = 0; i < data.Rows.Count; ++i) { var row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } //将文件流写入到excel workbook.Write(fs); return count; } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (fs != null) { fs.Close(); } } } 3.将excel中的数据导入到DataTable中： /// /// 将excel中的数据导入到DataTable中 /// /// excel工作薄sheet的名称 /// 第一行是否是DataTable的列名 /// 文件路径 /// 返回的DataTable public static DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName) { if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException(sheetName); } if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } var data = new DataTable(); IWorkbook workbook = null; FileStream fs = null; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) >

0) {workbook = new XSSFWorkbook (fs);} else if (fileName.IndexOf (".xls", StringComparison.Ordinal) > 0) {workbook = new HSSFWorkbook (fs);} ISheet sheet = null If (workbook! = null) {/ / if the sheet corresponding to the specified sheetName is not found, try to get the first sheet sheet = workbook.GetSheet (sheetName)? Workbook.GetSheetAt (0);} if (sheet = = null) return data; var firstRow = sheet.GetRow (0); / / the number of the last cell on a line is the total number of columns int cellCount = firstRow.LastCellNum; int startRow; if (isFirstRowColumn) {for (int I = firstRow.FirstCellNum; I < cellCount) + + I) {var cell = firstRow.GetCell (I); var cellValue = cell.StringCellValue; if (cellValue = = null) continue; var column = new DataColumn (cellValue); data.Columns.Add (column);} startRow = sheet.FirstRowNum + 1;} else {startRow = sheet.FirstRowNum } / / the label of the last column var rowCount = sheet.LastRowNum; for (var I = startRow; I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.