poi java生成excel_Java 使用Apache POI生成并导出excel

项目中使用POI生成excle报表

maven:org.apache.poi

poi  4.0.1

POI中概念：

HSSFWorkbook : 相当于一个excel文件

HSSFSheet:一张excel表，excel左下角的sheet0,sheet1..

HSSFRow：一张表格中的某一行

HSSFCell:一张表格中的一个单元格

1.创建一个workBook.

HSSFWorkbook wb = new HSSFWorkbook();

2.创建一张sheet

HSSFSheet sheet = wb.createSheet("sheet0");

3.创建第一行

HSSFRow row = sheet.createRow(0);

构造方法中的数值表示创建第几行，索引从0开始

4.创建一个单元格

HSSFCell cell = row.createCell(0);

构造方法中的数值表示创建第几列，索引从0开始

5.向表格中添加数据

cell.setCellValue().

此时，一个excel的雏形已经创建好了，但是，怎么导出并下载呢？

需要用到HttpServletResponse。

1.设置响应类型，告知类型为excel文件

response.setContentType("application/vnd.ms-excel");

2.设置字符编码

response.setCharacterEncoding("utf-8");

3.根据浏览器进行转码，使其支持中文文件名String browse = Excelutil.checkBrowse(request);String fileName ="新建表格";try {if ("MSIE".equalsIgnoreCase(browse.substring(0, 4))) {response.setHeader("content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8") +".xls");    }else {String newtitle =new String(fileName.getBytes("UTF-8"), "ISO8859-1");        response.setHeader("content-disposition", "attachment;filename=" + newtitle +".xls");    }}catch (Exception e) {e.printStackTrace();}

4.导出OutputStream out =null;try {out = response.getOutputStream();    wb.write(out);}catch (Exception e) {e.printStackTrace();}finally {if (null != out) {try {out.close();        }catch (IOException e) {e.printStackTrace();        }}}

到此为止可以导出一个excel表格了。

还没完，实际生产过程中需要用到的报表格式要复杂得多

1.首先，设置各个单元格的格式，比如大小，居中什么的(新版本的API可能不一样，具体可以看看官方API)

设置格式需要用到HSSFCellStyle。由HSSFWorkbook 生成，在方法定义中明确表示一个workbook

最多只能有4000个HSSFCellStyleHSSFCellStyle cs = wb.createCellStyle();cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);水平居中cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);垂直居中cs.setWrapText(true);字体是否加粗，默认为falsecs.setBorderBottom(BorderStyle.DOTTED);设置下边框样式，BorderStyle是一个枚举，有很多类型，具体可以看官方文档。cs.setBorderTop(BorderStyle.DOTTED)cs.setBorderLeft(BorderStyle.DOTTED)cs.setBorderRight(BorderStyle.DOTTED)cs.setBottomBorderColor(short color)设置边框颜色，有对应的上下左右方法cs.setFillBackgroundColor(short color)设置背景颜色cs.setFilllBackgroundColor(short color)设置前景颜色cs.setFont(HSSFFont font)设置字体HSSFFont font = wb.createFont();font.setFontName("黑体“)font.setFontHeightInPoints((short)12)设置字体

让格式生效

cell.setCellStyle(cs)

也可以使用

sheet.setColumnWidth(int columnIndex,int width);

设置某一列的宽度

2.合并单元格

sheet.addMergedRegion(new CellRangeAddress(int firstRow,int lastRow,int firstCol, int lastCol));

将多个单元格合成一个单元格，参数依次为起始行，结束行，起始列，结束列。

使用时先生成一个单元格，比如我要合并第一行的第一个和第二个单元格

1.先生成第一个单元格

HSSFCell cell = row.createCell(0);

2.合并

sheet.addMergedRegion(new CellRangeAddress(0,0,0, 1));

3.如果要生成下一个单元格，记得单元格的下标应该为2而不是1，因为你已经合并了一个单元

格，原则：生成新的单元格的时候要判断前面是否有合并的单元格，如果有，需要将合并的单元格也算进去。

4.poi也可以通过读取excel模板来生成新的excel报表

首先，需要将excel模板文件放入项目资源文件夹中，然后获取该模板文件的路径String path = AbroadCountUtils.class.getClassLoader().getResource("abroad.xls").getPath();

File file =newFile(path);

生成HSSFWorkbookHSSFWorkbook wb =newHSSFWorkbook();

生成HSSFSheetHSSFSheet sheet = wb.createSheet("sheet0");

后面的步骤就是一样的了，通过导入模板可以简化生成复杂表头的步骤，避免出错。

5.checkbrowser方法private final staticStringIE11="rv:11.0";

private final staticStringIE10="MSIE 10.0";

private final staticStringIE9="MSIE 9.0";

private final staticStringIE8="MSIE 8.0";

private final staticStringIE7="MSIE 7.0";

private final staticStringIE6="MSIE 6.0";

private final staticStringMAXTHON="Maxthon";

private final staticStringQQ="QQBrowser";

private final staticStringGREEN="GreenBrowser";

private final staticStringSE360="360SE";

private final staticStringFIREFOX="Firefox";

private final staticStringOPERA="Opera";

private final staticStringCHROME="Chrome";

private final staticStringSAFARI="Safari";

private final staticStringOTHER="其它";public staticStringcheckBrowse(HttpServletRequest request) {

String userAgent = request.getHeader("USER-AGENT");

if(regex(OPERA,userAgent))

returnOPERA;

if(regex(CHROME,userAgent))

returnCHROME;

if(regex(FIREFOX,userAgent))

returnFIREFOX;

if(regex(SAFARI,userAgent))

returnSAFARI;

if(regex(SE360,userAgent))

returnSE360;

if(regex(GREEN,userAgent))

returnGREEN;

if(regex(QQ,userAgent))

returnQQ;

if(regex(MAXTHON,userAgent))

returnMAXTHON;

if(regex(IE11,userAgent))

returnIE11;

if(regex(IE10,userAgent))

returnIE10;

if(regex(IE9,userAgent))

returnIE9;

if(regex(IE8,userAgent))

returnIE8;

if(regex(IE7,userAgent))

returnIE7;

if(regex(IE6,userAgent))

returnIE6;

returnOTHER;

}public static booleanregex(String regex,String str) {

Pattern p = Pattern.compile(regex,Pattern.MULTILINE);

Matcher m = p.matcher(str);

returnm.find();}

贴上从前端请求到后端处理的整个代码。

1.前端js方法，该方法为按钮的点击事件绑定的方法，点击后通过将参数拼接到url的方式进行传参。(有点丑，有更好的可以推荐！)function exportExcel() { var startDate = $('#startTime').val(); var endDate = $('#endTime').val(); var condition = $('#condition').val(); var url = 'exportExcel.do'; url = url + '?start=' + startDate + '&end=' + endDate + '&condition=' + condition; location.href = url;}

2.controller中的方法@RequestMapping("exportExcel.do")

public void exportExcel(HttpServletRequest request, HttpServletResponse response) {

String start = request.getParameter("start");

String end = request.getParameter("end");

String condition = request.getParameter("condition");

List titleList = new ArrayList<>();

titleList.add("ID");

titleList.add("name");

titleList.add("age");

List> content = new ArrayList();

HSSFWorkbook wb = GenerateExcelUtil.generateExcel(titleList, content);

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("utf-8");

String browse = GenerateExcelUtil.checkBrowse(request);

String fileName = "newExcel";

try { if ("MSIE".equalsIgnoreCase(browse.substring(0, 4))) {

response.setHeader("content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8") + ".xls"); }

else { String newtitle = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); response.setHeader("content-disposition", "attachment;filename=" + newtitle + ".xls"); }

} catch (Exception e) { e.printStackTrace(); } OutputStream out = null; try { out = response.getOutputStream(); wb.write(out); } catch (Exception e) { e.printStackTrace(); }finally { if (null != out) { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } }}

3.生成excel工具类public class GenerateExcelUtil {

private final static String IE11="rv:11.0";

private final static String IE10="MSIE 10.0";

private final static String IE9="MSIE 9.0";

private final static String IE8="MSIE 8.0";

private final static String IE7="MSIE 7.0";

private final static String IE6="MSIE 6.0";

private final static String MAXTHON="Maxthon";

private final static String QQ="QQBrowser";

private final static String GREEN="GreenBrowser";

private final static String SE360="360SE";

private final static String FIREFOX="Firefox";

private final static String OPERA="Opera";

private final static String CHROME="Chrome";

private final static String SAFARI="Safari";

private final static String OTHER="其它";

public static HSSFWorkbook generateExcel(List titles, List> contents){

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

sheet.setColumnWidth(1,256*32); sheet.setColumnWidth(4,256*20);

sheet.setColumnWidth(5,256*50); sheet.setColumnWidth(6,256*20);

sheet.setColumnWidth(8,256*20);

sheet.setDefaultRowHeight((short)(20*20));

HSSFRow row = sheet.createRow(0);

for (int i = 0 ;i< titles.size();i++) {

HSSFCell cell = row.createCell(i);

cell.setCellValue(titles.get(i));

HSSFCellStyle cs = wb.createCellStyle();

cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);

cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

cs.setWrapText(true); cell.setCellStyle(cs); }

for (int i = 1;i<=contents.size();i++) {

HSSFRow row1 = sheet.createRow(i);

List list = contents.get(i - 1);

for(int j= 0;j

HSSCell cell = row1.createCell(j);

cell.setCellValue(list.get(j))

}

public static String checkBrowse(HttpServletRequest request) {

String userAgent = request.getHeader("USER-AGENT");

if(regex(OPERA,userAgent)){ return OPERA; }

if(regex(CHROME,userAgent)){ return CHROME;}

if(regex(FIREFOX,userAgent)){ return FIREFOX;}

if(regex(SAFARI,userAgent)){ return SAFARI;}

if(regex(SE360,userAgent)){ return SE360;}

if(regex(GREEN,userAgent)){ return GREEN;}

if(regex(QQ,userAgent)){ return QQ;}

if(regex(MAXTHON,userAgent)){ return MAXTHON;}

if(regex(IE11,userAgent)){ return IE11;}

if(regex(IE10,userAgent)){ return IE10;}

if(regex(IE9,userAgent)){ return IE9;}

if(regex(IE8,userAgent)){ return IE8;}

if(regex(IE7,userAgent)){ return IE7;}

if(regex(IE6,userAgent)){ return IE6;}

return OTHER;}

private static boolean regex(String regex, String str) {

Pattern p = Pattern.compile(regex, Pattern.MULTILINE);

Matcher m = p.matcher(str);

return m.find();}

出现的问题：导出按钮在一个iframe中，使用ajax请求，服务器文件生成成功了，但是浏览器却没出现下载的文件。function exportExcel() {

var url ='/export';

var xhr =new XMLHttpRequest();

xhr.open('POST', url, true);    // 也可以使用POST方式，根据接口

xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded;charset=utf-8");

xhr.responseType ="blob";  // 返回类型blob

// 定义请求完成的处理函数，请求前也可以增加加载框/禁用下载按钮逻辑

xhr.onload =function () {

var data =this.response;

// 请求完成

if (this.status ===200) {

var blob =new Blob([data], {type:"application/vnd.ms-excel"});

saveAs(blob, "demo.xls");

}else {

alert("导出失败!")

}

};

// 发送ajax请求

xhr.send($("#form").serialize())}

ps:如果导出的excel数据量特别大，XSSFWorkbook 了解下。