使用POI读写Excel

  1 /* ====================================================================
  2    Licensed to the Apache Software Foundation (ASF) under one or more
  3    contributor license agreements.  See the NOTICE file distributed with
  4    this work for additional information regarding copyright ownership.
  5    The ASF licenses this file to You under the Apache License, Version 2.0
  6    (the "License"); you may not use this file except in compliance with
  7    the License.  You may obtain a copy of the License at
  8 
  9        http://www.apache.org/licenses/LICENSE-2.0
 10 
 11    Unless required by applicable law or agreed to in writing, software
 12    distributed under the License is distributed on an "AS IS" BASIS,
 13    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 14    See the License for the specific language governing permissions and
 15    limitations under the License.
 16 ==================================================================== */
 17 
 18 //package org.apache.poi.hssf.usermodel.examples;
 19 
 20 import java.io.FileOutputStream;
 21 import java.io.IOException;
 22 
 23 import org.apache.poi.hssf.usermodel.HSSFCell;
 24 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 25 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
 26 import org.apache.poi.hssf.usermodel.HSSFFont;
 27 import org.apache.poi.hssf.usermodel.HSSFRow;
 28 import org.apache.poi.hssf.usermodel.HSSFSheet;
 29 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 30 import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
 31 import org.apache.poi.ss.usermodel.BorderStyle;
 32 import org.apache.poi.ss.usermodel.FillPatternType;
 33 
 34 /**
 35  * Demonstrates many features of the user API at once.  Used in the HOW-TO guide.
 36  */
 37 public class BigExample {
 38     public static void main(String[] args) throws IOException {
 39         int rownum;
 40 
 41         // create a new workbook
 42         try (HSSFWorkbook wb = new HSSFWorkbook()) {
 43             // create a new sheet
 44             HSSFSheet s = wb.createSheet();
 45             // declare a row object reference
 46             HSSFRow r;
 47             // declare a cell object reference
 48             HSSFCell c;
 49             // create 3 cell styles
 50             HSSFCellStyle cs = wb.createCellStyle();
 51             HSSFCellStyle cs2 = wb.createCellStyle();
 52             HSSFCellStyle cs3 = wb.createCellStyle();
 53             // create 2 fonts objects
 54             HSSFFont f = wb.createFont();
 55             HSSFFont f2 = wb.createFont();
 56 
 57             //set font 1 to 12 point type
 58             f.setFontHeightInPoints((short) 12);
 59             //make it red
 60             f.setColor(HSSFColorPredefined.RED.getIndex());
 61             // make it bold
 62             //arial is the default font
 63             f.setBold(true);
 64 
 65             //set font 2 to 10 point type
 66             f2.setFontHeightInPoints((short) 10);
 67             //make it the color at palette index 0xf (white)
 68             f2.setColor(HSSFColorPredefined.WHITE.getIndex());
 69             //make it bold
 70             f2.setBold(true);
 71 
 72             //set cell style
 73             cs.setFont(f);
 74             //set the cell format see HSSFDataFormat for a full list
 75             cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
 76 
 77             //set a thin border
 78             cs2.setBorderBottom(BorderStyle.THIN);
 79             //fill w fg fill color
 80             cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 81             // set foreground fill to red
 82             cs2.setFillForegroundColor(HSSFColorPredefined.RED.getIndex());
 83 
 84             // set the font
 85             cs2.setFont(f2);
 86 
 87             // set the sheet name to HSSF Test
 88             wb.setSheetName(0, "HSSF Test");
 89             // create a sheet with 300 rows (0-299)
 90             for (rownum = 0; rownum < 300; rownum++) {
 91                 // create a row
 92                 r = s.createRow(rownum);
 93                 // on every other row
 94                 if ((rownum % 2) == 0) {
 95                     // make the row height bigger  (in twips - 1/20 of a point)
 96                     r.setHeight((short) 0x249);
 97                 }
 98 
 99                 //r.setRowNum(( short ) rownum);
100                 // create 50 cells (0-49) (the += 2 becomes apparent later
101                 for (int cellnum = 0; cellnum < 50; cellnum += 2) {
102                     // create a numeric cell
103                     c = r.createCell(cellnum);
104                     // do some goofy math to demonstrate decimals
105                     c.setCellValue(rownum * 10000 + cellnum
106                             + (((double) rownum / 1000)
107                             + ((double) cellnum / 10000)));
108 
109                     // on every other row
110                     if ((rownum % 2) == 0) {
111                         // set this cell to the first cell style we defined
112                         c.setCellStyle(cs);
113                     }
114 
115                     // create a string cell (see why += 2 in the
116                     c = r.createCell(cellnum + 1);
117 
118                     // set the cell's string value to "TEST"
119                     c.setCellValue("TEST");
120                     // make this column a bit wider
121                     s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20)));
122 
123                     // on every other row
124                     if ((rownum % 2) == 0) {
125                         // set this to the white on red cell style
126                         // we defined above
127                         c.setCellStyle(cs2);
128                     }
129 
130                 }
131             }
132 
133             //draw a thick black border on the row at the bottom using BLANKS
134             // advance 2 rows
135             rownum++;
136             rownum++;
137 
138             r = s.createRow(rownum);
139 
140             // define the third style to be the default
141             // except with a thick black border at the bottom
142             cs3.setBorderBottom(BorderStyle.THICK);
143 
144             //create 50 cells
145             for (int cellnum = 0; cellnum < 50; cellnum++) {
146                 //create a blank type cell (no value)
147                 c = r.createCell(cellnum);
148                 // set it to the thick black border style
149                 c.setCellStyle(cs3);
150             }
151 
152             //end draw thick black border
153 
154 
155             // demonstrate adding/naming and deleting a sheet
156             // create a sheet, set its title then delete it
157             wb.createSheet();
158             wb.setSheetName(1, "DeletedSheet");
159             wb.removeSheetAt(1);
160             //end deleted sheet
161 
162             // create a new file
163             try (FileOutputStream out = new FileOutputStream("workbook.xls")) {
164                 // write the workbook to the output stream
165                 // close our file (don't blow out our file handles
166                 wb.write(out);
167             }
168         }
169     }
170 }