标签归档:poi

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> 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<Model> modelList;
private String path;

public ExcelGenerate(String path, List<Model> originalDataRes) {
this.path=path;
this.modelList=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;

}
}