C # using Nopi to manipulate Excel

Recent projects need to export Excel, find out, Microsoft has its own Excel component using Microsoft.Office.Core; usingMicrosoft.Office.Interop.Excel;，但是有一个毛病，就是程序所在电脑安装Office,这个问题简直是致命的，因为导出服务我们要做在服务端，程序直接生成Excel,然后客户端路径去下载，所以我们不可能在部署服务的时候还要在服务器上安装office.最后终于发现有个NOPI库，可以很好的解决这个问题，现在就将项目的Excel 片段记录一下

Npoi, as the name implies, is the. NET version of POI. What about the POI? Poi is a set of Java-written libraries that help developers read and write office 97-2003 files without Microsoft Office installed, including XLS, Doc, ppt, and other file formats. At the time this article was released, the latest version of the POI was 3.5 beta 6. Npoi 1.x is developed based on the POI 3.x version, and the version corresponding to POI 3.2 is Npoi 1.2,

Now we're going to make such a table, design the font style, merge the cells.

Create header styles, column styles, and body styles

  Public StaticIcellstyle Createheaderstyle (Iworkbook book) {Icellstyle style=Book .            Createcellstyle (); //set the style of a cell: horizontal Align CenterStyle. Alignment =HorizontalAlignment.Center; Style. VerticalAlignment=Verticalalignment.center; //Create a new font style objectIFont Font =Book .            CreateFont (); //set font bold styleFont. Boldweight = Short.            MaxValue; Font. Fontheightinpoints= -; Font. Boldweight= ( Short) Fontboldweight.bold; Font. FontName="Microsoft Ya-Black"; //use the SetFont method to add a font style to a cell stylestyle.            SetFont (font); returnstyle; }         Public StaticIcellstyle Createtitlestyle (Iworkbook book) {Icellstyle CellStyle=Book .            Createcellstyle (); Cellstyle.alignment=HorizontalAlignment.Center; Cellstyle.verticalalignment=Verticalalignment.center; IFont Fontleft=Book .            CreateFont (); Fontleft.fontheightinpoints= the; Fontleft.boldweight= ( Short) Fontboldweight.bold; Fontleft.fontname="Song Body"; Cellstyle.shrinktofit=true;            Cellstyle.setfont (Fontleft); returnCellStyle; }         Public StaticIcellstyle Createcontentstyle (Iworkbook book) {Icellstyle CellStyle=Book .            Createcellstyle (); IFont Fontleft=Book .            CreateFont (); Fontleft.fontheightinpoints= the; Fontleft.fontname="Song Body"; Cellstyle.shrinktofit=true;            Cellstyle.setfont (Fontleft); returnCellStyle; }

An Excel file is Iworkbook a page is a isheet, line is IRow, a cell is Icell, know these are good to run. The above is the creation of various styles,

To create a table, it is important to note that the XLSX format file requires new Xssfworkbook (), and the creation of the XLS format file requires the new Hssfworkbook ();

Merge cell sheet. Addmergedregion (New cellrangeaddress (0, 0, 0, 10))

Create Rows IRow Rowheader = sheet. CreateRow (0);

Create page Isheet sheet = Book. Createsheet (dt. TableName);

Create cells cell = Rowtitle.createcell (i);

 #regionWrite Excel File//Hssfworkbook book = new Hssfworkbook ();Iworkbook book =NULL; if(filepath. IndexOf (". xlsx") >0)//version 2007Book =NewXssfworkbook (); Else if(filepath. IndexOf (". xls") >0)//Version 2003Book =NewHssfworkbook (); Isheet sheet=Book . Createsheet (dt.                TableName); //Create an Excel header, merge cell 10 columnsSheet. Addmergedregion (NewCellrangeaddress (0,0,0,Ten)); IRow Rowheader= Sheet. CreateRow (0); //in line: Create cell, parameter is column number, from 0Icell Cellheader = Rowheader.createcell (0); //Set cell contentsCellheader.setcellvalue ("Health Integrated Machine test report"); Cellheader.cellstyle=Createheaderstyle (book); Rowheader.height=650; Rowheader.rowstyle=Createheaderstyle (book); //Create an Excel columnIRow rowtitle = sheet. CreateRow (1); Rowtitle.height= -; Icell Cell=NULL;  for(inti =0; I <9; i++) {cell=Rowtitle.createcell (i); Cell. Setcellvalue (dt. Columns[i].                    ColumnName); Cell. CellStyle=Createtitlestyle (book); } cell= Rowtitle.createcell (9); Cell. Setcellvalue ("Heart Power"); Sheet. Addmergedregion (NewCellrangeaddress (1,1,9, at)); Cell. CellStyle=Createtitlestyle (book); Cell= Rowtitle.createcell ( -); Cell. Setcellvalue ("blood pressure"); Sheet. Addmergedregion (NewCellrangeaddress (1,1, -, to)); Cell. CellStyle=Createtitlestyle (book); Cell= Rowtitle.createcell ( +); Cell. Setcellvalue ("Blood Oxygen"); Sheet. Addmergedregion (NewCellrangeaddress (1,1, +, -)); Cell. CellStyle=Createtitlestyle (book); Cell= Rowtitle.createcell ( the); Cell. Setcellvalue ("Body temperature"); Sheet. Addmergedregion (NewCellrangeaddress (1,1, the, *)); Cell. CellStyle=Createtitlestyle (book); Cell= Rowtitle.createcell ( $); Cell. Setcellvalue ("Blood sugar"); Sheet. Addmergedregion (NewCellrangeaddress (1,1, $,Panax Notoginseng)); Cell. CellStyle=Createtitlestyle (book); Cell= Rowtitle.createcell ( -); Cell. Setcellvalue ("Uric Fluid"); Sheet. Addmergedregion (NewCellrangeaddress (1,1, -, -)); Cell. CellStyle=Createtitlestyle (book); Rowtitle= Sheet. CreateRow (2);  for(inti =9; I <= -; i++) {cell=Rowtitle.createcell (i); Cell. Setcellvalue (dt. Columns[i].                    ColumnName); Cell. CellStyle=Createtitlestyle (book); }                 for(inti =0; I <9; i++) {sheet. Addmergedregion (NewCellrangeaddress (1,2, I, i)); }                //Start writing data                 for(inti =0; i < dt. Rows.Count; i++) {IRow rowcontent= Sheet. CreateRow (i +3); Rowcontent.height= -;  for(intj =0; J < dt. Columns.count; J + +) {cell=Rowcontent.createcell (j); if(Cell! =NULL) {cell. Setcellvalue (convert.tostring (dt.                            ROWS[I][J])); Cell. CellStyle=Createcontentstyle (book); }                    }                }                //Write to client                using(System.IO.MemoryStream ms =NewSystem.IO.MemoryStream ()) {Book.                    Write (MS); using(FileStream fs =NewFileStream (filepath, FileMode.Create, FileAccess.Write)) {                        byte[] D =Ms.                        ToArray (); Fs. Write (d,0, d.length); Fs.                    Flush (); } Book=NULL; }                #endregion

Knowing that these can basically create a slightly more complex table, but before that, you have to introduce the Nopi library,

Official website: http://npoi.codeplex.com/, download the latest version of the library, it should be 2.3 version now. After downloading, go to Npoi-master\npoi-master\solution\visualstudio, open OOXML.sln, rebuild DLL, in npoi-master\npoi-master\solution \lib this directory. As shown below

What we need is a NOPI.DLL, a nopi. OOXML. DLL, Nopi. Openxml4net. Dll,icsharpcode.sharpziplib.dll.npoi.openxmlformats.dll these five libraries into the project. Now compile the library for everyone to download

Https://files.cnblogs.com/files/techdreaming/lib.zip

C # using Nopi to manipulate Excel