Example #1
0
  /**
   * Constructor HSSF - given a filename this outputs a sample sheet with just a Set of rows/cells.
   *
   * @param filename
   * @param Write
   * @exception IOException
   */
  public HSSF(String filename, bool Write) {

    short rownum = 0;
    FileOutputStream out = new FileOutputStream(filename);
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.CreateSheet();
    HSSFRow r = null;
    HSSFCell c = null;
    HSSFCellStyle cs = wb.CreateCellStyle();
    HSSFCellStyle cs2 = wb.CreateCellStyle();
    HSSFCellStyle cs3 = wb.CreateCellStyle();
    HSSFFont f = wb.CreateFont();
    HSSFFont f2 = wb.CreateFont();

    f.SetFontHeightInPoints((short) 12);
    f.SetColor((short) 0xA);
    f.SetBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    f2.SetFontHeightInPoints((short) 10);
    f2.SetColor((short) 0xf);
    f2.SetBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.SetFont(f);
    cs.SetDataFormat(HSSFDataFormat.GetBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.SetBorderBottom(HSSFCellStyle.BORDER_THIN);
    cs2.SetFillPattern((short) 1); // Fill w fg
    cs2.SetFillForegroundColor((short) 0xA);
    cs2.SetFont(f2);
    wb.SetSheetName(0, "HSSF Test");
    for (rownum = (short) 0; rownum < 300; rownum++) {
      r = s.CreateRow(rownum);
      if ((rownum % 2) == 0) {
        r.SetHeight((short) 0x249);
      }

      // r.SetRowNum(( short ) rownum);
      for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) {
        c = r.CreateCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
        c.SetCellValue(
            rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
        if ((rownum % 2) == 0) {
          c.SetCellStyle(cs);
        }
        c = r.CreateCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING);
        c.SetCellValue("TEST");
        s.SetColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
        if ((rownum % 2) == 0) {
          c.SetCellStyle(cs2);
        }
      } // 50 Chars divided by 1/20th of a point
    }

    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    r = s.CreateRow(rownum);
    cs3.SetBorderBottom(HSSFCellStyle.BORDER_THICK);
    for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
      c = r.CreateCell(cellnum, HSSFCell.CELL_TYPE_BLANK);

      // c.SetCellValue(0);
      c.SetCellStyle(cs3);
    }
    s.AddMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3));
    s.AddMergedRegion(
        new Region(
            (short) 100, (short) 100,
            (short) 110, (short) 110));

    // end draw thick black border
    // Create a sheet, Set its title then delete it
    s = wb.CreateSheet();
    wb.SetSheetName(1, "DeletedSheet");
    wb.RemoveSheetAt(1);

    // end deleted sheet
    wb.Write(out);
    out.close();
  }
Example #2
0
  /**
   * Method main
   *
   * <p>Given 1 argument takes that as the filename, inputs it and dumps the cell values/types out
   * to sys.out
   *
   * <p>given 2 arguments where the second argument Is the word "Write" and the first Is the
   * filename - Writes out a sample (test) spReadsheet (see public HSSF(String filename, bool
   * Write)).
   *
   * <p>given 2 arguments where the first Is an input filename and the second an output filename
   * (not Write), attempts to fully Read in the spReadsheet and fully Write it out.
   *
   * <p>given 3 arguments where the first Is an input filename and the second an output filename
   * (not Write) and the third Is "modify1", attempts to Read in the spReadsheet, deletes rows 0-24,
   * 74-99. Changes cell at row 39, col 3 to "MODIFIED CELL" then Writes it out. Hence this Is
   * "modify test 1". If you take the output from the Write test, you'll have a valid scenario.
   *
   * @param args
   */
  public static void main(String[] args) {
    if (args.Length < 2) {

      /*            try
      {
          HSSF hssf = new HSSF(args[ 0 ]);

          Console.WriteLine("Data dump:\n");
          HSSFWorkbook wb = hssf.hssfworkbook;

          for (int k = 0; k < wb.GetNumberOfSheets(); k++)
          {
              Console.WriteLine("Sheet " + k);
              HSSFSheet sheet = wb.GetSheetAt(k);
              int       rows  = sheet.GetPhysicalNumberOfRows();

              for (int r = 0; r < rows; r++)
              {
                  HSSFRow row   = sheet.GetPhysicalRowAt(r);
                  int     cells = row.GetPhysicalNumberOfCells();

                  Console.WriteLine("ROW " + row.GetRowNum());
                  for (int c = 0; c < cells; c++)
                  {
                      HSSFCell cell  = row.GetPhysicalCellAt(c);
                      String   value = null;

                      switch (cell.GetCellType())
                      {

                          case HSSFCell.CELL_TYPE_FORMULA :
                              value = "FORMULA ";
                              break;

                          case HSSFCell.CELL_TYPE_NUMERIC :
                              value = "NUMERIC value="
                                      + cell.GetNumericCellValue();
                              break;

                          case HSSFCell.CELL_TYPE_STRING :
                              value = "STRING value="
                                      + cell.GetStringCellValue();
                              break;

                          default :
                      }
                      Console.WriteLine("CELL col="
                                         + cell.GetCellNum()
                                         + " VALUE=" + value);
                  }
              }
          }
      }
      catch (Exception e)
      {
          e.printStackTrace();
      }*/
    } else if (args.Length == 2) {
      if (args[1].ToLower().Equals("Write")) {
        Console.WriteLine("Write mode");
        try {
          long time = System.currentTimeMillis();
          HSSF hssf = new HSSF(args[0], true);

          System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
        } catch (Exception e) {
          e.printStackTrace();
        }
      } else {
        Console.WriteLine("ReadWrite test");
        try {
          HSSF hssf = new HSSF(args[0]);

          // HSSFStream       hssfstream = hssf.hssfstream;
          HSSFWorkbook wb = hssf.hssfworkbook;
          FileOutputStream stream = new FileOutputStream(args[1]);

          // HSSFCell cell = new HSSFCell();
          // cell.SetCellNum((short)3);
          // cell.SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
          // cell.SetCellValue(-8009.999);
          // hssfstream.modifyCell(cell,0,(short)6);
          wb.Write(stream);
          stream.close();
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    } else if ((args.Length == 3) && args[2].ToLower().Equals("modify1")) {
      try // delete row 0-24, row 74 - 99 && Change cell 3 on row 39 to string "MODIFIED CELL!!"
      {
        HSSF hssf = new HSSF(args[0]);

        // HSSFStream       hssfstream = hssf.hssfstream;
        HSSFWorkbook wb = hssf.hssfworkbook;
        FileOutputStream stream = new FileOutputStream(args[1]);
        HSSFSheet sheet = wb.GetSheetAt(0);

        for (int k = 0; k < 25; k++) {
          HSSFRow row = sheet.GetRow(k);

          sheet.RemoveRow(row);
        }
        for (int k = 74; k < 100; k++) {
          HSSFRow row = sheet.GetRow(k);

          sheet.RemoveRow(row);
        }
        HSSFRow row = sheet.GetRow(39);
        HSSFCell cell = row.GetCell((short) 3);

        cell.SetCellType(HSSFCell.CELL_TYPE_STRING);
        cell.SetCellValue("MODIFIED CELL!!!!!");

        // HSSFCell cell = new HSSFCell();
        // cell.SetCellNum((short)3);
        // cell.SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
        // cell.SetCellValue(-8009.999);
        // hssfstream.modifyCell(cell,0,(short)6);
        wb.Write(stream);
        stream.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }