package com.timevale.esign.vip.util; import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List; import net.sf.json.JSONObject; import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory; import com.timevale.esign.db.bean.ErrRefLog;
import com.timevale.esign.db.utils.StringUtil;
import com.timevale.esign.db.utils.UUIDGenerator;
import com.timevale.esign.vip.bean.ExcelDataBean;
import com.timevale.esign.vip.constants.ExcelConstans; /**
* 类名：ExcellUtils.java <br/>
* 功能说明：excel解析工具类 <br/>
* 修改历史： <br/>
* 1.[2015年10月13日上午9:59:25]创建类 by hewu
*/
public class ExcelUtil { Workbook wb = null; List<String[]> dataList = new ArrayList<String[]>(100); private Logger LOG = LoggerFactory.getLogger(ExcelUtil.class); /**
* 功能说明：构造器
* @param path 修改历史：<br/>
* 1.[2015年10月13日下午3:52:19] 创建方法 by hewu
*/
public ExcelUtil(final String path) {
try {
final InputStream inp = new FileInputStream(path);
this.wb = WorkbookFactory.create(inp);
} catch (FileNotFoundException e) {
LOG.error("error to find excel File .", e);
e.printStackTrace();
} catch (InvalidFormatException e) {
LOG.error("error to InvalidFormat excel File .", e);
e.printStackTrace();
} catch (IOException e) {
LOG.error("error to deal excel File and found a IOException.", e);
e.printStackTrace();
}
} public ExcelUtil() { super();
} /**
* 功能说明：读取Excel所有数据，包含header
* @param sheetIndex sheet下标
* @return <br/>
* 修改历史：<br/>
* 1.[2015年10月13日下午3:21:47] 创建方法 by hewu
*/
public final ExcelDataBean readExcel(int sheetIndex) {
ExcelDataBean bean = new ExcelDataBean();
int columnNum = 0;
final Sheet sheet = this.wb.getSheetAt(sheetIndex);
String name = sheet.getRow(0).getCell(0).getStringCellValue().trim();
if (name.contains("企业")) {
bean.setType(ExcelConstans.USER_TYPE_ORGANIZE);
} else {
bean.setType(ExcelConstans.USER_TYPE_PERSON);
}
if (sheet.getRow(0) != null) {
columnNum = sheet.getRow(0).getLastCellNum()
- sheet.getRow(0).getFirstCellNum();
}
if (columnNum > 0) {
// for (Row row : sheet) {
int rowNum = sheet.getLastRowNum();
for (int k = 1; k <= rowNum; k++) {
final String[] singleRow = new String[columnNum];
int n = 0;
for (int i = 0; i < columnNum; i++) {
final Cell cell = sheet.getRow(k).getCell(i,
Row.CREATE_NULL_AS_BLANK);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
singleRow[n] = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
singleRow[n] = Boolean.toString(cell
.getBooleanCellValue());
break;
// 数值
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
singleRow[n] = String.valueOf(cell
.getDateCellValue());
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
String temp = cell.getStringCellValue();
// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
if (temp.indexOf(".") > -1) {
singleRow[n] = String.valueOf(new Double(temp))
.trim();
} else {
singleRow[n] = temp.trim();
}
}
break;
case Cell.CELL_TYPE_STRING:
singleRow[n] = cell.getStringCellValue().trim();
break;
case Cell.CELL_TYPE_ERROR:
singleRow[n] = "";
break;
case Cell.CELL_TYPE_FORMULA:
cell.setCellType(Cell.CELL_TYPE_STRING);
singleRow[n] = cell.getStringCellValue();
if (singleRow[n] != null) {
singleRow[n] = singleRow[n].replaceAll("#N/A", "")
.trim();
}
break;
default:
singleRow[n] = "";
break;
}
n++;
}
// 如果第一行为空，跳过
final StringBuffer str1 = new StringBuffer();
for (int t = 0; t < columnNum; t++) {
str1.append(singleRow[t]);
}
if (!StringUtil.isNull(str1.toString())) {
this.dataList.add(singleRow);
}
}
}
bean.setColData(dataList);
return bean;
} /**
* 功能说明：读取Excel，返回Excel最大行index值，实际行数要加1
* @param sheetIndex sheet下标
* @return <br/>
* 修改历史：<br/>
* 1.[2015年10月13日下午3:21:58] 创建方法 by hewu
*/
public final int getRowNum(final int sheetIndex) {
final Sheet sheet = this.wb.getSheetAt(sheetIndex);
return sheet.getLastRowNum();
} /**
* 功能说明：读取Excel,返回数据的列数
* @param sheetIndex sheet下标
* @return int <br/>
* 修改历史：<br/>
* 1.[2015年10月13日下午3:22:07] 创建方法 by hewu
*/
public final int getColumnNum(final int sheetIndex) {
final Sheet sheet = this.wb.getSheetAt(sheetIndex);
final Row row = sheet.getRow(0);
if (row != null && row.getLastCellNum() > 0) {
return row.getLastCellNum();
}
return 0;
} /**
* 读取Excel,获取某一行数据
* @param sheetIndex sheet下标
* @param rowIndex 计数从0开始，rowIndex为0代表header行
* @return String[] 修改历史：<br/>
* 1.[2015年10月13日下午3:22:07] 创建方法 by hewu
*/
public final String[] getRowData(final int sheetIndex, final int rowIndex) {
String[] dataArray = null;
if (rowIndex > this.getColumnNum(sheetIndex)) {
return dataArray;
} else {
dataArray = new String[this.getColumnNum(sheetIndex)];
return this.dataList.get(rowIndex);
} } /**
* 功能说明：读取Excel获取某一列数据
* @param sheetIndex sheet下标
* @param colIndex 列下标
* @return <br/>
* 修改历史：<br/>
* 1.[2015年10月13日下午3:22:46] 创建方法 by hewu
*/
public final String[] getColumnData(final int sheetIndex, final int colIndex) {
String[] dataArray = null;
if (colIndex > this.getColumnNum(sheetIndex)) {
return dataArray;
} else {
if (this.dataList != null && this.dataList.size() > 0) {
dataArray = new String[this.getRowNum(sheetIndex) + 1];
int index = 0;
for (String[] rowData : this.dataList) {
if (rowData != null) {
dataArray[index] = rowData[colIndex];
index++;
}
}
}
}
return dataArray; } /**
* 功能说明：导出excel（按照参数 userType导出个人/企业的失败日志）
* @param templatePath 导出模板路径
* @param ErrRefLogs 错误对象
* @param userType 用户类型
* @return String 导出的excel的临时文件<br/>
* 修改历史：<br/>
* 1.[2015年10月13日下午8:06:48] 创建方法 by hewu
*/
public String writeExcel(String templatePath, List<ErrRefLog> errRefLogs,
int userType) {
String onceFile = "";
onceFile = FileUtil.Copy(new File(templatePath), getClass()
.getResource("/errlog").getFile()
+ UUIDGenerator.getUUID()
+ ".xlsx");
FileInputStream input = null;
Workbook workbook = null;
String regStr = "[\\【\\,\\】]";
try {
input = new FileInputStream(new File(onceFile));
workbook = WorkbookFactory.create(input);
} catch (Exception e) {
e.printStackTrace();
this.LOG.error("error to create write Excel", e);
}
final Sheet sheet = workbook.getSheetAt(0);
if (userType == ExcelConstans.USER_TYPE_PERSON) {
// 解析每一条数据,增加行
for (int i = 0; i < errRefLogs.size(); i++) {
ErrRefLog log = errRefLogs.get(i);
final Row row = sheet.createRow(i
+ ExcelConstans.EXCEL_START_INDEX);
// 增加列数据
// ErrRefLog errRefLog = ErrRefLogs.get(i);
final CellStyle ss = workbook.createCellStyle();
ss.setLocked(false); final Cell nameC = row.createCell(0);
nameC.setCellStyle(ss);
nameC.setCellType(XSSFCell.CELL_TYPE_STRING);
nameC.setCellValue(log.getName());// 写入内容 final Cell mobileC = row.createCell(1);
mobileC.setCellType(XSSFCell.CELL_TYPE_STRING);
mobileC.setCellStyle(ss);
mobileC.setCellValue(log.getMobile());// 写入内容 final Cell idNoC = row.createCell(2);
idNoC.setCellType(XSSFCell.CELL_TYPE_STRING);
idNoC.setCellStyle(ss);
idNoC.setCellValue(log.getIdNo());// 写入内容 // final Cell emailC = row.createCell(3);
// emailC.setCellType(XSSFCell.CELL_TYPE_STRING);
// emailC.setCellStyle(ss);
// emailC.setCellValue(log.getEmail());// 写入内容 final Cell companyC = row.createCell(3);
companyC.setCellType(XSSFCell.CELL_TYPE_STRING);
companyC.setCellStyle(ss);
companyC.setCellValue(log.getOrgan());// 写入内容 StringBuffer errReason = new StringBuffer();
final Cell reasonC = row.createCell(4);
reasonC.setCellType(XSSFCell.CELL_TYPE_STRING);
reasonC.setCellStyle(ss);
if(log.getErrReason().contains("【")){
String [] reason = log.getErrReason().split(regStr);
errReason.append(reason[0]+"【");
errReason.append("*"+reason[1].subSequence(reason[1].length()-1, reason[1].length())+","+reason[2].substring(0, 6)+"****");
errReason.append("】"+reason[3]);
reasonC.setCellValue(errReason.toString());
}else {
reasonC.setCellValue(log.getErrReason());
}
}
} else if (userType == ExcelConstans.USER_TYPE_ORGANIZE) {
// 解析每一条数据,增加行
for (int i = 0; i < errRefLogs.size(); i++) {
final ErrRefLog log = errRefLogs.get(i);
final JSONObject obj = JSONObject.fromObject(log
.getContentJson());
final Row row = sheet.createRow(i
+ ExcelConstans.EXCEL_START_INDEX);
// 增加列数据
// ErrRefLog errRefLog = ErrRefLogs.get(i);
final CellStyle ss = workbook.createCellStyle();
ss.setLocked(false);
ss.setAlignment(CellStyle.ALIGN_CENTER);
// 企业名称
final Cell nameC = row.createCell(0);
nameC.setCellStyle(ss);
nameC.setCellType(XSSFCell.CELL_TYPE_STRING);
nameC.setCellValue(log.getName());
// 手机号
final Cell mobileC = row.createCell(1);
mobileC.setCellType(XSSFCell.CELL_TYPE_STRING);
mobileC.setCellStyle(ss);
mobileC.setCellValue(log.getMobile());
// 组织机构代码证号
final Cell organCodeC = row.createCell(2);
organCodeC.setCellType(XSSFCell.CELL_TYPE_STRING);
organCodeC.setCellStyle(ss);
organCodeC.setCellValue(log.getOrganCode());
// 邮箱
// final Cell emailC = row.createCell(3);
// emailC.setCellType(XSSFCell.CELL_TYPE_STRING);
// emailC.setCellStyle(ss);
// emailC.setCellValue(log.getEmail());
// 单位类型
final Cell companyC = row.createCell(3);
companyC.setCellType(XSSFCell.CELL_TYPE_STRING);
companyC.setCellStyle(ss);
companyC.setCellValue(obj.getInt("organType"));
// 注册类型
final Cell userTypeC = row.createCell(4);
userTypeC.setCellType(XSSFCell.CELL_TYPE_STRING);
userTypeC.setCellStyle(ss);
userTypeC.setCellValue(obj.getInt("userType"));
// 工商注册号
final Cell regCodeC = row.createCell(5);
regCodeC.setCellType(XSSFCell.CELL_TYPE_STRING);
regCodeC.setCellStyle(ss);
regCodeC.setCellValue(obj.getString("regCode"));
// 法定代表姓名
final Cell legalNameC = row.createCell(6);
legalNameC.setCellType(XSSFCell.CELL_TYPE_STRING);
legalNameC.setCellStyle(ss);
legalNameC.setCellValue(obj.getString("legalName"));
// 法定代表人身份证号
final Cell legalIdNoC = row.createCell(7);
legalIdNoC.setCellType(XSSFCell.CELL_TYPE_STRING);
legalIdNoC.setCellStyle(ss);
legalIdNoC.setCellValue(obj.getString("legalIdNo"));
// 法定代表人归属地
final Cell legalAreaC = row.createCell(8);
legalAreaC.setCellType(XSSFCell.CELL_TYPE_STRING);
legalAreaC.setCellStyle(ss);
legalAreaC.setCellValue(obj.getInt("legalArea"));
// 代理人姓名
final Cell agentNameC = row.createCell(9);
agentNameC.setCellType(XSSFCell.CELL_TYPE_STRING);
agentNameC.setCellStyle(ss);
agentNameC.setCellValue(obj.getString("agentName"));
// 代理人姓名
final Cell agentIdNoC = row.createCell(10);
agentIdNoC.setCellType(XSSFCell.CELL_TYPE_STRING);
agentIdNoC.setCellStyle(ss);
agentIdNoC.setCellValue(obj.getString("agentIdNo"));
// 公司地址
final Cell addressC = row.createCell(11);
addressC.setCellType(XSSFCell.CELL_TYPE_STRING);
addressC.setCellStyle(ss);
addressC.setCellValue(obj.getString("address"));
// 经营范围
final Cell scopeC = row.createCell(12);
scopeC.setCellType(XSSFCell.CELL_TYPE_STRING);
scopeC.setCellStyle(ss);
scopeC.setCellValue(obj.getString("scope"));
// 失败原因
StringBuffer errReason = new StringBuffer();
final Cell reasonC = row.createCell(13);
reasonC.setCellType(XSSFCell.CELL_TYPE_STRING);
reasonC.setCellStyle(ss);
if(log.getErrReason().contains("【")){
String [] reason = log.getErrReason().split(regStr);
errReason.append(reason[0]+"【");
errReason.append("*"+reason[1].subSequence(reason[1].length()-1, reason[1].length())+","+reason[2].substring(0, 6)+"****");
errReason.append("】"+reason[3]);
reasonC.setCellValue(errReason.toString());
}else {
reasonC.setCellValue(log.getErrReason());
}
}
} else { }
// 新建一输出流
FileOutputStream fout;
try {
fout = new FileOutputStream(onceFile);
// 存盘
workbook.write(fout);
fout.flush();
// 结束关闭
fout.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
this.LOG.error("fileNotFoundException", e);
} catch (IOException e) {
e.printStackTrace();
this.LOG.error("IOException", e);
}
return onceFile;
} // public static void main(String[] args) {
// // 导入excel
// // ExcelUtils utils = new ExcelUtils("D:\\用户导入模版.xls");
// ExcelUtil utils = new ExcelUtil("d:\\企业用户导入模版 .xlsx");
// List<String[]> list = utils.readExcel(0);
// for (int i = 0; i < list.size(); i++) {
// String[] str = list.get(i);
// for (int j = 0; j < str.length; j++) {
// System.out.println(str[j]);
// }
//
// }
// //导出excel
// ExcelUtils utils = new ExcelUtils(
// ExcelConstans.ERR_ORGANIZE_ACCOUNT_EXPORT_DIR);
// List<ErrRefLog> accounts = new ArrayList<ErrRefLog>();
// String path = utils.writeExcel(
// ExcelConstans.ERR_ORGANIZE_ACCOUNT_EXPORT_DIR, accounts, 2);
// System.out.println(path);
// }
// public static void main(String[] args) {
// String regStr = "[\\【\\,\\】]";
// String s = "我是【张三,430381199007086018】使用";
// System.out.println(s.split(regStr)[2]);
//
// }
}
文件解析的相关依赖包可以去maven库下载