[Java]POI-工具类
前言：Java 中操作 Excel 的工具类 - 基于 POI 封装
- ExcelUtils.java
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
/**
* Excel 工具类
*/
@Component
@Slf4j
public class ExcelUtils {
private static final String HEADER = "header";
private static final String ARIAL = "Arial";
private static final String HIDDEN = "hidden";
/**
* 样式列表
* 在创建初始化
*/
private Map<String, CellStyle> styles;
/**
* 字体列表
* 在创建初始化
*/
private Map<String, Font> font;
/**
* 创建一个工作簿
*
* @param type 创建工作簿的类型
*/
public Workbook createWorkbook(int type) {
Workbook wb;
if (type == 0) {
wb = new HSSFWorkbook();
} else if (type == 1) {
wb = new XSSFWorkbook();
} else {
wb = new SXSSFWorkbook();
}
this.font = createFont(wb);
this.styles = createStyles(wb);
return wb;
}
/**
* 以流的方式打开一个工作簿
*
* @param is 流文件
* @return 打开的工作簿
* @throws Exception yic
*/
@SneakyThrows
public Workbook openWorkBook(InputStream is) {
return WorkbookFactory.create(is);
}
/**
* 创建工作表
*
* @param workbook 工作簿
* @param sheetName 表名
* @param sheetNo sheet 数量
* @param index 序号
*/
public Sheet createSheet(Workbook workbook, String sheetName, double sheetNo, int index) {
Sheet sheet = workbook.createSheet();
// 设置工作表的名称。
if (sheetNo == 0) {
workbook.setSheetName(index, sheetName);
} else {
workbook.setSheetName(index, sheetName + index);
}
return sheet;
}
/**
* 根据表名获取工作表，表名为空时获取第一张工作表
*
* @param workbook 工作簿
* @param sheetName 表名
* @return 工作表
*/
public Sheet getSheet(Workbook workbook, String sheetName) {
Sheet sheet;
if (StringUtils.isNotEmpty(sheetName)) {
// 如果指定 sheet 名，则取指定 sheet 中的内容。
sheet = workbook.getSheet(sheetName);
} else {
// 如果传入的 sheet 名不存在则默认指向第 1 个 sheet.
sheet = workbook.getSheetAt(0);
}
return sheet;
}
/**
* 获取当前工作表的所有行数
*
* @param sheet 工作表
* @return 所有行数
*/
public int getRowCount(Sheet sheet) {
return sheet.getLastRowNum();
}
public void removeEmptyRow(Sheet sheet) {
int i = sheet.getLastRowNum();
Row tempRow;
while (i > 0) {
boolean remove = true;
i--;
tempRow = sheet.getRow(i);
if (tempRow != null) {
for (Cell c : tempRow) {
if (c.getCellType() != CellType.BLANK) {
remove = false;
break;
}
}
}
if (remove) {
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
}
}
}
/**
* 获取工作表行数据
*
* @param sheet 工作表
* @param rowIndex 行下表
* @return 数据集合
*/
public Map<Integer, Object> getRowData(Sheet sheet, int rowIndex) {
Map<Integer, Object> values = new HashMap<>();
Row row = sheet.getRow(rowIndex);
if (row != null) {
for (int i = 0; i < row.getLastCellNum(); i++) {
Object val = this.getCellValue(row, i);
values.put(i, val);
}
if (checkEmpty(values)) {
values = null;
}
} else {
values = null;
}
return values;
}
private boolean checkEmpty(Map<Integer, Object> values) {
boolean isEmpty = true;
for (int i = 0; i < values.size(); i++) {
Object value = values.get(i);
if (!DeprecatedStringUtils.isNullOrEmpty(value)) {
isEmpty = false;
break;
}
}
return isEmpty;
}
/**
* 创建行
*
* @param sheet 工作表
* @param rowIndex 行号
* @return 当前行
*/
public Row createRow(Sheet sheet, int rowIndex) {
return sheet.createRow(rowIndex);
}
/**
* 以“header”样式创建单元格
*
* @param cellValue 单元格内容
* @param row 行
* @param column 列下标
* @return 单元格 Cell
*/
public Cell createHeadCell(String cellValue, Row row, int column) {
// 创建列
Cell cell = row.createCell(column);
// 写入列信息
cell.setCellValue(cellValue);
cell.setCellStyle(styles.get(HEADER));
return cell;
}
/**
* 以“data”样式创建单元格
*
* @param cellValue 单元格内容
* @param row 行
* @param column 列下标
* @return 单元格 Cell
*/
public Cell createDataCell(Object cellValue, Row row, int column) {
//处理 Value 为 Null 的情况
if (DeprecatedStringUtils.isNullOrEmpty(cellValue)) {
cellValue = "";
}
// 创建列
Cell cell = row.createCell(column);
// 写入列信息
if (cellValue instanceof Double) {
cell.setCellValue((Double) cellValue);
} else if (cellValue instanceof Date) {
cell.setCellValue((Date) cellValue);
} else if (cellValue instanceof LocalDateTime) {
cell.setCellValue((LocalDateTime) cellValue);
} else if (cellValue instanceof LocalDate) {
cell.setCellValue((LocalDate) cellValue);
} else if (cellValue instanceof Calendar) {
cell.setCellValue((Calendar) cellValue);
} else if (cellValue instanceof RichTextString) {
cell.setCellValue((RichTextString) cellValue);
} else if (cellValue instanceof Boolean) {
cell.setCellValue((boolean) cellValue);
} else {
cell.setCellValue(cellValue.toString());
}
cell.setCellStyle(styles.get("data"));
return cell;
}
/**
* 指定样式创建富文本单元格
*
* @param value 富文本内容
* @param row 行
* @param column 列下标
* @param style 样式
* @return 单元格 Cell
*/
public Cell createCell(HSSFRichTextString value, Row row, int column, String style) {
Cell cell = row.createCell(column);
cell.setCellStyle(styles.get(style));
cell.setCellValue(value);
return cell;
}
/**
* 合并单元格
*
* @param sheet 工作表
* @param rowFrom 行始
* @param rowTo 行末
* @param columnFrom 列始
* @param columnTo 列末
*/
public void mergedRegion(Sheet sheet, int rowFrom, int rowTo, int columnFrom, int columnTo) {
if (rowFrom == rowTo && columnFrom == columnTo && rowFrom == columnFrom) {
return;
}
CellRangeAddress cellAddresses = new CellRangeAddress(rowFrom, rowTo, columnFrom, columnTo);
sheet.addMergedRegion(cellAddresses);
}
/**
* 创建表格样式
* 验证，下拉和提示
* Hssf 格式下，prompt 和 combo 选一；
*
* @param sheet 工作表
* @param row 行
* @param column 列下标
* @param prompt 提示
* @param combo 下拉列表
*/
public void setDataValidation(Sheet sheet, Row row, int column, String prompt, String[] combo) {
// 设置列宽
row.setHeight((short) (14 * 20));
// 如果设置了提示信息则鼠标放上去提示。
if (StringUtils.isNotEmpty(prompt)) {
// 这里默认设了 2-101 列提示。
setPrompt(sheet, "", prompt, 1, 100, column, column);
}
// 如果设置了 combo 属性则本列只能选择不能输入
if (combo != null && combo.length > 0) {
// 这里默认设了 2-101 列只能选择不能输入。
setValidation(sheet, combo, 1, 100, column, column);
}
}
/**
* 创建默认字体
*
* @param workbook wb
* @return 默认字体
*/
private Map<String, Font> createFont(Workbook workbook) {
Map<String, Font> fonts = new HashMap<>();
Font dataFont = workbook.createFont();
dataFont.setFontHeightInPoints((short) 10);
dataFont.setFontName(ARIAL);
fonts.put("data", dataFont);
Font headerFont = workbook.createFont();
headerFont.setFontName(ARIAL);
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBold(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
fonts.put(HEADER, headerFont);
Font explainFont = workbook.createFont();
explainFont.setFontName(ARIAL);
explainFont.setFontHeightInPoints((short) 10);
fonts.put("explain", explainFont);
HSSFFont explainBoldFont = (HSSFFont) workbook.createFont();
explainBoldFont.setFontName(ARIAL);
explainBoldFont.setBold(true);
explainBoldFont.setFontHeightInPoints((short) 16);
fonts.put("explainBold", explainBoldFont);
//创建字体
HSSFFont ftRed = (HSSFFont) workbook.createFont();
ftRed.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
fonts.put("test", ftRed);
return fonts;
}
/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
// 写入各条记录，每条记录对应 excel 表中的一行
Map<String, CellStyle> cellStyles = new HashMap<>();
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFont(this.font.get("data"));
cellStyles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(cellStyles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFont(this.font.get(HEADER));
cellStyles.put(HEADER, style);
//CellStyle-Explain
style = wb.createCellStyle();
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyles.put("explain", style);
//CellStyle-Date
style = wb.createCellStyle();
HSSFDataFormat format = (HSSFDataFormat) wb.createDataFormat();
style.setDataFormat(format.getFormat(CommonString.YYYY_MM_DD));
cellStyles.put("Date", style);
return cellStyles;
}
public CellStyle getDateStyleByFormat(Workbook wb, String formatStr) {
if (this.styles != null && this.styles.containsKey(formatStr)) {
return styles.get(formatStr);
} else {
if (this.styles == null) {
createWorkbook(0);
}
CellStyle style = wb.createCellStyle();
HSSFDataFormat format = (HSSFDataFormat) wb.createDataFormat();
style.setDataFormat(format.getFormat(formatStr));
styles.put(formatStr, style);
return style;
}
}
/**
* 获取字体
*
* @param key key
* @return 字体
*/
public Font getFont(String key) {
if (this.font == null || this.font.size() <= 0) {
createWorkbook(0);
}
return this.font.get(key);
}
/**
* 获取单元格样式
*
* @param key key
* @return 单元格样式
*/
public CellStyle getStyle(String key) {
if (this.styles == null || this.styles.size() <= 0) {
createWorkbook(0);
}
return this.styles.get(key);
}
/**
* 设置 POI XSSFSheet 单元格提示
*
* @param sheet 表单
* @param promptTitle 提示标题
* @param promptContent 提示内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
*/
public void setPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
DataValidation dataValidation = helper.createValidation(constraint, regions);
dataValidation.createPromptBox(promptTitle, promptContent);
dataValidation.setShowPromptBox(true);
sheet.addValidationData(dataValidation);
}
/**
* 设置某些列的值只能输入预制的数据，显示下拉框。
*
* @param sheet 要设置的 sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
*/
public void setValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上，四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理 Excel 兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else if (dataValidation instanceof HSSFDataValidation) {
// 绑定
dataValidation = getDataValidationHidden((HSSFSheet) sheet, regions, textlist, (HSSFWorkbook) sheet.getWorkbook(), firstCol);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
private static HSSFDataValidation getDataValidationHidden(HSSFSheet sheet, CellRangeAddressList regions, String[] dataArray,
HSSFWorkbook wbCreat, int index) {
HSSFSheet hidden = wbCreat.createSheet(HIDDEN + index);
HSSFCell cell;
for (int i = 0, length = dataArray.length; i < length; i++) {
String name = dataArray[i];
HSSFRow row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = wbCreat.createName();
namedCell.setNameName(HIDDEN + index);
namedCell.setRefersToFormula(HIDDEN + index + "!$A$1:$A$" + dataArray.length);
DVConstraint constraint = DVConstraint.createFormulaListConstraint(HIDDEN + index);
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
sheet.addValidationData(validation);
return validation;
}
/**
* 级联组件提示页，后期可以考虑做成下拉级联
*
* @param dataArray 数据
* @param wbCreat 工作簿
* @param index 索引
* @return 级联组件提示页
*/
public Sheet createCascaderPromptSheet(String[] dataArray, Workbook wbCreat, int index) {
Sheet prompt = wbCreat.createSheet("Prompt" + index);
Cell cell;
for (int i = 0, length = dataArray.length; i < length; i++) {
String name = dataArray[i];
Row row = prompt.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
return prompt;
}
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column) {
if (row == null) {
return null;
}
Object val = "";
try {
Cell cell = row.getCell(column);
if (cell != null) {
if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) {
val = cell.getNumericCellValue();
if (DateUtil.isCellDateFormatted(cell)) {
val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
} else {
if ((Double) val % 1 != 0) {
val = new BigDecimal(val.toString());
} else {
val = new DecimalFormat("0").format(val);
}
}
} else if (cell.getCellType() == CellType.STRING) {
val = cell.getStringCellValue();
} else if (cell.getCellType() == CellType.BOOLEAN) {
val = cell.getBooleanCellValue();
} else if (cell.getCellType() == CellType.ERROR) {
val = cell.getErrorCellValue();
}
}
} catch (Exception e) {
return val;
}
return val;
}
/**
* 自动列宽
*
* @param sheet 工作表
* @param columnCount 列行数
*/
public void autoColumnWidth(Sheet sheet, int columnCount) {
for (int i = 0; i < columnCount; i++) {
// 调整每一列宽度
sheet.autoSizeColumn((short) i);
// 解决自动设置列宽中文失效的问题
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
}
}
public String getDateCellValue(Row row, int column) {
Object date = getCellValue(row, column);
if (date instanceof Date) {
String excelDateFormat = row.getCell(column).getCellStyle().getDataFormatString();
excelDateFormat = excelDateFormat.replace("\\", "");
excelDateFormat = excelDateFormat.replace("m", "M");
excelDateFormat = excelDateFormat.replace(";@", "");
excelDateFormat = excelDateFormat.replace(":MM:", ":mm:");
SimpleDateFormat sf = new SimpleDateFormat(excelDateFormat);
return sf.format(date);
} else {
return date.toString();
}
}
public Sheet replace(Sheet sheet, Map<String, String> data) {
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
Row row = rows.next();
if (row != null) {
int rowCount = getRowCount(sheet);
for (int i = 0; i < rowCount; i++) {
Cell cell = row.getCell(i);
if (cell == null || DeprecatedStringUtils.isNullOrEmpty(cell.getStringCellValue())) {
continue;
}
String value = cell.getStringCellValue();
for (Map.Entry<String, String> entry : data.entrySet()) {
if (value.contains(entry.getKey())) {
value = value.replace(String.format("${%s}", entry.getKey()), entry.getValue());
cell.setCellValue(value);
}
}
}
}
}
return sheet;
}
@SneakyThrows
public static void createDownloadStream(HttpServletResponse response, String fileName, Workbook wb) {
response.setCharacterEncoding(CommonString.UTF8);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, CommonString.UTF8));
//激活下载操作
OutputStream os = response.getOutputStream();
try {
wb.write(os);
} finally {
try {
wb.close();
} catch (IOException e1) {
e1.printStackTrace();
}
if (os != null) {
try {
os.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
}
}- WordUtils.java
package com.bf.form.core.utils;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.stereotype.Component;
import java.io.InputStream;
import java.util.Map;
import java.util.SortedMap;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Word 工具类
*
* @author jinyiming
*/
@Component
@Slf4j
public class WordUtils {
@SneakyThrows
public XWPFDocument openWord07(InputStream is) {
return new XWPFDocument(is);
}
@SneakyThrows
public HWPFDocument openWord03(InputStream is) {
return new HWPFDocument(is);
}
public Object replace(Object doc, Map<String, String> data) {
if (doc instanceof HWPFDocument) {
replace03((HWPFDocument) doc, data);
} else if (doc instanceof XWPFDocument) {
replace07((XWPFDocument) doc, data);
}
return doc;
}
private void replace03(HWPFDocument doc, Map<String, String> data) {
//HWPFDocument 对应 03 版 word
//获得要替换的 word 模板
Range range = doc.getRange();
//range 获取 word 中的内容
for (Map.Entry<String, String> entry : data.entrySet()) {
//通过一个 Map 来替换内容 Map 中 key 值存被替换的内容 Map 中 value 值存要替换的内容，最后通过一个循环实现
range.replaceText(String.format("${%s}", entry.getKey()), entry.getValue());
}
}
private void replace07(XWPFDocument doc, Map<String, String> data) {
for (XWPFParagraph p : doc.getParagraphs()) {
replaceInParagraph(p, data);
}
for (XWPFTable tbl : doc.getTables()) {
for (XWPFTableRow row : tbl.getRows()) {
for (XWPFTableCell cell : row.getTableCells()) {
for (XWPFParagraph p : cell.getParagraphs()) {
replaceInParagraph(p, data);
}
}
}
}
}
private void replaceInParagraph(XWPFParagraph paragraph, Map<String, String> data) {
// 先获取段落字符串
String paragraphText = paragraph.getText();
// 只有包含${开头的才需要进入参数匹配
if (paragraphText.contains("${")) {
//获取所有的 XWPFRun 以及所在的段落位置
TreeMap<Integer, XWPFRun> posRuns = getPosToRuns(paragraph);
Matcher matcher = getMatcherKey(paragraphText);
// 检测所有匹配项
while (matcher.find()) {
String g = matcher.group(1);
int start = matcher.start(1);
int end = matcher.end(1);
String value = data.get(g);
if (value == null) {
value = "";
}
replaceCoreValue(posRuns.subMap(start - 2, true, end + 1, true), value);
}
}
}
Pattern pat = Pattern.compile("\\$\\{(.+?)\\}");
private Matcher getMatcherKey(String matcherStr) {
return pat.matcher(matcherStr);
}
private void replaceCoreValue(SortedMap<Integer, XWPFRun> range, String value) {
boolean dollar = false;
boolean leftBrace = false;
boolean rightBrace = false;
XWPFRun prevRun = null;
XWPFRun leftBraceRun = null;
int leftBracePos = -1;
for (XWPFRun run : range.values()) {
if (run == prevRun) {
continue;
}
if (rightBrace) {
break;
}
prevRun = run;
for (int k = 0; k < run.getCTR().sizeOfTArray(); k++) {
if (rightBrace) {
break;
}
String txt;
txt = run.getText(k);
if (txt == null) {
break;
}
if (txt.contains("$") && !dollar) {
txt = txt.replaceFirst("\\$", value);
dollar = true;
}
if (txt.contains("{") && !leftBrace && dollar) {
leftBraceRun = run;
leftBracePos = txt.indexOf('{');
txt = txt.replaceFirst("\\{", "");
leftBrace = true;
}
if (dollar && leftBrace && !rightBrace) {
if (txt.contains("}")) {
if (run == leftBraceRun) {
txt = txt.substring(0, leftBracePos) + txt.substring(txt.indexOf('}'));
} else {
txt = txt.substring(txt.indexOf('}'));
}
} else if (run == leftBraceRun) {
txt = txt.substring(0, leftBracePos);
} else {
txt = "";
}
}
if (txt.contains("}") && !rightBrace) {
txt = txt.replaceFirst("\\}", "");
rightBrace = true;
}
run.setText(txt, k);
}
}
}
private TreeMap<Integer, XWPFRun> getPosToRuns(XWPFParagraph paragraph) {
int pos = 0;
TreeMap<Integer, XWPFRun> map = new TreeMap<>();
for (XWPFRun run : paragraph.getRuns()) {
String runText = run.text();
if (runText != null && runText.length() > 0) {
for (int i = 0; i < runText.length(); i++) {
map.put(pos + i, run);
}
pos += runText.length();
}
}
return map;
}
}- 使用
//创建一个工作簿
Workbook wb = excelUtils.createWorkbook(0);
//以流的方式打开一个工作簿
Workbook wb = excelUtils.openWorkBook(is);
//创建工作表
Sheet sheet = excelUtils.createSheet(wb, "template", 0, 0);
//根据表名获取工作表，表名为空时获取第一张工作表
Sheet sheet = excelUtils.getSheet(wb, "template");
//创建行
excelUtils.createRow(sheet, 0);
//获取当前工作表的所有行数
excelUtils.getRowCount(sheet);
//获取当前工作表的所有行数
excelUtils.removeEmptyRow(sheet);
//获取工作表行数据
excelUtils.getRowData(sheet,0);
//创建单元格
excelUtils.createCell(textString, row, 0, "explain");
//合并单元
excelUtils.mergedRegion(sheet, 0, 0, 0, fields.size() - 1);
//获取字体
excelUtils.getFont("explainBold")- 引用包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</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-scratchpad</artifactId>
<version>5.0.0</version>
</dependency>