C # using NPOI to Operate Excel,

C # using NPOI to Operate Excel,

Reference page:

Http://www.yuanjiaocheng.net/Jsp/first.html

Http://www.yuanjiaocheng.net/Struts/first.html

Http://www.yuanjiaocheng.net/Hibernate/first.html

Http://www.yuanjiaocheng.net/Entity/first.html

Http://www.yuanjiaocheng.net/Entity/jieshao.html

C # There are many ways to Operate Excel. Common methods include Microsoft's official OLE Automation and Apache POI. This section describes how to translate POI into NPOI of C.

POI is an API for Apache to operate Office through Java. It is very powerful for Excel, Word, and PPT operations. Then it is translated into NPOI of C #, which is similar to log4j and log4net.

It seems that office2007 and later XML formats are not supported before NPOI. net4.0, but the latest version is supported. You only need to download and reference the following five sets.

Here is a class for Excel operations. The class provides four methods, two exports, and two imports. You can use DataSet to export an Excel file with multiple sheets or DataTable to export an Excel file with one Sheet. The same is true for importing data. You can specify the Sheet index to export the able, or directly export all sheets to return a DataSet.

　　

Public class ExcelHelper {// <summary> /// DataTable is returned Based on Excel and Sheet /// </summary> /// <param name = "filePath"> Excel file address </param> /// <param name = "sheetIndex"> Sheet index </param> /// <returns> DataTable </returns> public static DataTable GetDataTable (string filePath, int sheetIndex) {return GetDataSet (filePath, sheetIndex ). tables [0] ;}/// <summary> /// return DataSet /// </summary> /// <param name = "f IlePath "> Excel file address </param> // <param name =" sheetIndex "> Sheet Index, optional, all Sheet </param> /// <returns> DataSet </returns> public static DataSet GetDataSet (string filePath, int? SheetIndex = null) {DataSet ds = new DataSet (); IWorkbook fileWorkbook; using (FileStream fs = new FileStream (filePath, FileMode. open, FileAccess. read) {if (filePath. last () ='s ') {try {fileWorkbook = new HSSFWorkbook (fs);} catch (Exception ex) {throw ex ;}} else {try {fileWorkbook = new XSSFWorkbook (fs);} catch {fileWorkbook = new HSSFWorkbook (fs) ;}}for (int I = 0; I <fileWorkb Ook. NumberOfSheets; I ++) {if (sheetIndex! = Null & sheetIndex! = I) continue; DataTable dt = new DataTable (); ISheet sheet = fileWorkbook. getSheetAt (I); // header IRow header = sheet. getRow (sheet. firstRowNum); List <int> columns = new List <int> (); for (int j = 0; j