Exemple #1
0
  // returns true if data is set successfully else false
  public boolean setCellData(
      String sheetName, String colName, int rowNum, String data, String url) {
    // System.out.println("setCellData setCellData******************");
    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);

      if (rowNum <= 0) return false;

      int index = workBook.getSheetIndex(sheetName);
      int colNum = -1;
      if (index == -1) return false;

      sheet = workBook.getSheetAt(index);
      // System.out.println("A");
      row = sheet.getRow(0);
      for (int i = 0; i < row.getLastCellNum(); i++) {
        // System.out.println(row.getCell(i).getStringCellValue().trim());
        if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i;
      }

      if (colNum == -1) return false;
      sheet.autoSizeColumn(colNum); // ashish
      row = sheet.getRow(rowNum - 1);
      if (row == null) row = sheet.createRow(rowNum - 1);

      cell = row.getCell(colNum);
      if (cell == null) cell = row.createCell(colNum);

      cell.setCellValue(data);
      XSSFCreationHelper createHelper = workBook.getCreationHelper();

      // cell style for hyperlinks
      // by default hypelrinks are blue and underlined
      CellStyle hlink_style = workBook.createCellStyle();
      XSSFFont hlink_font = workBook.createFont();
      hlink_font.setUnderline(XSSFFont.U_SINGLE);
      hlink_font.setColor(IndexedColors.BLUE.getIndex());
      hlink_style.setFont(hlink_font);
      // hlink_style.setWrapText(true);

      XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
      link.setAddress(url);
      cell.setHyperlink(link);
      cell.setCellStyle(hlink_style);

      fos = new FileOutputStream(path);
      workBook.write(fos);

      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
Exemple #2
0
  public boolean setCellData(String sheetName, String colName, int rowNum, String data) {
    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);

      if (rowNum <= 0) return false;

      int index = workBook.getSheetIndex(sheetName);
      int colNum = -1;
      if (index == -1) return false;

      sheet = workBook.getSheetAt(index);

      row = sheet.getRow(0);
      for (int i = 0; i < row.getLastCellNum(); i++) {
        // System.out.println(row.getCell(i).getStringCellValue().trim());
        if (row.getCell(i).getStringCellValue().trim().equals(colName)) colNum = i;
      }
      if (colNum == -1) return false;

      sheet.autoSizeColumn(colNum);
      row = sheet.getRow(rowNum - 1);
      if (row == null) row = sheet.createRow(rowNum - 1);

      cell = row.getCell(colNum);
      if (cell == null) cell = row.createCell(colNum);

      // cell style
      // CellStyle cs = workbook.createCellStyle();
      // cs.setWrapText(true);
      // cell.setCellStyle(cs);
      cell.setCellValue(data);

      fos = new FileOutputStream(path);

      workBook.write(fos);

      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
  private void generateExcelDoc(String docName) throws FileNotFoundException, IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet overall = workbook.createSheet("Overall");
    XSSFRow row = overall.createRow(0);

    XSSFCellStyle topStyle = workbook.createCellStyle();
    topStyle.setAlignment(CellStyle.ALIGN_CENTER);

    XSSFCell theme = row.createCell(0);
    theme.setCellValue("Theme");
    overall.autoSizeColumn(0);

    XSSFCell occurs = row.createCell(1);
    occurs.setCellValue("Occurrences");
    overall.autoSizeColumn(1);

    XSSFCell prev = row.createCell(2);
    prev.setCellValue("Prevalence");
    overall.autoSizeColumn(2);

    theme.setCellStyle(topStyle);
    occurs.setCellStyle(topStyle);
    prev.setCellStyle(topStyle);

    for (int i = 0; i < themes.size(); i++) {
      XSSFRow r = overall.createRow((i + 1));

      XSSFCell c = r.createCell(0);
      c.setCellValue(themes.get(i).getName());

      XSSFCell c1 = r.createCell(1);
      c1.setCellValue(themes.get(i).getTotalOccurs());

      XSSFCell c2 = r.createCell(2);
      c2.setCellValue(calculatePrevalence(themes.get(i).getTotalOccurs(), lineCount));
    }

    // This could be done in the previous loop but since we don't need
    // indices as much, we may as well use the cleaner for each loop

    for (Theme t : themes) {
      XSSFSheet themeSheet = workbook.createSheet(t.getName());
      XSSFRow row1 = themeSheet.createRow(0);

      XSSFCell keyword = row1.createCell(0);
      keyword.setCellValue("Keyword");
      keyword.setCellStyle(topStyle);

      XSSFCell occ = row1.createCell(1);
      occ.setCellValue("Occurrences");
      occ.setCellStyle(topStyle);

      XSSFCell themePrev = row1.createCell(2);
      themePrev.setCellValue("Prevalence");
      themePrev.setCellStyle(topStyle);

      for (int i = 0; i < t.getKeywords().size(); i++) {
        Keyword k = t.getKeywords().get(i);
        XSSFRow r = themeSheet.createRow((i + 1));

        XSSFCell c = r.createCell(0);
        c.setCellValue(k.getName());

        XSSFCell c1 = r.createCell(1);
        c1.setCellValue(k.getNumOccurs());

        XSSFCell c2 = r.createCell(2);
        c2.setCellValue(calculatePrevalence(k.getNumOccurs(), t.getTotalOccurs()));
      }
    }

    FileOutputStream output = new FileOutputStream(docName);
    workbook.write(output);
    output.close();
  }
 @Override
 public Workbook writeWorkbook(
     Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) {
   if (workbook == null) workbook = new XSSFWorkbook();
   int exportRecordNum = tableDataList.size(); // 导出的总记录数
   int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数
   int sheetNum =
       exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum)
           + 1; // 工作表的页数
   List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>();
   if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet";
   for (int i = 0; i < sheetNum; i++) {
     // 在Excel工作簿中建一工作表
     String sTitle = sheetTitle + (i + 1);
     XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle);
     sheet.setSelected(true); // 设置工作薄为选中
     sheet.setAutobreaks(true);
     sheet.setPrintGridlines(true);
     sheetList.add(sheet);
   }
   /** ***********************输出表头********************************* */
   if (headerNum > 0) {
     for (XSSFSheet sheet : sheetList) {
       XSSFRow headRow = sheet.createRow(0);
       headRow.setHeightInPoints(20);
       for (int i = 0; i < headerNum; i++) {
         List<String> headerRowDataList = tableDataList.get(i);
         for (int j = 0; j < headerRowDataList.size(); j++) {
           XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式
           createCell(headRow, j, headerRowDataList.get(j), cellStyle);
         }
       }
       // 固定表头
       sheet.createFreezePane(0, 1);
     }
   }
   /** *********************输出表体内容************************* */
   // 设置列样式
   XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle();
   columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index);
   columnStyle.setWrapText(true);
   if (tableDataList.size() > (tableStartRowNum + 1)) {
     for (int i = tableStartRowNum; i < exportRecordNum; i++) {
       List<String> rowDataList = tableDataList.get(i);
       XSSFRow row = null;
       int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数
       XSSFSheet sheet = sheetList.get(currentSheet);
       int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet;
       row = sheet.createRow(rowIndex);
       for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) {
         createCell(row, colIndex, rowDataList.get(colIndex));
       }
     }
   }
   // 调整列的宽度(取第一列为基准)
   for (XSSFSheet sheet : sheetList) {
     for (int i = 0; i < tableDataList.get(0).size(); i++) {
       sheet.autoSizeColumn((short) i);
       sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000));
     }
   }
   return workbook;
 }
Exemple #5
0
 @Override
 public byte[] getImportTemplate(Boolean editProductType) throws IOException {
   XSSFWorkbook workBook = new XSSFWorkbook();
   XSSFSheet productSheet = workBook.createSheet(PRODUCT_SHEETNAME);
   XSSFSheet typeSheet = createTypeSheet(workBook);
   if (!(true == editProductType)) {
     workBook.setSheetHidden(
         workBook.getSheetIndex(typeSheet), XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);
   }
   XSSFRow titleRow = productSheet.createRow(0);
   List<TransformSetting> transSettings = new TransformSettingFactory().getProductSetting();
   XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
   for (int i = 0; i < transSettings.size(); i++) {
     TransformSetting setting = transSettings.get(i);
     XSSFCell titleCell = null;
     titleCell = titleRow.createCell(i, Cell.CELL_TYPE_STRING);
     String cellValue = setting.getDispName();
     titleCell.setCellValue(cellValue);
     productSheet.autoSizeColumn(i, true);
     int columnWidth = productSheet.getColumnWidth(i);
     int needWidth = cellValue.getBytes().length * 256;
     if (columnWidth < needWidth) {
       productSheet.setColumnWidth(i, needWidth);
     }
     if ("productTypeName".equals(setting.getPropertyName())) {
       Name type = workBook.createName();
       String name = "type";
       type.setNameName(name); // 定义名称
       type.setRefersToFormula(
           "OFFSET("
               + typeSheet.getSheetName()
               + "!$A$3,0,0,COUNTA("
               + typeSheet.getSheetName()
               + "!$A:$A)-2)");
       XSSFDataValidationConstraint constraint =
           (XSSFDataValidationConstraint) helper.createFormulaListConstraint(name); // 使用定义的名称
       CellRangeAddressList regions = new CellRangeAddressList(1, 10, i, i);
       DataValidation dataValidation = helper.createValidation(constraint, regions);
       productSheet.addValidationData(dataValidation);
     } else if ("productTypeItem".equals(setting.getPropertyName())) { // item是通过查询前面的名称来自动取得
       for (int j = 0; j < 10; j++) {
         XSSFRow row = productSheet.createRow(j + 1);
         XSSFCell itemCell = row.createCell(i, Cell.CELL_TYPE_STRING);
         int cellnum = j + 2;
         itemCell.setCellFormula(
             "IF(A"
                 + cellnum
                 + "=\"\",\"\",VLOOKUP(A"
                 + cellnum
                 + ",OFFSET(productType!$A$3,,,COUNTA(productType!$A:$A),2),2,0))");
       }
     } else if ("costMethod".equals(setting.getPropertyName())) {
       int len = CostMethod.values().length;
       String[] costNames = new String[len];
       for (int ci = 0; ci < len; ci++) {
         costNames[ci] = CostMethod.values()[ci].getMethodName();
       }
       //				XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
       XSSFDataValidationConstraint constraint =
           (XSSFDataValidationConstraint) helper.createExplicitListConstraint(costNames);
       CellRangeAddressList addressList = new CellRangeAddressList(1, 10, i, i);
       XSSFDataValidation validation =
           (XSSFDataValidation) helper.createValidation(constraint, addressList);
       validation.setShowErrorBox(true);
       productSheet.addValidationData(validation);
     }
   }
   workBook.setActiveSheet(workBook.getSheetIndex(productSheet));
   ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
   workBook.write(baos);
   return baos.toByteArray();
 }