Ejemplo n.º 1
3
  private void createSummerySheet() {
    sheet0 = workbook.createSheet("Summary");
    PrintSetup printSetup = sheet0.getPrintSetup();
    printSetup.setLandscape(true);
    sheet0.setFitToPage(true);
    sheet0.setHorizontallyCenter(true);

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

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

    // finally set column widths, the width is measured in units of 1/256th
    // of a character width
    sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
  }
Ejemplo n.º 2
0
  private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
    // 报表标题字体
    Font fontTitle = worksheet.getWorkbook().createFont();
    fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD);
    fontTitle.setFontHeight((short) 280);

    // 标题单元格格式
    HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
    cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleTitle.setWrapText(true);
    cellStyleTitle.setFont(fontTitle);

    HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
    rowTitle.setHeight((short) 500);
    HSSFCell cellTitle = rowTitle.createCell(startColIndex);
    cellTitle.setCellValue("学生列表");
    cellTitle.setCellStyle(cellStyleTitle);

    worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); // 标题合并列

    Date date = new Date();
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
    HSSFCell cellDate = dateTitle.createCell(startColIndex);
    cellDate.setCellValue("这个报表创建于: " + dateFormat.format(date));
  }
  private void writeHeader(HSSFSheet sheet) {

    this.rowCount = 0;
    short column = 0;

    // first row
    HSSFRow row = sheet.createRow(rowCount++);
    write(row, column++, "Project Name", style);
    write(row, column++, project.getName());

    // Second row
    column = 0;
    row = sheet.createRow(rowCount++);
    write(row, column++, "Version", style);
    write(row, column++, "Field", style);
    write(row, column++, "Type", style);

    // write locales
    for (Locale locale : getLocales()) {
      String displayStr = LanguageUtils.getShortDisplayString(locale, Locale.ENGLISH);
      write(row, column++, displayStr, style);
    }

    assert rowCount == 2;
  }
Ejemplo n.º 4
0
  /**
   * Builds the report title and the date header
   *
   * @param worksheet
   * @param startRowIndex starting row offset
   * @param startColIndex starting column offset
   */
  public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
    // Create font style for the report title
    Font fontTitle = worksheet.getWorkbook().createFont();
    fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
    fontTitle.setFontHeight((short) 280);

    // Create cell style for the report title
    HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
    cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleTitle.setWrapText(true);
    cellStyleTitle.setFont(fontTitle);

    // Create report title
    HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
    rowTitle.setHeight((short) 500);
    HSSFCell cellTitle = rowTitle.createCell(startColIndex);
    cellTitle.setCellValue("Compensation Report");
    cellTitle.setCellStyle(cellStyleTitle);

    // Create merged region for the report title
    worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));

    // Create date header
    HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
    HSSFCell cellDate = dateTitle.createCell(startColIndex);
    cellDate.setCellValue("This report was generated at " + new Date());
  }
Ejemplo n.º 5
0
  public static void writeExcel(String excelPath, String testCase) throws Exception {
    /**
     * create Excel file in excelPath build two sheet which names are "TestSummary" and testcase
     * detailinfo
     */
    FileOutputStream fos = new FileOutputStream(excelPath);
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet s1 = wb.createSheet();
    wb.setSheetName(0, "TestSummary");
    HSSFSheet s2 = wb.createSheet();
    wb.setSheetName(1, testCase + "_DetailInfo");

    HSSFRow s1row = s1.createRow(0);
    HSSFRow s2row = s2.createRow(0);

    s1row.createCell(0).setCellValue("Total TCs");
    s1row.createCell(1).setCellValue("Run TCs");
    s1row.createCell(2).setCellValue("Passed TCs");
    s1row.createCell(3).setCellValue("Failure TCs");
    s1row.createCell(4).setCellValue("NoRun TCs");

    s2row.createCell(0).setCellValue("SubTestCase");
    s2row.createCell(1).setCellValue("TestResult");
    s2row.createCell(2).setCellValue("AssertPointCheck");
    s2row.createCell(3).setCellValue("ScreenShot");
    s2row.createCell(4).setCellValue("Processor");

    wb.write(fos);
    fos.close();
  }
Ejemplo n.º 6
0
  public static void writeExcel(List<TongJiModel> list) throws Exception {
    // 创建Excel的工作书册 Workbook,对应到一个excel文档
    HSSFWorkbook wb = new HSSFWorkbook();

    int columnCount = 5;

    // 创建Excel的工作sheet,对应到一个excel文档的tab
    HSSFSheet sheet = wb.createSheet("sheet1");
    HSSFRow first = sheet.createRow(0); // 下标为0的行开始

    String[] firstRow = new String[columnCount];
    firstRow[0] = "姓名";
    firstRow[1] = "组名";
    firstRow[2] = "邀请投资次数";
    firstRow[3] = "邀请投资总金额";
    firstRow[4] = "邀请注册人数";

    for (int i = 0; i < columnCount; i++) {
      first.createCell(i).setCellValue(new HSSFRichTextString(firstRow[i]));
    }
    for (int i = 0; i < list.size(); i++) {
      HSSFRow row = sheet.createRow(i + 1);
      TongJiModel tongJiModel = list.get(i);
      row.createCell(0).setCellValue(new HSSFRichTextString(tongJiModel.getName()));
      row.createCell(1).setCellValue(new HSSFRichTextString(tongJiModel.getGroupName()));
      row.createCell(2).setCellValue(new HSSFRichTextString(tongJiModel.getInvestAcount()));
      row.createCell(3).setCellValue(new HSSFRichTextString(tongJiModel.getInvestSumAmount()));
      row.createCell(4).setCellValue(new HSSFRichTextString(tongJiModel.getRegisteCount()));
    }

    FileOutputStream os = new FileOutputStream("/Users/pansen/Downloads/20160428.xls");
    wb.write(os);
    os.close();
  }
  public void testRemoveNewRow_bug46312() {
    // To make bug occur, rowIndex needs to be >= ValueRecordsAggregate.records.length
    int rowIndex = 30;

    ValueRecordsAggregate vra = new ValueRecordsAggregate();
    try {
      vra.removeAllCellsValuesForRow(rowIndex);
    } catch (IllegalArgumentException e) {
      if (e.getMessage().equals("Specified rowIndex 30 is outside the allowable range (0..30)")) {
        throw new AssertionFailedError("Identified bug 46312");
      }
      throw e;
    }

    if (false) { // same bug as demonstrated through usermodel API

      HSSFWorkbook wb = new HSSFWorkbook();
      HSSFSheet sheet = wb.createSheet();
      HSSFRow row = sheet.createRow(rowIndex);
      if (false) { // must not add any cells to the new row if we want to see the bug
        row.createCell(0); // this causes ValueRecordsAggregate.records to auto-extend
      }
      try {
        sheet.createRow(rowIndex);
      } catch (IllegalArgumentException e) {
        throw new AssertionFailedError("Identified bug 46312");
      }
    }
  }
Ejemplo n.º 8
0
  @SuppressWarnings("unchecked")
  @Override
  protected void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    resp.setContentType("application/vnd.ms-excel");
    resp.setHeader("Content-Disposition", "attachment;filename=Rezultati.xls");
    OutputStream out = resp.getOutputStream();

    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("results");

    HSSFRow headerRow = sheet.createRow(0);
    headerRow.createCell(0).setCellValue("Voting option");
    headerRow.createCell(1).setCellValue("Votes received");

    int t = 1;
    for (VotingResultEntry vre :
        (List<VotingResultEntry>) req.getSession().getAttribute("results")) {
      HSSFRow row = sheet.createRow(t++);
      row.createCell(0).setCellValue(vre.getName());
      row.createCell(1).setCellValue(vre.getVotes());
    }

    hwb.write(out);
    out.flush();
    out.close();
  }
Ejemplo n.º 9
0
  @Override
  public ModelAndView getReportTemplate() {
    List<MonthlyTraffic> monthlyTrafficReports = monthlyTraffictDAO.getMonthlyTraffic(monthDate);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Monthly Traffic Report");

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

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

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

    Map<String, HSSFWorkbook> parameterMap = new HashMap<String, HSSFWorkbook>();
    parameterMap.put("excelBook", workbook);
    ModelAndView modelAndView = new ModelAndView();
    modelAndView.addAllObjects(parameterMap);
    return modelAndView;
  }
Ejemplo n.º 10
0
 @SuppressWarnings("deprecation")
 public void Daochu() throws Exception {
   HSSFWorkbook wb = new HSSFWorkbook();
   HSSFSheet sheet = wb.createSheet("库存盘点表");
   HSSFRow row = sheet.createRow((int) 0);
   HSSFCellStyle style = wb.createCellStyle();
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   HSSFCell cell = row.createCell((short) 0);
   cell.setCellValue("快递编号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 1);
   cell.setCellValue("目的地");
   cell.setCellStyle(style);
   cell = row.createCell((short) 2);
   cell.setCellValue("入库日期");
   cell.setCellStyle(style);
   cell = row.createCell((short) 3);
   cell.setCellValue("区号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 4);
   cell.setCellValue("排号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 5);
   cell.setCellValue("架号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 6);
   cell.setCellValue("位号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 7);
   cell.setCellValue("中转中心");
   cell.setCellStyle(style);
   FileInputStream fis = new FileInputStream("src/main/java/data/save/instock.txt");
   ObjectInputStream ois = new ObjectInputStream(fis);
   @SuppressWarnings("unchecked")
   List<InStoringpo> list = (List<InStoringpo>) ois.readObject();
   ois.close();
   InStoringpo[] sp = new InStoringpo[list.size()];
   for (int i = 0; i < list.size(); i++) {
     row = sheet.createRow((int) i + 1);
     sp[i] = list.get(i);
     // 第四步,创建单元格,并设置值
     row.createCell((short) 0).setCellValue(sp[i].bianhao);
     row.createCell((short) 1).setCellValue(sp[i].destination);
     row.createCell((short) 2).setCellValue(sp[i].year + "-" + sp[i].month + "-" + sp[i].day);
     row.createCell((short) 3).setCellValue(sp[i].quhao);
     row.createCell((short) 4).setCellValue(sp[i].paihao);
     row.createCell((short) 5).setCellValue(sp[i].jiahao);
     row.createCell((short) 6).setCellValue(sp[i].weihao);
     row.createCell((short) 7).setCellValue(sp[i].zhongzhuan);
   }
   try {
     FileOutputStream fout = new FileOutputStream("C:/大作业代码/kucundaochu.xls");
     wb.write(fout);
     fout.close();
   } catch (Exception e) {
     e.printStackTrace();
   }
   System.out.println("导出成功!");
 }
Ejemplo n.º 11
0
  /**
   * to build the response as excel file.
   *
   * @see org.springframework.web.servlet.view.document.AbstractExcelView#
   *     buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook,
   *     javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
   */
  @SuppressWarnings(AppConstants.SUPPRESS_WARNINGS_UNCHECKED)
  @Override
  protected void buildExcelDocument(
      Map<String, Object> model,
      HSSFWorkbook myWorkBook,
      HttpServletRequest arg2,
      HttpServletResponse arg3)
      throws Exception {
    List<QuestionDTO> questionDTOs = (List<QuestionDTO>) model.get("questions");

    HSSFSheet questionsSheet = myWorkBook.createSheet(AppConstants.EXCEL_SHEET_NAME);

    HSSFRow headerRow = questionsSheet.createRow(0);

    HSSFCellStyle wrapCellStyle = myWorkBook.createCellStyle();
    wrapCellStyle.setWrapText(true);
    wrapCellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);

    createNewCell(headerRow, QUESTIONID_CELLNUM, "Question ID", wrapCellStyle);
    createNewCell(headerRow, QUESTION_CELLNUM, "Question", wrapCellStyle);
    createNewCell(headerRow, ANSWER_CELLNUM, "Answer", wrapCellStyle);
    createNewCell(
        headerRow, CATEGORIES_CELLNUM, "Categories (Category seperated by comma)", wrapCellStyle);

    HSSFRow dataRow = null;

    if (!CoreUtil.isEmpty(questionDTOs)) {
      int i = 0;
      for (QuestionDTO questionDTO : questionDTOs) {
        dataRow = questionsSheet.createRow(i + 1);

        createNewCell(dataRow, QUESTIONID_CELLNUM, Long.toString(questionDTO.getId()), null);

        createNewCell(dataRow, QUESTION_CELLNUM, questionDTO.getQuestion(), wrapCellStyle);

        createNewCell(dataRow, ANSWER_CELLNUM, questionDTO.getAnswer(), wrapCellStyle);

        createNewCell(
            dataRow,
            CATEGORIES_CELLNUM,
            parseCategoryDTOs(questionDTO.getCategoryDTOs()),
            wrapCellStyle);

        // Increment row
        i = i + 1;
      }
    }

    questionsSheet.autoSizeColumn(QUESTIONID_CELLNUM);
    questionsSheet.autoSizeColumn(QUESTION_CELLNUM);
    questionsSheet.autoSizeColumn(ANSWER_CELLNUM);
    questionsSheet.autoSizeColumn(CATEGORIES_CELLNUM);

    questionsSheet.setDefaultColumnStyle(QUESTION_CELLNUM, wrapCellStyle);
    questionsSheet.setDefaultColumnStyle(ANSWER_CELLNUM, wrapCellStyle);
    questionsSheet.setDefaultColumnStyle(CATEGORIES_CELLNUM, wrapCellStyle);
  }
  /**
   * Discription:[写入excel文件,可用于导出,修改excel文件.目前只产生xls文件,并不产生xlsx文件]
   *
   * @param sheetName 写入excel的sheet名称
   * @param dataSet 要写入文件的内容(一个集合). 集合中存放符合javaBean格式的对象。 支持数据类型有基本数据类型及String,Date,byte[](图片数据)
   * @param out 要写入的文件流, 可用于导出, 或者写入硬盘文件
   * @param map 要导出的字段。(格式:字段名 <-> 字段中文名)其中字段名应该是对应javaBean中的某属性。<br>
   *     若该字段为公式的话,必须符合一定的格式:公式标识符formula公式名称&计算起始单元格列&计算终止单元格列&计算单元格行&分隔符(,:)&操作符<br>
   *     如:SUM&A&C&2&,&- 它返回的公司是:SUM(A3,-C3)。具体参照: <code>com.integral.util.office.ExcelFormula
   *     </code>
   * @param dateFormat 日期格式(可选),默认:yyyy-MM-dd
   * @author:[代超]
   * @throws IOException
   * @throws Exception
   * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
   */
  @SuppressWarnings("deprecation")
  public void writExcelFile(
      String sheetName, Collection<T> dataSet, OutputStream out, Map map, String dateFormat)
      throws Exception {
    if (dataSet == null || dataSet.size() < 1) {
      return;
    }
    if (map == null || map.size() < 1) {
      return;
    }
    if (dateFormat == null || "".equals(dateFormat.trim())) {
      dateFormat = "yyyy-MM-dd";
    }
    sheetName = sheetName == null ? "" : sheetName;
    // 声明一个工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 生成一个表格
    HSSFSheet sheet = workbook.createSheet(sheetName);
    // 设置表格默认列宽度为15个字节
    sheet.setDefaultColumnWidth(15);
    // 设置表格的样式
    CellStyle headerStyle = setRootSheetSysle(workbook);

    // 产生表格标题行
    HSSFRow row = sheet.createRow(0);
    // 将第一行冻结
    sheet.createFreezePane(1, 1);

    if (map != null) {
      Object[] obj = map.values().toArray();
      for (int i = 0; i < obj.length; i++) {
        HSSFCell cell = row.createCell(i);
        String header = obj[i] == null ? "" : obj[i].toString();
        cell.setCellStyle(headerStyle);
        if (obj[i] != null && obj[i].toString().indexOf("formula") > -1) {
          // 公式
          header = obj[i].toString().replace("formula", "");
        }
        HSSFRichTextString text = new HSSFRichTextString(header);
        cell.setCellValue(text);
      }
    }
    // 遍历数据集合,产生数据行
    Iterator<T> it = dataSet.iterator();
    for (int i = 1; it.hasNext(); i++) {
      row = sheet.createRow(i);
      T t = it.next();
      // BeanMap bm = new BeanMap(t);
      Map b = PropertyUtils.describe(t);
      Map c = new TreeMap();
      // 排序
      c.putAll(b);
      writeRow(row, c, map, dateFormat, t);
    }
    workbook.write(out);
  }
Ejemplo n.º 13
0
  /**
   * @see javax.servlet.http.HttpServlet#doGet( javax.servlet.http.HttpServletRequest,
   *     javax.servlet.http.HttpServletResponse)
   *     <p>Method requires 3 parameters: a, b and n. It creates a Microsoft excel file with n
   *     sheets, each sheet containing 2 columns. 1st Column contains numbers from a to b and the
   *     2nd column consists of the 1st column number's power depending on the sheet number.
   */
  @Override
  protected void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {

    Integer a = null;
    Integer b = null;
    Integer n = null;

    try {
      a = Integer.valueOf(req.getParameter("a"));
    } catch (Exception e) {
      req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
    }
    if (a < -100 || a > 100) {
      req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
    }

    try {
      b = Integer.valueOf(req.getParameter("b"));
    } catch (Exception e) {
      req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
    }
    if (b < -100 || b > 100) {
      req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
    }

    try {
      n = Integer.valueOf(req.getParameter("n"));
    } catch (Exception e) {
      req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
    }
    if (n < 1 || n > 5) {
      req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp);
    }

    HSSFWorkbook hwb = new HSSFWorkbook();
    for (int i = 0; i < n; i++) {
      HSSFSheet sheet = hwb.createSheet("Sheet " + (i + 1));
      HSSFRow rowhead = sheet.createRow(0);
      rowhead.createCell(0).setCellValue("Value");
      rowhead.createCell(1).setCellValue("Power " + (i + 1));
      for (int j = a; j <= b; j++) {
        HSSFRow row = sheet.createRow(j - a + 1);
        row.createCell(0).setCellValue(j);
        row.createCell(1).setCellValue(Math.pow(j, i + 1));
      }
    }

    resp.setContentType("application/vnd.ms-excel; charset=UTF-8");
    resp.setHeader("Content-Disposition", "inline");

    ServletOutputStream fileOut = resp.getOutputStream();
    hwb.write(fileOut);
    fileOut.close();
  }
Ejemplo n.º 14
0
  public static boolean saveAsExcelFile(File file, List<StatementsEx> statementsExs) {
    final HSSFWorkbook wb = new HSSFWorkbook();
    boolean needToWrite = false;
    for (StatementsEx statementsEx : statementsExs) {
      final String title = statementsEx.title;
      final Statements statements = statementsEx.statements;
      assert (statements != null);
      if (statements.getType() == Statement.Type.Unknown) {
        continue;
      }
      needToWrite = true;
      final HSSFSheet sheet = wb.createSheet(title);
      // Do not obtain "type" through statements, as there is possible that
      // statements is empty.
      final List<String> strings =
          Statement.typeToStrings(statements.getType(), statements.getGUIBundleWrapper());
      final int columnCount = strings.size();
      // First row. Print out table header.
      {
        final HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < columnCount; i++) {
          row.createCell(i).setCellValue(new HSSFRichTextString(strings.get(i)));
        }
      }

      final int rowCount = statements.size();
      for (int i = 0; i < rowCount; i++) {
        final HSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < columnCount; j++) {
          // Value shouldn't be null, as we prevent atom with null value.
          final Object value = statements.get(i).getAtom(j).getValue();
          final HSSFCell cell = row.createCell(j);
          POIUtils.invokeSetCellValue(cell, value);
        }
      }
    }
    if (needToWrite == false) {
      return needToWrite;
    }
    boolean status = false;
    FileOutputStream fileOut = null;
    try {
      fileOut = new FileOutputStream(file);
      wb.write(fileOut);
      status = true;
    } catch (FileNotFoundException ex) {
      log.error(null, ex);
    } catch (IOException ex) {
      log.error(null, ex);
    } finally {
      org.yccheok.jstock.gui.Utils.close(fileOut);
    }
    return status;
  }
Ejemplo n.º 15
0
  /**
   * @param excel_name 生成的Excel文件路径+名称
   * @param headList Excel文件Head标题集合
   * @param field_list Excel文件Field标题集合
   * @param dataList Excel文件数据内容部分
   * @throws Exception
   */
  public static void createExcel(
      String excel_name,
      List<String> headList,
      List<String> fieldList,
      List<Map<String, Object>> dataList)
      throws Exception {
    // 创建新的Excel 工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();

    // 在Excel工作簿中建一工作表,其名为缺省值
    // 如要新建一名为"效益指标"的工作表,其语句为:
    // HSSFSheet sheet = workbook.createSheet("效益指标");
    HSSFSheet sheet = workbook.createSheet();
    // 在索引0的位置创建行(最顶端的行)
    HSSFRow row = sheet.createRow(0);
    // ===============================================================
    for (int i = 0; i < headList.size(); i++) {

      // 在索引0的位置创建单元格(左上端)
      HSSFCell cell = row.createCell(i);
      // 定义单元格为字符串类型
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      // 在单元格中输入一些内容
      cell.setCellValue(headList.get(i));
    }
    // ===============================================================

    for (int n = 0; n < dataList.size(); n++) {
      // 在索引1的位置创建行(最顶端的行)
      HSSFRow row_value = sheet.createRow(n + 1);
      Map<String, Object> dataMap = dataList.get(n);
      // ===============================================================
      for (int i = 0; i < fieldList.size(); i++) {

        // 在索引0的位置创建单元格(左上端)
        HSSFCell cell = row_value.createCell(i);
        // 定义单元格为字符串类型
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        // 在单元格中输入一些内容
        cell.setCellValue(objToString(dataMap.get(fieldList.get(i))));
      }
      // ===============================================================
    }

    // 新建一输出文件流
    FileOutputStream fOut = new FileOutputStream(excel_name);
    // 把相应的Excel 工作簿存盘
    workbook.write(fOut);
    fOut.flush();
    // 操作结束,关闭文件
    fOut.close();
    // System.out.println("[" + excel_name + "]" + "文件生成...");
  }
  public static void writeDataToExcelFile(
      final String commaDelimitedHeaderColumns,
      final String commaDelimitedModelAttributes,
      final List<?> modelList,
      final String fileName) {

    FileOutputStream out = null;
    try {
      String[] headerColumns = commaDelimitedHeaderColumns.split(COMMA);
      String[] modelAttributes = commaDelimitedModelAttributes.split(COMMA);
      HSSFWorkbook excelWorkBook = new HSSFWorkbook();
      Map<String, CellStyle> styles = createStyles(excelWorkBook);
      HSSFSheet excelSheet = excelWorkBook.createSheet();
      HSSFRow excelHeaderRow;
      HSSFCell excelHeaderCell;
      HSSFRow excelRow;
      HSSFCell excelCell;

      excelHeaderRow = excelSheet.createRow(0);
      for (int columnIndex = 0; columnIndex < headerColumns.length; columnIndex++) {
        excelHeaderCell = excelHeaderRow.createCell(columnIndex);
        excelHeaderCell.setCellValue(new HSSFRichTextString(headerColumns[columnIndex]));
        excelHeaderCell.setCellStyle(styles.get("header"));
      }

      if (TGPObjectUtil.isDefined(modelList)) {
        for (int rowIndex = 0; rowIndex < modelList.size(); rowIndex++) {
          excelRow = excelSheet.createRow(rowIndex + 1);
          for (int columnIndex = 0; columnIndex < modelAttributes.length; columnIndex++) {
            excelCell = excelRow.createCell(columnIndex);
            Object object = modelList.get(rowIndex);
            Class<?> objectClass = object.getClass();
            Field objectField = objectClass.getDeclaredField(modelAttributes[columnIndex]);
            objectField.setAccessible(true);
            excelCell.setCellValue(new HSSFRichTextString((String) objectField.get(object)));
          }
        }
      }

      for (int i = TGPProcurementConstants.TGP_INTEGER_ZERO; i < headerColumns.length; i++) {
        excelSheet.autoSizeColumn(i);
      }
      out = new FileOutputStream(fileName);
      excelWorkBook.write(out);

    } catch (Exception e) {
      LOGGER.error(e.toString());
    } finally {
      IOUtils.closeQuietly(out);
    }
  }
Ejemplo n.º 17
0
  /**
   * 生成文件
   *
   * @throws IOException
   */
  public byte[] run() throws IOException {
    // 初始化参数
    initParameter();

    // 实例化decorator
    initDecorator();

    // 初始化
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度

    try {
      wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);
    } catch (Exception ex) {
      logger.error("生成表单时出错:怀疑是表单名导致。说明:poi对中文的支持不好,可能会有问题(仅表单名)如中文?--中文字加英文标点的情况");
    }

    baseCellStyle = wb.createCellStyle();
    baseCellStyle.setAlignment(align);
    baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

    dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat));

    // 创建标题行
    HSSFRow row = sheet.createRow((short) 0);

    // 创建标题
    for (int i = 0; i < nameList.size(); i++) {
      HSSFCell cell = row.createCell((short) i);
      writeCell(cell, (String) nameList.get(i));
    }

    // 创建数据
    for (int i = 0; i < dataList.size(); i++) {
      row = sheet.createRow((short) i + 1);
      for (int j = 0; j < fieldList.size(); j++) {
        HSSFCell cell = row.createCell((short) j);
        Object value = getValue(dataList.get(i), (String) fieldList.get(j));
        writeCell(cell, value);
      }
    }

    // 写入文件
    // FileOutputStream fileOut = new FileOutputStream(fileName);
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    wb.write(out);
    return out.toByteArray();
    // fileOut.close();
  }
  @Override
  protected void buildExcelDocument(
      Map<String, Object> model,
      HSSFWorkbook workbook,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {
    /*
     * if (workbook.getNumberOfSheets() == 0) { setUrl("/resources/xls/Product_Locator_API_Usage_Report"); renderMergedOutputModel(model, request, response); } else {
     */
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFCellStyle ordinStyle = workbook.createCellStyle();
    ordinStyle.setBorderRight(CellStyle.BORDER_THIN);
    ordinStyle.setBorderLeft(CellStyle.BORDER_THIN);

    HSSFCellStyle lineStyle = workbook.createCellStyle();
    lineStyle.setBorderTop(CellStyle.BORDER_THIN);

    short currentRow = 1;
    Report report = (Report) model.get(SEARCH_LIST_KEY);
    if (report != null) {
      List<ReportAPIUsage> list = report.getRepotAPIUsageList();
      HSSFRow row = sheet.getRow(0);
      row.createCell(0)
          .setCellValue("Date Range: " + report.getDateFrom() + " - " + report.getDateTo());
      for (ReportAPIUsage object : list) {
        currentRow++;
        row = sheet.createRow(currentRow);
        HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN);
        cellBrName.setCellValue(object.getClientKey());
        cellBrName.setCellStyle(ordinStyle);
        HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN);
        cellAnyPac.setCellValue(object.getClientName());
        cellAnyPac.setCellStyle(ordinStyle);
        HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN);
        cellSpecPac.setCellValue(object.getCountOfQueries());
        cellSpecPac.setCellStyle(ordinStyle);
      }
      currentRow++;
      row = sheet.createRow(currentRow);
      HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN);
      cellBrName.setCellStyle(lineStyle);
      HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN);
      cellAnyPac.setCellStyle(lineStyle);
      HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN);
      cellSpecPac.setCellStyle(lineStyle);
    }
    /* } */

  }
Ejemplo n.º 19
0
 private HSSFRow createRow(HSSFSheet sheet, int rownum) {
   HSSFRow newRow = sheet.createRow(rownum);
   if (rownum == 0) {
     newRow.setRowStyle(newStyle);
   }
   return newRow;
 }
Ejemplo n.º 20
0
  public Boolean insertRow(int startRow, int rows) {
    Boolean flag = false;
    sheet.shiftRows(startRow + 1, sheet.getLastRowNum(), rows, true, false);
    startRow = startRow - 1;

    for (int i = 0; i < rows; i++) {

      HSSFRow sourceRow = null;
      HSSFRow targetRow = null;
      HSSFCell sourceCell = null;
      HSSFCell targetCell = null;
      short m;

      startRow = startRow + 1;
      sourceRow = sheet.getRow(startRow);
      targetRow = sheet.createRow(startRow + 1);
      targetRow.setHeight(sourceRow.getHeight());

      for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {

        sourceCell = sourceRow.getCell(m);
        targetCell = targetRow.createCell(m);

        // targetCell.setEncoding(sourceCell.get);
        targetCell.setCellStyle(sourceCell.getCellStyle());
        targetCell.setCellType(sourceCell.getCellType());
      }
    }
    flag = true;
    return flag;
  }
Ejemplo n.º 21
0
 private HSSFRow getRow(int rowIndex) {
   HSSFRow row = sheet.getRow(rowIndex);
   if (row == null) {
     row = sheet.createRow(rowIndex);
   }
   return row;
 }
Ejemplo n.º 22
0
 @Override
 public void writeRow(List<?> row) {
   Row record = sheet.createRow(sheet.getLastRowNum() + 1);
   for (int i = 0; i < row.size(); i++) {
     Cell cell = record.createCell(i);
     Object value = row.get(i);
     if (value == null) {
       cell.setCellValue("");
     } else if (value instanceof String) {
       cell.setCellType(Cell.CELL_TYPE_STRING);
       cell.setCellValue((String) value);
     } else if (value instanceof Number) {
       cell.setCellType(Cell.CELL_TYPE_NUMERIC);
       cell.setCellValue(converters.convert(value, Double.class));
     } else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
       cell.setCellType(Cell.CELL_TYPE_NUMERIC);
       cell.setCellStyle(dateCellStyle);
       cell.setCellValue(converters.convert(value, Date.class));
     } else if (value instanceof Boolean) {
       cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
       cell.setCellValue((Boolean) value);
     } else {
       cell.setCellType(Cell.CELL_TYPE_STRING);
       cell.setCellValue(converters.convert(value, String.class));
     }
   }
 }
Ejemplo n.º 23
0
 /**
  * 业务数据类型生成
  *
  * @param wb excel工作页
  * @param btList 模型对象数组
  */
 public void BusTypeWriter(HSSFWorkbook wb, List<PDMBusinessDataType> btList) {
   HSSFSheet sheet = wb.getSheet(BT_SHEET);
   for (int i = 0; i < btList.size(); i++) {
     HSSFRow row = sheet.createRow(i + 2);
     PDMBusinessDataType bt = btList.get(i);
     // 1
     HSSFCell cell = row.createCell(1);
     cell.setCellValue(bt.getTypeName());
     cell.setCellStyle(getTextStyle(wb));
     // 2
     cell = row.createCell(2);
     cell.setCellValue(bt.getTypeChineseName());
     cell.setCellStyle(getTextStyle(wb));
     // 3
     cell = row.createCell(3);
     cell.setCellValue(bt.getStandardTypeName());
     cell.setCellStyle(getTextStyle(wb));
     // 4
     cell = row.createCell(4);
     cell.setCellValue(bt.getLength());
     cell.setCellStyle(getTextStyle(wb));
     // 5
     cell = row.createCell(5);
     cell.setCellValue(bt.getPrecision());
     cell.setCellStyle(getTextStyle(wb));
     // 6
     cell = row.createCell(6);
     cell.setCellValue(bt.getDefaultValue());
     cell.setCellStyle(getTextStyle(wb));
     // 7
     cell = row.createCell(7);
     cell.setCellValue(bt.getComment());
     cell.setCellStyle(getTextStyle(wb));
   }
 }
Ejemplo n.º 24
0
 /** Print header */
 private void printHeader(HSSFSheet sheet) {
   HSSFRow row = sheet.createRow(0);
   row.createCell(0).setCellValue(MEASURE);
   row.createCell(1).setCellValue(TYPE);
   row.createCell(2).setCellValue(DESCRIPTION);
   row.createCell(3).setCellValue(VALUE);
 }
 private void addOrderHeader(final HSSFSheet sheet, final Locale locale) {
   HSSFRow header = sheet.createRow(0);
   HSSFCell cell0 = header.createCell(0);
   cell0.setCellValue(
       translationService.translate(
           "qualityControls.qualityControl.report.product.number", locale));
   xlsHelper.setCellStyle(sheet, cell0);
   HSSFCell cell1 = header.createCell(1);
   cell1.setCellValue(
       translationService.translate(
           "qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.number.label",
           locale));
   xlsHelper.setCellStyle(sheet, cell1);
   HSSFCell cell2 = header.createCell(2);
   cell2.setCellValue(
       translationService.translate(
           "qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.controlledQuantity.label",
           locale));
   xlsHelper.setCellStyle(sheet, cell2);
   HSSFCell cell3 = header.createCell(3);
   cell3.setCellValue(
       translationService.translate(
           "qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.rejectedQuantity.label",
           locale));
   xlsHelper.setCellStyle(sheet, cell3);
   HSSFCell cell4 = header.createCell(4);
   cell4.setCellValue(
       translationService.translate(
           "qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.acceptedDefectsQuantity.label",
           locale));
   xlsHelper.setCellStyle(sheet, cell4);
 }
Ejemplo n.º 26
0
  public void body(TableModel model, Column column) {
    if (column.isFirstColumn()) {
      rownum++;
      cellnum = 0;
      hssfRow = sheet.createRow(rownum);
    }
    String value = column.getCellDisplay();
    if (StringUtils.isNumeric(value)) {
      value = column.getValue() == null ? "" : column.getValue().toString();
    }
    value = value.replaceAll("\t", "").replaceAll("\n", "");
    value = ExportViewUtils.parseXLS(value);

    HSSFCell hssfCell = hssfRow.createCell(cellnum);

    // modify by springside
    // hssfCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    // end of modify by springside

    if (column.isEscapeAutoFormat()) {
      writeToCellAsText(hssfCell, value);
    } else {
      writeToCellFormatted(hssfCell, value);
    }
    cellnum++;
  }
Ejemplo n.º 27
0
  /**
   * @param
   * @return void
   */
  private void generateColumn(
      HSSFSheet sheet,
      TableColumn tc,
      int maxlevel,
      int rownum,
      int colnum,
      HSSFCellStyle headerstyle) {
    HSSFRow row = sheet.getRow(rownum);
    if (row == null) row = sheet.createRow(rownum);

    HSSFCell cell = row.createCell(colnum);
    cell.setCellValue(tc.getDisplay());

    if (headerstyle != null) cell.setCellStyle(headerstyle);
    if (tc.isComplex()) {
      CellRangeAddress address =
          new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1);
      sheet.addMergedRegion(address);
      fillMergedRegion(sheet, address, headerstyle);

      int cn = colnum;
      for (int i = 0; i < tc.getChildren().size(); i++) {
        if (i != 0) {
          cn = cn + tc.getChildren().get(i - 1).getLength();
        }
        generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle);
      }
    } else {
      CellRangeAddress address =
          new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum);
      sheet.addMergedRegion(address);
      fillMergedRegion(sheet, address, headerstyle);
    }
    sheet.autoSizeColumn(colnum, true);
  }
Ejemplo n.º 28
0
 private void writeData2Excel(HSSFSheet sheet, HSSFCellStyle dataStyle, HSSFCellStyle headerStyle)
     throws ContainerException {
   RowSet searchResult = executeFrm.getSearchResult();
   if (searchResult != null && !searchResult.isEmpty()) {
     List<ReportViewFrm> definedViews = executeFrm.getDefinedViews();
     int fieldCount = definedViews.size();
     int dataCount = searchResult.getSize();
     int xlsRowNum = 0;
     for (int i = 0; i < dataCount; i++) {
       Row row = searchResult.getRow(i);
       xlsRowNum = i;
       if (isFirstFile) {
         xlsRowNum++;
       }
       HSSFRow xlsRow = sheet.createRow(xlsRowNum);
       xlsRow.setHeightInPoints(18);
       for (short j = 0; j < fieldCount; j++) {
         ReportViewFrm viewFrm = definedViews.get(j);
         String fieldName = viewFrm.getFieldName();
         HSSFCell xlsCell = xlsRow.createCell(j);
         xlsCell.setCellStyle(dataStyle);
         HSSFRichTextString richText = new HSSFRichTextString(row.getStrValue(fieldName));
         xlsCell.setCellValue(richText);
       }
     }
   }
 }
Ejemplo n.º 29
0
 private void writeHeaderRow(Entity entity, HSSFSheet sheet) {
   HSSFRow headerRow = sheet.createRow(0);
   int colnum = 0;
   for (Map.Entry<String, Object> component : getComponents(entity)) {
     String componentName = component.getKey();
     headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName));
     ComponentDescriptor cd = entity.descriptor().getComponent(componentName);
     PrimitiveType primitiveType;
     if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor)
       primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType();
     else
       throw new UnsupportedOperationException(
           "Can only export simple type attributes, "
               + "failed to export "
               + entity.type()
               + '.'
               + cd.getName());
     Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class);
     String formatString = null;
     if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern();
     else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern();
     else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern();
     else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern();
     else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern();
     if (formatString != null) {
       HSSFDataFormat dataFormat = workbook.createDataFormat();
       CellStyle columnStyle = workbook.createCellStyle();
       columnStyle.setDataFormat(dataFormat.getFormat(formatString));
       sheet.setDefaultColumnStyle(colnum, columnStyle);
     }
     colnum++;
   }
 }
Ejemplo n.º 30
0
  public void gerar(String caminho, int qtdLinhas, int qtdColunas) throws IOException {

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet1 = wb.createSheet("planilha um");

    for (int i = 0; i < qtdLinhas; i++) {

      HSSFRow row = sheet1.createRow(i);

      HSSFCellStyle style = wb.createCellStyle();
      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);

      for (int j = 0; j < qtdColunas; j++) {

        HSSFRichTextString strValor = new HSSFRichTextString("Linha: " + i + " Coluna " + j);
        HSSFCell cell = row.createCell(j);
        cell.setCellStyle(style);
        cell.setCellValue(strValor);
      }
    }

    FileOutputStream stream = new FileOutputStream(caminho + "/planilha1.xls");

    wb.write(stream);
  }