Working with Excel using the Npoi plugin in ASP. (Import, export, merge cells, set style, enter formula)

Preface

Npoi is a POI project. NET version, which does not use Office COM components and does not require Microsoft office to be installed, currently supports versions of Office 2003 and 2007.

1, the entire Excel table is called the worksheet: WorkBook (Workbook), contains the called page (sheet): Sheet; row: row; cell.

2, Npoi is the C # version of POI, Npoi row and column index are starting from 0

3, POI read Excel has two formats one is HSSF, the other is XSSF. The differences between HSSF and XSSF are as follows:
HSSF is the POI Project's pure Java implementation of the Excel ' ( -2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel OOXML (. xlsx) file format.
That is: HSSF applies to versions prior to 2007, XSSF is applicable to version 2007 and above.

This article will talk about how to use Npoi to export Excel, import Excel, merge cells, set styles, and enter the contents of a formula.

You need to download the Npoi plug-in package before using Npoi

: Https://archive.codeplex.com/?p=npoi

Npoi Exporting Excel

In MVC, you can use Fileresult to export Excel output to the front-end download

        <summary>//Export data///</summary>//<returns></returns>            Public Fileresult EXPORTJK (string Keyword) {Tablepager tp = new Tablepager (); Tp.            Keyword = Keyword;            DateTime now = DateTime.Now;            Random rad = new Random (); int num = rad.            Next (10, 100);            string pathstring = Request.applicationpath;            var MapPath = Server.MapPath (pathstring); var Domainpath = AppDomain.CurrentDomain.GetData ("DataDirectory").            ToString (); if (! Directory.Exists (String. Format ("{0}\\report", Domainpath)) {directory.createdirectory (string.            Format ("{0}\\report", domainpath)); }//string FileName = string.            Format (@ "{0}\temp\summarysalary_{1}.xlsx", MapPath, Stringhelper.getrandom (8)); String fileName = String.Format (@ "{0}\\report\\invoice{2}_{1}.xlsx", Domainpath, Num. ToString (), now. ToString ("YyyymmDdhhmmss ")); string download = string. Format (@ "invoice{1}_{0}.xlsx", Num. ToString (), now.            ToString ("Yyyymmddhhmmss"));            Ideliverservice _service = coreservicefactory.used.build<ideliverservice> (); List<exporttemplate1> Exps = _service.exportprinttable (this.            Appuser, TP);            System.IO.FileStream FS;                try {//Create an Excel file object Iworkbook workbook = new Hssfworkbook (); Add a sheet Npoi. Ss. Usermodel.isheet Sheet1 = workbook.                Createsheet ("Sheet1"); Add the header title Npoi of the first line to Sheet1. Ss. Usermodel.irow Row1 = Sheet1.                CreateRow (0); Row1. Createcell (0).                Setcellvalue ("Export waybill"); Row1. Createcell (1).                Setcellvalue ("channel Code"); Row1. Createcell (2).                Setcellvalue ("channel name"); Row1. Createcell (3).                Setcellvalue ("Sales Team"); Row1. Createcell (4).                Setcellvalue ("Sales Staff"); Row1. Createcell (5). SetcellvaluE ("Warehouse"); Row1. Createcell (6).                Setcellvalue ("courier Company"); Row1. Createcell (7).                Setcellvalue ("Sales number"); Row1. Createcell (8).                Setcellvalue ("Customer Name"); Row1. Createcell (9).                Setcellvalue ("Making a single Time"); Row1. Createcell (10).                Setcellvalue ("Invoice amount"); Row1. Createcell (11).                Setcellvalue ("collection"); Row1. Createcell (12).                Setcellvalue ("waybill number"); Progressively writes data to Sheet1 individual lines for (int i = 0; i < Exps. Count; i++) {Npoi. Ss. Usermodel.irow rowtemp = Sheet1.                    CreateRow (i + 1); Rowtemp. Createcell (0). Setcellvalue (Exps[i].                    Invoiceno); Rowtemp. Createcell (1). Setcellvalue (Exps[i].                    Saleschannelcode); Rowtemp. Createcell (2). Setcellvalue (Exps[i].                    Saleschannelname); Rowtemp. Createcell (3). Setcellvalue (Exps[i].                    Salesgroupname); Rowtemp. Createcell (4). Setcellvalue (Exps[i].                    Salesusername); RoWtemp. Createcell (5). Setcellvalue (Exps[i].                    Warehousename); Rowtemp. Createcell (6). Setcellvalue (Exps[i].                    Shippername); Rowtemp. Createcell (7). Setcellvalue (Exps[i].                    OrderNo); Rowtemp. Createcell (8). Setcellvalue (Exps[i].                    CustomerName); Rowtemp. Createcell (9). Setcellvalue (Exps[i].                    CreateDate); Rowtemp. Createcell (10). Setcellvalue (Exps[i].                    Receivable.tostring ("n")); Rowtemp. Createcell (11). Setcellvalue (Exps[i].                    Agencyfund.tostring ("n")); Rowtemp. Createcell (12). Setcellvalue (Exps[i].                Logisticcode);                }//write to Client fs = new FileStream (FileName, FileMode.OpenOrCreate); Workbook.                Write (FS); Fs.                Seek (0, Seekorigin.begin);            FS = Nexcelhelper.datatabletoexcel<exporttemplate1> (Exps, FileName, "Not Shipped list", True, ""); } catch (Exception) {throw new ExCeption ();        } return File (FS, "application/vnd.ms-excel", download); }

Effects such as

Npoi Importing Excel

Importing Excel generally means using EXECL to save data in bulk to the database, so the relative export is much more cumbersome and rigorous.

1. Excel template download available on the front page

2. Front-End selection file

Note that the form form needs to be enctype= "Multipart/form-data" to allow the request to commit the file type

3. Background program

        <summary>//Import commodity data///</summary>//<param name= "FilePath" ></par am>//<param name= "user" ></param>///<returns></returns> public Msgresu            Lt Importgoods (string FilePath, ApplicationUser user) {Msgresult result = new Msgresult (); Result.            Errornote = new list<errornote> ();            DateTime now = DateTime.Now;            var dt = Nexcelhelper.importexcelfile (FilePath, "Sheet1", false); if (dt. Rows.Count <= 0) {result.                ResultCode = Resultcode.error; Result.                Message = "There are no lines detected in the file";            return result; } if (dt. Columns.count! =) {result.                ResultCode = Resultcode.error; Result.                Message = "The file was detected with an incorrect data format";            return result; }//Transaction using (dbcontexttransaction dbtransaction = ProducTRepository.EFDb.Database.BeginTransaction ()) {try {for (int i = 0; i < dt. Rows.Count; i++) {Decimal UnitPrice = 0.00M;//Price Decimal cost = 0.0  0M;  Input decimal minprice = 0.00M;                        Minimum decimal maxprice = 0.00M;                        int isallow = 1; String brandname = dt. Rows[i][0].                        ToString (); String proname = dt. ROWS[I][2].                        ToString (); String prounitprice = dt. ROWS[I][7].                        ToString (); String procost = dt. ROWS[I][6].                        ToString (); String prominprice = dt. ROWS[I][8].                        ToString (); String promaxprice = dt. ROWS[I][9].                        ToString (); String proisdecimal = dt. ROWS[I][11].                        ToString (); BOOL IsSucc1 = decimal.     TryParse (Prounitprice, out UnitPrice);                   BOOL ISSUCC2 = decimal.                        TryParse (Procost, out cost); BOOL IsSucc3 = decimal.                        TryParse (Prominprice, out minprice); BOOL IsSucc4 = decimal.                        TryParse (Promaxprice, out maxprice); Int.                        TryParse (Proisdecimal, out isallow); if (string. Isnullorwhitespace (brandname)) {throw new Exception ("The brand is detected empty!                        "); } list<brand> brands = brandrepository.querybrands (t = = T.brandname = brandname &&amp ; T.state! = state.deleted).                        ToList (); if (brands! = NULL) {if (brands.  Count > 1) {throw new Exception ("System detected brand name" + brandname + " There is ambiguity!                            "); }} if (String.           Isnullorwhitespace (Proname))             {throw new Exception ("The product name is detected to be empty!                        "); } if (string. Isnullorwhitespace (Prounitprice)) {throw new Exception ("The detected sales amount is empty!")                        "); } if (! ISSUCC1) {throw new Exception ("The system detects that a price is not an amount type!                        "); } if (!string. Isnullorwhitespace (procost) &&! ISSUCC2) {throw new Exception ("The system has detected that there is not an amount type!                        "); } if (!string. Isnullorwhitespace (prominprice) &&! ISSUCC3) {throw new Exception ("The system has detected that the lowest selling price is not an amount type!                        "); } if (!string. Isnullorwhitespace (prominprice) &&! ISSUCC4) {thRow New Exception ("The system has detected that the maximum price is not an amount type!")                        ");                        } Product PD = new product (); Pd. Brandid = brands. FirstOrDefault ().                        Brandid; Pd. BarCode = dt. ROWS[I][1].                        ToString (); Pd.                        ProductName = Proname; Pd. shortname = dt. ROWS[I][3].                        ToString (); Pd. Spec = dt. ROWS[I][4].                        ToString (); Pd. Unit = dt. ROWS[I][5].                        ToString (); Pd.                        Cost = Cost; Pd.                        UnitPrice = UnitPrice; Pd.                        Minprice = Minprice; Pd.                        Maxprice = Maxprice; Pd. Remark = dt. ROWS[I][10].                        ToString (); Pd.                        Allowthedecimal = Isallow = = 1; Pd.                        Status = Productstatus.normal;                        int res = productrepository.insertforproduct (PD);                      if (res <= 0) {      throw new Exception ("The first" + i + "line insert failed, please check");                }} dbtransaction.commit ();                    } catch (Exception ex) {dbtransaction.rollback (); Result.                    ResultCode = resultcode.exception; Result. Message = ex.                    Message;                return result; }} result.            ResultCode = resultcode.success; Result. Message = "Product Batch import successful!"            ";        return result; }

In the code I used the whole process of things, and added some necessary validation to ensure the security of the data, authenticity.

Set cell style

When setting a cell style, be aware that you must create a new style object to set it, otherwise the style of all cells in the worksheet will be set together, and they should share a style object:

Icellstyle style = workbook. Createcellstyle ();//Set the style of the cell: Horizontal alignment Center style. Alignment = horizontalalignment.center;//Create a new font style object

IFont font = workbook. CreateFont (); //Set font bold style

Font. Boldweight = short. MaxValue;

Style. SetFont (font);//assigns a new style to cells cell. CellStyle = style;

set the height of the cell: actually set its row high, so to set the row height on the cell row, the row height setting value seems to be 1/20 pixels, so *20 to achieve the setting effect;

set the width of the cell: It is actually set to the width of the column, so to set it on the cell's column (the column's setting on the worksheet), the width value appears to be 1/256 of the character, so *256 to achieve the set effect.

Sets the height of the cell row. Height = 30 * 20;//Set the width of the cell

Sheet. Setcolumnwidth (0, 30 * 256);

Merge Cells: The merged cell is actually declaring an area in which the cells are merged, and the merged content and style are based on the cell in the upper-left corner of the region.

Set a merged cell range, using the up and down definitions cellrangeaddress area//cellrangeaddress Four parameters are: Start line, end row, start column, end column sheet. Addmergedregion (New cellrangeaddress (0, 0, 0, 10));

Add formula: use the cell's Cellformula to set the formula, which is a string that does not need to be added before the formula.

Write the formula to the cell through the cell's Cellformula//NOTE: Directly write the formula content, do not need to add ' = ' icell cell2 = sheet. CreateRow (1). Createcell (0); cell2. Cellformula = "HYPERLINK (\" Test picture. Jpg\ ", \" test picture. Jpg\ ")";

To write a workbook to a file to see the effect:

Write the workbook to a file using (FileStream fs = new FileStream ("Generate effects. xls", FileMode.Create, FileAccess.Write)) {    workbook. Write (fs);}

Effects such as:

PS: Welcome to scan the QR code below or click on the link, join QQ group

Working with Excel using the Npoi plugin in ASP. (Import, export, merge cells, set style, enter formula)