Spring Boot中前端通过请求接口下载后端存放的Excel模板以及解决使用HSSFWorkbook 导出的Excel，wps能打开office打不开问题
【代码】Spring Boot中前端通过请求接口下载后端存放的Excel模板。
·
JavaPOI导出Excel有三种形式，他们分别是
- HSSFWorkbook
SSFworkbook 解释如下：
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；
HSSFWorkbook：
poi导出excel最常用的方式；但是此种方式的局限就是导出的行数至多为65535行，超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况（OOM）。
用HSSFWorkbook这种方式去创建 Sheet有局限性 Excel2003以前（包括2003）的版本，扩展名是.xls；局限就是导出的行数至多为65535行，超出65536条后系统就会报错 所以现在代码中尽量不用这种方式去创建 Sheet 改为用 XSSFWorkbook去创建
- XSSFWorkbook
XSSFWorkbook 解释如下：
XSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；
XSSFWorkbook：
这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行，不过这样就伴随着一个问题—OOM内存溢出，原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化
- SXSSFWorkbook
SXSSFWorkbook 解释如下：
SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；
SXSSFWorkbook：
从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建，一个关键问题就是，要确保不会内存溢出。其实，就算生成很小的excel（比如几Mb），它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式（比如，加粗，背景标红之类的），那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hash map用空间换时间一样）。
SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到
1.HSSFWorkbook导出工具类
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: HSSFExcelUtils
* @Description: Excel相关操作
* @Author
* @Date 2022/4/11
* @Version 1.0
*/
@Component
public class HSSFExcelUtils {
@Resource
private 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的Header
String 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+1
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();
//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工作薄对象，表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(ObjectUtils.isNotEmpty(fileName)){
if (fileName.endsWith("xls")) {
//2003
workbook = 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:
//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) {
// 处理自定义日期格式：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();
}
}
}
}
2.XSSFWorkbook导出工具类
package com.yutu.garden.utils;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
/**
* @ClassName: XSSFExcelUtils
* @Description: Excel相关操作
* @Author wyf
* @Date 2022/4/11
* @Version 1.0
*/
public class XSSFExcelUtils {
/**
* 设置表格样式
*
* @param workbook workbook
* @param bold 字体是否加粗
* @param name 字体类型
* @param size 字体大小
* @param align 水平方向（左、中、右）
* @param alignment 垂直方向（上、中、下）
* @param isBorder 是否带边框
* @param wrapped 是否自动换行
* @param red 填充底色
* @param green 填充底色
* @param blue 填充底色
* @return
*/
public static XSSFCellStyle getStyle(XSSFWorkbook workbook, Boolean bold, String name, int size, HorizontalAlignment align,
VerticalAlignment alignment, Boolean isBorder, Boolean wrapped, Byte red, Byte green, Byte blue) {
//设置标题样式
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
if (bold != null && bold) {
font.setBold(true);
}
if (name == null || name.isEmpty()) {
name = "宋体";
}
font.setFontName(name);
font.setFontHeightInPoints((short) size);
style.setFont(font);
//水平
if (align == null) {
align = HorizontalAlignment.LEFT;
}
style.setAlignment(align);
//垂直
if (alignment == null) {
alignment = VerticalAlignment.CENTER;
}
style.setVerticalAlignment(alignment);
if (isBorder != null && isBorder) {
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
}
style.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy h:mm"));
if (wrapped != null && wrapped) {
//设置自动换行
style.setWrapText(true);
}
if (red != null && green != null && blue != null) {
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(red, green, blue)));
//设置填充样式（实心填充），不设置填充样式不会有颜色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
return style;
}
/**
* 表格赋值
*
* @param sheet sheet
* @param data 数据
* @param startNum 开始页码
* @param style 样式
* @param widths 定制宽度
* @param height 定制高度
*/
public static void setData(XSSFSheet sheet, List<Object[]> data, int startNum, XSSFCellStyle style, Integer[] widths, Short height, List<Short> heights) {
try {
int rowNum = startNum + 1;
for (int i = 0; i < data.size(); i++) {
XSSFRow row = sheet.createRow(rowNum);
if (heights != null && heights.size() > i && heights.get(i) != null) {
row.setHeightInPoints(heights.get(i));
} else if (height != null) {
row.setHeightInPoints(height);
}
for (int j = 0; j < data.get(i).length; j++) {
XSSFCell cell = row.createCell(j);
if (data.get(i)[j] != null) {
cell.setCellValue(data.get(i)[j].toString());
cell.setCellType(CellType.STRING);
} else {
cell.setCellValue("");
}
cell.setCellStyle(style);
if (widths != null && widths.length > j && widths[j] != null) {
sheet.setColumnWidth(j, widths[j] * 256);
} else {
// 根据内容自动调整列宽
sheet.autoSizeColumn(j);
}
}
rowNum++;
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void drawPictureInfoExcel(XSSFWorkbook workbook, XSSFSheet sheet, XSSFRow row, int rowIndex, int colIndex, String url) {
try {
byte[] imageBytes = HttpUtils.getFileBytes(url, 30000);
if (ObjectUtils.isNotEmpty(imageBytes) && imageBytes.length > 0) {
XSSFDrawing drawing = sheet.createDrawingPatriarch();
int pictureType = 0;
if (url.contains(".jpg") || url.contains(".JPG") || url.contains(".jpeg") || url.contains(".JPEG")) {
pictureType = Workbook.PICTURE_TYPE_JPEG;
} else if (url.contains(".png") || url.contains(".PNG")) {
pictureType = Workbook.PICTURE_TYPE_PNG;
}
int pictureIndex = workbook.addPicture(imageBytes, pictureType);
XSSFClientAnchor anchor = new XSSFClientAnchor(15, 15, 1023, 255, colIndex, rowIndex + 1, colIndex, rowIndex + 1);
drawing.createPicture(anchor, pictureIndex);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 导出图片
*/
public static void createPictureImage(XSSFWorkbook workbook, XSSFSheet sheet, Row row, int rowIndex, int colIndex, List<String> filePathList) {
try {
int num = 30;
int num1 = 15;
int num2 = 100;
int num3 = 100;
for (int i = 0; i < filePathList.size(); i++) {
String url = filePathList.get(i);
XSSFDrawing drawing = sheet.createDrawingPatriarch();
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File(url));
int width = bufferImg.getWidth();
int height = bufferImg.getHeight();
height = (int) Math.round((height * (30 * 13) * 1.0 / width));
row.setHeight((short) (height / 2 * 15));
String suffix = "";
int pictureType = 0;
if (url.contains(".jpg") || url.contains(".JPG")) {
suffix = "jpg";
pictureType = Workbook.PICTURE_TYPE_JPEG;
} else if (url.contains(".jpeg") || url.contains(".JPEG")) {
suffix = "jpeg";
pictureType = Workbook.PICTURE_TYPE_JPEG;
} else if (url.contains(".png") || url.contains(".PNG")) {
suffix = "png";
pictureType = Workbook.PICTURE_TYPE_PNG;
}
ImageIO.write(bufferImg, suffix, byteArrayOut);
XSSFClientAnchor anchor = new XSSFClientAnchor(num, num1, num2, num3, colIndex, rowIndex + 1, colIndex, rowIndex + 1);
num += 100;
num2 += 100;
try {
drawing.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), pictureType));
byteArrayOut.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 设置表头
*
* @param sheet sheet
* @param str 表头数据
* @param startNum 开始行
* @param height 高度
* @param style 样式
*/
public static void setTitle(XSSFSheet sheet, String[] str, int startNum, Short height, XSSFCellStyle style, Integer[] widths, Integer width) {
try {
XSSFRow row = sheet.createRow(startNum);
if (height == null) {
height = 20;
}
row.setHeightInPoints(height);
// 创建表头名称
XSSFCell cell;
for (int j = 0; j < str.length; j++) {
cell = row.createCell(j);
cell.setCellValue(str[j]);
cell.setCellStyle(style);
if (widths != null && widths.length > j && widths[j] != null) {
sheet.setColumnWidth(j, widths[j] * 256);
} else if (width != null) {
int columnWidth = (width * 256) / 10;
int length = cell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length + 2;
}
if (columnWidth > 255) {
columnWidth = 255;
}
sheet.setColumnWidth(j, columnWidth * 256);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void setTitle(XSSFSheet sheet, List<String> str, int startNum, Short height, XSSFCellStyle style, int width) {
try {
XSSFRow row = sheet.createRow(startNum);
if (height == null) {
height = (short) (20 * 20);
}
row.setHeight(height);
// 创建表头名称
XSSFCell cell;
for (int j = 0; j < str.size(); j++) {
cell = row.createCell(j);
cell.setCellValue(str.get(j));
cell.setCellStyle(style);
// 设置列宽度，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
int columnWidth = (width * 256) / 10 / 256;
int length = cell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length + 2;
}
if (columnWidth > 255) {
columnWidth = 255;
}
sheet.setColumnWidth(j, columnWidth * 256);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void setTitle(XSSFSheet sheet, String title, int startNum, Short height, XSSFCellStyle style) {
try {
XSSFRow row = sheet.createRow(startNum);
if (height == null) {
height = (short) (20 * 20);
}
row.setHeight(height);
// 创建表头名称
XSSFCell cell = row.createCell(0);
cell.setCellValue(title);
cell.setCellStyle(style);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 方法名：setData
* 功能：表格赋值
*/
public static void setData(XSSFSheet sheet, List<Object[]> data, int startNum, XSSFCellStyle style) {
try {
int rowNum = startNum + 1;
for (int i = 0; i < data.size(); i++) {
XSSFRow row = sheet.createRow(rowNum);
for (int j = 0; j < data.get(i).length; j++) {
XSSFCell 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++;
}
// 设置列宽
for (int j = 0; j < data.get(0).length; j++) {
int columnWidth = sheet.getColumnWidth(j) / 256;
for (int i = startNum; i < rowNum; i++) {
XSSFCell cell = sheet.getRow(i).getCell(j);
if (cell != null && cell.getStringCellValue() != null) {
int length = cell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length * 12 / 10;
}
}
}
if (columnWidth > 255) {
columnWidth = 255;
}
sheet.setColumnWidth(j, columnWidth * 256);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 表格赋值
*
* @param sheet
* @param data
* @param startNum
* @param style
* @param widths 宽度
*/
public static void setData(XSSFSheet sheet, List<Object[]> data, int startNum, XSSFCellStyle style, Integer[] widths) {
try {
int rowNum = startNum + 1;
for (int i = 0; i < data.size(); i++) {
XSSFRow row = sheet.createRow(rowNum);
for (int j = 0; j < data.get(i).length; j++) {
XSSFCell cell = row.createCell(j);
if (ObjectUtils.isEmpty(data.get(i)[j])) {
cell.setCellValue("");
} else {
cell.setCellValue(data.get(i)[j].toString());
}
cell.setCellStyle(style);
if (ObjectUtils.isNotEmpty(widths) && widths.length > j && ObjectUtils.isNotEmpty(widths[j])) {
// 如果指定了列宽度，则使用指定的列宽度
sheet.setColumnWidth(j, widths[j] * 256);
} else {
// 否则根据内容自动调整列宽
int columnWidth = sheet.getColumnWidth(j) / 256;
int length = cell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length + 2;
}
if (columnWidth > 255) {
columnWidth = 255;
}
sheet.setColumnWidth(j, columnWidth * 256);
}
}
rowNum++;
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void setDataByList(XSSFWorkbook workbook, XSSFSheet sheet, List<List<Object>> data, int startNum, XSSFCellStyle style) {
try {
int rowNum = startNum + 1;
for (int i = 0; i < data.size(); i++) {
XSSFRow row = sheet.createRow(rowNum);
for (int j = 0; j < data.get(i).size(); j++) {
XSSFCell cell = row.createCell(j);
if (ObjectUtils.isEmpty(data.get(i).get(j))) {
cell.setCellValue("");
} else {
String value = data.get(i).get(j).toString();
if (value.contains("down-file")) {
// 图片
drawPictureInfoExcel(workbook, sheet, row, rowNum - 1, j, value);
} else {
cell.setCellValue(value);
}
}
cell.setCellStyle(style);
}
// 设置列宽度
adjustColumnWidth(sheet);
rowNum++;
}
} catch (Exception e) {
e.printStackTrace();
}
}
private static void adjustColumnWidth(XSSFSheet sheet) {
for (int j = 0; j < sheet.getRow(0).getLastCellNum(); j++) {
int columnWidth = sheet.getColumnWidth(j) / 256;
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
XSSFRow currentRow;
if (sheet.getRow(i) != null) {
currentRow = sheet.getRow(i);
if (currentRow.getCell(j) != null && currentRow.getCell(j).getCellTypeEnum() == CellType.STRING) {
int length = currentRow.getCell(j).getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (columnWidth > 255) {
columnWidth = 255;
}
sheet.setColumnWidth(j, columnWidth * 256);
}
}
/**
* 方法名：setBrowser
* 功能：使用浏览器下载
*/
public static void setBrowser(HttpServletResponse response, XSSFWorkbook 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.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("UTF-8");
// 将excel写入到输出流中
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 居中
*
* @param workbook
* @return
*/
public static XSSFCellStyle getStyleByCENTER(XSSFWorkbook workbook, int size) {
// 设置标题样式
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont 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(workbook.createDataFormat().getFormat("m/d/yy h:mm"));
return style;
}
/**
* 靠左
*
* @param workbook
* @return
*/
public static XSSFCellStyle getStyleByLEFT(XSSFWorkbook workbook, int size) {
// 设置标题样式
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setBold(true);
font.setFontName("宋体");
font.setFontHeightInPoints((short) size);
style.setFont(font);
// 水平靠左
style.setAlignment(HorizontalAlignment.LEFT);
// 垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy h:mm"));
return style;
}
/**
* 带边框的样式
*
* @param workbook workbook
* @param size 字体大小
* @param isText 是否正文
* @return
*/
public static XSSFCellStyle getStyleByBorder(XSSFWorkbook workbook, int size, boolean isText) {
// 设置标题样式
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont 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(workbook.createDataFormat().getFormat("m/d/yy h:mm"));
//单元格文本格式
//style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
// 边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
return style;
}
public static XSSFCellStyle getStyleByBorder(XSSFWorkbook workbook, int size, boolean isText, int red, int green, int blue) {
// 设置标题样式
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont 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(workbook.createDataFormat().getFormat("m/d/yy h:mm"));
//单元格文本格式
//style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
// 边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 设置填充颜色
XSSFColor color = new XSSFColor(new java.awt.Color(red, green, blue));
style.setFillForegroundColor(color);
// 设置填充样式（实心填充）
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
public static XSSFCellStyle getDataStyle(XSSFWorkbook workbook, int size, boolean isSetBold, XSSFColor background) {
// 设置标题样式
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setBold(isSetBold);
font.setFontName("宋体");
font.setFontHeightInPoints((short) size);
style.setFont(font);
// 设置填充方式
if (background != null) {
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(background);
}
return style;
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
switch (cell.getCellTypeEnum()) {
case NUMERIC:
// 数字
if (DateUtil.isCellDateFormatted(cell)) {
// 日期类型处理
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = sdf.format(cell.getDateCellValue());
} else {
// 数字类型处理
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
// 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
// Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
// 公式
cellValue = cell.getCellFormula();
break;
case BLANK:
// 空值
cellValue = "";
break;
case ERROR:
// 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
public static String stringDateProcess(Cell cell) {
String result = "";
if (DateUtil.isCellDateFormatted(cell)) {
// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == 14) {
sdf = new SimpleDateFormat("yyyy-MM-dd");
} else if (cell.getCellStyle().getDataFormat() == 31) {
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
} 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 = DateUtil.getJavaDate(value);
result = sdf.format(date);
} else {
// 处理其他数字格式
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
short formatIndex = style.getDataFormat();
String formatString = style.getDataFormatString();
if (formatString != null && !formatString.isEmpty()) {
DecimalFormat format = new DecimalFormat(formatString);
result = format.format(value);
} else if (DateUtil.isADateFormat(formatIndex, formatString)) {
// 处理日期格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = DateUtil.getJavaDate(value);
result = sdf.format(date);
} else {
// 其他数字格式
result = String.valueOf(value);
}
}
return result;
}
/**
* @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;
}
}
控制层接口
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模板存放位置
Excel打不开问题
Java excel poi 使用HSSFWorkbook 导出的excel wps能打开office打不开问题解决 Excel无法打开xx.xlsx,因为文件格式或扩展名无效…导致office打不开原因是使用了HSSFworkbook，修改成XSSFWorkbook即可解决
技术共进，成长同行——讯飞AI开发者社区
更多推荐
5
0- 0
已为社区贡献14条内容
所有评论(0)