Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package data.services; import data.dao.CarCompletionOptionDao; import data.dao.CarOptionValueDao; import data.entity.CarCompletionOption; import data.entity.CarOptionValue; import data.entity.CarProperty; import data.entity.PropertyName; import data.services.parent.PrimService; import java.io.File; import java.io.FileInputStream; import java.util.Iterator; import java.util.List; import java.util.Objects; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.context.annotation.ScopedProxyMode; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import support.StringAdapter; /** * * @author bezdatiuzer */ @Service @Transactional @Scope(value = "request", proxyMode = ScopedProxyMode.TARGET_CLASS) public class CarOptionValueService extends PrimService { @Autowired private CarOptionValueDao carOptionValueDao; @Autowired private CarCompletionOptionDao carCompletionOptionDao; public List<CarCompletionOption> getUniqueCCOs() { return carOptionValueDao.getUniqueCCOs(); } public List<CarOptionValue> getUniqueOptionNames(Long ccoId) throws Exception { return carOptionValueDao.getUniqueOptionNames(ccoId); } public HSSFWorkbook getXls() { Logger log = Logger.getLogger(this.getClass()); try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("FirstSheet"); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(0).setCellValue("ID"); rowhead.createCell(1).setCellValue("UID"); rowhead.createCell(2).setCellValue("??"); rowhead.createCell(3).setCellValue("??"); rowhead.createCell(4).setCellValue(""); rowhead.createCell(5).setCellValue(""); rowhead.createCell(6).setCellValue(""); rowhead.createCell(7).setCellValue("?"); rowhead.createCell(8).setCellValue(""); rowhead.createCell(9).setCellValue("?"); List<CarOptionValue> covlist = getUniqueOptionNames(Long.valueOf(0)); int n = 1; if (!covlist.isEmpty()) { for (CarOptionValue cov : covlist) { HSSFRow rowbody = sheet.createRow((short) n); String desc = cov.getDescription(); if (desc.equals("")) { desc = " ??"; } rowbody.createCell(0).setCellValue(StringAdapter.getString(cov.getId())); rowbody.createCell(1).setCellValue(StringAdapter.getString(cov.getCCO().getUid())); rowbody.createCell(2).setCellValue(StringAdapter.getString(cov.getCCO().getTitle())); rowbody.createCell(3).setCellValue(desc); rowbody.createCell(4).setCellValue(StringAdapter.getString(cov.getRadical())); rowbody.createCell(5) .setCellValue(StringAdapter.getString(cov.getParamValue()).replace(".", ",")); rowbody.createCell(6).setCellValue(StringAdapter.getString(cov.getPercentValue())); rowbody.createCell(7).setCellValue(StringAdapter.getString(cov.getAudial())); rowbody.createCell(8).setCellValue(StringAdapter.getString(cov.getVisual())); rowbody.createCell(9).setCellValue(StringAdapter.getString(cov.getKinestet())); n++; } ; } return workbook; } catch (Exception e) { log.warn("HSSFWorkbook.getXls()", e); } return null; } public void updateFromXml(File fl) throws Exception { try { FileInputStream fi = new FileInputStream(fl); int i = 0; int m = 1; try { HSSFWorkbook workbook = new HSSFWorkbook(fi); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> it = sheet.iterator(); it.next(); while (it.hasNext()) { Row row = it.next(); m++; Cell idCell = row.getCell(0); Long covId = null; if (idCell != null) { String supstr = StringAdapter.HSSFSellValue(idCell).trim(); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { covId = StringAdapter.toLong(supstr); } } CarOptionValue cov = carOptionValueDao.find(covId); if (cov != null) { Cell radc = row.getCell(4); String rad = ""; if (radc != null) { rad = StringAdapter.HSSFSellValue(radc).trim(); if (rad.contains(".")) { int point = rad.indexOf("."); rad = rad.substring(0, point); } if (rad.trim().equals("0")) { rad = ""; } else { rad = rad.replace(" ", ""); } } Cell valc = row.getCell(5); Double val = (double) 0; if (valc != null) { String valstr = StringAdapter.HSSFSellValue(valc).replace(",", ".").trim(); val = StringAdapter.toDouble(valstr); } Cell percc = row.getCell(6); Long perc = (long) 0; if (percc != null) { String percstr = StringAdapter.HSSFSellValue(percc); if (percstr.contains(".")) { int point = percstr.indexOf("."); percstr = percstr.substring(0, point); } if (!percstr.equals("")) { perc = StringAdapter.toLong(percstr); } } Cell ac = row.getCell(7); Integer a = 0; if (ac != null) { String supstr = StringAdapter.HSSFSellValue(ac); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { a = Integer.valueOf(supstr); } } Cell vc = row.getCell(8); Integer v = 0; if (vc != null) { String supstr = StringAdapter.HSSFSellValue(vc); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { v = Integer.valueOf(supstr); } } Cell kc = row.getCell(9); Integer k = 0; if (kc != null) { String supstr = StringAdapter.HSSFSellValue(kc); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { k = Integer.valueOf(supstr); } } if (!Objects.equals(cov.getAudial(), a) || !Objects.equals(cov.getVisual(), v) || !Objects.equals(cov.getKinestet(), k) || cov.getParamValue().compareTo(val) != 0 || !cov.getRadical().equals(rad) || cov.getPercentValue().compareTo(perc) != 0) { updateByDescAndCcoId(covId, perc, val, a, v, k, rad, m); i++; } } else { addError("? ? ? " + covId + ", ? " + m + "; "); } } workbook.close(); addError(" " + i + " ?; ? ?: " + m + "; "); } catch (Exception e) { //addError(StringAdapter.getStackTraceException(e) + "str:"+i+"; "); throw new Exception(StringAdapter.getStackTraceException(e) + "str:" + i + "; "); } fi.close(); } catch (Exception e) { //addError(" xml"); //addError(e.getMessage()); throw new Exception(StringAdapter.getStackTraceException(e)); } } public void updateByDescAndCcoId(Long covId, Long percentValue, Double paramValue, Integer a, Integer v, Integer k, String rad, int row) throws Exception { /*addError("id="+StringAdapter.getString(ccoId)); addError("desc="+desc);*/ //CarCompletionOption cco = carCompletionOptionDao.find(ccoId); if (covId != null) { //addError(cco.getTitle()); /*CarOptionValue cov = new CarOptionValue(); cov.setDescription(desc); cov.setCCO(cco);*/ List<CarOptionValue> covList = carOptionValueDao.findByDescAndCco(covId); //addError(" size="+StringAdapter.getString(covList.size())+"; "); for (CarOptionValue ucov : covList) { ucov.setAudial(a); ucov.setVisual(v); ucov.setKinestet(k); ucov.setPercentValue(percentValue); ucov.setParamValue(paramValue); ucov.setRadical(rad); if (validate(ucov, " " + row + " - ; ")) { carOptionValueDao.update(ucov); } else { throw new Exception("!" + row + "!"); } } } } }