Apache POI导出excel
public String exportXls(HttpServletRequest request, HttpServletResponse response) { try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFRow row = null; HSSFCell cell = null; //建立新的sheet对象 HSSFSheet sheet = wb.createSheet("导出信息");//sheet的名称 sheet.setColumnWidth(0, 5*256);//设置第columnIndex+1列的列宽，单位为字符宽度的1/256 sheet.setColumnWidth(1, 30*256); sheet.setColumnWidth(2, 20*256); sheet.setColumnWidth(3, 10*256); sheet.setColumnWidth(4, 10*256); sheet.setColumnWidth(5, 10*256); sheet.setColumnWidth(6, 10*256); sheet.setColumnWidth(7, 10*256); //标题样式 HSSFCellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); org.apache.poi.ss.usermodel.Font ztFont = wb.createFont(); ztFont.setItalic(false); ztFont.setColor(org.apache.poi.ss.usermodel.Font.COLOR_NORMAL); ztFont.setFontHeightInPoints((short) 16); ztFont.setFontName("宋体"); ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); titleStyle.setFont(ztFont); //----------------二级标题格样式---------------------------------- HSSFCellStyle titleStyle2 = wb.createCellStyle(); //表格样式 titleStyle2.setAlignment(HSSFCellStyle.ALIGN_LEFT); titleStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont ztFont2 = wb.createFont(); ztFont2.setItalic(false); // 设置字体为斜体字 ztFont2.setColor(org.apache.poi.ss.usermodel.Font.COLOR_NORMAL); ztFont2.setFontHeightInPoints((short)11); // 将字体大小设置为18px ztFont2.setFontName("宋体"); // 字体应用到当前单元格上 titleStyle2.setFont(ztFont2); //---------------------------------------------------------- //----------------单元格样式---------------------------------- HSSFCellStyle cellStyle = wb.createCellStyle(); //表格样式 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 HSSFFont cellFont = wb.createFont(); cellFont.setItalic(false); // 设置字体为斜体字 cellFont.setColor(org.apache.poi.ss.usermodel.Font.COLOR_NORMAL); cellFont.setFontHeightInPoints((short)10); // 将字体大小设置为18px cellFont.setFontName("宋体"); // 字体应用到当前单元格上 cellStyle.setFont(cellFont); cellStyle.setWrapText(true);//设置自动换行 //---------------------------------------------------------- // ----------------------创建第一行--------------- // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个 row = sheet.createRow(0); // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个 cell = row.createCell(0); // 合并单元格CellRangeAddress构造参数依次表示 起始行，截至行，起始列， 截至列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // 设置单元格内容 cell.setCellValue("测试excel"); cell.setCellStyle(titleStyle); // ---------------------------------------------- // ------------------创建第二行()--------------------- row = sheet.createRow(1); // 创建第二行 cell = row.createCell(0); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1)); cell.setCellValue("入库单号:15203"); cell.setCellStyle(titleStyle2); cell = row.createCell(2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 4)); cell.setCellValue("执行单号:14520"); cell.setCellStyle(titleStyle2); cell = row.createCell(5); sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 7)); String createDate = MapUtil.getRealValue(headInfo, "create_date", ""); cell.setCellValue("创建时间:2018-06-19 13:59:23"); cell.setCellStyle(titleStyle2); //---------------------创建第三行------------------------------------- row = sheet.createRow(2); cell = row.createCell(0); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1)); cell.setCellValue("供应商:京东"); cell.setCellStyle(titleStyle2); cell = row.createCell(2); sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 4)); cell.setCellValue("仓库名称:一号仓库"); cell.setCellStyle(titleStyle2); cell = row.createCell(5); sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 7)); cell.setCellValue("总金额:23.65"); cell.setCellStyle(titleStyle2); // ------------------创建表头start--------------------- row = sheet.createRow(3); // 创建第四行 sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 0)); cell = row.createCell(0); cell.setCellValue("序号"); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 1)); cell = row.createCell(1); cell.setCellValue("物料名称"); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 2)); cell = row.createCell(2); cell.setCellValue("物料编号"); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 3)); cell = row.createCell(3); cell.setCellValue("物料产地"); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 4)); cell = row.createCell(4); cell.setCellValue("入库价格"); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 4)); cell = row.createCell(5); cell.setCellValue("参考价格"); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 5)); cell = row.createCell(6); cell.setCellValue("需求数量"); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 6)); cell = row.createCell(7); cell.setCellValue("总金额"); cell.setCellStyle(cellStyle); //循环填充数据 int i =1; int rowNum= 3; List<Object> data = null; for (Map m : mapList) { data = new ArrayList<>(); data.add(i); data.add((String) m.get("wuliao_name")); data.add((String) m.get("wuliao_no")); data.add((String) m.get("wuliao_address")); data.add((Double) m.get("danjia")); data.add((Double) m.get("reference_price")); data.add((Integer) m.get("wuliao_count")); data.add((Double) m.get("totalmoney")); rowNum ++; //从第四行开始 row = sheet.createRow(rowNum); for (int j = 0; j < data.size(); j++) { //将数据添加到单元格中 sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j, j)); cell = row.createCell(j); cell.setCellValue(""+data.get(j)+""); cell.setCellStyle(cellStyle); } i++; } //添加底部合计行 row = sheet.createRow(rowNum+1); sheet.addMergedRegion(new CellRangeAddress(rowNum+1, rowNum+1, 0, 4)); cell = row.createCell(0); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellStyle(cellStyle); cell = row.createCell(3); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellStyle(cellStyle); cell = row.createCell(5); cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(totalNumber); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(new BigDecimal(totalMoney).setScale(2, RoundingMode.DOWN).toString()); cell.setCellStyle(cellStyle); OutputStream output = response.getOutputStream(); response.reset();
response.setHeader("Content-Disposition", "filename="
+ new String("测试导出".getBytes("gb2312"), "iso8859-1"))+".xls";//设置文件头编码方式和文件名
response.setContentType("application/msexcel");
wb.write(output);
output.close();
} catch (Exception e) {
e.printStackTrace();
}
}
