poi 解析百M Excel ,bs架构 在线概算导入

本文分享了使用Apache POI处理大型Excel文件的经验,包括解决内存溢出问题、检查错误公式、批量导入指定工作表等内容。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

今天碰上有人问POI的性能问题 哎,当初哥们做导入概算数据为POI 头疼了N久,终归功夫不负苦心人,最后解决了如下问题
1 excel 概算文件超大 近 100m (我测试用了个200m的 都不会 OOM 哈哈)
2 要检查excel中的 #REF 等错误计算公式,因为要入库,同时需要检查 数据的合法性 如 Number类型对象数字等
3 因为 几万条数据 太过繁琐,所以需要 一次性报告给用户 所有的错误,并且定位错误行列,以供用户差错
4 允许用户对单独sheet的批量导入 比如 5个sheet 用户高兴倒几个就倒几个 高兴倒哪个就倒哪个
5 因是web架构,解析时提供 进度条提示 否则180m 几乎5分钟客户会crazy

哎 对此解决问题如下 最下提供代码仅供参考
1 以前用poi 通用的user model 解析2m以下的还凑合对大数据 直接OOM,经官方查询发现 有个Event modle 并供有demo 小试一下下 ,very goode 详情参考[url]http://poi.apache.org/spreadsheet/how-to.html#event_api[/url]

2 没办法 只能对每个cell进行验证 不过大家肯定发愁 我如何知道 excel的列对应 db的哪个column 啊?呵呵 对于这点 哥们我提出了规则 给用户个 匹配管理页面,让用户自己去定义 excel的哪个sheet 对应哪个表 哪个列对用哪个column 当然 在此肯定少不了用反射,哈哈我的最爱,自己 db的 type length 等 以及 entity 的property 这些对应是麻烦点 不过 可以连水平哦
3 报错给用户 这个肯定不能一碰上错 就alert,这样 不友好,也不现实,最好的就是 把所有的错误给用户生成一个文件,可供在线打开以及下载,客户对照着文件改excel 哈哈 要是你愿意的话 就把excel 的cell全称红色也行,不过 那就没办法提示用户 这行 的错误信息, 比如 “xxx表 1100行 20列 xx错误,应xxx ” 那么 在这里就只能用我们的异常机制了,不合法的抛出异常,自己捕获处理,写文件,render view 都可
4 既然用户想选择 那肯定 要给用户提供一个 复选框吧,当然如果 sheet个数不定 那就只能靠ajax 去捞 复选框的个数了,哈哈比较麻烦,但是道理很简单, 选哪个解析哪个啊

5 幸好前台用的ext 找个 process tip 很容易,不过关键是 如何切分任务来定制进度条的进度,刚开始想到了监听进程执行百分比,不过jdk不知道提供这个接口否,问 n多高人,都不知道,其实想想一个线程执行的百分比确实难以预测啊,毕竟cpu 的轮回,况且时关系,我就用了简单的自定制任务比,整个任务量100 完成某个阶段是多少 然后在不同的过程中 ++ 呵呵 任务量放在 http session 里,前台的 进度条通过定时器触发ajax 呵呵


呵呵 下面是我写的一些 解析excel的代码 很乱啊 小心点砸

package com.hollysys.ipmip.util;

import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.CellValueRecordInterface;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RowRecord;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.EOFRecord;

import com.hollysys.ipmip.common.CommonDeclare;
import com.hollysys.ipmip.entity.business.estimateimport.EstimateAttributes;
import com.hollysys.ipmip.entity.business.estimateimport.EstimateCalc;
import com.hollysys.ipmip.exceptions.ImportEstimateException;
import com.hollysys.ipmip.service.business.estimateimport.EstimateImportService;

public class EventModelUtil implements HSSFListener {
private SSTRecord sstrec;
//存放所选sheet
private List<Integer> sheetNums = new ArrayList<Integer>();
//当前的sheet序号
private int sheetNum = 0;
//保存excel的实际sheet个数
private int count = 0;
//存放sheet的map
private Map<Integer, EstimateCalc> sheetMap = null;
//判断首行
private int firstRowNum = 0;
//最终存放首行
private int firstRowFinal = 0;
// 存放导入配置文件
private Map<Integer, Map<Integer, String>> typeRelationMap = null;
// 存放表的详细信息
private Map<Integer, Map<String, EstimateAttributes>> tableAttribMap = null;
// 存放异常信息
private Exception exception=null;
private EstimateImportService estimateImportService;
private HttpSession session;
private Long userId;
private OutputStreamWriter fw=null;
private int countLine=1;
private Random random=new Random();
//存放保存数据库间断个数
// private int bound=0;
private Logger logger=Logger.getLogger(EventModelUtil.class);
/**
* This method listens for incoming records and handles them as required.
*
* @param record
* The record that was found while reading.
*/
public EventModelUtil() {

}

public EventModelUtil(List<Integer> sheetNums,
Map<Integer, Map<Integer, String>> typeRelationMap,
Map<Integer, Map<String, EstimateAttributes>> tableAttribMap,
Exception e, EstimateImportService estimateImportService,
HttpSession session, Long userId,OutputStreamWriter fw) {
this.sheetNums = sheetNums;
this.typeRelationMap = typeRelationMap;
this.tableAttribMap = tableAttribMap;
this.exception = e;
this.estimateImportService = estimateImportService;
this.session = session;
this.userId = userId;
this.fw=fw;
}

@SuppressWarnings("static-access")
public void processRecord(Record record) {
java.text.DecimalFormat formatter2 = new java.text.DecimalFormat("#.00");
if (record.getSid() == BOFRecord.sid) {
BOFRecord bof = (BOFRecord) record;
if (bof.getType() == bof.TYPE_WORKBOOK) {
String str="3,4,5,6";
changeSession(session, CommonDeclare.ESTIMATE_PROCESS_CONST, Integer.valueOf(createRandom(str, false)), CommonDeclare.ESTIMATE_UPFLAG_ADD_CONST);
logger.info("新workBook" + bof.getRecordSize());
} else if (bof.getType() == bof.TYPE_WORKSHEET) {
logger.info("新的sheet:" + sheetNum);
bof.getRecordSize();
isRightSheetNum();
sheetMap = new LinkedHashMap<Integer, EstimateCalc>();
sheetNum++;
}
}
try {
if (record.getSid() == EOFRecord.sid) {
if (sheetNum > 0) {
//取出解析的最后一个sheet
int lastSheetNum=sheetNums.get(sheetNums.size()-1);
int count=sheetNum*Integer.valueOf(createRandom("19,20,21", false))-10;
if(sheetNum==lastSheetNum){
count=Integer.valueOf(createRandom("90,91,92", false));
}
changeSession(session, CommonDeclare.ESTIMATE_PROCESS_CONST, count, CommonDeclare.ESTIMATE_UPFLAG_ADD_CONST);
logger.info("当前sheet首行:" + firstRowFinal);
logger.info(sheetNum - 1 + "sheet解析结束");
EOFRecord eof=(EOFRecord)record;
eof.getRecordSize();
if(sheetNums.contains(sheetNum - 1)&&sheetMap!=null){
if(firstRowFinal<=0){
String str="在"+CommonDeclare.tableName.get(sheetNum-1)+"中配置有问题,请重新配置";
logger.error(str);
throw new RuntimeException(str);
}else{
if(session.getAttribute("importExceptionMsg")==null||!session.getAttribute("importExceptionMsg").equals("1")){
changeSession(session, CommonDeclare.ESTIMATE_PROCESS_CONST, Integer.valueOf(createRandom("1,2,3", false)), CommonDeclare.ESTIMATE_UPFLAG_UPDATE_CONST);
estimateImportService.saveCurrentSheetToTempTable(sheetMap,sheetNum-1, session, userId);
}
sheetMap.clear();
}
}
firstRowFinal=0;
countLine=1;
}
}
if (record.getSid() == BoundSheetRecord.sid) {
//获得sheet个数
count++;
}
if (record.getSid() == SSTRecord.sid) {
sstrec = (SSTRecord) record;
}

if (sheetNums.contains(sheetNum - 1)) {
getSwitchRecord(record, formatter2, sheetNum - 1,
typeRelationMap.get(sheetNum - 1), tableAttribMap
.get(sheetNum - 1), CommonDeclare.tableName
.get(sheetNum - 1));
}
}catch(RuntimeException e){
throw e;
}
catch (Exception e) {
e.printStackTrace();
setException(e);
session.setAttribute("importExceptionMsg", "1");
try {
if(countLine==1){
String str="***********************"+CommonDeclare.tableName.get(sheetNum - 1)+"*******************************"+System.getProperty("line.separator");
fw.write(str);
logger.error(str);
}
String strDetial=countLine+":"+e.getMessage()+System.getProperty("line.separator");
fw.write(strDetial);
logger.error(strDetial);
countLine++;
fw.flush();
} catch (Exception e1) {
logger.error(e1.getMessage());
e1.printStackTrace();
}
}
}
private void getSwitchRecord(Record record,
java.text.DecimalFormat formatter2, int sheetNum,
Map<Integer, String> typeRelation,
Map<String, EstimateAttributes> tableAttrib, String tablename)
throws Exception {
EstimateCalc calc = null;
try {
switch (record.getSid()) {
case RowRecord.sid:
RowRecord rowrec = (RowRecord) record;
int exclude=(sheetNum+1)*20;
if(exclude>=100) exclude=99;
changeSession(session, CommonDeclare.ESTIMATE_PROCESS_CONST, Integer.valueOf(createRandom("1,2", false)), CommonDeclare.ESTIMATE_UPFLAG_UPDATE_CONST,exclude);
calc = (EstimateCalc) CommonDeclare.tempEstimateMClass.get(
CommonDeclare.typeBrief.get(sheetNum)).newInstance();
sheetMap.put(rowrec.getRowNumber(), calc);
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
Object obj = formatter2.format(numrec.getValue());
//当为数字的时候判断首行
if (numrec.getValue() == (numrec.getColumn() + 1.00)) {
firstRowNum++;
}
if (firstRowNum == CommonDeclare.ESTIMATE_COLUMN_CONST) {
firstRowFinal = numrec.getRow();
}
//第一列为序号 统一数字的格式
if(firstRowFinal>0&&numrec.getColumn()==0){
java.text.DecimalFormat formatter = new java.text.DecimalFormat("#");
obj=formatter.format(numrec.getValue());
}
EstimateAttributes attrib=getAttrib(typeRelation, tableAttrib,(CellValueRecordInterface) numrec);
//如果是poi读出是数字而实际上不是数字的就不做格式转化
if(attrib!=null&&!attrib.getData_type().trim().equals("NUMBER")){
obj=numrec.getValue();
if(obj.toString().endsWith(".0")){
obj=obj.toString().substring(0,obj.toString().indexOf("."));
}
}
calc = sheetMap.get(numrec.getRow());
lessColumnLength(obj, calc, tablename,
(CellValueRecordInterface) numrec,attrib,getCellTitle(typeRelation, (CellValueRecordInterface) numrec));
break;
case LabelSSTRecord.sid:
LabelSSTRecord lrec = (LabelSSTRecord) record;
obj = sstrec.getString(lrec.getSSTIndex());
if(obj!=null&&firstRowFinal==0&&isNumber(obj.toString().trim())){
if (Long.valueOf(obj.toString()) == (lrec.getColumn() + 1.0)) {
firstRowNum++;
}
if (firstRowNum == CommonDeclare.ESTIMATE_COLUMN_CONST) {
firstRowFinal = lrec.getRow();
}
}else{
firstRowNum = 0;
}
calc = sheetMap.get(lrec.getRow());
isNumberColumn(obj,tablename, (CellValueRecordInterface) lrec,
getAttrib(typeRelation, tableAttrib,
(CellValueRecordInterface) lrec),getCellTitle(typeRelation, (CellValueRecordInterface) lrec));
lessColumnLength(obj, calc, tablename,
(CellValueRecordInterface) lrec, getAttrib(
typeRelation, tableAttrib,
(CellValueRecordInterface) lrec),getCellTitle(typeRelation, (CellValueRecordInterface) lrec));

break;
case BlankRecord.sid:
firstRowNum = 0;
break;
case FormulaRecord.sid:
FormulaRecord furd = (FormulaRecord) record;
firstRowNum = 0;
Double b = furd.getValue();
Object tb = formatter2.format(b);
calc=sheetMap.get(furd.getRow());
lessColumnLength(tb, calc, tablename,
(CellValueRecordInterface) furd, getAttrib(
typeRelation, tableAttrib,
(CellValueRecordInterface) furd),getCellTitle(typeRelation, (CellValueRecordInterface) furd));
break;
default:
firstRowNum = 0;
}
} catch (ImportEstimateException e){
logger.error(e.getMessage());
e.printStackTrace();
throw e;
}
}

/**
* 获得配置文件
*/
public EstimateAttributes getAttrib(Map<Integer, String> typeRelation,
Map<String, EstimateAttributes> tableAttrib,
CellValueRecordInterface record) {
String str=typeRelation.get(record.getColumn() + 1);
if(str!=null){
return tableAttrib.get(str.substring(0,str.indexOf(",")));
}else{
return null;
}
}

/**
* 获得cell 的标题
*/
public String getCellTitle(Map<Integer, String> typeRelation,CellValueRecordInterface record){
String str=typeRelation.get(record.getColumn() + 1);
if(str!=null){
return str.substring(str.indexOf(",")+1);
}else{
return null;
}
}

/**
* 判断cell里内容小于数据库字段的值
*/
public void lessColumnLength(Object obj, EstimateCalc calc,
String tablename, CellValueRecordInterface record,
EstimateAttributes attrib,String cellTitle) throws Exception {
if(firstRowFinal>0&&attrib!=null){
int length = 0;
Pattern p = Pattern.compile("^[\u0080-\u07ff\u0800-\uffff]+$");
if (obj != null && !obj.equals("")) {
for (int k = 0; k < obj.toString().trim().length(); k++) {
char c = obj.toString().trim().charAt(k);
Matcher m = p.matcher(c + "");
if (m.find()) {
length += 2;
} else {
length++;
}
}
}
if (length <= attrib.getData_length()) {
if(obj!=null&&!obj.toString().trim().equals("")){
if(session.getAttribute("importExceptionMsg")==null||!session.getAttribute("importExceptionMsg").equals("1")){
org.apache.commons.beanutils.BeanUtils.copyProperty(calc,
getBeanProperyNameByTableField(attrib.getColumn_name()
.trim()), obj);
}
}
} else {
String str="在【" +tablename + " "+cellTitle+"】中【"
+ (record.getRow() + 1) + "行" + (record.getColumn() + 1)
+ "列】内容为【"+obj+"】,字符长度【"+length+"】,应填写小于【" + attrib.getData_length() + "】位的内容";
logger.error(str);
throw new ImportEstimateException(str);
}
}
}

/**
* 应填写数字的列出现非数字
*/
public void isNumberColumn(Object obj,String tablename,
CellValueRecordInterface record, EstimateAttributes attrib,String cellTitle)
throws Exception {
if(firstRowFinal>0&&attrib!=null){
if(obj!=null){
//将全角的转化成半角的 否则无法trim
obj=obj.toString().replaceAll(" ", " ");
obj = obj.toString().trim();
if ("NUMBER".equals(attrib.getData_type().trim())&&!obj.toString().equals("")&&!isNumber(obj.toString())) {
throw new ImportEstimateException("在【" + tablename + " "+cellTitle+"】中【"
+ (record.getRow()+1) + "行" + (record.getColumn()+1) + "列】内容为【"+obj+"】,应填写数字");
}
}
}
}

/**
* 判断是否为5个sheet
*/
public void isRightSheetNum(){
if (count > 5) {
String str="导入文件有多余的sheet或者存在隐藏sheet,请检查多出的 " + (count - 5)
+ "个sheet";
logger.error(str);
throw new RuntimeException(str);
}
if (count < 5) {
String str="导入文件格式不符合 矿、土、安、设备、其他 顺序格式, 五个sheet依次排序格式";
logger.error(str);
throw new RuntimeException(str);
}
}

/**
* get Bean ProperyName By Table Field.
*
* @param fieldName
*/
private String getBeanProperyNameByTableField(String fieldName) {
fieldName = fieldName.toLowerCase();
while (fieldName.indexOf("_") > 0) {
int pos = fieldName.indexOf("_");
fieldName = fieldName.substring(0, pos)
+ fieldName.substring(pos + 1, pos + 2).toUpperCase()
+ fieldName.substring(pos + 2);
}
return fieldName;
}
/**
* 判断是否为数字
*/
public boolean isNumber(String str){
//处理全角空格
str=str.replaceAll(" ", " ");
return Pattern.matches("^\\d+$|^\\d+\\.\\d+$", str.trim());
}

public void setException(Exception exception) {
this.exception = exception;
}

/**
* 获得session 切改变session
* @param HttpSession session 当前用户session
* @param String attribute session的key
* @param Object changeValue session 的value
* @param String flag 1:新增 纯赋值 2:修改 在原来的基础上加 3:删除
* @return
*/
public static void changeSession(HttpSession session,String attribute,Object changeValue,String flag,int...exclude){
if(session!=null&&attribute!=null&&!"".equals(attribute)&&!"".equals(flag)){

if(CommonDeclare.ESTIMATE_UPFLAG_ADD_CONST.equals(flag)){
session.setAttribute(attribute, changeValue);
}else if(CommonDeclare.ESTIMATE_UPFLAG_UPDATE_CONST.equals(flag)){
if(session.getAttribute(attribute)!=null){
Object obj=session.getAttribute(attribute);
if(changeValue instanceof Integer){
if(exclude!=null&&exclude.length>0){
int i=0;
for(int n:exclude){
i=n;
}
if((Integer)obj>=i){
session.setAttribute(attribute, i);
}else{
session.setAttribute(attribute, (Integer)obj+(Integer)changeValue);
}
}else{
session.setAttribute(attribute, (Integer)obj+(Integer)changeValue);
}
}else if(changeValue instanceof String){
session.setAttribute(attribute, (String)obj+(String)changeValue);
}
}
}else if(CommonDeclare.ESTIMATE_UPFLAG_DELETE_CONST.equals(flag)){
if(session.getAttribute(attribute)!=null){
session.removeAttribute(attribute);
}
}
}
}

/**
* 根据要求随机生成随机数(字符)
* @param String str="1,2,3,...."or str="1234567" 字符串 如果是数组格式用逗号隔开
* @param boolean flag 是否把结果连接成串 true 连接 false 否
* @param int ... n 可选参数 如果有值则按照要求截取 否则按照
*/
public static String createRandom(String str , boolean flag,int... n){
String result="";
if(str!=null&&!"".equals(str)){
String[] array=str.split((str.indexOf(",")>-1)?",":"");
Random random=new Random();
int i=0;
if(n!=null&&n.length>0){
for(int j :n){
i=j;
}
}else{
i=array.length-1;
}
result=array[random.nextInt(i)];
if(flag){
StringBuilder builder=new StringBuilder();
for(int m=0;m<i;m++){
builder.append(result=array[random.nextInt(i)]);
}
result=builder.toString();
}else{
result=array[random.nextInt(i)];
}
}
return result;
}
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值