导出工具类
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;
@Component
public class ExcelUtils {
@Resource
private ResourceLoader resourceLoader;
private static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
static{
System.setProperty("java.awt.headless", "true");
}
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);
if(ObjectUtils.isNotEmpty(widths) && ObjectUtils.isNotEmpty(widths[j]) ){
sheet.setColumnWidth(j, widths[j]);
}else{
sheet.autoSizeColumn(j);
int colWidth = sheet.getColumnWidth(j) * width / 10;
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);
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();
}
}
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();
}
}
public static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
try {
String name = URLEncoder.encode(fileName, "UTF-8");
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");
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
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;
}
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;
}
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 = getWorkBook(file);
if (ObjectUtils.isNotEmpty(workbook)) {
Sheet sheet = workbook.getSheetAt(0);
if (ObjectUtils.isEmpty(sheet)) {
return resultData;
}
resultSize = sheet.getLastRowNum() + 1;
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
if(ObjectUtils.isNotEmpty(endRow)){
lastRowNum = endRow;
}
for (int rowNum = firstRowNum + startRow; rowNum <= lastRowNum; rowNum++) {
Row row = sheet.getRow(rowNum);
if (rowIsEmpty(row)) {
break;
}
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
StringBuilder stringBuffer = new StringBuilder();
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
String cellValue = getCellValue(cell);
stringBuffer.append(getCellValue(cell)).append(",&");
}
resultData.add(stringBuffer.toString());
}
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
}
return resultData;
}
public static boolean checkFile(MultipartFile file) throws IOException {
if (null == file) {
log.error("文件不存在!");
return false;
}
String fileName = file.getOriginalFilename();
if (ObjectUtils.isEmpty(fileName) || (!fileName.endsWith("xls") && !fileName.endsWith("xlsx"))) {
log.error(fileName + "不是excel文件");
return false;
}
return true;
}
public static Workbook getWorkBook(MultipartFile file) {
String fileName = file.getOriginalFilename();
Workbook workbook = null;
try {
InputStream is = file.getInputStream();
if(ObjectUtils.isNotEmpty(fileName)){
if (fileName.endsWith("xls")) {
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx")) {
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:
cellValue = 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) {
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;
}
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;
}
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();
}
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 {
@Resource
private 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模板存放位置