POI、SAX读取Excel大文件

最新推荐文章于 2024-04-15 19:09:04 发布

ly5156

最新推荐文章于 2024-04-15 19:09:04 发布

阅读量1.4k

点赞数

分类专栏： java 文章标签： excel null upload attributes date bean

java 专栏收录该内容

179 篇文章 0 订阅

订阅专栏

poi 读取文件分为两种模式：用户模型、事件用户模型，第一种方案是以DOM方式读取Excel，好处是读取操作方便，不足的是一次性将文件加载到内存；第二种方案是SAX读取Excel，好处是内存占用小，因为每次只读取陪份数据，缺点是读取方件较不方便。

涉及的功能类： excel错误信息记录类、读取excel基类、读取excel公用类、示例类

测试结果（读取、存库）：

16万 8个字段 80s 全英文
10万 15个字段 120s 大部份中文
jvm要设置成-Xms512m -Xmx1024m 否则会很影响性能

excel错误信息记录类

view plain

/***********************************************************************
Copyright (c) 2007, AgileSC,Inc.China
All rights reserved.
************************************************************************/
package com.asc.console.dao.impl;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
/**
* 验证上传文件 .
* @author yangzelai
* @version 版本信息创建时间 Jan 15, 2011 2:36:37 PM
*/
public class ValidateFileUpload<T> {
public static String VALIDATE_NULL = "不能为空";
public static String VALIDATE_REPEATED = "忽略文件中重复的";
public static String VALIDATE_INVALID = "不合法";
public static String VALIDATE_EXIST = "已存在";
public static String VALIDATE_NOT_EXIST = "不存在";
//验证消息集合
private List<ValidateInfo> validateInfo = new ArrayList<ValidateInfo>();
//验证的字段
public Map<String,Object> map = new HashMap<String,Object>();
//上传的内容
List<T> uploadList = new LinkedList<T>();
//总记录数
public int total;
/*******************************/
public void addResult(String msg,Integer rowNumber,String value){
ValidateInfo result = null;
// 限制错误信息数量
if(uploadList.size() < 1000){
result = new ValidateInfo(msg,rowNumber,value);
}else if(uploadList.size() == 1000){
result = new ValidateInfo("错误信息已达到上限1000条,请检查数据格式!",rowNumber,value);
}else{
return;
}
this.validateInfo.add(result);
}
public void addResult(String msg,Integer rowNumber,String value,Integer valid){
ValidateInfo result = new ValidateInfo(msg,rowNumber,value,valid);
this.validateInfo.add(result);
}
public List<ValidateInfo> getValidateInfo() {
return this.validateInfo;
}
public void setValidateInfo(List<ValidateInfo> validateInfo) {
this.validateInfo = validateInfo;
}
public Map<String, Object> getMap() {
return this.map;
}
public void setMap(Map<String, Object> map) {
this.map = map;
}
public List<T> getUploadList() {
return this.uploadList;
}
public void setUploadList(List<T> uploadList) {
this.uploadList = uploadList;
}
/**
* @param validateInfo
* @param map
*/
public ValidateFileUpload(List<ValidateInfo> validateInfo,
Map<String, Object> map) {
super();
this.validateInfo = validateInfo;
this.map = map;
}
/**
*
*/
public ValidateFileUpload() {
super();
}
public int getTotal() {
return this.total;
}
public void setTotal(int total) {
this.total = total;
}
public void setTotal(){
this.total++;
}
}
/***********************************************************************
Copyright (c) 2007, AgileSC,Inc.China
All rights reserved.
************************************************************************/
package com.asc.console.dao.impl;
/**
* 封装验证的结果 .
* @author yangzelai
* @version 版本信息创建时间 Jan 15, 2011 2:34:59 PM
*/
public class ValidateInfo {
private String validateMsg; // 验证的类型
private Integer rowNumber; // 验证的记录在Excel中的行号
private String value; // 错误的值
private Integer valid; // 0 错误 1 正确
/**
*
*/
public ValidateInfo() {
super();
}
public String getValidateMsg() {
return this.validateMsg;
}
public void setValidateMsg(String validateMsg) {
this.validateMsg = validateMsg;
}
public Integer getRowNumber() {
return this.rowNumber;
}
public void setRowNumber(Integer rowNumber) {
this.rowNumber = rowNumber;
}
public String getValue() {
return this.value;
}
public void setValue(String value) {
this.value = value;
}
public Integer getValid() {
return this.valid;
}
public void setValid(Integer valid) {
this.valid = valid;
}
/**
* @param validateMsg
* @param rowNumber
* @param value
* @param valid
*/
public ValidateInfo(String validateMsg, Integer rowNumber, String value,
Integer valid) {
super();
this.validateMsg = validateMsg;
this.rowNumber = rowNumber;
this.value = value;
this.valid = valid;
}
public ValidateInfo(String validateMsg, Integer rowNumber, String value) {
super();
this.validateMsg = validateMsg;
this.rowNumber = rowNumber;
this.value = value;
this.setValid(0);
}
}

读取excel基类

view plain

package com.asc.console.fileupload;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
/**
* 1.标题必须在第一行,且符合规范
* 2.自动忽略空行
* 3.自动忽略标题外的多余列
* @author yangzelai
*
*/
public abstract class XxlsAbstract extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private int sheetIndex = -1;
private List<String> rowlist;
private int curRow = 1;
private int curCol = 1;
private String defaultStr = "";
private boolean listIsNull = true;
protected int total = 0;
private boolean allNull = true;
//excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型
public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;
//只遍历一个sheet，其中sheetId为要遍历的sheet索引，从1开始，1-3
public void processOneSheet(String filename,int sheetId) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// rId2 found by processing the Workbook
// 根据 rId# 或 rSheet# 查找sheet
InputStream sheet2 = r.getSheet("rId"+sheetId);
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public void processOneSheet(InputStream in,int sheetId) throws Exception {
OPCPackage pkg = OPCPackage.open(in);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// rId2 found by processing the Workbook
// 根据 rId# 或 rSheet# 查找sheet
InputStream sheet2 = r.getSheet("rId"+sheetId);
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
/**
* 遍历 excel 文件
*/
public void process(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory
.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => 单元格
if (name.equals("c")) {
// 如果下一个元素是 SST 的索引，则将nextIsString标记为true
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
curCol = getNumberFromLetter(attributes.getValue("r"));
} else if (name.equals("row")) {
// 设置行号
curRow = Integer.parseInt(attributes.getValue("r"));
if(listIsNull){
initRowList(attributes);
}
} else if (name.equals("dimension")){
//获得总计录数
String d = attributes.getValue("ref");
total = getNumber(d.substring(d.indexOf(":")+1,d.length()));
}
// 置空
lastContents = "";
}
@Override
public void endElement(String uri, String localName, String name)
throws SAXException {
// 根据SST的索引值的到单元格的真正要存储的字符串
// 这时characters()方法可能会被调用多次
if (nextIsString) {
try {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
.toString();
} catch (Exception e) {
}
}
// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
if (name.equals("v")) {
String value = lastContents.trim();
if(allNull && !value.equals("")){
allNull = false;
}
if(curCol <= rowlist.size()){
rowlist.set(curCol - 1, value);
}
}else {
if (name.equals("row") && !allNull) {
try {
optRows(sheetIndex, curRow, rowlist);
allNull = true;
} catch (IllegalArgumentException ie){
throw ie;
}catch (SQLException e) {
throw new SAXException(e);
}
resetRowList();
}
}
}
private void initRowList(Attributes attributes) {
// 以第一行为行数据标准
if (curRow == 1) {
int size = getColumns(attributes.getValue("spans"));
rowlist = new ArrayList<String>(size);
for (int i = 0; i < size; i++) {
rowlist.add(defaultStr);
}
}
listIsNull = false;
}
private static int getColumns(String spans) {
String number = spans.substring(spans.lastIndexOf(':') + 1,
spans.length());
return Integer.parseInt(number);
}
private static int getNumberFromLetter(String column) {
String c = column.toUpperCase().replaceAll("[0-9]", "");
int number = (int) c.charAt(0) - 64;
return number;
}
private static int getNumber(String column) {
String c = column.toUpperCase().replaceAll("[A-Z]", "");
return Integer.parseInt(c);
}
public void resetRowList() {
for (int i = 0; i < rowlist.size(); i++) {
rowlist.set(i, defaultStr);
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
//得到单元格内容的值
lastContents += new String(ch, start, length);
}
public void setAllNull(boolean allNull) {
this.allNull = allNull;
}
public boolean isAllNull() {
return allNull;
}
}

读取excel公用类

view plain

/***********************************************************************
Copyright (c) 2007, AgileSC,Inc.China
All rights reserved.
************************************************************************/
package com.asc.console.fileupload;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import com.asc.console.dao.impl.ValidateFileUpload;
/**
* 类描述 .
* @author yangzelai
* @version 版本信息创建时间 Jan 17, 2011 6:01:05 PM
*/
public abstract class FileUploadModel<T> extends XxlsAbstract {
// jdbc connection
public Connection conn;
// jdbc datasource
public DataSource dataSource;
// jdbc statement
public PreparedStatement ps;
// jdbc template;
public JdbcTemplate jdbcTemplate;
// 上传文件标题与列号 map
public Map<Integer,String> titleRowMap = new HashMap<Integer, String>();
// 验证类
public ValidateFileUpload<T> validate;
// 记录bean
public T bean;
// 批量提交大小默认1万
public int commitNumber = 10000*1;
// 是否已提交
public boolean isCommit = false;
// 临时表名
public String tempTable;
// 数据库中的表名
public String tableName;
// HttpServletRequest
public HttpServletRequest request;
// 读取单元格的值
public abstract void optRows(int sheetIndex, int rowNumber, List<String> rowList) throws SQLException;
// 设置bean的属性值 true：功能 false：失败
public abstract boolean setColumnValue(String colName, String colValue, int rowNumber, T bean);
// 初始化
public abstract void init();
// 结束
public abstract void end();
// 处理文件
public void process(FileInputStream in) throws Exception{
try {
init();
processOneSheet(in, 1);
if(!isCommit){
ps.executeBatch();
getConn().commit();
ps.clearBatch();
}
end();
clearCurrentProcess();
} catch (Exception e) {
throw e;
}finally{
try {
//删除临时表
dropTable();
//关闭连接
close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//创建表
public void createTable(){
@SuppressWarnings("unchecked")
List<Map<String, Object>> list= getJdbcTemplate().queryForList("select tname from tab where tname = '"+tempTable+"'");
if(null != list && list.size() > 0){
getJdbcTemplate().execute("drop table "+tempTable+" cascade constraints");
}
getJdbcTemplate().execute("create table "+tempTable+" as select * from "+tableName+" where rownum < -1");
getJdbcTemplate().execute("alter table "+tempTable+" add (excel_row_number NUMBER)");
}
//将临时表数据保存到正式表中
@Deprecated
public void insertDataFromTempTable(){
getJdbcTemplate().execute("insert into "+tableName+" select * from "+tempTable);
}
//删除表
public void dropTable(){
@SuppressWarnings("unchecked")
List<Map<String, Object>> list= getJdbcTemplate().queryForList("select tname from tab where tname = '"+tempTable+"'");
if(null != list && list.size() > 0){
getJdbcTemplate().execute("drop table "+tempTable+" cascade constraints");
}
}
//格式化日期
public Date formatDate(String colValue, String colName, int rowNumber, ValidateFileUpload<T> validate){
Date date = null;
try {
date = HSSFDateUtil.getJavaDate(Double.parseDouble(colValue));
} catch (Exception e) {
validate.addResult(colName+ValidateFileUpload.VALIDATE_INVALID+",正确的格式：2011-10-10", rowNumber, colValue);
}
return date;
}
@Deprecated
public String getInsertSQL(String sql){
//不能加this 否则指向本类
return "insert into "+tempTable+sql;
}
//设置实时处理变量
protected void setCurrentProcess(int totalRecord,int rowNumber){
getRequest().getSession().setAttribute("UPLOAD_TOTAL", totalRecord);
getRequest().getSession().setAttribute("UPLOAD_CURRENT_PROGRESS", rowNumber);
}
protected void clearCurrentProcess(){
getRequest().getSession().removeAttribute("UPLOAD_TOTAL");
getRequest().getSession().removeAttribute("UPLOAD_CURRENT_PROGRESS");
}
//关闭资源
public void close() throws Exception{
if(null != this.ps){
ps.close();
ps = null;
}
if(null != this.getConn()){
getConn().close();
setConn(null);
}
}
public boolean isEmpty(String param){
if(null == param){
return true;
}
param = param.trim();
if(param.length()==0){
return true;
}
return false;
}
public Connection getConn() {
try {
if(null == conn){
this.conn = getDataSource().getConnection();
}
} catch (Exception e) {
System.out.println("获得JDBC connection异常 ");
e.printStackTrace();
}
return conn;
}
public void setConn(Connection conn) {
this.conn = conn;
}
public PreparedStatement getPs() {
return this.ps;
}
public void setPs(PreparedStatement ps) {
this.ps = ps;
}
public Map<Integer, String> getTitleRowMap() {
return this.titleRowMap;
}
public void setTitleRowMap(Map<Integer, String> titleRowMap) {
this.titleRowMap = titleRowMap;
}
public ValidateFileUpload<T> getValidate() {
return this.validate;
}
public void setValidate(ValidateFileUpload<T> validate) {
this.validate = validate;
}
public T getBean() {
return this.bean;
}
public void setBean(T bean) {
this.bean = bean;
}
public DataSource getDataSource() {
return this.dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
getJdbcTemplate().setDataSource(dataSource);
}
public int getCommitNumber() {
return this.commitNumber;
}
public void setCommitNumber(int commitNumber) {
this.commitNumber = commitNumber;
}
public boolean isCommit() {
return this.isCommit;
}
public void setCommit(boolean isCommit) {
this.isCommit = isCommit;
}
public String getTempTable() {
return this.tempTable;
}
public void setTempTable(String tempTable) {
this.tempTable = tempTable;
}
public JdbcTemplate getJdbcTemplate() {
if(null == jdbcTemplate){
jdbcTemplate = new JdbcTemplate();
}
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public FileUploadModel() {
super();
}
public HttpServletRequest getRequest() {
return request;
}
public void setReqeust(HttpServletRequest request) {
this.request = request;
}
@SuppressWarnings("unused")
public void setNull(Object... list){
for (Object obj : list) {
obj = null;
}
}
/**
* 添加验证
* @param list list<map>
* @param msg 消息
* @param rowNumber 行号
* @param value 错误的值
*/
protected void addValidateResult(List<Map<String, Object>> list, String msg, String rowNumber,String value ){
if (null != list && list.size() > 0) {
for (Map<String, Object> record : list) {
try {
validate.addResult(msg, ((Number)record.get(rowNumber)).intValue(), (String)record.get(value));
} catch (Exception e) {
validate.addResult(msg, ((Number)record.get(rowNumber)).intValue(), ((Number)record.get(value)).intValue()+"");
}
}
}
}
}

上传示列：

view plain

/***********************************************************************
************************************************************************/
package com.asc.console.fileupload;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import com.asc.console.dao.impl.ValidateFileUpload;
import com.asc.maindatainfo.orginfo.bean.TskfOrg;
/**
* 上传企业文件时,不做数据验证 .
* @author yangzelai
* @version 版本信息创建时间 Jan 23, 2011 9:38:20 AM
*/
public class TskfOrgModel extends FileUploadModel<TskfOrg> {
//列
public static String COLUMNS = "ID,ORG_NAME,ORG_CODE,REGION_NAME,REGION_CODE" +
",PROVINCE_NAME,PROVINCE_CODE,CITY_NAME,CITY_CODE" +
",ADDRESS,ORG_TYPE,ORG_LEVEL,ORG_PROPERTY,ORG_CHANNEL,CREATE_DATE,UPDATE_MONTH";
public static boolean isLock = false;
/* (non-Javadoc)
* @see com.asc.console.fileupload.FileUploadModel#optRows(int, int, java.util.List)
*/
public static boolean isLock() {
return isLock;
}
public static void setLock(boolean isLock) {
TskfOrgModel.isLock = isLock;
}
public void optRows(int sheetIndex, int rowNumber, List<String> rowList)
throws SQLException {
if(rowNumber == 1){
int index = 0;
for (String title : rowList) {
if(!(TskfOrg.TITLE_COLUMN.contains(title) && TskfOrg.TITLE_COLUMN.indexOf(title) == index)){
throw new IllegalArgumentException("标题不符合规范："+title);
}
titleRowMap.put(index, title);
index++;
}
//创建临时表
createTable();
//设置事务
getConn().setAutoCommit(false);
ps = getConn().prepareStatement("INSERT INTO "+tempTable+" ("+COLUMNS+",EXCEL_ROW_NUMBER) VALUES (HIBERNATE_SEQUENCE.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
}else{
//读取行数据
this.bean = new TskfOrg();
boolean toSave = false;
for(int i=0; i<rowList.size(); i++){
String colName = titleRowMap.get(i);
String colValue = rowList.get(i);
toSave = setColumnValue(colName, colValue, rowNumber, bean);
//如果有一个字段值无效，这条数据不保存
if(!toSave){
break;
}
}
//添加到ps中
if(toSave){
ps.setString(1, bean.getOrgName());
ps.setString(2, bean.getOrgCode());
ps.setString(3, bean.getRegionName());
ps.setString(4, bean.getRegionCode());
ps.setString(5, bean.getProvinceName());
ps.setString(6, bean.getProvinceCode());
ps.setString(7, bean.getCityName());
ps.setString(8, bean.getCityCode());
ps.setString(9, bean.getAddress());
ps.setString(10, bean.getOrgType());
ps.setString(11, bean.getOrgLevel());
ps.setString(12, bean.getOrgProperty());
ps.setString(13, bean.getOrgChannel());
if(null != bean.getCreateDate()){
ps.setDate(14, new java.sql.Date(bean.getCreateDate().getTime()));
}else{
ps.setDate(14, null);
}
ps.setString(15, bean.getUpdateMonth());
ps.setInt(16, rowNumber);
ps.addBatch();
isCommit = false;
this.validate.setTotal();
}
//提交
if(rowNumber%commitNumber == 0){
ps.executeBatch();
getConn().commit();
ps.clearBatch();
isCommit = true;
setCurrentProcess(this.total, rowNumber);
}
}
}
/* (non-Javadoc)
* @see com.asc.console.fileupload.FileUploadModel#setColumnValue(java.lang.String, java.lang.String, int, java.lang.Object)
*/
public boolean setColumnValue(String colName, String colValue,
int rowNumber, TskfOrg bean) {
/************ 字段验证 start *************/
if(TskfOrg.ROW_ORG_NAME.equals(colName)){
bean.setOrgName(colValue);
}else if(TskfOrg.ROW_ORG_CODE.equals(colName)){
//去空、去重
if(isEmpty(colValue)){
validate.addResult(colName+ValidateFileUpload.VALIDATE_NULL, rowNumber, colValue);
return false;
}else{
bean.setOrgCode(colValue);
}
}else if(TskfOrg.ROW_REGION_NAME.equals(colName)){
bean.setRegionName(colValue);
}else if(TskfOrg.ROW_REGION_CODE.equals(colName)){
bean.setRegionCode(colValue);
}else if(TskfOrg.ROW_PROVINCE_NAME.equals(colName)){
bean.setProvinceName(colValue);
}else if(TskfOrg.ROW_PROVINCE_CODE.equals(colName)){
bean.setProvinceCode(colValue);
}else if(TskfOrg.ROW_CITY_NAME.equals(colName)){
bean.setCityName(colValue);
}else if(TskfOrg.ROW_CITY_CODE.equals(colName)){
bean.setCityCode(colValue);
}else if(TskfOrg.ROW_ADDRESS.equals(colName)){
bean.setAddress(colValue);
}else if(TskfOrg.ROW_ORG_TYPE.equals(colName)){
//去空、验证
if(isEmpty(colValue)){
validate.addResult(colName+ValidateFileUpload.VALIDATE_NULL, rowNumber, colValue);
return false;
}else if(!TskfOrg.ORG_TYPE.contains(colValue)){
validate.addResult(colName+ValidateFileUpload.VALIDATE_INVALID, rowNumber, colValue);
return false;
}else{
bean.setOrgType(colValue);
}
}else if(TskfOrg.ROW_ORG_LEVEL.equals(colName)){
bean.setOrgLevel(colValue);
}else if(TskfOrg.ROW_ORG_PROPERTY.equals(colName)){
bean.setOrgProperty(colValue);
}else if(TskfOrg.ROW_ORG_CHANNEL.equals(colName)){
bean.setOrgChannel(colValue);
}else if(TskfOrg.ROW_CREATE_DATE.equals(colName)){
if(!isEmpty(colValue)){
Date date = formatDate(colValue, colName, rowNumber, validate);
if(null != date){
bean.setCreateDate(date);
}else{
return false;
}
}
}else if(TskfOrg.ROW_UPDATE_MONTH.equals(colName)){
bean.setUpdateMonth(colValue);
}else{
validate.addResult(ValidateFileUpload.VALIDATE_INVALID+colName, rowNumber, colValue);
return false;
}
/************ 字段验证 end *************/
return true;
}
/* (non-Javadoc)
* @see com.asc.console.fileupload.FileUploadModel#init()
*/
public void init() {
validate = new ValidateFileUpload<TskfOrg>();
//validate.getMap().put("ORG_CODE",new HashMap<String,TskfOrg>());
}
@SuppressWarnings("unchecked")
public void end() {
/************ 校验数据 start **********/
//自身去重
StringBuffer tempDirtySQL = new StringBuffer();
tempDirtySQL.append(" WHERE ORG_CODE IN (");
tempDirtySQL.append(" SELECT ORG_CODE FROM ").append(tempTable);
tempDirtySQL.append(" GROUP BY ORG_CODE");
tempDirtySQL.append(" HAVING COUNT(1) > 1");
tempDirtySQL.append(") AND ID NOT IN (");
tempDirtySQL.append(" SELECT MIN(ID) FROM ").append(tempTable);
tempDirtySQL.append(" GROUP BY ORG_CODE");
tempDirtySQL.append(" HAVING COUNT(1) > 1");
tempDirtySQL.append(")");
List<Map<String, Object>> tempDirtyList= getJdbcTemplate().queryForList("SELECT ORG_CODE, EXCEL_ROW_NUMBER FROM "+ tempTable + tempDirtySQL+" AND ROWNUM <= 100");
addValidateResult(tempDirtyList,ValidateFileUpload.VALIDATE_REPEATED+TskfOrg.ROW_ORG_CODE
+ "(仅显示100条)","EXCEL_ROW_NUMBER","ORG_CODE");
if (null != tempDirtyList && tempDirtyList.size() > 0) {
getJdbcTemplate().update("DELETE "+ tempTable + tempDirtySQL);
}
//对比去重
StringBuffer compareDirtySQL = new StringBuffer();
compareDirtySQL.append(" LEFT JOIN ").append(tableName).append(" T ON (").append(tempTable).append(".ORG_CODE = T.ORG_CODE)");
compareDirtySQL.append(" WHERE T.ORG_CODE IS NOT NULL");
compareDirtySQL.append(" AND ROWNUM <= 100");
List<Map<String, Object>> compareDirtyList = getJdbcTemplate().queryForList("SELECT "+tempTable+".ORG_CODE, "+tempTable+".EXCEL_ROW_NUMBER FROM "+ tempTable + compareDirtySQL);
addValidateResult(compareDirtyList, TskfOrg.ROW_ORG_CODE
+ ValidateFileUpload.VALIDATE_EXIST
+ "(仅显示100条)", "EXCEL_ROW_NUMBER", "ORG_CODE");
/************ 校验数据 end **********/
StringBuffer insertSQL = new StringBuffer();
insertSQL.append("INSERT INTO ").append(tableName);
insertSQL.append(" (").append(COLUMNS).append(")");
insertSQL.append(" SELECT ").append(TskfOrgModel.COLUMNS.replaceFirst("ID", tempTable+".ID").replaceAll(",", ","+tempTable+"."));
insertSQL.append(" FROM ").append(tempTable);
insertSQL.append(" LEFT JOIN ").append(tableName).append(" T ON (").append(tempTable).append(".ORG_CODE = T.ORG_CODE)");
insertSQL.append(" WHERE T.ORG_CODE IS NULL");
int total = getJdbcTemplate().update(insertSQL.toString());
validate.setTotal(total);
setNull(tempDirtySQL,tempDirtyList,compareDirtySQL,compareDirtyList,insertSQL);
TskfOrgModel.setLock(false);
}
}

测试方法

view plain

TskfOrgModel mode = new TskfOrgModel();
mode.setDataSource(getDataSource());
mode.setTableName("TSKF_ORG");
mode.setTempTable("TEMP_TSKF_ORG");
mode.setReqeust(request);
mode.process(fileStream);//开始处理
ValidateFileUpload<TskfOrg> validate = mode.getValidate();
validate.addResult("保存成功", validate.getTotal(), "", 1);
response.getWriter().write("{success: true,msg:'上传成功',data:"+JsonUtil.toJson(validate.getValidateInfo(),"-class")+"}");

附SAX读Excel生成的xml格式：

view plain

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<dimension ref="A1:C2"/>
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="A2" sqref="A2"/></sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="13.5"/>
<cols>
<col min="1" max="1" width="15.375" customWidth="1"/>
<col min="2" max="2" width="19.25" customWidth="1"/>
<col min="3" max="3" width="13.75" customWidth="1"/>
</cols>
<sheetData>
<row r="1" spans="1:3">
<c r="A1" s="1" t="s">
<v>0</v>
</c>
<c r="B1" s="1"/>
<c r="C1" s="1">
<v>111.1</v>
</c>
</row>
<row r="2" spans="1:3">
<c r="C2">
<v>222.2</v>
</c>
</row>
</sheetData>
<phoneticPr fontId="1" type="noConversion"/>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<pageSetup paperSize="9" orientation="portrait" horizontalDpi="200" verticalDpi="200" r:id="rId1"/>
</worksheet>

ly5156

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
POI、SAX读取Excel大文件

poi 读取文件分为两种模式：用户模型、事件用户模型，第一种方案是以DOM方式读取Excel，好处是读取操作方便，不足的是一次性将文件加载到内存；第二种方案是SAX读取Excel，好处是内存占用小，因为每次只读取陪份数据，缺点是读取方件较不方便。涉及的功能类： excel错误信息记录类、读取excel基类、读取excel公用类、示例类测试结果（读取、存库）：16万
复制链接

扫一扫

专栏目录