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.)

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.)