Apache POI笔记—生成excel导出

都说好记性不如烂笔头，poi也用过几次，不过这两天用到的时候还是要找资料，索性趁中午休息的时间做个记录。 Demo功能：导出excel Demo流程： 1、点击web页面的导出按钮，Servlet后台根据web页面的条件查数据传导出Excel的ExcelGenerate类； 2、ExcelGenerate用poi读取excel模版文件，把数据后填充进poi读取的excel中，设置好单元格样式等； 3、然后把poi读取的excel写入输出流（OutStream），把输出流转换为输入流（InputStream）返回给Servlet； 4、Servlet设置好Response的ContentType与Header的信息为application/ms-excel、与"Content-disposition","attachment;filename=""+filename+"""，把输入流写入到Response的输出流（OutStream）中，关闭输入流（InputStream）、输出流（OutStream）。 Servlet类： public class OriginalDataServlet extends HttpServlet {

protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { List originalDataRes = originalRes.getOriginalDataList();

String path; String filename="test.xls"; //设置文件ContentType类型 response.setContentType("application/ms-excel"); response.setHeader("Contentdisposition","attachment;filename=""+filename+""");

path=request.getSession().getServletContext().getRealPath("/resource/"); ExcelGenerate generate = new ExcelGenerate(path,originalDataRes);

//取得生成excel后的输入流 InputStream input = generate.GenerateFileStream(); OutputStream output = response.getOutputStream();     //response输出流 int b = 0; byte[] buffer = new byte[512]; while ((b=input.read(buffer))!= -1) {          //excel输入流写到response输出流中 output.write(buffer, 0, b); }

} catch (Exception e) { e.printStackTrace(); }finally{ input.close(); output.close(); }

}

} ExcelGenerate类： public class ExcelGenerate { private List modelList; private String path;

public ExcelGenerate(String path, List originalDataRes)

/** * 生成Excel输入流 * @return * @throws FileNotFoundException * @throws URISyntaxException */ public InputStream GenerateFileStream( ) throws FileNotFoundException, URISyntaxException{

String file=path+"/test.xls";           //excel模版文件 // 输出流 ByteArrayOutputStream fOut = null ; InputStream input=new FileInputStream(file); try {

//读取excel表格 HSSFWorkbook workbook = new HSSFWorkbook(input); HSSFSheet sheet= workbook.getSheetAt(0);                      //  默认第一个表格

ExcelGenerate.log.info("====================="+originalDataRes.size()); for (int i = 0; i <modelList.size(); i++) { HSSFRow rowTemp =sheet.createRow(i+1);               //i+1是因为第一行是标题 //创建单元格 rowTemp.createCell(0); rowTemp.createCell(1); rowTemp.createCell(2); rowTemp.createCell(3);

//单元格样式 HSSFCellStyle style= this.getCellStyle(workbook.createCellStyle()); HSSFCell cell1=rowTemp.getCell(0); //单元格填值 cell1.setCellStyle(style); cell1.setCellValue(modelList.get(i).id);      //填单元格值

HSSFCell cell2=rowTemp.getCell(1); cell2.setCellStyle(style); cell2.setCellValue(modelList.get(i).name);

HSSFCell cell3=rowTemp.getCell(2); cell3.setCellStyle(style);

cell3.setCellValue();

HSSFCell cellTemp=rowTemp.getCell(3); cellTemp.setCellStyle(style); cellTemp.setCellValue(modelList.get(i).title); }

// 输出流 fOut = new ByteArrayOutputStream(); workbook.write(fOut);

//ExcelGenerate.log.info(value); input=new ByteArrayInputStream(fOut.toByteArray()); fOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }

return input; } /** * 设置单元格样式 * @param style * @return */ private HSSFCellStyle getCellStyle(HSSFCellStyle style){

style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); return style;

} }