使用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
??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;
}
}
電子游藝首存送彩金