private File writeSingleXLS() throws ReportException {
   File file = null;
   HSSFWorkbook wb = null;
   OutputStream out = null;
   try {
     file = getReportFile();
     wb = new HSSFWorkbook();
     ReportDefineFrm definedReport = executeFrm.getDefinedReport();
     String sheetName = definedReport.getReportName();
     HSSFSheet sheet = wb.createSheet(sheetName);
     sheet.setDisplayGridlines(false); // 不显示网格线
     HSSFCellStyle dataStyle = createDataStyle(wb);
     HSSFCellStyle headerStyle = createHeaderStyle(wb);
     if (isFirstFile) {
       writeDataHeader(sheet, headerStyle);
     }
     writeData2Excel(sheet, dataStyle, headerStyle);
     autoReSizeSheet(sheet);
     out = new FileOutputStream(file);
     wb.write(out);
     isFirstFile = false;
   } catch (Throwable ex) {
     Logger.logError(ex);
     throw new ReportException(ex.getMessage());
   } finally {
     try {
       if (out != null) {
         out.close();
       }
     } catch (IOException ex) {
       Logger.logError(ex);
     }
   }
   return file;
 }
  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
  }
Beispiel #3
1
  /**
   * ************************************************************************* Public methods
   * ************************************************************************
   */
  @Override
  public void open(String filepath) throws Exception {

    this.filepath = filepath;
    String author = ini.getValue("Excel", "Author", "Toël Hartmann");
    String keywords = ini.getValue("Excel", "Keywords", "");

    wb = new HSSFWorkbook();
    // Set some properties
    wb.createInformationProperties();
    wb.getSummaryInformation().setAuthor(author);
    wb.getSummaryInformation().setKeywords(keywords);
    wb.getSummaryInformation().setCreateDateTime(new Date());

    // Create the cell style for column titles
    titleStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    titleStyle.setFont(font);

    String sheetName = getConfigValue("Excel", "sheet", null);
    if (sheetName == null || sheetName.isEmpty())
      sheetName = FileUtils.getFileNameWithoutExtention(filepath);
    sheet = wb.createSheet(sheetName);
    if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) {
      sheet.createFreezePane(0, 1);
    }
  }
  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();
  }
 public void doService() throws Exception {
   // String filePath = "D:\\home\\filsoft\\financelease\\birtReport" +
   String filePath =
       "\\\\"
           + LeaseUtil.getIPAddress()
           + "\\home\\filsoft\\financelease\\birtReport"
           + File.separator
           + "待补-逾期"
           + File.separator
           + DateUtil.dateToString(new Date(), "yyyy_MM_dd");
   String fileName = "待补-逾期情况表.xls";
   OutputStream out = null;
   HSSFWorkbook wb = null;
   Map<String, Object> paramMap = new HashMap<String, Object>();
   try {
     ReportDateTo reportDate = ReportDateUtil.getDateByDate(DateUtil.dateToStr(new Date()));
     if (reportDate.getMonth() == 1) {
       reportDate.setMonth((short) 12);
       reportDate.setYear(reportDate.getYear() - 1);
     } else {
       reportDate.setMonth((short) (reportDate.getMonth() - 1));
     }
     reportDate =
         ReportDateUtil.getDateByYearAndMonth(reportDate.getYear(), reportDate.getMonth());
     File path = new File(filePath);
     path.mkdirs();
     File f = new File(path, fileName);
     wb = new HSSFWorkbook();
     // sheet1
     HSSFSheet sheet1 = wb.createSheet("当月拨款案件待补情况");
     List<Map<String, Object>> data4Of1 =
         (List<Map<String, Object>>) this.queryForList("job.getDataForHr_lack", reportDate);
     this.drawSheet(sheet1, data4Of1, getHeadByHardcode1());
     // sheet2
     HSSFSheet sheet2 = wb.createSheet("当月新增逾期91天以上案件");
     List<Map<String, Object>> data4Of2 =
         (List<Map<String, Object>>) this.queryForList("job.getDataForHr_dun", reportDate);
     this.drawSheet(sheet2, data4Of2, getHeadByHardcode2());
     out = new FileOutputStream(f);
     wb.write(out);
     // 发送Email
     MailSettingTo mailSettingTo = new MailSettingTo();
     mailSettingTo.setEmailAttachPath(f.getPath());
     mailUtilService.sendMail(132, mailSettingTo);
   } catch (Exception e) {
     e.printStackTrace();
     throw e;
   } finally {
     if (out != null) {
       out.flush();
       out.close();
     }
   }
 }
Beispiel #6
0
  @Override
  public void setAnchor(Object anchor) {
    if (anchor instanceof String) {
      sheet = workbook.getSheet((String) anchor);
      if (sheet == null) sheet = workbook.createSheet((String) anchor);
    } else if (anchor instanceof Integer) {
      sheet = workbook.getSheetAt((Integer) anchor);
      if (sheet == null) sheet = workbook.createSheet();
    }

    this.anchor = anchor;
  }
  @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;
  }
  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);
  }
  public void testShapeIds() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet();
    HSSFPatriarch patriarch1 = sheet1.createDrawingPatriarch();
    for (int i = 0; i < 2; i++) {
      patriarch1.createSimpleShape(new HSSFClientAnchor());
    }

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet1 = wb.getSheetAt(0);
    patriarch1 = sheet1.getDrawingPatriarch();

    EscherAggregate agg1 = HSSFTestHelper.getEscherAggregate(patriarch1);
    // last shape ID cached in EscherDgRecord
    EscherDgRecord dg1 = agg1.getEscherContainer().getChildById(EscherDgRecord.RECORD_ID);
    assertEquals(1026, dg1.getLastMSOSPID());

    // iterate over shapes and check shapeId
    EscherContainerRecord spgrContainer = agg1.getEscherContainer().getChildContainers().get(0);
    // root spContainer + 2 spContainers for shapes
    assertEquals(3, spgrContainer.getChildRecords().size());

    EscherSpRecord sp0 =
        ((EscherContainerRecord) spgrContainer.getChild(0)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1024, sp0.getShapeId());

    EscherSpRecord sp1 =
        ((EscherContainerRecord) spgrContainer.getChild(1)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1025, sp1.getShapeId());

    EscherSpRecord sp2 =
        ((EscherContainerRecord) spgrContainer.getChild(2)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1026, sp2.getShapeId());
  }
  public void beforeBody(TableModel model) {
    logger.debug("XlsView.init()");

    moneyFormat =
        model
            .getPreferences()
            .getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.money");
    if (StringUtils.isEmpty(moneyFormat)) {
      moneyFormat = DEFAULT_MONEY_FORMAT;
    }
    percentFormat =
        model
            .getPreferences()
            .getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.percent");
    if (StringUtils.isEmpty(percentFormat)) {
      percentFormat = DEFAULT_PERCENT_FORMAT;
    }

    wb = new HSSFWorkbook();
    sheet = wb.createSheet();
    wb.setSheetName(0, "Export Workbook");

    styles = initStyles(wb);
    ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);
    ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);

    createHeader(model);
  }
  public void addSheet(String name) {
    if (name.length() > 30) {
      name = name.substring(0, 25);
    }

    boolean found = false;

    String tempName = name.replace("/", "-");
    int i = 0;
    while (true) {

      if (i > 1000) {
        break;
      }

      for (int j = 0; j < workbook.getNumberOfSheets(); j++) {
        found = workbook.getSheetName(j).equalsIgnoreCase(tempName);
        if (found) {
          break;
        }
      }

      if (!found) {
        break;
      } else {
        i++;
        tempName = name + " (" + i + ")";
      }
    }

    activeSheet = workbook.createSheet(tempName);

    nextRowId = 0;
  }
  public void testCreateCFRuleRecord() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    CFRuleRecord record = CFRuleRecord.create(sheet, "7");
    testCFRuleRecord(record);

    // Serialize
    byte[] serializedRecord = record.serialize();

    // Strip header
    byte[] recordData = new byte[serializedRecord.length - 4];
    System.arraycopy(serializedRecord, 4, recordData, 0, recordData.length);

    // Deserialize
    record = new CFRuleRecord(TestcaseRecordInputStream.create(CFRuleRecord.sid, recordData));

    // Serialize again
    byte[] output = record.serialize();

    // Compare
    assertEquals("Output size", recordData.length + 4, output.length); // includes sid+recordlength

    for (int i = 0; i < recordData.length; i++) {
      assertEquals("CFRuleRecord doesn't match", recordData[i], output[i + 4]);
    }
  }
Beispiel #13
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();
  }
Beispiel #14
0
 /** @Description: 创建所有的Sheet */
 private static HSSFSheet[] getSheets(int num, String[] names) {
   HSSFSheet[] sheets = new HSSFSheet[num];
   for (int i = 0; i < num; i++) {
     sheets[i] = wb.createSheet(names[i]);
   }
   return sheets;
 }
Beispiel #15
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();
  }
Beispiel #16
0
  public void testClearShapesForPatriarch() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    patriarch.createSimpleShape(new HSSFClientAnchor());
    patriarch.createSimpleShape(new HSSFClientAnchor());
    patriarch.createCellComment(new HSSFClientAnchor());

    EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);

    assertEquals(agg.getShapeToObjMapping().size(), 6);
    assertEquals(agg.getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);

    patriarch.clear();

    assertEquals(agg.getShapeToObjMapping().size(), 0);
    assertEquals(agg.getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(agg.getShapeToObjMapping().size(), 0);
    assertEquals(agg.getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);
  }
Beispiel #17
0
  public void testRotation() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    HSSFSimpleShape rectangle =
        patriarch.createSimpleShape(
            new HSSFClientAnchor(0, 0, 100, 100, (short) 0, 0, (short) 5, 5));
    rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);

    assertEquals(rectangle.getRotationDegree(), 0);
    rectangle.setRotationDegree((short) 45);
    assertEquals(rectangle.getRotationDegree(), 45);
    rectangle.setFlipHorizontal(true);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
    assertEquals(rectangle.getRotationDegree(), 45);
    rectangle.setRotationDegree((short) 30);
    assertEquals(rectangle.getRotationDegree(), 30);

    patriarch.setCoordinates(0, 0, 10, 10);
    rectangle.setString(new HSSFRichTextString("1234"));
  }
Beispiel #18
0
  public void exportXLS(HttpServletResponse response) {

    // 1.创建一个 workbook
    HSSFWorkbook workbook = new HSSFWorkbook();

    // 2.创建一个 worksheet
    HSSFSheet worksheet = workbook.createSheet("数据统计报表");

    // 3.定义起始行和列
    int startRowIndex = 0;
    int startColIndex = 0;

    // 4.创建title,data,headers

    // 5.填充数据

    // 6.设置reponse参数
    String fileName = "Report.xls";
    response.setHeader("Content-Disposition", "inline; filename=" + fileName);
    // 确保发送的当前文本格式
    response.setContentType("application/vnd.ms-excel");

    // 7. 输出流

  }
  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");
      }
    }
  }
Beispiel #20
0
  /**
   * @param SheetName excel里面的sheet的名字 (可以任意写,一般和excelname一样)
   * @param response 要写的流
   * @param HeadName 数据的表头 即列名
   * @param data 数据 二维数组
   * @return
   * @throws IOException
   */
  public static OutputStream creatExcel(
      String SheetName, File file, List<String> HeadName, String[][] data, Integer[] ColumnWidth)
      throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet(SheetName);
    CellStyle s = new CellStyle();

    Font f12 = new Font();
    HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12);
    HSSFCellStyle style = ExcelUtil.getNewStyle(workbook, s, font12);

    // sheet.setDefaultColumnWidth(50);

    sheet.setDefaultRowHeight((short) 400);
    sheet.setColumnWidth(0, 18 * 256);
    OutputStream os = new FileOutputStream(file);
    // 设置excel的表头
    for (int i = 0; i < HeadName.size(); i++) {
      ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style);
    }
    // 设置excel数据
    for (int i = 0; i < data.length; i++) {
      for (int j = 0; j < data[i].length; j++) {
        ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style);
      }
    }
    // 设置excel 表格的列宽
    for (int i = 0; i < HeadName.size(); i++) {
      sheet.setColumnWidth(i, ColumnWidth[i]);
    }
    workbook.write(os);
    return os;
  }
Beispiel #21
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("导出成功!");
 }
 /** Sets up a test file */
 private ByteArrayOutputStream setupRunFile(HSSFWorkbook book) throws Exception {
   ByteArrayOutputStream stream = new ByteArrayOutputStream();
   HSSFSheet sheet = book.createSheet("Test");
   HSSFRow row = sheet.createRow(0);
   HSSFCell cell = row.createCell(0);
   cell.setCellValue(10.5);
   book.write(stream);
   return stream;
 }
Beispiel #23
0
  private void initialize(Map<String, ?> cfg) {
    workbook = new HSSFWorkbook();

    if (cfg.containsKey("header")) header = (List<String>) cfg.get("header");
    if (cfg.containsKey("type")) type = (List<Class>) cfg.get("type");
    if (cfg.containsKey("anchor")) anchor = cfg.get("anchor");

    if (anchor instanceof String) {
      sheet = workbook.createSheet((String) anchor);
    } else {
      sheet = workbook.createSheet();
    }
    if (cfg.containsKey("dateTimePattern")) {
      setDateTimePattern((String) cfg.get("dateTimePattern"));
    } else {
      setDateTimePattern(Environment.getDateTimePattern());
    }
  }
  /**
   * 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);
  }
  public void run() {
    int pagesize = PAGESIZE;
    try {
      while (!m_spool.isHasError() && !m_spool.isEmpty()) {
        if (linecounter >= pagesize) {
          // 处理组合单元格
          if (!mergeCells.isEmpty()) {
            Region[] regs = new Region[mergeCells.size()];
            mergeCells.values().toArray(regs);
            int len = regs.length;
            for (int i = 0; i < len; i++) m_sheet.addMergedRegion(regs[i]);
          }
          try {
            flushToFile();
          } catch (Exception e) {
            Logger.error(e.getMessage(), e);
            m_spool.setErrorMsg(e.getMessage());
          } finally {
            if (out != null)
              try {
                out.close();
              } catch (IOException e) {
                Logger.error(e.getMessage(), e);
              }
          }

          m_workbook = new HSSFWorkbook(); // 创建工作薄
          m_sheet = m_workbook.createSheet(); // 创建表单
          mergeCells = new Hashtable(); // 存放所有组合单元格
          styles = new Hashtable();
          linecounter = 0;
          sheetCounter++;
        }

        PrintCellData[] data = null;
        data = (PrintCellData[]) m_spool.retrieve();
        linecounter = linecounter + data.length; // 其实为单元格数目,非行数

        fileUtil.exportSheet2(m_templateData, data, m_sheet, m_workbook, styles, mergeCells);

        /* 每往Excel写入一行,进度条+1,已导出的行数+1 */
        m_spool.setExportedLineCount(m_spool.getExportedLineCount() + 1);
        stepProgressBar(1);
        setProgressBarHintMsg();
      }
    } catch (Exception e) {
      Logger.error(e.getMessage(), e);
      m_spool.setErrorMsg(e.getMessage());
    } catch (OutOfMemoryError e) {
      Logger.error(e.getMessage(), e);
      m_spool.setErrorMsg(
          nc.ui.ml.NCLangRes.getInstance()
              .getStrByID(
                  "10100108", "UPP10100108-000860") /*@res "内存不足,请修改Java Plugin参数,将内存调整大一些"*/);
    }
  }
Beispiel #27
0
  @Override
  public void buildExcelWorkBook(HSSFWorkbook workBook, Map<String, Object> map) {
    Sheet sheet = workBook.createSheet();
    generateHeaderRow(workBook, sheet, getColumnConfig());

    if (getDataJSONArray() != null && getDataJSONArray().length() > 0) {
      generateDatasRowByArray(sheet);
      return;
    }
  }
  private static void populateWorkbook(NodeList sheetList, Map cellStyles, HSSFWorkbook workbook)
      throws ExcelTransformerException {

    if (LOG.isLoggable(Level.FINE)) {
      LOG.entering(
          SimpleExcelRenderer.class.getName(),
          "populateWorkbook",
          String.valueOf(sheetList.getLength()));
    }

    for (int k = 0; k < sheetList.getLength(); k++) {
      Element sheet = (Element) sheetList.item(k);
      NodeList rowList = sheet.getElementsByTagName("row");

      if (rowList.getLength() > 0) {
        HSSFSheet hSheet = workbook.createSheet(sheet.getAttribute("name"));

        for (int i = 0; i < rowList.getLength(); i++) {
          Element row = (Element) rowList.item(i);
          HSSFRow hRow = hSheet.createRow(i);

          short cellCounter = 0;
          NodeList cells = row.getChildNodes();
          for (short j = 0; j < cells.getLength(); j++) {

            Node cell = cells.item(j);
            if (cell.getNodeType() == Node.ELEMENT_NODE && cell.getNodeName().equals("cell")) {
              Element cellE = (Element) cell;
              String value = XmlUtils.getElementText(cellE);
              String style = cellE.getAttribute("style");
              String colSpan = cellE.getAttribute("colspan");
              int colSpanI = 0;

              if (Utils.hasContent(colSpan)) {
                colSpanI = Integer.parseInt(colSpan);
                hSheet.addMergedRegion(
                    new Region(i, cellCounter, i, (short) (cellCounter + colSpanI - 1)));
              }

              HSSFCell hCell = hRow.createCell(cellCounter);
              if (Utils.hasContent(style))
                hCell.setCellStyle((HSSFCellStyle) cellStyles.get(style));

              hCell.setCellValue(value);
              cellCounter++;
            }
          }
        }
      }
    }

    if (LOG.isLoggable(Level.FINE)) {
      LOG.exiting(SimpleExcelRenderer.class.getName(), "populateWorkbook");
    }
  }
Beispiel #29
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();
  }
Beispiel #30
-1
  public void testCorrectOrderInOptRecord() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();

    try {
      HSSFSheet sheet = wb.createSheet();
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

      HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
      EscherOptRecord opt = HSSFTestHelper.getOptRecord(textbox);

      String opt1Str = opt.toXml();

      textbox.setFillColor(textbox.getFillColor());
      EscherContainerRecord container = HSSFTestHelper.getEscherContainer(textbox);
      EscherOptRecord optRecord = container.getChildById(EscherOptRecord.RECORD_ID);
      assertEquals(opt1Str, optRecord.toXml());
      textbox.setLineStyle(textbox.getLineStyle());
      assertEquals(opt1Str, optRecord.toXml());
      textbox.setLineWidth(textbox.getLineWidth());
      assertEquals(opt1Str, optRecord.toXml());
      textbox.setLineStyleColor(textbox.getLineStyleColor());
      assertEquals(opt1Str, optRecord.toXml());
    } finally {
      wb.close();
    }
  }