Ejemplo n.º 1
0
  public static void main(String[] args) throws Exception {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
    else wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
      headerCell = headerRow.createCell(i);
      headerCell.setCellValue(titles[i]);
      headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
      Row row = sheet.createRow(rownum++);
      for (int j = 0; j < titles.length; j++) {
        Cell cell = row.createCell(j);
        if (j == 9) {
          // the 10th cell contains sum over week days, e.g. SUM(C3:I3)
          String ref = "C" + rownum + ":I" + rownum;
          cell.setCellFormula("SUM(" + ref + ")");
          cell.setCellStyle(styles.get("formula"));
        } else if (j == 11) {
          cell.setCellFormula("J" + rownum + "-K" + rownum);
          cell.setCellStyle(styles.get("formula"));
        } else {
          cell.setCellStyle(styles.get("cell"));
        }
      }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
      cell = sumRow.createCell(j);
      String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
      cell.setCellFormula("SUM(" + ref + ")");
      if (j >= 9) cell.setCellStyle(styles.get("formula_2"));
      else cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
      Row row = sheet.getRow(2 + i);
      for (int j = 0; j < sample_data[i].length; j++) {
        if (sample_data[i][j] == null) continue;

        if (sample_data[i][j] instanceof String) {
          row.getCell(j).setCellValue((String) sample_data[i][j]);
        } else {
          row.getCell(j).setCellValue((Double) sample_data[i][j]);
        }
      }
    }

    // finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
      sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook) file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
  }
Ejemplo n.º 2
0
  private void saveExcelPoject(File file) throws IOException {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("timeplan");
    // Заголовок в 0 строке
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("Филиал");
    cell = row.createCell(1);
    cell.setCellValue("Город");
    Calendar cal = Calendar.getInstance();
    cal.set(2017, 0, 5); // Начальная дата проекта
    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
    for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
      cell = row.createCell(i + 2);
      cell.setCellValue(sdf.format(cal.getTime()));
      cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
    }

    // sheet.setColumnWidth(0, 256);

    // Цвета ячеек
    CellStyle[] styles = new CellStyle[6];
    styles[0] = wb.createCellStyle();
    styles[0].setFillForegroundColor(HSSFColor.RED.index);
    styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[1] = wb.createCellStyle();
    styles[1].setFillForegroundColor(HSSFColor.GREEN.index);
    styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[2] = wb.createCellStyle();
    styles[2].setFillForegroundColor(HSSFColor.BLUE.index);
    styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[3] = wb.createCellStyle();
    styles[3].setFillForegroundColor(HSSFColor.ROSE.index);
    styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[4] = wb.createCellStyle();
    styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[5] = wb.createCellStyle();
    styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND);

    short rowIdx = 0;
    for (Region region : this.regions) {
      row = sheet.createRow(++rowIdx);
      cell = row.createCell(0);
      cell.setCellValue(region.filial);
      cell = row.createCell(1);
      cell.setCellValue(region.name);

      cal = Calendar.getInstance();
      cal.set(2017, 0, 5); // Начальная дата проекта
      for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
        short color = region.getDateColorIndex(cal.getTime());
        if (color >= 0) {
          cell = row.createCell(i + 2);
          cell.setCellStyle(styles[color]);
        }

        cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
      }
    }

    try (FileOutputStream fileOut = new FileOutputStream(file)) {
      wb.write(fileOut);
    }
  }