Export data: data is mostly form, so often need to import to Excel, import and export of the use of the browser file download function, but with the window. The open open a new window to download and open in this page, the browser is hard to identify on the need to download the file
Must first league table query need to export the data, there are two kinds of msoffice excel, NPOI JAVA (POI) choice, we usually use the latter to export operation
1, create workbook
NPOI. HSSF. UserModel. HSSFWorkbook workbook=new NPOI. HSSF. UserModel. HSSFWorkbook ();
2, create a working table
NPOI. SS. UserModel. ISheet sheet1=workbook. CreateSheet ();
Modify the work table name
The custom name. SetSheetName (0, "working table name");
3, set the table headings
1.) create lines
NPOI. SS. UserModel. IRow rowTitle.=sheet1 CreateRow (0);//the subscript
RowTitle. HeightInPoints=35;//high HeightInPoints unit is the point, line and Height of the unit is 1/20 of a point, so the Height value is always HeightInPoints 20 times
2.)
create cellNPOI. SS. UserModel. ICell cell0=rowTitle. CreateCell (0);
3). The cell set values
Title string strTitle="cell";
Cell0. SetCellValue (strTitle);
4).
merged cellSheet1. AddMergedRegion (new NPOI. SS. Util. CellRangeAddress (0, 0, 0, 6));//0, 0, 0, 6 represent the first merger to 6 cell
5). Set the cell style
NPOI. SS. UserModel. ICellStyle
CellStyle_Title=workbook. CreateCellStyle ();
CellStyle_Title. Alignment=NPOI. SS. UserModel. HorizontalAlignment. Center;//horizontal center
CellStyle_Title. VerticalAlignment=NPOI. SS. UserModel. VerticalAlignment. Center;//vertical center
NPOI. SS. UserModel. IFont font_title=workbook. CreateFont ();//declare the font
Ont_title. Color=NPOI. HSSF. Util. HSSFColor. Blue. The Index;//set the font colour
Font_title. Boldweight=(short) NPOI. SS. UserModel. FontBoldWeight. Bold;//bold
Font_title. FontHeightInPoints=18;//font size
CellStyle_Title. SetFont (font_title);//set the cell font
Cell0. CellStyle=cellStyle_Title;//set the cell style
4, set the header
1.) create a row index
NPOI. SS. UserModel. IRow row1=sheet1. CreateRow (1);//give sheet to add the first row
2). Create a cell and set values
Row1. CreateCell (0). SetCellValue (" serial number ");
Row1. CreateCell (1). SetCellValue (" passenger name ");
Row1. CreateCell (2). SetCellValue (" certificate number ");
Row1. CreateCell (3). SetCellValue (" contact phone ");
3). Create a header style
NPOI. SS. UserModel. ICellStyle cellStyle_header=workbook. CreateCellStyle ();//statement style
CellStyle_header. Alignment=NPOI. SS. UserModel. HorizontalAlignment. Center;//horizontal center
CellStyle_header. VerticalAlignment=NPOI. SS. UserModel. VerticalAlignment. Center;//vertical center
4). Set the background color in
CellStyle_header. FillPattern=NPOI. SS. UserModel. FillPattern. SolidForeground;
CellStyle_header. FillForegroundColor=NPOI. HSSF. Util. HSSFColor. Aqua. Index;
5). Set the border lines for
CellStyle_header. BorderLeft=NPOI. SS. UserModel. BorderStyle. Thin;
CellStyle_header. BorderTop=NPOI. SS. UserModel. BorderStyle. Thin;
6). Set the font
NPOI. SS. UserModel. IFont font_header=workbook. CreateFont ();//declare the font
Font_header. Boldweight=(short) NPOI. SS. UserModel. FontBoldWeight. Bold;//bold
Font_header. FontHeightInPoints=10;//font size
CellStyle_header. SetFont (font_header);//add cell
//set the style for cell cycle
for (int i=0; I & lt; Row1. Cells. The Count; I++)
{row1. GetCell (I). The CellStyle=cellStyle_header; }
5, traverse the query to the data, set the table data
1). The internal part of the cell style to create data
NPOI. SS. UserModel. ICellStyle cellstyle_value=https://bbs.csdn.net/topics/workbook.CreateCellStyle ();//statement style
Cellstyle_value. Alignment=NPOI. SS. UserModel. HorizontalAlignment. Center;//horizontal center
Cellstyle_value. VerticalAlignment=NPOI. SS. UserModel. VerticalAlignment. Center;//vertical center
Part 2). Through data, create data procession
for (int i=0; I & lt; List. The Count; I++)
{
1. Create lines
NPOI. SS. UserModel. IRow row=sheet1. CreateRow (2 + I);//title and header has accounted for two lines
Row. Height=22 * 20;//set the line height
2. Create a column, and set the value of
Row. CreateCell (0). SetCellValue (I + 1);//serial number
Row. CreateCell (1). SetCellValue (the list [I] passengerName);
Row. CreateCell (4). SetCellValue (the list [I] certificatesCode);
Row. CreateCell (6). SetCellValue (the list [I] contactPhone);
3. Add style to each cell
For (int j=0; J & lt; Row. The Cells. The Count; J++)
{row. GetCell (j) CellStyle=cellstyle_value; }
6, set the column width to automatically adapt to
for (int i=0; I & lt; Sheet1. GetRow (1). Cells. The Count; I++)
{sheet1. AutoSizeColumn (I);
Sheet1. SetColumnWidth (I, sheet1. GetColumnWidth (I) * 17/10); }
7, to create good Excel output to the browser
String fileName="passenger information" + DateTime. Now. The ToString (" yyyy - MM - dd - HH - MM - ss - FFFF ") + ". XLS ";
The Excel into current output
MemoryStream BookStream=new MemoryStream ();//define flow
Workbook. Write (BookStream);//will work thin stream
BookStream. Seek (0, SeekOrigin. Begin);//output before invoking the Seek (offset, the cursor position)
Return the File (BookStream, "application/VND. - ms excel," fileName);//file type/name