POI導出Excel

使用POI生成excel，然后直接生成流文件，前臺頁面直接彈出下載窗口，不需要先生成excel文本再下載，省時省力

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

poi導出excel基本步驟

第一步：引入所需的jar在工程項目lib目錄下。

第二步：在點擊導出頁面設置一個事件觸發servlet讀出事先保存好的xsl文件；

<script type="text/javascript" charset="UTF-8">

   function excel(){

      window.location="exportExcel";  //“exportExcel”是一個servlet地址

   }

</script>

<input type="button" value="導出excel" onclick="excel()">

第三步：實現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.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.zust.excel.*;

import com.zust.paper.Paper;

public class exportExcel extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

    this.doPost(request, response);

}

public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

  //獲得項目服務器根路徑

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

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

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

  gradesXLS xls = new gradesXLS(path);

  List<Paper> list = (List<Paper>) 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調用的方法: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;

   //構造函數獲取保存excel的路徑

   public gradesXLS(String path){

  this.path = path;

   }

public Boolean CreateExcel(List paperList) {

Boolean flag = true;

// 創建一個工作簿

HSSFWorkbook workBook = new HSSFWorkbook();

// 創建一個工作表，名為：第一頁

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

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

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

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

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

// 創建一個單元格，從0開始

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

// 構造一個數組設置第一行之后的單元格

HSSFCell cell[] = new HSSFCell[5];

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

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

}

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

cell[1].setCellValue("開始時間");

cell[2].setCellValue("結束時間");

cell[3].setCellValue("集體編號");

cell[4].setCellValue("總分");

// 獲得從數據庫中查詢出來的數據

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

// 循環list中的數據

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("無");

    }

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

try {

// 輸出成XLS文件

File file = new File(path);

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

// 寫入數據，并關閉文件

workBook.write(fos);

fos.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

flag = false;

} catch (IOException e) {

e.printStackTrace();

flag = false;

}

}

}

return flag;

}

}