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