private static String [] title_arr = null;
static{
titleMap.put("亡者姓名", 0);
titleMap.put("亡者身份证号", 1);
titleMap.put("性别", 2);
titleMap.put("死亡年龄", 3);
titleMap.put("死亡日期", 4);
titleMap.put("户籍所在地址", 5);
titleMap.put("申请人姓名", 6);
titleMap.put("申请人性别", 7);
titleMap.put("申请人身份证号码", 8);
titleMap.put("与亡者关系", 9);
titleMap.put("联系电话", 10);
titleMap.put("领取金额", 11);
titleMap.put("领取时间", 12);
titleMap.put("经办人", 13);
titleMap.put("审核人", 14);
// 先判断表头是否正常
title_arr = new String[]{"亡者姓名","亡者身份证号","性别","死亡年龄","死亡日期","户籍所在地址",
"申请人姓名","申请人性别","申请人身份证号码","与亡者关系","联系电话","领取金额","领取时间","经办人","审核人"};
}
/**
* 导入依赖jar
* 1.jxl.jar
* 2.commons-fileupload.jar
* 3.poi-3.9-20121203.jar
* 4.poi-ooxml-3.9-20121203.jar
* 5.poi-ooxml-schemas-3.9-20121203.jar
* @return
*/
@Autowired
private ApplicationRecordService applicationRecordService;
public ActionForward importAllowanceReceiveInfo(ActionMapping mapping , ActionForm form , HttpServletRequest request, HttpServletResponse response) throws Exception{
log.info("资金历史补录导入开始>>>>>>>>>>>>>>>>>>>>>>>>>>...");
Long timeBegin = System.currentTimeMillis();
String importtype = request.getParameter("importtype");
int i_count = 0; // 累计错误行
// 设置在线用户信息
User user = UserSession.getUser(request);
// 通过user获取各项区域信息
AreaInfo areaInfo = Dicts.getAreaInfoByUserId(user.getId());
Long importSuccessCount = new Long(0);
String importmsg = "";
InputStream inxls = null;
HSSFWorkbook wb = null;
List<CellTable> tablelist = new ArrayList<CellTable>();
CellTable cellTable = null;
// 判断是否存在导入错误信息
String importerror = "";
try {
AllowanceReceiveViewInfoForm recelveForm = (AllowanceReceiveViewInfoForm)form;
FormFile file = recelveForm.getImportAllowanceReceiveInfoFile();
if(file == null){
importmsg = "文件导入错误,不存在导入文件。";
request.setAttribute("importmsg", importmsg);
return mapping.findForward("importSuccess");
}
// 文件输入流
inxls = file.getInputStream();
// 创建Excel对象
wb = new HSSFWorkbook(inxls);
// 获取第一个工作薄sheet
HSSFSheet sheet = wb.getSheetAt(0);
// 获取第一行
HSSFRow row_1 = sheet.getRow(0);
boolean title_true = true;
for(int a = 0 ; a < title_arr.length ; a++){
if(!title_arr[a].equalsIgnoreCase(row_1.getCell(a).getStringCellValue())){
title_true = false;
break;
}
}
if(title_true){
// 获取最后一行
int i_num = sheet.getLastRowNum();
if(i_num > 0){
for(int i = 1 ; i <= i_num ; i++){
HSSFRow row_ = sheet.getRow(i);
if(row_ != null){
String deadname = row_.getCell(0) == null?"":row_.getCell(0).getStringCellValue(); // 亡者姓名
String deadidcard = row_.getCell(1) == null?"":row_.getCell(1).getStringCellValue();// 亡者身份证号
String deadsex = row_.getCell(2) == null?"":row_.getCell(2).getStringCellValue();// 性别
String deadage = null;
try{
deadage = row_.getCell(3) == null?"":row_.getCell(3).getStringCellValue();// 死亡年龄
}catch(IllegalStateException ie){
log.error("deadage Cannot get a text value from a numeric cell");
deadage = "err";
}
String deaddate = null;
try{
deaddate = row_.getCell(4) == null?"":row_.getCell(4).getStringCellValue();// 死亡日期
}catch(IllegalStateException ie){
log.error("deaddate Cannot get a text value from a numeric cell");
deaddate = "err";
}
String deadhomeAddress = row_.getCell(5) == null?"":row_.getCell(5).getStringCellValue();// 户籍所在地址【非必填项】
String appname = row_.getCell(6) == null?"":row_.getCell(6).getStringCellValue();// 申请人姓名
String appsex = row_.getCell(7) == null?"":row_.getCell(7).getStringCellValue();// 申请人性别
String appidcard = row_.getCell(8) == null?"":row_.getCell(8).getStringCellValue();// 申请人身份证号码
String appnation = row_.getCell(9) == null?"":row_.getCell(9).getStringCellValue();// 与亡者关系
String apptel = null;
try{
apptel = row_.getCell(10) == null?"":row_.getCell(10).getStringCellValue();// 联系电话
}catch(IllegalStateException ie){
log.error("apptel Cannot get a text value from a numeric cell");
apptel = "err";
}
String receivemoney = null;
try{
receivemoney = row_.getCell(11) == null?"":row_.getCell(11).getStringCellValue();// 领取金额
}catch(IllegalStateException ie){
log.error("receivemoney Cannot get a text value from a numeric cell");
receivemoney = "err";
}
String receivedate = null;
try{
receivedate = row_.getCell(12) == null?"":row_.getCell(12).getStringCellValue();// 领取时间
}catch(IllegalStateException ie){
log.error("receivedate Cannot get a text value from a numeric cell");
receivedate = "err";
}
String operatename = row_.getCell(13) == null?"":row_.getCell(13).getStringCellValue();// 经办人
String shname = row_.getCell(14) == null?"":row_.getCell(14).getStringCellValue();// 审核人
if (Tools.isEmpty(deadname) && Tools.isEmpty(deadidcard)
&& Tools.isEmpty(deadsex) && Tools.isEmpty(deadage)
&& Tools.isEmpty(deaddate) && Tools.isEmpty(appname)
&& Tools.isEmpty(appsex) && Tools.isEmpty(appidcard)
&& Tools.isEmpty(appnation) && Tools.isEmpty(apptel)
&& Tools.isEmpty(receivemoney) && Tools.isEmpty(receivedate)
) {
continue;
}
// 验证必填项
if(Tools.isEmpty(deadname)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("亡者姓名")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("亡者姓名")+1)+"列"+title_arr[titleMap.get("亡者姓名")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(deadidcard)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("亡者身份证号")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("亡者身份证号")+1)+"列"+title_arr[titleMap.get("亡者身份证号")]+"是必填项未填写。";
i_count++;
}
if(!"".equals(IDCardValidate(deadidcard))){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("亡者身份证号")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("亡者身份证号")+1)+"列"+title_arr[titleMap.get("亡者身份证号")]+IDCardValidate(deadidcard);
i_count++;
}
if(Tools.isEmpty(deadsex)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("性别")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("性别")+1)+"列"+title_arr[titleMap.get("性别")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(deadage)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("死亡年龄")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("死亡年龄")+1)+"列"+title_arr[titleMap.get("死亡年龄")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(deaddate)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("死亡日期")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("死亡日期")+1)+"列"+title_arr[titleMap.get("死亡日期")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(appname)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("申请人姓名")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("申请人姓名")+1)+"列"+title_arr[titleMap.get("申请人姓名")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(appsex)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("申请人性别")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("申请人性别")+1)+"列"+title_arr[titleMap.get("申请人性别")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(appidcard)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("申请人身份证号码")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("申请人身份证号码")+1)+"列"+title_arr[titleMap.get("申请人身份证号码")]+"是必填项未填写。";
i_count++;
}
if(!"".equals(IDCardValidate(appidcard))){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("申请人身份证号码")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("申请人身份证号码")+1)+"列"+title_arr[titleMap.get("申请人身份证号码")]+IDCardValidate(appidcard);
i_count++;
}
if(Tools.isEmpty(appnation)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("与亡者关系")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("与亡者关系")+1)+"列"+title_arr[titleMap.get("与亡者关系")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(apptel)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("联系电话")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("联系电话")+1)+"列"+title_arr[titleMap.get("联系电话")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(receivemoney)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("领取金额")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("领取金额")+1)+"列"+title_arr[titleMap.get("领取金额")]+"是必填项未填写。";
i_count++;
}
if(Tools.isEmpty(receivedate)){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(titleMap.get("领取时间")+1); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("领取时间")+1)+"列"+title_arr[titleMap.get("领取时间")]+"是必填项未填写。";
i_count++;
}
// 验证死亡人身份证号是否存在重复
AidedPersonInfoQueryBean aidedPersonInfoBean = new AidedPersonInfoQueryBean();
aidedPersonInfoBean.setIdCards(deadidcard);
int idCardCount = applicationRecordService.idCardsCount(aidedPersonInfoBean);
if(idCardCount > 0){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(2); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("亡者身份证号")+1)+"列已经存在相同的"+title_arr[titleMap.get("亡者身份证号")]+"。";
i_count++;
}
// 验证死亡年龄格式
Long deadAge = new Long(0);
try{
deadAge = Long.parseLong(deadage);
}catch(NumberFormatException ne){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(4); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("死亡年龄")+1)+"列"+title_arr[titleMap.get("死亡年龄")]+"格式不正确。";
i_count++;
}
// 验证死亡日期格式
Date deadDate = null;
try{
deadDate = Tools.getDateByStr(deaddate, "yyyy-MM-dd");
}catch(ParseException pe){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(5); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("死亡日期")+1)+"列"+title_arr[titleMap.get("死亡日期")]+"格式不正确。";
i_count++;
}
// 验证领取金额格式
Long sumAllowance = new Long(0);
try{
sumAllowance = Long.parseLong(receivemoney);
}catch(NumberFormatException ne){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(12); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("领取金额")+1)+"列"+title_arr[titleMap.get("领取金额")]+"格式不正确。";
i_count++;
}
// 验证领取日期格式
Date receiveDate = null;
try{
receiveDate = Tools.getDateByStr(receivedate, "yyyy-MM-dd");
}catch(ParseException pe){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(13); // 存放列
cellTable.setType("wrong");
tablelist.add(cellTable);
importmsg = importmsg + "<br>第"+i+"行导入错误,第"+i+"行,第"+(titleMap.get("领取时间")+1)+"列"+title_arr[titleMap.get("领取时间")]+"格式不正确。";
i_count++;
}
if(importtype != null && "2".equalsIgnoreCase(importtype)){// 阻止型导入
// 正式导入数据
try{
importmsg = import_start(importmsg, user, areaInfo, i, deadname, deadsex, deadAge, deadidcard, deadDate, deadhomeAddress,
appname, appsex, appnation, appidcard, apptel, sumAllowance, receiveDate , operatename , shname);
// 不存在问题行和列
for(int c = 0 ; c < title_arr.length ; c++){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(c); // 存放列
cellTable.setType("right");
tablelist.add(cellTable);
}
}catch(Exception e2){
log.error("", e2.fillInStackTrace());
importmsg = importmsg + "<br>第"+i+"行导入错误,未知错误。";
continue;
}
}
}
}
if(importtype != null && "1".equalsIgnoreCase(importtype)){ // 提示型导入
/ 提示所有错误行,所有数据都不导入!如果数据全部正确,则全部导入
if(i_count == 0){
for(int i = 1 ; i <= i_num ; i++){
HSSFRow row_ = sheet.getRow(i);
String deadname = row_.getCell(0) == null?"":row_.getCell(0).getStringCellValue(); // 亡者姓名
String deadidcard = row_.getCell(1) == null?"":row_.getCell(1).getStringCellValue();// 亡者身份证号
String deadsex = row_.getCell(2) == null?"":row_.getCell(2).getStringCellValue();// 性别
String deadage = row_.getCell(3) == null?"":row_.getCell(3).getStringCellValue();// 死亡年龄
String deaddate = row_.getCell(4) == null?"":row_.getCell(4).getStringCellValue();// 死亡日期
String deadhomeAddress = row_.getCell(5) == null?"":row_.getCell(5).getStringCellValue();// 户籍所在地址【非必填项】
String appname = row_.getCell(6) == null?"":row_.getCell(6).getStringCellValue();// 申请人姓名
String appsex = row_.getCell(7) == null?"":row_.getCell(7).getStringCellValue();// 申请人性别
String appidcard = row_.getCell(8) == null?"":row_.getCell(8).getStringCellValue();// 申请人身份证号码
String appnation = row_.getCell(9) == null?"":row_.getCell(9).getStringCellValue();// 与亡者关系
String apptel = row_.getCell(10) == null?"":row_.getCell(10).getStringCellValue();// 联系电话
String receivemoney = row_.getCell(11) == null?"":row_.getCell(11).getStringCellValue();// 领取金额
String receivedate = row_.getCell(12) == null?"":row_.getCell(12).getStringCellValue();// 领取时间
String operatename = row_.getCell(13) == null?"":row_.getCell(13).getStringCellValue();// 经办人
String shname = row_.getCell(14) == null?"":row_.getCell(14).getStringCellValue();// 审核人
if (Tools.isEmpty(deadname) && Tools.isEmpty(deadidcard)
&& Tools.isEmpty(deadsex) && Tools.isEmpty(deadage)
&& Tools.isEmpty(deaddate) && Tools.isEmpty(appname)
&& Tools.isEmpty(appsex) && Tools.isEmpty(appidcard)
&& Tools.isEmpty(appnation) && Tools.isEmpty(apptel)
&& Tools.isEmpty(receivemoney) && Tools.isEmpty(receivedate)
) {
continue;
}
try{
import_start(importmsg, user, areaInfo, i, deadname, deadsex, new Long(deadage), deadidcard, Tools.getDateByStr(deaddate, "yyyy-MM-dd"), deadhomeAddress,
appname, appsex, appnation, appidcard, apptel, new Long(receivemoney), Tools.getDateByStr(receivedate, "yyyy-MM-dd") , operatename , shname);
importSuccessCount ++;
// 不存在问题行和列
for(int c = 0 ; c < title_arr.length ; c++){
cellTable = new CellTable();
cellTable.setRow_a(i); // 存放行
cellTable.setCell_b(c); // 存放列
cellTable.setType("right");
tablelist.add(cellTable);
}
}catch(Exception e2){
log.error("", e2.fillInStackTrace());
importmsg = importmsg + "<br>第"+i+"行导入错误,未知错误。";
continue;
}
}
}
}
}else{
importmsg = "无任何导入的数据。";
}
}else{
importmsg = "模板文件内容格式不正确,请勿修改模板格式。谢谢!";
}
if(importtype != null && "1".equalsIgnoreCase(importtype)){
if(i_count > 0){
importmsg = importmsg + "<br>由于本次导入存在错误数据,本次导入全部终止!";
importerror = "importerror";
}else{
importmsg = importmsg+"<br>导入成功。本次共导入"+importSuccessCount+"条。";
}
}
} catch (Exception e) {
importmsg = "文件导入错误,未知错误。";
e.printStackTrace();
}finally{
if(inxls != null){
inxls.close();
}
}
Long timeEnd = System.currentTimeMillis();
log.info("资金历史补录导入结束>>>>>>>>>>>>>>>>>>>>>>>>>>...");
log.info("#######导入结束!########,用时:" + (timeEnd - timeBegin ) + "s");
retExcel(request, user, tablelist, wb);
request.setAttribute("importerror", importerror);
request.setAttribute("importmsg", importmsg);
return mapping.findForward("importSuccess");
}
/***
* 回写导入模板Excel到服务器上
* @date Sep 18, 2013 4:42:32 PM
* @param request
* @param user
* @param tablelist
* @param wb
* @throws Exception
*/
private void retExcel(HttpServletRequest request , User user , List<CellTable> tablelist , HSSFWorkbook wb) throws Exception{
//回写文件到服务器 ///
FileOutputStream fileOutSave = null;
String webRootPath = request.getSession().getServletContext().getRealPath("");
String saveFilePath = "";
try{
if(System.getProperties().getProperty("os.name").toLowerCase().contains("windows")){
saveFilePath = webRootPath + "\\tempFiles\\money_receive_history_temp"+user.getId()+".xls";
}else{
saveFilePath = webRootPath + File.separator + "tempFiles/money_receive_history_temp"+user.getId()+".xls";
}
fileOutSave = new FileOutputStream(saveFilePath);
// ################################# //
HSSFSheet childSheet = wb.getSheetAt(0);// 读取第一个Sheet
HSSFCellStyle setBorder = wb.createCellStyle();
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
setBorder.setFillForegroundColor(HSSFColor.RED.index); //红色 错误的
HSSFCellStyle whiteStyle = wb.createCellStyle();
whiteStyle.setFillPattern(HSSFCellStyle.NO_FILL); // 填充单元格
whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index); //白色 正确的
// -----------------处理错误行,和正确行,标注红色和白色处理---------------- //
for(int b = 0 ; b < tablelist.size() ; b++){
CellTable c_table = (CellTable)tablelist.get(b);
if("wrong".equalsIgnoreCase(c_table.getType())){
HSSFRow row = childSheet.getRow(c_table.getRow_a());// 获得行对象
if(row != null){
HSSFCell ctemp =row.getCell(c_table.getCell_b()-1);// 第i行第0列
if(ctemp == null){
ctemp = row.createCell(c_table.getCell_b()-1);
}
ctemp.setCellStyle(setBorder);
}
}else{
HSSFRow row = childSheet.getRow(c_table.getRow_a());// 获得行对象
if(row != null){
HSSFCell ctemp =row.getCell(c_table.getCell_b());// 第i行第0列
if(ctemp == null){
ctemp = row.createCell(c_table.getCell_b());
}
ctemp.setCellStyle(whiteStyle);
}
}
}
//开始重新写入excel
log.info("###########开始写入excel###############");
wb.write(fileOutSave);
log.info("###########写入excel完成###############");
}catch(Exception e){
log.info("文件回写失败!");
e.printStackTrace();
}finally{
if(fileOutSave != null){
fileOutSave.close();
}
}
}