Ejemplo n.º 1
0
  private void createLabel(WritableSheet sheet) throws WriteException {

    WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
    // Define the cell format
    times = new WritableCellFormat(times10pt);
    // Lets automatically wrap the cells
    times.setWrap(true);

    // create create a bold font with unterlines
    WritableFont times10ptBoldUnderline =
        new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE);
    timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
    // Lets automatically wrap the cells
    timesBoldUnderline.setWrap(true);

    CellView cv = new CellView();
    cv.setFormat(times);
    cv.setFormat(timesBoldUnderline);

    // Write a few headers
    addCaption(sheet, 0, 0, "Adı");
    addCaption(sheet, 1, 0, "Miktar");
    addCaption(sheet, 2, 0, "Birim");
    addCaption(sheet, 3, 0, "Fiyat");
    addCaption(sheet, 4, 0, "Etiketler");
  }
Ejemplo n.º 2
0
  public static void addColumn(
      WritableSheet sheet, int startCol, int startRow, CellValue[] cellValues)
      throws WriteException {

    WritableCellFormat cellFormat = new WritableCellFormat();
    cellFormat.setWrap(true);
    cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

    NumberFormat towdps = new NumberFormat("#,##0");
    WritableCellFormat towdpsFormat = new WritableCellFormat(towdps);
    towdpsFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

    NumberFormat towdps2 = new NumberFormat("#,##0.00");
    WritableCellFormat towdpsFormat2 = new WritableCellFormat(towdps2);
    towdpsFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);

    DateFormat customDateFormat = new DateFormat("dd MMM yyyy");
    WritableCellFormat dateFormat = new WritableCellFormat(customDateFormat);
    dateFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

    for (int i = startRow; i < cellValues.length; i++) {
      if (cellValues[i] != null && cellValues[i].getValue() != null) {
        if (cellValues[i].getType().equals(CellDataType.STRING)) {
          Label label =
              new Label(startCol, i, String.valueOf(cellValues[i].getValue()), cellFormat);
          sheet.addCell(label);
        } else if (cellValues[i].getType().equals(CellDataType.INT)) {
          Number number = new Number(startCol, i, (Integer) cellValues[i].getValue(), towdpsFormat);
          sheet.addCell(number);
        } else if (cellValues[i].getType().equals(CellDataType.DOUBLE)) {
          Number number = new Number(startCol, i, (Double) cellValues[i].getValue(), towdpsFormat);
          sheet.addCell(number);
        } else if (cellValues[i].getType().equals(CellDataType.FLOAT)) {
          Number number = new Number(startCol, i, (Float) cellValues[i].getValue(), towdpsFormat);
          sheet.addCell(number);
        } else if (cellValues[i].getType().equals(CellDataType.DATE)) {
          Date now = new Date(((Timestamp) cellValues[i].getValue()).getTime());
          DateTime dateCell = new DateTime(startCol, i, now, dateFormat);
          sheet.addCell(dateCell);
        }
      } else {
        Label label = new Label(startCol, i, "", cellFormat);
        sheet.addCell(label);
      }
    }
  }
  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);
    }
  }
Ejemplo n.º 5
0
  /**
   * 业务逻辑实现 - 点击统计下载excel
   *
   * @param ws 工作表
   * @param totals 待写入数据
   * @throws WriteException
   */
  @SuppressWarnings("unchecked")
  private static void addLabelExcel(WritableSheet ws, List totals, List columnList)
      throws WriteException {
    int row = 0, column = 0;
    // 设置Excel列中文标题
    Iterator itColumnList = columnList.iterator();
    while (itColumnList.hasNext()) {
      DownloadAttribute columnTitle = (DownloadAttribute) itColumnList.next();
      ws.addCell(new Label(column, row, columnTitle.getExcelName()));
      column++;
    }
    if (totals != null && totals.size() != 0) {
      Iterator itTotals = totals.iterator();
      WritableFont detFont =
          new WritableFont(
              WritableFont.ARIAL,
              10,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK);
      NumberFormat pnf = new NumberFormat("0.00"); // 用于Price Number的格式
      WritableCellFormat priceFormat = new WritableCellFormat(detFont, pnf);
      NumberFormat nf = new NumberFormat("0"); // 用于Number的格式
      WritableCellFormat numberFormat = new WritableCellFormat(detFont, nf);

      WritableFont stringFont =
          new WritableFont(
              WritableFont.ARIAL,
              10,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK);
      WritableCellFormat stringFormat = new WritableCellFormat(stringFont);
      WritableCellFormat stringWrapFormat = new WritableCellFormat(stringFont);
      stringWrapFormat.setWrap(true);

      row = 1;
      while (itTotals.hasNext()) {
        Object obj = itTotals.next();
        Map subTotals = obj instanceof Map ? (HashMap) obj : ObjectUtil.transform(obj);
        Iterator itColumnName = columnList.iterator();
        column = 0;
        while (itColumnName.hasNext()) {
          DownloadAttribute columnInfo = (DownloadAttribute) itColumnName.next();
          String columnType = columnInfo.getExcelType();
          String columnName = columnInfo.getRowName();
          Object columnValue = subTotals.get(columnName);
          if (columnValue == null) {
            column++;
            continue;
          }
          String strValue = null;
          if (columnValue instanceof Date) {
            strValue = DateTimeUtil.getFormatDateTime((Date) columnValue);
          } else {
            strValue = columnValue.toString();
          }
          // 设置内容
          if (columnType.equals("Label")) {
            if (columnValue.toString().indexOf("\r\n") > 0) {
              ws.addCell(new Label(column, row, strValue, stringWrapFormat));
            } else {
              ws.addCell(new Label(column, row, strValue, stringFormat));
            }
          }
          if (columnType.equals("Number"))
            ws.addCell(
                new jxl.write.Number(column, row, Double.parseDouble(strValue), numberFormat));
          if (columnType.equals("Price"))
            ws.addCell(
                new jxl.write.Number(column, row, Double.parseDouble(strValue), priceFormat));
          column++;
        }
        row++;
      }
    }
  }