List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setAlignment
@Override public void setAlignment(HorizontalAlignment align)
From source file:com.openitech.util.HSSFWrapper.java
License:Apache License
public static final HSSFWorkbook getWorkbook(JTable source, boolean countRows) { HSSFWorkbook xls_workbook = new HSSFWorkbook(); HSSFSheet xls_sheet = xls_workbook.createSheet("Pregled podatkov"); HSSFPrintSetup xls_sheet_printsetup = xls_sheet.getPrintSetup(); xls_sheet_printsetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); xls_sheet_printsetup.setFitWidth((short) 1); TableColumnModel columnModel = source.getColumnModel(); Enumeration<TableColumn> columns = columnModel.getColumns(); HSSFRow xls_row = xls_sheet.createRow(0); short cell = 1; HSSFCellStyle xls_header_cell_style = xls_workbook.createCellStyle(); HSSFFont xls_header_font = xls_workbook.createFont(); xls_header_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); xls_header_cell_style.setFont(xls_header_font); xls_header_cell_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); xls_header_cell_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); xls_header_cell_style.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex()); //xls_header_cell_style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); java.util.Map<String, HSSFCellStyle> cellStyles = new java.util.HashMap<String, HSSFCellStyle>(); HSSFDataFormat xls_data_format = xls_workbook.createDataFormat(); HSSFCellStyle xls_date_cell_style = xls_workbook.createCellStyle(); xls_date_cell_style.setDataFormat(xls_data_format.getFormat("d.m.yyyy")); cellStyles.put("d.m.yyyy", xls_date_cell_style); HSSFCellStyle xls_double_cell_style = xls_workbook.createCellStyle(); xls_double_cell_style.setDataFormat(xls_data_format.getFormat("#,##0.00")); cellStyles.put("#,##0.00", xls_double_cell_style); while (columns.hasMoreElements()) { TableColumn column = columns.nextElement(); HSSFCell xls_cell = xls_row.createCell(cell++); xls_cell.setCellValue(new HSSFRichTextString(column.getHeaderValue().toString())); xls_cell.setCellStyle(xls_header_cell_style); }// ww w .j ava 2 s . c om TableModel tableModel = source.getModel(); DbTableModel dbTableModel = (tableModel instanceof DbTableModel) ? (DbTableModel) tableModel : null; Integer fetchSize = null; if (dbTableModel != null) { try { fetchSize = dbTableModel.getDataSource().getFetchSize(); dbTableModel.getDataSource().setFetchSize(2000); } catch (SQLException ex) { Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex); fetchSize = null; } } short row = 1; JWProgressMonitor progress = new JWProgressMonitor((java.awt.Frame) null); progress.setTitle("Izvoz podatkov v Excel"); progress.setMax(tableModel.getRowCount()); progress.setVisible(true); try { while (row <= tableModel.getRowCount()) { xls_row = xls_sheet.createRow(row); cell = 0; HSSFCell xls_cell = xls_row.createCell(cell++); if (countRows) { xls_cell.setCellValue(new HSSFRichTextString(Short.toString(row))); } while (cell <= columnModel.getColumnCount()) { Object value = tableModel.getValueAt(source.convertRowIndexToModel(row - 1), source.convertColumnIndexToModel(cell - 1)); if (value != null) { if (value instanceof DbTableModel.ColumnDescriptor.ValueMethod) { DbTableModel.ColumnDescriptor.ValueMethod vm = (DbTableModel.ColumnDescriptor.ValueMethod) value; if (vm.getColumnNames().size() == 1) { java.util.List<Object> values = vm.getValues(); java.util.List<String> cellFormats = vm.getCellFormats(); for (String cellFormat : cellFormats) { if (cellFormat != null) { if (!cellStyles.containsKey(cellFormat)) { HSSFCellStyle xls_cell_style = xls_workbook.createCellStyle(); xls_cell_style.setDataFormat(xls_data_format.getFormat(cellFormat)); cellStyles.put(cellFormat, xls_cell_style); } } } Object vm_value = values.get(0); HSSFCellStyle xls_cell_style = cellFormats.get(0) == null ? null : cellStyles.get(cellFormats.get(0)); if (vm_value != null) { xls_cell = xls_row.createCell(cell); if (vm_value instanceof java.util.Date) { xls_cell.setCellValue((java.util.Date) vm_value); xls_cell.setCellStyle( xls_cell_style == null ? xls_date_cell_style : xls_cell_style); } else if (vm_value instanceof java.lang.Number) { xls_cell.setCellValue(((java.lang.Number) vm_value).doubleValue()); if ((vm_value instanceof java.math.BigDecimal) || (vm_value instanceof java.lang.Double) || (vm_value instanceof java.lang.Float)) { xls_cell.setCellStyle(xls_cell_style == null ? xls_double_cell_style : xls_cell_style); } } else if (vm_value instanceof java.lang.Boolean) { xls_cell.setCellValue(((java.lang.Boolean) vm_value).booleanValue()); } else { xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } } else { xls_cell = xls_row.createCell(cell); xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } else { xls_cell = xls_row.createCell(cell); xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } cell++; } row++; progress.next(); } for (cell = 0; cell <= columnModel.getColumnCount(); cell++) { xls_sheet.autoSizeColumn(cell); } xls_sheet.createFreezePane(1, 1); } finally { progress.setVisible(false); if (fetchSize != null) { try { dbTableModel.getDataSource().setFetchSize(fetchSize); } catch (SQLException ex) { Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex); } } } return xls_workbook; }
From source file:com.project.jsica.cdi.ReporteBean.java
public void reporte2(List<ReportePermisoBean> reporte) { LOG.info("TAMAO reporte: " + reporte.size()); FacesContext fc = FacesContext.getCurrentInstance(); HttpServletResponse response = (HttpServletResponse) fc.getExternalContext().getResponse(); HSSFWorkbook libro = new HSSFWorkbook(); HSSFFont fuente = libro.createFont(); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle estiloCeldaCabecera = libro.createCellStyle(); estiloCeldaCabecera.setFont(fuente); estiloCeldaCabecera.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = libro.createDataFormat(); HSSFCellStyle style = libro.createCellStyle(); style.setDataFormat(format.getFormat("hh:mm:ss")); HSSFCellStyle fechas = libro.createCellStyle(); fechas.setDataFormat(format.getFormat("dd.MM.yyyy")); HSSFSheet hoja = libro.createSheet("hoja 1"); //CREAR LAS CABECERAS String[] cabeceras = { "CODIGO", "NOMBRE", "HORA INICIO", "HORA FIN", "HORAS", "MINUTOS", "FECHA", "MOTIVO" }; HSSFRow filaCabecera = hoja.createRow(0); for (int x = 0; x < cabeceras.length; x++) { HSSFCell cabecera = filaCabecera.createCell(x); cabecera.setCellValue(cabeceras[x]); cabecera.setCellStyle(estiloCeldaCabecera); }//w w w . j a va 2 s.c om //FIN DE CABECERAS for (int i = 0; i < reporte.size(); i++) { HSSFRow fila = hoja.createRow(i + 1); HSSFCell columna1 = fila.createCell(0); columna1.setCellValue(reporte.get(i).getCodigo()); HSSFCell columna2 = fila.createCell(1); columna2.setCellValue(reporte.get(i).getNombre()); HSSFCell columna3 = fila.createCell(2); columna3.setCellValue(reporte.get(i).getHoraInicio()); columna3.setCellStyle(style); HSSFCell columna4 = fila.createCell(3); columna4.setCellValue(reporte.get(i).getHoraFin()); columna4.setCellStyle(style); HSSFCell columna5 = fila.createCell(4); columna5.setCellValue(reporte.get(i).getHoras()); HSSFCell columna6 = fila.createCell(5); columna6.setCellValue(reporte.get(i).getMinutos()); HSSFCell columna7 = fila.createCell(6); columna7.setCellValue(reporte.get(i).getFechaReal()); columna7.setCellStyle(fechas); HSSFCell columna8 = fila.createCell(7); columna8.setCellValue(reporte.get(i).getMotivo()); } try { OutputStream output = response.getOutputStream(); libro.write(output); output.close(); fc.responseComplete(); } catch (IOException ex) { LOG.info("ERROR: " + ex); } }
From source file:com.project.jsica.cdi.ReporteBean.java
public void reporte3() { if (nuevoReporte) { LOG.info("OPCION: " + opcionReporte); String nombreReporte = ""; int filas = 0; if (opcionReporte == 2) { reporte = registroAsistenciaController.buscarXArea(areaSeleccionada, desde, hasta); LOG.info("TAMAO reporte: " + reporte.size()); nombreReporte = "Reporte de asistencia por area"; filas = 1;// w ww .ja va 2 s . c om } else if (opcionReporte == 1) { reporte = registroAsistenciaController.buscarXEmpleado(empleado, desde, hasta); LOG.info("TAMAO reporte: " + reporte.size()); nombreReporte = "Reporte de asistencia por empleado"; filas = 0; } FacesContext fc = FacesContext.getCurrentInstance(); HttpServletResponse response = (HttpServletResponse) fc.getExternalContext().getResponse(); response.reset(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=" + nombreReporte + ".xls"); HSSFWorkbook libro = new HSSFWorkbook(); HSSFFont fuente = libro.createFont(); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle estiloCeldaCabecera = libro.createCellStyle(); estiloCeldaCabecera.setFont(fuente); estiloCeldaCabecera.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = libro.createDataFormat(); HSSFCellStyle style = libro.createCellStyle(); style.setDataFormat(format.getFormat("hh:mm:ss")); HSSFCellStyle fechas = libro.createCellStyle(); fechas.setDataFormat(format.getFormat("dd.MM.yyyy")); HSSFSheet hoja = libro.createSheet("Reporte de Asistencias"); //CREAR LAS CABECERAS String[] cabeceras = { "CODIGO", "APELLIDOS Y NOMBRES", "FECHA", "TIPO", "HORA DE INGRESO", "HORA DE SALIDA", "MARCACION DE ENTRADA", "MARCACION DE SALIDA", "TARDANZA(Minutos)", "SALIDA REFRIGERIO", "ENTRADA REFRIGERIO", "MARCACION SALIDA R", "MARCACION DE ENTRADA R", "TARDANZA(refrigerio)", "TARDANZA TOTAL" }; if (filas == 1) { HSSFRow filaArea = hoja.createRow(0); HSSFCell Area = filaArea.createCell(0); Area.setCellValue("AREA"); Area.setCellStyle(estiloCeldaCabecera); HSSFCell nombre = filaArea.createCell(1); nombre.setCellValue(areaSeleccionada.getNombre() + ""); } HSSFRow filaCabecera = hoja.createRow(filas); for (int x = 0; x < cabeceras.length; x++) { HSSFCell cabecera = filaCabecera.createCell(x); cabecera.setCellValue(cabeceras[x]); cabecera.setCellStyle(estiloCeldaCabecera); } //FIN DE CABECERAS for (int i = filas; i < reporte.size(); i++) { HSSFRow fila = hoja.createRow(i + 1); HSSFCell columna1 = fila.createCell(0); columna1.setCellValue(reporte.get(i).getEmpleado().getCodigo()); HSSFCell columna2 = fila.createCell(1); columna2.setCellValue(reporte.get(i).getEmpleado().getNombreCompleto()); HSSFCell columna3 = fila.createCell(2); columna3.setCellValue(reporte.get(i).getFecha()); columna3.setCellStyle(fechas); HSSFCell columna4 = fila.createCell(3); columna4.setCellValue(reporte.get(i).getTipo() + ""); HSSFCell columna5 = fila.createCell(4); columna5.setCellValue(reporte.get(i).getHoraEntrada()); columna5.setCellStyle(style); HSSFCell columna6 = fila.createCell(5); columna6.setCellValue(reporte.get(i).getHoraSalida()); columna6.setCellStyle(style); HSSFCell columna7 = fila.createCell(6); if (reporte.get(i).getMarcacionInicio() != null) { columna7.setCellValue(reporte.get(i).getMarcacionInicio()); columna7.setCellStyle(style); } else { columna7.setCellValue("No marco."); } HSSFCell columna8 = fila.createCell(7); if (reporte.get(i).getMarcacionFin() != null) { columna8.setCellValue(reporte.get(i).getMarcacionFin()); columna8.setCellStyle(style); } else { columna8.setCellValue("No marco."); } HSSFCell columna9 = fila.createCell(8); int minutos = (int) ((reporte.get(i).getMilisTardanzaTotal() / (1000 * 60)) % 60); columna9.setCellValue(minutos); HSSFCell columna10 = fila.createCell(9); columna10.setCellValue(reporte.get(i).getHoraSalidaRefrigerio()); columna10.setCellStyle(style); HSSFCell columna11 = fila.createCell(10); columna11.setCellValue(reporte.get(i).getHoraEntradaRefrigerio()); columna11.setCellStyle(style); HSSFCell columna12 = fila.createCell(11); if (reporte.get(i).getMarcacionInicioRefrigerio() != null) { columna12.setCellValue(reporte.get(i).getMarcacionInicioRefrigerio()); columna12.setCellStyle(style); } else { columna12.setCellValue("No marco."); } HSSFCell columna13 = fila.createCell(12); if (reporte.get(i).getMarcacionFinRefrigerio() != null) { columna13.setCellValue(reporte.get(i).getMarcacionFinRefrigerio()); columna13.setCellStyle(style); } else { columna13.setCellValue("No marco."); } HSSFCell columna14 = fila.createCell(13); columna14.setCellValue((int) ((reporte.get(i).getMilisTardanzaRefrigerio() / (1000 * 60)) % 60)); HSSFCell columna15 = fila.createCell(14); columna15.setCellValue((int) ((reporte.get(i).getMilisTardanzaTotalFinal() / (1000 * 60)) % 60)); } try { OutputStream output = response.getOutputStream(); libro.write(output); output.close(); fc.responseComplete(); } catch (IOException ex) { LOG.info("ERROR: " + ex); } nuevoReporte = false; } }
From source file:com.pureinfo.dolphin.export.impl.ExcelExporterImpl.java
License:Open Source License
/** * Returns the head style// ww w . j av a2s . c o m * * @param _workbook * excell work book * @return the head style */ protected HSSFCellStyle getHeaderStyle(HSSFWorkbook _workbook) { HSSFCellStyle headerStyle = _workbook.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headFont = _workbook.createFont(); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(headFont); return headerStyle; }
From source file:com.pureinfo.srm.project.model.compile.helper.CompileExcelExportHelper.java
License:Open Source License
/** * Returns the date style in excel.//from www . ja v a 2 s. c o m * * @param _workbook * excell work book * @return the date style in excel. */ protected HSSFCellStyle getDateStyle(HSSFWorkbook _workbook) { HSSFCellStyle dateStyle = _workbook.createCellStyle(); HSSFDataFormat fmt = _workbook.createDataFormat(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-mm")); HSSFFont font = _workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); dateStyle.setWrapText(true); dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); dateStyle.setFont(font); return dateStyle; }
From source file:com.pureinfo.srm.project.model.compile.helper.CompileExcelExportHelper.java
License:Open Source License
/** * Returns the head style//from w ww . j ava 2s. c o m * * @param _workbook * excell work book * @return the head style */ protected HSSFCellStyle getHeaderStyle(HSSFWorkbook _workbook) { HSSFCellStyle headerStyle = _workbook.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headFont = _workbook.createFont(); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(headFont); headerStyle.setWrapText(true); return headerStyle; }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java
License:Open Source License
private HSSFCellStyle getHeaderStyle(final HSSFWorkbook workbook, final short borderLeft, final short borderRight, final short alignment, final short boldweight) { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); style.setBorderLeft(borderLeft);//from ww w . j ava2 s . c o m style.setBorderRight(borderRight); style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); style.setAlignment(alignment); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setIndention((short) 3); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Font font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 12); font.setBoldweight(boldweight); style.setFont(font); return style; }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java
License:Open Source License
private HSSFCellStyle getSeriesStyle(final HSSFWorkbook workbook, final short borderLeft, final short borderRight, final short alignment, final short boldweight) { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(borderLeft);/* w w w. j a va 2s. c om*/ style.setBorderRight(borderRight); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setAlignment(alignment); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); Font font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setBoldweight(boldweight); style.setFont(font); return style; }
From source file:com.sammyun.ExcelView.java
License:Open Source License
/** * ?Excel//from w w w . j a v a 2s . c om * * @param model ? * @param workbook workbook * @param request request * @param response response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "M" + "o" + "S" + "ho" + "o" + "p")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.selfsoft.business.service.impl.TbBusinessBalanceServiceImpl.java
public void printTbBusinessBalanceTemplate(OutputStream os, String tpl, Long id, String companyName) { // ??// ww w .j a v a2 s . c o m TbBusinessBalance tbBusinessBalance = this.findById(id); // ? TbFixEntrust tbFixEntrust = tbFixEntrustService.findById(tbBusinessBalance.getTbFixEntrust().getId()); // ? TbCustomer tbCustomer = tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId()); // ? TbCarInfo tbCarInfo = tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId()); // ?? TmCompany tmCompany = tmCompanyService.acquireUniqueTmCompany(); // ? List<TbFixEntrustContent> tbFixEntrustContentList = tbFixEntrustContentService .findTbFixEnTrustContentListByTbFixEntrustId(tbFixEntrust.getId()); List<TbFixEntrustContent> tbFixEntrustContentListPage = new ArrayList<TbFixEntrustContent>(); List<TbFixEntrustContent> tbFixEntrustContentListTemp = new ArrayList<TbFixEntrustContent>(); List<TbFixEntrustContent> tbFixEntrustContentListAdd = new ArrayList<TbFixEntrustContent>(); if (null != tbFixEntrustContentList && tbFixEntrustContentList.size() > 0) { for (int i = 0; i < tbFixEntrustContentList.size(); i++) { boolean flag = false; if (tbFixEntrustContentListTemp.size() == 0) { tbFixEntrustContentListTemp.add(tbFixEntrustContentList.get(i)); } else { if (tbFixEntrustContentListTemp.size() > 1) { for (TbFixEntrustContent _tbFixEntrustContent : tbFixEntrustContentListTemp) { if (_tbFixEntrustContent.getStationName() .equals(tbFixEntrustContentList.get(i).getStationName()) && _tbFixEntrustContent.getTbWorkingInfo().getId() .equals(tbFixEntrustContentList.get(i).getTbWorkingInfo().getId()) && _tbFixEntrustContent.getFreesymbol() .equals(tbFixEntrustContentList.get(i).getFreesymbol()) ) { flag = true; break; } } } } if (flag) { continue; } else { tbFixEntrustContentListTemp.add(tbFixEntrustContentList.get(i)); } TbFixEntrustContent temp = tbFixEntrustContentList.get(i); BigDecimal d = new BigDecimal(temp.getFixHourAll()); BigDecimal d2 = new BigDecimal(temp.getFixHour()); for (int j = i + 1; j < tbFixEntrustContentList.size(); j++) { if (temp.getStationName().equals(tbFixEntrustContentList.get(j).getStationName()) && temp.getTbWorkingInfo().getId() .equals(tbFixEntrustContentList.get(j).getTbWorkingInfo().getId()) && temp.getFreesymbol().equals(tbFixEntrustContentList.get(j).getFreesymbol())) { d = d.add(new BigDecimal(tbFixEntrustContentList.get(j).getFixHourAll())); d2 = d2.add(new BigDecimal(tbFixEntrustContentList.get(j).getFixHour())); } } temp.setFixHourAll(d.doubleValue()); temp.setFixHour(d2.doubleValue()); if (!temp.getFreesymbol().equals(1d) || !temp.getFixHourAll().equals(0d)) { tbFixEntrustContentListAdd.add(temp); } } } // ?? List<TbMaintianVo> maintianvos = tbMaintainPartContentService .getTbMaintianDetailVosByEntrustId(tbFixEntrust.getId(), Constants.BALANCE_ALL); // ? List<TmStockOutDetVo> tmStockOutDetVos = tmStockOutService .getSellDetailByEntrustCode(tbFixEntrust.getEntrustCode(), Constants.BALANCE_ALL); // List<TbBusinessBalanceItem> tbBusinessBalanceItemList = tbBusinessBalanceItemService .findGroupTbBusinessBalanceItemListByTbBusinessBalanceId(tbBusinessBalance.getId()); // ??? if (null == maintianvos) { maintianvos = new ArrayList<TbMaintianVo>(); } if (null != tmStockOutDetVos && tmStockOutDetVos.size() > 0) { for (TmStockOutDetVo tmStockOutDetVo : tmStockOutDetVos) { TbMaintianVo tbMaintianVo = new TbMaintianVo(); tbMaintianVo.setPartId(tmStockOutDetVo.getPartinfoId()); tbMaintianVo.setHouseName(tmStockOutDetVo.getHouseName()); tbMaintianVo.setPartCode(tmStockOutDetVo.getPartCode()); tbMaintianVo.setPartName(tmStockOutDetVo.getPartName()); tbMaintianVo.setUnitName(tmStockOutDetVo.getUnitName()); tbMaintianVo.setPrice(tmStockOutDetVo.getPrice()); tbMaintianVo.setPartQuantity(tmStockOutDetVo.getQuantity()); tbMaintianVo.setTotal(tmStockOutDetVo.getTotal()); tbMaintianVo.setIsFree(tmStockOutDetVo.getIsFree()); tbMaintianVo.setProjectType(tmStockOutDetVo.getProjectType()); tbMaintianVo.setZl(tmStockOutDetVo.getZl()); tbMaintianVo.setXmlx(tmStockOutDetVo.getXmlx()); maintianvos.add(tbMaintianVo); } } /** * add by ccr 2010-12-18 */ List<TbMaintianVo> maintianvosTemp = new ArrayList<TbMaintianVo>(); List<TbMaintianVo> maintianvosAdd = new ArrayList<TbMaintianVo>(); if (maintianvos.size() > 0) { for (int i = 0; i < maintianvos.size(); i++) { boolean flag = false; if (maintianvosTemp.size() == 0) { maintianvosTemp.add(maintianvos.get(i)); } else { if (maintianvosTemp.size() > 1) { int l = 0; for (TbMaintianVo _tbMaintianVo : maintianvosTemp) { if (_tbMaintianVo.getPartId().equals(maintianvos.get(i).getPartId()) && _tbMaintianVo.getIsFree().equals(maintianvos.get(i).getIsFree()) && _tbMaintianVo.getPrice().equals(maintianvos.get(i).getPrice())) { // maintianvosTemp.set(l, maintianvos.get(i)); flag = true; break; } l++; } } } if (flag) { continue; } else { maintianvosTemp.add(maintianvos.get(i)); } TbMaintianVo temp = maintianvos.get(i); BigDecimal d1 = new BigDecimal(temp.getPartQuantity()); BigDecimal d2 = new BigDecimal(temp.getTotal()); for (int j = i + 1; j < maintianvos.size(); j++) { if (temp.getPartId().equals(maintianvos.get(j).getPartId()) && temp.getIsFree().equals(maintianvos.get(j).getIsFree()) && temp.getPrice().equals(maintianvos.get(j).getPrice())) { temp.setPrice(maintianvos.get(j).getPrice()); d1 = d1.add(new BigDecimal(maintianvos.get(j).getPartQuantity())); d2 = d2.add(new BigDecimal(maintianvos.get(j).getTotal())); } } temp.setPartQuantity(d1.doubleValue()); temp.setTotal(d2.doubleValue()); if (!temp.getIsFree().equals(1L) || !temp.getPartQuantity().equals(0d)) { /* * temp.setPrice(new BigDecimal(temp.getTotal()).divide(new * BigDecimal(temp.getPartQuantity()),2, * BigDecimal.ROUND_HALF_UP).setScale(2, * BigDecimal.ROUND_HALF_UP).doubleValue()); */ maintianvosAdd.add(temp); } } } int fixSize = (tbFixEntrustContentListAdd == null ? 0 : tbFixEntrustContentListAdd.size()); int partSize = (maintianvosAdd == null ? 0 : maintianvosAdd.size()); try { HSSFWorkbook workbook = new HSSFWorkbook(this.getClass().getResourceAsStream(tpl)); HSSFSheet sheet = workbook.getSheetAt(0); HSSFCellStyle style = workbook.createCellStyle(); style.setWrapText(true); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 9); style.setFont(font); HSSFCellStyle styleRight = workbook.createCellStyle(); styleRight.setWrapText(true); styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleRight.setFont(font); HSSFCellStyle styleBorderThinAll = workbook.createCellStyle(); styleBorderThinAll.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setWrapText(true); styleBorderThinAll.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThinAll.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThinAll.setFont(font); HSSFCellStyle styleBorderThickLeft = workbook.createCellStyle(); styleBorderThickLeft.setBorderLeft(HSSFCellStyle.BORDER_THICK); styleBorderThickLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setWrapText(true); styleBorderThickLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThickLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickLeft.setFont(font); HSSFCellStyle styleBorderThickRight = workbook.createCellStyle(); styleBorderThickRight.setBorderRight(HSSFCellStyle.BORDER_THICK); styleBorderThickRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setWrapText(true); styleBorderThickRight.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThickRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickRight.setFont(font); HSSFCellStyle styleBorderThinAllCenter = workbook.createCellStyle(); styleBorderThinAllCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setWrapText(true); styleBorderThinAllCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThinAllCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThinAllCenter.setFont(font); HSSFCellStyle styleBorderThickLeftCenter = workbook.createCellStyle(); styleBorderThickLeftCenter.setBorderLeft(HSSFCellStyle.BORDER_THICK); styleBorderThickLeftCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setWrapText(true); styleBorderThickLeftCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThickLeftCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickLeftCenter.setFont(font); HSSFCellStyle styleBorderThickRightCenter = workbook.createCellStyle(); styleBorderThickRightCenter.setBorderRight(HSSFCellStyle.BORDER_THICK); styleBorderThickRightCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setWrapText(true); styleBorderThickRightCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThickRightCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickRightCenter.setFont(font); HSSFCellStyle styleCenter = workbook.createCellStyle(); styleCenter.setWrapText(true); styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCenter.setFont(font); HSSFRow row = null; HSSFCell cell = null; row = sheet.getRow(2); cell = row.getCell(4); // cell.setCellStyle(style); if ("dfbz".equals(companyName)) { cell.setCellValue(Constants.getCompanyMap().get("dfbzCode")); } else if ("xtl".equals(companyName)) { cell.setCellValue(Constants.getCompanyMap().get("xtlCode")); } row = sheet.getRow(2); cell = row.getCell(17); if ("xtl".equals(companyName)) { cell.setCellValue(/* tmCompany.getCompanyName() */"?"); } else { cell.setCellValue(tmCompany.getCompanyName()); } row = sheet.getRow(2); cell = row.getCell(33); cell.setCellValue(tmCompany.getCompanyPhone() == null ? "" : tmCompany.getCompanyPhone()); row = sheet.getRow(4); cell = row.getCell(4); if ("xtl".equals(companyName)) { cell.setCellValue(""); } else { cell.setCellValue(tmCompany.getCompanyZipCode() == null ? "" : tmCompany.getCompanyZipCode()); } row = sheet.getRow(4); cell = row.getCell(17); cell.setCellValue(tmCompany.getCompanyAddress() == null ? "" : tmCompany.getCompanyAddress()); row = sheet.getRow(4); cell = row.getCell(33); cell.setCellValue(tmCompany.getCompanyFax() == null ? "" : tmCompany.getCompanyFax()); row = sheet.getRow(10); cell = row.getCell(2); // cell.setCellStyle(style); String entrustCode = tbFixEntrust.getEntrustCode(); String[] es = entrustCode.split("-"); String newCode = "RO" + es[0].substring(2, 6) + es[1]; cell.setCellValue(newCode); row = sheet.getRow(10); cell = row.getCell(24); // cell.setCellStyle(style); cell.setCellValue((tbFixEntrust.getTmUser().getUserRealName() == null || "".equals(tbFixEntrust.getTmUser().getUserRealName())) ? tbFixEntrust.getTmUser().getUserName() : tbFixEntrust.getTmUser().getUserRealName()); row = sheet.getRow(10); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( CommonMethod.parseDateToString(tbBusinessBalance.getBananceDate(), "yyyy-MM-dd HH:mm")); row = sheet.getRow(8); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getFixDate(), "yyyy-MM-dd HH:mm")); row = sheet.getRow(12); cell = row.getCell(4); cell.setCellValue(tbCustomer.getCustomerName()); row = sheet.getRow(12); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getLicenseCode()); row = sheet.getRow(12); cell = row.getCell(41); cell.setCellValue(tbCarInfo.getTmCarModelType().getModelName()); row = sheet.getRow(14); cell = row.getCell(4); cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone()); row = sheet.getRow(18); cell = row.getCell(12); cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone()); row = sheet.getRow(14); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getChassisCode()); row = sheet.getRow(16); cell = row.getCell(4); cell.setCellValue(tbCustomer.getAddress() == null ? "" : tbCustomer.getAddress()); row = sheet.getRow(16); cell = row.getCell(24); cell.setCellValue(CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(), "yyyy-MM-dd")); row = sheet.getRow(16); cell = row.getCell(41); if ("xtl".equals(companyName)) { cell.setCellValue( tbFixEntrust.getEnterStationKilo() == null ? "" : new BigDecimal(tbFixEntrust.getEnterStationKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString() + " Km"); } else { cell.setCellValue( tbFixEntrust.getEnterStationKilo() == null ? "" : new BigDecimal(tbFixEntrust.getEnterStationKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString() + " Km"); } row = sheet.getRow(18); cell = row.getCell(4); cell.setCellValue(tbCustomer.getContractPerson() == null ? "" : tbCustomer.getContractPerson()); row = sheet.getRow(18); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getEngineCode() == null ? "" : tbCarInfo.getEngineCode()); row = sheet.getRow(18); cell = row.getCell(41); cell.setCellValue(tbCarInfo.getColor() == null ? "" : tbCarInfo.getColor()); /* * if (null != tbBusinessBalanceItemList && * tbBusinessBalanceItemList.size() > 0) { * * for (TbBusinessBalanceItem tbBusinessBalanceItem : * tbBusinessBalanceItemList) { * * if ("XLCLF".equals(tbBusinessBalanceItem .getBalanceItemCode())) * { * * row = sheet.getRow(77); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * if ("XLGSF".equals(tbBusinessBalanceItem .getBalanceItemCode())) * { * * row = sheet.getRow(75); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * if ("ZJE" .equals(tbBusinessBalanceItem.getBalanceItemCode())) { * * row = sheet.getRow(81); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * } * * row = sheet.getRow(79); * * cell = row.getCell(24); * * cell.setCellValue("0.00"); * * } */ BigDecimal d_f_w = new BigDecimal("0.00"); BigDecimal d_f_p = new BigDecimal("0.00"); BigDecimal d_f_i = new BigDecimal("0.00"); BigDecimal d_f_c = new BigDecimal("0.00"); if (fixSize > 0) { for (int i = 0; i < fixSize; i++) { TbFixEntrustContent t = tbFixEntrustContentListAdd.get(i); if ("W".equals(t.getZl())) { d_f_w = d_f_w.add(new BigDecimal(t.getFixHourAll())); } else if ("P".equals(t.getZl())) { d_f_p = d_f_p.add(new BigDecimal(t.getFixHourAll())); } else if ("I".equals(t.getZl())) { d_f_i = d_f_i.add(new BigDecimal(t.getFixHourAll())); } else if ("C".equals(t.getZl())) { d_f_c = d_f_c.add(new BigDecimal(t.getFixHourAll())); } } } BigDecimal d_p_w = new BigDecimal("0.00"); BigDecimal d_p_p = new BigDecimal("0.00"); BigDecimal d_p_i = new BigDecimal("0.00"); BigDecimal d_p_c = new BigDecimal("0.00"); if (partSize > 0) { for (int i = 0; i < partSize; i++) { TbMaintianVo t = maintianvosAdd.get(i); if ("W".equals(t.getZl())) { d_p_w = d_p_w.add(new BigDecimal(t.getTotal())); } else if ("P".equals(t.getZl())) { d_p_p = d_p_p.add(new BigDecimal(t.getTotal())); } else if ("I".equals(t.getZl())) { d_p_i = d_p_i.add(new BigDecimal(t.getTotal())); } else if ("C".equals(t.getZl())) { d_p_c = d_p_c.add(new BigDecimal(t.getTotal())); } } } row = sheet.getRow(75); cell = row.getCell(6); cell.setCellValue(d_f_w.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(6); cell.setCellValue(d_p_w.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(6); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(6); cell.setCellValue( d_f_w.add(d_p_w).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(12); cell.setCellValue(d_f_p.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(12); cell.setCellValue(d_p_p.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(12); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(12); cell.setCellValue( d_f_p.add(d_p_p).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(18); cell.setCellValue(d_f_i.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(18); cell.setCellValue(d_p_i.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(18); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(18); cell.setCellValue( d_f_i.add(d_p_i).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(24); cell.setCellValue(d_f_c.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(24); cell.setCellValue(d_p_c.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(24); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(24); cell.setCellValue( d_f_c.add(d_p_c).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); Double wt = tbBusinessBalance.getWorkingHourTotalAll() == null ? 0d : tbBusinessBalance.getWorkingHourTotalAll(); Double wf = tbBusinessBalance.getWorkingHourFavourRate() == null ? 0d : tbBusinessBalance.getWorkingHourFavourRate(); Double pt = tbBusinessBalance.getFixPartTotalAll() == null ? 0d : tbBusinessBalance.getFixPartTotalAll(); Double pf = tbBusinessBalance.getFixPartFavourRate() == null ? 0d : tbBusinessBalance.getFixPartFavourRate(); Double st = tbBusinessBalance.getSolePartTotalAll() == null ? 0d : tbBusinessBalance.getSolePartTotalAll(); Double sf = tbBusinessBalance.getSolePartFavourRate() == null ? 0d : tbBusinessBalance.getSolePartFavourRate(); BigDecimal fixF = new BigDecimal(wt).divide(new BigDecimal(1 - wf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(wf)); BigDecimal partF = new BigDecimal(pt).divide(new BigDecimal(1 - pf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(pf)); BigDecimal soleF = new BigDecimal(st).divide(new BigDecimal(1 - sf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(sf)); row = sheet.getRow(83); cell = row.getCell(4); // cell.setCellStyle(styleCenter); cell.setCellValue(fixF.add(partF).add(soleF).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); row = sheet.getRow(83); cell = row.getCell(14); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbBusinessBalance.getPayedAmount()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(68); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(CommonMethod .parseDateToString(tbBusinessBalance.getTbFixEntrust().getRemindMaintainDate(), "yyyy-MM-dd")); row = sheet.getRow(70); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(tbBusinessBalance.getTbFixEntrust().getRemindMaintainKilo() == null ? "" : new BigDecimal(tbBusinessBalance.getTbFixEntrust().getRemindMaintainKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP) + ""); row = sheet.getRow(77); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(tbBusinessBalance.getTbFixEntrust().getRemark()); ByteArrayOutputStream byteArrayOutImgLion = new ByteArrayOutputStream(); String pic = ""; if ("dfbz".equals(companyName)) { pic = "/lion_jsd.png"; } else if ("xtl".equals(companyName)) { pic = "/xtl_jsd.png"; } BufferedImage bufferImgLion = ImageIO.read(this.getClass().getResourceAsStream(pic)); ImageIO.write(bufferImgLion, "png", byteArrayOutImgLion); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchorLion = new HSSFClientAnchor(0, 0, 1023, 200, (short) 41, 0, (short) 46, 7); patriarch.createPicture(anchorLion, workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); /* * int maxSize = 19; * * int maxFixSize = (fixSize > 19 ? 19 : fixSize); * * int maxPartSize = maxSize - maxFixSize > partSize ? partSize : * maxSize - maxFixSize; * * int partStartRow = (fixSize == 0 ? 21 : 21 + maxFixSize * 2 + 4); * * int partStartRowClone = ((fixSize - maxFixSize) == 0 ? 21 : 21 + * (fixSize - maxFixSize) * 2 + 4); * * int minBlank = 9; * * if ((fixSize + partSize) > 17) { * * HSSFSheet sheetClone = workbook.cloneSheet(0); * * HSSFRow rowClone = sheetClone.getRow(87); * * HSSFCell cellClone = rowClone.getCell(34); * * cellClone.setCellValue("2 2 "); * * HSSFPatriarch patriarchClone = sheetClone * .createDrawingPatriarch(); * * HSSFClientAnchor anchorLionClone = new HSSFClientAnchor(0, 0, * 1023, 200, (short) 41, 0, (short) 47, 8); * * patriarchClone.createPicture(anchorLionClone, workbook * .addPicture(byteArrayOutImgLion.toByteArray(), * HSSFWorkbook.PICTURE_TYPE_PNG)); * * if ((fixSize - maxFixSize) > 0) { * * * sheetClone.addMergedRegion(new Region(21, (short) 0, 21, (short) * 49)); * * sheetClone.addMergedRegion(new Region(21, (short) 0, 21, (short) * 0)); * * * sheetClone.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); * * rowClone = sheetClone.getRow(21); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellValue(""); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(21); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(24); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(23); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * for (int i = 0; i < (fixSize - maxFixSize); i++) { * * TbFixEntrustContent tbFixEntrustContent = * tbFixEntrustContentListAdd .get(i + maxFixSize); * * rowClone = sheetClone.getRow(25 + i * 2); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(i + 1); * * cellClone = rowClone.getCell(2); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getStationCode()); * * cellClone = rowClone.getCell(10); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getStationName()); * * cellClone = rowClone.getCell(21); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent.getZl()); * * cellClone = rowClone.getCell(24); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getProjectType()); * * cellClone = rowClone.getCell(30); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getFixHour()).divide( new BigDecimal("1.00"), * 2, BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(34); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getWorkingHourPrice()) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(40); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbBusinessBalance * .getWorkingHourFavourRate()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(44); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getFixHourAll()).divide( new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } * * } * * if ((partSize - maxPartSize) > 0) { * * * sheetClone.addMergedRegion(new Region(partStartRowClone, (short) * 0, partStartRowClone, (short) 49)); * * sheetClone.addMergedRegion(new Region(partStartRowClone, (short) * 0, partStartRowClone, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * partStartRowClone, partStartRowClone, 0, 49)); * * rowClone = sheetClone.getRow(partStartRowClone); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellStyle(styleBorderThickLeft); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(partStartRowClone + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(partStartRowClone + 2 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(partStartRowClone + 2); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * for (int i = 0; i < (partSize - maxPartSize); i++) { * * TbMaintianVo tbMaintianVo = maintianvosAdd.get(i + maxPartSize); * * rowClone = sheetClone.getRow(partStartRowClone + 4 + i 2); * * if(null == rowClone) break; * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(i + 1); * * cellClone = rowClone.getCell(2); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getPartCode()); * * cellClone = rowClone.getCell(10); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getPartName()); * * cellClone = rowClone.getCell(21); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getZl()); * * cellClone = rowClone.getCell(24); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getProjectType()); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleRight); * * cellClone.setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(34); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbMaintianVo * .getPrice()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(40); * * // .setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbBusinessBalance * .getFixPartFavourRate()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(44); * * // cellClone.setCellStyle(styleCenter); * * cellClone .setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()) .multiply( new BigDecimal(tbMaintianVo * .getPrice())) .divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP) .toString()); } * * } * * int attachStartRowClone = 21; * * if ((fixSize - maxFixSize + partSize - maxPartSize) <= 17) { * * if ((fixSize - maxFixSize) != 0 && (partSize - maxPartSize) == 0 * && (fixSize - maxFixSize) < 18) { * * attachStartRowClone = 21 + 2 + 2 + (fixSize - maxFixSize) * 2; * * } * * if ((partSize - maxPartSize) != 0 && (fixSize - maxFixSize) == 0 * && (partSize - maxPartSize) < 18) { * * attachStartRowClone = 21 + 2 + 2 + 2 + (partSize - maxPartSize) * * 2; } * * if ((fixSize - maxFixSize) != 0 && (partSize - maxPartSize) != 0 * && (fixSize - maxFixSize + partSize - maxPartSize < 18)) { * * attachStartRowClone = 21 + 2 + 2 + 2 + 2 + (fixSize - maxFixSize) * * 2 + (partSize - maxPartSize) * 2; * * } * * * sheetClone.addMergedRegion(new Region(attachStartRowClone, * (short) 0, attachStartRowClone, (short) 49)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone, * (short) 0, attachStartRowClone, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone, attachStartRowClone, 0, 49)); * * rowClone = sheetClone.getRow(attachStartRowClone); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellStyle(styleBorderThickLeft); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(attachStartRowClone + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(attachStartRowClone + 2 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(attachStartRowClone + 2); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * if ("dfbz".equals(companyName)) { * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * } else if ("xtl".equals(companyName)) { * * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 2, * (short) 2, attachStartRowClone + 2, (short) 39)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 2, * (short) 2, attachStartRowClone + 2, (short) 39)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone + 2, attachStartRowClone + 2, 2, 39)); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * } * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(attachStartRowClone + 4); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("1"); * * rowClone = sheetClone.getRow(attachStartRowClone + 4 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 6, * (short) 0, 66, (short) 49)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 6, * (short) 0, 66, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone + 6, 66, 0, 49)); * * } * * } else { * * row = sheet.getRow(87); * * cell = row.getCell(34); * * cell.setCellValue(" 1 1 "); * * } * * if (fixSize > 0) { * * * sheet.addMergedRegion(new Region(21, (short) 0, 21, (short) 49)); * * sheet.addMergedRegion(new Region(21, (short) 0, 21, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); * * row = sheet.getRow(21); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellValue(""); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(22); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(24); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(23); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell.setCellStyle(styleBorderThickLeftCenter); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * for (int i = 0; i < maxFixSize; i++) { * * TbFixEntrustContent tbFixEntrustContent = * tbFixEntrustContentListAdd .get(i); * * row = sheet.getRow(25 + i * 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(i + 1); * * cell = row.getCell(2); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getStationCode()); * * cell = row.getCell(10); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getStationName()); * * cell = row.getCell(21); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getZl()); * * cell = row.getCell(24); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getProjectType()); * * cell = row.getCell(30); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getFixHour()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(34); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(40); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal((1 - tbBusinessBalance * .getWorkingHourFavourRate()) * 100) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(44); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getFixHourAll()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); } * * } * * if ((fixSize <= maxSize - 1) && partSize > 0) { * * * sheet.addMergedRegion(new Region(partStartRow, (short) 0, * partStartRow, (short) 49)); * * sheet.addMergedRegion(new Region(partStartRow, (short) 0, * partStartRow, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(partStartRow, * partStartRow, 0, 49)); * * row = sheet.getRow(partStartRow); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellValue(""); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(partStartRow + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(partStartRow + 2 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * row = sheet.getRow(partStartRow + 2); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("??"); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * if (partSize > 0) { * * for (int i = 0; i < maxPartSize; i++) { * * TbMaintianVo tbMaintianVo = maintianvosAdd.get(i); * * row = sheet.getRow(partStartRow + 4 + i * 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(i + 1); * * cell = row.getCell(2); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getPartCode()); * * cell = row.getCell(10); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getPartName()); * * cell = row.getCell(21); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getZl()); * * cell = row.getCell(24); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getProjectType()); * * cell = row.getCell(30); * * cell.setCellStyle(styleRight); * * cell.setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(34); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbMaintianVo * .getPrice()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(40); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal((1 - tbBusinessBalance * .getFixPartFavourRate()) * 100).divide( new BigDecimal("1.00"), * 2, BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(44); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbMaintianVo .getPartQuantity()) * .multiply( new BigDecimal(tbMaintianVo.getPrice())) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } * * } * * } * * int attachStartRow = 21; * * if ((fixSize + partSize) <= 17) { * * if (fixSize != 0 && partSize == 0 && fixSize < 18) { * * attachStartRow = 19 + 2 + 2 + 2 + fixSize * 2; * * } * * if (partSize != 0 && fixSize == 0 && partSize < 18) { * * attachStartRow = 19 + 2 + 2 + 2 + partSize * 2; } * * if (fixSize != 0 && partSize != 0 && (fixSize + partSize < 18)) { * * attachStartRow = 19 + 2 + 2 + 2 + 2 + 2 + fixSize * 2 + partSize * * 2; * * } * * * sheet.addMergedRegion(new Region(attachStartRow, (short) 0, * attachStartRow, (short) 49)); * * sheet.addMergedRegion(new Region(attachStartRow, (short) 0, * attachStartRow, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(attachStartRow, * attachStartRow, 0, 49)); * * row = sheet.getRow(attachStartRow); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellStyle(styleBorderThickLeft); * * cell.setCellValue(""); * * row = sheet.getRow(attachStartRow + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(attachStartRow + 2 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * row = sheet.getRow(attachStartRow + 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("??"); * * if ("dfbz".equals(companyName)) { * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * } else if ("xtl".equals(companyName)) { * * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * * sheet.addMergedRegion(new CellRangeAddress( attachStartRow + 2, * attachStartRow + 2, 2, 39)); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * } * * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * row = sheet.getRow(attachStartRow + 4); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("1"); * * row = sheet.getRow(attachStartRow + 4 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * if ("xtl".equals(companyName)) { * * * sheet.addMergedRegion(new Region(attachStartRow + 6, (short) 0, * 66, (short) 49)); * * sheet.addMergedRegion(new Region(attachStartRow + 6, (short) 0, * 66, (short) 49)); * * if (attachStartRow + 6 < 66) { * * sheet.addMergedRegion(new CellRangeAddress( attachStartRow + 6, * 66, 0, 49)); } * * } } */ int pageSize = 0; if ((fixSize > 0 && partSize == 0) || (partSize > 0 && fixSize == 0)) { if ((fixSize + partSize) <= 18) { pageSize = 1; } else { pageSize = (fixSize + partSize - 19) / 21 + 2; } for (int p = pageSize - 1; p >= 0; p--) { int maxSize = (p == pageSize - 1) ? (fixSize + partSize - p * 21) : 21; int startRow = 21; if (p == 0) { sheet = workbook.getSheetAt(0); for (int k = 1; k <= pageSize / 2; k++) { workbook.setSheetOrder(workbook.getSheetName(k), pageSize - k); } for (int k = 1; k < pageSize; k++) { workbook.setSheetName(k, "" + (k + 1) + ""); } workbook.setSheetName(0, "" + 1 + ""); } else { sheet = workbook.cloneSheet(0); //this.clearSheet(sheet, 21, 0, 66, 49); } if (fixSize > 0) { sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); row = sheet.getRow(21); cell = row.getCell(0); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(22); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(24); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(23); cell = row.getCell(0); cell.setCellValue("??"); cell.setCellStyle(styleBorderThickLeftCenter); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < maxSize; i++) { TbFixEntrustContent tbFixEntrustContent = tbFixEntrustContentListAdd.get(p * 21 + i); row = sheet.getRow(25 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * 21 + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getProjectType()); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHour()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( new BigDecimal((1 - tbBusinessBalance.getWorkingHourFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHourAll()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } else if (partSize > 0) { sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 0, 49)); row = sheet.getRow(startRow); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(startRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(startRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(startRow + 2); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < maxSize; i++) { TbMaintianVo tbMaintianVo = maintianvosAdd.get(p * 21 + i); row = sheet.getRow(startRow + 4 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * 21 + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getProjectType()); cell = row.getCell(30); cell.setCellStyle(styleRight); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal((1 - tbBusinessBalance.getFixPartFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .multiply(new BigDecimal(tbMaintianVo.getPrice())) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } row = sheet.getRow(87); cell = row.getCell(34); cell.setCellValue(" " + (p + 1) + " " + pageSize + " "); if (p == pageSize - 1) { int attachStartRow = 21 + 2 + 2 + (fixSize + partSize - (pageSize - 1) * 21) * 2; sheet.addMergedRegion(new CellRangeAddress(attachStartRow, attachStartRow, 0, 49)); row = sheet.getRow(attachStartRow); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(attachStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(attachStartRow + 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); if ("dfbz".equals(companyName)) { cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); } else if ("xtl".equals(companyName)) { cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue(""); } cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 4); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("1"); row = sheet.getRow(attachStartRow + 4 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); if ("xtl".equals(companyName)) { if (attachStartRow + 6 < 66) { sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 6, 66, 0, 49)); } } } } } if (fixSize > 0 && partSize > 0) { if ((fixSize + partSize) <= 16) { pageSize = 1; } else { pageSize = (fixSize + partSize - 17) / 19 + 2; } int fixPageSize = fixSize / pageSize; int partPageSize = partSize / pageSize; int fixLastPageSize = fixSize - (pageSize - 1) * fixPageSize; int partLastPageSize = partSize - (pageSize - 1) * partPageSize; if ((fixPageSize + partPageSize) < 19 && fixPageSize != 0) { //if(fixLastPageSize >= partSize){ if (fixPageSize >= partPageSize) { fixPageSize = (19 - partPageSize) > fixSize ? fixSize : (19 - partPageSize); } else { partPageSize = (19 - fixPageSize) > partSize ? partSize : (19 - fixPageSize); } } fixLastPageSize = fixSize - (pageSize - 1) * fixPageSize; partLastPageSize = (partSize - (pageSize - 1) * partPageSize) < 0 ? 0 : (partSize - (pageSize - 1) * partPageSize); for (int p = pageSize - 1; p >= 0; p--) { int partStartRow = 21 + 2 + 2 + ((p == pageSize - 1) ? fixLastPageSize : fixPageSize) * 2; if (p == 0) { sheet = workbook.getSheetAt(0); if (pageSize > 1) { for (int k = 1; k <= pageSize / 2; k++) { workbook.setSheetOrder(workbook.getSheetName(k), pageSize - k); } for (int k = 1; k < pageSize; k++) { workbook.setSheetName(k, "" + (k + 1) + ""); } } workbook.setSheetName(0, "" + 1 + ""); } else { sheet = workbook.cloneSheet(0); } if (fixSize > 0) { sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); row = sheet.getRow(21); cell = row.getCell(0); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(22); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(24); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(23); cell = row.getCell(0); cell.setCellValue("??"); cell.setCellStyle(styleBorderThickLeftCenter); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < ((p == pageSize - 1) ? fixLastPageSize : fixPageSize); i++) { TbFixEntrustContent tbFixEntrustContent = tbFixEntrustContentListAdd .get(p * fixPageSize + i); row = sheet.getRow(25 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * fixPageSize + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getProjectType()); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHour()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( new BigDecimal((1 - tbBusinessBalance.getWorkingHourFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHourAll()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } if (partSize > 0) { sheet.addMergedRegion(new CellRangeAddress(partStartRow, partStartRow, 0, 49)); row = sheet.getRow(partStartRow); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(partStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(partStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(partStartRow + 2); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < ((p == pageSize - 1) ? partLastPageSize : partPageSize); i++) { if (p * partPageSize + i > partSize - 1) { break; } TbMaintianVo tbMaintianVo = maintianvosAdd.get(p * partPageSize + i); row = sheet.getRow(partStartRow + 4 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * partPageSize + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getProjectType()); cell = row.getCell(30); cell.setCellStyle(styleRight); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal((1 - tbBusinessBalance.getFixPartFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .multiply(new BigDecimal(tbMaintianVo.getPrice())) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } row = sheet.getRow(87); cell = row.getCell(34); cell.setCellValue(" " + (p + 1) + " " + pageSize + " "); if (p == pageSize - 1) { int attachStartRow = 21 + 2 + 2 + 2 + 2 + (fixLastPageSize + partLastPageSize) * 2; sheet.addMergedRegion(new CellRangeAddress(attachStartRow, attachStartRow, 0, 49)); row = sheet.getRow(attachStartRow); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(attachStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(attachStartRow + 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); if ("dfbz".equals(companyName)) { cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); } else if ("xtl".equals(companyName)) { cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue(""); } cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 4); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("1"); row = sheet.getRow(attachStartRow + 4 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); if ("xtl".equals(companyName)) { if (attachStartRow + 6 < 66) { sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 6, 66, 0, 49)); } } } } } workbook.write(os); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 8 41 }