ASP. NET export Excel Extension (custom merged cells, non-Office components)

Combined with the last-written export Excel method, this time, I was asked to merge the column headers.

Previous effects:

The improved Effect:

In the previous article, I wrote about Excel's Export method, this time to avoid the hassle of using Office components in a production environment for various server permissions, and not to use Office components to generate Excel this time.

Previous article: ASP. NET export Excel (HTML output)

Key codes such as:

Here, in fact, the main idea is already very obvious.

After the end of data binding, redrawing the header replaces the original table header.

TableCell is a table label when customizing the header, it is convenient.

In addition, a small trick was found:

I use Office Excel 2013 to draw a good header, then copy it directly, paste it in the design panel of Macromedia Dreamweaver 8, and then go to the code page to get the table code quickly.

Finally, put these lines of code:

1 GVW. HeaderRow.Cells.Clear (); 2 New TableCell (); 3 // Redraw a table header 4 " <tr> ... </tr> " ; 5 GVW. HEADERROW.CELLS.ADD (TC);

Redraw a table header

In addition, see Bo Friends recommend me to use NPOI.HSSF.Util components, heard very easy to operate. Also recommended here!

//-Create ExcelHssfworkbook Hssfworkbook =NewHssfworkbook (); //-Create Sheet   varSheet = Hssfworkbook. Createsheet ("Retire Options Report"); //-Every Row in the SheetNpoi. Ss.   Usermodel.row Row; //-Create a "painter", which is used for all picture writes. //-Please note that is all the pictures, not a picture created one! Otherwise it will cause no picture   varPatriarch =sheet.    Createdrawingpatriarch (); //-Default cell styles and fonts, which are "Excel-level" if set, will cause all cells to be those fonts and styles//-Create a new font and style to ensure that these "cells" are independent of the font and style. //-Here is a style for creating a title   varCellfont =Hssfworkbook.   CreateFont (); varCellStyle =Hssfworkbook.    Createcellstyle (); //-Bold, white front viewCellfont.boldweight = ( Short) Npoi. Ss.   UserModel.FontBoldWeight.BOLD; Cellfont.color=Npoi. HSSF.    Util.HSSFColor.WHITE.index; //-Very confusing fillforegroundcolor this property, I set the background color, but in terms of meaning, this seems to be called "foreground color"? //-What's more, there are also fillbackgroundcolor properties. I don't know what to do. Cellstyle.fillforegroundcolor =Npoi. HSSF.   Util.HSSFColor.GREY_40_PERCENT.index; //-This is the pattern of filling, it can be grid, fancy and so on. If you need to fill a single color, use: Solid_foregroundCellstyle.fillpattern =Npoi. Ss.   UserModel.FillPatternType.SOLID_FOREGROUND; //-Set the font for this style, if not set, will have common fonts with all cells! Cellstyle.setfont (Cellfont);  for(inti =0; I <= Gridview1.rowcount; i++)   {//-Traverse Row This is the gridview,i of the Gridcontrol control <= Gridview1.rowcount because the first line we set as the header rowRow=sheet.       CreateRow (i); foreach(GridColumn columninchgridview1.columns) {//-Ensure that only the columns that you want to display           if(column. Visible) {//-Create a cell with the current row specified column indexNpoi. Ss. Usermodel.cell Cell =row. Createcell (column.               Visibleindex); //-header row               if(i = =0) {row. Heightinpoints= 50f;//-Set line height row. Height needs to be multiplied byCell. Setcellvalue (column. Caption);;//-Set cell contentsCell. CellStyle = CellStyle;//-Set the independent style of the cell               }               Else{row. Heightinpoints= 100f;//set row height rows. Height needs to be multiplied by                   ObjectValue = Gridview1.getrowcellvalue (i-1, column); //-If it is a picture                   if(Value! =NULL&& value. GetType () = =typeof(byte[])) {sheet. Setcolumnwidth (column. Visibleindex, -* the);//-Set the width of the column to multiply by//-Insert a picture into Excel and return the identity of a picture                       varPictureidx = Hssfworkbook. AddPicture ((byte[]) value, Npoi. Ss.                        UserModel.PictureType.JPEG); //-Where to create pictures                       varAnchor =NewHssfclientanchor (0,0,//-top left to top right position, is based on the column position below                           0,0,//-bottom left to bottom right position, is based on the column position belowcolumn. Visibleindex, I, column. Visibleindex+1, i +1); //-the position of the image output is calculated as follows://-Suppose we want to place the picture in line 2nd of column 5th (E)//-the corresponding index is 4:1 (the default location)//-Place the position equal to (the default position) to (the default location of each row, one column)Patriarch. Createpicture (anchor, PICTUREIDX);//-Drawing pictures using the paint device                   }                   Else{cell. Setcellvalue (value.                   Tostringorempty ()); }               }               //-CenterCell. Cellstyle.verticalalignment =Npoi. Ss.               UserModel.VerticalAlignment.CENTER; Cell. Cellstyle.alignment=Npoi. Ss.                UserModel.HorizontalAlignment.CENTER; //-Fine edgesCell. Cellstyle.borderbottom =Npoi. Ss.               UserModel.CellBorderType.THIN; Cell. Cellstyle.borderleft=Npoi. Ss.               UserModel.CellBorderType.THIN; Cell. Cellstyle.borderright=Npoi. Ss.               UserModel.CellBorderType.THIN; Cell. Cellstyle.bordertop=Npoi. Ss.                UserModel.CellBorderType.THIN; Cell. Cellstyle.bottombordercolor=Npoi. HSSF.               Util.HSSFColor.BLACK.index; Cell. Cellstyle.leftbordercolor=Npoi. HSSF.               Util.HSSFColor.BLACK.index; Cell. Cellstyle.rightbordercolor=Npoi. HSSF.               Util.HSSFColor.BLACK.index; Cell. Cellstyle.topbordercolor=Npoi. HSSF.           Util.HSSFColor.BLACK.index; }}} FileStream file=NewFileStream (FileName, FileMode.Create); Hssfworkbook. Write (file);//-SaveFile. Close ();

Npoi. HSSF. Util component Use Method (reprint)