PoiExcelHelper
工具类说明
- 能够导出最简单的数据表,通过web流下载
- 行高会根据换行符
\n\r
自动判断,也可以自己设置正则 - 列宽会根据换行符
\n\r
自动判断,也可以自己设置正则 - 有默认的单元格和Header样式,可以自己设置
- 由于业务数据多变,因此每一列的数据处理没有继续封装(如传入富文本之类的情况)
- 使用起来已经算是非常简洁,可以看具体示例
- 导入还没做(偷懒)
依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
效果图
使用示例
/**
* 导出excel示例
*/
@GetMapping(value = "/exportXls")
@ApiOperation(value = "excel导出")
public void exportXls(HttpServletResponse response) throws IOException {
//行数据,从其他业务接口来,这里mock一下 todo
List<Map<String, Object>> dataRows = new ArrayList<>();
Map<String, Object> row = new HashMap<>();
row.put("order", "01");
row.put("partProject", "地基工程");
row.put("descritopn", "基底浮土清理不干净");
row.put("changeRequirement", "无");
row.put("problemLevel", "四级");
row.put("status", "正常");
row.put("limitTime", "3");
dataRows.add(row);
//开始生成表格
PoiExcelHelper poiExcelHelper = new PoiExcelHelper();
poiExcelHelper.createSheet("质量问题库");
List<String> headerValues = Arrays.asList("序号", "分部分项工程", "描述", "整改要求", "问题等级", "状态", "整改时限");
//表头
poiExcelHelper.createHeaderRow(headerValues);
//数据行
dataRows.forEach(data -> {
HSSFRow dataRow = poiExcelHelper.createRow();
//序号
Object order = data.get("order");
poiExcelHelper.createCell(dataRow, order);
//分部分项工程
Object partProject = data.get("partProject");
poiExcelHelper.createCell(dataRow, partProject);
//描述,可以单独设置cell的富文本
String descritopn = (String) data.get("descritopn");
Font font = poiExcelHelper.getWorkbook().createFont();
font.setColor(HSSFColor.HSSFColorPredefined.LIGHT_BLUE.getIndex());
HSSFRichTextString richDescription = new HSSFRichTextString(descritopn);
richDescription.applyFont(0, descritopn.length(), font);
poiExcelHelper.createCell(dataRow, richDescription);
//整改要求
Object changeRequirement = data.get("changeRequirement");
poiExcelHelper.createCell(dataRow, changeRequirement);
//问题等级
Object problemLevel = data.get("problemLevel");
poiExcelHelper.createCell(dataRow, problemLevel);
//状态
Object status = data.get("status");
poiExcelHelper.createCell(dataRow, status);
//整改时限
Object limitTime = data.get("limitTime");
poiExcelHelper.createCell(dataRow, limitTime);
});
//web下载
poiExcelHelper.downloadWebFile(response, "质量问题库");
}
工具类
package org.jeecg.config;
import lombok.Setter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
/**
* @author Yoko
* @date 2021.03.23
* @description POI Excel帮助工具类封装
*/
@Setter
public class PoiExcelHelper {
private static final String DEFAULT_SHEET_NAME = "默认";
public double baseHeightUnit = 256 * 1.5;
public double baseWidthUnit = 256 * 3.0;
public String defaultFontFamily = "宋体";
public short defaultFontSize = 11;
public short defaultFontColor = HSSFFont.COLOR_NORMAL;
private HSSFWorkbook workbook;
private HSSFSheet defaultSheet;
private HSSFFont font;
private HSSFFont boldFont;
private HSSFCellStyle cellStyle;
private HSSFCellStyle boldCellStyle;
public PoiExcelHelper(HSSFWorkbook workbook) {
this.workbook = workbook;
}
public PoiExcelHelper() {
}
public HSSFWorkbook getWorkbook() {
if (null == workbook) {
workbook = new HSSFWorkbook();
}
return workbook;
}
public HSSFSheet getDefaultSheet() {
if (null == defaultSheet) {
defaultSheet = getWorkbook().getSheet(DEFAULT_SHEET_NAME);
}
return defaultSheet;
}
public HSSFFont getFont() {
if (null == font) {
font = getWorkbook().createFont();
font.setFontHeightInPoints(defaultFontSize); //字体高度
font.setColor(defaultFontColor); //字体颜色
font.setFontName(defaultFontFamily); //字体
}
return font;
}
public HSSFFont getBoldFont() {
if (null == boldFont) {
boldFont = getWorkbook().createFont();
boldFont.setFontHeightInPoints(defaultFontSize); //字体高度
boldFont.setColor(defaultFontColor); //字体颜色
boldFont.setFontName(defaultFontFamily); //字体
boldFont.setBold(true);
}
return boldFont;
}
public HSSFCellStyle getCellStyle() {
if (null == cellStyle) {
cellStyle = getWorkbook().createCellStyle();
cellStyle.setFont(getFont());
cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平布局:居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//单元格垂直居中
cellStyle.setWrapText(true);//换行
}
return cellStyle;
}
public HSSFCellStyle getBoldCellStyle() {
if (null == boldCellStyle) {
boldCellStyle = getWorkbook().createCellStyle();
boldCellStyle.cloneStyleFrom(getCellStyle());
boldCellStyle.setFont(getBoldFont());
}
return boldCellStyle;
}
public HSSFSheet createSheet(String sheetName) {
if (null == workbook) {
workbook = new HSSFWorkbook();
}
HSSFSheet sheet = workbook.createSheet(sheetName);
if (defaultSheet == null) {
defaultSheet = sheet;
}
return sheet;
}
public HSSFSheet getSheet(String sheetName) {
return getWorkbook().getSheet(sheetName);
}
public HSSFSheet getSheet(int sheetIndex) {
return getWorkbook().getSheetAt(sheetIndex);
}
/**
* 自动创建新行
*
* @param sheet
* @param rownum
* @return
*/
public HSSFRow createRow(HSSFSheet sheet, int rownum) {
if (null == sheet) {
sheet = getDefaultSheet();
}
if (rownum < 0) {
rownum = sheet.getLastRowNum() + 1;
}
return sheet.createRow(rownum);
}
public HSSFRow createRow(int rownum) {
return createRow(null, rownum);
}
public HSSFRow createRow(HSSFSheet sheet) {
return createRow(sheet, -1);
}
public HSSFRow createRow() {
return createRow(null, -1);
}
/**
* 创建header头
*
* @param sheet
* @param values
* @return
*/
public HSSFRow createHeaderRow(HSSFSheet sheet, List<String> values) {
if (null == sheet) {
sheet = getDefaultSheet();
}
HSSFRow header = createRow(0);
for (int i = 0; i < values.size(); i++) {
String value = values.get(i);
setColumnWidth(sheet, i, value, null);
HSSFCell cell = header.createCell(i);
cell.setCellStyle(getBoldCellStyle());
cell.setCellValue(value);
}
return header;
}
public HSSFRow createHeaderRow(List<String> values) {
return createHeaderRow(null, values);
}
public HSSFCell createCell(HSSFRow row, Object value) {
return createCell(row, -1, value, null, true, true);
}
/**
* 创建默认cell
*
* @param row
* @param value
* @param style
* @param autoWidth
* @param autoHeight
* @return
*/
public HSSFCell createCell(HSSFRow row, int index, Object value, HSSFCellStyle style, boolean autoWidth, boolean autoHeight) {
if (null == row) return null;
if (null == style) {
style = getCellStyle();
}
HSSFCell newCell = null;
//初始格子
int lastCellNum = row.getLastCellNum();
if (-1 == lastCellNum) {
//没有第0格的时候,值为-1,有了第0格,值会变成1
lastCellNum = 0;
}
if (index > -1) {
lastCellNum = index;
}
newCell = row.createCell(lastCellNum);
newCell.setCellStyle(style);
//值封装
if (value == null) {
value = "";
}
if (value instanceof String || value instanceof Integer || value instanceof Short) {
newCell.setCellValue(value + "");
}
if (value instanceof Date) {
newCell.setCellValue((Date) value);
}
if (value instanceof Calendar) {
newCell.setCellValue((Calendar) value);
}
if (value instanceof Double || value instanceof Float) {
newCell.setCellValue(Double.valueOf(value + ""));
}
if (value instanceof RichTextString) {
newCell.setCellValue((RichTextString) value);
}
if (autoWidth) {
setColumnWidth(row.getSheet(), newCell.getColumnIndex(), value.toString(), null);
}
if (autoHeight) {
setRowHeight(row, value.toString());
}
return newCell;
}
/**
* 根据内容长度设置单元格的高度,若未超过当前高不设置
*
* @param row
* @param valStr
*/
public void setRowHeight(HSSFRow row, String valStr) {
setRowHeight(row, valStr, null);
}
/**
* 根据内容长度设置单元格的高度,若未超过当前高不设置
*
* @param row
* @param valStr
* @param reg 自定义正则
*/
public void setRowHeight(HSSFRow row, String valStr, String reg) {
if (null == valStr) valStr = "";
if (null == reg || "".equals(reg.trim())) {
reg = "\r\n";
}
String[] split = valStr.split(reg);
short newHeight = (short) (split.length * baseHeightUnit);
if (row.getHeight() < newHeight) {
row.setHeight(newHeight);
}
}
/**
* 根据内容长度设置sheet列的宽度,若未超过当前宽不设置
*
* @param sheet
* @param columnIndex
* @param valStr
*/
public void setColumnWidth(HSSFSheet sheet, int columnIndex, String valStr, String reg) {
if (null == sheet) {
sheet = getDefaultSheet();
}
if (null == valStr){ valStr = "";}
//判断当前宽度
int width = sheet.getColumnWidth(columnIndex);
int newWidth = width;
//判断每一行的数据长度,遍历每行取最长
if(null == reg || "".equals(reg.trim())){ reg = "\r\n";}
for (String line : Arrays.asList(valStr.split(reg))) {
double lineWidth = line.length() * baseWidthUnit;
if (lineWidth > newWidth) {
newWidth = (int) lineWidth;
}
}
if (newWidth > width) {
sheet.setColumnWidth(columnIndex, newWidth);
}
}
public void setColumnWidth(int columnIndex, String valStr) {
setColumnWidth(null, columnIndex, valStr, null);
}
/**
* 下载excel文件
*
* @param response
* @param name 带.xls后缀
* @throws IOException
*/
public void downloadWebFile(HttpServletResponse response, String name) throws IOException {
OutputStream output = response.getOutputStream();
if (!name.contains(".xls")) {
name += ".xls";
}
//中文乱码处理
String fileName = URLEncoder.encode(name, "utf-8");
response.setHeader("Content-disposition",
"attachment;filename=" + fileName +
";filename*=utf-8''" + fileName);
response.setContentType("application/x-download");//下面三行是关键代码,处理乱码问题
response.setCharacterEncoding("utf-8");
getWorkbook().write(output);
output.close();
}
}