Пример #1
5
 /**
  * 初始化函数
  *
  * @param title 表格标题,传“空值”,表示无标题
  * @param headerList 表头列表
  */
 private void initialize(String title, List<String> headerList) {
   this.wb = new SXSSFWorkbook(500);
   this.sheet = wb.createSheet("Export");
   this.styles = createStyles(wb);
   // Create title
   if (StringUtils.isNotBlank(title)) {
     Row titleRow = sheet.createRow(rownum++);
     titleRow.setHeightInPoints(30);
     Cell titleCell = titleRow.createCell(0);
     titleCell.setCellStyle(styles.get("title"));
     titleCell.setCellValue(title);
     sheet.addMergedRegion(
         new CellRangeAddress(
             titleRow.getRowNum(),
             titleRow.getRowNum(),
             titleRow.getRowNum(),
             headerList.size() - 1));
   }
   // Create header
   if (headerList == null) {
     throw new RuntimeException("headerList not null!");
   }
   Row headerRow = sheet.createRow(rownum++);
   headerRow.setHeightInPoints(16);
   for (int i = 0; i < headerList.size(); i++) {
     Cell cell = headerRow.createCell(i);
     cell.setCellStyle(styles.get("header"));
     String[] ss = StringUtils.split(headerList.get(i), "**", 2);
     if (ss.length == 2) {
       cell.setCellValue(ss[0]);
       Comment comment =
           this.sheet
               .createDrawingPatriarch()
               .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
       comment.setString(new XSSFRichTextString(ss[1]));
       cell.setCellComment(comment);
     } else {
       cell.setCellValue(headerList.get(i));
     }
     sheet.autoSizeColumn(i);
   }
   for (int i = 0; i < headerList.size(); i++) {
     int colWidth = sheet.getColumnWidth(i) * 2;
     sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
   }
   log.debug("Initialize success.");
 }
Пример #2
3
  private void createSummerySheet() {
    sheet0 = workbook.createSheet("Summary");
    PrintSetup printSetup = sheet0.getPrintSetup();
    printSetup.setLandscape(true);
    sheet0.setFitToPage(true);
    sheet0.setHorizontallyCenter(true);

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

    for (int i = 0; i < titles.length; i++) {
      Row _row = sheet0.createRow(i + 1);
      Cell headerCell = _row.createCell(0);
      headerCell.setCellValue(titles[i]);
      headerCell.setCellStyle(styles.get("header"));
      _row.setHeightInPoints(20);
    }

    // finally set column widths, the width is measured in units of 1/256th
    // of a character width
    sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
  }
  private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
      throws Exception {
    Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
    Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
    nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
    nCell.setCellStyle(tCell.getCellStyle());

    tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
    nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
    nCell.setCellValue(
        tCell
            .getStringCellValue()
            .replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
            .replaceAll(
                PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
            .replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
    nCell.setCellStyle(tCell.getCellStyle());

    Row tempRow = tempSheet.getRow(4);
    Row newRow = newSheet.createRow(4);

    for (int c = 0; c < tempRow.getLastCellNum(); c++) {
      tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
      nCell = newRow.createCell(c, tCell.getCellType());
      nCell.setCellValue(tCell.getStringCellValue());
      nCell.setCellStyle(tCell.getCellStyle());
    }

    for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
      CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
      newSheet.addMergedRegion(mergedRegion);
    }
  }
 /**
  * Forms report in excel file. Sum weight. Sum volume etc.
  *
  * @param sheet Sheet where write the items
  * @param c Container from where take the info
  * @param lastRow last row after all items were written method uses lastRow + 2 to write report in
  *     next 2 rows after all items of the container were written
  */
 private void setReport(Container c, int lastRow) {
   String[] reportHeadings = {
     "Суммарный вес", // 6
     "Суммарный объем", // 7
     "Остаток вес", // 8
     "Остаток объем" // 9
   };
   double[] values = {
     c.getWeight(),
     c.getVolume(),
     c.getWeightLimit() - c.getWeight(),
     c.getVolumeLimit() - c.getVolume()
   };
   int dataCell = 5;
   int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc)
   lastRow++;
   Row headings = outputSheet.createRow(lastRow);
   lastRow += 2;
   Row data = outputSheet.createRow(lastRow);
   for (String report : reportHeadings) {
     Cell heading = headings.createCell(dataCell);
     heading.setCellValue(report);
     Cell dataCellValue = data.createCell(dataCell);
     dataCellValue.setCellValue(values[valuesIndex]);
     dataCell++;
     valuesIndex++;
   }
 }
Пример #5
0
  @Override
  public ModelAndView getReportTemplate() {
    List<MonthlyTraffic> monthlyTrafficReports = monthlyTraffictDAO.getMonthlyTraffic(monthDate);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Monthly Traffic Report");

    int rownum = 0;
    Row row0 = sheet.createRow(rownum++);

    row0.createCell(0).setCellValue("Source Country");
    row0.createCell(1).setCellValue("Service");
    row0.createCell(2).setCellValue("Destination Country");
    row0.createCell(3).setCellValue("Total Call Minutes");

    for (MonthlyTraffic mt : monthlyTrafficReports) {
      Row row = sheet.createRow(rownum++);
      row.createCell(0).setCellValue(mt.getFromCountry());
      row.createCell(1).setCellValue(mt.getServiceName());
      row.createCell(2).setCellValue(mt.getToCountry());
      row.createCell(3).setCellValue(mt.getMinutesOfCalls());
    }

    Map<String, HSSFWorkbook> parameterMap = new HashMap<String, HSSFWorkbook>();
    parameterMap.put("excelBook", workbook);
    ModelAndView modelAndView = new ModelAndView();
    modelAndView.addAllObjects(parameterMap);
    return modelAndView;
  }
Пример #6
0
 @Test
 public void linksTest() throws IOException {
   FileInputStream f =
       new FileInputStream(
           "C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\excelfiles\\datadrivenexcel.xlsx");
   XSSFWorkbook wb = new XSSFWorkbook(f);
   XSSFSheet ws = wb.getSheet("sheet1");
   Iterator<Row> row = ws.iterator();
   row.next();
   while (row.hasNext()) {
     Row r = row.next();
     String linkname = r.getCell(0).getStringCellValue();
     try {
       driver.findElement(By.linkText(linkname)).click();
       String acturl = driver.getCurrentUrl();
       r.createCell(2).setCellValue(acturl);
       String expurl = r.getCell(1).getStringCellValue();
       if (acturl.equals(expurl)) {
         r.createCell(3).setCellValue("passsed");
       } else {
         r.createCell(3).setCellValue("failed");
       }
       driver.navigate().back();
     } catch (Exception e) {
       r.createCell(3).setCellValue("links not found");
     }
   }
   FileOutputStream f1 =
       new FileOutputStream(
           "C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\resultsexcelfiles\\datadrivenexcel.xlsxlinks.xlsx");
   wb.write(f1);
   f1.close();
   driver.quit();
 }
  @Test
  public void dataDrivernTest() throws IOException {
    FileInputStream f =
        new FileInputStream(
            "E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\excelfiles\\registration.xlsx");

    XSSFWorkbook wb = new XSSFWorkbook(f);
    XSSFSheet ws = wb.getSheet("Sheet1");
    Iterator<Row> row = ws.iterator();
    row.next();

    WelcomeMercuryTours wm = PageFactory.initElements(driver, WelcomeMercuryTours.class);
    RegisterMercuryTours1 rm1 = PageFactory.initElements(driver, RegisterMercuryTours1.class);
    RegisterMercuryTours2 rm2 = PageFactory.initElements(driver, RegisterMercuryTours2.class);
    wm.menu.register();
    while (row.hasNext()) {
      Row r = row.next();
      rm1.contactInformation(r);
      boolean result = rm2.validateRegistration(r.getCell(9).getStringCellValue());
      if (result == true) {
        r.createCell(12).setCellValue("Passed");
      } else {
        r.createCell(12).setCellValue("Failed");
      }
      driver.navigate().back();
    }
    FileOutputStream f1 =
        new FileOutputStream(
            "E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\resultexcelfiles\\registration.xlsx");
    wb.write(f1);
    f1.close();
    driver.quit();
  }
Пример #8
0
  /*
   * Don't hardcode US Locale for datetimes in Excel.  Experiment a bit.
   */
  public static void issue26() throws InvalidFormatException, IOException {

    System.out.println("Testing issue26 =================================");
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);

    // first cell
    Cell cell0 = row.createCell(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
    cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
    cell0.setCellStyle(cellStyle);

    // second cell using another format with French locale
    CellStyle cs2 = wb.createCellStyle();
    String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
    System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
    String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
    System.out.println(
        "Converted pattern in FRENCH locale is: "
            + DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));

    DataFormat df = wb.createDataFormat();
    cs2.setDataFormat(df.getFormat(excelFormatPattern));
    Cell cell1 = row.createCell(1);
    cell1.setCellValue(new Date(1385903303326L));
    cell1.setCellStyle(cs2);

    FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
    wb.write(out);
    out.close();
    System.out.println("Wrote /tmp/issue26_out.xlsx");
  }
Пример #9
0
  /**
   * make the excel file based on headers and contents which should have been set
   *
   * @param sheetName specified sheet name
   * @return ExcelMaker itself
   * @throws IllegalArgumentException only support String, Integer, Long, Double, Date, null as cell
   *     data.
   */
  public ExcelMaker make(String sheetName) throws IllegalArgumentException {

    // check if headers and contents set
    if (!contextReady()) {
      throw new IllegalArgumentException("invalid headers or contents");
    }

    Sheet sheet = sheetName == null ? workbook.createSheet() : workbook.createSheet(sheetName);

    // write the display headers as the 1st row in the sheet
    int rowNum = 0;
    Row headerRow = sheet.createRow(rowNum++);
    if (displayHeaders != null) {
      for (int cellNum = 0; cellNum < displayHeaders.size(); cellNum++) {
        String header = displayHeaders.get(cellNum);
        Cell cell = headerRow.createCell(cellNum);
        cell.setCellValue(header);
      }
    } else { // if display headers not set, use headers as default
      for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
        String header = headers.get(cellNum);
        Cell cell = headerRow.createCell(cellNum);
        cell.setCellValue(header);
      }
    }

    // parse each Object in content list and write as a row
    for (Object content : contents) {
      Map<String, Object> headerMap =
          objectMapper.convertValue(content, new TypeReference<HashMap<String, Object>>() {});
      Row row = sheet.createRow(rowNum++);
      // only find properties that declared by headers
      for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
        String header = headers.get(cellNum);
        Cell cell = row.createCell(cellNum);
        Object obj = headerMap.get(header);
        if (obj == null) {
          obj = "";
        }
        if (obj instanceof String) {
          cell.setCellValue((String) obj);
        } else if (obj instanceof Integer) {
          cell.setCellValue((Integer) obj);
        } else if (obj instanceof Long) {
          cell.setCellValue((Long) obj);
        } else if (obj instanceof Double) {
          cell.setCellValue((Double) obj);
        } else if (obj instanceof Date) {
          cell.setCellValue((Date) obj);
        } else {
          throw new IllegalArgumentException("unsupported cell type");
        }
      }
    }
    return this;
  }
Пример #10
0
  public static File writerFile(String[] title, List<String[]> content, String filePath)
      throws IOException {
    checkDir(filePath);

    File f = new File(filePath);

    if (!f.exists()) {
      f.createNewFile();
    }
    FileOutputStream out = new FileOutputStream(f);

    wb = new SXSSFWorkbook();
    setStyle(wb);
    Sheet sheet = wb.createSheet("sheet1");

    Row titleRow = sheet.createRow(0);

    titleRow.setHeightInPoints(20);

    int tCount = title.length;
    for (int i = 0; i < tCount; i++) {
      Cell cell = titleRow.createCell(i);
      cell.setCellStyle(titleStyle);
      cell.setCellType(Cell.CELL_TYPE_STRING);
      cell.setCellValue(title[i]);

      sheet.setColumnWidth(i, 5000);
    }
    int rnum = 1;
    for (String[] c : content) {
      Row r = sheet.createRow(rnum);
      for (int i = 0; i < c.length; i++) {
        Cell cell = r.createCell(i);
        cell.setCellStyle(contentStyle);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        sheet.setColumnWidth(i, 5000);

        String v = c[i];
        if (v == null) {
          v = "";
        }

        cell.setCellValue(v);
      }

      rnum++;
    }

    wb.write(out);
    out.flush();
    wb.close();
    out.close();

    return f;
  }
Пример #11
0
  protected void exportExtra(Row row, Item item) {
    QiyiTop50 rank = (QiyiTop50) item;

    Cell cell = row.createCell(2);
    cell.setCellValue(rank.getActorsString());
    cell = row.createCell(3);
    cell.setCellValue(rank.getLastWeek().longValue());
    cell = row.createCell(4);
    cell.setCellValue(rank.getYesterDay().longValue());
    cell = row.createCell(5);
    cell.setCellValue(rank.getHistory().longValue());
  }
  // =================================================================================================================================
  private int exportFrontEquipListReport_header(
      List<FrontEquipListReport_subtype> subtypes, int rownum, XSSFWorkbook wb, Sheet sheet) {
    CellStyle style = getHeaderStyle(wb, (short) 11);
    CellStyle style2 = getHeaderStyle(wb, (short) 10);

    // 第一行列标题
    Row row_subtype = sheet.createRow(rownum++);
    Cell cell_subtype_0 = row_subtype.createCell(0);
    cell_subtype_0.setCellValue("");
    cell_subtype_0.setCellStyle(style);
    sheet.setColumnWidth(0, 700);
    Cell cell_subtype_1 = row_subtype.createCell(1);
    cell_subtype_1.setCellValue("");
    cell_subtype_1.setCellStyle(style);
    sheet.setColumnWidth(1, 1800);
    Cell cell_subtype_2 = row_subtype.createCell(2);
    cell_subtype_2.setCellValue("");
    cell_subtype_2.setCellStyle(style);
    sheet.setColumnWidth(2, 4800);

    // 第二行列标题
    Row row_prod = sheet.createRow(rownum++);
    Cell cell_prod_0 = row_prod.createCell(0);
    cell_prod_0.setCellValue("序号");
    cell_prod_0.setCellStyle(style2);
    Cell cell_prod_1 = row_prod.createCell(1);
    cell_prod_1.setCellValue("点位编号");
    cell_prod_1.setCellStyle(style2);
    Cell cell_prod_2 = row_prod.createCell(2);
    cell_prod_2.setCellValue("点位名称");
    cell_prod_2.setCellStyle(style2);

    int cell_num = 3;
    for (FrontEquipListReport_subtype subtype : subtypes) {
      int start_cell_num = cell_num;
      Cell cell_subtype = row_subtype.createCell(cell_num);
      cell_subtype.setCellValue(subtype.getSubtype_name());
      cell_subtype.setCellStyle(style);

      for (FrontEquipListReport_prod prod : subtype.getProds()) {
        if (start_cell_num != cell_num) {
          cell_subtype = row_subtype.createCell(cell_num);
          cell_subtype.setCellStyle(style);
        }

        Cell cell_prod = row_prod.createCell(cell_num);
        cell_prod.setCellValue(prod.getProd_name() + "(" + prod.getProd_style() + ")");
        cell_prod.setCellStyle(style2);
        cell_num++;
      }
      // 对子类型进行横向的单元格合并
      sheet.addMergedRegion(
          new CellRangeAddress(1, (short) 1, start_cell_num, (short) cell_num - 1));
    }

    return rownum;
  }
Пример #13
0
  private void writeModule(TrainingModule module) {

    Row row = trainingSheet.createRow(++actualRow);
    row.createCell(COL_MODULE_NUMBER).setCellValue(module.getNumber());
    row.createCell(COL_MODULE).setCellValue(module.getName());

    // for all topics write topics
    Iterator<TrainingTopic> it = module.getTopics().iterator();
    while (it.hasNext()) {
      row = trainingSheet.createRow(++actualRow);
      writeTopic(it.next(), row);
    }
  }
Пример #14
0
 protected void createHeaderExtra(Row row, CellStyle style) {
   Cell cell = row.createCell(2);
   cell.setCellStyle(style);
   cell.setCellValue("Actors");
   cell = row.createCell(3);
   cell.setCellStyle(style);
   cell.setCellValue("LastWeek");
   cell = row.createCell(4);
   cell.setCellStyle(style);
   cell.setCellValue("YesterDay");
   cell = row.createCell(5);
   cell.setCellStyle(style);
   cell.setCellValue("History");
 }
  private Cell headInfo(CellStyle headerStyle, Row row) {
    Cell cell = row.createCell(0);
    cell.setCellStyle(headerStyle);
    cell.setCellValue(getText("title.accountDetail.serialNumber"));

    cell = row.createCell(1);
    cell.setCellStyle(headerStyle);
    cell.setCellValue(getText("page.accountDetail.accountNum"));

    cell = row.createCell(STR_2);
    cell.setCellStyle(headerStyle);
    cell.setCellValue(getText("page.accountDetail.accountName"));

    cell = row.createCell(STR_3);
    cell.setCellStyle(headerStyle);
    cell.setCellValue(getText("title.accountDetail.tranDate"));

    cell = row.createCell(STR_4);
    cell.setCellStyle(headerStyle);
    cell.setCellValue(getText("title.accountDetail.currency"));
    cell = row.createCell(STR_5);
    cell.setCellStyle(headerStyle);
    cell.setCellValue(getText("title.accountDetail.expenditure"));
    cell = row.createCell(STR_6);
    cell.setCellStyle(headerStyle);
    cell.setCellValue(getText("title.accountDetail.income"));
    cell = row.createCell(STR_7);
    cell.setCellStyle(headerStyle);
    cell.setCellValue(getText("title.accountDetail.balance"));
    return cell;
  }
  private void fillRowWith(Row r, String[] data) {
    Cell c = null;

    for (String s : data) {
      if (c == null) c = r.createCell(0);
      else c = r.createCell(c.getColumnIndex() + 1);

      try {
        double d = Double.valueOf(s);
        c.setCellValue(d);
      } catch (NumberFormatException e) {
        c.setCellValue(s);
      }
    }
  }
Пример #17
0
 @Override
 public void writeRow(List<?> row) {
   Row record = sheet.createRow(sheet.getLastRowNum() + 1);
   for (int i = 0; i < row.size(); i++) {
     Cell cell = record.createCell(i);
     Object value = row.get(i);
     if (value == null) {
       cell.setCellValue("");
     } else if (value instanceof String) {
       cell.setCellType(Cell.CELL_TYPE_STRING);
       cell.setCellValue((String) value);
     } else if (value instanceof Number) {
       cell.setCellType(Cell.CELL_TYPE_NUMERIC);
       cell.setCellValue(converters.convert(value, Double.class));
     } else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
       cell.setCellType(Cell.CELL_TYPE_NUMERIC);
       cell.setCellStyle(dateCellStyle);
       cell.setCellValue(converters.convert(value, Date.class));
     } else if (value instanceof Boolean) {
       cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
       cell.setCellValue((Boolean) value);
     } else {
       cell.setCellType(Cell.CELL_TYPE_STRING);
       cell.setCellValue(converters.convert(value, String.class));
     }
   }
 }
Пример #18
0
 private static void createRow(
     Sheet sheet,
     Map<String, Object> rowData,
     List<String> mapping,
     List<CellStyle> styles,
     int startRowNum) {
   Row row = sheet.createRow(startRowNum);
   for (int i = 0; i < mapping.size(); i++) {
     String name = mapping.get(i);
     Object obj = rowData.get(name);
     Cell newCell = row.createCell(i);
     CellStyle style = styles.get(i);
     newCell.setCellStyle(style);
     if (obj != null) {
       if (obj instanceof Date) {
         newCell.setCellValue((Date) obj);
       } else if (obj instanceof BigDecimal) {
         double dd = ((BigDecimal) obj).doubleValue();
         newCell.setCellValue(dd);
       } else {
         newCell.setCellValue(obj.toString());
       }
     }
   }
 }
Пример #19
0
  public void format() {

    wb = new XSSFWorkbook();

    Map styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    sheet.setColumnWidth(0, 768);
    sheet.setColumnWidth(1, 768);
    sheet.setColumnWidth(2, 2816);
    sheet.setColumnWidth(3, 3584);
    sheet.setColumnWidth(4, 3584);
    sheet.setColumnWidth(5, 3584);
    sheet.setColumnWidth(6, 3584);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35F);
    for (int i = 1; i <= 7; i++)
      titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));

    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple");
  }
Пример #20
0
  public void insertDataToExcel(int numRow, Object[] object) {

    try {

      if (null != wb.getSheetAt(0)) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.getRow((short) numRow);

        if (row == null) row = aSheet.createRow((short) numRow);

        for (int i = 0; i < object.length; i++) {
          Cell csCell = row.createCell((short) i);

          CellStyle style = wb.createCellStyle();
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          style.setRightBorderColor(HSSFColor.BLACK.index);
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          style.setTopBorderColor(HSSFColor.BLACK.index);

          csCell.setCellStyle(style);

          if (object[i] != null) csCell.setCellValue(object[i].toString());
          else csCell.setCellValue("0");
        }
      }

    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
  private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("sheet1");
    CellStyle headerStyle = getHeaderStyle(wb);
    CellStyle firstCellStyle = getFirsetCellStyle(wb);
    CellStyle commonCellStyle = getCommonCellStyle(wb);
    CellStyle amtCellStyle = getAmtCellStyle(wb);

    for (int i = 0; i < LENGTH_9; i++) {
      sheet1.setColumnWidth(i, STR_15 * STR_256);
    }

    // 表头
    Row row = sheet1.createRow(0);
    row.setHeightInPoints(STR_20);

    Cell cell = headInfo(headerStyle, row);

    if (detailList.size() == 0) {
      row = sheet1.createRow(1);
      cell = row.createCell(0);
      cell.setCellValue(NO_RECORD);
    } else {
      fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
    }
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    wb.write(outputStream);
    InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
    outputStream.close();
    return inputStream;
  }
Пример #22
0
 public static void copySheet(Sheet sheet, Sheet newSheet) {
   int maxCol = 0;
   for (int row = 0; row <= sheet.getLastRowNum(); row++) {
     Row oldRow = sheet.getRow(row);
     if (oldRow == null) continue;
     Row newRow = newSheet.getRow(row);
     if (newRow == null) newRow = newSheet.createRow(row);
     if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
     maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
     for (int col = 0; col < oldRow.getLastCellNum(); col++) {
       Cell oldCell = oldRow.getCell(col);
       if (oldCell == null) continue;
       Cell newCell = newRow.getCell(col);
       if (newCell == null) newCell = newRow.createCell(col);
       copyCell(oldCell, newCell, true);
     }
   }
   for (int col = 0; col <= maxCol; col++) {
     if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
   }
   for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
     CellRangeAddress cra = sheet.getMergedRegion(i);
     newSheet.addMergedRegion(cra);
   }
 }
Пример #23
0
  private void llenarCeldas(Row row, Object[] datos) {
    int cellnum = 0;

    // Columnas para datos de los partidos
    for (int i = 0; i < datos.length; i++) {

      Cell cell = row.createCell(cellnum++);

      if (datos[i] == null) {
        cell.setCellValue("");
      } else if (datos[i] instanceof Date) {
        cell.setCellValue((Date) datos[i]);
      } else if (datos[i] instanceof Boolean) {
        cell.setCellValue((Boolean) datos[i]);
      } else if (datos[i] instanceof String) {
        cell.setCellValue((String) datos[i]);
      } else if (datos[i] instanceof Character) {
        cell.setCellValue(String.valueOf(datos[i]));
      } else if (datos[i] instanceof Double) {
        cell.setCellValue((Double) datos[i]);
      } else if (datos[i] instanceof Integer) {
        cell.setCellValue((Integer) datos[i]);
      }
    }
  }
Пример #24
0
 /**
  * write excel sheet, specified value to specified cell.
  *
  * @param sheetName excel sheet name
  * @param row row index which to be changed
  * @param col column index which to be changed
  * @param value value to be put into cell
  * @throws RuntimeException
  */
 public void setExcelValue(String sheetName, int row, int col, String value) {
   Workbook workBook = null;
   try {
     if (new File(fileName).exists()) {
       workBook = getWorkBook(new FileInputStream(fileName), false);
     } else {
       workBook = getWorkBook(null, false);
     }
     xlSheet = workBook.getSheet(sheetName);
     if (xlSheet == null) {
       xlSheet = workBook.createSheet(sheetName);
     }
     xlRow = xlSheet.getRow(row - 1);
     if (xlRow == null) {
       xlRow = xlSheet.createRow((short) row - 1);
     }
     xlCell = xlRow.getCell(col - 1);
     if (xlCell == null) {
       xlCell = xlRow.createCell(col - 1);
     }
     xlCell.setCellType(1); // set cell type as string
     xlCell.setCellValue(value);
     FileOutputStream fileOut = new FileOutputStream(fileName);
     workBook.write(fileOut);
     fileOut.flush();
     fileOut.close();
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException("set excel value failed:" + e.getMessage());
   }
 }
Пример #25
0
 public void writeData(Data data, boolean onlyNumbers) {
   try {
     File file = new File(path);
     if (!file.exists()) {
       file.createNewFile();
     }
     FileOutputStream fOutputStream = new FileOutputStream(file);
     org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
     org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
     @SuppressWarnings("rawtypes")
     ArrayList<ArrayList> dataList = data.getData();
     for (int i = 0; i < dataList.size(); i++) {
       Row row = sheet.createRow(i);
       for (int j = 0; j < dataList.get(i).size(); j++) {
         row.createCell(j);
         if (onlyNumbers) {
           row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
         } else {
           row.getCell(j).setCellValue((String) dataList.get(i).get(j));
         }
       }
     }
     workbook.write(fOutputStream);
     workbook.close();
     fOutputStream.close();
     workbook = null;
     file = null;
     sheet = null;
   } catch (EncryptedDocumentException | IOException e) {
     e.printStackTrace();
   }
 }
Пример #26
0
  public void fillData(List<Mobile> list, XSSFWorkbook workbook, XSSFSheet sheet) {
    int j = 0;
    int rowIndex = 2;
    int stt = 1;
    //        XSSFCellStyle style1 = workbook.createCellStyle();
    //        style1.setFillForegroundColor(HSSFColor.AQUA.index);
    //        style1.setFillPattern(CellStyle.SOLID_FOREGROUND);

    //        XSSFCreationHelper createHelper = workbook.getCreationHelper();
    //        XSSFCellStyle style2 = workbook.createCellStyle();
    //        style2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));

    ExcelUtil ex = new ExcelUtil(workbook);
    for (int i = 0; i < list.size(); i++) {
      Row row = sheet.createRow(rowIndex++);
      j = 0;
      row.createCell(j++).setCellValue(stt++);
      row.createCell(j++).setCellValue(list.get(i).getWeb());
      row.createCell(j++).setCellValue(list.get(i).getType());
      row.createCell(j++).setCellValue(list.get(i).getBrand());
      if (list.get(i).getPriceNumber() != null) {
        ex.formatNumber(row, j++, list.get(i).getPriceNumber(), ex.number);
      } else {
        j++;
      }
      row.createCell(j++).setCellValue(list.get(i).getItemCode());
      row.createCell(j++).setCellValue(list.get(i).getName());
      row.createCell(j++).setCellValue(list.get(i).getModel());
      row.createCell(j++).setCellValue(list.get(i).getStorage());
      row.createCell(j++).setCellValue(list.get(i).getRam());
      row.createCell(j++).setCellValue(list.get(i).getScreen());
      row.createCell(j++).setCellValue(list.get(i).getCpu());
      row.createCell(j++).setCellValue(list.get(i).getBackCamera());
      row.createCell(j++).setCellValue(list.get(i).getFrontCamera());
      row.createCell(j++).setCellValue(list.get(i).getOs());
      row.createCell(j++).setCellValue(list.get(i).getBattery());
      row.createCell(j++).setCellValue(list.get(i).getSim());
      row.createCell(j++).setCellValue(list.get(i).getColor());
      row.createCell(j++).setCellValue(list.get(i).getPromotion());
      //            Cell cell = row.createCell(j++);
      //            cell.setCellValue(list.get(i).getLastUpdate());
      //            cell.setCellStyle(style2);
      ex.formatDate(row, j++, list.get(i).getLastUpdate(), ex.date);
      row.createCell(j++).setCellValue(list.get(i).getLink());
    }
  }
Пример #27
0
 public static Cell createCell(Row row, int index, Boolean value) {
   Cell cell = row.createCell(index);
   if (value == null) {
     cell.setCellValue("");
   }
   cell.setCellValue(value);
   return cell;
 }
  public void generateReport() throws IOException {
    System.out.println("Test Report Gen");
    Sheet sheet = workbook.getSheet("sheet1");

    try {
      String strSql = "Select * from participant;";
      Sql db = new Sql();
      ResultSet rs = db.executeQuery(strSql);
      ResultSetMetaData rsMeta = rs.getMetaData();

      Row row = sheet.createRow(0);
      Cell cell;
      // get table columns which will act as title columns
      for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
        String colName = rsMeta.getColumnName(i);
        int dataType = rsMeta.getColumnType(i);
        cell = row.createCell(i - 1);
        cell.setCellValue(colName);
      }

      int i = 1;
      while (rs.next()) {
        // create row to write data to
        row = sheet.createRow(i);
        // create cells for writing data to
        for (int c = 1; c <= rsMeta.getColumnCount(); c++) {
          cell = row.createCell(c - 1);
          String colName = rsMeta.getColumnName(c);
          cell.setCellValue(rs.getString(colName));
        }

        i++;
      }

      JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated");
    } catch (SQLException ex) {
      ex.printStackTrace();
      // log Error
      FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
    }

    /// Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("reportTest.xls");
    workbook.write(fileOut);
    fileOut.close();
  }
Пример #29
0
 public static Cell createCell(Row row, int index, RichTextString value) {
   Cell cell = row.createCell(index);
   if (value == null) {
     cell.setCellValue("");
     return cell;
   }
   cell.setCellValue(value);
   return cell;
 }
Пример #30
0
 public static Cell createCell(Row row, int index, Date value) {
   Cell cell = row.createCell(index);
   if (value == null) {
     cell.setCellValue("");
     return cell;
   }
   cell.setCellValue(DateUtils.formatDate(value, DateUtils.DateFormatType.DATE_FORMAT_STR_CHINA));
   return cell;
 }