如何使用POI下载、导出Excel模板以及设置样式？
如何使用POI生成Excel模板以及设置样式？具体设置样式参考我写的工具类模板
1.导入pom文件
<!--ApachePOI依赖包--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> <!--支持excel 2007版本的操作--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>ooxml-schemas</artifactId> <version>1.4</version> </dependency>
2.工具类
package cn.mindgd.util; import lombok.Data; import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.util.Base64Utils; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * @author JiaXinMa * @description Excel工具类 * @date 2021/4/12 */ @Slf4j @Data public class ExcelUtils { private Workbook workbook; private String pattern;// 日期格式 public ExcelUtils(Workbook workbook) { this.workbook = workbook; } /** * @author: JiaXinMa * @description: 下载2007模板 * @date: 2021/6/8 */ public static void downloadExcel2003(HttpServletResponse response, HttpServletRequest request, List<List<Object>> cells, String fireName, String sheetName) { try { fireName = fireName + ".xls"; ExcelUtils.setResponseAttribute(response, request, fireName); HSSFWorkbook workbook = getHSSFWorkbook(cells, sheetName); ServletOutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); //关闭流 workbook.close();//在servlet 完成生命周期之后servlet 容器会自动关闭流 outputStream.close(); } catch (IOException e) { log.info("下载模板失败！！"); e.printStackTrace(); } } /** * @author: JiaXinMa * @description: 获取一个文件对象2003版本 HSSFWorkbook * @date: 2021/6/8 * .xls */ public static HSSFWorkbook getHSSFWorkbook(List<List<Object>> rowsData, String sheetName) { //1.创建一个文件对象HSSFWorkbook HSSFWorkbook workbook = new HSSFWorkbook(); //2.创建表单sheet HSSFSheet sheet = workbook.createSheet(sheetName); //3.数据进入Workbook writeRowsDataOnWorkbook(rowsData, sheet); return workbook; } /** * @author: JiaXinMa * @description: 下载2007模板 * @date: 2021/6/8 */ public static void downloadExcel2007(HttpServletResponse response, HttpServletRequest request, List<List<Object>> cells, String fireName, String sheetName) { try { fireName = fireName + ".xlsx"; ExcelUtils.setResponseAttribute(response, request, fireName); XSSFWorkbook workbook = getXSSFWorkbook(cells, sheetName); ServletOutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); //关闭流 workbook.close();//在servlet 完成生命周期之后servlet 容器会自动关闭流 outputStream.close(); } catch (IOException e) { log.info("下载模板失败！！"); e.printStackTrace(); } } /** * @author: JiaXinMa * @description: 获取一个文件对象2007版本 XSSFWorkbook * @date: 2021/6/8 * .xlsx */ public static XSSFWorkbook getXSSFWorkbook(List<List<Object>> rowsData, String sheetName) { //1.创建一个文件对象XSSFWorkbook XSSFWorkbook workbook = new XSSFWorkbook(); //2.创建表单sheet XSSFSheet sheet = workbook.createSheet(sheetName); //3.数据进入Workbook writeRowsDataOnWorkbook(rowsData, sheet); return workbook; } /** * @author: JiaXinMa * @description: 写数据进入Workbook * @date: 2021/6/8 * 如果想设置样式可以在这里修改 */ private static void writeRowsDataOnWorkbook(List<List<Object>> rowsData, Sheet sheet) { // 遍历输出每行 for (int i = 0; i < rowsData.size(); i++) { // 创建一个row Row row = sheet.createRow(i); // 每一行的数据 List<Object> rowData = rowsData.get(i); // 遍历生成每个单元格 for (int j = 0; j < rowData.size(); j++) { Cell cell = row.createCell(j); // 设置单元格样式 //cell.setCellStyle() //单元格赋值 Object cellData = rowData.get(j); setDataOnCell(cell, cellData); } } } /** * @author: JiaXinMa * @description: 导出 MaterielStorage excel文件 * @date: 2021/6/8 * sheet为9列 * 第一行为9个单元格合并为一个单元格，背景为灰色，居中 * 第二行为字体加粗，背景为灰色 * 如果想要设置样式的可以参考该方法 */ public static void downloadMaterielStorageExcelFile(HttpServletResponse response, HttpServletRequest request, List<List<Object>> rowsData, String fileName, String sheetName) { try { // 创建一个Workbook XSSFWorkbook workbook = new XSSFWorkbook(); // 创建一个Sheet XSSFSheet XSSFSheet sheet = workbook.createSheet(sheetName); // 合并单元格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8); sheet.addMergedRegion(region); // 设置列宽 setMaterielStorageColumnWidthOnSheet(sheet); CellStyle titleStyle = workbook.createCellStyle();//标题 CellStyle contentFirstStyle = workbook.createCellStyle();//内容第一行 CellStyle contentStyle = workbook.createCellStyle();//内容 //设置单元格背景 titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); contentFirstStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); contentFirstStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置单元格边框 setBorderOnCell(titleStyle); setBorderOnCell(contentFirstStyle); setBorderOnCell(contentStyle); // 设置单元格字体 setFontStylePlus(workbook, titleStyle, "等线", 20, false); setFontStylePlus(workbook, contentFirstStyle, "等线", 14, true); setFontStylePlus(workbook, contentStyle, "等线", 11, false); // 设置单元格居中 titleStyle.setAlignment(HorizontalAlignment.CENTER); contentFirstStyle.setAlignment(HorizontalAlignment.CENTER); contentStyle.setAlignment(HorizontalAlignment.CENTER); // 遍历输出每行 for (int i = 0; i < rowsData.size(); i++) { // 创建一个row XSSFRow row = sheet.createRow(i); // 每一行的数据 List<Object> rowData = rowsData.get(i); // 遍历生成每个单元格 for (int j = 0; j < rowData.size(); j++) { XSSFCell cell = row.createCell(j); // 设置单元格样式 if (i == 0) { cell.setCellStyle(titleStyle);//标题样式 } else if (i == 1) { cell.setCellStyle(contentFirstStyle);//内容第一行样式 } else { cell.setCellStyle(contentStyle);// 内容样式 } //单元格赋值 Object cellData = rowData.get(j); setDataOnCell(cell, cellData); } } fileName = fileName + ".xls"; ExcelUtils.setResponseAttribute(response, request, fileName); ServletOutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); //关闭流 workbook.close();//在servlet 完成生命周期之后servlet 容器会自动关闭流 outputStream.close(); } catch (IOException e) { log.info("导出excel失败"); } } /** * @author: JiaXinMa * @description: 设置列的宽度 * @date: 2021/6/8 * 设置MaterielStorage的Excel文件 */ private static void setMaterielStorageColumnWidthOnSheet(Sheet sheet) { sheet.setColumnWidth(0, 2500); // 第1列的宽度为2000 sheet.setColumnWidth(1, 4500); // 第2列的宽度为4500 sheet.setColumnWidth(2, 4500); // 第3列的宽度为4500 sheet.setColumnWidth(3, 4500); // 第4列的宽度为4500 sheet.setColumnWidth(4, 4500); // 第5列的宽度为4500 sheet.setColumnWidth(5, 4500); // 第6列的宽度为4500 sheet.setColumnWidth(6, 4500); // 第7列的宽度为4500 sheet.setColumnWidth(7, 4500); // 第8列的宽度为4500 sheet.setColumnWidth(8, 13000); // 第9列的宽度为13000 } /** * @author: JiaXinMa * @description: 导出 MaterielStorage汇总 excel文件 * @date: 2021/6/17 * sheet为11列 * 第一行为11个单元格合并为一个单元格，背景为灰色，居中 * 第二行为字体加粗，背景为灰色 * 如果想要设置样式的可以参考该方法 */ public static void downloadMaterielStorageTotalExcelFile(HttpServletResponse response, HttpServletRequest request, List<List<Object>> rowsData, String fileName, String sheetName) { try { // 创建一个Workbook XSSFWorkbook workbook = new XSSFWorkbook(); // 创建一个Sheet XSSFSheet XSSFSheet sheet = workbook.createSheet(sheetName); // 合并单元格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, 10); sheet.addMergedRegion(region); // 设置列宽 setMaterielStorageTotalColumnWidthOnSheet(sheet); CellStyle titleStyle = workbook.createCellStyle();//标题 CellStyle contentFirstStyle = workbook.createCellStyle();//内容第一行 CellStyle contentStyle = workbook.createCellStyle();//内容 //设置单元格背景 titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); contentFirstStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); contentFirstStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置单元格边框 setBorderOnCell(titleStyle); setBorderOnCell(contentFirstStyle); setBorderOnCell(contentStyle); // 设置单元格字体 setFontStylePlus(workbook, titleStyle, "等线", 20, false); setFontStylePlus(workbook, contentFirstStyle, "等线", 14, true); setFontStylePlus(workbook, contentStyle, "等线", 11, false); // 设置单元格居中 titleStyle.setAlignment(HorizontalAlignment.CENTER); contentFirstStyle.setAlignment(HorizontalAlignment.CENTER); contentStyle.setAlignment(HorizontalAlignment.CENTER); // 遍历输出每行 for (int i = 0; i < rowsData.size(); i++) { // 创建一个row XSSFRow row = sheet.createRow(i); // 每一行的数据 List<Object> rowData = rowsData.get(i); // 遍历生成每个单元格 for (int j = 0; j < rowData.size(); j++) { XSSFCell cell = row.createCell(j); // 设置单元格样式 if (i == 0) { cell.setCellStyle(titleStyle);//标题样式 } else if (i == 1) { cell.setCellStyle(contentFirstStyle);//内容第一行样式 } else { cell.setCellStyle(contentStyle);// 内容样式 } //单元格赋值 Object cellData = rowData.get(j); setDataOnCell(cell, cellData); } } fileName = fileName + ".xls"; ExcelUtils.setResponseAttribute(response, request, fileName); ServletOutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); //关闭流 workbook.close();//在servlet 完成生命周期之后servlet 容器会自动关闭流 outputStream.close(); } catch (IOException e) { log.info("导出excel失败"); } } /** * @author: JiaXinMa * @description: 设置列的宽度 * @date: 2021/6/17 * 设置MaterielStorage汇总的Excel文件 */ private static void setMaterielStorageTotalColumnWidthOnSheet(Sheet sheet) { sheet.setColumnWidth(0, 2500); // 第1列的宽度为2000 for (int i = 1; i < 11; i++) {// 第2-11列的宽度为4500 sheet.setColumnWidth(i, 4500); } } /** * @author: JiaXinMa * @description: 设置列的宽度 * @date: 2021/6/8 * Integer[] rowColumnWidth 每一列的宽度,有几列就有几个长度 */ private static void setColumnWidthOnSheetPlus(Sheet sheet, Integer[] rowColumnWidth) { for (int i = 0; i < rowColumnWidth.length; i++) { sheet.setColumnWidth(i, rowColumnWidth[i]); } } /** * @author: JiaXinMa * @description: 设置字体 * @date: 2021/6/8 */ private static void setFontStylePlus(Workbook workbook, CellStyle cellStyle, String fontName, int fontSize, Boolean isBold) { Font font = workbook.createFont(); font.setColor((short) 42); // 设置字体颜色 font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getColor().getIndex()); // XSSFColor中未找到颜色和short数值的映射,使用HSSFColor来定位颜色的short值 font.setFontName(fontName); // 设置字体 font.setFontHeightInPoints((short) fontSize);// 设置字体大小 font.setBold(isBold);//设置粗体显示 // font.setUnderline(HSSFFont.SYMBOL_CHARSET);//设置文字下划线 cellStyle.setFont(font); } /** * @author: JiaXinMa * @description: 设置字体 * @date: 2021/6/8 */ private static void setFontStyle(Workbook workbook, CellStyle cellStyle) { Font font = workbook.createFont(); font.setColor((short) 42); // 设置字体颜色 font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getColor().getIndex()); // XSSFColor中未找到颜色和short数值的映射,使用HSSFColor来定位颜色的short值 font.setFontName("宋体"); // 设置字体 font.setFontHeightInPoints((short) 12);// 设置字体大小 // font.setBold(true);//设置粗体显示 // font.setUnderline(HSSFFont.SYMBOL_CHARSET);//设置文字下划线 cellStyle.setFont(font); } /** * @author: JiaXinMa * @description: 给每一行赋值和样式 * @date: 2021/6/7 */ private static void setDataAndStyleOnRow(CellStyle titleStyle, Row row, List<Object> rowData) { for (int j = 0; j < rowData.size(); j++) { //给单元格设置样式 Cell cell = row.createCell(j); cell.setCellStyle(titleStyle); //给单元格赋值 Object cellData = rowData.get(j); setDataOnCell(cell, cellData); } } /** * @author: JiaXinMa * @description: 给单元格设置边框 * @date: 2021/6/7 */ private static void setBorderOnCell(CellStyle cellStyle) { cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框 cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框 cellStyle.setBorderTop(BorderStyle.THIN);// 上边框 cellStyle.setBorderRight(BorderStyle.THIN);// 右边框 } /** * @author: JiaXinMa * @description: 给单元格赋值 * @date: 2021/6/7 */ private static void setDataOnCell(Cell cell, Object cellData) { // 如果为空,就不做设值处理 if (cellData == null) { return; } // String类型数值 if (cellData.getClass() == String.class) { cell.setCellValue((String) cellData); } // double类型数值 else if (cellData.getClass() == double.class || cellData.getClass() == Double.class) { cell.setCellValue((Double) cellData); } // float类型数值 else if (cellData.getClass() == float.class || cellData.getClass() == Float.class) { cell.setCellValue((Float) cellData); } // integer类型数值 else if (cellData.getClass() == int.class || cellData.getClass() == Integer.class) { cell.setCellValue((Integer) cellData); } } /** * @author: JiaXinMa * @description: 设置响应头的属性 * @date: 2021/6/8 */ public static void setResponseAttribute(HttpServletResponse response, HttpServletRequest request, String fileName) throws UnsupportedEncodingException { response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); String agent = request.getHeader("USER-AGENT").toLowerCase(); String uncodeFileNameString = ""; if (agent.contains("firefox")) {//判断是否火狐 uncodeFileNameString = "=?UTF-8?B?" + (new String(Base64Utils.encodeToString(fileName.getBytes("UTF-8")))) + "?="; } else { uncodeFileNameString = URLEncoder.encode(fileName, "UTF-8"); uncodeFileNameString = uncodeFileNameString.replace("+", "%20"); // 空格被转为了 "+" ，要转成utf-8的空格符号 "%20" } response.setContentType("application/octet-stream;charset=UTF-8"); response.setHeader("Content-disposition", "attachment; filename=" + uncodeFileNameString); } /** * 是否是2003的excel，返回true是2003 */ public static boolean isExcel2003(String filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); } /** * 是否是2007的excel，返回true是2007 */ public static boolean isExcel2007(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } /** * 验证EXCEL文件 */ public static boolean validateExcel(String filePath) { if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) { return false; } return true; } /** * 读取 Excel 第一页数据 */ public List<List<String>> read() throws Exception { return read(0, 0, getRowCount(0) - 1); } /** * 读取 Excel 数据 * 指定 sheet 页，从 0 开始 */ public List<List<String>> read(int sheetIx) throws Exception { return read(sheetIx, 0, getRowCount(sheetIx) - 1); } /** * 读取 Excel 数据 * 指定 sheet 页，从 0 开始 * 指定开始行，从 0 开始 * 指定结束行，从 0 开始 */ public List<List<String>> read(int sheetIx, int start, int end) throws Exception { Sheet sheet = workbook.getSheetAt(sheetIx); List<List<String>> list = new ArrayList<List<String>>(); if (end > getRowCount(sheetIx)) { end = getRowCount(sheetIx); } int cols = sheet.getRow(0).getLastCellNum(); // 第一行总列数 for (int i = start; i <= end; i++) { List<String> rowList = new ArrayList<String>(); Row row = sheet.getRow(i); for (int j = 0; j < cols; j++) { if (row == null) { rowList.add(null); continue; } rowList.add(getCellValueToString(row.getCell(j))); } list.add(rowList); } return list; } /** * 返回sheet中的行数 */ public int getRowCount(int sheetIx) { Sheet sheet = workbook.getSheetAt(sheetIx); if (sheet.getPhysicalNumberOfRows() == 0) { return 0; } return sheet.getLastRowNum() + 1; } /** * 将转换单元格的类型为String */ private String getCellValueToString(Cell cell) { String strCell = ""; if (cell == null) { return null; } switch (cell.getCellType()) { case BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue()); break; case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (pattern != null) { SimpleDateFormat sdf = new SimpleDateFormat(pattern); strCell = sdf.format(date); } else { strCell = date.toString(); } break; } // 不是日期格式，则防止当数字过长时以科学计数法显示 cell.setCellType(CellType.STRING); strCell = cell.toString(); break; case STRING: strCell = cell.getStringCellValue(); break; default: break; } return strCell; } }
3.具体业务层和控制层我就不列代码了，直接调用工具类的方法就行
效果如下：
控制层最好就是无返回值
如果想读取excel文件可以调用工具类的 read()方法，大家可以结合自己的业务多尝试写一些工具类，一起学习交流
想看更多精彩内容，可以关注我的CSDN
