Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
tgtools.excel.poi.ImportExcelImpl Maven / Gradle / Ivy
package tgtools.excel.poi;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.apache.poi.hssf.record.CellValueRecordInterface;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.*;
import tgtools.excel.ImportExcel;
import tgtools.excel.listener.ImportListener;
import tgtools.excel.listener.event.*;
import tgtools.excel.util.JsonUtil;
import tgtools.exceptions.APPErrorException;
import tgtools.util.FileUtil;
import tgtools.util.LogHelper;
import tgtools.util.StringUtil;
import java.io.*;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 名 称：
* 编写者：田径
* 功 能：
* 时 间：21:47
*/
public class ImportExcelImpl implements ImportExcel {
protected int mbeginDataRow = 0;
protected int mbeginTitleRow = 0;
protected LinkedHashMap mCoulmns;
protected ObjectNode mImportResult;
protected Workbook mWorkbook;
protected ImportListener mListener;
protected Map mSheetTable;
protected ArrayList mTableNames;
protected String mDatabaseType = null;
protected LinkedHashMap mParseData = null;
@Override
public void setListener(ImportListener pListener) {
mListener = pListener;
}
@Override
public void init(LinkedHashMap pColumns, Map pSheetTable) {
init(pColumns, pSheetTable, null, 0, 1);
}
@Override
public void init(LinkedHashMap pColumns, Map pSheetTable, String pDatabaseType) {
init(pColumns, pSheetTable, pDatabaseType, 0, 1);
}
@Override
public void init(LinkedHashMap pColumns, Map pSheetTable, String pDatabaseType, int pBeginDataRow) {
init(pColumns, pSheetTable, pDatabaseType, 0, pBeginDataRow);
}
@Override
public void init(LinkedHashMap pColumns, Map pSheetTable, String pDatabaseType, int pBeginTitleRow, int pBeginDataRow) {
mCoulmns = pColumns;
mDatabaseType = pDatabaseType;
mbeginTitleRow = pBeginTitleRow;
mbeginDataRow = pBeginDataRow;
mSheetTable = pSheetTable;
}
@Override
public void importExcel(File pFile) throws APPErrorException {
if (null == pFile || !pFile.exists()) {
throw new APPErrorException("文件不存在！p_File：" + (null == pFile ? "null" : pFile.getAbsolutePath()));
}
String ext = FileUtil.getExtensionName(pFile.getAbsolutePath());
try {
importExcel(new FileInputStream(pFile), ext);
}catch (FileNotFoundException e)
{
throw new APPErrorException("文件不存在！p_File：" + (null == pFile ? "null" : pFile.getAbsolutePath()));
}
}
@Override
public void importExcel(byte[] pDatas, String pVersion) throws APPErrorException {
if (null == pDatas || pDatas.length < 1) {
throw new APPErrorException("无效的文件内容p_Datas");
}
importExcel(new ByteArrayInputStream(pDatas), pVersion);
}
@Override
public void importExcel(InputStream pInputStream, String pVersion) throws APPErrorException {
mImportResult = JsonUtil.getEmptyObjectNode();
mTableNames = new ArrayList();
mParseData = new LinkedHashMap();
try {
mWorkbook = WorkbookFactory.createWorkbook(pInputStream, pVersion);
CreateWorkbookEvent event = new CreateWorkbookEvent();
event.setData(pInputStream);
event.setWorkbook(mWorkbook);
onCreateWorkbook(event);
} catch (Exception ex) {
throw new APPErrorException("创建excel错误", ex);
}
try {
doImportExcel();
} catch (Exception ex) {
throw new APPErrorException("导入excel错误；原因：" + ex.getMessage(), ex);
}
}
protected void doImportExcel() throws APPErrorException {
if (mbeginDataRow < 0) {
throw new APPErrorException("数据行不能小于0");
}
if (mbeginTitleRow < 0) {
throw new APPErrorException("标题行不能小于0");
}
if (mbeginDataRow <= mbeginTitleRow) {
throw new APPErrorException("数据行只能大于标题行；标题行：" + String.valueOf(mbeginTitleRow) + ";数据行：" + String.valueOf(mbeginDataRow));
}
if (null != mCoulmns && mCoulmns.size() > 0) {
parseExcel();
}
}
/**
* 根据excel列名获取表列明
*
* @param pExcelColumnName
*
* @return
*/
private String getAttrName(String pExcelColumnName) {
for (Map.Entry item : mCoulmns.entrySet()) {
if (item.getValue().equals(pExcelColumnName)) {
return item.getKey();
}
}
return null;
}
/**
* 获取日期型单元格值
*
* @param pDate
*
* @return
*/
private String getDateCellValue(Date pDate) {
Date date = pDate;
TimeZone zone = TimeZone.getTimeZone("Asia/Shanghai");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
sdf.setTimeZone(zone);
return sdf.format(date);
}
/**
* 用于xls 2003 数据变成科学计数后的问题
*
* @param pCell
* @param pDefaultValue
*
* @return
*/
private String getFixNumericValue(Cell pCell, String pDefaultValue) {
String result = pDefaultValue;
try {
//excel 2003
if (pCell instanceof HSSFCell) {
Method method = pCell.getClass().getDeclaredMethod("getCellValueRecord");
if (null != method) {
method.setAccessible(true);
CellValueRecordInterface obj = (CellValueRecordInterface) method.invoke(pCell);
String value = obj.toString();
value = value.substring(value.indexOf(".value=") + 7);
result = value.substring(0, value.indexOf("\n"));
}
} else {
//excel 2007
BigDecimal bigDecimal = new BigDecimal(result);
String str = bigDecimal.toPlainString();
String pattern = "(\\d.*[1-9])";
String res = tgtools.util.RegexHelper.regexFirst(str, pattern);
try {
new BigDecimal(res);
result = res;
} catch (Exception ex) {
LogHelper.error("系统", "转换科学基数出错，原值：" + pDefaultValue + ";;转换后：" + res, "ImportExcel.getFixNumericValue", ex);
}
}
} catch (Exception e) {
LogHelper.error("系统", "转换科学基数出错，不支持的excel类型，请尝试excel2003", "ImportExcel.getFixNumericValue", e);
}
return result;
}
/**
* 获取公式的值
*
* @param pCell
*
* @return
*/
@SuppressWarnings("deprecation")
private Object getFORMULACellValue(Cell pCell) {
String cellValue = "";
FormulaEvaluator evaluator = pCell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
CellValue evalValue = evaluator.evaluate(pCell);
switch (evalValue.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = evalValue.getStringValue().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
return evalValue.getNumberValue();
case Cell.CELL_TYPE_BOOLEAN:
return evalValue.getBooleanValue();
default:
cellValue = "";
}
return cellValue;
}
/**
* 获取单元格值
*
* @param pCell
*
* @return
*/
@SuppressWarnings("deprecation")
private Object getCellValue(Cell pCell) {
String cellValue = "";
if (null == pCell) {
return StringUtil.EMPTY_STRING;
}
switch (pCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = pCell.getRichStringCellValue().getString().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(pCell)) {
cellValue = getDateCellValue(DateUtil.getJavaDate(pCell.getNumericCellValue()));
break;
}
cellValue = new HSSFDataFormatter().formatCellValue(pCell);
if (cellValue.indexOf(".") < 0) {
return Integer.parseInt(cellValue);
}
if (!StringUtil.isNullOrEmpty(cellValue) && (cellValue.indexOf("E+") > 0 || cellValue.indexOf("E-") > 0)) {
cellValue = getFixNumericValue(pCell, cellValue);
}
break;
case Cell.CELL_TYPE_BOOLEAN:
return pCell.getBooleanCellValue();
case Cell.CELL_TYPE_FORMULA:
return getFORMULACellValue(pCell);
default:
cellValue = "";
}
return cellValue;
}
/**
* 解析数据集
*
* @return
*
* @throws APPErrorException
*/
private void parseDatas() throws APPErrorException {
int sheetcount = mWorkbook.getNumberOfSheets();
//循环excel
for (int i = 0; i < sheetcount; i++) {
Sheet sheet = mWorkbook.getSheetAt(i);
if (null == sheet) {
throw new APPErrorException("获取第" + i + "个表错误");
}
int titlerow = mbeginTitleRow;
int datarow = mbeginDataRow;
int rowcount = 0;
Row titleRow = null;
ArrayNode dt = JsonUtil.getEmptyArrayNode();
try {
ReadSheetEvent readevent = new ReadSheetEvent();
readevent.setCancel(false);
readevent.setBeginDataRow(mbeginDataRow);
readevent.setBeginTitleRow(mbeginTitleRow);
readevent.setSheetName(sheet.getSheetName());
onReadSheet(readevent);
if (readevent.getCancel()) {
continue;
}
//获取总行数（由于获取行号从0开始，所以行数应为行号加1）
rowcount = sheet.getLastRowNum() + 1;
LogHelper.info("", "正在解析总行数：" + rowcount, "");
titleRow = sheet.getRow(titlerow);
} catch (Exception ex) {
throw new APPErrorException("解析第" + i + "表错误；起始行：" + mbeginDataRow + "；标题行：" + mbeginTitleRow);
}
//循环添加行
for (int r = datarow; r < rowcount; r++) {
try {
Row sheetrow = sheet.getRow(r);
if (null == sheetrow) {
continue;
}
LogHelper.info("", "正在解析行：" + r, "");
//int colcount = sheetrow.getPhysicalNumberOfCells();
int colcount = sheetrow.getLastCellNum();
LogHelper.info("", "正在解析总列数：" + colcount, "");
ObjectNode row = JsonUtil.getEmptyObjectNode();
//循环添加列值
for (int c = 0; c < colcount; c++) {
if (null == titleRow.getCell(c)) {
break;
//throw new APPErrorException("无效的标题cell：" + c + "sheetname:" + sheet.getSheetName());
}
LogHelper.info("", "正在解析行：" + r + "正在解析列：" + c + "标题：" + titleRow.getCell(c).getStringCellValue(), "");
//取得excel列名
String colName = titleRow.getCell(c).getStringCellValue();
String col = getAttrName(colName);
if (StringUtil.isNullOrEmpty(col)) {
continue;
}
if(null==sheet.getRow(r).getCell(c))
{
continue;
}
Object value = "";
try {
value = getCellValue(sheet.getRow(r).getCell(c));
LogHelper.info("", "正在解析行：" + r + "正在解析列：" + c + "标题：" + titleRow.getCell(c).getStringCellValue() + "；值：" + value, "");
} catch (Exception ex) {
throw new APPErrorException("取值错误：" + ex.getMessage() + ";行：" + String.valueOf(r) + "；列：" + String.valueOf(c) + ";sheet:" + sheet.toString() + ";row:" + sheet.getRow(r).toString(), ex);
}
try {
ImportEvent event = new ImportEvent();
event.setpColumns(mCoulmns);
event.setRowIndex(r);
event.setColumnIndex(c);
event.setValue(value);
onGetValue(event);
value = event.getValue();
if (value instanceof String) {
row.put(col, (String) value);
} else {
row.putPOJO(col, value);
}
} catch (Exception ex) {
throw new APPErrorException("添加数据到datatable出错：" + ex.getMessage() + ";行：" + String.valueOf(r) + "；列：" + String.valueOf(c) + ";sheet:" + sheet.toString() + ";row:" + sheet.getRow(r).toString(), ex);
}
}
dt.add(row);
} catch (Exception ex) {
throw new APPErrorException("添加数据到datatable出错：" + ex.getMessage() + ";行：" + String.valueOf(r) + ";sheet:" + sheet.toString(), ex);
}
}
SheetParsedEvent sheetevent = new SheetParsedEvent();
sheetevent.setSheetName(sheet.getSheetName());
sheetevent.setData(dt);
onSheetParsed(sheetevent);
if (null != dt && dt.size() > 0) {
mParseData.put(sheet.getSheetName(), dt);
mTableNames.add(sheet.getSheetName());
}
}
}
/**
* 解析excel
*
* @return
*
* @throws APPErrorException
*/
public void parseExcel() throws APPErrorException {
try {
parseDatas();
} catch (Exception ex) {
LogHelper.error("", "excel解析错误22", "ImportExcel。parseExcel1", ex);
}
if (StringUtil.isNullOrEmpty(mDatabaseType)) {
return;
}
parseToDatabase();
}
protected void parseToDatabase() throws APPErrorException {
try {
if (null == mParseData || mParseData.size() < 1) {
throw new APPErrorException("excel没有有效内容");
}
int count = 0;
int sucess = 0;
int error = 0;
int i = 0;
for (Map.Entry item : mParseData.entrySet()) {
ArrayNode table = item.getValue();
count += table.size();
String tablename = mTableNames.get(i);
for (int rownum = 0; rownum < table.size(); rownum++) {
JsonNode row = table.get(rownum);
try {
ImportEvent event = new ImportEvent();
event.setpColumns(mCoulmns);
event.setIsExcute(true);
event.setRow(row);
String sql = tgtools.util.JsonSqlFactory.parseInsertSql(row, mDatabaseType, tablename);
event.setSql(sql);
onExcuteSQL(event);
if (event.getIsExcute()) {
sucess += execute(event.getSql());
} else {
sucess += event.getIsSucess() ? 1 : 0;
}
} catch (Exception e) {
error = error + 1;
LogHelper.error("", "excel导入出错", "ImportExcel.parseExcel1", e);
}
}
i = i + 1;
}
mImportResult.put("count", count);
mImportResult.put("success", sucess);
mImportResult.put("error", error);
ExcelCompletedEvent event = new ExcelCompletedEvent();
event.setDatas(mParseData);
event.setWorkbook(mWorkbook);
onCompleted(event);
} catch (Exception ex) {
throw new APPErrorException("解析excel错误，原因：" + ex.getMessage(), ex);
}
}
private int execute(String sql) throws APPErrorException {
sql = tgtools.util.SqlStrHelper.processKeyWord(sql);
return tgtools.db.DataBaseFactory.getDefault().executeUpdate(sql);
}
@Override
public ObjectNode getImportResult() {
return mImportResult;
}
@Override
public LinkedHashMap getParseData() {
return new LinkedHashMap(){{putAll(mParseData);}};
}
@Override
public void close() throws IOException {
if(null!=mParseData)
{
mParseData.clear();
}
if (null != mCoulmns) {
mCoulmns.clear();
}
if (null != mImportResult) {
mImportResult.removeAll();
}
if (null != mWorkbook) {
try {
mWorkbook.close();
} catch (Exception e) {
}
}
if (null != mSheetTable) {
mSheetTable.clear();
}
if (null != mTableNames) {
mTableNames.clear();
}
mCoulmns = null;
mImportResult = null;
mWorkbook = null;
mListener = null;
mSheetTable = null;
mTableNames = null;
mParseData=null;
}
//------------------------------ Listener ------------------------------------
/**
* 创建excel workbook后对workbook的事件
*
* @param pEvent
*/
protected void onCreateWorkbook(CreateWorkbookEvent pEvent) {
if (null != mListener) {
mListener.onCreateWorkbook(pEvent);
}
}
public void onReadSheet(ReadSheetEvent pEvent) {
if (null != mListener) {
mListener.onReadSheet(pEvent);
}
}
/**
* 整个任务完成后事件
*
* @param pEvent
*/
protected void onCompleted(ExcelCompletedEvent pEvent) {
if (null != mListener) {
mListener.onCompleted(pEvent);
}
}
protected void onExcuteSQL(ImportEvent pEvent) {
if (null != mListener) {
mListener.onExcuteSQL(pEvent);
}
}
public void onSheetParsed(SheetParsedEvent pEvent) {
if (null != mListener) {
mListener.onSheetParsed(pEvent);
}
}
protected void onGetValue(ImportEvent pEvent) {
if (null != mListener) {
mListener.onGetValue(pEvent);
}
}
public static void main(String[] args) {
String filepath = "C:\\tianjing\\Desktop\\项目立项导入模板.xlsx";
ImportExcelImpl importExcel = new ImportExcelImpl();
LinkedHashMap columns = new LinkedHashMap();
// columns.put("ID", "序号");
// columns.put("NAME", "间隔");
// columns.put("BIR", "设备类别");
columns.put("XMLB", "项目类别");
columns.put("ZY", "专业");
columns.put("GQ", "市（县）/分部工区");
columns.put("JHND", "计划年度");
columns.put("XMBZSJ", "项目编制时间");
columns.put("ZYLB", "专业类别");
columns.put("ZYXF", "专业细分");
columns.put("DXMD", "大修目的");
columns.put("QKJ_XMZT", "项目状态（全口径）");
columns.put("LXYJ", "立项依据");
columns.put("BHSTZ", "不含税投资（万元）");
columns.put("DQ", "地区");
columns.put("QKJ_JBXX_XMMC", "项目名称");
columns.put("QKJ_JBXX_CBBH", "储备编号");
columns.put("QKJ_JBXX_YJDW", "一级单位");
columns.put("QKJ_JBXX_EJDW", "二级单位");
columns.put("QKJ_JBXX_YJFL", "一级分类");
columns.put("QKJ_JBXX_EJFL", "二级分类");
columns.put("QKJ_JBXX_SJFL", "三级分类");
columns.put("QKJ_JBXX_GKBM", "归口部门");
columns.put("QKJ_JBXX_CJR", "创建人");
columns.put("QKJ_JBXX_XMSZD", "项目所在地");
columns.put("QKJ_JBXX_SSDW", "实施单位");
columns.put("QKJ_JBXX_ZCXZ", "资产性质");
columns.put("QKJ_JBXX_ZJSX", "资金属性");
columns.put("QKJ_JBXX_XMPJ", "项目评级");
columns.put("QKJ_JBXX_XMPX", "项目排序");
columns.put("QKJ_JBXX_ZT", "账套");
columns.put("QKJ_JBXX_XMFL", "项目分类");
columns.put("QKJ_JBXX_JSXZ", "建设性质");
columns.put("QKJ_JBXX_SFSBGW", "是否上报国网");
columns.put("QKJ_JBXX_DYDJ", "电压等级");
columns.put("QKJ_JBXX_SFLSTDXM", "是否绿色通道项目");
columns.put("QKJ_JBXX_XMSSBM", "项目实施部门");
columns.put("QKJ_ZJXZ_KYZTZ", "可研总投资（万元）");
columns.put("QKJ_ZJXZ_YSZTZ", "预算总投资（万元）");
columns.put("QKJ_ZJXZ_PZZTZ", "批准总投资（万元）");
columns.put("QKJ_ZJXZ_CBZX", "成本中心");
columns.put("QKJ_ZJXZ_LRZX", "利润中心");
columns.put("QKJ_ZJXZ_XMZTZ", "项目总投资（万元）");
columns.put("QKJ_WHXX_KYPFWH", "可研批复文号");
columns.put("QKJ_WHXX_JSPFWH", "建设批复文号");
columns.put("QKJ_WHXX_GWPFWH", "国网批复文号");
columns.put("QKJ_WHXX_BZGSWH", "标准概算文号");
columns.put("QKJ_WHXX_KGPFWH", "开工批复文号");
columns.put("QKJ_RQXX_JSKSNX", "建设开始年限");
columns.put("QKJ_RQXX_JSJSNX", "建设结束年限");
columns.put("QKJ_GWSX_JJXMGM", "基建项目规模");
columns.put("QKJ_GWSX_JJXXTZXS", "基建项目投资形式");
columns.put("QKJ_GWSX_SFZBTGXM", "是否总部统管项目");
columns.put("QKJ_GWSX_SFDJXM", "是否代建项目");
columns.put("QKJ_GWSX_SFSYGBZJ", "是否使用国拨资金");
columns.put("QKJ_GWSX_ZYBWWH", "中央部委文号");
columns.put("QKJ_GWSX_ZJSX", "国网资金属性");
columns.put("QKJ_GWSX_ZBFZT", "总部非直投");
columns.put("QKJ_GWSX_SFDXJG", "是否大型技改");
columns.put("QKJ_GWSX_JJXMZXGCLB", "基建项目专项工程类别");
columns.put("QKJ_GWSX_JJXMDWLB", "基建项目电网类别");
columns.put("QKJ_GWSX_ZBTGBSM", "总部统管标识码");
columns.put("QKJ_GWSX_TZDW", "投资单位");
columns.put("QKJ_GWSX_GBZJXMLX", "国拨资金项目类型");
columns.put("QKJ_GWSX_GWDYDJ", "国网电压等级");
columns.put("QKJ_GWSX_ZNDWFL", "智能电网分类");
columns.put("QKJ_GWSX_ZBXMGLB", "总部项目管理部");
columns.put("QKJ_GWSX_JJXMJSXZ", "基建项目建设性质");
columns.put("QKJ_ZCZXYZB_TDSS", "停电损失");
columns.put("QKJ_ZCZXYZB_DLSHSS", "电量损耗损失");
columns.put("QKJ_ZCZXYZB_PJFZL", "平均负载率");
columns.put("QKJ_ZCZXYZB_DWZCSDSY", "单位资产售电收益");
columns.put("QKJ_ZCZXYZB_LSDLZZL", "历史电量增长率");
columns.put("QKJ_ZCZXYZB_WYZCYWF", "万元资产运维费");
columns.put("QKJ_ZCZXYZB_YWFZZL", "运维费增长率");
columns.put("QKJ_ZCZXYZB_ZCZLRL", "资产组利润率");
columns.put("QKJ_XMJYS_XMBYX_JBQKCZWT", "基本情况，存在问题");
columns.put("QKJ_XMJYS_XMBYX_SSNR", "实施内容");
columns.put("QKJ_XMJYS_CWSH_JE", "金额（万元）");
columns.put("QKJ_XMJYS_CWSH_DNYSAP", "当年预算安排（万元）");
columns.put("QKJ_XMJYS_CWSH_SHYJ", "审核意见");
columns.put("QKJ_XMJYS_CWSH_SHHZSXMYJ", "审核后正式项目意见");
columns.put("QKJ_XMJYS_ZTZ_SBJCLGZF", "设备及材料购置费（万元）");
columns.put("QKJ_XMJYS_ZTZ_SGF", "施工费（万元）");
columns.put("QKJ_XMJYS_ZTZ_QTFY", "其他费用（万元）");
columns.put("QKJ_XMJYS_ZTZ_HJ", "合计（万元）");
HashMap table = new HashMap();
table.put("sheet1", "MQ_SYS.ACT_ID_USER");
//默认不做数据库操作 之转换成json
importExcel.init(columns, table,null,2,3);
//设置数据库类型后进行sql 操作
//importExcel.init(columns, table,"dm");
try {
importExcel.importExcel(new File(filepath));
Map ds = importExcel.getParseData();
importExcel.close();
System.out.println(ds);
} catch (Exception e) {
e.printStackTrace();
}
}
}
