Ejemplo n.º 1
0
 // ����Excel
 public String ExcelExport() throws Exception {
   HttpServletRequest request = ServletActionContext.getRequest();
   String ids = request.getParameter("ids");
   List<Dise> list = new ArrayList<Dise>();
   String[] array = ids.split(",");
   int[] id = new int[array.length];
   for (int i = 0; i < id.length; i++) {
     Dise dise = DiseService.findById(Integer.valueOf(array[i]));
     list.add(dise);
   }
   Workbook workbook = new HSSFWorkbook();
   Sheet sheet = workbook.createSheet("ѧ����Ϣ");
   Row row = sheet.createRow(0);
   row.createCell(0).setCellValue("ѧ��");
   row.createCell(1).setCellValue("����");
   row.createCell(2).setCellValue("����");
   row.createCell(3).setCellValue("�Ա�");
   row.createCell(4).setCellValue("��ַ");
   CellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
   for (int i = 1; i <= list.size(); i++) {
     Dise stu = list.get(i - 1);
     row = sheet.createRow(i);
     row.createCell(0).setCellValue(stu.getIds());
     row.createCell(1).setCellValue(stu.getName());
     row.createCell(2).setCellValue(stu.getA());
     row.createCell(3).setCellValue(stu.getB());
     row.createCell(4).setCellValue(stu.getC());
   }
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   workbook.write(baos);
   excelFile = new ByteArrayInputStream(baos.toByteArray());
   baos.close();
   return "excel";
 }
Ejemplo n.º 2
0
  public void make(File source, DataSet header, DataSet master) throws Exception {
    this.header = header;
    this.master = master;

    if (header == null || master == null) throw new Exception("Dataset is empty");

    long t = System.currentTimeMillis();

    InputStream inp = new FileInputStream(source);
    Workbook oldBook = WorkbookFactory.create(inp);
    Sheet oldSheet = oldBook.getSheetAt(0);

    Workbook newBook = new HSSFWorkbook();
    Sheet newSheet = newBook.createSheet(oldSheet.getSheetName());

    init(newBook);
    process(oldSheet, newSheet);

    File target = File.createTempFile("libra", ".xls");
    target.deleteOnExit();
    FileOutputStream fileOut = new FileOutputStream(target);

    newBook.write(fileOut);
    fileOut.close();
    oldBook.close();
    inp.close();

    Desktop.getDesktop().open(target);

    System.out.println(System.currentTimeMillis() - t);
  }
Ejemplo n.º 3
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());
   }
 }
Ejemplo n.º 4
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");
  }
Ejemplo n.º 5
0
  /**
   * Called to update the embedded Excel workbook. As the format and structire of the workbook are
   * known in advance, all this code attempts to do is write a new value into the first cell on the
   * first row of the first worksheet. Prior to executing this method, that cell will contain the
   * value 1.
   *
   * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException Rather than use the specific
   *     classes (HSSF/XSSF) to handle the embedded workbook this method uses those defeined in the
   *     SS stream. As a result, it might be the case that a SpreadsheetML file is opened for
   *     processing, throwing this exception if that file is invalid.
   * @throws java.io.IOException Thrown if a problem occurs in the underlying file system.
   */
  public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
      pIter = embeddedDocs.iterator();
      while (pIter.hasNext()) {
        pPart = pIter.next();
        if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
            || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {

          // Get an InputStream from the pacage part and pass that
          // to the create method of the WorkbookFactory class. Update
          // the resulting Workbook and then stream that out again
          // using an OutputStream obtained from the same PackagePart.
          workbook = WorkbookFactory.create(pPart.getInputStream());
          sheet = workbook.getSheetAt(SHEET_NUM);
          row = sheet.getRow(ROW_NUM);
          cell = row.getCell(CELL_NUM);
          cell.setCellValue(NEW_VALUE);
          workbook.write(pPart.getOutputStream());
        }
      }

      // Finally, write the newly modified Word document out to file.
      this.doc.write(new FileOutputStream(this.docFile));
    }
  }
Ejemplo n.º 6
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();
   }
 }
Ejemplo n.º 7
0
 @RequestMapping(value = "exportExcel")
 public String exportExcel(HttpServletRequest request, HttpServletResponse response)
     throws Exception {
   String operator = request.getParameter("filter_LIKES_operator");
   String time = request.getParameter("filter_LIKES_time");
   StringBuilder builder = new StringBuilder("from OptLog where 1=1");
   if (StringUtils.isNotBlank(operator)) {
     builder.append(" and operator like '%").append(operator).append("%'");
     request.setAttribute("filter_LIKES_operator", operator);
   }
   if (StringUtils.isNotBlank(time)) {
     builder.append(" and time like '%").append(time).append("%'");
     request.setAttribute("filter_LIKES_time", time);
   }
   builder.append(" order by id desc");
   List<OptLog> data = optLogManager.findOptLogOrderby(builder.toString());
   // 生成Excel文件.
   Workbook wb = new ExcelExporter().export("操作日志", data);
   // 输出Excel文件.
   // HttpServletResponse response = Struts2Utils.getResponse();
   response.setContentType(ServletUtils.EXCEL_TYPE);
   ServletUtils.setFileDownloadHeader(response, "操作日志.xls");
   wb.write(response.getOutputStream());
   response.getOutputStream().flush();
   return null;
 }
Ejemplo n.º 8
0
  @RequestMapping(value = "/excel", method = RequestMethod.GET)
  public void excel(HttpServletResponse response) throws Exception {
    Workbook wb = new HSSFWorkbook();
    //        CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");
    org.apache.poi.ss.usermodel.Row row = sheet.createRow((short) 0);
    row = sheet.createRow((short) 1);
    row.createCell(0).setCellValue("Mã");
    row.createCell(1).setCellValue("Tên");
    row.createCell(2).setCellValue("Mã Path");
    row.createCell(3).setCellValue("Tên path");
    row.createCell(4).setCellValue("Cấp danh mục");

    int i = 1;
    List<Category> listAll = categoryService.listAll();
    for (Category print : listAll) {
      if (print.getName().length() > 19) {
        i++;
        row = sheet.createRow((short) i);
        row.createCell(0).setCellValue(String.valueOf(print.getId()));
        row.createCell(1).setCellValue(String.valueOf(print.getName()));
        row.createCell(2).setCellValue(String.valueOf(print.getPath()));
        String path = "";
        for (String categoryId : print.getPath()) {
          Category get = categoryService.get(categoryId);
          path += get.getName() + " >>";
        }
        row.createCell(3).setCellValue(String.valueOf(path));
        row.createCell(4).setCellValue(String.valueOf(print.getLevel()));
      }
    }
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=Danh-sach-danh-muc.xls");
    wb.write(response.getOutputStream());
  }
  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;
  }
Ejemplo n.º 10
0
  /**
   * 基类基于子类提供的相关参数数据, 生成JXLS报表
   *
   * @see #exportXlsForGrid(List, Sort, GroupPropertyFilter) 此方法中基于参数组装好相关的data数据后,调用此方法生成Excel响应
   * @param dataMap
   */
  protected void exportExcel(
      String templateFileName, String exportFileName, Map<String, Object> dataMap) {
    // 日期格式定义
    dataMap.put("dateFormatter", new SimpleDateFormat(DateUtils.DEFAULT_DATE_FORMAT));
    dataMap.put("timeFormatter", new SimpleDateFormat(DateUtils.DEFAULT_TIME_FORMAT));

    HttpServletResponse response = ServletActionContext.getResponse();
    InputStream fis = null;
    OutputStream fos = null;
    try {
      Resource resource = new ClassPathResource("/template/xls/" + templateFileName);
      logger.debug("Open template file inputstream: {}", resource.getURL());
      fis = resource.getInputStream();

      XLSTransformer transformer = new XLSTransformer();
      // generate the excel workbook according to the template and
      // parameters
      Workbook workbook = transformer.transformXLS(fis, dataMap);
      String filename = exportFileName;
      filename = new String(filename.getBytes("GBK"), "ISO-8859-1");
      response.setHeader("Content-Disposition", "attachment;filename=" + filename);
      response.setContentType("application/vnd.ms-excel;charset=utf-8");
      fos = response.getOutputStream();
      // output the generated excel file
      workbook.write(fos);
    } catch (Exception e) {
      throw new WebException(e.getMessage(), e);
    } finally {
      IOUtils.closeQuietly(fis);
      IOUtils.closeQuietly(fos);
    }
  }
Ejemplo n.º 11
0
  public void inserirCliente(Cliente cliente) {

    int count = 0;
    boolean achou = false;
    int ultimaLinha = wb.getSheetAt(0).getLastRowNum();
    // verifica se existe um espaço em null entre as linhas da planilha
    while (count <= ultimaLinha && !achou) {

      if (wb.getSheetAt(0).getRow(count) == null) {
        Row selectedRow = wb.getSheetAt(0).createRow(count);
        this.createRow(selectedRow, cliente);
        achou = true;
      } else if (count == ultimaLinha) {
        Row selectedRow = wb.getSheetAt(0).createRow(wb.getSheetAt(0).getLastRowNum() + 1);
        this.createRow(selectedRow, cliente);
        achou = true;
      }
      count++;
    }

    try {
      this.fos = new FileOutputStream("RepositorioCliente.xls");
      wb.write(fos);
      fos.close();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Ejemplo n.º 12
0
  public void removerCliente(String cpf) {

    int count = 1;
    boolean achou = false;
    int ultimaLinha = wb.getSheetAt(0).getLastRowNum();

    while (count < ultimaLinha && !achou) {

      if (wb.getSheetAt(0).getRow(count).getCell(1).equals(cpf)) {
        Row row = wb.getSheetAt(0).getRow(count);
        wb.getSheetAt(0).removeRow(row);
        achou = true;
      } else {
        count++;
      }
    }
    try {
      this.fos = new FileOutputStream("RepositorioCliente.xls");
      wb.write(fos);
      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Ejemplo n.º 13
0
 /**
  * write the excel that have been made into a OutputStream
  *
  * @param os destination OutputStream
  * @return ExcelMaker itself
  */
 public ExcelMaker writeTo(OutputStream os)
     throws NullPointerException, IllegalArgumentException, IOException {
   checkNotNull(workbook, "workbook isn't created yet");
   if (workbook.getNumberOfSheets() == 0) {
     this.make();
   }
   workbook.write(os);
   return this;
 }
Ejemplo n.º 14
0
 @Override
 public void close() throws IOException {
   try {
     workbook.write(os);
   } catch (IOException e) {
     throw new MolgenisDataException("Exception writing to excel file", e);
   }
   os.close();
 }
Ejemplo n.º 15
0
 @Override
 protected void closeFile() {
   try {
     wb.write(fileOut);
     fileOut.close();
   } catch (IOException e) {
     e.printStackTrace();
   }
 }
Ejemplo n.º 16
0
 private void saveWorkbook(Workbook resultWorkbook, String fileName) throws IOException {
   String saveResultsProp = System.getProperty("saveResults");
   if ("true".equalsIgnoreCase(saveResultsProp)) {
     OutputStream os = new BufferedOutputStream(new FileOutputStream(fileName));
     resultWorkbook.write(os);
     os.flush();
     os.close();
   }
 }
Ejemplo n.º 17
0
 private void saveWorkbook(Workbook resultWorkbook, String fileName) throws IOException {
   if (log.isInfoEnabled()) {
     log.info("Saving " + fileName);
   }
   OutputStream os = new BufferedOutputStream(new FileOutputStream(fileName));
   resultWorkbook.write(os);
   os.flush();
   os.close();
   log.info("Output Excel saved to " + fileName);
 }
Ejemplo n.º 18
0
  private void writeFullDataToSpreadsheet(
      OutputStream fos, Map<String, List<Map<String, Object>>> data) throws IOException {
    LOG.debug("writeFullDataToSpreadsheet started" + data);

    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    // Sheet sheet = workbook.getSheetAt(0);

    // LOG.debug("workbook.getActiveSheetIndex()" + workbook.getActiveSheetIndex());
    // sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    int rowNum = sheet.getLastRowNum();
    LOG.debug("sheet.getLastRowNum()" + sheet.getLastRowNum());

    for (String methodName : data.keySet()) {

      boolean isHeaderWritten = false;
      Map<String, Integer> parameterIndexMap = new LinkedHashMap<String, Integer>();
      for (Map<String, Object> methodData : data.get(methodName)) {
        // rowNum increment by one to proceed with next record of the method.
        LOG.debug("methodData.keySet().size" + methodData.keySet().size());
        LOG.debug("methodData" + methodData);

        if (!isHeaderWritten) {
          int columnIndex = 0;
          // Write the method name and parameter names in header.
          writeDataToCell(sheet, rowNum, columnIndex++, methodName);
          for (String parameterName : methodData.keySet()) {
            writeDataToCell(sheet, rowNum, columnIndex, parameterName);
            // capturing column index so that corresponding values will be placed at same column
            parameterIndexMap.put(parameterName, columnIndex);
            columnIndex++;
          }
          // incrementing row after writing header
          rowNum++;
          isHeaderWritten = true;
        }

        // Write the actual result and test status values.
        if (isHeaderWritten) {

          int columnIndex = 0;
          // we need to put empty cell in first column as per easytest xls structure.
          writeDataToCell(sheet, rowNum, columnIndex++, null);
          for (String parameter : methodData.keySet()) {
            writeDataToCell(
                sheet, rowNum, parameterIndexMap.get(parameter), methodData.get(parameter));
          }
          rowNum++;
        }
      }
    }
    // Write the output to a file
    workbook.write(fos);
    LOG.debug("writeFullDataToSpreadsheet finished");
  }
Ejemplo n.º 19
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;
  }
Ejemplo n.º 20
0
 /** @throws Exception */
 public void makeXlsxFile() throws Exception {
   String fileName = "test.xlsx";
   FileOutputStream fileOut = new FileOutputStream(fileName);
   Workbook wb = new XSSFWorkbook();
   Sheet sheet = wb.createSheet("test"); // シート作成
   Row row = sheet.createRow((short) 0);
   Cell cell = row.createCell(0);
   cell.setCellValue("日本語は通る?");
   wb.write(fileOut);
   fileOut.close();
 }
Ejemplo n.º 21
0
 @RequestMapping("export")
 public void exportData(HttpServletResponse response) {
   try (OutputStream out = response.getOutputStream();
       Workbook wb = bo.doExport(); ) {
     response.setContentType("application/x-msexcel");
     response.setHeader("Content-Disposition", "attachment; filename=record.xls");
     wb.write(out);
   } catch (IOException e) {
     e.printStackTrace();
   }
 }
Ejemplo n.º 22
0
  /**
   * put dataList to excel sheets.
   *
   * @param sheetName excel sheet name.
   * @param dataList data list to be parsed and put into excel sheets.
   * @param rowNum row count of the sheet to be modified.
   * @param ignoreRows rows to skip when put value.
   * @param ignoreColumns columns to skip when put value.
   * @throws RuntimeException
   * @throws IllegalArgumentException
   */
  public void putListToExcelWithFullIgnore(
      String sheetName, List<String> dataList, int rowNum, int ignoreRows, int ignoreColumns) {
    if (dataList.size() % rowNum != 0) {
      LOG.error("dataList has wrong element count for excel!");
      throw new IllegalArgumentException("dataList has wrong element count for excel!");
    }

    String value = null;
    int index = 0;
    final int colCount = dataList.size() / rowNum;
    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);
      }

      for (int j = ignoreRows; j < ignoreRows + rowNum; j++) {
        xlRow = xlSheet.getRow(j);
        if (xlRow == null) {
          xlRow = xlSheet.createRow(j);
        }
        for (int i = ignoreColumns; i < ignoreColumns + colCount; i++) {
          value = dataList.get(index);
          xlCell = xlRow.getCell(i);
          if (xlCell == null) {
            xlCell = xlRow.createCell(i);
          }

          xlCell.setCellType(1);
          if (value != null) {
            xlCell.setCellValue(value);
          }
          index++;
        }
      }

      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());
    }
  }
Ejemplo n.º 23
0
 /**
  * 导出对象到Excel,不是基于模板的,直接新建一个Excel完成导出,基于流
  *
  * @param os 输出流
  * @param objs 对象列表
  * @param clz 对象类型
  * @param isXssf 是否是2007版本
  */
 public void exportExcelByPath(
     OutputStream os, List objs, Class clz, boolean isXssf, String message) {
   try {
     Workbook wb = handleExcel(objs, clz, isXssf, message);
     wb.write(os);
   } catch (FileNotFoundException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (IOException e) {
     e.printStackTrace();
     logger.error(e);
   }
 }
Ejemplo n.º 24
0
 public String setTestData(String sheetName, int rowNum, int colNum, String data)
     throws EncryptedDocumentException, InvalidFormatException, IOException {
   FileInputStream fis = new FileInputStream(filepath);
   Workbook wb = WorkbookFactory.create(fis);
   Sheet sh = wb.getSheet(sheetName);
   Row row = sh.getRow(rowNum);
   Cell cel = row.createCell(colNum);
   cel.setCellType(Cell.CELL_TYPE_STRING);
   cel.setCellValue(data);
   FileOutputStream fos = new FileOutputStream(filepath);
   cel.setCellValue(data);
   wb.write(fos);
   wb.close();
   return data;
 }
  public static void main(String[] args) throws Exception {
    // The path to the documents directory.
    String dataDir = Utils.getDataDir(ApacheHideUnHideCells.class);

    InputStream inStream = new FileInputStream(dataDir + "workbook.xls");
    Workbook workbook = WorkbookFactory.create(inStream);
    Sheet sheet = workbook.createSheet();
    Row row = sheet.createRow(0);
    row.setZeroHeight(true);

    FileOutputStream fileOut = new FileOutputStream(dataDir + "ApacheHideUnhideCells.xls");
    workbook.write(fileOut);
    fileOut.close();

    System.out.println("Process Completed.");
  }
  public Reporting() {
    try {
      FileInputStream file = new FileInputStream(new File("reportTest.xls"));
      workbook = new HSSFWorkbook(file);

      Workbook wb = new HSSFWorkbook();
      FileOutputStream fileOut = new FileOutputStream("Testbook.xls");
      wb.write(fileOut);
      fileOut.close();
    } catch (IOException ex) {
      ex.printStackTrace();
      JOptionPane.showMessageDialog(null, ex);
      // log Error
      FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
    }
  }
Ejemplo n.º 27
0
  @Override
  public OutputStream createXlsData(
      String metricName, String rrdFilename, long startTime, long endTime)
      throws IOException, MetricsGraphException {
    LOGGER.trace("ENTERING: createXlsData");

    Workbook wb = new HSSFWorkbook();
    createSheet(wb, metricName, rrdFilename, startTime, endTime);

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    wb.write(bos);
    bos.close();

    LOGGER.trace("EXITING: createXlsData");

    return bos;
  }
Ejemplo n.º 28
0
  public String uploadTemplateDownload() throws IOException {
    // 1. 调用 Service 方法, 获取 Excel 文档对应的 Workbook 对象
    Workbook workbook = employeeService.buildUploadExcelWorkbook();
    // 2. 先写到一个指定的位置
    String fileName =
        ServletActionContext.getServletContext()
            .getRealPath("/files/" + System.currentTimeMillis() + ".xls");
    workbook.write(new FileOutputStream(fileName));

    // 3. 确定输入流
    inputStream = new FileInputStream(fileName);
    this.contentType = "application/vnd.ms-excel";
    this.contentLength = inputStream.available();
    this.contentDisposition = "attachment;filename=employees.xls";

    return "download-success";
  }
  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();
  }
Ejemplo n.º 30
0
 @ResponseBody
 @RequestMapping(value = "/admin/orders/export.html", method = RequestMethod.GET)
 public ResponseEntity<byte[]> export() throws Exception {
   Map<String, Object> model = new HashMap<String, Object>();
   List<Order> orders = orderRepository.export();
   model.put("orders", orders);
   XLSTransformer transformer = new XLSTransformer();
   Resource resource = new ClassPathResource("/template/OrderExport.xlsx");
   Workbook workbook = transformer.transformXLS(resource.getInputStream(), model);
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   workbook.write(baos);
   final HttpHeaders headers = new HttpHeaders();
   headers.setContentType(
       MediaType.valueOf("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
   headers.set("Content-Disposition", "attachment;Filename=OrderExport.xlsx");
   headers.setCacheControl("no-cache");
   return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.OK);
 }