参考博客：https://www.cnblogs.com/codecat/p/10948120.html
1、 package com.ainemo.console.util; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddressList; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.util.List; /** * @author jiangwentao * @date 2019/9/26 */ public class ExcelTemplateUtil { private static final Logger log = LoggerFactory.getLogger(ExcelTemplateUtil.class); /** * 生成Excel导入模板 * * @param filePath Excel文件路径 * @param fileName 文件名 * @param headers Excel列标题(数组) * @param downData 下拉框数据(数组) * @param downRows 下拉列的序号(数组,序号从0开始) * @param notes 注意事项 第二行 * @param title 标题 */ public static void createExcelTemplate(String filePath, String fileName, String[] headers, List<String[]> downData, String[] downRows, String notes, String title) { //创建工作薄 HSSFWorkbook wb = new HSSFWorkbook(); //表头样式 HSSFCellStyle style = wb.createCellStyle(); //创建一个居中格式 style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //字体样式 HSSFFont fontStyle = wb.createFont(); fontStyle.setFontName("宋体"); fontStyle.setFontHeightInPoints((short) 11); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(fontStyle); //新建sheet HSSFSheet sheet1 = wb.createSheet("Sheet1"); HSSFSheet sheet2 = wb.createSheet("Sheet2"); //生成sheet1内容 第一个sheet的第一行内容（填写须知） HSSFRow rowFirst = sheet1.createRow(0); HSSFCell cellRemark = rowFirst.createCell(0); sheet1.setColumnWidth(0, 8000); cellRemark.setCellStyle(style); cellRemark.setCellValue("填写须知:"); //生成sheet1内容 第一个sheet的第二行内容（注意事项） HSSFRow rowNotes = sheet1.createRow(1); HSSFCell cellNotes = rowNotes.createCell(0); sheet1.setColumnWidth(0, 8000); cellNotes.setCellStyle(style); cellNotes.setCellValue(notes); //生成sheet1内容 第一个sheet的第五行内容（标题） HSSFRow rowTitle = sheet1.createRow(4); HSSFCell cellTitle = rowTitle.createCell(0); sheet1.setColumnWidth(0, 8000); cellTitle.setCellStyle(style); cellTitle.setCellValue(title); //生成sheet1内容 第一个sheet的第五行内容（设置字段名称及属性） HSSFRow rowHeader = sheet1.createRow(5); for (int i = 0; i < headers.length; i++) { //获取第一行的每个单元格 HSSFCell cell = rowHeader.createCell(i); //设置每列的列宽 sheet1.setColumnWidth(i, 8000); //加样式 cell.setCellStyle(style); //往单元格里写数据 cell.setCellValue(headers[i]); } //设置下拉框数据 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; HSSFRow row; for (int r = 0; r < downRows.length; r++) { //获取下拉对象 String[] dlData = downData.get(r); int rowNum = Integer.parseInt(downRows[r]); //255以内的下拉 if (dlData.length < 255) { //255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列 //超过255个报错 sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 50000, rowNum, rowNum+1)); } else { //255以上的下拉，即下拉列表元素很多的情况 //1、设置有效性 //String strFormula = "Sheet2!$A$1:$A$5000" ; //Sheet2第A1到A5000作为下拉列表来源数据 //Sheet2第A1到A5000作为下拉列表来源数据 String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$5000"; //设置每列的列宽 sheet2.setColumnWidth(r, 8000); //设置数据有效性加载在哪个单元格上,参数分别是：从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列 //下拉列表元素很多的情况 sheet1.addValidationData(setDataValidation(strFormula, 1, 50000, rowNum, rowNum)); //2、生成sheet2内容 for (int j = 0; j < dlData.length; j++) { //第1个下拉选项，直接创建行、列 if (index == 0) { //创建数据行 row = sheet2.createRow(j); //设置每列的列宽 sheet2.setColumnWidth(j, 8000); //设置对应单元格的值 row.createCell(0).setCellValue(dlData[j]); } else { //非第1个下拉选项 int rowCount = sheet2.getLastRowNum(); //前面创建过的行，直接获取行，创建列 if (j <= rowCount) { //获取行，创建列,设置对应单元格的值 sheet2.getRow(j).createCell(index).setCellValue(dlData[j]); } else { //未创建过的行，直接创建行、创建列,设置每列的列宽 sheet2.setColumnWidth(j, 8000); //创建行、创建列,设置对应单元格的值 sheet2.createRow(j).createCell(index).setCellValue(dlData[j]); } } } index++; } }//设置某一列为正整数 if (!Objects.isNull(createExcelTemplate.getPositiveIntRows())) { for (Integer rowNum : createExcelTemplate.getPositiveIntRows()) { sheet1.addValidationData(setValidate(firstRow, endRow, rowNum, rowNum)); } }try { //写文件 File f = new File(filePath + "/" + fileName); //不存在则新增 if (!f.getParentFile().exists()) { f.getParentFile().mkdirs(); } if (!f.exists()) { f.createNewFile(); } FileOutputStream out = new FileOutputStream(f); out.flush(); wb.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } }/** * 设置单元格为正整数 * @param firstRow 开始行 * @param lastRow 结束行 * @param firstCol 开始字段列 * @param lastCol 结束字段列 */ private static HSSFDataValidation setValidate(int firstRow, int lastRow, int firstCol, int lastCol) { // 创建一个规则：1-100的数字 //OperatorType.GREATER_THAN 大于的意思 数据有效性为正整数 ValidationType.INTEGER（整数） DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.GREATER_THAN, "0", null); // 设定在哪个单元格生效 CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); // 创建规则对象 return new HSSFDataValidation(regions, constraint); }/** * * @param strFormula 下拉列表来源数据 * @param firstRow 起始行 * @param endRow 终止行 * @param firstCol 起始列 * @param endCol 终止列 */ private static HSSFDataValidation setDataValidation(String strFormula, int firstRow, int endRow, int firstCol, int endCol) { // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula); HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint); dataValidation.createErrorBox("Error", "Error"); dataValidation.createPromptBox("", null); return dataValidation; } /** * 下拉列表元素不多的情况(255以内的下拉) * @param sheet sheet * @param textList 下拉对象 * @param firstRow 起始行 * @param endRow 终止行 * @param firstCol 起始列 * @param endCol 终止列 */ private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) { DataValidationHelper helper = sheet.getDataValidationHelper(); //加载下拉列表内容 DataValidationConstraint constraint = helper.createExplicitListConstraint(textList); //DVConstraint constraint = new DVConstraint(); constraint.setExplicitListValues(textList); //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); //数据有效性对象 //DataValidation data_validation = new DataValidation(regions, constraint); return helper.createValidation(constraint, regions); } /** * 下载指定路径的Excel文件 * @param filePath 文件路径 * @param fileName 文件名 */ public static void getExcel(String filePath, String fileName, HttpServletResponse response, HttpServletRequest request) { try { //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型 response.setContentType("multipart/form-data"); //2.设置文件头：最后一个参数是设置下载文件名 response.setHeader("Content-disposition", "attachment; filename=\"" + encodeChineseDownloadFileName(request, fileName) + "\""); // response.setHeader("Content-Disposition", "attachment;filename=" // + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xls"); //中文文件名 //通过文件路径获得File对象 File file = new File(filePath + "/" + fileName); FileInputStream in = new FileInputStream(file); //3.通过response获取OutputStream对象(out) OutputStream out = new BufferedOutputStream(response.getOutputStream()); int b = 0; byte[] buffer = new byte[2048]; while ((b = in.read(buffer)) != -1) { //4.写到输出流(out)中 out.write(buffer, 0, b); } in.close(); out.flush(); out.close(); } catch (IOException e) { log.error("下载Excel模板异常", e); } } /** * 设置中文文件名 * @param pFileName 文件名 */ private static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName) throws UnsupportedEncodingException { String filename = null; String agent = request.getHeader("USER-AGENT"); //System.out.println("agent==========》"+agent); if (null != agent) { //Firefox if (-1 != agent.indexOf("Firefox")) { filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?="; } else if (-1 != agent.indexOf("Chrome")) { //Chrome filename = new String(pFileName.getBytes(), "ISO8859-1"); } else {//IE7+ filename = java.net.URLEncoder.encode(pFileName, "UTF-8"); //替换空格 filename = StringUtils.replace(filename, "+", "%20"); } } else { filename = pFileName; } return filename; } /** * 删除文件 * @param filePath 文件路径 */ public static void delFile(String filePath, String fileName) { File delFile = new File(filePath + "/" + fileName); delFile.delete(); } }
2、
/** * 生成Excel模板并导出 */ @RequestMapping("/getExcelTemplate") public void getExcelTemplate(HttpServletRequest request, HttpServletResponse response){ //模板名称 String fileName = "excel_template_voucherWallet.xlsx"; String filePath = request.getSession().getServletContext().getRealPath("/temp/"); //列字段标题 String[] headers = {"姓名","代理商","国家码","手机号","代金券类型","代金券张数"}; //设置填Excel的注意事项 String notes = "1、手机号不能为空; 2、国家码默认为+86(中国)"; //设置Excel标题 String title = "代金券钱包";//设置数据有效性(第几列为正整数) Integer [] positiveIntRows = {5};//下拉框数据 List<String[]> downData = new ArrayList<>(); List<VoucherType> voucherTypeList; try { voucherTypeList = crmVoucherWalletService.getAllVoucherTypes(); } catch (Exception e) { LOGGER.error("fail to get voucherType list", e); voucherTypeList = null; } if (CollectionUtils.isEmpty(voucherTypeList)) { downData.add(null); } else { List<String> typeList = voucherTypeList.stream().map(VoucherType::getTypeName).collect(Collectors.toList()); String[] types = new String[typeList.size()]; downData.add(typeList.toArray(types)); } //下拉的列序号数组(序号从0开始) String [] downRows = {"4"}; try { ExcelTemplateUtil.createExcelTemplate(filePath, fileName, headers, downData, downRows, notes, title, positiveIntRows);ExcelTemplateUtil.getExcel(filePath, fileName, response, request); ExcelTemplateUtil.delFile(filePath, fileName); } catch (Exception e) { LOGGER.error("fail to get excel template"); } }
数据有效性参考
参考链接:https://blog.csdn.net/nihaoqiulinhe/article/details/56017450
2202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
