使用POI生成excel，然后直接生成流文件，前臺(tái)頁(yè)面直接彈出下載窗口，不需要先生成excel文本再下載，省時(shí)省力

Apache POI是Apache軟件基金會(huì)的開(kāi)放源碼函式庫(kù)，POI提供API給Java程式對(duì)Microsoft Office格式檔案讀和寫(xiě)的功能。 .NET的開(kāi)發(fā)人員則可以利用NPOI (POI for .NET) 來(lái)存取 POI 的功能。

第一步：引入所需的jar在工程項(xiàng)目lib目錄下。

第二步：在點(diǎn)擊導(dǎo)出頁(yè)面設(shè)置一個(gè)事件觸發(fā)servlet讀出事先保存好的xsl文件；

第三步：實(shí)現(xiàn)servlet

package com.zust.servlet;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.io.PrintWriter;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletResponse;

import com.zust.excel.*;

import com.zust.paper.Paper;

public class exportExcel extends HttpServlet {

public void doGet(HttpServlet request, HttpServletResponse response)

throws ServletException, IOException {

????this.doPost(request, response);

}

public void doPost(HttpServlet request, HttpServletResponse response)

throws ServletException, IOException {

??//獲得項(xiàng)目服務(wù)器根路徑

??String path = request.getSession().getServletContext().getRealPath();

??????//把所有的路徑的單斜杠替換成雙斜杠?

??path = path.replace(\\,//);

??gradesXLS xls = new gradesXLS(path);

??List list = (List) request.getSession().getAttribute(paperLit);

??Boolean flag = ?xls.CreateExcel(list);

OutputStream o = response.getOutputStream();

byte b[] = new byte[1024];

// the file to download.

File fileLoad = new File(path,grades.xls);

// the dialogbox of download file.

response.setHeader(Content-disposition, attachment;filename=+ grades.xls);

// set the MIME type.

response.setContentType(application/vnd.ms-excel);

// get the file length.

long fileLength = fileLoad.length();

String length = String.valueOf(fileLength);

response.setHeader(Content_Length, length);

// download the file.

FileInputStream in = new FileInputStream(fileLoad);

int n = 0;

while ((n = in.read(b)) != -1) {

o.write(b, 0, n);

}

}

}

第四部：servlet調(diào)用的方法:xls.CreateExcel(list);

package com.zust.excel;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.zust.paper.Paper;

public class gradesXLS {

private String path;

???//構(gòu)造函數(shù)獲取保存excel的路徑

???public gradesXLS(String path){

??this.path = path;

???}

public Boolean CreateExcel(List paperList) {

Boolean flag = true;

// 創(chuàng)建一個(gè)工作簿

HSSFWorkbook workBook = new HSSFWorkbook();

// 創(chuàng)建一個(gè)工作表，名為：第一頁(yè)

HSSFSheet sheet = workBook.createSheet(成績(jī)表);

// 設(shè)置單元格的寬度(0:表示第一行的第一個(gè)單元格，1：第一行的第二個(gè)單元格)

sheet.setColumnWidth((short) 0, 3500);

sheet.setColumnWidth((short) 1, 5000);

sheet.setColumnWidth((short) 2, 5000);

// 創(chuàng)建一個(gè)單元格，從0開(kāi)始

HSSFRow row = sheet.createRow((short) 0);

// 構(gòu)造一個(gè)數(shù)組設(shè)置第一行之后的單元格

HSSFCell cell[] = new HSSFCell[5];

for (int i = 0; i < 5; i++) {

cell[i] = row.createCell(i);

}

cell[0].setCellValue(用戶(hù)名);

cell[1].setCellValue(開(kāi)始時(shí)間);

cell[2].setCellValue(結(jié)束時(shí)間);

cell[3].setCellValue(集體編號(hào));

cell[4].setCellValue(總分);

// 獲得從數(shù)據(jù)庫(kù)中查詢(xún)出來(lái)的數(shù)據(jù)

if (paperList != null && paperList.size() > 0) {

// 循環(huán)list中的數(shù)據(jù)

for (int i = 0; i < paperList.size(); i++) {

Paper p = (Paper) paperList.get(i);

HSSFRow dataRow = sheet.createRow(i + 1);

HSSFCell data[] = new HSSFCell[5];

for (int j = 0; j < 5; j++) {

data[j] = dataRow.createCell(j);

}

data[0].setCellValue(p.getUsername());

data[1].setCellValue(p.getStarttime());

data[2].setCellValue(p.getEndtime());

???if(p.getGroupno()!=-1){

????data[3].setCellValue(p.getGroupno());

????}else{

????data[3].setCellValue(無(wú));

????}

data[4].setCellValue(p.getTotal());

try {

// 輸出成XLS文件

File file = new File(path);

FileOutputStream fos = new FileOutputStream(file+\\grades.xls);

// 寫(xiě)入數(shù)據(jù)，并關(guān)閉文件

workBook.write(fos);

fos.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

flag = false;

} catch (IOException e) {

e.printStackTrace();

flag = false;

}

}

}

return flag;

}

}