推荐:
更加规范和完整的工具:
码云-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;
}
}