java中poi创建Excel 跨行跨列 java 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 了解下。