HSSF


1   /* ====================================================================
2      Copyright 2002-2004   Apache Software Foundation
3   
4      Licensed under the Apache License, Version 2.0 (the "License");
5      you may not use this file except in compliance with the License.
6      You may obtain a copy of the License at
7   
8          http://www.apache.org/licenses/LICENSE-2.0
9   
10     Unless required by applicable law or agreed to in writing, software
11     distributed under the License is distributed on an "AS IS" BASIS,
12     WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13     See the License for the specific language governing permissions and
14     limitations under the License.
15  ==================================================================== */
16  
17  
18  package org.apache.poi.hssf.dev;
19  
20  import java.io.InputStream  ;
21  import java.io.IOException  ;
22  import java.io.FileInputStream  ;
23  import java.io.FileOutputStream  ;
24  
25  import org.apache.poi.poifs.filesystem.POIFSFileSystem;
26  import org.apache.poi.hssf.record.*;
27  import org.apache.poi.hssf.usermodel.*;
28  import org.apache.poi.hssf.util.*;
29  
30  /**
31   * File for HSSF testing/examples
32   *
33   * THIS IS NOT THE MAIN HSSF FILE!!  This is a util for testing functionality.
34   * It does contain sample API usage that may be educational to regular API users.
35   *
36   * @see #main
37   * @author Andrew Oliver (acoliver at apache dot org)
38   */
39  
40  public class HSSF
41  {
42      private String           filename     = null;
43  
44      // private POIFSFileSystem     fs           = null;
45      private InputStream      stream       = null;
46      private Record[]       records      = null;
47      protected HSSFWorkbook hssfworkbook = null;
48  
49      /**
50       * Constructor HSSF - creates an HSSFStream from an InputStream.  The HSSFStream
51       * reads in the records allowing modification.
52       *
53       *
54       * @param filename
55       *
56       * @exception IOException
57       *
58       */
59  
60      public HSSF(String   filename)
61          throws IOException  
62      {
63          this.filename = filename;
64          POIFSFileSystem fs =
65              new POIFSFileSystem(new FileInputStream  (filename));
66  
67          hssfworkbook = new HSSFWorkbook(fs);
68  
69          // records = RecordFactory.createRecords(stream);
70      }
71  
72      /**
73       * Constructor HSSF - given a filename this outputs a sample sheet with just
74       * a set of rows/cells.
75       *
76       *
77       * @param filename
78       * @param write
79       *
80       * @exception IOException
81       *
82       */
83  
84      public HSSF(String   filename, boolean write)
85          throws IOException  
86      {
87          short            rownum = 0;
88          FileOutputStream   out    = new FileOutputStream  (filename);
89          HSSFWorkbook     wb     = new HSSFWorkbook();
90          HSSFSheet        s      = wb.createSheet();
91          HSSFRow          r      = null;
92          HSSFCell         c      = null;
93          HSSFCellStyle    cs     = wb.createCellStyle();
94          HSSFCellStyle    cs2    = wb.createCellStyle();
95          HSSFCellStyle    cs3    = wb.createCellStyle();
96          HSSFFont         f      = wb.createFont();
97          HSSFFont         f2     = wb.createFont();
98  
99          f.setFontHeightInPoints(( short ) 12);
100         f.setColor(( short ) 0xA);
101         f.setBoldweight(f.BOLDWEIGHT_BOLD);
102         f2.setFontHeightInPoints(( short ) 10);
103         f2.setColor(( short ) 0xf);
104         f2.setBoldweight(f2.BOLDWEIGHT_BOLD);
105         cs.setFont(f);
106         cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
107         cs2.setBorderBottom(cs2.BORDER_THIN);
108         cs2.setFillPattern(( short ) 1);   // fill w fg
109         cs2.setFillForegroundColor(( short ) 0xA);
110         cs2.setFont(f2);
111         wb.setSheetName(0, "HSSF Test");
112         for (rownum = ( short ) 0; rownum < 300; rownum++)
113         {
114             r = s.createRow(rownum);
115             if ((rownum % 2) == 0)
116             {
117                 r.setHeight(( short ) 0x249);
118             }
119 
120             // r.setRowNum(( short ) rownum);
121             for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
122             {
123                 c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
124                 c.setCellValue(rownum * 10000 + cellnum
125                                + ((( double ) rownum / 1000)
126                                   + (( double ) cellnum / 10000)));
127                 if ((rownum % 2) == 0)
128                 {
129                     c.setCellStyle(cs);
130                 }
131                 c = r.createCell(( short ) (cellnum + 1),
132                                  HSSFCell.CELL_TYPE_STRING);
133                 c.setCellValue("TEST");
134                 s.setColumnWidth(( short ) (cellnum + 1),
135                                  ( short ) ((50 * 8) / (( double ) 1 / 20)));
136                 if ((rownum % 2) == 0)
137                 {
138                     c.setCellStyle(cs2);
139                 }
140             }   // 50 characters divided by 1/20th of a point
141         }
142 
143         // draw a thick black border on the row at the bottom using BLANKS
144         rownum++;
145         rownum++;
146         r = s.createRow(rownum);
147         cs3.setBorderBottom(cs3.BORDER_THICK);
148         for (short cellnum = ( short ) 0; cellnum < 50; cellnum++)
149         {
150             c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK);
151 
152             // c.setCellValue(0);
153             c.setCellStyle(cs3);
154         }
155         s.addMergedRegion(new Region(( short ) 0, ( short ) 0, ( short ) 3,
156                                      ( short ) 3));
157         s.addMergedRegion(new Region(( short ) 100, ( short ) 100,
158                                      ( short ) 110, ( short ) 110));
159 
160         // end draw thick black border
161         // create a sheet, set its title then delete it
162         s = wb.createSheet();
163         wb.setSheetName(1, "DeletedSheet");
164         wb.removeSheetAt(1);
165 
166         // end deleted sheet
167         wb.write(out);
168         out.close();
169     }
170 
171     /**
172      * Constructor HSSF - takes in file - attempts to read it then reconstruct it
173      *
174      *
175      * @param infile
176      * @param outfile
177      * @param write
178      *
179      * @exception IOException
180      *
181      */
182 
183     public HSSF(String   infile, String   outfile, boolean write)
184         throws IOException  
185     {
186         this.filename = filename;
187         POIFSFileSystem fs =
188             new POIFSFileSystem(new FileInputStream  (filename));
189 
190         hssfworkbook = new HSSFWorkbook(fs);
191 
192         // HSSFWorkbook book = hssfstream.getWorkbook();
193     }
194 
195     /**
196      * Method main
197      *
198      * Given 1 argument takes that as the filename, inputs it and dumps the
199      * cell values/types out to sys.out
200      *
201      * given 2 arguments where the second argument is the word "write" and the
202      * first is the filename - writes out a sample (test) spreadsheet (see
203      * public HSSF(String filename, boolean write)).
204      *
205      * given 2 arguments where the first is an input filename and the second
206      * an output filename (not write), attempts to fully read in the
207      * spreadsheet and fully write it out.
208      *
209      * given 3 arguments where the first is an input filename and the second an
210      * output filename (not write) and the third is "modify1", attempts to read in the
211      * spreadsheet, deletes rows 0-24, 74-99.  Changes cell at row 39, col 3 to
212      * "MODIFIED CELL" then writes it out.  Hence this is "modify test 1".  If you
213      * take the output from the write test, you'll have a valid scenario.
214      *
215      * @param args
216      *
217      */
218 
219     public static void main(String   [] args)
220     {
221         if (args.length < 2)
222         {
223 
224 /*            try
225             {
226                 HSSF hssf = new HSSF(args[ 0 ]);
227 
228                 System.out.println("Data dump:\n");
229                 HSSFWorkbook wb = hssf.hssfworkbook;
230 
231                 for (int k = 0; k < wb.getNumberOfSheets(); k++)
232                 {
233                     System.out.println("Sheet " + k);
234                     HSSFSheet sheet = wb.getSheetAt(k);
235                     int       rows  = sheet.getPhysicalNumberOfRows();
236 
237                     for (int r = 0; r < rows; r++)
238                     {
239                         HSSFRow row   = sheet.getPhysicalRowAt(r);
240                         int     cells = row.getPhysicalNumberOfCells();
241 
242                         System.out.println("ROW " + row.getRowNum());
243                         for (int c = 0; c < cells; c++)
244                         {
245                             HSSFCell cell  = row.getPhysicalCellAt(c);
246                             String   value = null;
247 
248                             switch (cell.getCellType())
249                             {
250 
251                                 case HSSFCell.CELL_TYPE_FORMULA :
252                                     value = "FORMULA ";
253                                     break;
254 
255                                 case HSSFCell.CELL_TYPE_NUMERIC :
256                                     value = "NUMERIC value="
257                                             + cell.getNumericCellValue();
258                                     break;
259 
260                                 case HSSFCell.CELL_TYPE_STRING :
261                                     value = "STRING value="
262                                             + cell.getStringCellValue();
263                                     break;
264 
265                                 default :
266                             }
267                             System.out.println("CELL col="
268                                                + cell.getCellNum()
269                                                + " VALUE=" + value);
270                         }
271                     }
272                 }
273             }
274             catch (Exception e)
275             {
276                 e.printStackTrace();
277             }*/
278         }
279         else if (args.length == 2)
280         {
281             if (args[ 1 ].toLowerCase().equals("write"))
282             {
283                 System.out.println("Write mode");
284                 try
285                 {
286                     long time = System.currentTimeMillis();
287                     HSSF hssf = new HSSF(args[ 0 ], true);
288 
289                     System.out
290                         .println("" + (System.currentTimeMillis() - time)
291                                  + " ms generation time");
292                 }
293                 catch (Exception   e)
294                 {
295                     e.printStackTrace();
296                 }
297             }
298             else
299             {
300                 System.out.println("readwrite test");
301                 try
302                 {
303                     HSSF             hssf   = new HSSF(args[ 0 ]);
304 
305                     // HSSFStream       hssfstream = hssf.hssfstream;
306                     HSSFWorkbook     wb     = hssf.hssfworkbook;
307                     FileOutputStream   stream = new FileOutputStream  (args[ 1 ]);
308 
309                     // HSSFCell cell = new HSSFCell();
310                     // cell.setCellNum((short)3);
311                     // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
312                     // cell.setCellValue(-8009.999);
313                     // hssfstream.modifyCell(cell,0,(short)6);
314                     wb.write(stream);
315                     stream.close();
316                 }
317                 catch (Exception   e)
318                 {
319                     e.printStackTrace();
320                 }
321             }
322         }
323         else if ((args.length == 3)
324                  && args[ 2 ].toLowerCase().equals("modify1"))
325         {
326             try   // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
327             {
328                 HSSF             hssf   = new HSSF(args[ 0 ]);
329 
330                 // HSSFStream       hssfstream = hssf.hssfstream;
331                 HSSFWorkbook     wb     = hssf.hssfworkbook;
332                 FileOutputStream   stream = new FileOutputStream  (args[ 1 ]);
333                 HSSFSheet        sheet  = wb.getSheetAt(0);
334 
335                 for (int k = 0; k < 25; k++)
336                 {
337                     HSSFRow row = sheet.getRow(k);
338 
339                     sheet.removeRow(row);
340                 }
341                 for (int k = 74; k < 100; k++)
342                 {
343                     HSSFRow row = sheet.getRow(k);
344 
345                     sheet.removeRow(row);
346                 }
347                 HSSFRow  row  = sheet.getRow(39);
348                 HSSFCell cell = row.getCell(( short ) 3);
349 
350                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
351                 cell.setCellValue("MODIFIED CELL!!!!!");
352 
353                 // HSSFCell cell = new HSSFCell();
354                 // cell.setCellNum((short)3);
355                 // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
356                 // cell.setCellValue(-8009.999);
357                 // hssfstream.modifyCell(cell,0,(short)6);
358                 wb.write(stream);
359                 stream.close();
360             }
361             catch (Exception   e)
362             {
363                 e.printStackTrace();
364             }
365         }
366     }
367 }
368
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags