Java tutorial
package com.kysoft.cpsi.audit.service; import com.kysoft.cpsi.audit.entity.*; import com.kysoft.cpsi.audit.mapper.*; import com.kysoft.cpsi.repo.mapper.HcclMapper; import com.kysoft.cpsi.task.entity.Hcrw; import com.kysoft.cpsi.task.mapper.HcrwMapper; import net.sf.husky.exception.ExceptionUtils; import net.sf.husky.log.MongoLogger; import net.sf.husky.log.service.LogService; import net.sf.husky.utils.POIUtils; import org.apache.commons.collections.map.HashedMap; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; 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.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import javax.annotation.Resource; import java.io.InputStream; import java.math.BigDecimal; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Map; import java.util.UUID; @Service("selfCheckService") public class SelfCheckServiceImpl implements SelfCheckService { @Resource AnnualReportMapper annualReportMapper;// @Resource GuaranteeMapper guaranteeMapper;//? @Resource LicenseMapper licenseMapper;//? @Resource HomepageMapper homepageMapper;//? @Resource InvestmentMapper investmentMapper;// @Resource StockholderContributionMapper stockholderContributionMapper;// @Resource StockRightChangeMapper stockRightChangeMapper;//?? @Resource LogService logService; @Resource JsStockholderContributionMapper jsStockholderContributionMapper; @Resource JsGqbgMapper jsGqbgMapper; @Resource JsXzcfMapper jsXzcfMapper; @Resource JsLicenseMapper jsLicenseMapper; @Resource JsZscqMapper jsZscqMapper; @Resource HcrwMapper hcrwMapper; @Resource HcclMapper hcclMapper; @Value(value = "${import.selfdata.flag}") private String importFlag; /** * ?? * ? * ? yyyy-MM-dd * */ private Date getDateValue(String dateStr) throws Exception { if (null != dateStr && !dateStr.equals("")) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); return sdf.parse(dateStr); } else { return null; } } private Float parseFloat(String floatStr) { if (null != floatStr && !floatStr.trim().equals("")) { return Float.parseFloat(floatStr); } else { return Float.parseFloat("0.00"); } } private Integer parseInt(String floatStr) { if (null != floatStr && !floatStr.trim().equals("")) { return Integer.parseInt(floatStr); } else { return 0; } } private BigDecimal parseBigDecimal(String bigDecimalStr) { DecimalFormat df = new java.text.DecimalFormat("#.00"); if (null != bigDecimalStr && !bigDecimalStr.trim().equals("")) { return new BigDecimal(df.format(new BigDecimal(bigDecimalStr))); } else { return new BigDecimal("0.00"); } } //? private void nianbaoWangzhiwangdian(Hcrw hcrw, Sheet sheetZCB, Integer nd) { try { //? if (null != POIUtils.getCellFormatValue(sheetZCB.getRow(7).getCell(3)).trim() && !POIUtils.getCellFormatValue(sheetZCB.getRow(7).getCell(3)).trim().equals("")) { Homepage homepage = new Homepage(); homepage.setNd(nd); homepage.setXydm(hcrw.getHcdwXydm()); homepage.setId(UUID.randomUUID().toString().replace("-", "")); homepage.setName("??"); homepage.setType("??"); homepage.setWz(POIUtils.getCellFormatValue(sheetZCB.getRow(7).getCell(3))); //?? homepageMapper.deleteByTaskIdNd(hcrw.getId(), nd); //?? homepageMapper.insert2(homepage); } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException( "[??][??]??;"); } } //? private void nianbao(Hcrw hcrw, Sheet sheetZCFZB, Sheet sheetZCB, Sheet sheetLRB, Integer nd) { String errorMsg = ""; try { DecimalFormat decimalFormat = new DecimalFormat("#.00"); //? AnnualReport annualReport = new AnnualReport(); annualReport.setNd(nd); annualReport.setXydm(hcrw.getHcdwXydm()); annualReport.setQymc(hcrw.getHcdwName()); errorMsg = "??"; annualReport.setSyzqyhj(parseFloat(decimalFormat .format(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(47).getCell(10))) / 10000))); //? errorMsg = "?"; Float lrze = parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(6).getCell(3))) - parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(9).getCell(3))) - parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(12).getCell(3))) - parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(13).getCell(3))) - parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(14).getCell(3))) - parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(15).getCell(3))) - parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(16).getCell(3))) + parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(18).getCell(3))) + parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(19).getCell(3))) + parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(21).getCell(3))) - parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(23).getCell(3))); annualReport.setLrze(parseFloat(decimalFormat.format(lrze / 10000))); // errorMsg = ""; Float jlr = lrze - parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(26).getCell(3))); annualReport.setJlr(parseFloat(decimalFormat.format(jlr / 10000))); errorMsg = "?"; annualReport.setYyzsr(parseFloat(decimalFormat .format(parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(6).getCell(3))) / 10000))); errorMsg = "?"; annualReport.setZyywsr(parseFloat(decimalFormat .format(parseFloat(POIUtils.getStringCellValue(sheetLRB.getRow(7).getCell(3))) / 10000))); errorMsg = "?"; annualReport.setNsze(parseFloat( decimalFormat.format(parseFloat(POIUtils.getStringCellValue(sheetZCB.getRow(9).getCell(5)))))); errorMsg = "?"; annualReport.setFzze(parseFloat(decimalFormat .format(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(35).getCell(10))) / 10000))); errorMsg = "?"; annualReport.setZcze(parseFloat(decimalFormat .format(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5))) / 10000))); errorMsg = "?"; annualReport.setTxdz(POIUtils.getStringCellValue(sheetZCB.getRow(6).getCell(3))); errorMsg = "?"; annualReport.setYzbm(POIUtils.getStringCellValue(sheetZCB.getRow(8).getCell(5))); errorMsg = "??"; annualReport.setLxdh(POIUtils.getStringCellValue(sheetZCB.getRow(5).getCell(5))); errorMsg = "?"; annualReport.setMail(POIUtils.getStringCellValue(sheetZCB.getRow(6).getCell(5))); errorMsg = ""; annualReport.setCyrs(parseInt(POIUtils.getStringCellValue(sheetZCB.getRow(7).getCell(5)))); errorMsg = ""; annualReport.setGxbysGg(0); errorMsg = "??"; annualReport.setGxbysJy(0); errorMsg = ""; annualReport.setTysbsGg(0); errorMsg = "??"; annualReport.setTysbsJy(0); errorMsg = ""; annualReport.setCjrsGg(0); errorMsg = "??"; annualReport.setCjrsJy(0); errorMsg = "?"; annualReport.setZjysGg(0); errorMsg = "???"; annualReport.setZjysJy(0); errorMsg = "???"; annualReport.setJyzt(POIUtils.getStringCellValue(sheetZCB.getRow(8).getCell(3))); errorMsg = "??"; annualReport.setSftzgmgq(POIUtils.getStringCellValue(sheetZCB.getRow(13).getCell(4))); errorMsg = "???"; annualReport.setSfydwdbxx(POIUtils.getStringCellValue(sheetZCB.getRow(14).getCell(4))); //?? annualReportMapper.deleteByTaskIdNd2(hcrw.getId(), nd); //?? annualReportMapper.insert2(annualReport); } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException( "[??? " + errorMsg + "]??;"); } } //- private void gudongchuzi(Hcrw hcrw, Sheet sheetGDCZ, Integer nd) { try { stockholderContributionMapper.deleteByTaskIdNd(hcrw.getId(), nd); // int rowNum = sheetGDCZ.getLastRowNum(); for (int i = 6; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(2)).trim().equals("")) { StockholderContribution stockholderContribution = new StockholderContribution(); stockholderContribution.setId(UUID.randomUUID().toString().replace("-", "")); stockholderContribution.setNd(nd); stockholderContribution.setXydm(hcrw.getHcdwXydm()); stockholderContribution.setGd(POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(2))); stockholderContribution.setRjcze(null); stockholderContribution.setRjczdqsj(null); stockholderContribution.setRjczfs(null); stockholderContribution .setSjcze(parseFloat(POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(3)))); stockholderContribution.setSjczsj(POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(4))); stockholderContribution.setSjczfs(POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(5))); stockholderContributionMapper.insert2(stockholderContribution); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ?? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException("[??]??;"); } } //-?? private void guquanbiangeng(Hcrw hcrw, Sheet sheetGQBG, Integer nd) { try { stockRightChangeMapper.deleteByTaskIdNd(hcrw.getId(), nd); // int rowNum = sheetGQBG.getLastRowNum(); for (int i = 6; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheetGQBG.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheetGQBG.getRow(i).getCell(2)).trim().equals("")) { StockRightChange stockRightChange = new StockRightChange(); stockRightChange.setId(UUID.randomUUID().toString().replace("-", "")); stockRightChange.setNd(nd); stockRightChange.setXydm(hcrw.getHcdwXydm()); stockRightChange.setGd(POIUtils.getStringCellValue(sheetGQBG.getRow(i).getCell(2))); stockRightChange.setBgqGqbl( parseFloat(POIUtils.getStringCellValue(sheetGQBG.getRow(i).getCell(3))) * 100); stockRightChange.setBghGqbl( parseFloat(POIUtils.getStringCellValue(sheetGQBG.getRow(i).getCell(4))) * 100); stockRightChange.setBgrq(POIUtils.getStringCellValue(sheetGQBG.getRow(i).getCell(5))); stockRightChangeMapper.insert2(stockRightChange); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ???? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException("[????]??;"); } } //- private void duiwaitouzi(Hcrw hcrw, Sheet sheetDWTZ, Integer nd) { try { investmentMapper.deleteByTaskIdNd(hcrw.getId(), nd); // int rowNum = sheetDWTZ.getLastRowNum(); for (int i = 5; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheetDWTZ.getRow(i).getCell(3)).trim() && !POIUtils.getStringCellValue(sheetDWTZ.getRow(i).getCell(3)).trim().equals("")) { Investment investment = new Investment(); investment.setId(UUID.randomUUID().toString().replace("-", "")); investment.setNd(nd); investment.setXydm(hcrw.getHcdwXydm()); investment.setTzqymc(POIUtils.getStringCellValue(sheetDWTZ.getRow(i).getCell(2))); investment.setTzqyZch(POIUtils.getStringCellValue(sheetDWTZ.getRow(i).getCell(3))); investmentMapper.insert2(investment); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ????? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException("[?????]??;"); } } //-? private void duiwandanbao(Hcrw hcrw, Sheet sheetDWDB, Integer nd) { try { guaranteeMapper.deleteByTaskIdNd(hcrw.getId(), nd); // int rowNum = sheetDWDB.getLastRowNum(); for (int i = 5; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(2)).trim().equals("")) { Guarantee guarantee = new Guarantee(); guarantee.setId(UUID.randomUUID().toString().replace("-", "")); guarantee.setNd(nd); guarantee.setXydm(hcrw.getHcdwXydm()); guarantee.setZqr(POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(2))); guarantee.setZwr(POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(3))); guarantee.setZzqzl(POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(4))); guarantee.setZzqse(parseFloat(POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(5)))); guarantee.setLxzwqx(POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(6))); guarantee.setBzqj(POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(7))); guarantee.setBzfs(POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(8))); guarantee.setBzdbfw(POIUtils.getStringCellValue(sheetDWDB.getRow(i).getCell(9))); guaranteeMapper.insert2(guarantee); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ?? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException("[??]??;"); } } //-? private void xingzhengxuke(Hcrw hcrw, Sheet sheetXZXK, Integer nd) { try { licenseMapper.deleteByTaskIdNd(hcrw.getId(), nd); // int rowNum = sheetXZXK.getLastRowNum(); for (int i = 5; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheetXZXK.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheetXZXK.getRow(i).getCell(2)).trim().equals("")) { License license = new License(); license.setId(UUID.randomUUID().toString().replace("-", "")); license.setNd(nd); license.setXydm(hcrw.getHcdwXydm()); license.setXkwjmc(POIUtils.getStringCellValue(sheetXZXK.getRow(i).getCell(3))); license.setYxq(POIUtils.getStringCellValue(sheetXZXK.getRow(i).getCell(4)) + "-" + POIUtils.getStringCellValue(sheetXZXK.getRow(i).getCell(5))); licenseMapper.insert2(license); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ?????? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException("[??????]??;"); } } //?- private void jsGudongchuzhi(Hcrw hcrw, Sheet sheet) throws Exception { try { jsStockholderContributionMapper.deleteByTaskId2(hcrw.getId()); // int rowNum = sheet.getLastRowNum(); for (int i = 6; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim().equals("")) { JsStockholderContribution jsStockholderContribution = new JsStockholderContribution(); jsStockholderContribution.setId(""); jsStockholderContribution.setXydm(hcrw.getHcdwXydm()); jsStockholderContribution.setGd(POIUtils.getStringCellValue(sheet.getRow(i).getCell(2))); jsStockholderContribution .setBgrq(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4)))); jsStockholderContribution.setRje(null); jsStockholderContribution .setSje(new BigDecimal(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3)))); jsStockholderContribution.setGssj(null); jsStockholderContribution.setRjczfs(null); jsStockholderContribution.setRjcze(null); jsStockholderContribution.setRjczrq(null); jsStockholderContribution.setSjczfs(POIUtils.getStringCellValue(sheet.getRow(i).getCell(5))); jsStockholderContribution .setSjcze(new BigDecimal(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3)))); jsStockholderContribution .setSjczrq(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4)))); jsStockholderContributionMapper.insert2(jsStockholderContribution); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ?? ? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException("[??](?)??;"); } } //?-?? private void jsGuquanbiangeng(Hcrw hcrw, Sheet sheet) throws Exception { try { jsGqbgMapper.deleteByTaskId2(hcrw.getId()); // int rowNum = sheet.getLastRowNum(); for (int i = 6; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim().equals("")) { JsGqbg jsGqbg = new JsGqbg(); jsGqbg.setId(""); jsGqbg.setXydm(hcrw.getHcdwXydm()); jsGqbg.setGd(POIUtils.getStringCellValue(sheet.getRow(i).getCell(2))); jsGqbg.setBgrq(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(5)))); jsGqbg.setBgqbl(new BigDecimal(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3)))); jsGqbg.setBghbl(new BigDecimal(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4)))); jsGqbg.setGssj(null); jsGqbgMapper.insert2(jsGqbg); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ???? ? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException( "[????](?)??;"); } } //?- private void jsXingzhengchufa(Hcrw hcrw, Sheet sheet) throws Exception { try { jsXzcfMapper.deleteByTaskId2(hcrw.getId()); // int rowNum = sheet.getLastRowNum(); for (int i = 6; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim().equals("")) { JsXzcf jsXzcf = new JsXzcf(); jsXzcf.setId(""); jsXzcf.setXydm(hcrw.getHcdwXydm()); jsXzcf.setXzcfjdswh(POIUtils.getStringCellValue(sheet.getRow(i).getCell(2))); jsXzcf.setWflx(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3))); jsXzcf.setXzcfnr(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4))); jsXzcf.setCfjg(POIUtils.getStringCellValue(sheet.getRow(i).getCell(5))); jsXzcf.setCfrq(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(6)))); jsXzcf.setBz(null); jsXzcf.setGssj(null); jsXzcfMapper.insert2(jsXzcf); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ?? ? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException("[??](?)??;"); } } //?-? private void jsXingzhengxuke(Hcrw hcrw, Sheet sheet) throws Exception { try { jsLicenseMapper.deleteByTaskId2(hcrw.getId()); // int rowNum = sheet.getLastRowNum(); for (int i = 5; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim().equals("")) { JsLicense jsXjsLicensecf = new JsLicense(); jsXjsLicensecf.setId(""); jsXjsLicensecf.setXydm(hcrw.getHcdwXydm()); jsXjsLicensecf.setXkwjbh(POIUtils.getStringCellValue(sheet.getRow(i).getCell(2))); jsXjsLicensecf.setXkwjmc(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3))); jsXjsLicensecf.setYxqKs(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4)))); jsXjsLicensecf.setYxqJs(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(5)))); jsXjsLicensecf.setXkjg(POIUtils.getStringCellValue(sheet.getRow(i).getCell(6))); jsXjsLicensecf.setXknr(""); jsXjsLicensecf.setZt(POIUtils.getStringCellValue(sheet.getRow(i).getCell(7))); jsXjsLicensecf.setXq(null); jsXjsLicensecf.setHdrq(null); jsXjsLicensecf.setGssj(null); jsLicenseMapper.insert2(jsXjsLicensecf); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ?????? ? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException( "[??????](?)??;"); } } //?-? private void jsZhishichanquan(Hcrw hcrw, Sheet sheet) throws Exception { try { jsZscqMapper.deleteByTaskId2(hcrw.getId()); // int rowNum = sheet.getLastRowNum(); for (int i = 5; i < rowNum; i++) { if (null != POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim() && !POIUtils.getStringCellValue(sheet.getRow(i).getCell(2)).trim().equals("")) { JsZscq jsZscq = new JsZscq(); jsZscq.setId(""); jsZscq.setXydm(hcrw.getHcdwXydm()); jsZscq.setQymc(POIUtils.getStringCellValue(sheet.getRow(i).getCell(3))); jsZscq.setZl(POIUtils.getStringCellValue(sheet.getRow(i).getCell(4))); jsZscq.setCzrmc(POIUtils.getStringCellValue(sheet.getRow(i).getCell(5))); jsZscq.setZqrmc(POIUtils.getStringCellValue(sheet.getRow(i).getCell(6))); jsZscq.setZqdjrq(getDateValue(POIUtils.getStringCellValue(sheet.getRow(i).getCell(7)))); jsZscq.setZt(POIUtils.getStringCellValue(sheet.getRow(i).getCell(8))); jsZscq.setBhqk(POIUtils.getStringCellValue(sheet.getRow(i).getCell(9))); jsZscq.setGssj(null); jsZscqMapper.insert2(jsZscq); } } } catch (Exception e) { e.printStackTrace(); MongoLogger.warn("?? ?? ? ", ExceptionUtils.getStackTrace(e), hcrw.getId()); throw new RuntimeException("[??](?)??;"); } } private BigDecimal getSumValue(Sheet sheet, int columnId, int startRow, int endRow) { BigDecimal result = new BigDecimal("0"); for (int i = startRow; i <= endRow; i++) { result = result.add(parseBigDecimal(POIUtils.getCellFormatValue(sheet.getRow(i).getCell(columnId)))); } return result; } private void validateExcel(String hcrwId, Workbook workbook) throws Exception { Sheet sheetZCFZB = workbook.getSheet(""); Sheet sheetLRB = workbook.getSheet(""); String errorMsg = ""; /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4))).compareTo(new BigDecimal("0"))==0 && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5))).compareTo(new BigDecimal("0"))==0 && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9))).compareTo(new BigDecimal("0"))==0 && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10))).compareTo(new BigDecimal("0"))==0){ errorMsg=errorMsg+"[E49 F49 J49 K49?0] "; }else{ }*/ if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4))) - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9)))) < 0.00000001) { } else { errorMsg = errorMsg + "?E49??(?)?J49"; } if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5))) - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10)))) < 0.00000001) { } else { errorMsg = errorMsg + "F49??(?)K49"; } if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(3))) - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10)))) < 0.00000001 || parseBigDecimal(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(3))) .compareTo(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10))) .subtract(parseBigDecimal( POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9))))) == 0) { } else { errorMsg = errorMsg + "???D41??K47";// ???D41?K47-??J47] } if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(4))) - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9)))) < 0.00000001) { } else { errorMsg = errorMsg + "???E41???J47"; } /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(4))).compareTo(getSumValue(sheetZCFZB,4,7,19))==0){ }else{ errorMsg=errorMsg+"E8-E20??E21"; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(5))).compareTo(getSumValue(sheetZCFZB,5,7,19))==0){ }else{ errorMsg=errorMsg+"[F8-F20??F21] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(4))).compareTo(getSumValue(sheetZCFZB,4,22,38))==0){ }else{ errorMsg=errorMsg+"[E23-E39??E40] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(5))).compareTo(getSumValue(sheetZCFZB,5,22,38))==0){ }else{ errorMsg=errorMsg+"[F23-F39??F40] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4))).compareTo( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(4))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(4)))))==0){ }else{ errorMsg=errorMsg+"[E49?E21+E40] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5))).compareTo( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(5))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(5)))))==0){ }else{ errorMsg=errorMsg+"[F49?F21+F40] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(9))).compareTo(getSumValue(sheetZCFZB,9,7,20))==0){ }else{ errorMsg=errorMsg+"[J8-J21??J22] "; }*/ /* if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(10))).compareTo(getSumValue(sheetZCFZB,10,7,20))==0){ }else{ errorMsg=errorMsg+"[k8-k21??k22] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(9))).compareTo(getSumValue(sheetZCFZB,9,27,33).add(getSumValue(sheetZCFZB,9,23,24)))==0){ }else{ errorMsg=errorMsg+"[J24-J25J28-J34?J35] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(10))).compareTo(getSumValue(sheetZCFZB,10,27,33).add(getSumValue(sheetZCFZB,10,23,24)))==0){ }else{ errorMsg=errorMsg+"[K24-K25K28-K34?K35] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(35).getCell(9))).compareTo( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(9))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(9)))))==0){ }else{ errorMsg=errorMsg+"[J22+J35?J36] "; }*/ /* if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(35).getCell(10))).compareTo( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(10))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(10)))))==0){ }else{ errorMsg=errorMsg+"[K22+K35?K36] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(47).getCell(9))).compareTo( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(37).getCell(9))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(38).getCell(9)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(41).getCell(9)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(43).getCell(9)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(44).getCell(9)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(45).getCell(9)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9)))))==0){ }else{ errorMsg=errorMsg+"[J38+J39+J42+J44+J45+J46+J47?J48] "; }*/ /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(47).getCell(10))).compareTo( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(37).getCell(10))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(38).getCell(10)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(41).getCell(10)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(43).getCell(10)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(44).getCell(10)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(45).getCell(10)))).add( parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10)))))==0){ }else{ errorMsg=errorMsg+"[K38+K39+K42+K44+K45+K46+K47?K48] "; }*/ /*if(Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4)))-parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9))))<0.00000001){ }else{ errorMsg=errorMsg+"[?E49??J49] "; }*/ /*if(Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5)))-parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10))))<0.00000001){ }else{ errorMsg=errorMsg+"[F49?K49] "; }*/ /*if(!errorMsg.equals("")){ throw new RuntimeException(errorMsg); }*/ if (null != errorMsg && !errorMsg.equals("")) { hcrwMapper.updateZcbResultByPrimaryKey(hcrwId, " ??" + errorMsg); } else { hcrwMapper.updateZcbResultByPrimaryKey(hcrwId, ""); } } @Override @Transactional public void uploadSelfCheckData(InputStream is, String hcrwId, String fileName, Integer nd) throws Exception { Hcrw hcrw = hcrwMapper.selectByPrimaryKey(hcrwId); Workbook workbook = null; if (fileName.endsWith("xls")) { POIFSFileSystem fs = new POIFSFileSystem(is); workbook = new HSSFWorkbook(fs); } else if (fileName.endsWith("xlsx")) { workbook = new XSSFWorkbook(is); } //? validateExcel(hcrwId, workbook); if (importFlag.equals("1")) { //? nianbaoWangzhiwangdian(hcrw, workbook.getSheet("??"), nd); //? nianbao(hcrw, workbook.getSheet(""), workbook.getSheet("??"), workbook.getSheet(""), nd); // gudongchuzi(hcrw, workbook.getSheet("??"), nd); //?? guquanbiangeng(hcrw, workbook.getSheet("????"), nd); // duiwaitouzi(hcrw, workbook.getSheet("?????"), nd); //? duiwandanbao(hcrw, workbook.getSheet("??"), nd); //? xingzhengxuke(hcrw, workbook.getSheet("??????"), nd); jsGudongchuzhi(hcrw, workbook.getSheet("??")); jsGuquanbiangeng(hcrw, workbook.getSheet("????")); jsXingzhengxuke(hcrw, workbook.getSheet("??????")); jsZhishichanquan(hcrw, workbook.getSheet("??")); jsXingzhengchufa(hcrw, workbook.getSheet("??")); } workbook.close(); } @Override public Map<String, Object> getDXNHccl(Map<String, Object> params) { return hcclMapper.getDXNHcsx(params).get(0); } @Override public void judgeRepeatExcle(InputStream is, int firstRowNum, int colNum, String fileName) throws Exception { Map<String, Object> sheetValues = new HashedMap(); Workbook workbook = null; if (fileName.endsWith("xls")) { POIFSFileSystem fs = new POIFSFileSystem(is); workbook = new HSSFWorkbook(fs); } else if (fileName.endsWith("xlsx")) { workbook = new XSSFWorkbook(is); } Sheet sheet = workbook.getSheetAt(0); for (int i = firstRowNum; i < sheet.getLastRowNum() + 1; i++) { Row row = sheet.getRow(i - 1); System.out.println(i); Cell cell = row.getCell(colNum - 1); if (null != cell && null != POIUtils.getStringCellValue(cell) && !POIUtils.getStringCellValue(cell).equals("")) { if (sheetValues.containsKey(POIUtils.getStringCellValue(cell))) { throw new RuntimeException( POIUtils.getStringCellValue(cell) + "??????"); } else { sheetValues.put(POIUtils.getStringCellValue(cell), i); } } } } }