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;

}

}