C # Use the npoi library to read and write Excel files (reproduced)

Npoi is A. Net version of the open-source poi project. It can be used to read and write Excel, Word, and pptfiles. In processing Excel files, npoi can simultaneously accommodate XLS and XLSX. The official website provides examples, which provides examples of many application scenarios. The packaged binary file library is only a few MB, which is very convenient to use.

Read Excel

Npoi usageHSSFWorkbookClass to process xls,XSSFWorkbookClass to process XLSX, all of which inherit the interfaceIWorkbookTherefore, you can useIWorkbookTo process files in the XLS and XLSX formats.

The following is a simple example.

Public void readfromexcelfile (string filepath) {iworkbook wk = NULL; string extension = system. io. path. getextension (filepath); try {filestream FS = file. openread (filepath); If (extension. equals (". xls ") {// write the data in the xls file to wk = new hssfworkbook (FS );} else {// write data in the XLSX file to WK where wk = new xssfworkbook (FS);} FS. close (); // read the current table data isheet sheet = wk. getsheetat (0); irow ROW = sheet. getrow (0); // read data from the current row // Lastrownum is the total number of rows in the current table-1 (Note) int offset = 0; For (INT I = 0; I <= sheet. lastrownum; I ++) {ROW = sheet. getrow (I); // read the data of the current row if (row! = NULL) {// lastcellnum is the total number of columns in the current row for (Int J = 0; j <row. lastcellnum; j ++) {// read the data in column J of the row string value = row. getcell (j ). tostring (); console. write (value. tostring () + "");} console. writeline ("\ n") ;}} catch (exception e) {// The console is output only in debug mode. writeline (E. message );}}

Cells in Excel have different data formats, such as numbers, dates, strings, etc. During reading, you can set different types of objects based on different formats to facilitate later data processing.

// Obtain the cell data and set it to the corresponding data type public object getcellvalue (icell cell) {object value = NULL; try {If (cell. celltype! = Celltype. blank) {Switch (cell. celltype) {Case celltype. numeric: // The data celltype of date type is Numeric if (dateutil. iscelldateformatted (cell) {value = cell. datecellvalue;} else {// numeric type value = cell. numericcellvalue;} break; Case celltype. boolean: // boolean type value = cell. booleancellvalue; break; default: // string type value = cell. stringcellvalue; break ;}} catch (exception) {value = "" ;}return value ;}

Special noteYesCellTypeThere is no date, and the Data Type of the date type isNumericIn fact, date data is also stored in Excel as numbers. AvailableDateUtil.IsCellDateFormattedMethod to determine whether it is a date type.

WithGetCellValueWhen writing data to excelSetCellValueMethod. You can add the missing types by yourself.

// Set cellpublic void setcellvalue (icell cell, object OBJ) {If (obj. getType () = typeof (INT) {Cell. setcellvalue (INT) OBJ);} else if (obj. getType () = typeof (double) {Cell. setcellvalue (double) OBJ);} else if (obj. getType () = typeof (irichtextstring) {Cell. setcellvalue (irichtextstring) OBJ);} else if (obj. getType () = typeof (string) {Cell. setcellvalue (obj. tostring ();} else if (obj. getType () = typeof (datetime) {Cell. setcellvalue (datetime) OBJ);} else if (obj. getType () = typeof (bool) {Cell. setcellvalue (bool) OBJ);} else {Cell. setcellvalue (obj. tostring ());}}

cell.SetCellValue()There are only four methods to be overloaded. The parameters arestring,bool,DateTime,double,IRichTextString(Formula usedIRichTextString)

Write Excel

The following is a simple example. For more information, see examples on the official website.

Public void writetoexcel (string filepath) {// create the iworkbook WB; string extension = system. io. path. getextension (filepath); // creates a class if (extension. equals (". xls ") {WB = new hssfworkbook ();} else {WB = new xssfworkbook ();} icellstyle style1 = WB. createcellstyle (); // style style1.alignment = npoi. SS. usermodel. horizontalalignment. left; // horizontal text alignment = npoi. SS. usermodel. ver Ticalalignment. center; // text vertical alignment // set border style1.borderbottom = npoi. SS. usermodel. borderstyle. thin; style1.borderleft = npoi. SS. usermodel. borderstyle. thin; style1.borderright = npoi. SS. usermodel. borderstyle. thin; style1.bordertop = npoi. SS. usermodel. borderstyle. thin; style1.wraptext = true; // icellstyle style2 = WB. createcellstyle (); // style ifont font1 = WB. createfont (); // font1.fontname = ""; Font1.color = hssfcolor. red. index; // font1.boldweight = (short) fontboldweight. normal; // font bold style style2.setfont (font1); // set the font style in the style. // set the background color style2.fillforegroundcolor = npoi. hssf. util. hssfcolor. yellow. index; style2.fillpattern = fillpattern. solidforeground; style2.fillbackgroundcolor = npoi. hssf. util. hssfcolor. yellow. index; style2.alignment = npoi. SS. usermodel. horizontalalignment. left; // text level Alignment = npoi. SS. usermodel. verticalalignment. center; // vertical text alignment // create a table isheet TB = WB. createsheet ("sheet0"); // set the column width int [] columnwidth = {10, 10, 10, 20}; // test data int rowcount = 3, columncount = 4; object [,] DATA = {"column 0", "column 1", "column 2", "column 3" },{ "", 400, 5.2, 6.01 }, {"", datetime. today, true, "2014-07-02" }}; for (INT I = 0; I <columnwidth. length; I ++) {// set the column width, which is 256 * characters, because the unit is 1/256 characters TB. setcolumnwidth (I, 256 * columnwidth [I]);} irow row; icell cell; For (INT I = 0; I <rowcount; I ++) {ROW = TB. createrow (I); // create row I for (Int J = 0; j <columncount; j ++) {Cell = row. createcell (j); // create cell in column J. cellstyle = J % 2 = 0? Style1: style2; // set different types of cell setcellvalue (cell, data [I, j]) according to the data type; }}// merge cells, if data exists in the cells to be merged, only the // cellrangeaddress (0, 2, 0, 0) in the upper left corner is retained and the row 0-2 is merged, cellrangeaddress region = new cellrangeaddress (0, 2, 0, 0); TB. addmergedregion (region); try {filestream FS = file. openwrite (filepath); WB. write (FS); // write the table to the opened xls file and save it. FS. Close () ;}catch (exception e) {Debug. writeline (E. Message );}}

 

Reprinted from: http://www.cnblogs.com/restran/p/3889479.html

Related information:

Npoi Official Website: http://npoi.codeplex.com/

Npoi Daquan: http://www.cnblogs.com/atao/category/209358.html

: Npoi 2.1 examples, npoi 2.1.1 binary