NPOI read and write Excel (NPOI2.0 includes versions 03 and 07)

NPOI2.0 puts OOXML code in a separate DLL. before using it, make sure that the following DLLNPOI. dllNPOI. OOXML. dllNPOI. OpenXml4Net. dllNPOI. OpenXmlFormats. dll release is referenced --------------------------------------------------------------------------------------------------------

NPOI 2.0 puts OOXML code in a separate DLL. before using it, make sure that the following dll npoi. dll NPOI. OOXML. dll NPOI. OpenXml4Net. dll NPOI. OpenXmlFormats. dll release is referenced --------------------------------------------------------------------------------------------------------

NPOI 2.0 puts OOXML code in a separate DLL. Make sure that the following DLL is referenced before use.

NPOI. dll

NPOI. OOXML. dll

NPOI. OpenXml4Net. dll

NPOI. OpenXmlFormats. dll

Certificate ------------------------------------------------------------------------------------------------------------------------------------------

Http://www.cnblogs.com/luxiaoxun/p/3374992.html

1. The whole Excel table is called a worksheet: WorkBook (working thin), including a page (worksheet): Sheet; Row: Row; Cell.

2. NPOI is the C # version of POI. The index of NPOI rows and columns starts from 0.

3. There are two formats for POI to read Excel: HSSF and XSSF. The differences between HSSF and XSSF are as follows:
HSSF is the POI Project's pure Java implementation of the Excel '97 (-2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
That is, HSSF is applicable to versions earlier than 2007, and XSSF is applicable to versions 2007 and later.

The following is an example of using NPOI to read and write Excel: The ExcelHelper encapsulation function is mainly to write data in the able to Excel, or to read data from Excel to a DataTable.

ExcelHelper class:

Using System; using System. collections. generic; using System. linq; using System. text; using NPOI. SS. userModel; using NPOI. XSSF. userModel; using NPOI. HSSF. userModel; using System. IO; using System. data; namespace GMapDemo {class ExcelHelper: IDisposable {private string fileName = null; // file name private IWorkbook workbook = null; private FileStream fs = null; private bool disposed; public ExcelHelper (string fileName) {this. fileName = fileName; disposed = false ;}////// Import the DataTable data to excel //////Data to be imported///Whether to import the column name of DataTable///Name of the sheet of the excel file to be imported///
 
  
Number of imported data rows (the row containing the column name)
 Public int DataTableToExcel (DataTable data, string sheetName, bool isColumnWritten) {int I = 0; int j = 0; int count = 0; ISheet sheet = null; fs = new FileStream (fileName, FileMode. openOrCreate, FileAccess. readWrite); if (fileName. indexOf (". xlsx ")> 0) // workbook version 2007 = new XSSFWorkbook (); else if (fileName. indexOf (". xls ")> 0) // workbook version 2003 = new HSSFWorkbook (); try {if (workbook! = Null) {sheet = workbook. createSheet (sheetName);} else {return-1;} if (isColumnWritten = true) // name of the column written to the DataTable {IRow row = sheet. createRow (0); for (j = 0; j <data. columns. count; ++ j) {row. createCell (j ). setCellValue (data. columns [j]. columnName) ;}count = 1 ;}else {count = 0 ;}for (I = 0; I <data. rows. count; ++ I) {IRow row = sheet. createRow (count); for (j = 0; j <data. columns. count; ++ j) {row. createCell (j ). setCellValue (data. rows [I] [j]. toString () ;}++ count ;}workbook. write (fs); // Write to excel return count;} catch (Exception ex) {Console. writeLine ("Exception:" + ex. message); return-1 ;}}////// Import data from excel to DataTable //////Name of the excel worksheet///Whether the first row is the column name of the DataTable///
 
  
Returned DataTable
 Public DataTable exceltoable able (string sheetName, bool isFirstRowColumn) {ISheet sheet = null; DataTable data = new DataTable (); int startRow = 0; try {fs = new FileStream (fileName, FileMode. open, FileAccess. read); if (fileName. indexOf (". xlsx ")> 0) // workbook version 2007 = new XSSFWorkbook (fs); else if (fileName. indexOf (". xls ")> 0) // workbook 2003 = new HSSFWorkbook (fs); if (sheetName! = Null) {sheet = workbook. GetSheet (sheetName);} else {sheet = workbook. GetSheetAt (0);} if (sheet! = Null) {IRow firstRow = sheet. getRow (0); int cellCount = firstRow. lastCellNum; // The number of the last cell in a row, that is, the total number of columns if (isFirstRowColumn) {for (int I = firstRow. firstCellNum; I <cellCount; ++ I) {DataColumn column = new DataColumn (firstRow. getCell (I ). stringCellValue); data. columns. add (column);} startRow = sheet. firstRowNum + 1;} else {startRow = sheet. firstRowNum;} // int rowCount = sheet in the last column. lastRowNu M; for (int I = startRow; I <= rowCount; ++ I) {IRow row = sheet. getRow (I); if (row = null) continue; // The default value of a row without data is null DataRow dataRow = data. newRow (); for (int j = row. firstCellNum; j <cellCount; ++ j) {if (row. getCell (j )! = Null) // Similarly, all cells without data are null dataRow [j] = row by default. getCell (j ). toString ();} data. rows. add (dataRow) ;}} return data ;}catch (Exception ex) {Console. writeLine ("Exception:" + ex. message); return null ;}} public void Dispose () {Dispose (true); GC. suppressFinalize (this);} protected virtual void Dispose (bool disposing) {if (! This. disposed) {if (disposing) {if (fs! = Null) fs. Close () ;}fs = null; disposed = true ;}}}}

1. NPOI: http://npoi.codeplex.com/releases/view/38113

2. NPOI Learning Series of tutorials recommended: http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html

Refer:

Http://www.cnblogs.com/Erik_Xu/archive/2012/06/08/2541957.html

Http://www.cnblogs.com/linzheng/archive/2010/12/20/1912137.html

Http://www.cnblogs.com/knowledgesea/archive/2012/11/16/2772547.html