private void printPreferencias() {
   int rowNum;
   XSSFSheet sheet = report.createSheet("Preferencias");
   rowNum = 0;
   for (Preferencia p : AlumnosCursosModel.getInstance().getPreferencias()) {
     XSSFRow row = sheet.createRow(rowNum++);
     int cellNum = 0;
     if (rowNum == 1) {
       XSSFCell cell = row.createCell(cellNum++);
       cell.setCellValue("Id 1");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Id 2");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Satisfecha?");
       cell = row.createCell(cellNum++);
       cellNum = 0;
       row = sheet.createRow(rowNum++);
     }
     XSSFCell cell = row.createCell(cellNum++);
     cell.setCellValue(p.getAlumno1().getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(p.getAlumno2().getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(p.getIsSatisfecha().toString());
     cell = row.createCell(cellNum++);
   }
 }
Beispiel #2
0
 private XSSFSheet createTypeSheet(XSSFWorkbook workBook) {
   XSSFSheet typeSheet = workBook.createSheet(PRODUCTTYPE_SHEETNAME);
   List<ProductType> allTypes = productTypeDao.getAll();
   Collections.sort(allTypes, new LftRgtTreeNodeComparator()); // 按左值排序。
   List<AdjacencyNode<ProductType>> adjacencyNodes =
       new LftRgtTreeMenuTool<ProductType>().toAdjacencyNode(allTypes);
   int rownum = 0;
   XSSFRow typeTitleRow = typeSheet.createRow(rownum++);
   typeTitleRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue("类别名称");
   typeTitleRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("类别编号");
   typeTitleRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("父类编号");
   for (AdjacencyNode<ProductType> productType : adjacencyNodes) {
     XSSFRow row = typeSheet.createRow(rownum);
     XSSFCell typeCell = row.createCell(0, Cell.CELL_TYPE_STRING);
     typeCell.setCellValue(productType.getNode().getName());
     XSSFCell itemCell = row.createCell(1, Cell.CELL_TYPE_STRING);
     itemCell.setCellValue(productType.getNode().getItem());
     if (productType.getParent() != null) {
       XSSFCell parentItemCell = row.createCell(2, Cell.CELL_TYPE_STRING);
       parentItemCell.setCellValue(productType.getParent().getItem());
     }
     rownum++;
   }
   return typeSheet;
 }
 private void printCurso(Curso c) {
   int rowNum;
   XSSFSheet sheet =
       report.createSheet("Curso " + c.getNombre() + (c.getIsMixto() ? "(Mixto)" : "(No Mixto)"));
   rowNum = 0;
   for (Alumno a : c.getAlumnos()) {
     XSSFRow row = sheet.createRow(rowNum++);
     int cellNum = 0;
     if (rowNum == 1) {
       XSSFCell cell = row.createCell(cellNum++);
       cell.setCellValue("Id");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Apellido");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Nombre");
       cell = row.createCell(cellNum++);
       cellNum = 0;
       row = sheet.createRow(rowNum++);
     }
     XSSFCell cell = row.createCell(cellNum++);
     cell.setCellValue(a.getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(a.getApellido());
     cell = row.createCell(cellNum++);
     cell.setCellValue(a.getNombres());
     cell = row.createCell(cellNum++);
   }
 }
  public static void main(String[] args) throws Exception {

    XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow((short) 2);

    XSSFCell cell = row.createCell(1);
    XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");

    XSSFFont font1 = wb.createFont();
    font1.setBold(true);
    font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
    rt.applyFont(0, 10, font1);

    XSSFFont font2 = wb.createFont();
    font2.setItalic(true);
    font2.setUnderline(XSSFFont.U_DOUBLE);
    font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
    rt.applyFont(10, 19, font2);

    XSSFFont font3 = wb.createFont();
    font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
    rt.append(" Jumped over the lazy dog", font3);

    cell.setCellValue(rt);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
    wb.write(fileOut);
    fileOut.close();
  }
Beispiel #5
0
 /**
  * 指定した行番号の<b>XSSFRow</b>を取得する。有効範囲外の行の場合は新規作成する。
  *
  * @param sheet <b>XSSFSheet</b>
  * @param nRow 取得したい行の行番号
  * @return <b>XSSFRow</b>
  */
 public static XSSFRow getRowAnyway(XSSFSheet sheet, int nRow) {
   assert sheet != null;
   XSSFRow row = sheet.getRow(nRow);
   if (row == null) {
     row = sheet.createRow(nRow);
   }
   return row;
 }
Beispiel #6
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;
  }
Beispiel #7
0
 public void generate4Dispatches(
     List<String> sheetNames, List<String[]> headersList, List<List<String[]>> contents)
     throws IOException {
   for (int i = 0; i < sheetNames.size(); i++) {
     // Create a blank sheet
     XSSFSheet spreadsheet = workbook.createSheet(sheetNames.get(i));
     // Create row object
     XSSFRow row;
     // This data needs to be written (Object[])
     Map<Integer, String[]> contentMap = new TreeMap<>();
     contentMap.put(1, headersList.get(i));
     for (int j = 0; j < contents.get(i).size(); j++) {
       contentMap.put(j + 2, contents.get(i).get(j));
     }
     // Iterate over data and write to sheet
     Set<Integer> keyid = contentMap.keySet();
     int rowid = 0;
     for (Integer key : keyid) {
       row = spreadsheet.createRow(rowid++);
       String[] objectArr = contentMap.get(key);
       int cellid = 0;
       for (String obj : objectArr) {
         if (obj.contains("\r\n")) {
           String[] strArray = obj.split("\r\n");
           for (String str : strArray) {
             Cell cell = row.createCell(cellid);
             cell.setCellValue(str);
             row = spreadsheet.createRow(rowid++);
           }
         } else {
           Cell cell = row.createCell(cellid++);
           cell.setCellValue(obj);
         }
       }
     }
     //            for (int c = 0; c < headersList.get(i).length; c++) {
     //                spreadsheet.autoSizeColumn(c);
     //            }
     // Write the workbook in file system
     FileOutputStream out = new FileOutputStream(new File(fileName));
     workbook.write(out);
     out.close();
   }
 }
Beispiel #8
0
  public <S, L> void rulewriter(Map<S, L> map) throws IOException {
    FileOutputStream fos = new FileOutputStream(file);

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet xsheet = workbook.createSheet();
    int count = 0;
    XSSFRow row = xsheet.createRow(count++);
    row.createCell(0).setCellValue("Error ID");
    row.createCell(1).setCellValue("Rule ID");

    for (Entry<S, L> entry : map.entrySet()) {
      row = xsheet.createRow(count);
      row.createCell(0).setCellValue((String) entry.getKey());
      row.createCell(1).setCellValue(entry.getValue().toString());

      count++;
    }
    workbook.write(fos);
    fos.close();
  }
Beispiel #9
0
 public static void setCellStyleX(int row, int col, XSSFSheet sheet, XSSFCellStyle style) {
   XSSFRow r = sheet.getRow(row);
   if (null == r) {
     r = sheet.createRow(row);
   }
   XSSFCell cell = r.getCell(col);
   if (null == cell) {
     cell = sheet.getRow(row).createCell(col);
   }
   cell.setCellStyle(style);
 }
Beispiel #10
0
 public static void setCellsX(int row, int col, Object colValue, XSSFFont font, XSSFSheet sheet) {
   XSSFRow r = sheet.getRow(row);
   if (null == r) {
     r = sheet.createRow(row);
   }
   XSSFCell cell = r.getCell(col);
   if (null == cell) {
     cell = sheet.getRow(row).createCell(col);
   }
   setCellValueOfCnX(cell, colValue);
   cell.getCellStyle().setFont(font);
 }
 public static void addErrorDescriptionRow(int rowNum, String field, String errorDescription) {
   try {
     XSSFRow destRow = sheetWrite2.createRow(rowCount);
     XSSFCell cell = destRow.createCell(0);
     cell.setCellValue(Integer.toString(rowNum));
     cell = destRow.createCell(1);
     cell.setCellValue(field);
     cell = destRow.createCell(2);
     cell.setCellValue(errorDescription);
   } catch (Exception ex) {
     System.out.println(
         "Error in writing descriptionRow in reject excel file :: " + ex.getMessage());
     ex.printStackTrace();
   }
 }
  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());
    }
  }
Beispiel #13
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 XSSFRow createRowFrom(IRecordable o) {
    XSSFRow lastRow, r;
    String[] data;

    if (dicoLocations.containsKey(o.getClassement()))
      lastRow = dicoLocations.get(o.getClassement());
    else {
      XSSFSheet logSheet = wb.createSheet(o.getClassement());
      lastRow = logSheet.createRow(0);
      data = o.getTitles();
      fillRowWith(lastRow, data);
      dicoLocations.put(o.getClassement(), lastRow);
    }
    r = lastRow.getSheet().createRow(lastRow.getRowNum() + 1);
    data = o.getRecords();
    fillRowWith(r, data);
    dicoLocations.replace(o.getClassement(), r);
    return r;
  }
  /**
   * @param newSheet the sheet to create from the copy.
   * @param sheet the sheet to copy.
   * @param copyStyle true copy the style.
   */
  public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap =
        (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
      XSSFRow srcRow = sheet.getRow(i);
      XSSFRow destRow = newSheet.createRow(i);
      if (srcRow != null) {
        XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
        if (srcRow.getLastCellNum() > maxColumnNum) {
          maxColumnNum = srcRow.getLastCellNum();
        }
      }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
      newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
  }
 public static void addErrorRow(XSSFRow srcRow) {
   try {
     XSSFRow destRow = sheetWrite.createRow(rowCount);
     // XSSFRow destRow =null;
     for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
       XSSFCell oldCell = srcRow.getCell(j); // ancienne cell
       XSSFCell newCell = destRow.getCell(j); // new cell
       if (oldCell != null) {
         if (newCell == null) {
           newCell = destRow.createCell(j);
         }
         copyCell(oldCell, newCell);
       }
     }
   } catch (Exception ex) {
     System.out.println("Error in writing Row in reject excel file :: " + ex.getMessage());
     ex.printStackTrace();
   }
 }
Beispiel #17
0
  public static void main(String[] args) throws IOException {

    File file = new File("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
    FileInputStream fis = new FileInputStream(file);

    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = sheet.getRow(0);
    XSSFCell cell = row.getCell(0);
    System.out.println(cell);

    XSSFSheet write = workbook.createSheet();
    XSSFRow rowwrite = write.createRow(0);
    XSSFCell cell1 = rowwrite.createCell(1);
    cell1.setCellValue("Yamini");
    FileOutputStream file1 =
        new FileOutputStream("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
    workbook.write(file1);
    file1.close();
  }
Beispiel #18
0
  // returns true if column is created successfully
  public boolean addColumn(String sheetName, String colName) {
    // System.out.println("**************addColumn*********************");

    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);
      int index = workBook.getSheetIndex(sheetName);
      if (index == -1) return false;

      XSSFCellStyle style = workBook.createCellStyle();
      style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

      sheet = workBook.getSheetAt(index);

      row = sheet.getRow(0);
      if (row == null) row = sheet.createRow(0);

      // cell = row.getCell();
      // if (cell == null)
      // System.out.println(row.getLastCellNum());
      if (row.getLastCellNum() == -1) cell = row.createCell(0);
      else cell = row.createCell(row.getLastCellNum());

      cell.setCellValue(colName);
      cell.setCellStyle(style);

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

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }

    return true;
  }
Beispiel #19
0
 public static void WriteCol(String QueryType, String num) throws IOException {
   Map<String, Object[]> data = new HashMap<String, Object[]>();
   List<String> sqlDML = new ArrayList<String>();
   File myfile = new File("C://Users/Administrator/Documents/Heckathon/Result.xlsx");
   FileInputStream fis = new FileInputStream(myfile);
   XSSFWorkbook myworkbook = new XSSFWorkbook(fis);
   XSSFSheet mysheet = myworkbook.getSheetAt(2);
   data.put(num, new Object[] {QueryType, num});
   Set<String> newRows = data.keySet();
   int rownum = mysheet.getPhysicalNumberOfRows();
   for (String Key : newRows) {
     Row row = mysheet.createRow(rownum++);
     Object[] objArr = data.get(Key);
     int cellnum = 0;
     for (Object obj : objArr) {
       Cell cell = row.createCell(cellnum++);
       cell.setCellValue((String) obj);
     }
   }
   FileOutputStream os = new FileOutputStream(myfile);
   myworkbook.write(os);
   System.out.println("Record Entered");
 }
  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();
  }
Beispiel #21
0
  public static boolean writeArray2XLSXFile(
      String FileName, String[] titleNames, List<String[]> arry) throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet("Data");
    int columnNum = titleNames.length;
    int rowNum = arry.size();

    // for (int i = 0; i < columnNum; i++) {
    //  sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
    // }
    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom headFont to the text in the comment
    XSSFFont headFont = wb.createFont();
    headFont.setFontName("Courier New");
    headFont.setFontHeightInPoints((short) 10);
    headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    headFont.setColor(HSSFColor.BLACK.index);

    headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
    headStyle.setLocked(true);
    headStyle.setFont(headFont);
    headStyle.setBorderTop((short) 2);
    headStyle.setBorderBottom((short) 1);

    XSSFCellStyle contentStyle = wb.createCellStyle();
    // apply custom headFont to the text in the comment
    XSSFFont contentFont = wb.createFont();
    contentFont.setFontName("Courier New");
    contentFont.setFontHeightInPoints((short) 9);
    // headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    contentFont.setColor(HSSFColor.BLACK.index);

    contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
    contentStyle.setFont(contentFont);

    // create titile row
    XSSFRow row = sheet1.createRow(0);
    int heandLine = 0;
    XSSFCell cell = null;

    if (titleNames != null) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(titleNames[i]);
        cell.setCellStyle(headStyle);
      }
      heandLine++;
    }

    for (int i = 0; i < rowNum; i++) {
      row = sheet1.createRow((i + heandLine));
      String[] line = (String[]) arry.get(i);
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (line[j] != null) {
          if (Util.isNumeric(line[j])) {
            // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(line[j]));
          } else {
            cell.setCellValue(line[j]);
          }
        } else {
          cell.setCellValue(".");
        }
      }
    }

    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(FileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }
Beispiel #22
0
  public static boolean writeArray2XLSXSheet(
      XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
    int rowNum = arry.size();
    if (rowNum == 0) {
      System.err.println("No input data!");
      return false;
    }

    String[] titleNames = null;
    if (hasHead) {
      titleNames = (String[]) arry.get(0);
    }
    int columnNum = ((String[]) arry.get(0)).length;

    for (int i = 0; i < columnNum; i++) {
      sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20)));
    }

    XSSFFont font = wb.createFont();
    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

    XSSFCellStyle headStyle = wb.createCellStyle();
    headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    headStyle.setLocked(true);
    headStyle.setFont(font);

    XSSFCellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    XSSFCellStyle markedBodyStyle = wb.createCellStyle();
    markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
    markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    int rowIndex = 0;
    // create titile row
    XSSFRow row = sheet1.createRow(rowIndex);

    XSSFCell cell = null;
    if (titleNames != null) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell((short) i);
        cell.setCellValue(titleNames[i]);
        cell.setCellStyle(headStyle);
      }
      rowIndex++;
    }

    for (int i = rowIndex; i < rowNum; i++) {
      row = sheet1.createRow((i));
      String[] line = (String[]) arry.get(i);
      columnNum = line.length;
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (line[0] != null) {
          cell.setCellStyle(markedBodyStyle);
        } else {
          cell.setCellStyle(bodyStyle);
        }
        if (line[j] != null) {
          if (Util.isNumeric(line[j])) {
            // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(line[j]));
          } else {
            cell.setCellValue(line[j]);
          }
        } else {
          cell.setCellValue("");
        }
      }
    }
    return true;
  }
Beispiel #23
0
  public static boolean convertTextFile2XLSXFile(
      String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception {
    BufferedReader br = LocalFileFunc.getBufferedReader(inFileName);
    String line = br.readLine();
    if (line == null) {
      return false;
    }
    String[] cells1 = Util.tokenize(line, '\t');
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet("Data");

    int columnNum = cells1.length;
    for (int i = 0; i < columnNum; i++) {
      sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
    }

    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom font to the text in the comment
    XSSFFont font = wb.createFont();
    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

    headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    headStyle.setLocked(true);
    headStyle.setFont(font);

    XSSFCellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    XSSFCellStyle markedBodyStyle = wb.createCellStyle();
    markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    int rowIndex = 0;
    // create titile row
    XSSFRow row = sheet1.createRow(rowIndex);

    String lastKey = null;
    int switcher = -1;
    XSSFCell cell = null;
    if (hasHead) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(cells1[i]);
        cell.setCellStyle(headStyle);
      }
    } else {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(cells1[i]);
        cell.setCellStyle(bodyStyle);
      }
    }
    rowIndex++;
    while ((line = br.readLine()) != null) {
      cells1 = Util.tokenize(line, '\t');
      row = sheet1.createRow((rowIndex));
      columnNum = cells1.length;
      if (indexKey >= 0) {
        if (lastKey == null && cells1[indexKey] != null) {
          lastKey = cells1[indexKey];
          switcher *= -1;
        } else if (lastKey != null && cells1[indexKey] == null) {
          lastKey = cells1[indexKey];
          switcher *= -1;
        } else if (lastKey == null && cells1[indexKey] == null) {
        } else {
          if (!lastKey.equals(cells1[indexKey])) {
            switcher *= -1;
            lastKey = cells1[indexKey];
          }
        }
      } else {
        switcher = 1;
      }
      // System.out.println(cells1[0]);
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (switcher > 0) {
          cell.setCellStyle(bodyStyle);
        } else {
          cell.setCellStyle(markedBodyStyle);
        }

        if (cells1[j] != null) {
          if (Util.isNumeric(cells1[j])) {
            // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(cells1[j]));
          } else {
            cell.setCellValue(cells1[j]);
          }
        } else {
          cell.setCellValue(".");
        }
      }
      rowIndex++;
    }
    br.close();
    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(outFileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }
Beispiel #24
0
  public static boolean writeMultArray2XLSXFile(
      String fileName,
      List<List<String[]>> arrys,
      List<String> sheetLabels,
      boolean hasHead,
      int indexKey)
      throws Exception {

    if (arrys.isEmpty()) {
      System.err.println("No input data!");
      return false;
    }

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom font to the text in the comment
    XSSFFont font = wb.createFont();

    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

    headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    headStyle.setLocked(true);
    headStyle.setFont(font);

    XSSFCellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    XSSFCellStyle markedBodyStyle = wb.createCellStyle();
    markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    String lastKey = null;
    int switcher = -1;
    XSSFCell cell = null;
    String[] titleNames = null;
    int d = 0;
    for (List<String[]> arry : arrys) {
      XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d));
      if (hasHead) {
        titleNames = (String[]) arry.get(0);
      }
      int columnNum = ((String[]) arry.get(0)).length;

      for (int i = 0; i < columnNum; i++) {
        sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
      }

      int rowIndex = 0;
      // create titile row
      XSSFRow row = sheet1.createRow(rowIndex);

      if (titleNames != null) {
        for (int i = 0; i < columnNum; i++) {
          cell = row.createCell(i);
          cell.setCellValue(titleNames[i]);
          cell.setCellStyle(headStyle);
        }
        rowIndex++;
      }
      int rowNum = arry.size();

      for (int i = rowIndex; i < rowNum; i++) {
        row = sheet1.createRow((i));
        String[] line = (String[]) arry.get(i);
        columnNum = line.length;
        if (indexKey >= 0) {
          if (lastKey == null && line[indexKey] != null) {
            lastKey = line[indexKey];
            switcher *= -1;
          } else if (lastKey != null && line[indexKey] == null) {
            lastKey = line[indexKey];
            switcher *= -1;
          } else if (lastKey == null && line[indexKey] == null) {
          } else {
            if (!lastKey.equals(line[indexKey])) {
              switcher *= -1;
              lastKey = line[indexKey];
            }
          }
        } else {
          switcher = 1;
        }
        for (int j = 0; j < columnNum; j++) {
          cell = row.createCell(j);
          if (switcher > 0) {
            cell.setCellStyle(bodyStyle);
          } else {
            cell.setCellStyle(markedBodyStyle);
          }

          if (line[j] != null) {
            if (Util.isNumeric(line[j])) {
              // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
              cell.setCellType(0);
              cell.setCellValue(Double.parseDouble(line[j]));
            } else {
              cell.setCellValue(line[j]);
            }
          } else {
            cell.setCellValue(".");
          }
        }
      }

      d++;
    }

    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(fileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }
Beispiel #25
0
 private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
   XSSFWorkbook wb = null;
   try {
     if (isXssf) {
       XSSFWorkbook w = new XSSFWorkbook();
     } else {
       HSSFWorkbook w = new HSSFWorkbook();
     }
     wb = new XSSFWorkbook();
     XSSFDataFormat format = wb.createDataFormat();
     XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
     XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
     XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
     XSSFDataFormat passwordFormat = wb.createDataFormat();
     passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
     List<ExcelHeader> headers = getHeaderList(clz);
     Collections.sort(headers);
     sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
     Row r0 = sheet.createRow(0);
     Cell cell = r0.createCell(0);
     r0.setHeightInPoints(28);
     cell.setCellValue(message + "备份记录");
     Row r = sheet.createRow(1);
     r.setHeightInPoints(25);
     cell.setCellStyle(cellStyle);
     // 输出标题
     for (int i = 0; i < headers.size(); i++) {
       Cell cell1 = r.createCell(i);
       if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
       else cell1.setCellStyle(cellStyle);
       cell1.setCellValue(headers.get(i).getTitle());
     }
     Object obj = null;
     // 输出用户资料信息
     if (message.indexOf("用户资料 ") > 0) {
       sheet.setColumnWidth(3, 32 * 150);
       sheet.setColumnWidth(4, 32 * 110);
       sheet.setColumnWidth(7, 32 * 120);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           if (getMethodName(headers.get(j)).equals("nabled"))
             cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
           else if (getMethodName(headers.get(j)).equals("password")) {
             cell2.setCellStyle(passwordCellStyle);
             cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
           } else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 输出房间使用信息数据
     else {
       sheet.setColumnWidth(0, 32 * 80);
       sheet.setColumnWidth(2, 32 * 100);
       sheet.setColumnWidth(3, 32 * 190);
       sheet.setColumnWidth(4, 32 * 190);
       sheet.setColumnWidth(5, 32 * 190);
       sheet.setColumnWidth(10, 32 * 130);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           if (j == 3 || j == 4 || j == 5) {
             XSSFCellStyle cs3 = wb.createCellStyle();
             cell2.setCellValue(new Date());
             copyDefaultCellStyle(format, cell2, cs3, 1);
           }
           if (j == 10) {
             XSSFCellStyle cs2 = wb.createCellStyle();
             copyDefaultCellStyle(format, cell2, cs2, 2);
           }
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 设置行列的默认宽度和高度
   } catch (IllegalAccessException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (InvocationTargetException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (NoSuchMethodException e) {
     e.printStackTrace();
     logger.error(e);
   }
   return wb;
 }
Beispiel #26
0
  @Command
  public void exportar() throws CloneNotSupportedException {
    //		if(itemSel == null){
    //			Clientes.showWarning("Seleccione una factura para realizar esta operacion");
    //			return;
    //		}

    XSSFWorkbook workbook = new XSSFWorkbook();

    // Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Articulos");

    // This data needs to be written (Object[])
    //		Map<String, Object[]> data = new TreeMap<String, Object[]>();
    //		data.put("1", new Object[] { "ID", "COD.GRUPO"
    //					, "GRUPO"
    //					, "COD.ART"
    //					, "COD.ART"
    //					, "ARTICULO"
    //					, "MARCA"
    //					, "PROCEDENCIA"
    //					, "U.MEDIDA"
    //					, "CANT"
    //					});

    Filtro f = new Filtro();
    //		f.setId_facturacion(itemSel.getId());
    f.setInicio(0);
    f.setFin(0);
    java.util.List itemsModel = itemsDao.selectPagos(f); // detalleFacturasMapper.selectDetalle(f);
    Iterator<Articulos> det = itemsModel.iterator();
    int rownum = 1;
    // cabeceras
    Row row0 = sheet.createRow(0);
    int i = 0;
    row0.createCell(i++).setCellValue("COD.GRUPO");
    row0.createCell(i++).setCellValue("GRUPO");
    row0.createCell(i++).setCellValue("COD.ART");
    row0.createCell(i++).setCellValue("ARTICULO");
    row0.createCell(i++).setCellValue("MARCA");
    row0.createCell(i++).setCellValue("PROCEDENCIA");
    row0.createCell(i++).setCellValue("U.MEDIDA");
    row0.createCell(i++).setCellValue("CANTIDAD");
    //		FacturasItemsExample fie = new FacturasItemsExample();
    //		fie.createCriteria().andId_factEqualTo(itemSel.getId());
    //		List<FacturasItems> flist = facturasItemsMapper.selectByExample(fie);
    //		Iterator<FacturasItems> flistItem = flist.iterator();
    //		int i = 5;
    //		while (flistItem.hasNext()) {
    //			FacturasItems ff = flistItem.next();
    //			row0.createCell(i++).setCellValue(ff.getDescripcion());
    //		}
    // datos
    while (det.hasNext()) {
      Articulos df = det.next();
      Row row = sheet.createRow(rownum++);
      i = 0;
      row.createCell(i++).setCellValue(df.getCodigogrupo());
      row.createCell(i++).setCellValue(df.getGrupo());
      row.createCell(i++).setCellValue(df.getCodigo());
      row.createCell(i++).setCellValue(df.getArticulo());
      row.createCell(i++).setCellValue(df.getMarca());
      row.createCell(i++).setCellValue(df.getProcedencia());
      row.createCell(i++).setCellValue(df.getUnidadmedida());
      row.createCell(i++).setCellValue(new BigDecimal("0").doubleValue());

      //			DetalleFacturasItemsExample de = new DetalleFacturasItemsExample();
      //			de.createCriteria().andId_dfEqualTo(df.getId());
      //			List<DetalleFacturasItems> dflist = detalleFacturasItemsMapper
      //					.selectByExample(de);
      //			Iterator<DetalleFacturasItems> dfit = dflist.iterator();
      //			i = 5;
      //			while (dfit.hasNext()) {
      //				DetalleFacturasItems dfi = dfit.next();
      //				row.createCell(i++).setCellValue(dfi.getValor().doubleValue());
      //			}

    }

    try {
      // Write the workbook in file system

      ParametrosKey pk = new ParametrosKey();
      pk.setId("DATA_FOLDER");
      pk.setId_app(UsuarioLogueado.getIdApp());
      String dataFolder = parametrosMapper.selectByPrimaryKey(pk).getValor();
      String path = dataFolder + File.separatorChar + "export_articulos_tmp.xlsx";
      FileOutputStream out = new FileOutputStream(new File(path));
      workbook.write(out);
      out.close();

      File iof = new File(path);
      if (!iof.exists()) {
        Clientes.showWarning("No se puede encontrar el archivo generado");
        return;
      }

      java.io.InputStream is =
          new FileInputStream(iof); // desktop.getWebApp().getResourceAsStream(path);
      if (is != null) {
        AMedia am = new AMedia(iof, null, null);
        SimpleDateFormat fmt = new SimpleDateFormat("MM-yyyy");
        String fe = fmt.format(new java.util.Date());
        String me = fe.substring(0, 7);
        Filedownload.save(is, am.getContentType(), "Db_productos_" + me);

      } else {
        Clientes.showWarning("No se puede encontrar el archivo generado");
      }

      // System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
 @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;
 }
Beispiel #28
0
  public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
    File excelFile = new File("export_parts.xls");
    // Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    // Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Parts Data");

    String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
    String[] columns = header.split(";");

    Map<Integer, String[]> data = new HashMap<>();
    String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
    data.put(1, headerFormatted);

    Map<Integer, String[]> commentsData = new HashMap<>();
    String[] headerComments = createXLSHeaderRowComments(header, columns);
    commentsData.put(1, headerComments);

    List<String> selects = queryResult.getQuery().getSelects();
    int i = 1;
    for (QueryResultRow row : queryResult.getRows()) {
      i++;
      data.put(i, createXLSRow(selects, row, baseURL));
      commentsData.put(i, createXLSRowComments(selects, row));
    }

    // Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;

    for (Integer key : keyset) {

      Row row = sheet.createRow(rownum++);
      String[] objArr = data.get(key);
      int cellnum = 0;
      for (String obj : objArr) {
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue(obj);
      }

      CreationHelper factory = workbook.getCreationHelper();
      Drawing drawing = sheet.createDrawingPatriarch();
      String[] commentsObjArr = commentsData.get(key);
      cellnum = 0;
      for (String commentsObj : commentsObjArr) {
        if (commentsObj.length() > 0) {
          Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);

          // When the comment box is visible, have it show in a 1x3 space
          ClientAnchor anchor = factory.createClientAnchor();
          anchor.setCol1(cell.getColumnIndex());
          anchor.setCol2(cell.getColumnIndex() + 1);
          anchor.setRow1(row.getRowNum());
          anchor.setRow2(row.getRowNum() + 1);

          Comment comment = drawing.createCellComment(anchor);
          RichTextString str = factory.createRichTextString(commentsObj);
          comment.setString(str);

          // Assign the comment to the cell
          cell.setCellComment(comment);
        }
        cellnum++;
      }
    }

    // Define header style
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setFontName("Courier New");
    headerFont.setItalic(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    // Set header style
    for (int j = 0; j < columns.length; j++) {
      Cell cell = sheet.getRow(0).getCell(j);
      cell.setCellStyle(headerStyle);

      if (cell.getCellComment() != null) {
        String comment = cell.getCellComment().getString().toString();

        if (comment.equals(QueryField.CTX_PRODUCT_ID)
            || comment.equals(QueryField.CTX_SERIAL_NUMBER)
            || comment.equals(QueryField.PART_MASTER_NUMBER)) {
          for (int k = 0; k < queryResult.getRows().size(); k++) {
            Cell grayCell =
                sheet.getRow(k + 1).getCell(j) != null
                    ? sheet.getRow(k + 1).getCell(j)
                    : sheet.getRow(k + 1).createCell(j);
            grayCell.setCellStyle(headerStyle);
          }
        }
      }
    }

    try {
      // Write the workbook in file system
      FileOutputStream out = new FileOutputStream(excelFile);
      workbook.write(out);
      out.close();
    } catch (Exception e) {
      LOGGER.log(Level.FINEST, null, e);
    }
    return excelFile;
  }
  @Override
  public XSSFWorkbook exportAllStatistics(
      List<StatisticData> statistics, Interview interview, ByteArrayOutputStream out)
      throws IOException {
    /*NULL is correct value*/
    try (XSSFWorkbook book = new XSSFWorkbook()) {
      XSSFSheet sheet = book.createSheet(SHEET_NAME);
      int rowNumber = 0;

      XSSFRow mainHeader = sheet.createRow(rowNumber++);
      XSSFCell cell = mainHeader.createCell(0);
      cell.setCellType(Cell.CELL_TYPE_STRING);
      cell.setCellValue(HEADER_PREFIX + interview.getName() + "\"");
      makeCellAutosizeAndBold(book, mainHeader);

      rowNumber++;

      for (StatisticData statistic : statistics) {
        /*Add question text*/
        XSSFRow row = sheet.createRow(rowNumber++);
        XSSFCell questionText = row.createCell(0);
        questionText.setCellType(Cell.CELL_TYPE_STRING);
        questionText.setCellValue(QUESTION_PREFIX + statistic.getQuestionText());

        XSSFRow tableHeader = sheet.createRow(rowNumber++);
        XSSFCell c1 = tableHeader.createCell(0);
        XSSFCell c2 = tableHeader.createCell(1);
        XSSFCell c3 = tableHeader.createCell(2);
        XSSFCell c4 = tableHeader.createCell(3);

        c1.setCellType(Cell.CELL_TYPE_STRING);
        c2.setCellType(Cell.CELL_TYPE_NUMERIC);
        c3.setCellType(Cell.CELL_TYPE_NUMERIC);
        c4.setCellType(Cell.CELL_TYPE_STRING);

        c1.setCellValue(ANSWER_CELL_HEADER);
        c2.setCellValue(PEOPLE_COUNT_HEADER);
        c3.setCellValue(PERCENT_HEADER);
        c4.setCellValue(RESPONDENTS_HEADER);

        makeCellAutosizeAndBold(book, tableHeader);

        Map<String, Object[]> answerData = statistic.getAnswerData();

        for (String key : answerData.keySet()) {
          XSSFRow answer = sheet.createRow(rowNumber++);
          XSSFCell answerText = answer.createCell(0);
          XSSFCell peopleResponded = answer.createCell(1);
          XSSFCell percentResponded = answer.createCell(2);
          XSSFCell respondents = answer.createCell(3);

          answerText.setCellType(Cell.CELL_TYPE_STRING);
          peopleResponded.setCellType(Cell.CELL_TYPE_NUMERIC);
          percentResponded.setCellType(Cell.CELL_TYPE_NUMERIC);
          respondents.setCellType(Cell.CELL_TYPE_STRING);

          answerText.setCellValue(key);
          Object[] values = answerData.get(key);

          peopleResponded.setCellValue(values[0].toString());
          percentResponded.setCellValue(values[1].toString().replace(",", "."));
          respondents.setCellValue(userAnswerService.getRespondentListHowLine(interview, key));

          makeCellsAutosize(book, answer);
        }

        rowNumber++;
      }

      book.write(out);
      return book;
    }
  }
  public XSSFWorkbook writeCTGI(
      XSSFWorkbook cTGWorkbook,
      MultipartFile fileIn,
      String classId,
      MultipartFile staplesMasterStyleGuide,
      MultipartFile attributeReport) {
    try {
      List<XSSFCell> attributeCell = writeStyle(staplesMasterStyleGuide, classId);
      List<String> attributes = getAttributes(attributeReport, classId);
      XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
      XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
      XSSFSheet worksheetOut = cTGWorkbook.getSheetAt(0);
      int i = 1;
      int j = 4;
      XSSFCell cell = null;
      for (i = 1; i < worksheetIn.getLastRowNum(); i++) {
        XSSFRow rowIn = worksheetIn.getRow(i);
        XSSFRow rowOut = worksheetOut.createRow(j);

        if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
          XSSFCell cell1 = rowOut.createCell(13);
          if (rowIn.getCell(2) != null) cell1.setCellValue(rowIn.getCell(2).getStringCellValue());
          XSSFCell cell2 = rowOut.createCell(15);
          cell2.setCellValue(classId);
          XSSFCell cell3 = rowOut.createCell(19);
          if (rowIn.getCell(3) != null) cell3.setCellValue(rowIn.getCell(3).getStringCellValue());
          XSSFCell cell4 = rowOut.createCell(20);
          if (rowIn.getCell(0) != null) cell4.setCellValue(rowIn.getCell(0).getStringCellValue());
          XSSFCell cell5 = rowOut.createCell(24);
          if (rowIn.getCell(3) != null) cell5.setCellValue(rowIn.getCell(3).getStringCellValue());
          XSSFCell cell6 = rowOut.createCell(179);
          if (rowIn.getCell(5) != null) cell6.setCellValue(rowIn.getCell(5).getNumericCellValue());

          XSSFCell cell7 = rowOut.createCell(26);
          cell7 = attributeCell.get(0);

          int z = 1;
          for (int k = 36; k < 48; k++) {
            cell = rowOut.createCell(k);
            cell.setCellValue((attributeCell.get(z)).getStringCellValue());
            z++;
          }
          int counter = 1;
          int cellCount = 63;
          for (String attribute : attributes) {

            if (counter <= 50) {
              String lable = attribute.split("###")[0];
              String value = attribute.split("###")[1];
              cell = rowOut.createCell(cellCount);
              cell.setCellValue(lable);
              cell = rowOut.createCell(cellCount + 1);
              cell.setCellValue(value);
              counter++;
              cellCount += 2;
            }
          }
        }
        j++;
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
    return cTGWorkbook;
  }