最近老是遇到需要导出excel的业务，觉得一直cv太麻烦了，而且poi的业务重复度很高，影响阅读，自己封装了一个excel处理类，目前暂时只支持普通的数据导出，复杂的单元格合并的情况暂时没有处理。
废话不多说，直接贴代码，主要作为自己的笔记记录，大佬勿喷！有需要者自取
maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
ExcelExportParam.java
用于设定导出的参数
package com.pub.bean;
import lombok.Data;
import net.sf.json.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
/**
* @Description: [ExcelExportParam]
* @Author: Luo Ly
* @Date: 2022/5/7 16:21
*/
@Data
public class ExcelExportParam {
private String title; //表格标题
private String fileName; //文件名称
private JSONArray data; //数据主体
private String[] head; //表头
private String[] cellKey; //单元格中对应表头的字段名（注意：必须与表头对应，否则数据填充错乱）
private int[] cellWidth; //单元格中对应表头的字段宽度（注意：必须与表头对应，否则数据填充错乱，单位为：字符）
private Boolean hasNumber; //是否添加序号
private HSSFCellStyle titleStyle; //标题样式（不设置则使用默认样式）
private HSSFCellStyle headStyle; //表头样式（不设置则使用默认样式）
private HSSFCellStyle cellStyle; //单元格样式（不设置则使用默认样式）
}
ExcelExportService.java
poi的处理类，准备后期新增其他处理方式的，目前只支持普通数据
package com.pub.service;
import com.pub.bean.ExcelExportParam;
import com.utils.ExcelUtils;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
/**
* @Description: [ExcelExportService]
* @Author: Luo Ly
* @Date: 2022/5/7 16:21
*/
public class ExcelExportService {
/**
*常规文件导出（适用于无单元格合并，无特殊数据处理的普通数据列表 ）
* @param response
* @param param 参数配置
* @throws Exception
*/
public static void exportExcelNormal(HttpServletResponse response,ExcelExportParam param) throws Exception{
//参数校验
if(param.getHead()==null || param.getHead().length<=0){
System.err.println("表头参数【head】不完整！");
return;
}
if(param.getCellKey()==null || param.getCellKey().length<=0){
System.err.println("字段参数【cellKey】不完整！");
return;
}
if(param.getCellWidth()==null || param.getCellWidth().length<=0){
System.err.println("单元格宽度参数【cellWidth】不完整！");
return;
}
if(param.getHasNumber()==null){
param.setHasNumber(false);
}
HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象
HSSFSheet sheet = workbook.createSheet("sheet");// 产生工作表对象
HSSFCellStyle titleStyle = workbook.createCellStyle(); // 标题
HSSFCellStyle headStyle = workbook.createCellStyle(); // 表头
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 表格内容
if(param.getTitleStyle()!=null){
titleStyle=param.getTitleStyle();
}else{
titleStyle= ExcelUtils.getTitleStyle(workbook);
}
if(param.getHeadStyle()!=null){
headStyle=param.getHeadStyle();
}else{
headStyle= ExcelUtils.getHeadStyle(workbook);
}
if(param.getCellStyle()!=null){
cellStyle=param.getCellStyle();
}else{
cellStyle= ExcelUtils.getCellStyle(workbook);
}
// 设置标题栏
HSSFRow titleRow = sheet.createRow(0);
if(param.getHasNumber()){
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, param.getHead().length);
sheet.addMergedRegion(cra);
}else{
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, param.getHead().length - 1);
sheet.addMergedRegion(cra);
}
titleRow.setHeight((short) (40 * 20));
HSSFCell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(titleStyle);
titleCell.setCellValue(param.getTitle());
// 表头
HSSFRow row = sheet.createRow(1);
if(param.getHasNumber()){
String []newHead=new String[param.getHead().length+1];
int [] newWidth=new int[param.getCellWidth().length+1];
for (int i = 0; i < newHead.length; i++) {
if(i==0){
newHead[0]="序号";
newWidth[0]=5;
}else{
newHead[i]=param.getHead()[i-1];
newWidth[i]=param.getCellWidth()[i-1];
}
}
param.setHead(newHead);
param.setCellWidth(newWidth);
}
for (int i = 0; i < param.getHead().length; i++) {
row.setHeight((short) (25 * 20));
HSSFCell headCell = row.createCell(i);
headCell.setCellStyle(headStyle);
headCell.setCellValue(param.getHead()[i]);
sheet.setColumnWidth(i, ExcelUtils.formatWidth2Char(param.getCellWidth()[i]));
}
//数据主体
JSONArray data=param.getData();
for (int i = 0; i < data.size(); i++) {
JSONObject item=data.getJSONObject(i);
HSSFRow itemRow = sheet.createRow(i+2); //除去标题和表头的序列
itemRow.setHeightInPoints(25);
if(param.getHasNumber()){
HSSFCell cell = itemRow.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue(i + 1);
}
for (int i1 = 0; i1 < param.getCellKey().length; i1++) {
String key=param.getCellKey()[i1];
if(item.has(key)){
int cellIndex=param.getHasNumber()?i1+1:i1; //当前列的序列
HSSFCell cell = itemRow.createCell(cellIndex);
cell.setCellStyle(cellStyle);
cell.setCellValue(item.getString(key));
}else{
System.err.println("数据【"+i+"】不存在"+key);
}
}
}
//为作用单元格加上边框
ExcelUtils.setBorder(sheet,true,0,data.size(),0,param.getHead().length);
// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
response.reset();
response.setContentType("APPLICATION/vnd.ms-excel");
// 注意，如果去掉下面一行代码中的attachment; 那么也会使IE自动打开文件。
response.setHeader("Content-Disposition", "attachment;filename=\""
+ new String(param.getFileName().getBytes("gb2312"), "ISO8859-1")
+ ".xls\"");
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
}
}
ExcelUtils.java
业务过程中一些的工具，单元格高宽换算的部分是在网上大佬的代码中cv过来魔改的，查了很多资料很难做到统一。然后我就根据实际导出效果来计算的大概数据，如果在业务中出现高宽异常的情况，可能需要调整一下这部分的逻辑
package com.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import sofun.util.StringUtil;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* POI excel 工具集
*
* @Description: [ExcelUtils]
* @Author: Luo Ly
* @Date: 2022/5/7 15:21
*/
public class ExcelUtils {
/**
* 合并指定单元格并设置边框
*
* @param sheet
* @param startRow
* @param endRow
* @param startCell
* @param endCell
* @date 2022年5月6日16:43:58
* @author lly
*/
public static void mergedRegion(HSSFSheet sheet, int startRow, int endRow, int startCell, int endCell) {
CellRangeAddress cra = new CellRangeAddress(startRow, endRow, startCell, endCell);
sheet.addMergedRegion(cra);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
}
/**
* 给指定单元格加上边框
*
* @param sheet
* @param autoRowHeight 是否自定调整行高
* @param startRow 开始行
* @param endRow 结束行
* @param startCell 开始列
* @param endCell 结束列
*/
public static void setBorder(HSSFSheet sheet, boolean autoRowHeight, int startRow, int endRow, int startCell, int endCell) {
for (int i = startRow; i < endRow; i++) {
for (int j = startCell; j < endCell; j++) {
CellRangeAddress cra = new CellRangeAddress(i, i, j, j);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
HSSFCell cell = sheet.getRow(i).getCell(j);
if (cell != null && autoRowHeight) {
autoRowHeight(cell, (short) 0);
}
}
}
}
/**
* 获取默认标题样式
*
* @param workbook
* @return
*/
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
HSSFCellStyle titleStyle = workbook.createCellStyle();
HSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20); // 字号
titleFont.setBold(true);
titleStyle.setFont(titleFont);
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
return titleStyle;
}
/**
* 获取默认表头样式
*
* @param workbook
* @return
*/
public static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook) {
HSSFCellStyle headStyle = workbook.createCellStyle();
HSSFFont headFont = workbook.createFont();
headFont.setFontHeightInPoints((short) 15); // 字号
headFont.setBold(true);
headStyle.setFont(headFont);
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置25灰度背景颜色
// headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return headStyle;
}
/**
* 获取默认单元格样式
*
* @param workbook
* @return
*/
public static HSSFCellStyle getCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont cellFont = workbook.createFont();
cellFont.setFontHeightInPoints((short) 10); // 字号
cellFont.setBold(true);
cellStyle.setFont(cellFont);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
cellStyle.setWrapText(true); //自动换行
return cellStyle;
}
/**
* 自适应excel行高
*
* @param cell
* @param margin
*/
public static void autoRowHeight(HSSFCell cell, short margin) {
if (cell == null) {
return;
}
String cellVal = getStringCellValue(cell);
if (StringUtil.isEmpty(cellVal)) {
return;
}
HSSFRow row = cell.getRow();
HSSFWorkbook workbook = row.getSheet().getWorkbook();
// pt = px*72/96 = px*3/4
//换算思路如下（根据使用情况测出，与实际单位换算有出入）：
// 宽度计算
// 字体12px时 50宽 可容纳22个字
// 每个字占宽约 2.28宽
// 字体每px占约0.19宽度
// 宽度20的单元格 可容纳字数应为：20/(12*0.19) = 8.78个字
// 高度计算
// 字体为12px时 高30pt的单元格可容纳2行
// 单行占高 30/2=15pt
// 字体每px约占15/12=1.25pt高度
// 2行应占高度为 1.25*12*2=30pt;
float fontSize = cell.getCellStyle().getFont(workbook).getFontHeightInPoints(); // 字体大小
float cellWidth = getCellWidth(cell);// 单元格宽度
int fontForCellWidth = (int) (cellWidth / (fontSize * 0.19)); // 每行可容纳字数
if (fontForCellWidth <= 0) {
return;
}
int fontLength = cellVal.length();
// 计算内容中的换行
String v = cellVal.replaceAll("\\n|\\r", "");
// 单元格内换行符出现的次数
int line = fontLength - v.length();
int rowNum = fontLength / fontForCellWidth + (fontLength % fontForCellWidth > 0 ? 1 : 0);// 分行
rowNum += line;
short cellHeight = (short) ((1.25 * fontSize + margin) * rowNum);
float oldH = row.getHeightInPoints();
if (cellHeight > oldH) {
row.setHeightInPoints(cellHeight);
}
}
private static String getStringCellValue(Cell cell) {
if (cell.getCellType() == CellType.FORMULA) {
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd")) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
return cell.getCellFormula();
} else if (cell.getCellType() == CellType.STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == CellType.NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
String dateStr = "";
int style = cell.getCellStyle().getDataFormat();
Date date = cell.getDateCellValue();
// 对不一样格式的日期类型作不一样的输出，与单元格格式保持一致
switch (style) {
case 178:
dateStr = new SimpleDateFormat("yyyy'年'M'月'd'日'").format(date);
break;
case 14:
dateStr = new SimpleDateFormat("yyyy-MM-dd").format(date);
break;
case 179:
dateStr = new SimpleDateFormat("yyyy/MM/dd HH:mm").format(date);
break;
case 181:
dateStr = new SimpleDateFormat("yyyy/MM/dd HH:mm a ").format(date);
break;
case 22:
dateStr = new SimpleDateFormat(" yyyy/MM/dd HH:mm:ss ").format(date);
break;
default:
break;
}
return dateStr;
} else {
cell.setCellType(CellType.STRING);
}
return cell.getStringCellValue();
} else if (cell.getCellType() == CellType.BOOLEAN) {
return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
} else {
return "";
}
}
/**
* 字符长度转poi长度
*
* @param columnWidth
* @return
*/
public static float formatWidth(float columnWidth) {
columnWidth = (float) ((columnWidth / 256) - 0.72);
return columnWidth;
}
/**
* 转字符长度，输出数值与excel中数值一直（有微弱偏差，可忽略）
*
* @param num
* @return
*/
public static int formatWidth2Char(float num) {
return (int) ((num + 0.72) * 256);
}
private static float getCellWidth(HSSFCell cell) {
if (cell == null) {
return 0;
}
HSSFSheet sheet = cell.getSheet();
int rowIndex = cell.getRowIndex();
int columnIndex = cell.getColumnIndex();
// float width = sheet.getColumnWidth(columnIndex);
float width = formatWidth(sheet.getColumnWidth(columnIndex));
boolean isPartOfRegion = false;
int firstColumn = 0;
int lastColumn = 0;
int firstRow = 0;
int lastRow = 0;
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
firstColumn = ca.getFirstColumn();
lastColumn = ca.getLastColumn();
firstRow = ca.getFirstRow();
lastRow = ca.getLastRow();
if (rowIndex == firstRow && rowIndex <= lastRow) {
if (columnIndex == firstColumn && columnIndex <= lastColumn) {
isPartOfRegion = true;
break;
}
}
}
if (isPartOfRegion) {
width = 0;
for (int i = firstColumn; i <= lastColumn; i++) {
// width += sheet.getColumnWidth(i);
width += formatWidth(sheet.getColumnWidth(i));
}
}
return width;
}
}调用范例
懒得写假数据了，前面几行获取数据的代码自行替换
组织装ExcelExportParam 然后直接调用就OK了
方便处理 data使用的JSONArray 如果业务不允许需要自行修改service
@RequestMapping("export")
public void export(HttpServletRequest request, HttpServletResponse response) {
try {
//必须转码，不然会导致乱码查询参数错误
request.setCharacterEncoding("UTF-8");
String exportTitle=request.getParameter("exportTitle");
JSONObject queryParam = super.getQueryParam(request);
PageResult pageResult=super.pageQuery(1,9999,queryParam);
JSONArray data=JSONArray.fromObject(pageResult.getData());
//表头
String []head=new String[]{"标题","文件类型","流水号","状态","接收人姓名","接收人部门"};
//表头对应字段名
String []cellKey=new String[]{"title","type","documentNumber","state","receiveName","receiveDeptName"};
//表头对应单元格宽度
int []cellWidth=new int[]{60,20,15,30,20,10};
ExcelExportParam eep=new ExcelExportParam();
eep.setFileName(exportTitle);
eep.setTitle(exportTitle);
eep.setHasNumber(true);
eep.setHead(head);
eep.setCellKey(cellKey);
eep.setCellWidth(cellWidth);
eep.setData(data);
ExcelExportService.exportExcelNormal(response,eep);
}catch (Exception e){
e.printStackTrace();
System.err.println("导出失败!");
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
