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

Original article: C # Use npoi to read and write Excel files

Npoi is A. Net version of the open-source poi project. It can be used to read and write Excel, Word, and pptfiles. Npoi can be compatible with XLS and XLSX when processing Excel files. 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: // Date comes here if (dateutil. iscelldateformatted (cell) {value = cell. datecellvalue;} else {// numeric type value = cell. numericcellvalue;} break; Case celltype. boolean: // boolean type value = cell. booleancellvalue; break; Case celltype. formula: value = cell. cellformula; 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 static 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
Set formula usagecell.SetCellFormula(string formula)

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; // text vertical alignment icellstyle datestyle = WB. createcellstyle (); // style datestyle. alignment = npoi. SS. usermodel. horizontalalignment. left; // horizontal text alignment datestyle. verticalalignment = npoi. SS. usermodel. verticalalignment. center; // text vertical alignment // set the data display format idataformat dataformatcustom = WB. createdataformat (); datestyle. dataformat = dataformatcustom. Getformat ("yyyy-mm-dd hh: mm: SS"); // create a form isheet sheet = WB. createsheet ("sheet0"); // set the column width to int [] columnwidth = {10, 10, 20, 10}; For (INT I = 0; I <columnwidth. length; I ++) {// set the column width, which is 256 * characters, because the unit is 1/256 characters. setcolumnwidth (I, 256 * columnwidth [I]);} // test data int rowcount = 3, columncount = 4; object [,] DATA = {"column 0 ", "column 1", "column 2", "column 3" },{ "", 400, 5.2, 6.01 },{ "", true, "", datetime. Now} // The date can be directly transmitted as a string, npoi will automatically recognize // if it is of the datetime type, set cellstyle. dataformat, otherwise it will be displayed as a number}; irow row; icell cell; For (INT I = 0; I <rowcount; I ++) {ROW = sheet. 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 Object OBJ = data [I, j]; setcellvalue (cell, data [I, j]) based on the data type; // if it is a date, set the date display format if (obj. getType () = typeof (datetime) {Cell. cellstyle = datestyle;} // If You Want To adjust the column width automatically based on the content, You Need To setcellvalue before calling // sheet. autosizecolumn (j) ;}/// 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 ), merge row 0-2 and cell cellrangeaddress region = new cellrangeaddress (0, 2, 0, 0) in column 0-0; sheet. addme Rgedregion (region); try {filestream FS = file. openwrite (filepath); WB. Write (FS); // write a form to the opened Excel file and save it. FS. Close () ;}catch (exception e) {Debug. writeline (E. Message );}}

To set a cell to read-only or writable, refer to the following method:

Icellstyle unlocked = WB. createcellstyle (); unlocked. islocked = false; // set the cell to be unlocked. setcellvalue ("Unlocked"); cell. cellstyle = unlocked ;... // protection form, password is the unlock password // cell. cellstyle. the islocked = true; cell will be read-only sheet. protectsheet ("password ");

cell.CellStyle.IsLockedThe default value is true, sosheet.ProtectSheet("password")You must execute the command to lock cells. For cells that do not want to be locked, you must setcellOfCellStyleInIsLocked = false