private void exportReport2Excel( ExportMaterialReportBean bean, ExportMaterialReportDTO results, HttpServletRequest request, HttpServletResponse response) { try { String outputFileName = "/files/temp/BaoCaoNhapXuatTonNPL" + System.currentTimeMillis() + ".xls"; String reportTemplate = request .getSession() .getServletContext() .getRealPath("/files/export/ExportMaterialReport.xls"); String export2FileName = request.getSession().getServletContext().getRealPath(outputFileName); Workbook templateWorkbook = Workbook.getWorkbook(new File(reportTemplate)); WritableWorkbook workbook = Workbook.createWorkbook(new File(export2FileName), templateWorkbook); WritableSheet sheet = workbook.getSheet(0); WritableFont normalFont = new WritableFont( WritableFont.TIMES, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat normalFormat = new WritableCellFormat(normalFont); normalFormat.setAlignment(Alignment.LEFT); normalFormat.setWrap(true); normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); WritableFont boldFont = new WritableFont( WritableFont.TIMES, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat headerFormat = new WritableCellFormat(boldFont); headerFormat.setAlignment(Alignment.CENTRE); headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE); headerFormat.setWrap(true); headerFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); headerFormat.setBackground(jxl.format.Colour.GRAY_25); WritableCellFormat boldFormat = new WritableCellFormat(boldFont); boldFormat.setAlignment(Alignment.CENTRE); boldFormat.setWrap(true); boldFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); DecimalFormat decimalFormat = new DecimalFormat("###,###.##"); Date from = bean.getFromDate(); Label fromCell = new Label( 1, 1, from != null ? DateUtils.date2String(from, "dd/MM/yyyy") : "", boldFormat); sheet.addCell(fromCell); Date to = bean.getToDate(); Label toCell = new Label(1, 2, to != null ? DateUtils.date2String(to, "dd/MM/yyyy") : "", boldFormat); sheet.addCell(toCell); int startRow = 5; List<ExportMaterialReportDetailDTO> initialValue = results.getInitialValue(); Map<String, Double> mapImportValue = results.getMapImportValue(); Map<String, Double> mapExportUtilDateValue = results.getMapExportUtilDateValue(); Map<String, Double> mapExportDuringDateValue = results.getMapExportDuringDateValue(); int index; CellValue[] resValue; Double iVal; Double exportToVal; Double importVal; Double exportVal; Double remainVal; String key, origin; for (ExportMaterialReportDetailDTO initVal : initialValue) { origin = initVal.getOrigin() != null ? initVal.getOrigin().getOriginID().toString() : ""; key = initVal.getMaterial().getMaterialID() + "_" + origin; exportToVal = mapExportUtilDateValue.get(key) != null ? mapExportUtilDateValue.get(key) : 0d; iVal = initVal.getQuantity() != null ? initVal.getQuantity() - exportToVal : 0 - exportToVal; importVal = mapImportValue.get(key) != null ? mapImportValue.get(key) : 0d; exportVal = mapExportDuringDateValue.get(key) != null ? mapExportDuringDateValue.get(key) : 0d; remainVal = iVal + importVal - exportVal; index = 0; resValue = new CellValue[10]; resValue[index++] = new CellValue( CellDataType.STRING, initVal.getOrigin() != null ? initVal.getOrigin().getName() : ""); resValue[index++] = new CellValue( CellDataType.STRING, initVal.getMaterial() != null ? initVal.getMaterial().getName() : ""); resValue[index++] = new CellValue( CellDataType.STRING, StringUtils.isNotBlank(initVal.getCode()) ? initVal.getCode() : ""); resValue[index++] = new CellValue( CellDataType.STRING, initVal.getMaterial() != null ? initVal.getMaterial().getUnit() != null ? initVal.getMaterial().getUnit().getName() : "" : ""); resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(iVal)); resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(importVal)); resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(exportVal)); resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(remainVal)); resValue[index++] = new CellValue(CellDataType.STRING, ""); resValue[index++] = new CellValue( CellDataType.STRING, initVal.getImportDate() != null ? DateUtils.date2String(initVal.getImportDate(), "dd/MM/yyyy") : ""); ExcelUtil.addRow( sheet, startRow++, resValue, normalFormat, normalFormat, normalFormat, normalFormat); } workbook.write(); workbook.close(); response.sendRedirect( request.getSession().getServletContext().getContextPath() + outputFileName); } catch (Exception ex) { logger.error(ex.getMessage(), ex); } }
private void exportUsedMaterial2Excel( SearchUsedMaterialBean bean, SummaryUsedMaterialDTO result, HttpServletRequest request, HttpServletResponse response) { try { String outputFileName = "/files/temp/VatTuSuDung_" + System.currentTimeMillis() + ".xls"; String reportTemplate = request.getSession().getServletContext().getRealPath("/files/export/UsedMaterial.xls"); String export2FileName = request.getSession().getServletContext().getRealPath(outputFileName); Workbook templateWorkbook = Workbook.getWorkbook(new File(reportTemplate)); WritableWorkbook workbook = Workbook.createWorkbook(new File(export2FileName), templateWorkbook); WritableSheet sheet = workbook.getSheet(0); WritableFont normalFont = new WritableFont( WritableFont.TIMES, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat normalFormat = new WritableCellFormat(normalFont); normalFormat.setAlignment(Alignment.CENTRE); normalFormat.setWrap(true); normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); DecimalFormat decimalFormat = new DecimalFormat("###,###.##"); DecimalFormat decimalFormat2 = new DecimalFormat("###,###.####"); int startRow = 4; int stt = 1; List<UsedMaterialDTO> shareUsedMaterials = result.getShareUsedMaterials(); List<UsedMaterialDTO> usedProducts = result.getUsedProducts(); List<UsedMaterialDTO> usedMeasurementMaterials = result.getUsedMeasurementMaterials(); Double totalMainProductKg = 0d; Double totalMainProductMet2 = 0d; Integer counter = 0; Double totalMet2 = 0d; Double totalKg = 0d; Double kgMet; Double kgTan; Double materialUsed; if (usedProducts != null && usedProducts.size() > 0) { for (UsedMaterialDTO usedProduct : usedProducts) { totalKg = usedProduct.getTotalKgUsed(); totalMet2 = usedProduct.getTotalMUsed() * Integer.valueOf(usedProduct.getWidth()) / 1000; counter++; totalMainProductKg += totalKg; totalMainProductMet2 += totalMet2; addUsedMaterialRow( sheet, normalFormat, decimalFormat, decimalFormat2, startRow++, counter, usedProduct.getProductName().getName(), "Kg", totalKg, null, null); if (usedProduct.getUsedMaterialDTOs() != null && usedProduct.getUsedMaterialDTOs().size() > 0) { for (UsedMaterialDTO usedMaterial : usedProduct.getUsedMaterialDTOs()) { counter++; materialUsed = usedMaterial.getTotalUsed(); kgMet = totalMet2 > 0 ? materialUsed / totalMet2 : null; kgTan = totalKg > 0 ? materialUsed * 1000 / totalKg : null; addUsedMaterialRow( sheet, normalFormat, decimalFormat, decimalFormat2, startRow++, counter, usedMaterial.getMaterial().getName(), usedMaterial.getMaterial().getUnit().getName(), materialUsed, kgMet, kgTan); } } } } if (usedMeasurementMaterials != null && usedMeasurementMaterials.size() > 0) { for (UsedMaterialDTO usedMaterial : usedMeasurementMaterials) { counter++; materialUsed = usedMaterial.getTotalUsed(); kgMet = totalMet2 > 0 ? materialUsed / totalMainProductMet2 : null; kgTan = totalKg > 0 ? materialUsed * 1000 / totalMainProductKg : null; addUsedMaterialRow( sheet, normalFormat, decimalFormat, decimalFormat2, startRow++, counter, usedMaterial.getMaterial().getName(), usedMaterial.getMaterial().getUnit().getName(), materialUsed, kgMet, kgTan); } } if (shareUsedMaterials != null && shareUsedMaterials.size() > 0) { for (UsedMaterialDTO usedMaterial : shareUsedMaterials) { counter++; materialUsed = usedMaterial.getTotalUsed(); kgMet = totalMet2 > 0 ? materialUsed / totalMainProductMet2 : null; kgTan = totalKg > 0 ? materialUsed * 1000 / totalMainProductKg : null; addUsedMaterialRow( sheet, normalFormat, decimalFormat, decimalFormat2, startRow++, counter, usedMaterial.getMaterial().getName(), usedMaterial.getMaterial().getUnit().getName(), materialUsed, kgMet, kgTan); } } workbook.write(); workbook.close(); response.sendRedirect( request.getSession().getServletContext().getContextPath() + outputFileName); } catch (Exception ex) { logger.error(ex.getMessage(), ex); } }