//Excel文件导入
@SuppressWarnings({ "unused", "static-access" })
public void uploadExcel(){
// 首先判断Excel文件是否成功上传并获取到文件
if (getExcelFile() == null || !excelFile.exists()) {
outString("{\"flag\":\"导入出错，请刷新页面重试。\"}");
return;
}
//增加异常信息记录信息 add by2017-12-14 PM begin。
StringBuffer sb_error = new StringBuffer();
//增加异常信息记录信息 add by2017-12-14 PM end。
// 读取解析Excel,并判断是否解析成功
final List<String[]> listOrg = new ArrayList<String[]>();
File ff = excelFile;
String name =request.getSession().getId() + ff.getName();
//System.out.println(excelFile.getName());
long l = ff.length();
//long start = System.currentTimeMillis();
//首先判断是EXCEL还是CSV文件
if(excelFile.getName().indexOf(".csv") != -1){
}else{//excel文件
//如果是数据量大的就执行大数据量的解析方法，否则就执行传统解析方式
if(ff.length()>521000){
try {
//执行大数据量的导入转成xml文件格式读取
BigExcelReaderTest reader = new BigExcelReaderTest(ff) {
protected void outputRow(String[] datas, int[] rowTypes, int rowIndex) {
//获取每一行的数据将数据放入list中保存
listOrg.add(datas);
System.out.println(rowIndex);
}
};
reader.parse();
} catch (IOException e1) {
e1.printStackTrace();
if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【IO异常】:").append(e1.getMessage());
}else{
sb_error.append(";").append("【IO异常】:").append(e1.getMessage());
}
} catch (OpenXML4JException e1) {
e1.printStackTrace();
if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【格式转换异常】:").append(e1.getMessage());
}else{
sb_error.append(";").append("【格式转换异常】:").append(e1.getMessage());
}
} catch (SAXException e1) {
e1.printStackTrace();
if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【SAXE解析异常】:").append(e1.getMessage());
}else{
sb_error.append(";").append("【SAXE解析异常】:").append(e1.getMessage());
}
}
}else{
List<String[]> listOrgR = AddressExcelUtil.readExcel2(excelFile);
listOrg.addAll(listOrgR);
}
}
if(listOrg.get(0).length==22){
List<String[]> list = listOrg.subList(1, listOrg.size());
int count = 10000;//一个线程处理20万条数据
int listSize = list.size();//数据集合大小
success =0;
dataExctption =0;
//Salemanage.put(name+"Size", listSize);//百分比
int runSize = (listSize/count)+1; //开启的线程数
List<String[]> newlist = null;//存放每个线程的据
JDBCPool cd = new JDBCPool();
Connection ct = null;
PreparedStatement pst = null;
String insertSql = "insert into ts_sys_Equipment(id,addrid,address,"
+ "Equipment,OBD,Onequipmenttype,onequipment,"
+ "OLT,PON,Accessmode,Coveragemode, "
+ "netsource,Openservice,Openservicenum,addrtime, "
+ "equipmentaddr,oneaddr,twoaddr,threeaddr, "
+ "fouraddr,Fiveaddr,sixaddr,Sevenaddr"
+ ") "
+ "values(sys_Equipment_ID_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
ct = cd.getConnection();
pst = ct.prepareStatement(insertSql);
ct.setAutoCommit(false);// 开始事务
} catch (SQLException e1) {
e1.printStackTrace();
if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【打开JDBC异常】:").append(e1.getMessage());
}else{
sb_error.append(";").append("【打开JDBC异常】:").append(e1.getMessage());
}
}
DecimalFormat fm = new DecimalFormat("#");
SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddhhmmss");
//增加异常信息输出到日志信息
StringBuffer sb = new StringBuffer();
for(int i=0,len=list.size();i<len;i++){
String[] test = list.get(i);
try{
//地址ID
if(test[0] != null && !"0".equals(test[0]) && !"".equals(test[0])){
pst.setInt(1, Integer.parseInt(test[0]));
}else{
pst.setInt(1,0);
}
pst.setString(2, test[1]);
pst.setString(3, test[2]);
pst.setString(4, test[3]);
pst.setString(5, test[4]);
pst.setString(6, test[5]);
pst.setString(7, test[6]);
pst.setString(8, test[7]);
pst.setString(9, test[8]);
pst.setString(10, test[9]);
pst.setString(11, test[10]);
pst.setString(12, test[11]);
if(test[12] != null && !"0".equals(test[12]) && !"".equals(test[12])){
//System.out.println(test[12]);
pst.setInt(13, Integer.parseInt(test[12]));
}else{
pst.setInt(13,0);
}
if(test[13]==null || "".equals(test[13])){//时间格式
pst.setString(14,null);
}else{
pst.setTimestamp(14,new java.sql.Timestamp((sf.parse(test[13])).getTime()));
}
if(test[14]==null || "".equals(test[14])){//时间格式
pst.setString(15, null);
}else{
pst.setTimestamp(15,new java.sql.Timestamp((sf.parse(test[14]).getTime())));
}
if(test[15]==null || "".equals(test[15])){//如果一级地址为空
pst.setString(16,"空");
}else{
pst.setString(16, test[15]);
}
if(test[16]==null || "".equals(test[16])){//如果二级地址为空
pst.setString(17, "空");
}else{
pst.setString(17, test[16]);
}
if(test[17]==null || "".equals(test[17])){//如果三级地址为空
pst.setString(18, "空");
}else{
pst.setString(18, test[17]);
}
if(test[18]==null || "".equals(test[18])){//如果四级地址为空
pst.setString(19, "空");
}else{
pst.setString(19, test[18]);
}
if(test[19]==null || "".equals(test[19])){//如果五级地址为空
pst.setString(20, "空");
}else{
pst.setString(20, test[19]);
}
if(test[20]==null || "".equals(test[20])){//如果六级地址为空
pst.setString(21, "空");
}else{
pst.setString(21, test[20]);
}
if(test[21]==null || "".equals(test[21])){//如果七级地址为空
pst.setString(22,"空");
}else{
//System.out.println(test[21]);
pst.setString(22, test[21]);
}
pst.addBatch();
if(((i+1)%count)==0){//每次插入list.size条数据
int[] executeBatch = pst.executeBatch();
success +=executeBatch.length;//累计成功数
System.out.println("提交"+(i+1)+"条");
pst.clearBatch();
ct.commit();
}
}catch(Exception e){
//数据格式异常
dataExctption++;
e.printStackTrace();
if(StringUtils.isEmpty(sb.toString())){
sb.append(test[0]);
}else{
sb.append(",");
sb.append(test[0]);
}
}
}
if(StringUtils.isNotEmpty(sb.toString())){
logger.error("*******************异常导入数据信息:"+sb.toString());
if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【导入数据格式异常】:").append(sb.toString());
}else{
sb_error.append(";").append("【导入数据格式异常】:").append(sb.toString());
}
}
try {
int[] executeBatch = pst.executeBatch();
success +=executeBatch.length;//累计成功数
ct.commit();
pst.clearBatch();
// add in 2017-12-12 pm 所有增加事务还原的自动提交的代码
ct.setAutoCommit(true);
/***
* modify by in 2017-12-13 am 这个过程很耗时，将其迁移到前台关闭窗口在去调用
//增加导入数据后,各级地址修证的储存过程调用。
AddressOverride addressoverride = new AddressOverride();
logger.error("******************************准备执行【设备地址导入时各级地址修证的储存过程调用】************************");
service.saveFlushDatas(addressoverride);
logger.error("******************************执行完毕【设备地址导入时各级地址修证的储存过程调用】************************");
****/
} catch (Exception e) {
e.printStackTrace();
// add in 2017-12-12 pm 异常事务回滚
try {
//异常时回滚
ct.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
if(pst != null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ct != null){
try {
ct.setAutoCommit(true);
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//记录操作日志
OperationLog operationLog = new OperationLog();
operationLog.setUserAccount(user.getAccount());
operationLog.setUserIp(user.getUserIp());
operationLog.setOpType("导入");
operationLog.setOpResource("AddressOverrideAction#upload");
operationLog.setOpFunction("设备覆盖地地址管理");
operationLog.setOpContent("文件名称为："+"设备覆盖地地址管理"+"文件大小为："+(excelFile.length()/1024+1)+"KB,总共导入："+total+",导入成功："+success+",导入失败："+(total-success));
IOperationLogService operatelog = (IOperationLogService) SpringContainer.getBeanByName("operationLogService");
operatelog.insertData(operationLog);
//返回最终结果
JSONObject jsonObj = new JSONObject();
if(success>0){
jsonObj.put("flag", "导入完成! 导入总记录数"+listSize+",导入成功"+success+"条,失败数量:"+(listSize-success)+"条(导入时出错"+(listSize-success-dataExctption)+"条,数据格式异常数量"+dataExctption+");"+"异常信息:"+sb_error.toString());
}else{
jsonObj.put("flag", "导入失败! 导入总记录数"+listSize+"失败数量:"+(listSize-success)+"条(导入时出错"+(listSize-success-dataExctption)+"条,数据格式异常数量"+dataExctption+");"+"异常信息:"+sb_error.toString());
}
outString(jsonObj.toString());
}else{
outString(new JSONObject().put("flag", "导入失败!表格格式不正确.").toString());
}
}
///===================excel文件读取及解析成List 工具类=========================
/*
* @describe
* @fileName com.cttsp.frame.util.StringUtil
* @company 深圳元道通信技术有限公司
* @author admin
* @date 2012 2012-11-30
*/
package com.cttsp.frame.util;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.Arrays;
import java.util.Date;
import java.util.regex.Pattern;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
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;
public abstract class BigExcelReaderTest{
/* public static void main(String[] args) throws Exception{
Fiel filepath = "";
BigExcelReader reader = new BigExcelReader(filepath) {
@Override
protected void outputRow(String[] datas, int[] rowTypes, int rowIndex) {
// 此处输出每一行的数据
System.out.println(Arrays.toString(datas));
}
};
// 执行解析
reader.parse();
} */
enum xssfDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
}
public static final int ERROR = 1;
public static final int BOOLEAN = 1;
public static final int NUMBER = 2;
public static final int STRING = 3;
public static final int DATE = 4;
public static final String DATE_FORMAT_STR = "yyyy-MM-dd HH:mm:ss";
public final static String PHONE_PATTERN="^((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(17([0,1,6,7,]))|(18[0-2,5-9]))\\d{8}$";
// private DataFormatter formatter = new DataFormatter();
private InputStream sheet;
private XMLReader parser;
private InputSource sheetSource;
private int index = 0;
/**
* 读大数据量Excel
*
* @param file Excel文件
* @param maxColNum 读取的最大列数
* @throws IOException
* @throws OpenXML4JException
* @throws SAXException
*/
public BigExcelReaderTest(File file) throws IOException, OpenXML4JException, SAXException{
try {
OPCPackage pkg = OPCPackage.openOrCreate(file);
init(pkg);
} catch (Exception e1){
e1.printStackTrace();
}
}
/**
* 初始化 将Excel转换为XML
*
* @param pkg
* @throws IOException
* @throws OpenXML4JException
* @throws SAXException
*/
private void init(OPCPackage pkg) throws IOException, OpenXML4JException, SAXException{
XSSFReader xssfReader = new XSSFReader(pkg);
System.out.println(xssfReader);
SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
System.out.println(sharedStringsTable);
StylesTable stylesTable = xssfReader.getStylesTable();
System.out.println(stylesTable);
sheet = xssfReader.getSheet("rId1");
System.out.println(sheet);
parser = fetchSheetParser(sharedStringsTable, stylesTable);
System.out.println(parser);
sheetSource = new InputSource(sheet);
System.out.println(sheetSource);
}
/**
* 执行解析操作
*
* @return 读取的Excel行数
*/
public int parse() {
try {
parser.parse(sheetSource);
}
catch (IOException e) {
e.printStackTrace();
}
catch (SAXException e) {
e.printStackTrace();
}
finally{
if(sheet != null){
try {
sheet.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return index;
}
private XMLReader fetchSheetParser(SharedStringsTable sharedStringsTable, StylesTable stylesTable) throws SAXException {
XMLReader parser =
XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser"
);
try {
ContentHandler handler = new SheetHandler(sharedStringsTable, stylesTable);
parser.setContentHandler(handler);
}catch(Exception e){
e.printStackTrace();
}
return parser;
}
/**
* SAX解析的处理类
* 每解析一行数据后通过outputRow(String[] datas, int[] rowTypes, int rowIndex)方法进行输出
*
* @author zpin
*/
private class SheetHandler extends DefaultHandler {
private SharedStringsTable sharedStringsTable; // 存放映射字符串
private StylesTable stylesTable;// 存放单元格样式
private String readValue;// 存放读取值
private xssfDataType dataType;// 单元格类型
private String[] rowDatas;// 存放一行中的所有数据
private int[] rowTypes;// 存放一行中所有数据类型
private int colIdx;// 当前所在列
private short formatIndex;
// private String formatString;// 对数值型的数据直接读为数值，不对其格式化，所以隐掉此处
private SheetHandler(SharedStringsTable sst,StylesTable stylesTable) {
this.sharedStringsTable = sst;
this.stylesTable = stylesTable;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if(name.equals("c")) {// c > 单元格
colIdx = getColumn(attributes);
String cellType = attributes.getValue("t");
String cellStyle = attributes.getValue("s");
this.dataType = xssfDataType.NUMBER;
if ("b".equals(cellType)){
this.dataType = xssfDataType.BOOL;
}
else if ("e".equals(cellType)){
this.dataType = xssfDataType.ERROR;
}
else if ("inlineStr".equals(cellType)){
this.dataType = xssfDataType.INLINESTR;
}
else if ("s".equals(cellType)){
this.dataType = xssfDataType.SSTINDEX;
}
else if ("str".equals(cellType)){
this.dataType = xssfDataType.FORMULA;
}
else if(cellStyle != null){
int styleIndex = Integer.parseInt(cellStyle);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
// this.formatString = style.getDataFormatString();
}
}
// 解析到一行的开始处时，初始化数组
else if(name.equals("row")){
int cols = getColsNum(attributes);// 获取该行的单元格数
rowDatas = new String[cols];
rowTypes = new int[cols];
}
readValue = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
if(name.equals("v")) { // 单元格的值
switch(this.dataType){
case BOOL: {
char first = readValue.charAt(0);
rowDatas[colIdx] = first == '0' ? "FALSE" : "TRUE";
rowTypes[colIdx] = BOOLEAN;
break;
}
case ERROR: {
rowDatas[colIdx] = "ERROR:" + readValue.toString();
rowTypes[colIdx] = ERROR;
break;
}
case INLINESTR: {
rowDatas[colIdx] = new XSSFRichTextString(readValue).toString();
rowTypes[colIdx] = STRING;
break;
}
case SSTINDEX:{
int idx = Integer.parseInt(readValue);
rowDatas[colIdx] = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx)).toString();
rowTypes[colIdx] = STRING;
break;
}
case FORMULA:{
rowDatas[colIdx] = readValue;
rowTypes[colIdx] = STRING;
break;
}
case NUMBER:{
// 判断是否是日期格式
// if(HSSFDateUtil.isValidExcelDate(Double.parseDouble(readValue))){
// Double d = Double.parseDouble(readValue);
// Date date = HSSFDateUtil.getJavaDate(d);
// rowDatas[colIdx] = DateFormatUtils.format(date, DATE_FORMAT_STR);
// rowTypes[colIdx] = DATE;
// }
// if (HSSFDateUtil.isADateFormat(formatIndex, readValue)&&!Pattern.compile(PHONE_PATTERN).matcher(readValue).matches()) {
// Double d = Double.parseDouble(readValue);
// Date date = HSSFDateUtil.getJavaDate(d);
// rowDatas[colIdx] = DateFormatUtils.format(date, DATE_FORMAT_STR);
// rowTypes[colIdx] = DATE;
// }
// else if (formatString != null){
// cellData.value = formatter.formatRawCellContents(Double.parseDouble(cellValue), formatIndex, formatString);
// cellData.dataType = NUMBER;
// }
// else{
rowDatas[colIdx] = readValue;
rowTypes[colIdx] = NUMBER;
// }
break;
}
}
}
// 当解析的一行的末尾时，输出数组中的数据
else if(name.equals("row")){
outputRow(rowDatas, rowTypes, index++);
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
readValue += new String(ch, start, length);
}
}
/**
* 输出每一行的数据
*
* @param datas 数据
* @param rowTypes 数据类型
* @param rowIndex 所在行
*/
protected abstract void outputRow(String[] datas, int[] rowTypes, int rowIndex);
private int getColumn(Attributes attrubuts) {
String name = attrubuts.getValue("r");
int column = -1;
for (int i = 0; i < name.length(); ++i) {
if (Character.isDigit(name.charAt(i))) {
break;
}
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}
private int getColsNum(Attributes attrubuts){
String spans = attrubuts.getValue("spans");
String cols = spans.substring(spans.indexOf(":") + 1);
return Integer.parseInt(cols);
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
