1    
2    /* ====================================================================
3     * The Apache Software License, Version 1.1
4     *
5     * Copyright (c) 2002 The Apache Software Foundation.  All rights
6     * reserved.
7     *
8     * Redistribution and use in source and binary forms, with or without
9     * modification, are permitted provided that the following conditions
10    * are met:
11    *
12    * 1. Redistributions of source code must retain the above copyright
13    *    notice, this list of conditions and the following disclaimer.
14    *
15    * 2. Redistributions in binary form must reproduce the above copyright
16    *    notice, this list of conditions and the following disclaimer in
17    *    the documentation and/or other materials provided with the
18    *    distribution.
19    *
20    * 3. The end-user documentation included with the redistribution,
21    *    if any, must include the following acknowledgment:
22    *       "This product includes software developed by the
23    *        Apache Software Foundation (http://www.apache.org/)."
24    *    Alternately, this acknowledgment may appear in the software itself,
25    *    if and wherever such third-party acknowledgments normally appear.
26    *
27    * 4. The names "Apache" and "Apache Software Foundation" and
28    *    "Apache POI" must not be used to endorse or promote products
29    *    derived from this software without prior written permission. For
30    *    written permission, please contact apache@apache.org.
31    *
32    * 5. Products derived from this software may not be called "Apache",
33    *    "Apache POI", nor may "Apache" appear in their name, without
34    *    prior written permission of the Apache Software Foundation.
35    *
36    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
37    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
38    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
39    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
40    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
41    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
42    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
43    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
44    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
45    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
46    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
47    * SUCH DAMAGE.
48    * ====================================================================
49    *
50    * This software consists of voluntary contributions made by many
51    * individuals on behalf of the Apache Software Foundation.  For more
52    * information on the Apache Software Foundation, please see
53    * <http://www.apache.org/>.
54    */
55   
56   package org.apache.poi.hssf.dev;
57   
58   import java.io.InputStream;
59   import java.io.IOException;
60   import java.io.ByteArrayInputStream;
61   import java.io.FileInputStream;
62   import java.io.FileOutputStream;
63   
64   import java.util.Random;
65   
66   import org.apache.poi.poifs.filesystem.POIFSFileSystem;
67   import org.apache.poi.hssf.record.*;
68   import org.apache.poi.hssf.model.*;
69   import org.apache.poi.hssf.usermodel.*;
70   import org.apache.poi.hssf.util.*;
71   
72   /**
73    * File for HSSF testing/examples
74    *
75    * THIS IS NOT THE MAIN HSSF FILE!!  This is a util for testing functionality.
76    * It does contain sample API usage that may be educational to regular API users.
77    *
78    * @see #main
79    * @author Andrew Oliver (acoliver at apache dot org)
80    */
81   
82   public class HSSF
83   {
84       private String         filename     = null;
85   
86       // private POIFSFileSystem     fs           = null;
87       private InputStream    stream       = null;
88       private Record[]       records      = null;
89       protected HSSFWorkbook hssfworkbook = null;
90   
91       /**
92        * Constructor HSSF - creates an HSSFStream from an InputStream.  The HSSFStream
93        * reads in the records allowing modification.
94        *
95        *
96        * @param filename
97        *
98        * @exception IOException
99        *
100       */
101  
102      public HSSF(String filename)
103          throws IOException
104      {
105          this.filename = filename;
106          POIFSFileSystem fs =
107              new POIFSFileSystem(new FileInputStream(filename));
108  
109          hssfworkbook = new HSSFWorkbook(fs);
110  
111          // records = RecordFactory.createRecords(stream);
112      }
113  
114      /**
115       * Constructor HSSF - given a filename this outputs a sample sheet with just
116       * a set of rows/cells.
117       *
118       *
119       * @param filename
120       * @param write
121       *
122       * @exception IOException
123       *
124       */
125  
126      public HSSF(String filename, boolean write)
127          throws IOException
128      {
129          short            rownum = 0;
130          FileOutputStream out    = new FileOutputStream(filename);
131          HSSFWorkbook     wb     = new HSSFWorkbook();
132          HSSFSheet        s      = wb.createSheet();
133          HSSFRow          r      = null;
134          HSSFCell         c      = null;
135          HSSFCellStyle    cs     = wb.createCellStyle();
136          HSSFCellStyle    cs2    = wb.createCellStyle();
137          HSSFCellStyle    cs3    = wb.createCellStyle();
138          HSSFFont         f      = wb.createFont();
139          HSSFFont         f2     = wb.createFont();
140  
141          f.setFontHeightInPoints(( short ) 12);
142          f.setColor(( short ) 0xA);
143          f.setBoldweight(f.BOLDWEIGHT_BOLD);
144          f2.setFontHeightInPoints(( short ) 10);
145          f2.setColor(( short ) 0xf);
146          f2.setBoldweight(f2.BOLDWEIGHT_BOLD);
147          cs.setFont(f);
148          cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
149          cs2.setBorderBottom(cs2.BORDER_THIN);
150          cs2.setFillPattern(( short ) 1);   // fill w fg
151          cs2.setFillForegroundColor(( short ) 0xA);
152          cs2.setFont(f2);
153          wb.setSheetName(0, "HSSF Test");
154          for (rownum = ( short ) 0; rownum < 300; rownum++)
155          {
156              r = s.createRow(rownum);
157              if ((rownum % 2) == 0)
158              {
159                  r.setHeight(( short ) 0x249);
160              }
161  
162              // r.setRowNum(( short ) rownum);
163              for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
164              {
165                  c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
166                  c.setCellValue(rownum * 10000 + cellnum
167                                 + ((( double ) rownum / 1000)
168                                    + (( double ) cellnum / 10000)));
169                  if ((rownum % 2) == 0)
170                  {
171                      c.setCellStyle(cs);
172                  }
173                  c = r.createCell(( short ) (cellnum + 1),
174                                   HSSFCell.CELL_TYPE_STRING);
175                  c.setCellValue("TEST");
176                  s.setColumnWidth(( short ) (cellnum + 1),
177                                   ( short ) ((50 * 8) / (( double ) 1 / 20)));
178                  if ((rownum % 2) == 0)
179                  {
180                      c.setCellStyle(cs2);
181                  }
182              }   // 50 characters divided by 1/20th of a point
183          }
184  
185          // draw a thick black border on the row at the bottom using BLANKS
186          rownum++;
187          rownum++;
188          r = s.createRow(rownum);
189          cs3.setBorderBottom(cs3.BORDER_THICK);
190          for (short cellnum = ( short ) 0; cellnum < 50; cellnum++)
191          {
192              c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK);
193  
194              // c.setCellValue(0);
195              c.setCellStyle(cs3);
196          }
197          s.addMergedRegion(new Region(( short ) 0, ( short ) 0, ( short ) 3,
198                                       ( short ) 3));
199          s.addMergedRegion(new Region(( short ) 100, ( short ) 100,
200                                       ( short ) 110, ( short ) 110));
201  
202          // end draw thick black border
203          // create a sheet, set its title then delete it
204          s = wb.createSheet();
205          wb.setSheetName(1, "DeletedSheet");
206          wb.removeSheetAt(1);
207  
208          // end deleted sheet
209          wb.write(out);
210          out.close();
211      }
212  
213      /**
214       * Constructor HSSF - takes in file - attempts to read it then reconstruct it
215       *
216       *
217       * @param infile
218       * @param outfile
219       * @param write
220       *
221       * @exception IOException
222       *
223       */
224  
225      public HSSF(String infile, String outfile, boolean write)
226          throws IOException
227      {
228          this.filename = filename;
229          POIFSFileSystem fs =
230              new POIFSFileSystem(new FileInputStream(filename));
231  
232          hssfworkbook = new HSSFWorkbook(fs);
233  
234          // HSSFWorkbook book = hssfstream.getWorkbook();
235      }
236  
237      /**
238       * Method main
239       *
240       * Given 1 argument takes that as the filename, inputs it and dumps the
241       * cell values/types out to sys.out
242       *
243       * given 2 arguments where the second argument is the word "write" and the
244       * first is the filename - writes out a sample (test) spreadsheet (see
245       * public HSSF(String filename, boolean write)).
246       *
247       * given 2 arguments where the first is an input filename and the second
248       * an output filename (not write), attempts to fully read in the
249       * spreadsheet and fully write it out.
250       *
251       * given 3 arguments where the first is an input filename and the second an
252       * output filename (not write) and the third is "modify1", attempts to read in the
253       * spreadsheet, deletes rows 0-24, 74-99.  Changes cell at row 39, col 3 to
254       * "MODIFIED CELL" then writes it out.  Hence this is "modify test 1".  If you
255       * take the output from the write test, you'll have a valid scenario.
256       *
257       * @param args
258       *
259       */
260  
261      public static void main(String [] args)
262      {
263          if (args.length < 2)
264          {
265  
266  /*            try
267              {
268                  HSSF hssf = new HSSF(args[ 0 ]);
269  
270                  System.out.println("Data dump:\n");
271                  HSSFWorkbook wb = hssf.hssfworkbook;
272  
273                  for (int k = 0; k < wb.getNumberOfSheets(); k++)
274                  {
275                      System.out.println("Sheet " + k);
276                      HSSFSheet sheet = wb.getSheetAt(k);
277                      int       rows  = sheet.getPhysicalNumberOfRows();
278  
279                      for (int r = 0; r < rows; r++)
280                      {
281                          HSSFRow row   = sheet.getPhysicalRowAt(r);
282                          int     cells = row.getPhysicalNumberOfCells();
283  
284                          System.out.println("ROW " + row.getRowNum());
285                          for (int c = 0; c < cells; c++)
286                          {
287                              HSSFCell cell  = row.getPhysicalCellAt(c);
288                              String   value = null;
289  
290                              switch (cell.getCellType())
291                              {
292  
293                                  case HSSFCell.CELL_TYPE_FORMULA :
294                                      value = "FORMULA ";
295                                      break;
296  
297                                  case HSSFCell.CELL_TYPE_NUMERIC :
298                                      value = "NUMERIC value="
299                                              + cell.getNumericCellValue();
300                                      break;
301  
302                                  case HSSFCell.CELL_TYPE_STRING :
303                                      value = "STRING value="
304                                              + cell.getStringCellValue();
305                                      break;
306  
307                                  default :
308                              }
309                              System.out.println("CELL col="
310                                                 + cell.getCellNum()
311                                                 + " VALUE=" + value);
312                          }
313                      }
314                  }
315              }
316              catch (Exception e)
317              {
318                  e.printStackTrace();
319              }*/
320          }
321          else if (args.length == 2)
322          {
323              if (args[ 1 ].toLowerCase().equals("write"))
324              {
325                  System.out.println("Write mode");
326                  try
327                  {
328                      long time = System.currentTimeMillis();
329                      HSSF hssf = new HSSF(args[ 0 ], true);
330  
331                      System.out
332                          .println("" + (System.currentTimeMillis() - time)
333                                   + " ms generation time");
334                  }
335                  catch (Exception e)
336                  {
337                      e.printStackTrace();
338                  }
339              }
340              else
341              {
342                  System.out.println("readwrite test");
343                  try
344                  {
345                      HSSF             hssf   = new HSSF(args[ 0 ]);
346  
347                      // HSSFStream       hssfstream = hssf.hssfstream;
348                      HSSFWorkbook     wb     = hssf.hssfworkbook;
349                      FileOutputStream stream = new FileOutputStream(args[ 1 ]);
350  
351                      // HSSFCell cell = new HSSFCell();
352                      // cell.setCellNum((short)3);
353                      // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
354                      // cell.setCellValue(-8009.999);
355                      // hssfstream.modifyCell(cell,0,(short)6);
356                      wb.write(stream);
357                      stream.close();
358                  }
359                  catch (Exception e)
360                  {
361                      e.printStackTrace();
362                  }
363              }
364          }
365          else if ((args.length == 3)
366                   && args[ 2 ].toLowerCase().equals("modify1"))
367          {
368              try   // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
369              {
370                  HSSF             hssf   = new HSSF(args[ 0 ]);
371  
372                  // HSSFStream       hssfstream = hssf.hssfstream;
373                  HSSFWorkbook     wb     = hssf.hssfworkbook;
374                  FileOutputStream stream = new FileOutputStream(args[ 1 ]);
375                  HSSFSheet        sheet  = wb.getSheetAt(0);
376  
377                  for (int k = 0; k < 25; k++)
378                  {
379                      HSSFRow row = sheet.getRow(k);
380  
381                      sheet.removeRow(row);
382                  }
383                  for (int k = 74; k < 100; k++)
384                  {
385                      HSSFRow row = sheet.getRow(k);
386  
387                      sheet.removeRow(row);
388                  }
389                  HSSFRow  row  = sheet.getRow(39);
390                  HSSFCell cell = row.getCell(( short ) 3);
391  
392                  cell.setCellType(HSSFCell.CELL_TYPE_STRING);
393                  cell.setCellValue("MODIFIED CELL!!!!!");
394  
395                  // HSSFCell cell = new HSSFCell();
396                  // cell.setCellNum((short)3);
397                  // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
398                  // cell.setCellValue(-8009.999);
399                  // hssfstream.modifyCell(cell,0,(short)6);
400                  wb.write(stream);
401                  stream.close();
402              }
403              catch (Exception e)
404              {
405                  e.printStackTrace();
406              }
407          }
408      }
409  }
410