一、使用场景
由于系统环境原因，无法使用EasyExcel的填充功能，只能使用POI处理Excel。业务给到一个需求，要求按照模板导出数据至Excel的指定数据页签，用户会在其他页签引用该页签的数据进行各种分析计算。如下所示，有”data“、”分析“两个页签，需要将基础数据载入第一个页签，并且刷新第二个页签的公式。
二、实现
2.1、POI依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
<scope>compile</scope>
</dependency>
2.2、FileUtils
实现文件工具类，主要是生成文件名。
import java.io.*;
import java.util.HashSet;
import java.util.Objects;
import java.util.Set;
public class FileUtils {
private static final String COPY_FILE_SUFFIX = "_副本";
/**
* 将输入流写入文件
* @param in 输入流
* @param path 输出文件的路径
* @param fileName 输出文件的名称
*
* */
public static void writeFile(InputStream in ,String path ,String fileName) throws FileNotFoundException {
FileOutputStream outputStream = new FileOutputStream("");
}
/**
* 给定一个目录和文件，若该目录存在名称相同的文件，则返回一个可用的副本文件名，否则直接返回输入的文件名。
* @param path 文件的目录
* @param fileName 文件名
* @return 可用的文件名
* */
public static String getAvailableName(String path ,String fileName){
File filePath = new File(path);
String suffix = fileName.substring(fileName.lastIndexOf("."));
File[] files = filePath.listFiles( f -> f.getName().endsWith(suffix) );
if ( Objects.isNull(files) ) return fileName;
Set<String> fileNameSet = new HashSet<>();
for (File file: files ) {
fileNameSet.add(file.getName().substring(0 , file.getName().lastIndexOf(".")));
}
boolean isExist = true;
int count = 1;
String tempFileName = fileName.substring(0,fileName.lastIndexOf("."));
String tempFileName1 = tempFileName;
while(isExist){
if (fileNameSet.contains(tempFileName)){
tempFileName = tempFileName1 + COPY_FILE_SUFFIX + count++;
}else{
isExist = false;
}
}
return tempFileName + suffix;
}
/**
* 给定一个文件的完整路径，若该文件已经存在，则返回一个可用的副本文件名，否则直接返回输入的文件名。
* @param fullPath 文件的完整路径
* @return 可用的文件名
* */
public static String getAvailableName( String fullPath ){
File file = new File(fullPath);
if ( !file.isFile() ) throw new RuntimeException( String.format("not a file [%s].",fullPath));
return getAvailableName( file.getParent() ,file.getName() );
}
/**
* 给定一个文件的完整路径，若该文件已经存在，则返回一个完整可用的副本文件名，否则直接返回输入的文件名。
* @param fullPath 文件的完整路径
* @return 完整可用的文件名
* */
public static String getAvailableFullName( String fullPath ){
File file = new File(fullPath);
if ( !file.isFile() ) throw new RuntimeException( String.format("not a file [%s].",fullPath));
return file.getParent() + File.separator + getAvailableName(fullPath);
}
}
2.3、ExcelFillUtils
实现Excel填充工具类，用来填充Excel模板，并生成填充好的副本文件。
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.regex.Pattern;
public class ExcelFillUtils {
// public static final String FILL_EXPRESSION_REGEX = "\\{\\.\\w+\\}";
private static final String FILL_EXPRESSION_REGEX = "\\{\\.[\\p{L}\\p{M}\\S]+\\}";
/**
* 给定模板，指定某个页签，将数据填充到模板中的指定页签，并在模板所在目录生成新的副本文件。
* @param template 模板文件地址
* @param sheetName 页签名称
* @param data 待填充的数据,数据格式如下
* [
* {"colName1":v1 ,"colName2":v2...},
* {"colName1":v1 ,"colName2":v2...}
* ,...
* ]
* @return 新生成的副本文件的地址
*
* */
public static String fillOneSheet(String template ,String sheetName , List<Map<String,Object>> data) {
return fillOneSheet(template ,FileUtils.getAvailableFullName(template) ,sheetName ,data);
}
/**
* 给定模板，指定某个页签，将数据填充到模板中的指定页签，并将数据导入到指定文件上。
* @param template 模板文件地址
* @param outputFile 新生成的文件的地址
* @param sheetName 页签名称
* @param data 待填充的数据,数据格式如下
* [
* {"colName1":v1 ,"colName2":v2...},
* {"colName1":v1 ,"colName2":v2...}
* ,...
* ]
* @return 新生成的副本文件的地址
*
* */
public static String fillOneSheet(String template , String outputFile,String sheetName , List<Map<String,Object>> data){
try (Workbook workbook = new XSSFWorkbook(new FileInputStream(template))) {
fill(workbook,sheetName,data);//填充数据
refreshFormula(workbook);//刷新公式
try (FileOutputStream outputStream = new FileOutputStream(outputFile)) {
workbook.write(outputStream);
}catch (Exception e){
throw new RuntimeException(e);
}
}catch (IOException e){
throw new RuntimeException(e);
}
return outputFile;
}
/**
* 给定模板,将数据填充到模板中的多个页签，并在模板所在目录生成新的副本文件。
* @param template 模板文件地址
* @param datas 待填充的数据集,数据格式如下
* {
* "SheetName1":[
* {"colName1":v1 ,"colName2":v2...},
* {"colName1":v1 ,"colName2":v2...}
* ,...
* ],
* "SheetName2":[
* {"colName1":v1 ,"colName2":v2...}
* {"colName1":v1 ,"colName2":v2...}
* ,...
* ],
* ...
* }
*
* */
public static String fillMultipleSheet(String template ,Map<String,List<Map<String,Object>>> datas) {
return fillMultipleSheet(template ,FileUtils.getAvailableFullName(template) ,datas);
}
/**
* 给定模板,将数据填充到模板中的多个页签，并将数据导入到指定文件上。
* @param template 模板文件地址
* @param datas 待填充的数据集,数据格式如下
* {
* "SheetName1":[
* {"colName1":v1 ,"colName2":v2...},
* {"colName1":v1 ,"colName2":v2...}
* ,...
* ],
* "SheetName2":[
* {"colName1":v1 ,"colName2":v2...}
* {"colName1":v1 ,"colName2":v2...}
* ,...
* ],
* ...
* }
*
* */
public static String fillMultipleSheet(String template ,String outputFile ,Map<String,List<Map<String,Object>>> datas){
try (Workbook workbook = new XSSFWorkbook(new FileInputStream(template))) {
datas.forEach( (sheetName ,data)-> fill(workbook,sheetName,data) );//填充数据
refreshFormula(workbook);//刷新公式
try (FileOutputStream outputStream = new FileOutputStream(outputFile)) {
workbook.write(outputStream);
}catch (Exception e){
throw new RuntimeException(e);
}
}catch (IOException e){
throw new RuntimeException(e);
}
return outputFile;
}
private static Workbook fill(Workbook workbook ,String sheetName ,List<Map<String,Object>> data) {
Sheet sheet = workbook.getSheet(sheetName);
if (sheet == null) {
throw new RuntimeException(String.format("sheet [%s] does not exist.",sheetName));
}
//找到所有的表达式单元格
Map<String,Cell> expressionCellMap = new HashMap<>();
for( int i = 0 ;i < sheet.getPhysicalNumberOfRows() ;i++){
Row row = sheet.getRow(i);
for( int j = 0 ;j< row.getPhysicalNumberOfCells() ;j++){
Cell cell = row.getCell(j);
if( !Objects.isNull(cell) && isFillExpression(cell.getStringCellValue()) ){//判断该单元格是否是填充公式
expressionCellMap.put(getColNameFromEx(cell.getStringCellValue()),cell);
}
}
}
//填充数据
for(int i = 0 ;i< data.size() ;i++){
Map<String,Object> dataRow = data.get(i);
for (Map.Entry<String,Object> entry : dataRow.entrySet()){
String colName = entry.getKey();
Object value = entry.getValue();
if(expressionCellMap.containsKey(colName)){
Cell cell = expressionCellMap.get(colName);//公式所在的单元格
int rowID = cell.getRowIndex() + i;
int colId = cell.getColumnIndex();
Row fillRow = sheet.getRow(rowID);
fillRow = Objects.isNull(fillRow) ? sheet.createRow(rowID) : fillRow;
Cell fillCell = fillRow.getCell(colId);
//创建的新单元格需要复制公式单元格的格式
fillCell = Objects.isNull(fillCell) ? CellUtil.createCell(fillRow,colId,"", cell.getCellStyle()) : fillCell;
if ( value instanceof String){
fillCell.setCellValue( String.valueOf(value) );
}else if( value instanceof Number ){
fillCell.setCellValue( ((Number)value).doubleValue() );
}else{
throw new RuntimeException(String.format("Unsupported data type [%s].",value.getClass().toString()));
}
}
}
}
return workbook;
}
private static void refreshFormula(Workbook workbook){
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateAll();
}
private static boolean isFillExpression(String ex){
if (ex.isBlank()) return false;
return Pattern.matches(FILL_EXPRESSION_REGEX ,ex);
}
private static String getColNameFromEx(String ex){
if (!isFillExpression(ex)) throw new RuntimeException("Illegal expression " + ex );
return ex.substring(2,ex.length() - 1);
}
}
2.4、测试
@Test
public void fillOneTest_1(){
// 定义要填充数据的Excel模板文件的路径
String template = "C:\\Users\\Administrator\\Desktop\\test.xlsx";
// 定义要填充数据的工作表名称
String sheetName = "data";
// 创建一个列表，用来存储要填充到Excel中的数据
List<Map<String,Object>> list = new ArrayList<>();
// 向列表中添加数据
list.add( Map.of("name","zou" ,"age" ,18) );
list.add( Map.of("name","li" ,"age" ,28) );
list.add( Map.of("name","wang" ,"age" ,15) );
list.add( Map.of("name","quan" ,"age" ,19) );
list.add( Map.of("name","zhao" ,"age" ,98) );
// 调用ExcelFillUtils类的fillOneSheet方法，实现对Excel模板的数据填充
ExcelFillUtils.fillOneSheet(template ,sheetName ,list);
}
扫一扫
1436
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
