Npio Source Address: Https://github.com/tonyqus/npoi
Npio Use reference: Npoitest project in the source code
The following code includes:
1. Excel with multiple sheet
2. Cell Merging
3. Set cell style: field, color
4. Set the cell as a drop-down box and limit the input value
5. Set cells to enter only numbers
// //GET:/excel/ PublicActionResult Write () {varWorkbook =NewHssfworkbook ();//Create a Workbook object from stream contentIsheet sheet = ((hssfworkbook) workbook). Createsheet ("Sheetone");//Create a worksheetIRow Row= Sheet. CreateRow (0);//add a row to a worksheetIcell cell = row. Createcell (1);//add a column to the rowCell. Setcellvalue ("Test");//set the contents of a columnsetcellstyle (workbook, cell); Mergecell (Sheet,0,0,1,4); Sheet= ((hssfworkbook) workbook). Createsheet ("Sheet2");//Create a worksheetsetcelldropdownlist (sheet); Setcellinputnumber (sheet); stringFilePath = Server.MapPath ("~/exportfiles/test.xls"); FileStream FS=NewFileStream (FilePath, FileMode.Create); Workbook. Write (FS); Fs. Close (); return NULL; } /// <summary> ///set the cell as a drop-down box and limit the input value/// </summary> /// <param name= "sheet" ></param> Private voidsetcelldropdownlist (isheet sheet) {//set the rows and columns of the Build drop-down box varCellregions =NewCellrangeaddresslist (0,65535,0,0); //Set drop-down box contentsDvconstraint constraint =Dvconstraint.createexplicitlistconstraint (New string[] {"ItemA","Itemb","ITEMC" }); //Bind the drop box and the action area, and set the error messageHssfdatavalidation datavalidate =Newhssfdatavalidation (cellregions, constraint); Datavalidate.createerrorbox ("Input not valid","Please enter a value from the drop-down list. "); Datavalidate.showpromptbox=true; Sheet. Addvalidationdata (datavalidate); } /// <summary> ///set cells to enter only numbers/// </summary> /// <param name= "sheet" ></param> Private voidsetcellinputnumber (isheet sheet) {//set the rows and columns of the Build drop-down box varCellregions =NewCellrangeaddresslist (0,65535,1,1); //second parameter int comparisonoperator reference source Access//Https://github.com/tonyqus/npoi //Npoitest ProjectDvconstraint constraint =Dvconstraint.createnumericconstraint (Validationtype.integer, Operatortype.between,"0"," -"); Hssfdatavalidation datavalidate=Newhssfdatavalidation (cellregions, constraint); Datavalidate.createerrorbox ("Input not valid","Please enter a 1~100 number. "); //datavalidate.promptboxtitle = "Errorinput";sheet. Addvalidationdata (datavalidate); } /// <summary> ///Merge Cells/// </summary> /// <param name= "sheet" ></param> /// <param name= "FirstRow" ></param> /// <param name= "LastRow" ></param> /// <param name= "FirstCell" ></param> /// <param name= "Lastcell" ></param> Private voidMergecell (Isheet sheet,intFirstRow,intLastRow,intFirstCell,intLastcell) {Sheet. Addmergedregion (NewCellrangeaddress (FirstRow, LastRow, FirstCell, Lastcell));//2.0 use 2.0 below for region } /// <summary> ///Set cell style/// </summary> /// <param name= "Workbook" ></param> /// <param name= "cell" ></param> Private voidSetcellstyle (hssfworkbook workbook, Icell cell) {Hssfcellstyle Fcellstyle=(hssfcellstyle) workbook. Createcellstyle (); Hssffont Ffont=(hssffont) workbook. CreateFont (); Ffont. Fontheight= -* -; Ffont. FontName="Song Body"; Ffont. Color=HSSFColor.Red.Index; Fcellstyle.setfont (Ffont); Fcellstyle.verticalalignment= Npoi. Ss. UserModel.VerticalAlignment.Center;//Vertical AlignmentFcellstyle.alignment = Npoi. Ss. UserModel.HorizontalAlignment.Center;//Horizontal AlignmentCell. CellStyle =Fcellstyle; }
Npoi generate Excel (cell merge, set cell style: field, color, set cell as drop-down box and limit input values, set cells to enter only numbers, etc.)