导出工具类

package com.yutu.garden.utils;import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.core.io.ResourceLoader;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;/*** @ClassName: ExcelUtils* @Description: Excel相关操作* @Author* @Date 2022/4/11* @Version 1.0*/
@Component
public class ExcelUtils {@Resourceprivate  ResourceLoader resourceLoader;private static Logger log = LoggerFactory.getLogger(ExcelUtils.class);static{System.setProperty("java.awt.headless", "true");}/*** 设置表头* @param sheet sheet* @param str 表头数据* @param startNum 开始行* @param height 高度* @param style 样式*/public static void setTitle(HSSFSheet sheet, String[] str,int startNum,Short height,HSSFCellStyle style,Integer[] widths,int width) {try {HSSFRow row = sheet.createRow(startNum);if(ObjectUtils.isEmpty(height)){height = (short)(20*20);}row.setHeight(height);//创建表头名称HSSFCell cell;for (int j = 0; j < str.length; j++) {cell = row.createCell(j);//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度if(ObjectUtils.isNotEmpty(widths) && ObjectUtils.isNotEmpty(widths[j]) ){sheet.setColumnWidth(j, widths[j]);}else{sheet.autoSizeColumn(j);int colWidth = sheet.getColumnWidth(j) * width / 10;
//					if (colWidth > 255) {
//						colWidth = 255;
//					}sheet.setColumnWidth(j, colWidth);}cell.setCellValue(str[j]);cell.setCellStyle(style);}} catch (Exception e) {e.printStackTrace();}}public static void setTitle(HSSFSheet sheet, List<String> str,int startNum,Short height,HSSFCellStyle style,Integer[] widths) {try {HSSFRow row = sheet.createRow(startNum);if(ObjectUtils.isEmpty(height)){height = (short)(20*20);}row.setHeight(height);//创建表头名称HSSFCell cell;for (int j = 0; j < str.size(); j++) {cell = row.createCell(j);//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度if(ObjectUtils.isNotEmpty(widths) && ObjectUtils.isNotEmpty(widths[j]) ){sheet.setColumnWidth(j, widths[j]);}else{sheet.autoSizeColumn(j);sheet.setColumnWidth(j, sheet.getColumnWidth(j) * 30 / 10);}cell.setCellValue(str.get(j));cell.setCellStyle(style);}} catch (Exception e) {e.printStackTrace();}}/*** 方法名：setData* 功能：表格赋值*/public static void setData(HSSFSheet sheet, List<Object[]> data,int startNum,HSSFCellStyle style) {try{int rowNum = startNum+1;for (int i = 0; i < data.size(); i++) {HSSFRow row = sheet.createRow(rowNum);for (int j = 0; j < data.get(i).length; j++) {HSSFCell cell = row.createCell(j);if(ObjectUtils.isEmpty(data.get(i)[j])){cell.setCellValue("");}else{cell.setCellValue(data.get(i)[j].toString());}cell.setCellStyle(style);}rowNum++;}}catch (Exception e){e.printStackTrace();}}public static void setDataByList(HSSFSheet sheet, List<List<Object>> data,int startNum) {try{int rowNum = startNum+1;for (int i = 0; i < data.size(); i++) {HSSFRow row = sheet.createRow(rowNum);for (int j = 0; j < data.get(i).size(); j++) {if(ObjectUtils.isEmpty(data.get(i).get(j))){row.createCell(j).setCellValue("");}else{row.createCell(j).setCellValue(data.get(i).get(j).toString());}}rowNum++;}}catch (Exception e){e.printStackTrace();}}/*** 方法名：setBrowser* 功能：使用浏览器下载*/public static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {try {//清空response//response.reset();//设置response的HeaderString name = URLEncoder.encode(fileName, "UTF-8");//Content-disposition 的 attachment参数将文件作为附件下载//response.setHeader("Content-disposition", "attachment;filename=" + fileName+".xlsx");response.setHeader( "Content-Disposition", "attachment;filename=\"" + name + "\".xlsx;filename*=utf-8''" + name +".xlsx");OutputStream os = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");//将excel写入到输出流中workbook.write(os);os.flush();os.close();} catch (Exception e) {e.printStackTrace();}}/*** 居中* @param workbook* @return*/public static HSSFCellStyle getStyleByCENTER(HSSFWorkbook workbook,int size){//设置标题样式HSSFCellStyle style = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setBold(true);font.setFontName("宋体");font.setFontHeightInPoints((short) size);style.setFont(font);//水平居中style.setAlignment(HorizontalAlignment.CENTER);//垂直居中style.setVerticalAlignment(VerticalAlignment.CENTER);style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));return style;}/*** 带边框的样式* @param workbook workbook* @param size 字体大小* @param isText 是否正文* @return*/public static HSSFCellStyle getStyleByBorder(HSSFWorkbook workbook,int size,boolean isText){//设置标题样式HSSFCellStyle style = workbook.createCellStyle();HSSFFont font = workbook.createFont();if(!isText){font.setBold(true);}font.setFontName("宋体");font.setFontHeightInPoints((short) size);style.setFont(font);//水平居中style.setAlignment(HorizontalAlignment.CENTER);//垂直居中style.setVerticalAlignment(VerticalAlignment.CENTER);style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//边框style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);return style;}public static HSSFCellStyle getDataStyle(HSSFWorkbook workbook,int size,boolean isSetBold,HSSFColor.HSSFColorPredefined background){//设置标题样式HSSFCellStyle style = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setBold(isSetBold);font.setFontName("宋体");font.setFontHeightInPoints((short) size);style.setFont(font);if(ObjectUtils.isNotEmpty(background)){//设置填充方式style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillForegroundColor(background.getIndex());}return style;}/*** 解析上传的excel， 默认只解析第一张Sheet** @param file     excel* @param startRow 数据开始行* @return List<String [ ]>* @throws IOException*/public static List<String> getExcelData(MultipartFile file, int startRow, Integer endRow){int resultSize = 0;ArrayList<String> resultData = new ArrayList<>(resultSize);try {if (!checkFile(file)) {log.error("上传的excel文件格式有问题");return resultData;}//获得Workbook工作薄对象Workbook workbook = getWorkBook(file);if (ObjectUtils.isNotEmpty(workbook)) {//获取第一张sheet工作表Sheet sheet = workbook.getSheetAt(0);if (ObjectUtils.isEmpty(sheet)) {return resultData;}// 重新初始化List结果大小resultSize = sheet.getLastRowNum() + 1;//获得当前sheet的开始行int firstRowNum = sheet.getFirstRowNum();//获得当前sheet的结束行int lastRowNum = sheet.getLastRowNum();if(ObjectUtils.isNotEmpty(endRow)){lastRowNum = endRow;}//循环除了startRow的所有行,如果要循环除第一行以外的就firstRowNum+1for (int rowNum = firstRowNum + startRow; rowNum <= lastRowNum; rowNum++) {//获得当前行Row row = sheet.getRow(rowNum);if (rowIsEmpty(row)) {break;}//获得当前行的开始列int firstCellNum = row.getFirstCellNum();//获得当前行的列数int lastCellNum = row.getLastCellNum();//String[] cells = new String[lastCellNum];StringBuilder stringBuffer = new StringBuilder();//循环当前行for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {Cell cell = row.getCell(cellNum);//cells[cellNum] = getCellValue(cell);String cellValue = getCellValue(cell);stringBuffer.append(getCellValue(cell)).append(",&");//加个&防止有答案中有,的}//resultData.add(cells);resultData.add(stringBuffer.toString());}workbook.close();}} catch (IOException e) {e.printStackTrace();}return resultData;}/*** 检查文件格式** @param file* @throws IOException*/public static boolean checkFile(MultipartFile file) throws IOException {if (null == file) {log.error("文件不存在！");return false;}//获得文件名String fileName = file.getOriginalFilename();//判断文件是否是excel文件if (ObjectUtils.isEmpty(fileName) || (!fileName.endsWith("xls") && !fileName.endsWith("xlsx"))) {log.error(fileName + "不是excel文件");return false;}return true;}/*** 获取工作簿对象** @param file* @return*/public static Workbook getWorkBook(MultipartFile file) {//获得文件名String fileName = file.getOriginalFilename();//创建Workbook工作薄对象，表示整个excelWorkbook workbook = null;try {//获取excel文件的io流InputStream is = file.getInputStream();//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象if(ObjectUtils.isNotEmpty(fileName)){if (fileName.endsWith("xls")) {//2003workbook = new HSSFWorkbook(is);} else if (fileName.endsWith("xlsx")) {//2007 及2007以上workbook = new XSSFWorkbook(is);}}} catch (IOException e) {log.error(e.getMessage());}return workbook;}public static String getCellValue(Cell cell) {String cellValue = "";if (cell == null) {return cellValue;}switch (cell.getCellTypeEnum()) {case NUMERIC://数字cellValue = stringDateProcess(cell);break;case STRING://字符串cellValue = String.valueOf(cell.getStringCellValue());break;case BOOLEAN://BooleancellValue = String.valueOf(cell.getBooleanCellValue());break;case FORMULA://公式cellValue = String.valueOf(cell.getCellFormula());break;case BLANK://空值cellValue = "";break;case ERROR://故障cellValue = "非法字符";break;default:cellValue = "未知类型";break;}return cellValue;}public static String stringDateProcess(Cell cell) {String result = new String();if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式SimpleDateFormat sdf = null;if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {sdf = new SimpleDateFormat("HH:mm");} else {// 日期sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");}Date date = cell.getDateCellValue();result = sdf.format(date);} else if (cell.getCellStyle().getDataFormat() == 58) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);result = sdf.format(date);} else {double value = cell.getNumericCellValue();CellStyle style = cell.getCellStyle();DecimalFormat format = new DecimalFormat();String temp = style.getDataFormatString();// 单元格设置成常规if ("General".equals(temp)) {int decimalPlaces = countDecimalPlaces(value);StringBuilder pattern = new StringBuilder("#.");for (int i = 0; i < decimalPlaces; i++) {pattern.append("#");}format.applyPattern(pattern.toString());}result = format.format(value);}return result;}public static int countDecimalPlaces(double value) {String stringValue = Double.toString(value);int integerPlaces = stringValue.indexOf('.');return stringValue.length() - integerPlaces - 1;}/*** @return * @param null* @Author* @Description //TODO 判断excel的row是否全为空* @Date 2019/12/2 19:30* @Param*/public static boolean rowIsEmpty(Row row) {if (null == row) {return true;}for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {Cell cell = row.getCell(c);if (cell != null && cell.getCellTypeEnum() != CellType.BLANK) {return false;}}return true;}/*** 模板下载* @param response* @param request* @param filename* @param path* @throws IOException*/public void downloadTemplate(HttpServletResponse response, HttpServletRequest request, String filename, String path) throws IOException {InputStream inputStream = null;ServletOutputStream servletOutputStream = null;try {org.springframework.core.io.Resource resource = resourceLoader.getResource("classpath:" + path);response.setContentType("application/vnd.ms-excel");response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");response.addHeader("charset", "utf-8");response.addHeader("Pragma", "no-cache");String encodeName = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());response.setHeader("Content-Disposition", "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);inputStream = resource.getInputStream();servletOutputStream = response.getOutputStream();IOUtils.copy(inputStream, servletOutputStream);response.flushBuffer();} catch (Exception e) {e.printStackTrace();} finally {try {if (servletOutputStream != null) {servletOutputStream.close();}if (inputStream != null) {inputStream.close();}// jvm的垃圾回收System.gc();} catch (Exception e) {e.printStackTrace();}}}
}

控制层接口

package com.yutu.garden.controller;import com.yutu.garden.utils.ExcelUtilsWP;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;@RestController
@RequestMapping("excel")
@Api(tags = "Excel模板下载")
public class ExcelTemplateController {@Resourceprivate ExcelUtilsWP excelUtils;@ApiOperation("下载苗木计划清单模板")@GetMapping("/downloadPunishTemplate")public void downloadPunishTemplate(HttpServletResponse response, HttpServletRequest request) throws IOException {String filename = "苗木计划清单模板.xlsx";String path = "template/苗木计划清单模板.xlsx";excelUtils.downloadTemplate(response,request,filename,path);}
}

Excel模板存放位置