本篇是针对接口开发的案例～～
Apache POI提供了HSSFWorkbook操作2003版本的Excel文件, XSSFWorkbook操作2007版Excel文件.
Excel文件导出操作在我们工作中有很多场景都会用到. 如报表数据下载, 消费记录下载等…
下面针对不同场景, 封装了不同的工具类, 但底层都是用的基础的公共api, 我使用的是HSSFWorkbook, 如果要生成2007版的Excel文件, 只需将HSSFWorkbook替换成XSSFWorkbook即可。
简单的具体实现在网上有很多案例可以参考学习, 我就不写入门案例了, 下面我会将自己工作中用到的场景封装成工具类记录下来, 供以后翻看复习。
注意：
EXCEL的压缩率特别高，能达到80%，12M的文件压缩后才2M左右。 如果未经过压缩、不仅会占用用户带宽，且会导致负载服务器（apache）和应用服务器之间，长时间占用连接(二进制流转发)，导致负载服务器请求阻塞，不能提供服务。
所有方式仅供参考，具体实现根据业务自行封装对应的工具类。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.0</version>
</dependency>
<!--hutool 工具类 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.5.11</version>
</dependency>
此工具类以下几种输出方式：
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.ai.sop.common.constants.WebConstant;
import com.ai.system.entity.beans.TdSysStaff;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.awt.*;
import java.awt.Color;
import java.awt.Font;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;
/**
* @author daniel
* @description excel 导出工具类
*/
public class ExcelUtils {
private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);
/**
* 用户信息导出类(添加水印,可打印,插入图片的方式)
* @desc 支持HSSFWorkbook格式的导出,插入图片方法.没有考虑合并单元格等情况.
* @param response 响应
* @param fileName 文件名
* @param columnList 每列的标题名
* @param dataList 导出的数据
*/
public static void exportExcelWaterMarkPrint(HttpServletResponse response,String fileName,List<String> columnList,List<List<String>> dataList){
//声明输出流
OutputStream os = null;
//设置响应头
setResponseHeader(response,fileName);
try {
//获取输出流
os = response.getOutputStream();
String user = "daniel";
String date = DateUtil.format(new Date(), DatePattern.NORM_DATETIME_FORMAT); //yyyy-MM-dd HH:mm:ss
String watermark = user + " " + date;
// String watermark = "daniel 2022-09-29 11:55:66";
//内存中保留1000条数据，以免内存溢出，其余写入硬盘
HSSFWorkbook wb = new HSSFWorkbook();
log.info("报表导出Size: " + dataList.size() + "条。");
Sheet sheet = null;
int excelRow = 0;//定义表格行
int index = 1;//定义初始sheet页码
HSSFPatriarch patriarch = null;
BufferedImage waterimg = null;
java.io.ByteArrayOutputStream wos = null;
List<HSSFClientAnchor> wanList = null;
sheet = wb.createSheet("sheet" + index);
sheet.setDefaultColumnWidth((short)20);//将默认的列宽设为25个文字大小
//创建标题行
Row titleRow = sheet.createRow(excelRow++);
for(int i = 0;i<columnList.size();i++){
//创建该行下的每一列，并写入标题数据
Cell cell = titleRow.createCell(i);
cell.setCellValue(columnList.get(i));
}
//设置内容行
if(dataList != null && dataList.size() > 0){
//外层for循环创建行
for(int i = 0;i<dataList.size();i++) {
Row dataRow = sheet.createRow((i+1)%65000 == 0 ? 65000 : (i+1)%65000);
//内层for循环创建每行对应的列，并赋值
for(int j = 0;j<dataList.get(i).size();j++){
Cell cell = dataRow.createCell(j);
cell.setCellValue(dataList.get(i).get(j));
}
//每65000条数据创建一个sheet页
if ((i+1)%65000 == 0) {
index++;
sheet = wb.createSheet("sheet" + index);
sheet.setDefaultColumnWidth((short)20);//将默认的列宽设为20个文字大小
Row title = sheet.createRow(0);
for(int k = 0; k < columnList.size();k++){//新建的sheet页表头写入
//创建该行下的每一列，并写入标题数据
Cell cell = title.createCell(k);
cell.setCellValue(columnList.get(k));
}
}
}
}
//获取excel工作簿中sheet个数
int sheets = wb.getNumberOfSheets();
wos = new java.io.ByteArrayOutputStream();
waterimg = createWaterRemark(watermark);//设置水印图片
ImageIO.write(waterimg, "png", wos);
//遍历sheet页并计算水印覆盖面积
for (int q = 0; q < sheets; q++) {
sheet.protectSheet("123456");//excel表格的编辑密码
sheet = wb.getSheetAt(q);//获取sheet
//开始在每个sheet页部署水印
patriarch = (HSSFPatriarch) sheet.createDrawingPatriarch();
//计算水印覆盖面积
wanList = circulationPic((HSSFSheet) sheet, watermark);
//根据行与列计算实际所需多少水印
for (HSSFClientAnchor wanchor : wanList) {
patriarch.createPicture(wanchor, wb.addPicture(wos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
}
}
//将整理好的excel数据写入流中
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
// 关闭输出流
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 用户信息导出类(带水印,背景图,非插入图片的方式)
* @param response 响应
* @param fileName 文件名
* @param columnList 每列的标题名
* @param dataList 导出的数据
*/
public static void exportExcelWaterMark(HttpServletResponse response, String fileName, List<String> columnList, List<List<String>> dataList){
//声明输出流
OutputStream os = null;
//设置响应头
setResponseHeader(response,fileName);
try {
//获取输出流
os = response.getOutputStream();
//内存中保留1000条数据，以免内存溢出，其余写入硬盘
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
//获取该工作区的第一个sheet
SXSSFSheet sheet1 = wb.createSheet("sheet1");
//添加水印
WaterMarkUtil.insertWaterMarkTextToXlsx(wb);
sheet1.trackAllColumnsForAutoSizing();
int excelRow = 0;
//创建标题行
Row titleRow = sheet1.createRow(excelRow++);
for(int i = 0;i<columnList.size();i++){
//创建该行下的每一列，并写入标题数据
Cell cell = titleRow.createCell(i);
cell.setCellValue(columnList.get(i));
sheet1.autoSizeColumn(i);
sheet1.setColumnWidth(i,sheet1.getColumnWidth(i)*17/10);
}
//设置内容行
if(dataList!=null && dataList.size()>0){
//序号是从1开始的
// int count = 1;
//外层for循环创建行
for(int i = 0;i<dataList.size();i++){
Row dataRow = sheet1.createRow(excelRow++);
//内层for循环创建每行对应的列，并赋值(这种写法加了一个序号列。)
// for(int j = -1;j
// Cell cell = dataRow.createCell(j+1);
// if(j==-1)
// {//第一列是序号列，不是在数据库中读取的数据，因此手动递增赋值
// cell.setCellValue(count++);
// }
// else{//其余列是数据列，将数据库中读取到的数据依次赋值
// cell.setCellValue(dataList.get(i).get(j));
// }
// }
//根据业务重新修改（如果第一列不要序号列，为了防止第一列为空导致异常，不能get(0),要get(i)）
for (int j = 0; j<dataList.get(i).size(); j++)
{
Cell cell = dataRow.createCell(j);
cell.setCellValue(dataList.get(i).get(j));
sheet1.autoSizeColumn(j);
sheet1.setColumnWidth(j, sheet1.getColumnWidth(j) * 17 / 10);
}
}
}
//设置密码
sheet1.protectSheet("123456");
//将整理好的excel数据写入流中
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
// 关闭输出流
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 用户信息导出类(不带水印直接导出)
* @param response 响应
* @param fileName 文件名
* @param columnList 每列的标题名
* @param dataList 导出的数据
*/
public static void exportExcel(HttpServletResponse response, String fileName, List<String> columnList, List<List<String>> dataList){
//声明输出流
OutputStream os = null;
//设置响应头
setResponseHeader(response,fileName);
try {
//获取输出流
os = response.getOutputStream();
//内存中保留1000条数据，以免内存溢出，其余写入硬盘
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
//获取该工作区的第一个sheet
SXSSFSheet sheet1 = wb.createSheet("sheet1");
sheet1.trackAllColumnsForAutoSizing();
int excelRow = 0;
//创建标题行
Row titleRow = sheet1.createRow(excelRow++);
for(int i = 0;i<columnList.size();i++){
//创建该行下的每一列，并写入标题数据
Cell cell = titleRow.createCell(i);
cell.setCellValue(columnList.get(i));
sheet1.autoSizeColumn(i);
sheet1.setColumnWidth(i,sheet1.getColumnWidth(i)*17/10);
}
//设置内容行
if(dataList!=null && dataList.size()>0){
//序号是从1开始的
// int count = 1;
//外层for循环创建行
for(int i = 0;i<dataList.size();i++){
Row dataRow = sheet1.createRow(excelRow++);
//内层for循环创建每行对应的列，并赋值
// for(int j = -1;j
// Cell cell = dataRow.createCell(j+1);
// if(j==-1)
// {//第一列是序号列，不是在数据库中读取的数据，因此手动递增赋值
// cell.setCellValue(count++);
// }
// else{//其余列是数据列，将数据库中读取到的数据依次赋值
// cell.setCellValue(dataList.get(i).get(j));
// }
// }
//根据业务重新修改
for (int j = 0; j<dataList.get(0).size(); j++)
{
Cell cell = dataRow.createCell(j);
cell.setCellValue(dataList.get(i).get(j));
sheet1.autoSizeColumn(j);
sheet1.setColumnWidth(j, sheet1.getColumnWidth(j) * 17 / 10);
}
}
}
//将整理好的excel数据写入流中
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
// 关闭输出流
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 设置浏览器下载响应头
*/
private static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=UTF-8");
// response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
/**
* @param entityClass excel中每一行数据的实体类
* @param in excel文件
* @param fields 字段名字 需要注意的是这个方法中的map中： excel表格中每一列名为键，每一列对应的实体类的英文名为值
* @throws Exception
*/
public static <T> List<T> ExcelToList(InputStream in, Class<T> entityClass, Map<String, String> fields) throws Exception {
List<T> resultList = new ArrayList<T>();
XSSFWorkbook workbook = new XSSFWorkbook(in);
// excel中字段的中英文名字数组
String[] egtitles = new String[fields.size()];
String[] cntitles = new String[fields.size()];
Iterator<String> it = fields.keySet().iterator();
int count = 0;
while (it.hasNext()) {
String cntitle = (String) it.next();
String egtitle = fields.get(cntitle);
egtitles[count] = egtitle;
cntitles[count] = cntitle;
count++;
}
// 得到excel中sheet总数
int sheetcount = workbook.getNumberOfSheets();
if (sheetcount == 0) {
workbook.close();
throw new Exception("Excel文件中没有任何数据");
}
// 数据的导出
for (int i = 0; i < sheetcount; i++) {
Sheet sheet = workbook.getSheetAt(i);
if (sheet == null) {
continue;
}
// 每页中的第一行为标题行，对标题行的特殊处理
Row firstRow = sheet.getRow(0);
int celllength = firstRow.getLastCellNum();
String[] excelFieldNames = new String[celllength];
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
// 获取Excel中的列名
for (int f = 0; f < celllength; f++) {
Cell cell = firstRow.getCell(f);
excelFieldNames[f] = cell.getStringCellValue().trim();
// 将列名和列号放入Map中,这样通过列名就可以拿到列号
for (int g = 0; g < excelFieldNames.length; g++) {
colMap.put(excelFieldNames[g], g);
}
}
// 由于数组是根据长度创建的，所以值是空值，这里对列名map做了去空键的处理
colMap.remove(null);
// 判断需要的字段在Excel中是否都存在
// 需要注意的是这个方法中的map中：中文名为键，英文名为值
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fields.keySet()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
}
// 如果有列名不存在，则抛出异常，提示错误
if (!isExist) {
workbook.close();
throw new Exception("Excel中缺少必要的字段，或字段名称有误");
}
String endName = "default";
// 将sheet转换为list
for (int j = 1; j <= sheet.getLastRowNum(); j++) {
boolean flag = false;
String enNormalName = "";
String content = "";
Row row = sheet.getRow(j);
// 根据泛型创建实体类
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Map.Entry<String, String> entry : fields.entrySet()) {
// 获取中文字段名
String cnNormalName = entry.getKey();
// 获取英文字段名
enNormalName = entry.getValue();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
content = "";
if (row.getCell(col) != null){
if(StringUtils.isNotBlank(row.getCell(col).toString())){
flag = true;
}
content = row.getCell(col).toString().trim();
}
if(enNormalName.equals("name") && content.equals("")){
endName = "";
break;
}
// 给对象赋值
setFieldValueByName(enNormalName, content, entity);
}
if(!endName.equals("") && flag){
resultList.add(entity);
}
}
}
workbook.close();
return resultList;
}
/**
* @MethodName : setFieldValueByName
* @Description : 根据字段名给对象的字段赋值
* @param fieldName
* 字段名
* @param fieldValue
* 字段值
* @param o
* 对象
*/
private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {
Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {
field.setAccessible(true);
// 获取字段类型
Class<?> fieldType = field.getType();
// 根据字段类型给字段赋值
if (String.class == fieldType) {
field.set(o, fieldValue);
} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
if(!fieldValue.equals("")){
field.set(o, Integer.parseInt(fieldValue.toString()));
}
}
// else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
// field.set(o, Long.valueOf(fieldValue.toString()));
// }
else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(o, Float.valueOf(fieldValue.toString()));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(o, Short.valueOf(fieldValue.toString()));
} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
field.set(o, Double.valueOf(fieldValue.toString()));
} else if (Character.TYPE == fieldType) {
if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
}
} else if (Date.class == fieldType) {
field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
} else {
field.set(o, fieldValue);
}
} else {
throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
}
}
/**
* @MethodName : getFieldByName
* @Description : 根据字段名获取字段
* @param fieldName
* 字段名
* @param clazz
* 包含该字段的类
* @return 字段
*/
private static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段，则返回
for (Field field : selfFields) {
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有，则返回空
return null;
}
public static void exportNewExcel(HttpServletResponse response, String fileName, List<String> columnList, List<List<String>> dataList,String teamType) throws IOException {
setResponseHeader(response,fileName);
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1);
if ("B22".equals(teamType)){
EasyExcel.write(response.getOutputStream(), HYEXCELData.class).autoCloseStream(Boolean.FALSE).registerWriteHandler(horizontalCellStyleStrategy).sheet(fileName).
registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)) .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)25)).
doWrite(dataList);
}
if ("B30".equals(teamType)){
EasyExcel.write(response.getOutputStream(), SQEXCELData.class).autoCloseStream(Boolean.FALSE).registerWriteHandler(horizontalCellStyleStrategy).sheet(fileName).
registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)) .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)25)).
doWrite(dataList);
}
}
public static void exportNoNumberExcel(HttpServletResponse response, String fileName, List<String> columnList, List<List<String>> dataList){
//声明输出流
OutputStream os = null;
//设置响应头
setResponseHeader(response,fileName);
try {
//获取输出流
os = response.getOutputStream();
//内存中保留1000条数据，以免内存溢出，其余写入硬盘
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
//获取该工作区的第一个sheet
Sheet sheet1 = wb.createSheet("sheet1");
int excelRow = 0;
//创建标题行
Row titleRow = sheet1.createRow(excelRow++);
for(int i = 0;i<columnList.size();i++){
//创建该行下的每一列，并写入标题数据
Cell cell = titleRow.createCell(i);
cell.setCellValue(columnList.get(i));
}
//设置内容行
if(dataList!=null && dataList.size()>0){
//外层for循环创建行
for(int i = 0;i<dataList.size();i++){
Row dataRow = sheet1.createRow(excelRow++);
//内层for循环创建每行对应的列，并赋值
for(int j = 0;j<dataList.get(0).size();j++){
Cell cell = dataRow.createCell(j);
cell.setCellValue(dataList.get(i).get(j));
}
}
}
//将整理好的excel数据写入流中
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
// 关闭输出流
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 生成水印图片
*/
public static BufferedImage createWaterRemark(String watermark) {
//设置字体格式
Font font = new Font("宋体", Font.PLAIN, 20);
int width = 300;
int height = 200;
//宽度
BufferedImage img = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象
// 背景透明 开始
Graphics2D loGraphic = img.createGraphics();// 获取Graphics2d对象
img = loGraphic.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
loGraphic.dispose();
// 背景透明 结束
loGraphic = img.createGraphics();
loGraphic.setColor(Color.gray);//设置画笔颜色
loGraphic.setStroke(new BasicStroke(1));//设置字体
loGraphic.setFont(font);//设置画笔字体
loGraphic.rotate(Math.toRadians(-30), (double) img.getWidth() / 2, (double) img.getHeight() / 2);//设置水印倾斜度
// 设置透明度
loGraphic.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
//设置字体平滑
loGraphic.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
FontRenderContext context = loGraphic.getFontRenderContext();
Rectangle2D bounds = font.getStringBounds(watermark, context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double baseY = y + ascent;
// 写入水印文字原定高度过小，所以累计写水印，增加高度
loGraphic.drawString(watermark, (int) x, (int) baseY);
//释放画笔
loGraphic.dispose();
return img;
}
/**
* 计算应被水印覆盖的面积
*/
public static List<HSSFClientAnchor> circulationPic(HSSFSheet sheet, String watermark) {
List<HSSFClientAnchor> list = new ArrayList<>();
int maxColum = 0;//列
int lastrow = sheet.getLastRowNum();//获取最后一行
for (int i = 0; i < lastrow; i++) {
int rows = sheet.getRow(i).getPhysicalNumberOfCells();
if (rows - maxColum > 0) {
maxColum = sheet.getRow(i).getLastCellNum();
}
}
//单元格宽和高
double cellwidth = sheet.getColumnWidthInPixels(0) * 1.0;
double cellheight = 22.0;
int columLimit = (int) Math.ceil(watermark.length() * 10 / cellwidth);
int rowLimit = (int) Math.ceil(watermark.length() * 10 / 2.0 / cellheight);
maxColum = (maxColum > columLimit && maxColum % columLimit == 0) ? maxColum : (int) Math.ceil(maxColum / columLimit * 1.0) * columLimit;
lastrow = ((lastrow + 1) > rowLimit && (lastrow + 1) % rowLimit == 0) ? (lastrow + 1) : (int) Math.ceil((lastrow + 1) / rowLimit * 1.0) * rowLimit;
for (int i = 0; i <= maxColum; i = i + columLimit) {
for (int j = 0; j <= lastrow; j = j + rowLimit) {
HSSFClientAnchor wanchor = new HSSFClientAnchor(0, 0, 255, 255, (short) i, j, (short) (i + columLimit - 1), j + rowLimit - 1);
list.add(wanchor);
}
}
return list;
}
}
插入图片的生成水印方式，在【createWaterRemark和circulationPic】两个方法，分别是：
背景图生成水印的方式，参考下面的工具类。
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ReflectUtil;
import com.ai.sop.management.constant.WebConstant;
import com.ai.system.entity.beans.TdSysStaff;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author daniel
* @description Excel 添加水印。
* 支持 SXSSFWorkbook 和 XSSFWorkbook 模式
* 不支持 HSSFWorkbook 导出的方式
*/
public class WaterMarkUtil {
/**
* Excel 导出添加水印
*
* @param workbook ExcelWorkbook
*/
public static void insertWaterMarkTextToXlsx(Workbook workbook) throws IOException {
//设置水印内容
String user = "daniel"; //导出人
String date = DateUtil.format(new Date(), DatePattern.NORM_DATETIME_FORMAT); //yyyy-MM-dd HH:mm:ss
String waterMarkText = user + " " + date;
if (workbook instanceof SXSSFWorkbook) {
insertWaterMarkTextToXlsx((SXSSFWorkbook) workbook, waterMarkText);
} else if (workbook instanceof XSSFWorkbook) {
insertWaterMarkTextToXlsx((XSSFWorkbook) workbook, waterMarkText);
}
//throw new RemoteException("HSSFWorkbook 模式不支持 Excel 水印");
}
/**
* 给 Excel 添加水印
*
* @param workbook SXSSFWorkbook
* @param waterMarkText 水印文字内容
*/
public static void insertWaterMarkTextToXlsx(SXSSFWorkbook workbook, String waterMarkText) throws IOException {
//设置水印文字内容
BufferedImage image = createWatermarkImage(waterMarkText);
ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
ImageIO.write(image, "png", imageOs);
int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFPictureData pictureData = (XSSFPictureData) workbook.getAllPictures().get(pictureIdx);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
SXSSFSheet sheet = workbook.getSheetAt(i);
//这里由于 SXSSFSheet 没有 getCTWorksheet() 方法，通过反射取出 _sh 属性
XSSFSheet shReflect = (XSSFSheet) ReflectUtil.getFieldValue(sheet, "_sh");
PackagePartName ppn = pictureData.getPackagePart().getPartName();
String relType = XSSFRelation.IMAGES.getRelation();
PackageRelationship pr = shReflect.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
shReflect.getCTWorksheet().addNewPicture().setId(pr.getId());
}
}
/**
* 给 Excel 添加水印
*
* @param workbook XSSFWorkbook
* @param waterMarkText 水印文字内容
*/
public static void insertWaterMarkTextToXlsx(XSSFWorkbook workbook, String waterMarkText) throws IOException {
BufferedImage image = createWatermarkImage(waterMarkText);
ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
ImageIO.write(image, "png", imageOs);
int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFPictureData pictureData = workbook.getAllPictures().get(pictureIdx);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
XSSFSheet sheet = workbook.getSheetAt(i);
PackagePartName ppn = pictureData.getPackagePart().getPartName();
String relType = XSSFRelation.IMAGES.getRelation();
PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
}
}
/**
* 创建水印图片
*
* @param waterMark 水印文字
*/
public static BufferedImage createWatermarkImage(String waterMark) {
String[] textArray = waterMark.split("\n");
Font font = new Font("microsoft-yahei", Font.PLAIN, 32);
int width = 500;
int height = 400;
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
// 背景透明 开始
Graphics2D g = image.createGraphics();
image = g.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g.dispose();
// 背景透明 结束
g = image.createGraphics();
g.setColor(new Color(Color.lightGray.getRGB()));// 设定画笔颜色
g.setFont(font);// 设置画笔字体
// g.shear(0.1, -0.26);// 设定倾斜度
//设置字体平滑
g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
//文字从中心开始输入，算出文字宽度，左移动一半的宽度，即居中
FontMetrics fontMetrics = g.getFontMetrics(font);
// 水印位置
int x = width / 2;
int y = height / 2;
// 设置水印旋转
g.rotate(Math.toRadians(-40), x, y);
for (String s : textArray) {
// 文字宽度
int textWidth = fontMetrics.stringWidth(s);
g.drawString(s, x - (textWidth / 2), y);// 画出字符串
y = y + font.getSize();
}
g.dispose();// 释放画笔
return image;
}
/**
* 设置打印的参数
*
* @param wb XSSFWorkbook
*/
public static void setPrintParams(XSSFWorkbook wb) {
XSSFSheet sheet = wb.getSheetAt(0);
XSSFPrintSetup printSetup = sheet.getPrintSetup();
// 打印方向，true：横向，false：纵向(默认
printSetup.setLandscape(true);
//设置A4纸
printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
// 将整个工作表打印在一页（缩放）,如果行数很多的话，可能会出问题
// sheet.setAutobreaks(true);
//将所有的列调整为一页，行数多的话，自动分页
printSetup.setScale((short) 70);//缩放的百分比，自行调整
sheet.setAutobreaks(false);
}
}
此方法在ExcelUtils类中有调用，参考【exportExcelWaterMark】方法。
参考即使用即可，具体情况根据项目内容修改
@PostMapping("/roleQueryExport")
@ApiOperation(value = "导出excel")
public void excelExport(@RequestBody User user, HttpServletResponse response) {
String fileName = "导出excel" +".xls";//可以随便写，让前端去控制导出的名字
// 列名,表格第一行
List<String> columnList = Arrays.asList("列1","列2","列3","列4");
try {
List<List<String>> list = userService.excelExport(user);
// 将需要写入Excel的数据传入
//ExcelUtils.exportExcelWaterMark(response, fileName, columnList, list);//不能打印,背景图格式
ExcelUtils.exportExcelWaterMarkPrint(response, fileName, columnList, list);//可打印,插入透明图片格式
}catch (Exception e) {
log.info(e.getMessage());
}
其他方式操作excel 文章参考：《EasyExcel导出导入的方式参考链接》
以上就是使用POI方式导出Excel的基础使用过程，欢迎点赞关注交流。