本文介绍了一个基于Java的Excel处理工具，支持多种Excel操作，包括模板替换、数据填充、公式计算等高级功能。提供了详细的实现思路、基本概念及使用示例。
推荐：
更加规范和完整的工具：
码云-chimm.excel
码云-chimm.excel：设置单元格公式 值不对Issuse
个人学习练手的工具：java 读取excel(支持xlsx、xls格式， 支持合并单元格)
实现效果
- 效果1：模板简单使用
对应代码，具体见代码实现/测试代码
exr.setMarkersByRow(title1, productList, "product");
- 效果2：替换模板，按行追加数据&公式计算
对应代码，具体见代码实现/测试代码
// 按行替换marker
exr.setMarkersByRow(title2, products, "category"+i);
// 公式计算
exr.computeFormula(title2, "SUM", false, rowIdx, colIdx, rowIdx-5, rowIdx-1, colIdx, colIdx);
- 效果3：替换模板，按列追加数据
对应代码，具体见代码实现/测试代码
// 设置品牌
exr.setMarkersByCol(title3, brandWrapList, "${category" + i + ".brand}");
// 设置描述
exr.setMarkersByCol(title3, descWrapList, "${category" + i + ".desc}");
实现思路
- java中调整excel样式非常麻烦。参考freemarker，提供一个excel模板，打上标记，代码替换标记内容即可
- 约定格式
- 对于 ${*}模板， 将被替换为java中数据指定的内容，单个单元格单个内容替换。允许一个单元格内容存在多个${*}模板
- 对于 ${*.*}模板 我们要求一行中 ${row.a} ${row.b} ${row.c} 属于同一个数据结构，不能有其他数据结构。下一组的${row.a} ${row.b} ${row.c} 将被写入excel下一行。一个单元格只允许存在一个${row.*}模板
基本概念
- excel文件对应的是 workbook（xls xlsx）
- 每个workbook 中有多个 sheet
- 每个sheet有多行 Row
- 每行有多列 Cell
- sheet.getLastRowNum：获取sheet最后有数据行的下标(包含空行)。最后有数据的行是第n行则返回n-1，一行数据都没有则返回-1。行遍历，下标从0开始
- row.getLastCellNum：获取某行最后有数据的列(包含空列)。只有第一列有数据则返回1，最后有数据的列是第n列则返回n，一列数据都没有则返回-1。列遍历，下标从0开始
- sheet.getPhysicalNumberOfRows：获取sheet中实际有数据的行数（即，非默认格式的行数），一行数据都没有则返回0
- row.getPhysicalNumberOfCells：获取某行实际有数据的列数（即，非默认格式的列数），一列数据都没有则返回0
注意事项
1、获取的excel行数不正确解决方法
2、poi认为的有数据：一般情况，非默认格式的行或列都是有数据的。特例，行高改变，被认为该行是有数据的；列宽改变，被认为该列是无数据列。
依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
对外提供的方法
- replaceMarker: 实现${*} 格式的模板内容替换，一个单元格允许有多个${*} 。方法会有重载，允许替换所有sheet或指定sheet
- setMarkersByRow：替换${*.*}格式marker，批量按行替换指定属性列单元格内容。方法会有重载，允许替换所有sheet或指定sheet
- setMarkersByCol：替换${*.*}格式marker，批量按列替换指定属性行单元格内容。方法会有重载，允许替换所有sheet或指定sheet
- renameSheetTitle：sheet标题重命名
- getContentLocation：获取内容在sheet中的位置。按照从上到下，从左到右的顺序遍历，返回第一个该内容所在的位置
- getContentAllLocation：获取内容在sheet中所有位置，按照从上到下，从左到右的顺序遍历
- computeFormula：excel平均表达式或求和表达式计算
- getBytes：获取byte[]，把数据存入文件中，实现生成新的excel文件
- coverWriteToLocal：保存excel文件到本地
- downExcel：通过网页导出excel文件
- close：关闭流
内部方法
- 循环workbook
- 循环sheet
- 循环row
- java对象转map
…
代码实现
所有代码都在一个类，方便直接使用。
完整代码及excel模板：https://gitee.com/andus_top/java-excel
测试代码
public static void main(String[] args) throws IOException, IllegalAccessException {
ExcelWriter exr = new ExcelWriter("C:/Users/Administrator/Desktop/template.xlsx");
/*----------------${*}marker替换示例----------------*/
// 替换所有sheet中${year}、${quarter}、${deptName}、${recordUser}
HashMap<String, Object> baseInfoMap = new HashMap<>();
baseInfoMap.put("year", 2021);
baseInfoMap.put("quarter", 3);
baseInfoMap.put("deptName", "产品库存管理部");
baseInfoMap.put("recordUser", "搬砖工人");
// 替换excel中所有sheet中的模板
exr.replaceMarker(baseInfoMap);
String title1 = "AAA";
String title2 = "BBB";
String title3 = "CCC";
baseInfoMap.put("title1", title1);
baseInfoMap.put("title2", title2);
baseInfoMap.put("title3", title3);
// 可每个属性单独替换
exr.replaceMarker(baseInfoMap);
// sheet重命名
exr.renameSheetTitle("title1", title1);
exr.renameSheetTitle("title2", title2);
exr.renameSheetTitle("title3", title3);
// 初始化参数。
List<Product> productList = initData();
/*----------------sheet1 模板简单使用----------------*/
exr.setMarkersByRow(title1, productList, "product");
/*----------------sheet2 marker替换（按行追加数据）与公式计算----------------*/
/**
* 申明：ExcelWriter.setMarkersByRow及其重载方法，不支持跨行合并单元格处理。见ExcelWriter.copyRowMerge方法fixme部分。
* 所以，我们约定：
* 1、约定每个category对应的品牌个数为5个，且模板中需要把5列创建好，可以多余5列，但是不能少于。
* 2、每个category下对应的品牌个数不一致的话，公式计算时可能会有问题。
* 3、合并单元格问题，可根据工具类ExcelWriter.copyRowMerge方法中fixme部分，再结合业务进行修改
* 4、公式计算问题，求平均或者合计的行或列，可根据模板调整行或列的下移后者右移，见ExcelWriter.setRowMarkersByCol；ExcelWriter.setRowMarkersByRow方法中fixme部分
*/
// 按大类分组。key：大类名称，value：对应集合，size=5
Map<String, List<Product>> categoryMap = productList.stream().collect(Collectors.groupingBy(Product::getCategory));
for (int i = 1; i <= categoryMap.keySet().size(); i++) {
String category = String.valueOf(categoryMap.keySet().toArray()[i-1]);
baseInfoMap.put("category"+i, category);
exr.replaceMarker(baseInfoMap);
List<Product> products = categoryMap.get(category);
// 按行替换marker
exr.setMarkersByRow(title2, products, "category"+i);
}
// 设置sheet中所有合计值。
List<Integer[]> contentAllLocation = exr.getContentAllLocation(title2, "合计");
for (Integer[] location : contentAllLocation) {
int rowIdx = location[0];
int colIdx = location[3]+1;
exr.computeFormula(title2, "SUM", false, rowIdx, colIdx,
rowIdx-5, rowIdx-1, colIdx, colIdx);
}
/*----------------sheet3 marker替换（按列追加数据）----------------*/
/**
* 申明：ExcelWriter.setMarkersByCol及其重载方法，不支持跨行合并单元格处理。见ExcelWriter.copyRowMerge方法fixme部分。
* 所以，我们约定：
* 1、约定每个category对应的品牌个数为5个，且模板中需要把5列创建好，可以多余5列，但是不能少于。
* 2、每个category下对应的品牌个数不一致的话，公式计算时可能会有问题。
* 3、合并单元格问题，可根据工具类ExcelWriter.copyRowMerge方法中fixme部分，再结合业务进行修改
* 4、公式计算问题，求平均或者合计的行或列，可根据模板调整行或列的下移后者右移，见ExcelWriter.setRowMarkersByCol；ExcelWriter.setRowMarkersByRow方法中fixme部分
*/
for (int i = 1; i <= categoryMap.keySet().size(); i++) {
String category = String.valueOf(categoryMap.keySet().toArray()[i-1]);
// 外层List代表有几列数据，内层List代表当前列数据有几行值
List<List<Object>> brandWrapList = new ArrayList<>();
List<List<Object>> descWrapList = new ArrayList<>();
List<Product> products = categoryMap.get(category);
for (Product product : products) {
brandWrapList.add(Arrays.asList(product.getBrand(), product.getBrand()));// 添加2个重复元素，体现是按列添加数据
descWrapList.add(Arrays.asList(product.getDesc(), product.getDesc()));// 添加2个重复元素，体现是按列添加数据
}
// 设置品牌
exr.setMarkersByCol(title3, brandWrapList, "${category" + i + ".brand}");
// 设置描述
exr.setMarkersByCol(title3, descWrapList, "${category" + i + ".desc}");
// todo：设置平均分和合计
}
byte[] bt = exr.getBytes();
exr.coverWriteToLocal("C:\\Users\\Administrator\\Desktop", "记录表.xlsx", bt);
exr.close();
}
public static List<Product> initData(){
return Arrays.asList(
new Product(1, 10, "20200101", "电子产品类", "OPPO", "说明：拍照5分钟，充电两小时", 2000.22d, LocalDateTime.now()),
new Product(2, 20, "20200101", "电子产品类", "VIVO", "说明：x60", 1111.22d, LocalDateTime.now()),
new Product(3, 30, "20200101", "电子产品类", "小米", "说明：为发烧而生", 3333.22d, LocalDateTime.now()),
new Product(4, 40, "20200101", "电子产品类", "华为", "说明：鸿蒙操作系统", 4444.22d, LocalDateTime.now()),
new Product(5, 50, "20200101", "电子产品类", "苹果", "说明：一起都是产品问题", 5555.22d, LocalDateTime.now()),
new Product(6, 60, "20200101", "家居类", "桌子", "说明：这是桌子", 17.22d, LocalDateTime.now()),
new Product(7, 70, "20200101", "家居类", "椅子", "说明：这是椅子", 21.22d, LocalDateTime.now()),
new Product(8, 80, "20200101", "家居类", "油烟机", "说明：这是油烟机", 34.22d, LocalDateTime.now()),
new Product(9, 90, "20200101", "家居类", "电风扇", "说明：这是电风扇", 56.22d, LocalDateTime.now()),
new Product(10, 100, "20200101", "家居类", "液晶电视", "说明：这是液晶电视", 32.22d, LocalDateTime.now())
);
}
class Product {
public int id;
public int count;
public String addOrder; // 进货编号
public String category;
public String brand;
public String desc;
public double price;
public LocalDateTime createTime;
// get set 构造方法省略
}
ExcelWriter.java 工具类
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 创建excel <br>
* 实现思路：java中调整excel样式非常麻烦。参考freemarker，提供一个excel模板，打上标记，代码替换标记内容即可 <br>
* 约定格式： <br>
* 对于 ${*} marker， 将被替换为java中数据指定的内容，单个单元格单个内容替换。允许一个单元格内容存在多个${*} marker<br>
* 对于 ${*.*} marker 我们要求一行中 ${row.a} ${row.b} ${row.c} 属于同一个数据结构的属性，不能有其他数据结构。下一组的${row.a} ${row.b} ${row.c} 将被写入excel下一行。一个单元格只允许存在一个${row.*} marker <br>
* @Date 2021/6/25 13:01
* @author andus.top
*/
public class ExcelWriter {
// excel对象
private Workbook workbook;
// 暂存将会替换的 ${*}模板的数据
private Map<String, Object> tmpMaker = new HashMap<>();
/**
* 传入模板
* @param in 文件内容流
* @param xls true:表示是旧的excel(*.xls) ,false:表示是新版excel(*.xlsx)
* @author andus.top
* @date 2021/6/25 13:01
* @return null
**/
public ExcelWriter(InputStream in, boolean xls) throws IOException {
if(xls){
workbook = new HSSFWorkbook(in);
}else{
workbook = new XSSFWorkbook(in);
}
}
/**
* 传入模板
* @param template excel模板
* @author andus.top
* @date 2021/6/25 13:07
* @return null
**/
public ExcelWriter(File template) throws IOException {
String fileName = template.getName();
try(FileInputStream in = new FileInputStream(template)){
if(fileName.endsWith(".xls")){
workbook = new HSSFWorkbook(in);
}else if(fileName.endsWith(".xlsx")){
workbook = new XSSFWorkbook(in);
}
}catch (Exception e){
e.printStackTrace();
}finally {
}
if(workbook == null){
throw new RuntimeException("excel 文件格式错误");
}
}
/**
* 传入模板
* @param templateFilePath 文件路径，包含文件后缀
* @author andus.top
* @date 2021/6/25 12:52
* @return null
**/
public ExcelWriter(String templateFilePath) throws IOException {
this(new File(templateFilePath));
}
/**
* 通过sheet名称获取sheet对象
* @param sheetTitle map集合。<br>如key=unitName，value=CBA公司，所有sheet中${unitName}将会被替换为'CBA公司'
* @author andus.top
* @date 2021/6/25 23:31
* @return Sheet
**/
public Sheet getSheet(String sheetTitle){
return workbook.getSheet(sheetTitle);
}
/**
* 通过sheet下标获取sheet对象
* @param sheetIdx sheet下标，从0开始
* @author andus.top
* @date 2021/6/25 23:32
* @return Sheet
**/
public Sheet getSheet(int sheetIdx){
return workbook.getSheetAt(sheetIdx);
}
/**
* 替换excel所有sheet中${*}模板为info中的值，没有则置空
* @param info map集合。<br>如key=unitName，value=CBA公司，所有sheet中${unitName}将会被替换为'CBA公司'
* @author andus.top
* @date 2021/6/25 13:18
* @return ExcelWriter
**/
public ExcelWriter replaceMarker(Map<String, Object> info) {
replaceWorkbookMarker(info);
if(info != null)info.clear();
return this;
}
/**
* 替换excel所有sheet中${key}模板为value值，没有则置空
* @param key 模板中的key
* @param value 模板被替换的值
* @author andus.top
* @date 2021/6/25 13:28
* @return ExcelWriter
**/
public ExcelWriter replaceMarker(String key, Object value) {
tmpMaker.put(key, value);
replaceWorkbookMarker(tmpMaker);
tmpMaker.clear();
return this;
}
/**
* 替换excel指定sheet中${*}模板为info中的值，没有则置空
* @param sheetIndex sheet 下标序号,从0开始
* @param info map集合。<br>如key=unitName，value=CBA公司，所有sheet中${unitName}将会被替换为'CBA公司'
* @author andus.top
* @date 2021/6/25 13:30
* @return ExcelWriter
**/
public ExcelWriter replaceMarker(int sheetIndex, Map<String, Object> info) {
if (sheetIndex < 0 || sheetIndex >= workbook.getNumberOfSheets()) {
return this;
}
replaceSheetMarker(workbook.getSheetAt(sheetIndex), info);
if(info != null)info.clear();
return this;
}
/**
* 替换excel指定sheet中${key}模板为value值，没有则置空
* @param sheetIndex sheet 下标序号,从0开始
* @param key 模板中的key
* @param value 模板被替换的值
* @author andus.top
* @date 2021/6/25 13:30
* @return ExcelWriter
**/
public ExcelWriter replaceMarker(int sheetIndex, String key, Object value) {
if (sheetIndex < 0 || sheetIndex >= workbook.getNumberOfSheets()) {
return this;
}
// 暂存 key-value
tmpMaker.put(key, value);
replaceSheetMarker(workbook.getSheetAt(sheetIndex), tmpMaker);
tmpMaker.clear();
return this;
}
/**
* 替换excel指定sheet中${*}模板为info中的值，没有则置空
* @param sheetTitle sheet名称
* @param info map集合。<br>如key=unitName，value=CBA公司，所有sheet中${unitName}将会被替换为'CBA公司'
* @author andus.top
* @date 2021/6/25 13:34
* @return ExcelWriter
**/
public ExcelWriter replaceMarker(String sheetTitle, Map<String, Object> info) {
replaceSheetMarker(workbook.getSheet(sheetTitle), info);
if(info != null)info.clear();
return this;
}
/**
* 替换excel指定sheet中${key}模板为value值，没有则置空
* @param sheetTitle sheet名称
* @param key 模板中的key
* @param value 模板被替换的值
* @author andus.top
* @date 2021/6/25 13:30
* @return ExcelWriter
**/
public ExcelWriter replaceMarker(String sheetTitle, String key, Object value) {
tmpMaker.put(key, value);
replaceSheetMarker(workbook.getSheet(sheetTitle), tmpMaker);
tmpMaker.clear();
return this;
}
/**
* 批量设置模板中${rowKey.x}的值，按行追加数据
* @param infos 将excel中的${rowKey.*} 替换为infos中bean对象的属性值。如List<User>集合，一个user对应excel一行
* @param rowKey 设置rowkey。如user.id，此时rowkey=user，id为User的属性
* @author andus.top
* @date 2021/6/25 13:09
* @return ExcelWriter
**/
public ExcelWriter setMarkersByRow(List<?> infos, String rowKey) throws IllegalAccessException {
setWorkbookMarkers(infos, rowKey);
return this;
}
/**
* 批量设置指定sheet中${rowKey.x}模板的值，按行追加数据<br>不会覆盖首列为“合计”的行
* @param sheetIndex sheet下标（从0开始）
* @param infos 将excel中的${rowKey.*} 设置为infos中的值。如List<User>集合，一个user对应excel一行
* @param rowKey 设置rowkey。如user.id，此时rowkey=user，id为User的属性
* @author andus.top
* @date 2021/6/25 13:12
* @return ExcelWriter
**/
public ExcelWriter setMarkersByRow(int sheetIndex, List<?> infos, String rowKey) throws IllegalAccessException {
if (sheetIndex < 0 || sheetIndex >= workbook.getNumberOfSheets()) {
return this;
}
setSheetMarkersByRow(workbook.getSheetAt(sheetIndex), infos, rowKey);
return this;
}
/**
* 批量设置指定sheet中${rowKey.x}模板的值，按行追加数据<br>不会覆盖首列为“合计”的行
* @param sheetTitle sheet下标（从0开始）
* @param infos 将excel中的${rowKey.*} 设置为infos中的值。如List<User>集合，一个user对应excel一行
* @param rowKey 设置rowkey。如user.id，此时rowkey=user，id为User的属性
* @author andus.top
* @date 2021/6/25 13:16
* @return ExcelWriter
**/
public ExcelWriter setMarkersByRow(String sheetTitle, List<?> infos, String rowKey) throws IllegalAccessException {
setSheetMarkersByRow(workbook.getSheet(sheetTitle), infos, rowKey);
return this;
}
/**
* excel所有sheet中，从${}模板的位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param infos bean对象的集合。外层List代表有几列数据，内层List代表当前列数据有几行值
* @param marker 设置marker模板。假设${}模板在sheet中[row=0，col=1]的位置。将会从[0,1]单元格为起始位置，按列添加数据。
* @author andus.top
* @date 2021/6/25 13:55
* @return ExcelWriter
**/
public ExcelWriter setMarkersByCol(List<List<Object>> infos, String marker) throws IllegalAccessException {
for (int x = 0, sw = workbook.getNumberOfSheets(); x < sw; x++) {
setSheetMarkersByCol(workbook.getSheetAt(x), infos, marker);
}
return this;
}
/**
* excel所有sheet中，从startWriteLocation位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param infos List对象。外层List代表有几列数据，内层List代表当前列数据有几行值
* @param startWriteLocation 单元格位置。数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
* @author andus.top
* @date 2021/6/25 14:12
* @return ExcelWriter
**/
public ExcelWriter setMarkersByCol(List<List<Object>> infos, Integer[] startWriteLocation) throws IllegalAccessException {
for (int x = 0, sw = workbook.getNumberOfSheets(); x < sw; x++) {
setRowMarkersByCol(workbook.getSheetAt(x), infos, startWriteLocation);
}
return this;
}
/**
* 指定sheet中，从${}模板的位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param sheetTitle sheet名称
* @param infos List对象。外层List代表有几列数据，内层List代表当前列数据有几行值
* @param marker 设置marker模板。假设${}模板在sheet中[row=0，col=1]的位置。将会从[0,1]单元格为起始位置，按列添加数据。
* @author andus.top
* @date 2021/6/25 14:20
* @return ExcelWriter
**/
public ExcelWriter setMarkersByCol(String sheetTitle, List<List<Object>> infos, String marker) throws IllegalAccessException {
if (StringUtils.isEmpty(sheetTitle)) {
return this;
}
setSheetMarkersByCol(workbook.getSheet(sheetTitle), infos, marker);
return this;
}
/**
* 指定sheet中，从startWriteLocation位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param sheetTitle sheet名称
* @param infos List对象。外层List代表有几列数据，内层List代表当前列数据有几行值
* @param startWriteLocation 单元格位置。数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
* @author andus.top
* @date 2021/6/25 14:23
* @return ExcelWriter
**/
public ExcelWriter setMarkersByCol(String sheetTitle, List<List<Object>> infos, Integer[] startWriteLocation) throws IllegalAccessException {
if (StringUtils.isEmpty(sheetTitle)) {
return this;
}
setRowMarkersByCol(workbook.getSheet(sheetTitle), infos, startWriteLocation);
return this;
}
/**
* 指定sheet中，从${}模板的位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param sheetIndex sheet下标，从0开始
* @param infos List对象。外层List代表有几列数据，内层List代表当前列数据有几行值
* @param marker 设置marker模板。假设${}模板在sheet中[row=0，col=1]的位置。将会从[0,1]单元格为起始位置，按列添加数据。
* @author andus.top
* @date 2021/6/25 14:24
* @return ExcelWriter
**/
public ExcelWriter setMarkersByCol(int sheetIndex, List<List<Object>> infos, String marker) throws IllegalAccessException {
if (sheetIndex < 0 || sheetIndex >= workbook.getNumberOfSheets()) {
return this;
}
setSheetMarkersByCol(workbook.getSheetAt(sheetIndex), infos, marker);
return this;
}
/**
* 指定sheet中，从startWriteLocation位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param sheetIndex sheet名称
* @param infos List对象。外层List代表有几列数据，内层List代表当前列数据有几行值
* @param startWriteLocation 单元格位置。数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
* @author andus.top
* @date 2021/6/25 14:23
* @return ExcelWriter
**/
public ExcelWriter setMarkersByCol(int sheetIndex, List<List<Object>> infos, Integer[] startWriteLocation) throws IllegalAccessException {
if (sheetIndex < 0 || sheetIndex >= workbook.getNumberOfSheets()) {
return this;
}
setRowMarkersByCol(workbook.getSheetAt(sheetIndex), infos, startWriteLocation);
return this;
}
/**
* 根据内容获取该内容所在的位置（返回第一内容所在的位置）<br>从第0行到0列到最后一行最后一列，依次遍历
* @param sheetTitle sheet名称
* @param tar 要查找的数据
* @author andus.top
* @date 2021/6/25 15:16
* @return Integer[]<br> 1、返回null，表示该数据不存在
* <br> 2、数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
**/
public Integer[] getContentLocation(String sheetTitle, String tar){
return getContentLocation(workbook.getSheet(sheetTitle), tar);
}
/**
* 根据内容获取该内容所在的位置（返回第一内容所在的位置）<br>从第0行到0列到最后一行最后一列，依次遍历
* @param sheetIdx sheet下标从0开始
* @param tar 要查找的数据
* @author andus.top
* @date 2021/6/25 15:17
* @return Integer[]<br> 1、返回null，表示该数据不存在
* <br> 2、数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
**/
public Integer[] getContentLocation(int sheetIdx, String tar){
return getContentLocation(workbook.getSheetAt(sheetIdx), tar);
}
/**
* 根据内容获取该内容 在该sheet中所有的位置<br>从第0行到0列到最后一行最后一列，依次遍历
* @param sheetTitle sheet名称
* @param tar 要查找的数据
* @author andus.top
* @date 2021/6/25 15:18
* @return java.util.List<java.lang.Integer[]> <br> 1、返回null，表示该数据不存在
* <br>2、数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
**/
public List<Integer[]> getContentAllLocation(String sheetTitle, String tar){
return getContentAllLocation(workbook.getSheet(sheetTitle), tar);
}
/**
* 根据内容获取该内容 在该sheet中所有的位置<br>从第0行到0列到最后一行最后一列，依次遍历
* @param sheetIdx sheet下标从0开始
* @param tar 要查找的数据
* @author andus.top
* @date 2021/6/25 15:19
* @return java.util.List<java.lang.Integer[]><br> 1、返回null，表示该数据不存在
* <br> 2、数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
**/
public List<Integer[]> getContentAllLocation(int sheetIdx, String tar){
return getContentAllLocation(workbook.getSheetAt(sheetIdx), tar);
}
/**
* 单元格表达式，按列计算
* @param sheetTitle sheet名称
* @param formulaFlag 表达式。SUM/AVERAGE
* @param isPoint true,结果保留两位小数，false，结果保留0为小数
* @param startWriteLocation 存放结果单元格位置。数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
* @param rowStarIdx 数据开始行下标，从0开始
* @param rowEndIdx 数据结束行下标，从0开始
* @param colStartIdx 数据开始列下标，从0开始
* @param colEndIdx 数据结束列下标，从0开始
* @author andus.top
* @date 2021/6/25 15:52
* @return
**/
public void computeFormula(String sheetTitle, String formulaFlag, Boolean isPoint, Integer[] startWriteLocation, int rowStarIdx, int rowEndIdx, int colStartIdx, int colEndIdx){
Sheet sheet = workbook.getSheet(sheetTitle);
computeFormula(sheet, formulaFlag, isPoint, startWriteLocation, rowStarIdx, rowEndIdx, colStartIdx, colEndIdx);
}
/**
* 单元格表达式，按列计算
* @param sheet sheet
* @param formulaFlag 表达式。SUM/AVERAGE
* @param isPoint true,结果保留两位小数，false，结果保留0为小数
* @param startWriteLocation 存放结果单元格位置。数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
* @param rowStarIdx 数据开始行下标，从0开始
* @param rowEndIdx 数据结束行下标，从0开始
* @param colStartIdx 数据开始列下标，从0开始
* @param colEndIdx 数据结束列下标，从0开始
* @author andus.top
* @date 2021/6/25 16:00
* @return
**/
public void computeFormula(Sheet sheet, String formulaFlag, Boolean isPoint, Integer[] startWriteLocation, int rowStarIdx, int rowEndIdx, int colStartIdx, int colEndIdx){
int resRowIdx = startWriteLocation[0]; // 合并单元格取首行
int resColIdx = startWriteLocation[2]; // 合并单元格取首列
if(sheet != null) {
Cell cell = sheet.getRow(resRowIdx).getCell(resColIdx);
computeFormula(sheet, formulaFlag, isPoint, cell, rowStarIdx, rowEndIdx, colStartIdx, colEndIdx);
}
}
/**
* 单元格表达式，按列计算
* @param sheetTitle sheet名称
* @param formulaFlag 表达式。SUM/AVERAGE
* @param isPoint true,结果保留两位小数，false，结果保留0为小数
* @param resRowIdx 存放计算结果cell 行下标
* @param resColIdx 存放计算结果cell 列下标
* @param rowStarIdx 数据开始行下标，从0开始
* @param rowEndIdx 数据结束行下标，从0开始
* @param colStartIdx 数据开始列下标，从0开始
* @param colEndIdx 数据结束列下标，从0开始
* @author andus.top
* @date 2021/6/25 15:52
* @return
**/
public void computeFormula(String sheetTitle, String formulaFlag, Boolean isPoint, int resRowIdx, int resColIdx, int rowStarIdx, int rowEndIdx, int colStartIdx, int colEndIdx){
Sheet sheet = workbook.getSheet(sheetTitle);
if(sheet != null) {
Cell cell = sheet.getRow(resRowIdx).getCell(resColIdx);
computeFormula(sheet, formulaFlag, isPoint, cell, rowStarIdx, rowEndIdx, colStartIdx, colEndIdx);
}
}
/**
* 单元格表达式，按列计算
* @param sheetTitle sheet名称
* @param formulaFlag 表达式。SUM/AVERAGE
* @param isPoint true,结果保留两位小数，false，结果保留0为小数
* @param cell 存放计算结果的cell
* @param rowStarIdx 数据开始行下标，从0开始
* @param rowEndIdx 数据结束行下标，从0开始
* @param colStartIdx 数据开始列下标，从0开始
* @param colEndIdx 数据结束列下标，从0开始
* @author andus.top
* @date 2021/6/25 15:51
* @return
**/
public void computeFormula(String sheetTitle, String formulaFlag, Boolean isPoint, Cell cell, int rowStarIdx, int rowEndIdx, int colStartIdx, int colEndIdx){
Sheet sheet = workbook.getSheet(sheetTitle);
computeFormula(sheet, formulaFlag, isPoint, cell, rowStarIdx, rowEndIdx, colStartIdx, colEndIdx);
}
/**
* 单元格表达式，按列计算
* @param sheet sheet
* @param formulaFlag 表达式。SUM/AVERAGE
* @param isPoint true,结果保留两位小数，false，结果保留0为小数
* @param cell 存放计算结果的cell
* @param rowStartIdx 数据开始行下标，从0开始
* @param rowEndIdx 数据结束行下标，从0开始
* @param colStartIdx 数据开始列下标，从0开始
* @param colEndIdx 数据结束列下标，从0开始
* @author andus.top
* @date 2021/6/25 15:38
* @return
**/
public void computeFormula(Sheet sheet, String formulaFlag, Boolean isPoint, Cell cell, int rowStartIdx, int rowEndIdx, int colStartIdx, int colEndIdx){
if(sheet != null && cell != null) {
if(isPoint){
// 结果保留两位小数,并且内容居中显示
setDoubleCell(cell);
}else{
// 结果保留0为小数,并且内容居中显示
setNumberCell(cell);
}
String colTag = CellReference.convertNumToColString(colStartIdx);
String colTag2 = CellReference.convertNumToColString(colEndIdx);
//sum/avg函数
String formula = formulaFlag + "(" + colTag + (rowStartIdx+1) + ":" + colTag2 + (rowEndIdx+1) + ")"; // SUM(C1:C9) AVERAGE(C4:C8)
cell.setCellFormula(formula);
// 进行强制公式计算
sheet.setForceFormulaRecalculation(true);// 重新计算公式的意思
}
}
/**
* 在指定的行插入一行，样式来源于sourceRow行
* @param sheet 某个sheet页
* @param sourceRow 具备样式的行
* @param tarRowNum 拷贝到目标行的下标
* @author andus.top
* @date 2021/6/25 15:22
* @return org.apache.poi.ss.usermodel.Row
**/
public static Row insertRow(Sheet sheet, Row sourceRow, int tarRowNum){
int lastRow = sheet.getLastRowNum(); // n行返回n-1
// 插入行
if(tarRowNum <= lastRow){
// 先把tarRowNum到lastRow向下移动n行，在移动后的tarRowNum处（移动后留的空白），新建一个新行，这样就实现了数据行的插入
// 从下标为tarRowNum的行开始移动
// 到下标为lastRow的行结束移动
// 向下移动1行
// true: 要复制 row height 样式
// false: 是否将原始行的高度设置为默认值
sheet.shiftRows(tarRowNum, lastRow, 1, true, false); // 向excel插入行，并复制原有样式
}
// 在tarRowNum 下标处创建行
Row newRow = sheet.createRow(tarRowNum);
copyRowStyle(sourceRow, newRow);
return newRow;
}
/**
* 复制整个sheet
* @param sheetName source sheet 名称
* @param targetName target sheet 名称
* @author andus.top
* @date 2021/6/25 16:28
* @return ExcelWriter
**/
public ExcelWriter copySheet(String sheetName, String targetName) {
int index = workbook.getSheetIndex(sheetName);
if (index >= 0) {
Sheet sh = workbook.cloneSheet(index);
workbook.setSheetName(workbook.getSheetIndex(sh), targetName);
}
return this;
}
/**
* 删除sheet
* @param sheetName sheet 名称
* @author andus.top
* @date 2021/6/25 16:28
* @return ExcelWriter
**/
public ExcelWriter removeSheet(String sheetName ) {
workbook.removeSheetAt(workbook.getSheetIndex(sheetName));
return this;
}
/**
* 更换sheet名称
* @param oldName 原sheet名称
* @param newName 新sheet名称
* @author andus.top
* @date 2021/6/18 19:01
* @return void
**/
public void renameSheetTitle(String oldName, String newName){
workbook.setSheetName(workbook.getSheetIndex(oldName), newName);
}
/**
* 获取workbook的字节数组，方便转为excel文件
* @param
* @author andus.top
* @date 2021/6/25 16:29
* @return byte[]
**/
public byte[] getBytes() {
byte[] bs = null;
try(ByteArrayOutputStream baos = new ByteArrayOutputStream();){
workbook.write(baos);
bs = baos.toByteArray();
baos.flush();
}catch (Exception e){
throw new RuntimeException("getBytes occour error");
}finally {
try{
workbook.close();
}catch (Exception e){
throw new RuntimeException(e);
}
}
return bs;
}
/**
* 根据byte数组，生成文件到本地
* @param filePath 文件路径
* @param fileName 文件名称（需要带后缀，如*.jpg、*.java、*.xml）
* @param bs 字节数组
* @author andus.top
* @date 2021/6/25 16:30
* @return void
**/
public void coverWriteToLocal(String filePath, String fileName, byte[] bs) {
BufferedOutputStream bos = null;
FileOutputStream fos = null;
File file = null;
try {
File dir = new File(filePath);
if(!dir.exists() && !dir.isDirectory()){//判断文件目录是否存在
dir.mkdirs();
}
file = new File(filePath + File.separator + fileName);
fos = new FileOutputStream(file);
bos = new BufferedOutputStream(fos);
bos.write(bs);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bos != null) {
try {
bos.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
if (fos != null) {
try {
fos.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
}
/**
* 根据byte数组，网页下载文件
* @param response 文件路径
* @param fileName 文件名称（需要带后缀，如*.jpg、*.java、*.xml）
* @param bytes 字节数组
* @author andus.top
* @date 2021/6/25 16:31
* @return void
**/
public void downExcel(HttpServletResponse response, String fileName, byte[] bytes){
ByteArrayInputStream in = new ByteArrayInputStream(bytes);
try {
response.setContentType("multipart/form-data");
response.setHeader("content-disposition", "attachement;filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1"));
int b = 0;
byte[] buffer = new byte[512];
while (b != -1) {
b = in.read(buffer);
if (b != -1) {
response.getOutputStream().write(buffer, 0, b);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (in != null) {
in.close();
}
response.getOutputStream().flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 关闭流
* @param
* @author andus.top
* @date 2021/6/25 16:32
* @return
**/
public void close(){
try {
if(workbook != null){
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 遍历每个sheet，替换excel所有sheet中${*}模板为info中的值，没有则置空
* @param info map集合。<br>如key=unitName，value=CBA公司，所有sheet中${unitName}将会被替换为'CBA公司'
* @author andus.top
* @date 2021/6/25 16:58
* @return ExcelWriter
**/
private void replaceWorkbookMarker(Map<String, Object> info) {
for (int x = 0, sw = workbook.getNumberOfSheets(); x < sw; x++) {
replaceSheetMarker(workbook.getSheetAt(x), info);
}
}
/**
* 逐行遍历，替换excel所有sheet中${*}模板为info中的值，没有则置空
* @param sheet
* @param sheetInfo map集合。<br>如key=unitName，value=CBA公司，所有sheet中${unitName}将会被替换为'CBA公司'
* @author andus.top
* @date 2021/6/25 16:59
* @return ExcelWriter
**/
private void replaceSheetMarker(Sheet sheet, Map<String, Object> sheetInfo) {
if (sheet == null) {
return;
}
for (int x = sheet.getLastRowNum(); x >= 0; x--) { // sheet.getLastRowNum(); // n行返回n-1
replaceMarkerByRow(sheet.getRow(x), sheetInfo);
}
}
/**
* 替换一行中${*}模板为info中的值，没有则置空
* @param row
* @param info map集合。<br>如key=unitName，value=CBA公司，所有sheet中${unitName}将会被替换为'CBA公司'
* @author andus.top
* @date 2021/6/25 17:06
* @return ExcelWriter
**/
private void replaceMarkerByRow(Row row, Map<String, Object> info) {
if (row == null) {
return;
}
for (int x = 0, lc = row.getLastCellNum(); x < lc; x++) { // row.getLastCellNum() n列，返回n
Cell cell = row.getCell(x);
if (cell != null) {
// 某行某列的内容
String content = String.valueOf(getCellContent(row, cell));
// 核验是否是模板。我们约定格式： ${*} ，${*.*}是模板
List<String[]> markerList = checkMarker(content);
if (markerList != null && !markerList.isEmpty()) {
// if(markerList.size() == 1) {// 单元格只有一个marker，可直接覆盖
// String listKey = markerList.get(0)[0];
// String mapKey = markerList.get(0)[1];
// if (listKey == null && mapKey != null && info.get(mapKey) != null) {
// String value = String.valueOf(info.get(mapKey));
// cell.setCellValue(value);
// }
// }
// 单元格包含多个marker，使用String[]中第三个值采用替换方式
String cellContent = String.valueOf(getCellContent(row, cell));
for (String[] markerInfos : markerList) {
String listKey = markerInfos[0];
String mapKey = markerInfos[1];
String marker = markerInfos[2];
if (listKey == null && mapKey != null && info.get(mapKey) != null) {
String value = String.valueOf(info.get(mapKey));
cellContent = cellContent.replace(marker, value);
}
}
try {
Integer integer = Integer.valueOf(cellContent);
// 设置单元格为数字类型,并且内容居中显示
setNumberCell(cell);
cell.setCellValue(integer);
}catch (Exception ex){
cell.setCellValue(cellContent);
}
}
}
}
}
/**
* 遍历每个sheet，将${rowkey.x}替换为 infos中bean对象的属性值<br>不会覆盖首列为“合计”的行
* @param infos bean对象的集合
* @param rowKey 设置rowkey。如user.id，此时rowkey=user
* @author andus.top
* @date 2021/6/25 17:07
* @return void
**/
private void setWorkbookMarkers(List<?> infos, String rowKey) throws IllegalAccessException {
for (int x = 0, sw = workbook.getNumberOfSheets(); x < sw; x++) {
setSheetMarkersByRow(workbook.getSheetAt(x), infos, rowKey);
}
}
/**
* 将sheet中${rowkey.x}替换为 infos中bean对象的属性值<br>不会覆盖首列为“合计”的行
* @param sheet 某个sheet页
* @param infos bean对象的集合
* @param rowKey 设置rowkey。如user.id，此时rowkey=user
* @author andus.top
* @date 2021/6/25 17:08
* @return
**/
private void setSheetMarkersByRow(Sheet sheet, List<?> infos, String rowKey) throws IllegalAccessException {
if (sheet == null) {
return;
}
for (int x = sheet.getLastRowNum(); x >= 0; x--) { // sheet.getLastRowNum() n行返回n-1
setRowMarkersByRow(sheet.getRow(x), infos, rowKey);
}
}
/**
* 逐行，将${rowkey.x}替换为 infos中bean对象的属性值<br>不会覆盖首列为“合计”的行
* @param infos bean对象的集合
* @param rowKey 设置rowkey。如user.id，此时rowkey=user
* @author andus.top
* @date 2021/6/25 17:17
* @return
**/
private void setRowMarkersByRow(Row row, List<?> infos, String rowKey) throws IllegalAccessException {
if (row == null) {
return;
}
boolean checked = false;
// 暂存表头。key：列的下标，value：bean对象的属性名称
Map<Integer, String> keyMap = new HashMap<>();
for (int x = 0, lc = row.getLastCellNum(); x < lc; x++) {// n列，返回n。
Cell cell = row.getCell(x);
if (cell != null) {
// 某行某列的内容
String content = String.valueOf(getCellContent(row, cell));
// 核验是否是模板。我们约定格式： ${*} 和 ${*.*} 是模板
List<String[]> markerList = checkMarker(content);
if(markerList != null && !markerList.isEmpty()){
for (String[] markerInfos : markerList) {
String listKey = markerInfos[0];
String mapKey = markerInfos[1];
String marker = markerInfos[2];
if(marker.indexOf(".") > 0){
if (rowKey.equals(listKey)) { // 是同一个rowkey才替换
checked = true;
keyMap.put(x, mapKey);
} else {
if (checked) { // 该行，第一列rowkey是对的，此时checked = true; 后面的列有可能rowkey是错的
// String sheetName = row.getSheet().getSheetName();
// throw new RuntimeException("marker 标记错误:[sheet=" + sheetName + ",row=" + (row.getRowNum()+1)
// + ",column=" + x + "],marker=" + content);
} else {
break;
}
}
}
}
}
}
}
if (checked) {
if (infos == null || infos.isEmpty()) {// 如果为空,只需要把marker替换为空
String tmp = null;
for (Map.Entry<Integer, String> e : keyMap.entrySet()) {
// e.getKey() 列的下标；row.getCell()获取该行某列Cell
row.getCell(e.getKey()).setCellValue(tmp);
}
} else {
// 将markers 替换为对应的值
Map<String, Object> tmap = beanToMap(infos.get(0));// java Bean 转为Map。key：属性名，vlaue：属性值
for (Map.Entry<Integer, String> e : keyMap.entrySet()) {
// e.getKey() 列的下标；row.getCell()获取该行某列Cell。
// e.getValue() 某个bean 的属性名
try {
Integer integer = Integer.valueOf(String.valueOf(tmap.get(e.getValue())));
Cell cell = row.getCell(e.getKey());
// 设置单元格为数字类型,并且内容居中显示
setNumberCell(cell);
cell.setCellValue(integer);
}catch (Exception ex){
row.getCell(e.getKey()).setCellValue(String.valueOf(tmap.get(e.getValue())));
}
}
// 复制行并设置对应的值
int r = row.getRowNum(); // 行下标
for (int x = 1, s = infos.size(); x < s; x++) {// 一个bean对应一行，x为相对${rowkey.x}的便宜量。
tmap = beanToMap(infos.get(x));
// 当前需要复制的行的下标。r为模板所在的行
int tarRowNum = r + x;
Row rowx = row.getSheet().getRow(tarRowNum);
// fixme：根据模板中“合计”或"平均"位置调整代码
if(rowx == null || "合计".equals(getCellContent(rowx, rowx.getCell(0)))){// 模板已创建row
rowx = insertRow(row.getSheet(), row, tarRowNum);
}
for (Map.Entry<Integer, String> e : keyMap.entrySet()) {
// 列下标
int cellNum = e.getKey();
// 新创建行的某列单元格
Cell cell = rowx.getCell(cellNum);
if (cell == null) {
// 每行创建单元格
// cell = rowx.createCell(cellNum, CellType.NUMERIC);
cell = rowx.createCell(cellNum);
}
try {
Integer integer = Integer.valueOf(String.valueOf(tmap.get(e.getValue()))); // e.getValue() 某个bean 的属性名
// 设置单元格为数字类型,并且内容居中显示
setNumberCell(cell);
cell.setCellValue(integer);
}catch (Exception ex){
// 该单元格设置值
cell.setCellValue(String.valueOf(tmap.get(e.getValue())));// e.getValue() 某个bean 的属性名
}
}
}
}
}
}
/**
* 逐行遍历，从${}模板的位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param sheet 某个sheet页
* @param infos bean对象的集合。外层list.size 表示新增列数，内层list.size 表示当前新增列新增的行数
* @param marker 设置marker。假设${}模板在sheet中[row=0，col=1]的位置。将会从[0,1]单元格为起始位置，按列添加数据。
* @author andus.top
* @date 2021/6/25 17:19
* @return
**/
private void setSheetMarkersByCol(Sheet sheet, List<List<Object>> infos, String marker) throws IllegalAccessException {
if (sheet == null) {
return;
}
for (int x = sheet.getLastRowNum(); x >= 0; x--) { // sheet.getLastRowNum() n行返回n-1
setRowMarkersByCol(sheet, infos, marker);
}
}
/**
* 从${}模板的位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param infos bean对象的集合。外层list.size 表示新增列数，内层list.size 表示当前新增列新增的行数
* @param marker 设置marker。如${a.b}
* @author andus.top
* @date 2021/6/25 17:24
* @return
**/
private void setRowMarkersByCol(Sheet sheet, List<List<Object>> infos, String marker) throws IllegalAccessException {
if (StringUtils.isEmpty(marker)) {
return;
}
/**
* 1、返回null，表示该数据不存在
* 2、数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
*/
Integer[] contentLocation = getContentLocation(sheet, marker);
setRowMarkersByCol(sheet, infos, contentLocation);
}
/**
* 从${}模板的位置开始，按列追加数据。<br><b>不会覆盖首列单元格内容为“平均”的行&不会覆盖“合计”所在的列</b>
* @param sheet 当前sheet
* @param infos list对象的集合。外层list.size 表示新增列数，内层list.size 表示当前新增列新增的行数
* @param startWriteLocation 单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
* @author andus.top
* @date 2021/6/25 17:25
* @return
**/
private void setRowMarkersByCol(Sheet sheet, List<List<Object>> infos, Integer[] startWriteLocation) throws IllegalAccessException {
if (sheet == null || infos == null || infos.isEmpty() || startWriteLocation == null || startWriteLocation.length != 4) {
return;
}
// 具备样式的单元格
Row sourceRow = sheet.getRow(startWriteLocation[0]); // 始终以 marker 所在行为源样式行
// 每次采用每行第一列为sourceCell。若为平均列，则使用上一行的第一列作为sourceCell。fixme：根据模板中“合计”或"平均"位置调整代码
if("平均".equals(getCellContent(sourceRow, sourceRow.getCell(0)))){
sourceRow = sheet.getRow(startWriteLocation[0]-1);
}
if (startWriteLocation != null) {
for (int c = 0; c < infos.size(); c++) { // c 表示相对 marker 所在列向右偏移的列数
// 向右偏移量。考虑列为合并单元格。contentLocation[3]-contentLocation[2]+1 表示合并单元格列长度
int shiftColIdx = c * (startWriteLocation[3]-startWriteLocation[2]+1);
List<Object> colDataList = infos.get(c);// 每列值的集合
if (colDataList != null && !colDataList.isEmpty()) {
Cell sourceCell = null;
if (c == 0 || c == 1) {
sourceCell = sourceRow.getCell(startWriteLocation[2]);
}
if (c > 1) {
sourceCell = sourceRow.getCell(startWriteLocation[2] + shiftColIdx - (startWriteLocation[3]-startWriteLocation[2]+1));
}
for (int r = 0; r < colDataList.size(); r++) {// r 表示相对 marker 所在行向下偏移的行数
// marker的标记位置，直接替换为数据
if (r == 0 && c == 0 && sourceRow.getRowNum() == startWriteLocation[0]) {
sourceCell.setCellValue(String.valueOf(colDataList.get(r)));
continue;
}
// 向下偏移量。考虑行为合并单元格。startWriteLocation[1]-startWriteLocation[0]+1 表示合并单元格行长度
int shiftRowIdx = r * (startWriteLocation[1]-startWriteLocation[0]+1);
Row tarRow = sheet.getRow(startWriteLocation[0] + shiftRowIdx);
// fixme：根据模板中“合计”或"平均"位置调整代码
if (tarRow == null || "平均".equals(getCellContent(tarRow, tarRow.getCell(0)))) {
tarRow = insertRow(sheet, sourceRow, startWriteLocation[0] + shiftRowIdx);
}
Cell tarCell = tarRow.getCell(startWriteLocation[2] + shiftColIdx);
// 将合计列右移，保留合计列。fixme：根据模板中“合计”或"平均"位置调整代码
if("合计".equals(getCellContent(tarRow, tarRow.getCell(startWriteLocation[2] + shiftColIdx)))){
tarRow.shiftCellsRight(startWriteLocation[2] + shiftColIdx, startWriteLocation[2] + shiftColIdx,1);
tarCell = null;
}
if (tarCell == null) {
// 创建单元格
tarCell = tarRow.createCell(startWriteLocation[2] + shiftColIdx);
}
// 拷贝上一个单元格
copyCellStyle(sheet, sourceCell, tarCell, startWriteLocation[1]-startWriteLocation[0]+1, startWriteLocation[3]-startWriteLocation[2]+1);
// 该单元格设置值
try {
Integer integer = Integer.valueOf(String.valueOf(colDataList.get(r)));
// 设置单元格为数字类型,并且内容居中显示
setNumberCell(tarCell);
tarCell.setCellValue(integer);
}catch (Exception ex){
tarCell.setCellValue(String.valueOf(colDataList.get(r)));
}
}
} else {
// 每列值的集合 为null或空
}
}
}
}
/**
* 根据内容获取该内容所在的位置（返回第一内容所在的位置）<br>从第0行到0列到最后一行最后一列，依次遍历
* @param sheet 当前sheet
* @param tar 要查找的数据
* @author andus.top
* @date 2021/6/25 17:36
* @return java.lang.Integer[]<br> 1、返回null，表示该数据不存在
* <br> 2、数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。行或列数据不同，表明有行或列单元格合并。
**/
private Integer[] getContentLocation(Sheet sheet, String tar){
if(sheet != null && tar != null && tar.length() > 0){
for (int r = 0, endRow = sheet.getLastRowNum(); r <= endRow; r++) {// n行返回n-1
Row row = sheet.getRow(r);
if(row == null){
// 必须执行到最后一行，遍历完excel中的数据
continue;
}
// fixme 【已修复】开始行设置为0，保证每列数据不会错误。即，保证转换为key，value是 不会错位
short startCol = 0; // row.getFirstCellNum()：第0列无数据，第1列有数据，此时返回1
short endCol = (short)(row.getLastCellNum()-(short) 1); // n列，返回n。fixme： 【已修复】所以最大列数需减一
// 遍历指定范围的列
for (int x = startCol; x <= endCol; x++) {
Cell cell = row.getCell(x);
if(cell != null) {
boolean isMerge = isMergedRegion(sheet, row.getRowNum(), cell.getColumnIndex());
//判断是否具有合并单元格
if (isMerge) {// 合并单元格
Integer[] mergedRegionValueLocation = getMergedRegionValueLocation(row.getSheet(), row.getRowNum(), cell.getColumnIndex(), tar);
if(mergedRegionValueLocation != null){
return mergedRegionValueLocation;
}else{
continue;
}
} else {// 非合并单元格
String cellVal = String.valueOf(getCell(cell));
if(tar.equals(cellVal)){
Integer[] cellValueLocation = new Integer[]{row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex()};
return cellValueLocation;
}else{
continue;
}
}
}
}
}
}
// 返回null，表示该数据不存在
return null;
}
/**
* 根据内容获取该内容在该sheet中所有的位置<br>从第0行到0列到最后一行最后一列，依次遍历
* @param sheet 当前sheet
* @param tar 要查找的数据
* @author andus.top
* @date 2021/6/25 17:38
* @return java.util.List<java.lang.Integer[]><br> 1、返回null，表示该数据不存在
* <br> 2、数组数据依次为单元格的起始行, 结束行, 起始列, 结束列 的下标。数据相同时，表明有行或列单元格合并
**/
private List<Integer[]> getContentAllLocation(Sheet sheet, String tar){
if(sheet != null && tar != null && tar.length() > 0){
List<Integer[]> locationList = new ArrayList<>();
for (int r = 0, endRow = sheet.getLastRowNum(); r <= endRow; r++) {// n行返回n-1
Row row = sheet.getRow(r);
if(row == null){
// 必须执行到最后一行，遍历完excel中的数据
continue;
}
// fixme 【已修复】开始行设置为0，保证每列数据不会错误。即，保证转换为key，value是 不会错位
short startCol = 0; // row.getFirstCellNum()：第0列无数据，第1列有数据，此时返回1
short endCol = (short)(row.getLastCellNum()-(short) 1); // n列，返回n。fixme： 【已修复】所以最大列数需减一
// 遍历指定范围的列
for (int x = startCol; x <= endCol; x++) {
Cell cell = row.getCell(x);
if(cell != null) {
boolean isMerge = isMergedRegion(sheet, row.getRowNum(), cell.getColumnIndex());
//判断是否具有合并单元格
if (isMerge) {// 合并单元格
Integer[] mergedRegionValueLocation = getMergedRegionValueLocation(row.getSheet(), row.getRowNum(), cell.getColumnIndex(), tar);
if(mergedRegionValueLocation != null){
locationList.add(mergedRegionValueLocation);
}else{
continue;
}
} else {// 非合并单元格
String cellVal = String.valueOf(getCell(cell));
if(tar.equals(cellVal)){
Integer[] cellValueLocation = new Integer[]{row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex()};
locationList.add(cellValueLocation);
}else{
continue;
}
}
}
}
}
return locationList;
}
// 返回null，表示该数据不存在
return null;
}
/**
* 获取合并单元格的值<br>
* 即获取合并单元格第一个cell的值
* @param sheet 当前sheet
* @param row 当前行下标
* @param column 当前列下标
* @param tar 查询的值
* @author andus.top
* @date 2021/6/25 17:39
* @return java.lang.Integer[]该合并单元格的位置。结构为：[int startRow, int endRow, int startCol, int endCol] <br>
* 值不存在时返回null
**/
private Integer[] getMergedRegionValueLocation(Sheet sheet ,int row , int column, String tar){
// 获得一个 sheet 中合并单元格的数量
int sheetMergeCount = sheet.getNumMergedRegions();
// 遍历合并单元格
for(int i = 0 ; i < sheetMergeCount ; i++){
// 得出具体的合并单元格
CellRangeAddress ca = sheet.getMergedRegion(i);
// 得到合并单元格的起始行, 结束行, 起始列, 结束列
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
// 获取合并单元格第一个cell的值
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
String cellContent = String.valueOf(getCell(fCell));
if(tar.equals(cellContent)){
return new Integer[]{firstRow, lastRow, firstColumn, lastColumn};
}else{
continue;
}
}
}
}
return null ;
}
/**
* 获取某单元格的值。若是合并单元格，取合并单元格的值
* @param row 某行
* @param cell 某单元格
* @author andus.top
* @date 2021/6/25 17:32
* @return java.lang.Object
**/
private Object getCellContent(Row row, Cell cell){
if(row == null || cell == null){
return null;
}
boolean isMerge = isMergedRegion(row.getSheet(), row.getRowNum(), cell.getColumnIndex());
//判断是否具有合并单元格
if (isMerge) {
Object cellValue = getMergedRegionValue(row.getSheet(), row.getRowNum(), cell.getColumnIndex());
return cellValue;
} else {
Object cellValue = getCell(cell);
return cellValue;
}
}
/**
* 获取非合并单元格的值
* @param cell
* @author andus.top
* @date 2021/6/25 17:32
* @return java.lang.Object
**/
private Object getCell(Cell cell){
CellType cft = cell.getCellType();
switch (cft){
case BLANK:
return "";
case BOOLEAN:
return cell.getBooleanCellValue();
case ERROR:
return cell.getErrorCellValue();
case NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
return cell.getLocalDateTimeCellValue();
}else{
return cell.getNumericCellValue();
}
case FORMULA:
return cell.getCellFormula();
//return cell.getStringCellValue();
default:
return cell.getStringCellValue();
}
}
/**
* 获取合并单元格的值<br>
* 即获取合并单元格第一个cell的值
* @param sheet 当前sheet
* @param row 当前行下标
* @param column 当前列下标
* @author andus.top
* @date 2021/6/25 17:35
* @return java.lang.Object 该合并单元格的值
**/
private Object getMergedRegionValue(Sheet sheet ,int row , int column){
// 获得一个 sheet 中合并单元格的数量
int sheetMergeCount = sheet.getNumMergedRegions();
// 遍历合并单元格
for(int i = 0 ; i < sheetMergeCount ; i++){
// 得出具体的合并单元格
CellRangeAddress ca = sheet.getMergedRegion(i);
// 得到合并单元格的起始行, 结束行, 起始列, 结束列
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
// 获取合并单元格第一个cell的值
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCell(fCell) ;
}
}
}
return null ;
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @author andus.top
* @date 2021/6/25 17:34
* @return boolean true：是合并单元格
**/
private static boolean isMergedRegion(Sheet sheet, int row ,int column) {
// 得到一个sheet中有多少个合并单元格
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
// 得出具体的合并单元格
CellRangeAddress range = sheet.getMergedRegion(i);
// 得到合并单元格的起始行, 结束行, 起始列, 结束列
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
// 判断该单元格是否在合并单元格范围之内, 如果是, 则返回 true
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
/**
* 当前行是否包含合并行
* @param row
* @author andus.top
* @date 2021/6/25 17:41
* @return int 返回合并行的长度<br>不包含，返回=1
**/
private static int isContainMergeRow(Row row){
if(row != null) {
Sheet sheet = row.getSheet();
int rowNum = row.getRowNum();
for (int x = 0, s = sheet.getNumMergedRegions(); x < s; x++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(x); // 返回一共几个合并单元格
int mFirstRowNum = mergedRegion.getFirstRow(); // 合并单元格起始行
int mLastRowNum = mergedRegion.getLastRow();// 合并单元格结束行
int mFirstColNum = mergedRegion.getFirstColumn();// 合并单元格起始列
int mLastColNum = mergedRegion.getLastColumn();// 合并单元格结束列
if(rowNum >= mFirstRowNum && rowNum <= mLastRowNum){
return mLastRowNum - mFirstRowNum + 1;
}
}
}
return -1;
}
/**
* 将单元格设置为整数格式，并居中显示
* @param cell
* @author andus.top
* @date 2021/6/25 15:39
* @return
**/
private void setNumberCell(Cell cell){
// CellStyle cellStyle = workbook.createCellStyle();
CellStyle cellStyle = cell.getCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// cellStyle.setBorderBottom(BorderStyle.THIN); // 底部边框
// cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
// cellStyle.setBorderLeft(BorderStyle.THIN); // 左边边框
// cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
// cellStyle.setBorderRight(BorderStyle.THIN); // 右边边框
// cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边边框颜色
// cellStyle.setBorderTop(BorderStyle.THIN); // 上边边框
// cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边边框颜色
DataFormat dataFormat = workbook.createDataFormat();//创建格式化对象
cellStyle.setDataFormat(dataFormat.getFormat("0"));
cell.setCellStyle(cellStyle);// 单元格类型
}
/**
* 将单元格设置为保留两位小数格式，并居中显示
* @param cell
* @author andus.top
* @date 2021/6/25 15:40
* @return
**/
private void setDoubleCell(Cell cell){
// CellStyle cellStyle = workbook.createCellStyle();
CellStyle cellStyle = cell.getCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// cellStyle.setBorderBottom(BorderStyle.THIN); // 底部边框
// cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
// cellStyle.setBorderLeft(BorderStyle.THIN); // 左边边框
// cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
// cellStyle.setBorderRight(BorderStyle.THIN); // 右边边框
// cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边边框颜色
// cellStyle.setBorderTop(BorderStyle.THIN); // 上边边框
// cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边边框颜色
DataFormat dataFormat = workbook.createDataFormat();//创建格式化对象
cellStyle.setDataFormat(dataFormat.getFormat("0.00"));
cell.setCellStyle(cellStyle);// 单元格类型
}
/**
* 检验mark格式。不是marker返回null或者空集合
* @param cellContent 该值可能包含多个${}形式marker，或者1个${a.b}形式marker
* @author andus.top
* @date 2021/6/25 17:28
* @return java.util.List<java.lang.String[]>
**/
private List<String[]> checkMarker(String cellContent) {
if (StringUtils.isNotEmpty(cellContent)) {
List<String[]> list = new ArrayList<>();
int start = cellContent.indexOf("${");
int end = cellContent.indexOf("}");
while (start != -1 && end != -1){
String marker = cellContent.substring(start, end+1);
if(end + 1 <= cellContent.length()) {
cellContent = cellContent.substring(end + 1);
}
int point = marker.indexOf(".");
if(point != -1){// ${a.b}
String key = marker.substring(2, point);
String field = marker.substring(point+1, marker.length()-1);
if(StringUtils.isNotEmpty(key) && StringUtils.isNotEmpty(field)){
// key、value、全部的marker，方便直接替换内容
String[] strings = new String[]{key, field, marker};
list.add(strings);
// 检查marker
if(list.size() > 1){ // list.size 表示该cell 存在${rowkey.field}类型的marker 的数量
throw new RuntimeException("一个单元格只允许一个${rowkey.field}类型的marker：[ cellContent=" + cellContent +" ]");
}
}else{
throw new RuntimeException("marker标记错误:" + marker);
}
}else{ // ${a}
String key = null;
String field = marker.substring(2, marker.length()-1);
if(StringUtils.isNotEmpty(field)){
// key、value、全部的marker，方便直接替换内容
String[] strings = new String[]{key, field, marker};
list.add(strings);
}else{
throw new RuntimeException("marker标记错误:" + marker);
}
}
start = cellContent.indexOf("${");
end = cellContent.indexOf("}");
}
return list;
}
return null;
}
/**
* 复制行样式
* @param source 样式行
* @param target 目标行
* @param sourceRowLength source cell 跨行数
* @param sourceColLength source cell 跨行数
* @author andus.top
* @date 2021/6/25 17:41
* @return
**/
private void copyCellStyle(Sheet sheet, Cell source, Cell target, int sourceRowLength, int sourceColLength) {
CellStyle sourceCellStyle = source.getCellStyle();
if(sourceCellStyle != null){
target.setCellStyle(sourceCellStyle);
}
// 行或列偏移量大于1，说明是合并单元格。此时，每个单元格样式都复制
if(sourceRowLength > 1 || sourceColLength > 1){
sourceCellStyle.setAlignment(HorizontalAlignment.CENTER);
sourceCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
target.setCellStyle(sourceCellStyle);
try{
// 存在就无需添加
CellRangeAddress region = new CellRangeAddress(
target.getRowIndex(), target.getRowIndex()+sourceRowLength-1,
target.getColumnIndex(), target.getColumnIndex()+sourceColLength-1);
sheet.addMergedRegion(region);
}catch (Exception e){
e.printStackTrace();
}
}
}
/**
* 复制行样式
* @param source 样式行
* @param target 目标行
* @author andus.top
* @date 2021/6/25 17:42
* @return
**/
private static void copyRowStyle(Row source, Row target) {
CellStyle sourceRowStyle = source.getRowStyle();
// 行样式
if(sourceRowStyle != null){
target.setRowStyle(sourceRowStyle);
}
// 每列样式
for (int x = 0; x < source.getLastCellNum(); x++) { // getLastCellNum() n列，返回n。
Cell sourceCell = source.getCell(x);
CellStyle sourceCellStyle = sourceCell.getCellStyle();
Cell targetCell = target.getCell(x);
if(targetCell == null){
targetCell = target.createCell(x);
}
if(sourceCellStyle != null){
targetCell.setCellStyle(sourceCellStyle);
}
}
target.setHeight(source.getHeight());
// 列合并单元格样式
copyRowMerge(source, target);
}
/**
* 复制列合并单元格。不支持跨行合并单元格样式拷贝
* @param source 样式行
* @param target 目标行
* @author andus.top
* @date 2021/6/25 15:24
* @return
**/
private static void copyRowMerge(Row source, Row target) {
Sheet sheet = source.getSheet();
int targetRowNum = target.getRowNum();
int sourceRowNum = source.getRowNum();
for (int x = 0, s = sheet.getNumMergedRegions(); x < s; x++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(x); // 返回一共几个合并单元格
int mFirstRowNum = mergedRegion.getFirstRow(); // 合并单元格起始行
int mLastRowNum = mergedRegion.getLastRow();// 合并单元格结束行
if(targetRowNum >= mFirstRowNum && targetRowNum <= mLastRowNum && mFirstRowNum < mLastRowNum){
// fixme：可根据业务情况单独处理合并单元格问题
throw new RuntimeException("本方法不能有跨行的合并单元格[" + "firstRow: " + mFirstRowNum + ", lastRow: " + mLastRowNum + "]");
}
if(sourceRowNum >= mFirstRowNum && sourceRowNum <= mLastRowNum){
if(mFirstRowNum < mLastRowNum){
// fixme：可根据业务情况单独处理合并单元格问题
throw new RuntimeException("本方法不能有跨行的合并单元格[" + "firstRow: " + mFirstRowNum + ", lastRow: " + mLastRowNum + "]");
}
int mFirstColNum = mergedRegion.getFirstColumn();// 合并单元格起始列
int mLastColNum = mergedRegion.getLastColumn();// 合并单元格结束列
// fixme：可根据业务情况单独处理合并单元格问题
sheet.addMergedRegion(new CellRangeAddress(targetRowNum, targetRowNum, mFirstColNum, mLastColNum));
}
}
}
/**
* java bean 转换为map
* @param o java bean
* @author andus.top
* @date 2021/6/25 17:40
* @return map对象。key：属性名称，value：属性的值
**/
private static Map<String, Object> beanToMap(Object o) throws IllegalAccessException {
if(o == null){
return new HashMap<>();
}
// Map 对象直接转换
if(o instanceof Map){
Map<String, Object> map = new HashMap<>();
((Map<?, ?>)o).forEach((k, v) -> map.put(String.valueOf(k), v));
return map;
}
// bean 对象通过放射获取属性名称与值，封装为map
HashMap<String, Object> map = new HashMap<>();
Field[] fields = o.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
map.put(field.getName(), field.get(o));
}
return map;
}
}
扫一扫
1646
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
