List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
From source file:com.wabacus.system.component.application.report.DetailReportType.java
License:Open Source License
private void showReportDataOnPlainExcel(Workbook workbook) { DisplayBean dbean = rbean.getDbean(); if (lstReportData == null || lstReportData.size() == 0) { lstReportData = new ArrayList<AbsReportDataPojo>(); lstReportData.add(//w ww . j av a 2 s. c om ReportAssistant.getInstance().getPojoClassInstance(rrequest, rbean, rbean.getPojoClassObj())); } CellStyle titleCellStyle = StandardExcelAssistant.getInstance().getTitleCellStyleForStandardExcel(workbook); CellStyle dataCellStyle = StandardExcelAssistant.getInstance().getDataCellStyleForStandardExcel(workbook);//??? CellStyle dataCellStyleWithFormat = StandardExcelAssistant.getInstance() .getDataCellStyleForStandardExcel(workbook); for (AbsReportDataPojo rowDataObjTmp : this.lstReportData) { DetailReportColBean drcolbean = null; ColDisplayData colDisplayData; int startcolidx = 0; int endcolidx = -1; if (sheetsize > 0 && excelRowIdx >= sheetsize) { createNewSheet(workbook, 20); } Row dataRow = excelSheet.createRow(this.excelRowIdx); DetailReportColPositionBean colPositionBeanTmp; boolean hasDisplayColInThisRow = false; Cell cell; CellRangeAddress region; int colspan; String labelTmp; for (ColBean cbean : dbean.getLstCols()) { if (Consts.COL_DISPLAYTYPE_HIDDEN .equals(cbean.getDisplaytype(rrequest.getShowtype() == Consts.DISPLAY_ON_PAGE))) continue; drcolbean = (DetailReportColBean) cbean.getExtendConfigDataForReportType(KEY); colPositionBeanTmp = mColPositions.get(cbean.getColid()); if (colPositionBeanTmp.getDisplaymode() > 0) { hasDisplayColInThisRow = true; labelTmp = cbean.getLabel(rrequest); if (labelTmp != null && !ColBean.NON_LABEL.equals(labelTmp)) {//<col/>label?? String plainexceltitle = null; if (this.pedebean != null) plainexceltitle = this.pedebean.getPlainexceltitle(); if ("none".equals(plainexceltitle)) { labelTmp = ""; } else if ("column".equals(plainexceltitle)) { labelTmp = cbean.getColumn(); } else { colDisplayData = ColDisplayData.getColDataFromInterceptor(this, cbean, rowDataObjTmp, -1, null, cbean.getLabel(rrequest)); labelTmp = colDisplayData.getValue(); } colspan = drcolbean.getLabelcolspan(); if (colspan <= 0) colspan = 1; startcolidx = endcolidx + 1; endcolidx = startcolidx + colspan - 1; if (colspan == 1) { cell = dataRow.createCell(endcolidx); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(labelTmp); cell.setCellStyle(StandardExcelAssistant.getInstance().setCellAlign(titleCellStyle, cbean.getLabelalign())); } else { region = new CellRangeAddress(excelRowIdx, excelRowIdx, startcolidx, endcolidx);//??firstrow?lastrow?firstcol?lastcol StandardExcelAssistant.getInstance() .setRegionCellStringValue(workbook, excelSheet, region, StandardExcelAssistant .getInstance().setCellAlign(titleCellStyle, cbean.getLabelalign()), labelTmp); } } if (!cbean.isNonValueCol()) { Object objvalueTmp = rowDataObjTmp.getColValue(cbean); colspan = colPositionBeanTmp.getColspan(); if (colspan <= 0) colspan = 1; startcolidx = endcolidx + 1; endcolidx = startcolidx + colspan - 1; if (colspan == 1) { cell = dataRow.createCell(endcolidx); boolean flag = StandardExcelAssistant.getInstance().setCellValue(workbook, cbean.getValuealign(), cell, objvalueTmp, cbean.getDatatypeObj(), dataCellStyleWithFormat); if (!flag) cell.setCellStyle(StandardExcelAssistant.getInstance().setCellAlign(dataCellStyle, cbean.getValuealign())); } else { region = new CellRangeAddress(excelRowIdx, excelRowIdx, startcolidx, endcolidx);//??firstrow?lastrow?firstcol?lastcol StandardExcelAssistant.getInstance().setRegionCellRealTypeValue(workbook, excelSheet, region, StandardExcelAssistant.getInstance().setCellAlign(dataCellStyle, cbean.getValuealign()), dataCellStyleWithFormat, cbean.getValuealign(), objvalueTmp, cbean.getDatatypeObj()); } } } if (drcolbean.isBr() && hasDisplayColInThisRow) { hasDisplayColInThisRow = false; dataRow = excelSheet.createRow(++excelRowIdx); startcolidx = 0; endcolidx = -1; } } excelRowIdx = excelRowIdx + 2; } }
From source file:com.wabacus.system.component.application.report.UltraListReportType.java
License:Open Source License
protected void showLabelInPlainExcel(Workbook workbook, Sheet sheet, List lstChildren, Map<String, ColAndGroupTitlePositionBean> mColAndGroupTitlePostions) { CellStyle titleCellStyle = StandardExcelAssistant.getInstance().getTitleCellStyleForStandardExcel(workbook); ColBean colbean;/*from w ww .j av a 2 s . c o m*/ UltraListReportGroupBean groupBean; ColDisplayData colDisplayData; ColAndGroupTitlePositionBean positionBeanTmp; String id; String label = null; String align = null; CellRangeAddress region; for (Object obj : lstChildren) { colbean = null; groupBean = null; positionBeanTmp = null; id = null; align = null; if (obj instanceof ColBean) { colbean = (ColBean) obj; id = colbean.getColid(); label = colbean.getLabel(rrequest); align = colbean.getLabelalign(); } else if (obj instanceof UltraListReportGroupBean) { groupBean = ((UltraListReportGroupBean) obj); label = groupBean.getLabel(rrequest); id = groupBean.getGroupid(); } positionBeanTmp = mColAndGroupTitlePostions.get(id); if (positionBeanTmp == null || positionBeanTmp.getDisplaymode() <= 0) continue; colDisplayData = ColDisplayData.getColDataFromInterceptor(this, obj, null, -1, null, label); region = new CellRangeAddress(positionBeanTmp.getStartrowindex(), positionBeanTmp.getStartrowindex() + positionBeanTmp.getRowspan() - 1, positionBeanTmp.getStartcolindex(), positionBeanTmp.getStartcolindex() + positionBeanTmp.getColspan() - 1);//??firstrow?lastrow?firstcol?lastcol StandardExcelAssistant.getInstance().setRegionCellStringValue(workbook, sheet, region, StandardExcelAssistant.getInstance().setCellAlign(titleCellStyle, align), colDisplayData.getValue()); } }
From source file:com.zdtx.ifms.specific.service.monitor.IpCamManager.java
@SuppressWarnings("unchecked") public InputStream getExcel(String title) { List<Camera> data = new ArrayList<Camera>(); Page<Camera> page_export = (Page<Camera>) Utils.getSession().getAttribute("page_export"); DetachedCriteria criteria_export = (DetachedCriteria) Utils.getSession().getAttribute("criteria_export"); List<Order> orderList_export = (List<Order>) Utils.getSession().getAttribute("orderList_export"); page_export.setPageSize(page_export.getTotalCount()); Page<Camera> pageResult = dao.getBatch(page_export, criteria_export.getExecutableCriteria(dao.getSession()), orderList_export);/*from ww w. j av a2s . c o m*/ if (null != pageResult) { if (0 != pageResult.getResult().size()) { data = pageResult.getResult(); } } ExportExcel ee = new ExportExcel() { @Override protected HSSFWorkbook disposeData(HSSFWorkbook wb, Object[] total, List<?> data) throws IOException { HSSFSheet sheet = wb.getSheetAt(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); HSSFRow rowss = sheet.createRow(0); rowss.setHeightInPoints(20); HSSFCell hssfCell = rowss.createCell(0); hssfCell = this.createCell(wb, hssfCell, total[0].toString()); HSSFCellStyle style = this.createStyle(wb); HSSFRow row2 = sheet.createRow(1); HSSFCell cellrow01 = row2.createCell(0); cellrow01.setCellStyle(style); HSSFCell cellrow02 = row2.createCell(1); cellrow02.setCellStyle(style); HSSFCell cellrow03 = row2.createCell(2); cellrow03.setCellStyle(style); HSSFCell cellrow04 = row2.createCell(3); cellrow04.setCellStyle(style); HSSFCell cellrow05 = row2.createCell(4); cellrow05.setCellStyle(style); HSSFCell cellrow06 = row2.createCell(5); cellrow06.setCellStyle(style); HSSFCell cellrow07 = row2.createCell(6); cellrow07.setCellStyle(style); cellrow01.setCellValue("No."); cellrow02.setCellValue("Camera Name"); cellrow03.setCellValue("Department"); cellrow04.setCellValue("Camera Model"); cellrow05.setCellValue("IP"); cellrow06.setCellValue("Login Username"); cellrow07.setCellValue("Login Password"); if (null != data && 0 != data.size()) { for (int i = 0; i < data.size(); i++) { Object[] o = (Object[]) data.get(i); CamModel cm = (CamModel) o[0]; Camera a = (Camera) o[1]; HSSFRow row = sheet.createRow(i + 2); HSSFCell cell001 = row.createCell(0); cell001.setCellStyle(style); cell001.setCellValue(i + 1); HSSFCell cell002 = row.createCell(1); cell002.setCellStyle(style); cell002.setCellValue(a.getCameraName()); HSSFCell cell003 = row.createCell(2); cell003.setCellStyle(style); cell003.setCellValue(a.getDeptname()); HSSFCell cell004 = row.createCell(3); cell004.setCellStyle(style); cell004.setCellValue(cm.getModelName()); HSSFCell cell005 = row.createCell(4); cell005.setCellStyle(style); cell005.setCellValue(a.getIpAddress()); HSSFCell cell006 = row.createCell(5); cell006.setCellStyle(style); cell006.setCellValue(a.getAdminName()); HSSFCell cell007 = row.createCell(6); cell007.setCellStyle(style); cell007.setCellValue(a.getAdminPass()); } } return wb; } }; Object[] total = new Object[1]; total[0] = title; String str = title; return ee.export(total, data, str); }
From source file:com.zdtx.ifms.specific.service.task.FuelMileageManager.java
@SuppressWarnings("unchecked") public InputStream getData(Page<Mileageoil> page, List<Mileageoil> list, FuelMileageVo fmvo) { //Page<Mileageoil> pageResult = this.getBetch(page, fmvo); Page<Mileageoil> page_export = (Page<Mileageoil>) Struts2Util.getSession().getAttribute("page_export"); DetachedCriteria criteria_export = (DetachedCriteria) Struts2Util.getSession() .getAttribute("criteria_export"); List<Order> orderList_export = (List<Order>) Struts2Util.getSession().getAttribute("orderList_export"); page_export.setPageSize(page_export.getTotalCount()); Page<Mileageoil> pageResult = baseDao.getBatch(page_export, criteria_export.getExecutableCriteria(baseDao.getSession()), orderList_export); List<Mileageoil> data = new ArrayList<Mileageoil>(); if (null != pageResult) { if (0 != pageResult.getResult().size()) { data = pageResult.getResult(); }//from w w w. j ava 2s . c om } ExportExcel ee = new ExportExcel() { @Override protected HSSFWorkbook disposeData(HSSFWorkbook wb, Object[] total, List<?> data) throws IOException { HSSFSheet sheet = wb.getSheetAt(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); HSSFRow rowss = sheet.createRow(0); rowss.setHeightInPoints(20); HSSFCell hssfCell = rowss.createCell(0); hssfCell = this.createCell(wb, hssfCell, "Fuel Mileage"); HSSFCellStyle style = this.createStyle(wb); HSSFRow row2 = sheet.createRow(1); HSSFCell cellrow01 = row2.createCell(0); cellrow01.setCellStyle(style); HSSFCell cellrow02 = row2.createCell(1); cellrow02.setCellStyle(style); HSSFCell cellrow03 = row2.createCell(2); cellrow03.setCellStyle(style); HSSFCell cellrow04 = row2.createCell(3); cellrow04.setCellStyle(style); HSSFCell cellrow05 = row2.createCell(4); cellrow05.setCellStyle(style); HSSFCell cellrow06 = row2.createCell(5); cellrow06.setCellStyle(style); cellrow01.setCellValue("No."); cellrow02.setCellValue("Plate Number"); cellrow03.setCellValue("Vehicle Type"); cellrow04.setCellValue("Date"); cellrow05.setCellValue("Mileage"); cellrow06.setCellValue("Fuel consumption"); if (null != data && 0 != data.size()) { for (int i = 0; i < data.size(); i++) { HSSFRow row = sheet.createRow(i + 2); Mileageoil o = (Mileageoil) data.get(i); HSSFCell cell001 = row.createCell(0); cell001.setCellStyle(style); cell001.setCellValue(i + 1); HSSFCell cell002 = row.createCell(1); cell002.setCellStyle(style); cell002.setCellValue(o.getVehiclename()); HSSFCell cell003 = row.createCell(2); cell003.setCellStyle(style); cell003.setCellValue(o.getTypename()); HSSFCell cell004 = row.createCell(3); cell004.setCellStyle(style); cell004.setCellValue(o.getRiqi()); HSSFCell cell005 = row.createCell(4); cell005.setCellStyle(style); cell005.setCellValue(o.getMileage() + "m"); HSSFCell cell006 = row.createCell(5); cell006.setCellStyle(style); cell006.setCellValue(o.getOilcost() == null ? "0" : o.getOilcost() + "L"); } } return wb; } }; Object[] total = new Object[1]; total[0] = ""; String str = "Fuel Mileage"; return ee.export(total, data, str); }
From source file:com.zdtx.ifms.specific.service.vehicle.VehicleListManager.java
@SuppressWarnings("unchecked") public InputStream getExcel(String title) { List<VehcileView> data = new ArrayList<VehcileView>(); Page<VehcileView> page_export = (Page<VehcileView>) Utils.getSession().getAttribute("page_export"); DetachedCriteria criteria_export = (DetachedCriteria) Utils.getSession().getAttribute("criteria_export"); List<Order> orderList_export = (List<Order>) Utils.getSession().getAttribute("orderList_export"); page_export.setPageSize(page_export.getTotalCount()); Page<VehcileView> pageResult = baseDao.getBatch(page_export, criteria_export.getExecutableCriteria(baseDao.getSession()), orderList_export); if (null != pageResult) { if (0 != pageResult.getResult().size()) { data = pageResult.getResult(); }/*ww w . ja v a2 s . c o m*/ } ExportExcel ee = new ExportExcel() { @Override protected HSSFWorkbook disposeData(HSSFWorkbook wb, Object[] total, List<?> data) throws IOException { HSSFSheet sheet = wb.getSheetAt(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); HSSFRow rowss = sheet.createRow(0); rowss.setHeightInPoints(20); HSSFCell hssfCell = rowss.createCell(0); hssfCell = this.createCell(wb, hssfCell, total[0].toString()); HSSFCellStyle style = this.createStyle(wb); HSSFRow row2 = sheet.createRow(1); HSSFCell cellrow01 = row2.createCell(0); cellrow01.setCellStyle(style); HSSFCell cellrow02 = row2.createCell(1); cellrow02.setCellStyle(style); HSSFCell cellrow03 = row2.createCell(2); cellrow03.setCellStyle(style); HSSFCell cellrow04 = row2.createCell(3); cellrow04.setCellStyle(style); HSSFCell cellrow05 = row2.createCell(4); cellrow05.setCellStyle(style); HSSFCell cellrow06 = row2.createCell(5); cellrow06.setCellStyle(style); HSSFCell cellrow07 = row2.createCell(6); cellrow07.setCellStyle(style); HSSFCell cellrow08 = row2.createCell(7); cellrow08.setCellStyle(style); HSSFCell cellrow09 = row2.createCell(8); cellrow09.setCellStyle(style); HSSFCell cellrow10 = row2.createCell(9); cellrow10.setCellStyle(style); cellrow01.setCellValue("No."); cellrow02.setCellValue("Plate Number"); cellrow03.setCellValue("Fleet"); cellrow04.setCellValue("Department"); cellrow05.setCellValue("Device"); cellrow06.setCellValue("Vehicle Type"); cellrow07.setCellValue("Vehicle Brand"); cellrow08.setCellValue("Key Code"); cellrow09.setCellValue("IP"); cellrow10.setCellValue("Description"); if (null != data && 0 != data.size()) { for (int i = 0; i < data.size(); i++) { VehcileView a = (VehcileView) data.get(i); HSSFRow row = sheet.createRow(i + 2); HSSFCell cell001 = row.createCell(0); cell001.setCellStyle(style); cell001.setCellValue(i + 1); HSSFCell cell002 = row.createCell(1); cell002.setCellStyle(style); cell002.setCellValue(a.getVehiclename()); HSSFCell cell003 = row.createCell(2); cell003.setCellStyle(style); cell003.setCellValue(a.getFleetname()); HSSFCell cell004 = row.createCell(3); cell004.setCellStyle(style); cell004.setCellValue(a.getDeptname()); HSSFCell cell005 = row.createCell(4); cell005.setCellStyle(style); cell005.setCellValue(a.getDevicename()); HSSFCell cell006 = row.createCell(5); cell006.setCellStyle(style); cell006.setCellValue(a.getTypename()); HSSFCell cell007 = row.createCell(6); cell007.setCellStyle(style); cell007.setCellValue(a.getBrandname()); HSSFCell cell008 = row.createCell(7); cell008.setCellStyle(style); cell008.setCellValue(a.getKeycode()); HSSFCell cell009 = row.createCell(8); cell009.setCellStyle(style); cell009.setCellValue(a.getCctvip()); HSSFCell cell010 = row.createCell(9); cell010.setCellStyle(style); cell010.setCellValue(a.getDescription()); } } return wb; } }; Object[] total = new Object[1]; total[0] = title; String str = title; return ee.export(total, data, str); }
From source file:Controller.Sonstiges.ExcelController.java
private void makeTitleOfPage() throws IOException { this.sheet.addMergedRegion(new CellRangeAddress(0, 3, 0, 13)); this.row = this.sheet.createRow(0); XSSFCell cell2 = this.row.createCell(0); String title = "Inprotuc Datenbank | Informationen zur Personen \nSuchkriterien: "; ArrayList<String> array2 = this.model.getQueryInfo(); ArrayList<String> array = this.deleteEmptyValueOArray(array2); String info = ""; if (array.size() == 2) { info = array.get(0) + " / " + array.get(1) + "."; }/*from w ww.j av a 2s. com*/ if (array.size() == 4) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + "."; } if (array.size() == 6) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + ", "; info = info + array.get(4) + "/" + array.get(5) + "."; } cell2.setCellValue(title + info); CellStyle cellStyle = this.wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.LEFT); // font Font font = this.wb.createFont(); font.setFontHeightInPoints((short) 14); font.setFontName(HSSFFont.FONT_ARIAL); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cell2.setCellStyle(cellStyle); }
From source file:Dao.XlsBillDao.java
public void GenFullXLS(String pono, String relpath) { try {// w w w.ja va 2 s . c o m //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data ArrayList<WorkItemBean> wi1 = new ArrayList<WorkItemBean>(); WorkDao wdao1 = new WorkDao(); wi1 = wdao1.getWOItem(pono); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); // spreadsheet.protectSheet("kandarpCBA"); // spreadsheet.setColumnWidth(0, 255); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("NOTE"); cell.setCellStyle(xstyle); int i = 2; for (WorkItemBean w : wi1) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(w.getLINE_NO()); cell = row.createCell(1); cell.setCellValue(w.getITEM_ID()); cell = row.createCell(2); cell.setCellValue(w.getITEM_DESC()); cell = row.createCell(3); cell.setCellValue(w.getUOM()); cell = row.createCell(4); cell.setCellValue(w.getQTY()); cell = row.createCell(5); cell.setCellValue(w.getRATE()); cell = row.createCell(6); cell.setCellValue(w.getCMT()); i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "uxls//" + pono.replace("/", "-") + "_Items" + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}uxls//{1}" + "_Items" + ".xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsBillDao.java
public void GenXLS(String pono, String relpath) { try {/*from w ww . j a va 2 s . co m*/ //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; String sql; Connection con; PreparedStatement ps; ResultSet rs; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data con = DBmanager.GetConnection(); sql = "SELECT DISTINCT cba_wo_item.line_no, cba_wo_item.item_id,\n" + " mtl_system_items.description, cba_wo_item.uom,\n" + " cba_wo_item.qty, cba_wo_item.rate, cba_wo_item.cmt,\n" + " cba_wo_item.plant, cba_wo_item.proj, cba_wo_item.task," + " cba_wo_item.po_no\n" + " FROM cba_wo_item, mtl_system_items\n" + " WHERE ( (cba_wo_item.item_id = mtl_system_items.segment1)\n" + " AND (mtl_system_items.organization_id = 0)\n" + " AND (cba_wo_item.po_no = '" + pono + "')\n" + " )\n" + " ORDER BY cba_wo_item.line_no"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); //spreadsheet.protectSheet("kandarpCBA"); //spreadsheet.setColumnWidth(0, 255); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); // borderStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex()); // borderStyle.setFillPattern(CellStyle.ALIGN_FILL); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono + " Note : If WO is with project information,each bill item should have project and task"); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("WO NOTE"); cell.setCellStyle(xstyle); cell = row.createCell(7); cell.setCellValue("PLANT"); cell.setCellStyle(xstyle); cell = row.createCell(8); cell.setCellValue("COST CENTER"); cell.setCellStyle(xstyle); cell = row.createCell(9); cell.setCellValue("PROJECT"); cell.setCellStyle(xstyle); cell = row.createCell(10); cell.setCellValue("TASK"); cell.setCellStyle(xstyle); cell = row.createCell(11); cell.setCellValue("HERE ADD NOTE"); cell.setCellStyle(xstyle); //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(rs.getString(1)); cell = row.createCell(1); cell.setCellValue(rs.getString(2)); cell = row.createCell(2); cell.setCellValue(rs.getString(3)); cell = row.createCell(3); cell.setCellValue(rs.getString(4)); cell = row.createCell(4); cell.setCellValue(rs.getString(6)); cell = row.createCell(5); cell.setCellValue(rs.getString(5)); cell = row.createCell(6); cell.setCellValue(""); cell = row.createCell(7); cell.setCellValue(rs.getString(7)); cell = row.createCell(8); cell.setCellValue(rs.getString(8)); cell = row.createCell(9); cell.setCellValue(rs.getString(9)); cell = row.createCell(10); cell.setCellValue(rs.getString(10)); cell = row.createCell(11); cell.setCellValue(""); i++; } //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row2; XSSFCell cell2; XSSFSheet ccsheet = workbook.createSheet("Cost Center"); row2 = ccsheet.createRow(0); cell2 = row2.createCell(0); cell2.setCellValue("Cost Center name and code. Please enter only code in excel"); cell2.setCellStyle(borderStyle); ccsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row2 = ccsheet.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("NAME"); cell2.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_cc_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i2 = 2; while (rs.next()) { row2 = ccsheet.createRow(i2); cell2 = row2.createCell(0); cell2.setCellValue(rs.getString(1)); cell2 = row2.createCell(1); cell2.setCellValue(rs.getString(2)); i2++; } //THIRD SHEET //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row3; XSSFCell cell3; XSSFSheet plantsheet = workbook.createSheet("Plant Center"); row3 = plantsheet.createRow(0); cell3 = row3.createCell(0); cell3.setCellValue("Plant Center name and code. Please enter only code in excel"); cell3.setCellStyle(borderStyle); plantsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row3 = plantsheet.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("NAME"); cell3.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_plant_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i3 = 2; while (rs.next()) { row3 = plantsheet.createRow(i3); cell3 = row3.createCell(0); cell3.setCellValue(rs.getString(1)); cell3 = row3.createCell(1); cell3.setCellValue(rs.getString(2)); i3++; } //SHEET 3 HEADER //row1 XSSFSheet spreadsheet4 = workbook.createSheet("Project And Task"); XSSFRow row4 = spreadsheet4.createRow(0); XSSFCell cell4 = row4.createCell(0); cell4.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row4 = spreadsheet4.createRow(1); cell4 = row4.createCell(0); cell4.setCellValue("PROJECT CODE"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(1); cell4.setCellValue("PROJECT NAME"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(2); cell4.setCellValue("TASK CODE"); cell4.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst("123"); for (WorkItemBean w : wi1) { row4 = spreadsheet4.createRow(j); cell4 = row4.createCell(0); cell4.setCellValue(w.getPROJ()); cell4 = row4.createCell(1); cell4.setCellValue(w.getPROJ_NAME()); cell4 = row4.createCell(2); cell4.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "xls//" + pono.replace("/", "-") + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}xls//{1}.xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (SQLException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsWoDao.java
public void GenXLS(String orgId, String relpath) { try {//from w w w.ja va 2s . co m //2.Create WorkBook and Sheet XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet1 = workbook.createSheet("WorkOrder Detail"); XSSFSheet spreadsheet2 = workbook.createSheet("Plant Master"); XSSFSheet spreadsheet3 = workbook.createSheet("Project and Task Master"); //style XSSFFont xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); XSSFCellStyle xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //SHEET 1 HEADER //1row XSSFRow row1 = spreadsheet1.createRow(0); XSSFCell cell1 = row1.createCell(0); cell1.setCellValue("Note : If you are not sure " + "about plant,project,task please " + "leave it blank." + "It can be add when release bill"); spreadsheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 15)); //2row row1 = spreadsheet1.createRow(1); cell1 = row1.createCell(0); cell1.setCellValue("Create Work Order Template"); cell1.setCellStyle(borderStyle); spreadsheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); //3row row1 = spreadsheet1.createRow(2); cell1 = row1.createCell(0); cell1.setCellValue("ITEM_NO"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(1); cell1.setCellValue("RATE"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(2); cell1.setCellValue("PLANT"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(3); cell1.setCellValue("PROJECT"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(4); cell1.setCellValue("TASK"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(5); cell1.setCellValue("ADDITIONAL NOTE"); cell1.setCellStyle(xstyle); //SHEET 2 HEADER //row1 XSSFRow row2 = spreadsheet2.createRow(0); XSSFCell cell2 = row2.createCell(0); cell2.setCellValue("Note : Please copy plant code and paste into 1 sheet"); spreadsheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row2 = spreadsheet2.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("PLANT_CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("PLANT NAME"); cell2.setCellStyle(xstyle); //SHEET 2 DATA int i = 2; Map<String, String> plant = Dropdown.LoadPlantMst(); for (Map.Entry<String, String> entry : plant.entrySet()) { row2 = spreadsheet2.createRow(i); cell2 = row2.createCell(0); cell2.setCellValue(entry.getKey()); cell2 = row2.createCell(1); cell2.setCellValue(entry.getValue()); i++; } //SHEET 3 HEADER //row1 XSSFRow row3 = spreadsheet3.createRow(0); XSSFCell cell3 = row3.createCell(0); cell3.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row3 = spreadsheet3.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("PROJECT CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("PROJECT NAME"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(2); cell3.setCellValue("TASK CODE"); cell3.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst(orgId); for (WorkItemBean w : wi1) { row3 = spreadsheet3.createRow(j); cell3 = row3.createCell(0); cell3.setCellValue(w.getPROJ()); cell3 = row3.createCell(1); cell3.setCellValue(w.getPROJ_NAME()); cell3 = row3.createCell(2); cell3.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); // FileOutputStream out = new FileOutputStream(new File(relpath + "uxls//" + "WO_Creation_Template" + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(relpath)); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:data.services.EventService.java
public void saveLog(Long evId, List<EntityCar> carList, List<IdealEntityParam> ieplist, List<EntityCar> cutCarList, Sequence seq) throws Exception { Logger log = Logger.getLogger(this.getClass()); //Event ev = eventDao.find(evId); try {/*www . ja v a2s .c om*/ HashSet<String> iepuids = new HashSet(); for (IdealEntityParam iep : ieplist) { iepuids.add(iep.getBaseParam().getUid()); } List<BaseParam> bpList = baseParamService.getParams(); HashMap<Long, String> genMap = subModelService.getGenerationUrls(); HSSFWorkbook workbook = new HSSFWorkbook(); Event ev = eventDao.find(evId); HashMap<Long, SubModel> bodyMap = subModelService.getBodies(); HSSFCellStyle style = workbook.createCellStyle(); //style.setFillBackgroundColor(HSSFColor.ORANGE.index); style.setFillForegroundColor(HSSFColor.ORANGE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFSheet idealEntitySheet = workbook.createSheet("? ??"); HSSFSheet chosenSheet = workbook.createSheet(" ?"); HSSFSheet cutedSheet = workbook.createSheet(" ?"); HSSFSheet moneySheet = workbook.createSheet(" "); HSSFSheet instChosenSheet = workbook.createSheet(""); int complectList = 1; int ierownum = 1; idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getName() + "; " + ev.getId() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue("?: " + ev.getAuthor() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getAddDate() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getScene().getName() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getMoney() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(" ???: " + ev.getPerception().getName() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getRadical() + "(" + ev.getColor() + ")" + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue("?: " + seq.getRadCore() + ";"); HSSFRow ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? A1: "); ierowhead.createCell(1).setCellValue(ev.getStrategyA1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? B1: "); ierowhead.createCell(1).setCellValue(ev.getStrategyB1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? : "); ierowhead.createCell(1).setCellValue(ev.getResourceType().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? -: "); ierowhead.createCell(1).setCellValue(ev.getParamTox1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? ?: "); ierowhead.createCell(1).setCellValue(ev.getValueTox1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue(" ?: "); ierowhead.createCell(1).setCellValue(seq.getAllowance() + "%"); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? A2: "); ierowhead.createCell(1).setCellValue(ev.getStrategyA2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? B2: "); ierowhead.createCell(1).setCellValue(ev.getStrategyB2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? -, . ?: "); ierowhead.createCell(1).setCellValue(ev.getParamTox2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? ?, . ?: "); ierowhead.createCell(1).setCellValue(ev.getValueTox2().getName()); ierownum++; int phn = 1; HSSFRow paramsierowhead = idealEntitySheet.createRow(ierownum++); paramsierowhead.createCell(phn++).setCellValue("UID"); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue(" ."); paramsierowhead.createCell(phn++).setCellValue(""); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue(" ?."); paramsierowhead.createCell(phn++).setCellValue(""); int A = 0; int B = 0; for (IdealEntityParam iep : ieplist) { int pbn = 0; HSSFRow paramsierowbody = idealEntitySheet.createRow(ierownum++); paramsierowbody.createCell(pbn++).setCellValue(iep.getNumber()); paramsierowbody.createCell(pbn++).setCellValue(iep.getBaseParam().getUid()); paramsierowbody.createCell(pbn++).setCellValue(iep.getBaseParam().getName()); paramsierowbody.createCell(pbn++).setCellValue(iep.getIepClass().getName()); paramsierowbody.createCell(pbn++).setCellValue(iep.getAmin().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getValueMin().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getValueMax().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getAmax().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(StringAdapter.getString(iep.getRank())); if (Rank.A.equals(iep.getRank())) { A++; } else if (Rank.B.equals(iep.getRank())) { B++; } } ierownum++; ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? - ?:"); ierowhead.createCell(1).setCellValue((double) A); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? - B"); ierowhead.createCell(1).setCellValue((double) B); ierownum++; ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? :"); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(1).setCellValue("UID"); ierowhead.createCell(2).setCellValue("?"); for (SequenceValueRange svr : seq.getSequenceValueRanges()) { String uid = svr.getUid(); if (!iepuids.contains(uid)) { ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(1).setCellValue(svr.getUid()); ierowhead.createCell(2).setCellValue(baseParamService.getBaseParam(bpList, uid).getName()); } } int rowheadNumCell = 0; HSSFRow rowhead = chosenSheet.createRow(0); rowhead.createCell(rowheadNumCell++).setCellValue(""); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue(""); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue(" ?"); rowhead.createCell(rowheadNumCell++).setCellValue("??"); rowhead.createCell(rowheadNumCell++).setCellValue("?? ??"); rowhead.createCell(rowheadNumCell++).setCellValue("? A"); rowhead.createCell(rowheadNumCell++).setCellValue(" A"); rowhead.createCell(rowheadNumCell++).setCellValue("? ? A "); rowhead.createCell(rowheadNumCell++).setCellValue("? B"); rowhead.createCell(rowheadNumCell++).setCellValue(" "); rowhead.createCell(rowheadNumCell++).setCellValue("? ? B"); rowhead.createCell(rowheadNumCell++).setCellValue("? ???"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ??"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ???"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ???"); int numscell = 0; HSSFRow instrowhead = instChosenSheet.createRow(0); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(" ?"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??? 1"); instrowhead.createCell(numscell++).setCellValue("??? 2"); instrowhead.createCell(numscell++).setCellValue("? ???"); instrowhead.createCell(numscell++).setCellValue("? ??"); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 13)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 14, 18)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 26)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 27, 32)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5)); HSSFRow firstRowMoneyHead = moneySheet.createRow(0); HSSFRow secondRowMoneyHead = moneySheet.createRow(1); int frn = 0; firstRowMoneyHead.createCell(frn++).setCellValue(""); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue(""); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue(" ?"); firstRowMoneyHead.createCell(frn++).setCellValue("??? I"); firstRowMoneyHead.createCell(frn + 7).setCellValue("?? I"); firstRowMoneyHead.createCell(frn + 12).setCellValue("??? II"); firstRowMoneyHead.createCell(frn + 20).setCellValue("?? II"); int srn = 6; secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue(" ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?"); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ???"); secondRowMoneyHead.createCell(srn++).setCellValue("- ?? "); secondRowMoneyHead.createCell(srn++).setCellValue(""); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue(" ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?"); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ???"); secondRowMoneyHead.createCell(srn++).setCellValue("- ?? "); secondRowMoneyHead.createCell(srn++).setCellValue(""); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ?"); int n = 1; int optrownum = 1; if (!carList.isEmpty()) { //while (optrownum < 50000) { for (EntityCar car : carList) { if (optrownum > 60000) { instChosenSheet = workbook.createSheet(" " + complectList++); optrownum = 1; numscell = 0; instrowhead = instChosenSheet.createRow(0); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(" ?"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??? 1"); instrowhead.createCell(numscell++).setCellValue("??? 2"); instrowhead.createCell(numscell++).setCellValue("? ???"); instrowhead.createCell(numscell++).setCellValue("? ??"); } Car realCar = car.car; SubModel sm = bodyMap.get(car.car.getCmsqId()); String body = ""; if (sm != null) { body = sm.getBody(); } int m = n + 1; int bpropsCount = 0; int cn = 0; HSSFRow carRow = instChosenSheet.createRow(optrownum); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.car.getCarId())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getModelTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getCarTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(body)); carRow.createCell(cn++).setCellValue(car.fullPrice.doubleValue()); carRow.createCell(cn++).setCellValue(""); carRow.createCell(cn + 6).setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); optrownum++; /*carRow.createCell(3).setCellValue(renderDecimalNoPoint(car.basePrice).replace(".", ",")); carRow.createCell(6).setCellValue(StringAdapter.getString(car.staticRate).replace(".", ",")); carRow.createCell(7).setCellValue(StringAdapter.getString(car.staticRate.add(car.dinamicRate)).replace(".", ","));*/ for (String uid : car.aprops.keySet()) { EntityPropertyHolder eph = car.aprops.get(uid); boolean alternatives = false; List<EntityProperty> insttalledElems = eph.getActiveProperty(); if (insttalledElems.size() > 1) { alternatives = true; } for (EntityProperty ep : insttalledElems) { HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(body); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } optRow.createCell(on++).setCellValue("A"); optRow.createCell(on++).setCellValue(ep.sign); optRow.createCell(on++).setCellValue(ep.type.getName()); optRow.createCell(on++).setCellValue(ep.name); optRow.createCell(on++).setCellValue(ep.title); optRow.createCell(on++).setCellValue(ep.value.doubleValue()); optRow.createCell(on++).setCellValue(eph.dinamicRate.doubleValue()); optrownum++; /*optRow.createCell(1).setCellValue("A"); optRow.createCell(2).setCellValue(ep.type.getName()); optRow.createCell(3).setCellValue(ep.name); optRow.createCell(4).setCellValue(ep.title); optRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ",")); optRow.createCell(6).setCellValue(StringAdapter.getString(eph.dinamicRate).replace(".", ",")); optRow.createCell(7).setCellValue(StringAdapter.getString(eph.staticRate).replace(".", ","));*/ } } for (String uid : car.bprops.keySet()) { EntityPropertyHolder eph = car.bprops.get(uid); boolean alternatives = false; List<EntityProperty> insttalledElems = eph.getActiveProperty(); if (insttalledElems.size() > 1) { alternatives = true; } if (!insttalledElems.isEmpty()) { bpropsCount++; for (EntityProperty ep : eph.getActiveProperty()) { HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(body)); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.price)); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } optRow.createCell(on++).setCellValue("B"); optRow.createCell(on++).setCellValue(ep.sign); optRow.createCell(on++).setCellValue(ep.type.getName()); optRow.createCell(on++).setCellValue(ep.name); optRow.createCell(on++).setCellValue(ep.title); optRow.createCell(on++).setCellValue(ep.value.doubleValue()); optRow.createCell(on++).setCellValue(eph.dinamicRate.doubleValue()); optrownum++; /*HSSFRow optRow = instChosenSheet.createRow((short) optrownum); HSSFCell uidCell = optRow.createCell(0); uidCell.setCellValue(uid); if(alternatives){ //optRow.createCell(8).setCellValue(""); uidCell.setCellStyle(style); } optRow.createCell(1).setCellValue("B"); optRow.createCell(2).setCellValue(ep.type.getName()); optRow.createCell(3).setCellValue(ep.name); optRow.createCell(4).setCellValue(ep.title); optRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ",")); optRow.createCell(6).setCellValue(StringAdapter.getString(eph.dinamicRate).replace(".", ",")); optRow.createCell(7).setCellValue(StringAdapter.getString(eph.staticRate).replace(".", ",")); optrownum++;*/ } } else { BaseParam bp = baseParamService.getBaseParam(uid); HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(body)); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); optRow.createCell(on++).setCellValue("B"); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue(bp.getName()); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue("null"); optRow.createCell(on++).setCellValue(" - "); optrownum++; } } /*String aRate = " - "; if(car.dinamicRateA.compareTo(BigDecimal.valueOf(0))>0){ aRate = StringAdapter.getString(car.dinamicRateA).replace(".", ","); } String acount = " - "; if(car.aprops.keySet().size()>0){ acount = StringAdapter.getString(car.aprops.keySet().size()); }*/ String msA = " - "; BigDecimal medA = BigDecimal.valueOf(0); if (car.aprops.keySet().size() > 0) { medA = car.dinamicRateA.divide(BigDecimal.valueOf(car.aprops.keySet().size()), 2, RoundingMode.HALF_UP); msA = StringAdapter.getString(medA).replace(".", ","); } /* String bRate = " - "; if(car.dinamicRateB.compareTo(BigDecimal.valueOf(0))>0){ bRate = StringAdapter.getString(car.dinamicRateB).replace(".", ","); }*/ String bcount = " - "; if (bpropsCount > 0) { bcount = StringAdapter.getString(bpropsCount); } String msB = " - "; BigDecimal medB = BigDecimal.valueOf(0); if (bpropsCount > 0) { medB = car.dinamicRateB.divide(BigDecimal.valueOf(bpropsCount), 2, RoundingMode.HALF_UP); msB = StringAdapter.getString(medB).replace(".", ","); } /*String bsr = " - "; if(car.baseStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ bsr = StringAdapter.getString(car.baseStaticRate2).replace(".", ","); } String fsr = " - "; if(car.freeStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ fsr = StringAdapter.getString(car.freeStaticRate2).replace(".", ","); } String msr = " - "; if(car.monetaryStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ msr = StringAdapter.getString(car.monetaryStaticRate2).replace(".", ","); }*/ Model model = realCar.getModel(); HSSFRow rowbody = chosenSheet.createRow(n); int rowbodynum = 0; rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.car.getCarId())); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowbody.createCell(rowbodynum++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); HSSFCell nameCellWithLink = rowbody.createCell(rowbodynum++); nameCellWithLink.setCellValue(StringAdapter.getString(car.getCarTitle())); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress("http://quto.ru/" + model.getMark().getUrl() + "/" + model.getUrl() + "/" + genMap.get(realCar.getCmgqId()) + "/" + bodyMap.get(realCar.getCmsqId()).getUrl() + "/" + realCar.getUrl()); nameCellWithLink.setHyperlink(link); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(body)); rowbody.createCell(rowbodynum++).setCellValue(car.basePrice.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.fullPrice.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.dinamicRateA.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue((double) car.aprops.keySet().size()); rowbody.createCell(rowbodynum++).setCellValue(medA.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.dinamicRateB.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue((double) bpropsCount); rowbody.createCell(rowbodynum++).setCellValue(medB.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(medA.add(medB).doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.baseStaticRate2.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.freeStaticRate2.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.monetaryStaticRate2.doubleValue()); int rmbn = 0; HSSFRow rowMoneyBody = moneySheet.createRow(m); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.car.getId())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowMoneyBody.createCell(rmbn++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getCarTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(body)); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.aprops.keySet().size()); rowMoneyBody.createCell(rmbn++).setCellValue((double) bpropsCount); rowMoneyBody.createCell(rmbn++).setCellValue(car.basePrice.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.bFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund1.add(car.bFund1).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue( ev.getMoney().subtract(car.aFund1.add(car.bFund1).add(car.basePrice)).doubleValue()); rowMoneyBody.createCell(rmbn++) .setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.baseStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.freeStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.payStaticCount1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.staticFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.monetaryStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.aCount2); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.bCount2); rowMoneyBody.createCell(rmbn++).setCellValue(car.basePrice.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.bFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund2.add(car.bFund2).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue( ev.getMoney().subtract(car.aFund2.add(car.bFund2).add(car.basePrice)).doubleValue()); rowMoneyBody.createCell(rmbn++) .setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.baseStaticRate2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.freeStaticRate2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.payStaticCount2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.staticFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.monetaryStaticRate2.doubleValue()); n++; HSSFRow carStaticRowHead = instChosenSheet.createRow(optrownum); int csn = 0; carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.car.getCarId())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getModelTitle())); carStaticRowHead.createCell(csn++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getCarTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(body)); //carStaticRowHead.createCell(3).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice())); carStaticRowHead.createCell(csn++).setCellValue(":"); carStaticRowHead.createCell(csn + 7).setCellValue(car.staticRate.doubleValue()); optrownum++; Map<String, List<EntityProperty>> StaticMap = car.staticProps; for (String uid : StaticMap.keySet()) { List<EntityProperty> insttalledElems = StaticMap.get(uid); if (insttalledElems != null && !insttalledElems.isEmpty()) { boolean alternatives = false; if (insttalledElems.size() > 1) { alternatives = true; } for (EntityProperty ep : insttalledElems) { String title = ep.title; if (!ep.description.trim().equals("")) { title += " - " + ep.description; } HSSFRow carStaticRow = instChosenSheet.createRow(optrownum); int cson = 0; carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.car.getCarId())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getMarkTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getModelTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getCarTitle())); carStaticRow.createCell(cson++).setCellValue(StringAdapter.getString(body)); //carStaticRow.createCell(cson++).setCellValue(StringAdapter.getString(ep.getPrice()).replace(".", ",")); carStaticRow.createCell(cson++).setCellValue(" - "); HSSFCell uidCell = carStaticRow.createCell(cson++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } //carStaticRow.createCell(5).setCellValue(uid); carStaticRow.createCell(cson++).setCellValue("S"); carStaticRow.createCell(cson++).setCellValue(ep.sign); carStaticRow.createCell(cson++).setCellValue(ep.type.getName()); carStaticRow.createCell(cson++).setCellValue(ep.name); carStaticRow.createCell(cson++).setCellValue(title); carStaticRow.createCell(cson++).setCellValue(ep.radicalValue); carStaticRow.createCell(cson + 1).setCellValue(ep.value.doubleValue()); /*carStaticRow.createCell(1).setCellValue(ep.name); carStaticRow.createCell(2).setCellValue(ep.title); carStaticRow.createCell(3).setCellValue(ep.type.getName()); carStaticRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ","));*/ optrownum++; } } } /*break;*/ } } int k = 0; HSSFRow rowhead20 = cutedSheet.createRow(k++); HSSFRow rowhead21 = cutedSheet.createRow(k++); HSSFRow rowhead22 = cutedSheet.createRow(k++); int ccn = 0; rowhead20.createCell(0).setCellValue("UID ??"); rowhead21.createCell(ccn++).setCellValue(""); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue(""); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue(" ?"); rowhead22.createCell(0).setCellValue(" ???"); int cutoptrownum = 0; for (IdealEntityParam iep : ieplist) { rowhead20.createCell(ccn).setCellValue(StringAdapter.getString(iep.baseParam.getUid())); rowhead21.createCell(ccn).setCellValue(iep.number + "." + iep.baseParam.getName()); rowhead22.createCell(ccn++).setCellValue(iep.aimBefore.doubleValue()); } if (!cutCarList.isEmpty()) { for (EntityCar car : cutCarList) { SubModel sm = bodyMap.get(car.car.getCmsqId()); String body = ""; if (sm != null) { body = sm.getBody(); } HSSFRow rowbody = cutedSheet.createRow(k++); int con = 0; rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.car.getCarId())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getCarTitle())); rowbody.createCell(con).setCellValue(body); int num = 0; if (car.cutNum != null) { num = car.cutNum; } String cutValue = car.cutVal; if (cutValue == null || cutValue.equals("null")) { cutValue = "? "; } try { Double cutedValue = Double.valueOf(car.cutVal); rowbody.createCell(con + num).setCellValue(cutedValue); } catch (Exception e) { rowbody.createCell(con + num).setCellValue(cutValue.replace(".", ",")); } /*HSSFRow carRow = instCutedSheet.createRow(cutoptrownum); carRow.createCell(0).setCellValue(StringAdapter.getString(car.car.getCarId())); carRow.createCell(1).setCellValue(StringAdapter.getString(car.getMarkTitle())); carRow.createCell(2).setCellValue(StringAdapter.getString(car.getModelTitle())); carRow.createCell(3).setCellValue(StringAdapter.getString(car.getCarTitle())); carRow.createCell(4).setCellValue(RenderSupport.renderDecimalNoPoint(car.basePrice).replace(".", ",")); carRow.createCell(5).setCellValue(RenderSupport.renderDecimalNoPoint(car.fixPrice).replace(".", ",")); carRow.createCell(6).setCellValue(StringAdapter.getString(car.dinamicRateA.add(car.dinamicRateB)).replace(".", ",")); carRow.createCell(7).setCellValue(StringAdapter.getString(car.staticRate).replace(".", ",")); carRow.createCell(8).setCellValue(StringAdapter.getString(car.staticRate.add(car.dinamicRateA.add(car.dinamicRateB))).replace(".", ",")); cutoptrownum++; HSSFRow carRowHead = instCutedSheet.createRow(cutoptrownum); carRowHead.createCell(0).setCellValue(StringAdapter.getString("")); carRowHead.createCell(1).setCellValue(StringAdapter.getString("")); carRowHead.createCell(2).setCellValue(StringAdapter.getString("??")); carRowHead.createCell(3).setCellValue(StringAdapter.getString("?????")); carRowHead.createCell(4).setCellValue(StringAdapter.getString("???")); carRowHead.createCell(5).setCellValue(StringAdapter.getString("%")); carRowHead.createCell(6).setCellValue(StringAdapter.getString("??")); cutoptrownum++;*/ /*for (PropertyName pn : car.car.getPropertyNames()) { CarProperty p = pn.getCarProperty(); if (p != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (p.getUid() != null) { optRow.createCell(0).setCellValue(p.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("?"); if (p.getTitle() != null) { optRow.createCell(2).setCellValue(p.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (pn.getPropertyNameValue() != null && !pn.getPropertyNameValue().equals("")) { optRow.createCell(3).setCellValue(pn.getPropertyNameValue()); } else { optRow.createCell(3).setCellValue("? "); } if (pn.getParamValue() != null && !pn.getParamValue().equals("")) { optRow.createCell(4).setCellValue(pn.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (pn.getPercentValue() != null && !pn.getPercentValue().equals("")) { optRow.createCell(5).setCellValue(pn.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (pn.getRadical() != null && !pn.getRadical().equals("")) { optRow.createCell(6).setCellValue(pn.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } } List<Feature> flist = carService.getFeatures(car.car); for (Feature f : flist) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (f.getUid() != null) { optRow.createCell(0).setCellValue(f.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("??"); optRow.createCell(2).setCellValue(""); if (f.getTitle() != null) { optRow.createCell(3).setCellValue(f.getTitle()); } else { optRow.createCell(3).setCellValue("? "); } if (f.getParamValue() != null) { optRow.createCell(4).setCellValue(f.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (f.getPercentValue() != null) { optRow.createCell(5).setCellValue(f.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (f.getRadical() != null) { optRow.createCell(6).setCellValue(f.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; }*/ /*for (CarOptionValue cov : car.car.getCarOptionValues()) { CarCompletionOption cco = cov.getCCO(); if (cco != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (cco.getUid() != null) { optRow.createCell(0).setCellValue(cco.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("?"); if (cco.getTitle() != null) { optRow.createCell(2).setCellValue(cco.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (cov.getTitle() != null) { optRow.createCell(3).setCellValue(cov.getTitle()); } else { optRow.createCell(3).setCellValue("? "); } if (cco.getParamValue() != null) { optRow.createCell(4).setCellValue(cco.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (cco.getPercentValue() != null) { optRow.createCell(5).setCellValue(cco.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (cco.getRadical() != null) { optRow.createCell(6).setCellValue(cco.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } } for (CarColorValue ccv : car.car.getCarColorValues()) { Color col = ccv.getColor(); if (col != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (col.getUid() != null) { optRow.createCell(0).setCellValue(col.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue(""); if (col.getTitle() != null) { optRow.createCell(2).setCellValue(col.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (col.getName() != null) { optRow.createCell(3).setCellValue(col.getName()); } else { optRow.createCell(3).setCellValue("? "); } if (col.getParamValue() != null) { optRow.createCell(4).setCellValue(col.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (col.getPercentValue() != null) { optRow.createCell(5).setCellValue(col.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (col.getRadical() != null) { optRow.createCell(6).setCellValue(col.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } }*/ /*for(String uid:car.aprops.keySet()){ for(EntityProperty ep:car.aprops.get(uid).getPropList()){ HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); optRow.createCell(0).setCellValue(uid); optRow.createCell(1).setCellValue(ep.name); optRow.createCell(2).setCellValue(ep.title); optRow.createCell(3).setCellValue(ep.type.getName()); optRow.createCell(4).setCellValue(StringAdapter.getString(ep.value)); cutoptrownum++; } } for(String uid:car.bprops.keySet()){ for(EntityProperty ep:car.bprops.get(uid).getPropList()){ HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); optRow.createCell(0).setCellValue(uid); optRow.createCell(1).setCellValue(ep.name); optRow.createCell(2).setCellValue(ep.title); optRow.createCell(3).setCellValue(ep.type.getName()); optRow.createCell(4).setCellValue(StringAdapter.getString(ep.value)); cutoptrownum++; } }*/ } } File f = new File("/usr/local/etc/xls/" + evId + ".xls"); if (f.exists()) { f.delete(); } workbook.write(new FileOutputStream("/usr/local/etc/xls/" + evId + ".xls")); } catch (Exception e) { //log.warn("HSSFWorkbook.getXls()", e); throw new Exception(e); } }