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.CarDao; import data.dao.FreeOptionDao; import data.dao.MarkDao; import data.dao.SubModelDao; import data.entity.Car; import data.entity.FreeOption; import data.entity.Mark; import data.entity.Model; import data.entity.SubModel; import data.services.parent.PrimService; import java.io.File; import java.io.FileInputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import logic.PropertyType; 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 FreeOptionService extends PrimService { @Autowired private FreeOptionDao freeOptionDao; @Autowired private CarDao carDao; @Autowired private MarkDao markDao; @Autowired private SubModelDao subModelDao; public void create(FreeOption fo) throws Exception { if (validate(fo)) { freeOptionDao.save(fo); } } private HashMap<Long, SubModel> getBodies() { HashMap<Long, SubModel> bodyMap = new HashMap(); List<SubModel> smlist = subModelDao.getAllSubs(); for (SubModel sm : smlist) { bodyMap.put(sm.getQutoModelSubId(), sm); } return bodyMap; } public HSSFWorkbook getXls() throws Exception { try { HashMap<Long, SubModel> bodyMap = getBodies(); List<Mark> marks = markDao.getAllAsc("titleRus"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheetExample = workbook.createSheet(""); HSSFRow rowEx = sheetExample.createRow(0); rowEx.createCell(0).setCellValue( "1. ? 3 . '' , ???? ?? ? ?? (feature), ?(property), (car completion option), , ? '?'"); rowEx.createCell(2).setCellValue( "2. ? ? ? . ?? . ? , ? ?. ?? , ? ?."); //rowEx.createCell(5).setCellValue(StringAdapter.getString(bodyMap.keySet().size())); HSSFRow rowCarHeadE = sheetExample.createRow(1); rowCarHeadE.createCell(0).setCellValue("CAR_ID"); rowCarHeadE.createCell(1).setCellValue(""); rowCarHeadE.createCell(2).setCellValue("????"); HSSFRow rowCarE = sheetExample.createRow(2); rowCarE.createCell(0).setCellValue("123"); rowCarE.createCell(1).setCellValue("?- "); rowCarE.createCell(2).setCellValue("- "); HSSFRow rowOptionHeadE = sheetExample.createRow(3); rowOptionHeadE.createCell(0).setCellValue("OPTION_ID"); rowOptionHeadE.createCell(1).setCellValue("UID"); rowOptionHeadE.createCell(2).setCellValue(""); rowOptionHeadE.createCell(3).setCellValue("?????"); rowOptionHeadE.createCell(4).setCellValue("??"); rowOptionHeadE.createCell(5).setCellValue("??"); rowOptionHeadE.createCell(6).setCellValue("??"); rowOptionHeadE.createCell(7).setCellValue("???"); rowOptionHeadE.createCell(8).setCellValue("?? ???"); rowOptionHeadE.createCell(9).setCellValue("??"); rowOptionHeadE.createCell(10).setCellValue("?"); rowOptionHeadE.createCell(11).setCellValue("?"); HSSFRow rowOptionBodyE = sheetExample.createRow(4); rowOptionBodyE.createCell(0).setCellValue("12"); rowOptionBodyE.createCell(1).setCellValue("0102001"); rowOptionBodyE.createCell(2).setCellValue("?"); rowOptionBodyE.createCell(3).setCellValue(""); rowOptionBodyE.createCell(4).setCellValue( " ? ???? ? ??"); rowOptionBodyE.createCell(5).setCellValue("10000"); rowOptionBodyE.createCell(6).setCellValue("1234"); rowOptionBodyE.createCell(7).setCellValue("0"); rowOptionBodyE.createCell(8).setCellValue("75"); rowOptionBodyE.createCell(9).setCellValue("35"); rowOptionBodyE.createCell(10).setCellValue("10"); rowOptionBodyE.createCell(11).setCellValue("5"); HSSFRow rowOptionExplainsHead = sheetExample.createRow(6); HSSFRow rowOptionExplainsBody1 = sheetExample.createRow(7); HSSFRow rowOptionExplainsBody2 = sheetExample.createRow(8); HSSFRow rowOptionExplainsBody3 = sheetExample.createRow(9); HSSFRow rowOptionExplainsBody4 = sheetExample.createRow(10); rowOptionExplainsHead.createCell(0).setCellValue( "? ? ?:"); rowOptionExplainsBody1.createCell(0).setCellValue("?:"); rowOptionExplainsBody1.createCell(1).setCellValue("?/"); rowOptionExplainsBody2.createCell(0).setCellValue("??:"); rowOptionExplainsBody2.createCell(1).setCellValue("??/?"); rowOptionExplainsBody3.createCell(0).setCellValue("?:"); rowOptionExplainsBody3.createCell(1).setCellValue("?/"); rowOptionExplainsBody4.createCell(0).setCellValue(":"); rowOptionExplainsBody4.createCell(1).setCellValue("/"); if (!marks.isEmpty()) { for (Mark mark : marks) { int r = 0; HSSFSheet sheet = workbook.createSheet(mark.getTitleRus()); for (Model model : mark.getModels()) { for (Car car : model.getCars()) { String body = ""; String title = ""; String smClass = ""; String smType = ""; String dc = ""; SubModel sm = bodyMap.get(car.getCmsqId()); if (sm != null) { body = StringAdapter.getString(sm.getBody()); title = StringAdapter.getString(sm.getTitle()); smClass = StringAdapter.getString(sm.getCarClass()); smType = StringAdapter.getString(sm.getType()); dc = StringAdapter.getString(sm.getDoorsCount()); } /*if(body==null){ body="? "; }*/ HSSFRow rowCarhead = sheet.createRow(r); rowCarhead.createCell(0).setCellValue("CAR_ID"); rowCarhead.createCell(1).setCellValue(""); rowCarhead.createCell(2).setCellValue("?????"); rowCarhead.createCell(3).setCellValue(" ?"); rowCarhead.createCell(4).setCellValue("????"); rowCarhead.createCell(5).setCellValue(""); rowCarhead.createCell(6).setCellValue("?"); rowCarhead.createCell(7).setCellValue(" "); r++; HSSFRow rowCar = sheet.createRow(r); rowCar.createCell(0).setCellValue(car.getId()); rowCar.createCell(1).setCellValue(model.getTitle()); rowCar.createCell(2).setCellValue(car.getTitle()); rowCar.createCell(3).setCellValue(body); rowCar.createCell(4).setCellValue(title); rowCar.createCell(5).setCellValue(smType); rowCar.createCell(6).setCellValue(smClass); rowCar.createCell(7).setCellValue(dc); r++; HSSFRow rowOptionHead = sheet.createRow(r); rowOptionHead.createCell(0).setCellValue("OPTION_ID"); rowOptionHead.createCell(1).setCellValue("UID"); rowOptionHead.createCell(2).setCellValue(""); rowOptionHead.createCell(3).setCellValue("?????"); rowOptionHead.createCell(4).setCellValue("??"); rowOptionHead.createCell(5).setCellValue("??"); rowOptionHead.createCell(6).setCellValue("??"); rowOptionHead.createCell(7).setCellValue("???"); rowOptionHead.createCell(8).setCellValue("?? ???"); rowOptionHead.createCell(9).setCellValue("??"); rowOptionHead.createCell(10).setCellValue("?"); rowOptionHead.createCell(11).setCellValue("?"); r++; for (FreeOption fo : car.getFreeOptions()) { HSSFRow rowOptionBody = sheet.createRow(r); rowOptionBody.createCell(0).setCellValue(fo.getId()); rowOptionBody.createCell(1).setCellValue(fo.getUid()); rowOptionBody.createCell(2).setCellValue(fo.getType().getName()); rowOptionBody.createCell(3).setCellValue(fo.getTitle()); rowOptionBody.createCell(4).setCellValue(fo.getDescription()); rowOptionBody.createCell(5).setCellValue(fo.getPrice()); rowOptionBody.createCell(6).setCellValue(fo.getRadical()); rowOptionBody.createCell(7).setCellValue( StringAdapter.getString(fo.getParamValue()).replace(".", ",")); rowOptionBody.createCell(8).setCellValue(fo.getPercentValue()); rowOptionBody.createCell(9).setCellValue(fo.getAudial()); rowOptionBody.createCell(10).setCellValue(fo.getVisual()); rowOptionBody.createCell(11).setCellValue(fo.getKinestetic()); r++; } } } } return workbook; } } catch (Exception e) { throw new Exception("HSSFWorkbook.getXls()", e); } return null; } public void updateFromXml(File fl) { try { FileInputStream fi = new FileInputStream(fl); int i = 1; String listName = "? ."; int s = 39191091; List<FreeOption> foForSave = new ArrayList(); List<FreeOption> foForUpd = new ArrayList(); try { HSSFWorkbook workbook = new HSSFWorkbook(fi); int sheetNumber = workbook.getNumberOfSheets(); while (i < sheetNumber) { HSSFSheet sheet = workbook.getSheetAt(i); i++; listName = sheet.getSheetName(); Iterator<Row> it = sheet.iterator(); Car car = new Car(); s = 0; while (it.hasNext()) { Row row = it.next(); s++; Cell idCell = row.getCell(0); if (idCell.getCellType() == Cell.CELL_TYPE_STRING) { String nameCell = idCell.getStringCellValue().trim(); if (nameCell.equals("CAR_ID")) { row = it.next(); s++; Cell carIdCell = row.getCell(0); String carIdstr = StringAdapter.HSSFSellValue(carIdCell); if (carIdstr.contains(".")) { int point = carIdstr.indexOf("."); carIdstr = carIdstr.substring(0, point); } Long carId = StringAdapter.toLong(carIdstr); car = carDao.find(carId); //throw new Exception(" carIdstr="+carIdstr+"; carId="+carId+"; "); } else if (nameCell.equals("OPTION_ID")) { while (it.hasNext()) { row = it.next(); s++; Cell optIdCell = row.getCell(0); if (optIdCell != null) { if (optIdCell.getCellType() == Cell.CELL_TYPE_STRING) { String optIdstr = optIdCell.getStringCellValue().trim(); if (optIdstr.equals("CAR_ID")) { /*it.remove(); break;*/ row = it.next(); s++; Cell carIdCell = row.getCell(0); String carIdstr = StringAdapter.HSSFSellValue(carIdCell); if (carIdstr.contains(".")) { int point = carIdstr.indexOf("."); carIdstr = carIdstr.substring(0, point); } Long carId = StringAdapter.toLong(carIdstr); car = carDao.find(carId); it.next(); s++; } else { String oIdstr = StringAdapter.HSSFSellValue(optIdCell); if (oIdstr.contains(".")) { int point = oIdstr.indexOf("."); oIdstr = oIdstr.substring(0, point); } Long optId = Long.valueOf(oIdstr); FreeOption fored = freeOptionDao.find(optId); if (fored != null) { FreeOption supfo = getOptFromRow(row); fored.setAudial(supfo.getAudial()); fored.setDescription(supfo.getDescription()); fored.setKinestetic(supfo.getKinestetic()); fored.setParamValue(supfo.getParamValue()); fored.setPercentValue(supfo.getPercentValue()); fored.setPrice(supfo.getPrice()); fored.setRadical(supfo.getRadical()); fored.setTitle(supfo.getTitle()); fored.setType(supfo.getType()); fored.setUid(supfo.getUid()); fored.setVisual(supfo.getVisual()); //throw new Exception("4!"); if (validate(fored)) { //freeOptionDao.update(fored); foForUpd.add(fored); } } else { FreeOption fo = getOptFromRow(row); if (car != null) { fo.setCar(car); if (validate(fo)) { foForSave.add(fo); } } //addError(": ? , ."); } } } else if (optIdCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { String oIdstr = StringAdapter.HSSFSellValue(optIdCell); if (oIdstr.contains(".")) { int point = oIdstr.indexOf("."); oIdstr = oIdstr.substring(0, point); } Long optId = Long.valueOf(oIdstr); //Long optId = StringAdapter.toLong(StringAdapter.getString(optIdCell.getNumericCellValue())); FreeOption fored = freeOptionDao.find(optId); if (fored != null) { FreeOption supfo = getOptFromRow(row); fored.setAudial(supfo.getAudial()); fored.setDescription(supfo.getDescription()); fored.setKinestetic(supfo.getKinestetic()); fored.setParamValue(supfo.getParamValue()); fored.setPercentValue(supfo.getPercentValue()); fored.setPrice(supfo.getPrice()); fored.setRadical(supfo.getRadical()); fored.setTitle(supfo.getTitle()); fored.setType(supfo.getType()); fored.setUid(supfo.getUid()); fored.setVisual(supfo.getVisual()); //throw new Exception("3!"); if (validate(fored)) { //freeOptionDao.update(fored); foForUpd.add(fored); } } else { FreeOption fo = getOptFromRow(row); if (car != null) { fo.setCar(car); if (validate(fo)) { foForSave.add(fo); } } } } else if (optIdCell.getCellType() == Cell.CELL_TYPE_BLANK) { if (car != null) { FreeOption fo = getOptFromRow(row); fo.setCar(car); //throw new Exception("2!"); if (validate(fo)) { foForSave.add(fo); } } } } else { if (car != null) { FreeOption fo = getOptFromRow(row); fo.setCar(car); //throw new Exception("1! rad="+fo.getRadical()+"; a="+fo.getAudial()+"; v="+fo.getVisual()+"; perc="+fo.getPercentValue()+"; val="+fo.getParamValue()+"; price="+fo.getPrice()+"; "); if (validate(fo)) { foForSave.add(fo); } } } } } } } } workbook.close(); } catch (Exception e) { addError(": ?:" + i + ", " + listName + ", ?:" + s + ", " + StringAdapter.getStackTraceException(e)); } fi.close(); for (FreeOption fo : foForSave) { freeOptionDao.save(fo); } for (FreeOption fo : foForUpd) { freeOptionDao.update(fo); } } catch (Exception e) { addError(" xml"); addError(e.getMessage()); } } private FreeOption getOptFromRow(Row row) throws Exception { FreeOption res = new FreeOption(); Cell uidc = row.getCell(1); String uid = ""; if (uidc != null) { uid = StringAdapter.HSSFSellValue(uidc); if (uid.contains(".")) { int point = uid.indexOf("."); uid = uid.substring(0, point); } } Cell typec = row.getCell(2); PropertyType type = PropertyType.OPTION; if (typec != null) { type = PropertyType.getSelectTypeFromString(StringAdapter.HSSFSellValue(typec)); } Cell titlec = row.getCell(3); String title = ""; if (titlec != null) { title = StringAdapter.HSSFSellValue(titlec); } Cell descrc = row.getCell(4); String descr = ""; if (descrc != null) { descr = StringAdapter.HSSFSellValue(descrc); } Cell pricec = row.getCell(5); Double price = (double) 0; if (pricec != null) { String pricestr = StringAdapter.HSSFSellValue(pricec); price = StringAdapter.toDouble(pricestr); } Cell radc = row.getCell(6); String rad = ""; if (radc != null) { rad = StringAdapter.HSSFSellValue(radc); 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(7); Double val = (double) 0; if (valc != null) { String valstr = StringAdapter.HSSFSellValue(valc).replace(",", ".").trim(); if (!valstr.equals("")) { val = StringAdapter.toDouble(valstr); } } Cell percc = row.getCell(8); 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 audc = row.getCell(9); String a = "0"; if (audc != null) { a = StringAdapter.HSSFSellValue(audc); if (a.contains(".")) { int point = a.indexOf("."); a = a.substring(0, point); } if (a.equals("")) { a = "0"; } } Cell visc = row.getCell(10); String v = "0"; if (visc != null) { v = StringAdapter.HSSFSellValue(visc); if (v.contains(".")) { int point = v.indexOf("."); v = v.substring(0, point); } if (v.equals("")) { v = "0"; } } Cell kinc = row.getCell(11); String k = "0"; if (kinc != null) { k = StringAdapter.HSSFSellValue(kinc); if (k.contains(".")) { int point = k.indexOf("."); k = k.substring(0, point); } if (k.equals("")) { k = "0"; } } res.setUid(uid); res.setAudial(Integer.valueOf(a)); res.setVisual(Integer.valueOf(v)); res.setKinestetic(Integer.valueOf(k)); res.setTitle(title); res.setRadical(rad); res.setParamValue(val); res.setPercentValue(perc); res.setPrice(price); res.setType(type); res.setDescription(descr); return res; } public void deleteAll() throws Exception { freeOptionDao.deleteAll(); } }