业务需求需要导出Excel，然而xls部分公式无法使用，且导出效率和行数有限制，因此更换导出文件为xlsx，下拉框个数根据参数传入。代码写得有点乱，如下代码为导出excel xlsx格式的代码
/***导出Excel xlsx* @param excelHeader 导出excel的头部 格式UserNo#userNo(输出excel的列的首行名字#所在列对应的实体bean的属性名字)，多列以','隔开 参考UserController里面的导出用户功能;* @param dataList 需要导出数据的集合。* @param indexs 下拉框索引* @param slist 下拉框数组，支持多个下拉框*/public static <T> HSSFWorkbook downloadExcel(HttpServletResponse response, String fileName, String excelHeader, Integer[] indexs, List<String[]> slist) throws Exception {String[] excelHeaderArray = null;if (excelHeader != null) {excelHeaderArray = excelHeader.split(",");}response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));XSSFWorkbook wb = new XSSFWorkbook();XSSFCellStyle styleb = wb.createCellStyle();styleb.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式XSSFSheet data = wb.createSheet("Sheet1");XSSFSheet select = wb.createSheet("select");wb.setSheetHidden(1, true);Row rowFirst = data.createRow(0);//第一个sheet的第一行为标题for (int i = 0; i < excelHeaderArray.length; i++) {Cell cell = rowFirst.createCell(i); //获取第一行的每个单元格XSSFCellStyle style = wb.createCellStyle();style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式//标头字体样式Font fontStyle = wb.createFont();fontStyle.setFontName(Const.EXPORT_EXCEL_HEADER_FONT);fontStyle.setFontHeightInPoints(Const.EXPORT_EXCEL_HEADER_FONT_SIZE);fontStyle.setBoldweight(Font.BOLDWEIGHT_BOLD);style.setFont(fontStyle);String excelHeaderstr = excelHeaderArray[i];if (StringUtils.isNotEmpty(excelHeaderstr) && excelHeaderstr.contains("*")) {fontStyle.setColor(Font.COLOR_RED);} else {//fontStyle.setColor(Font.COLOR_NORMAL);}data.setColumnWidth(i, excelHeaderArray[i].length() * 2 * 256); //设置每列的列宽cell.setCellStyle(style); //加样式cell.setCellValue(excelHeaderArray[i]); //往单元格里写数据cell.setCellType(Cell.CELL_TYPE_STRING);}//设置单元格的字体XSSFCellStyle style2 = wb.createCellStyle();XSSFDataFormat format = wb.createDataFormat();style2.setDataFormat(format.getFormat("@"));style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);style2.setBorderTop(XSSFCellStyle.BORDER_THIN);style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);style2.setBorderRight(XSSFCellStyle.BORDER_THIN);style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);Font dataFont = wb.createFont();dataFont.setFontHeightInPoints(Const.EXPORT_EXCEL_BODY_FONT_SIZE);dataFont.setFontName(Const.EXPORT_EXCEL_BODY_FONT);style2.setFont(dataFont);//默认创建100行数据for (int i = 1; i < 100; i++) {Row row = data.createRow(i);for (int j = 0; j < excelHeaderArray.length; j++) {Cell cell = row.createCell(j); //获取第一行的每个单元格cell.setCellType(Cell.CELL_TYPE_STRING);cell.setCellStyle(style2);}}//列 默认只支持26列，需要增加在数组中增加String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};int index = 0;Row row = null;//循环输出slist 下拉框数组集合。for (int i = 0; i < slist.size(); i++) {String[] selectData = slist.get(i);if (selectData == null) {selectData = new String[0];}下拉框的索引位置int rowNum = indexs[i];//下拉框公式String strFormula = "'select'!$" + arr[index] + "$2:$" + arr[index] + "$" + (selectData.length + 1); //select第A1到A5000作为下拉列表来源数据select.setColumnWidth(i, 4000); //设置每列的列宽//设置下拉框数据校验data.addValidationData(ExcelUtil.setXSSFDataValidation(select, strFormula, 1, 50000, rowNum, rowNum)); //下拉列表元素很多的情况，循环下入到隐藏的sheet中for (int j = 0; j < selectData.length; j++) {if (index == 0) {row = select.createRow(j + 1); //创建数据行select.setColumnWidth(j + 1, 4000); //设置每列的列宽row.createCell(0).setCellValue(selectData[j]); //设置对应单元格的值} else {int rowCount = select.getLastRowNum();//判断最后一行数据的索引是否大于当前的行数，大于则需要创建一行，小于只需要创建单元格，赋值就好了。if (j < rowCount) {select.getRow(j + 1).createCell(index).setCellValue(selectData[j]); //设置对应单元格的值} else {select.setColumnWidth(j + 1, 4000); //设置每列的列宽//创建行、创建列select.createRow(j + 1).createCell(index).setCellValue(selectData[j]); //设置对应单元格的值}}}index++;}try {OutputStream outputStream = response.getOutputStream();// 打开流wb.write(outputStream);// HSSFWorkbook写入流//wb.close();// HSSFWorkbook关闭outputStream.flush();// 刷新流outputStream.close();// 关闭流} catch (Exception e) {e.printStackTrace();}return null;}
ExcelUtil:setXSSFDataValidation
public static DataValidation setXSSFDataValidation(XSSFSheet sheet,String formula,int firstRow, int endRow, int firstCol, int endCol){CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);DataValidationHelper dvHelper = sheet.getDataValidationHelper();XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.createFormulaListConstraint(formula);XSSFDataValidation validation =(XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);//sheet.addValidationData(validation);return validation;}
