Question

java处理excel

需求是将已经合并的单元格拆分成若干行，然后将数据填充到各行。

Answer 1

用java处理Excel或Word文档，可以用Apache的POI技术，POI功能强大，配合java程序处理，完全能实现你的需求

Answer 2

嗯 以下代码看看：
import com.pingan.assetGradeAndCalculate.po.NrBankDatePublicPo;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.ParseException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.pingan.srpt.util.ReportUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
public static void addBankDataPublic(String fileName){
try {
Workbook workbook = new XSSFWorkbook(new FileInputStream(fileName)); //xlsx
Sheet sheet = workbook .getSheet("2020.12");
int rows = sheet.getLastRowNum() + 1;//获取最大行数
int columnNum = sheet.getRow(3).getPhysicalNumberOfCells(); //获取最大列数
System.out.println("总行数:" + rows +" 总列数:"+ columnNum);

//业务线数据处理
for (int i = 0; i < rows; i++) { //行数
NrBankDatePublicPo bd= new NrBankDatePublicPo();
Row row = sheet.getRow(i);//指定行
if (row == null || i < 3) {//略过空行和前3标题行
continue;
}
String c=cellValue(row.getCell(2));
if(StringUtils.isNotBlank(c) && c.trim().length()>6){
if(c.contains("假")){
System.out.println(i+"行："+c);
}else{
Pattern p = Pattern.compile("\\s*|\t|\r|\n");
Matcher m = p.matcher(c);
String dest = m.replaceAll("");
String result="18:00-"+dest.substring(5,10);
System.out.println(i+"行：");
if(null==row.getCell(3)){
row.createCell(3).setCellValue("");
}
row.getCell(3).setCellValue(result);
}
}
}
FileOutputStream out = new FileOutputStream(fileName);
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//String值处理
public static String cellValue(Cell cell){
String cellValue="";
if(null!=cell){
cellValue=cell.getStringCellValue();
if("na".equals(cellValue)){
cellValue=null;
}
}
return cellValue;
}
addBankDataPublic("D:\\a.xlsx");