001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: package org.apache.poi.hssf.dev;
019:
020: import java.io.IOException;
021: import java.io.FileInputStream;
022: import java.io.FileOutputStream;
023:
024: import org.apache.poi.poifs.filesystem.POIFSFileSystem;
025: import org.apache.poi.hssf.usermodel.*;
026: import org.apache.poi.hssf.util.*;
027:
028: /**
029: * File for HSSF testing/examples
030: *
031: * THIS IS NOT THE MAIN HSSF FILE!! This is a util for testing functionality.
032: * It does contain sample API usage that may be educational to regular API users.
033: *
034: * @see #main
035: * @author Andrew Oliver (acoliver at apache dot org)
036: */
037:
038: public class HSSF {
039: private String filename = null;
040:
041: protected HSSFWorkbook hssfworkbook = null;
042:
043: /**
044: * Constructor HSSF - creates an HSSFStream from an InputStream. The HSSFStream
045: * reads in the records allowing modification.
046: *
047: *
048: * @param filename
049: *
050: * @exception IOException
051: *
052: */
053:
054: public HSSF(String filename) throws IOException {
055: this .filename = filename;
056: POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
057: filename));
058:
059: hssfworkbook = new HSSFWorkbook(fs);
060:
061: // records = RecordFactory.createRecords(stream);
062: }
063:
064: /**
065: * Constructor HSSF - given a filename this outputs a sample sheet with just
066: * a set of rows/cells.
067: *
068: *
069: * @param filename
070: * @param write
071: *
072: * @exception IOException
073: *
074: */
075:
076: public HSSF(String filename, boolean write) throws IOException {
077: short rownum = 0;
078: FileOutputStream out = new FileOutputStream(filename);
079: HSSFWorkbook wb = new HSSFWorkbook();
080: HSSFSheet s = wb.createSheet();
081: HSSFRow r = null;
082: HSSFCell c = null;
083: HSSFCellStyle cs = wb.createCellStyle();
084: HSSFCellStyle cs2 = wb.createCellStyle();
085: HSSFCellStyle cs3 = wb.createCellStyle();
086: HSSFFont f = wb.createFont();
087: HSSFFont f2 = wb.createFont();
088:
089: f.setFontHeightInPoints((short) 12);
090: f.setColor((short) 0xA);
091: f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
092: f2.setFontHeightInPoints((short) 10);
093: f2.setColor((short) 0xf);
094: f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
095: cs.setFont(f);
096: cs.setDataFormat(HSSFDataFormat
097: .getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
098: cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
099: cs2.setFillPattern((short) 1); // fill w fg
100: cs2.setFillForegroundColor((short) 0xA);
101: cs2.setFont(f2);
102: wb.setSheetName(0, "HSSF Test");
103: for (rownum = (short) 0; rownum < 300; rownum++) {
104: r = s.createRow(rownum);
105: if ((rownum % 2) == 0) {
106: r.setHeight((short) 0x249);
107: }
108:
109: // r.setRowNum(( short ) rownum);
110: for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) {
111: c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
112: c
113: .setCellValue(rownum
114: * 10000
115: + cellnum
116: + (((double) rownum / 1000) + ((double) cellnum / 10000)));
117: if ((rownum % 2) == 0) {
118: c.setCellStyle(cs);
119: }
120: c = r.createCell((short) (cellnum + 1),
121: HSSFCell.CELL_TYPE_STRING);
122: c.setCellValue("TEST");
123: s.setColumnWidth((short) (cellnum + 1),
124: (short) ((50 * 8) / ((double) 1 / 20)));
125: if ((rownum % 2) == 0) {
126: c.setCellStyle(cs2);
127: }
128: } // 50 characters divided by 1/20th of a point
129: }
130:
131: // draw a thick black border on the row at the bottom using BLANKS
132: rownum++;
133: rownum++;
134: r = s.createRow(rownum);
135: cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);
136: for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
137: c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK);
138:
139: // c.setCellValue(0);
140: c.setCellStyle(cs3);
141: }
142: s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3,
143: (short) 3));
144: s.addMergedRegion(new Region((short) 100, (short) 100,
145: (short) 110, (short) 110));
146:
147: // end draw thick black border
148: // create a sheet, set its title then delete it
149: s = wb.createSheet();
150: wb.setSheetName(1, "DeletedSheet");
151: wb.removeSheetAt(1);
152:
153: // end deleted sheet
154: wb.write(out);
155: out.close();
156: }
157:
158: /**
159: * Constructor HSSF - takes in file - attempts to read it then reconstruct it
160: *
161: *
162: * @param infile
163: * @param outfile
164: * @param write
165: *
166: * @exception IOException
167: *
168: */
169:
170: public HSSF(String infile, String outfile, boolean write)
171: throws IOException {
172: this .filename = infile;
173: POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
174: filename));
175:
176: hssfworkbook = new HSSFWorkbook(fs);
177:
178: // HSSFWorkbook book = hssfstream.getWorkbook();
179: }
180:
181: /**
182: * Method main
183: *
184: * Given 1 argument takes that as the filename, inputs it and dumps the
185: * cell values/types out to sys.out
186: *
187: * given 2 arguments where the second argument is the word "write" and the
188: * first is the filename - writes out a sample (test) spreadsheet (see
189: * public HSSF(String filename, boolean write)).
190: *
191: * given 2 arguments where the first is an input filename and the second
192: * an output filename (not write), attempts to fully read in the
193: * spreadsheet and fully write it out.
194: *
195: * given 3 arguments where the first is an input filename and the second an
196: * output filename (not write) and the third is "modify1", attempts to read in the
197: * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to
198: * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you
199: * take the output from the write test, you'll have a valid scenario.
200: *
201: * @param args
202: *
203: */
204:
205: public static void main(String[] args) {
206: if (args.length < 2) {
207:
208: /* try
209: {
210: HSSF hssf = new HSSF(args[ 0 ]);
211:
212: System.out.println("Data dump:\n");
213: HSSFWorkbook wb = hssf.hssfworkbook;
214:
215: for (int k = 0; k < wb.getNumberOfSheets(); k++)
216: {
217: System.out.println("Sheet " + k);
218: HSSFSheet sheet = wb.getSheetAt(k);
219: int rows = sheet.getPhysicalNumberOfRows();
220:
221: for (int r = 0; r < rows; r++)
222: {
223: HSSFRow row = sheet.getPhysicalRowAt(r);
224: int cells = row.getPhysicalNumberOfCells();
225:
226: System.out.println("ROW " + row.getRowNum());
227: for (int c = 0; c < cells; c++)
228: {
229: HSSFCell cell = row.getPhysicalCellAt(c);
230: String value = null;
231:
232: switch (cell.getCellType())
233: {
234:
235: case HSSFCell.CELL_TYPE_FORMULA :
236: value = "FORMULA ";
237: break;
238:
239: case HSSFCell.CELL_TYPE_NUMERIC :
240: value = "NUMERIC value="
241: + cell.getNumericCellValue();
242: break;
243:
244: case HSSFCell.CELL_TYPE_STRING :
245: value = "STRING value="
246: + cell.getStringCellValue();
247: break;
248:
249: default :
250: }
251: System.out.println("CELL col="
252: + cell.getCellNum()
253: + " VALUE=" + value);
254: }
255: }
256: }
257: }
258: catch (Exception e)
259: {
260: e.printStackTrace();
261: }*/
262: } else if (args.length == 2) {
263: if (args[1].toLowerCase().equals("write")) {
264: System.out.println("Write mode");
265: try {
266: long time = System.currentTimeMillis();
267: HSSF hssf = new HSSF(args[0], true);
268:
269: System.out.println(""
270: + (System.currentTimeMillis() - time)
271: + " ms generation time");
272: } catch (Exception e) {
273: e.printStackTrace();
274: }
275: } else {
276: System.out.println("readwrite test");
277: try {
278: HSSF hssf = new HSSF(args[0]);
279:
280: // HSSFStream hssfstream = hssf.hssfstream;
281: HSSFWorkbook wb = hssf.hssfworkbook;
282: FileOutputStream stream = new FileOutputStream(
283: args[1]);
284:
285: // HSSFCell cell = new HSSFCell();
286: // cell.setCellNum((short)3);
287: // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
288: // cell.setCellValue(-8009.999);
289: // hssfstream.modifyCell(cell,0,(short)6);
290: wb.write(stream);
291: stream.close();
292: } catch (Exception e) {
293: e.printStackTrace();
294: }
295: }
296: } else if ((args.length == 3)
297: && args[2].toLowerCase().equals("modify1")) {
298: try // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
299: {
300: HSSF hssf = new HSSF(args[0]);
301:
302: // HSSFStream hssfstream = hssf.hssfstream;
303: HSSFWorkbook wb = hssf.hssfworkbook;
304: FileOutputStream stream = new FileOutputStream(args[1]);
305: HSSFSheet sheet = wb.getSheetAt(0);
306:
307: for (int k = 0; k < 25; k++) {
308: HSSFRow row = sheet.getRow(k);
309:
310: sheet.removeRow(row);
311: }
312: for (int k = 74; k < 100; k++) {
313: HSSFRow row = sheet.getRow(k);
314:
315: sheet.removeRow(row);
316: }
317: HSSFRow row = sheet.getRow(39);
318: HSSFCell cell = row.getCell((short) 3);
319:
320: cell.setCellType(HSSFCell.CELL_TYPE_STRING);
321: cell.setCellValue("MODIFIED CELL!!!!!");
322:
323: // HSSFCell cell = new HSSFCell();
324: // cell.setCellNum((short)3);
325: // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
326: // cell.setCellValue(-8009.999);
327: // hssfstream.modifyCell(cell,0,(short)6);
328: wb.write(stream);
329: stream.close();
330: } catch (Exception e) {
331: e.printStackTrace();
332: }
333: }
334: }
335: }
|