引入依赖

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> </dependency>

1、无合并行的数据导出

public static void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(),"ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename="+ fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } /** * 导出人员信息Excel * @param map */ @ResponseBody @RequestMapping(value = "exportUserInfoExcel", method = RequestMethod.POST, produces = { "application/json;charset=UTF-8" }) public void exportUserInfoExcel(HttpServletRequest request,HttpServletResponse response) { String[] split= new String[9]; split[0] = "序号"; split[1] = "姓名"; split[2] = "性别"; split[3] = "年龄"; split[4] = "地址"; split[5] = "爱好"; split[6] = "薪资"; split[7] = "银行卡号"; //创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //创建合并单元格对象 CellRangeAddress callRangeAddressTitle0 = new CellRangeAddress(0,0,0,split.length-1); CellRangeAddress callRangeAddressTitle01 = new CellRangeAddress(1,1,0,0); CellRangeAddress callRangeAddressTitle02 = new CellRangeAddress(1,1,1,1); CellRangeAddress callRangeAddressTitle03 = new CellRangeAddress(1,1,2,2); CellRangeAddress callRangeAddressTitle04 = new CellRangeAddress(1,1,3,3); CellRangeAddress callRangeAddressTitle05 = new CellRangeAddress(1,1,4,4); CellRangeAddress callRangeAddressTitle06 = new CellRangeAddress(1,1,5,5); CellRangeAddress callRangeAddressTitle07 = new CellRangeAddress(1,1,6,6); //创建工作表 HSSFSheet sheet = workbook.createSheet("人员信息.xls"); //title样式 HSSFCellStyle titleStyle = CommonUtils.createCellStyle(workbook, (short)15, true, true, true, false); //标题样式 HSSFCellStyle colStyle = CommonUtils.createCellStyle(workbook, (short)10, true, true, true, false); //数据样式 HSSFCellStyle rowStyle = CommonUtils.createCellStyle(workbook, (short)10, false, true, true, false); //加载合并单元格对象 sheet.addMergedRegion(callRangeAddressTitle0); sheet.addMergedRegion(callRangeAddressTitle01); sheet.addMergedRegion(callRangeAddressTitle02); sheet.addMergedRegion(callRangeAddressTitle03); sheet.addMergedRegion(callRangeAddressTitle04); sheet.addMergedRegion(callRangeAddressTitle05); sheet.addMergedRegion(callRangeAddressTitle06); sheet.addMergedRegion(callRangeAddressTitle07); //设置默认列宽 sheet.setDefaultColumnWidth(20); //创建列标题,并且设置列标题 HSSFRow row0 = sheet.createRow(0); String[] title = {"人员信息导出",""}; for(int i=0;i<title.length;i++) { HSSFCell cell1 = row0.createCell(i); cell1.setCellStyle(titleStyle); cell1.setCellValue(title[i]); } HSSFRow row1 = sheet.createRow(1); //列数 int titleNum = split.length; for(int i=0;i<titleNum;i++) { HSSFCell cell1 = row1.createCell(i); cell1.setCellStyle(colStyle); cell1.setCellValue(split[i]); } //将数据写入Excel if(userInfoList.size() != 0) { for(int i=0;i<userInfoList.size();i++) { //创建行数据 HSSFRow row = sheet.createRow(i+2); //临时数据存储集合 List<Object> data = new ArrayList<Object>(); data.add(i+1); data.add(userInfoList.get(i).getMc()); data.add(userInfoList.get(i).getSex()); data.add(ysbkuserInfoListist.get(i).getAge()); data.add(ysbkList.get(i).getAddress()); data.add(userInfoList.get(i).getHobby()); data.add(userInfoList.get(i).getSalary()); data.add(userInfoList.get(i).getBankCardNumber()); for (int j=0;j<data.size();j++) { HSSFCell cell = row.createCell(j); cell.setCellStyle(rowStyle); if("null".equals(String.valueOf(data.get(j))) || String.valueOf(data.get(j)) == null) { cell.setCellValue(""); }else { cell.setCellValue(String.valueOf(data.get(j))); } } } } //文件名称 String fileName = "人员信息.xls"; try { ExcelUtil.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); }catch (Exception e) { e.printStackTrace(); } }

2、有行合并的数据导出

public static void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(),"ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename="+ fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } /** * 导出人员信息Excel * @param map */ @ResponseBody @RequestMapping(value = "exportUserInfoExcel", method = RequestMethod.POST, produces = { "application/json;charset=UTF-8" }) public void exportUserInfoExcel(HttpServletRequest request,HttpServletResponse response) { String[] split= new String[21]; split[0] = "序号"; split[1] = "姓名"; split[2] = "性别"; split[3] = "年龄"; split[4] = "职级"; split[5] = "一季度"; split[6] = ""; split[7] = ""; split[8] = ""; split[9] = "二季度"; split[10] = ""; split[11] = ""; split[12] = ""; split[13] = "三季度"; split[14] = ""; split[15] = ""; split[16] = ""; split[17] = "四季度"; split[18] = ""; split[19] = ""; split[20] = ""; //创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //创建合并单元格对象 CellRangeAddress callRangeAddressTitle0 = new CellRangeAddress(0,0,0,split.length-1); CellRangeAddress callRangeAddressTitle01 = new CellRangeAddress(1,2,0,0); CellRangeAddress callRangeAddressTitle02 = new CellRangeAddress(1,2,1,1); CellRangeAddress callRangeAddressTitle03 = new CellRangeAddress(1,2,2,2); CellRangeAddress callRangeAddressTitle04 = new CellRangeAddress(1,2,3,3); CellRangeAddress callRangeAddressTitle05 = new CellRangeAddress(1,2,4,4); CellRangeAddress callRangeAddressTitle06 = new CellRangeAddress(1,1,5,8); CellRangeAddress callRangeAddressTitle07 = new CellRangeAddress(1,1,9,12); CellRangeAddress callRangeAddressTitle08 = new CellRangeAddress(1,1,13,16); CellRangeAddress callRangeAddressTitle09 = new CellRangeAddress(1,1,17,20); //创建工作表 HSSFSheet sheet = workbook.createSheet("人员信息.xls"); //title样式 HSSFCellStyle titleStyle = CommonUtils.createCellStyle(workbook, (short)15, true, true, true, false); //标题样式 HSSFCellStyle colStyle = CommonUtils.createCellStyle(workbook, (short)10, true, true, true, false); //数据样式 HSSFCellStyle rowStyle = CommonUtils.createCellStyle(workbook, (short)10, false, true, true, false); //加载合并单元格对象 sheet.addMergedRegion(callRangeAddressTitle0); sheet.addMergedRegion(callRangeAddressTitle01); sheet.addMergedRegion(callRangeAddressTitle02); sheet.addMergedRegion(callRangeAddressTitle03); sheet.addMergedRegion(callRangeAddressTitle04); sheet.addMergedRegion(callRangeAddressTitle05); sheet.addMergedRegion(callRangeAddressTitle06); sheet.addMergedRegion(callRangeAddressTitle07); sheet.addMergedRegion(callRangeAddressTitle08); sheet.addMergedRegion(callRangeAddressTitle09); //设置默认列宽 sheet.setDefaultColumnWidth(20); //创建列标题,并且设置列标题 HSSFRow row0 = sheet.createRow(0); String[] title = {"人员信息导出",""}; for(int i=0;i<title.length;i++) { HSSFCell cell1 = row0.createCell(i); cell1.setCellStyle(titleStyle); cell1.setCellValue(title[i]); } HSSFRow row1 = sheet.createRow(1); //列数 int titleNum = split.length; for(int i=0;i<titleNum;i++) { HSSFCell cell1 = row1.createCell(i); cell1.setCellStyle(colStyle); cell1.setCellValue(split[i]); } String[] split1={"1月","2月","3月","比例","4月","5月","6月","比例","7月","8月","9月","比例","10月","11月","12月","比例"}; HSSFRow row3 = sheet.createRow(2); int titleNum2 = split1.length; for(int i=0;i<titleNum2;i++) { HSSFCell cell1 = row3.createCell(i+5); cell1.setCellStyle(colStyle); cell1.setCellValue(split1[i]); } //将数据写入Excel if(userInfoList.size() != 0) { for(int i=0;i<userInfoList.size();i++) { //创建行数据 HSSFRow row = sheet.createRow(i+3); //临时数据存储集合 List<Object> data = new ArrayList<Object>(); data.add(i+1); data.add(userInfoList.get(i).getName()); data.add(userInfoList.get(i).getSex()); data.add(userInfoList.get(i).getAge()); data.add(userInfoList.get(i).getRank()); data.add(userInfoList.get(i).getM1()); data.add(userInfoList.get(i).getM2()); data.add(userInfoList.get(i).getM3()); data.add(userInfoList.get(i).getOne()); data.add(userInfoList.get(i).getM4()); data.add(userInfoList.get(i).getM5()); data.add(userInfoList.get(i).getM6()); data.add(userInfoList.get(i).getTwo()); data.add(userInfoList.get(i).getM7()); data.add(userInfoList.get(i).getM8()); data.add(userInfoList.get(i).getM9()); data.add(userInfoList.get(i).getThr()); data.add(userInfoList.get(i).getM10()); data.add(userInfoList.get(i).getM11()); data.add(userInfoList.get(i).getM12()); data.add(userInfoList.get(i).getFou()); for (int j=0;j<data.size();j++) { HSSFCell cell = row.createCell(j); cell.setCellStyle(rowStyle); if("null".equals(String.valueOf(data.get(j))) || String.valueOf(data.get(j)) == null) { cell.setCellValue(""); }else { cell.setCellValue(String.valueOf(data.get(j))); } } } } //文件名称 String fileName = "人员信息.xls"; try { ExcelUtil.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); }catch (Exception e) { e.printStackTrace(); } }

另外： 也可用https://www.e-iceblue.cn/Introduce/Spire-PDF-NET.html# 还没用过，也是文档操作的工具，不过不是免费的