POI-Excel表格导入和导出

ExcelWriter

  1 /**
  2  * @author zuzhilong
  3  * @date 2013-10-10 下午08:04:02
  4  * @desc 生成导出Excel文件对象
  5  * @modify 
  6  * @version 1.0.0
  7  */
  8 package com.haoyisheng.util;
  9 
 10 import java.io.File;
 11 import java.io.FileNotFoundException;
 12 import java.io.FileOutputStream;
 13 import java.io.IOException;
 14 import java.io.OutputStream;
 15 import java.util.Calendar;
 16 
 17 import org.apache.poi.hssf.usermodel.HSSFCell;
 18 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 19 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
 20 import org.apache.poi.hssf.usermodel.HSSFRow;
 21 import org.apache.poi.hssf.usermodel.HSSFSheet;
 22 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 23 
 24 public class ExcelWriter {
 25     // 设置cell编码解决中文高位字节截断
 26     private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;
 27 
 28     // 定制浮点数格式
 29     private static String NUMBER_FORMAT = "#,##0.00";
 30 
 31     // 定制日期格式
 32     private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"
 33 
 34     private OutputStream out = null;
 35 
 36     private HSSFWorkbook workbook = null;
 37 
 38     private HSSFSheet sheet = null;
 39 
 40     private HSSFRow row = null;
 41 
 42     public ExcelWriter() {
 43     }
 44 
 45     /**
 46      * 初始化Excel
 47      * 
 48      */
 49     public ExcelWriter(OutputStream out) {
 50         this.out = out;
 51         this.workbook = new HSSFWorkbook();
 52         this.sheet = workbook.createSheet();
 53     }
 54 
 55     /**
 56      * 导出Excel文件
 57      * 
 58      * @throws IOException
 59      */
 60     public void export() throws FileNotFoundException, IOException {
 61         try {
 62             workbook.write(out);
 63             out.flush();
 64             out.close();
 65         } catch (FileNotFoundException e) {
 66             throw new IOException(" 生成导出Excel文件出错! ", e);
 67         } catch (IOException e) {
 68             throw new IOException(" 写入Excel文件出错! ", e);
 69         }
 70 
 71     }
 72 
 73     /**
 74      * 增加一行
 75      * 
 76      * @param index
 77      *            行号
 78      */
 79     public void createRow(int index) {
 80         this.row = this.sheet.createRow(index);
 81     }
 82 
 83     /**
 84      * 获取单元格的值
 85      * 
 86      * @param index
 87      *            列号
 88      */
 89     public String getCell(int index) {
 90         HSSFCell cell = this.row.getCell((short) index);
 91         String strExcelCell = "";
 92         if (cell != null) { // add this condition
 93             // judge
 94             switch (cell.getCellType()) {
 95             case HSSFCell.CELL_TYPE_FORMULA:
 96                 strExcelCell = "FORMULA ";
 97                 break;
 98             case HSSFCell.CELL_TYPE_NUMERIC: {
 99                 strExcelCell = String.valueOf(cell.getNumericCellValue());
100             }
101             break;
102             case HSSFCell.CELL_TYPE_STRING:
103                 strExcelCell = cell.getStringCellValue();
104                 break;
105             case HSSFCell.CELL_TYPE_BLANK:
106                 strExcelCell = "";
107                 break;
108             default:
109                 strExcelCell = "";
110                 break;
111             }
112         }
113         return strExcelCell;
114     }
115 
116     /**
117      * 设置单元格
118      * 
119      * @param index
120      *            列号
121      * @param value
122      *            单元格填充值
123      */
124     public void setCell(int index, int value) {
125         HSSFCell cell = this.row.createCell((short) index);
126         cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
127         cell.setCellValue(value);
128     }
129 
130     /**
131      * 设置单元格
132      * 
133      * @param index
134      *            列号
135      * @param value
136      *            单元格填充值
137      */
138     public void setCell(int index, double value) {
139         HSSFCell cell = this.row.createCell((short) index);
140         cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
141         cell.setCellValue(value);
142         HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
143         HSSFDataFormat format = workbook.createDataFormat();
144         cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
145         cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
146     }
147 
148     /**
149      * 设置单元格
150      * 
151      * @param index
152      *            列号
153      * @param value
154      *            单元格填充值
155      */
156     public void setCell(int index, String value) {
157         HSSFCell cell = this.row.createCell((short) index);
158         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
159 //        cell.setEncoding(XLS_ENCODING);
160         cell.setCellValue(value);
161     }
162 
163     /**
164      * 设置单元格
165      * 
166      * @param index
167      *            列号
168      * @param value
169      *            单元格填充值
170      */
171     public void setCell(int index, Calendar value) {
172         HSSFCell cell = this.row.createCell((short) index);
173 //        cell.setEncoding(XLS_ENCODING);
174         cell.setCellValue(value.getTime());
175         HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
176         cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
177         cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
178     }
179 
180     public static void main(String[] args) {
181         System.out.println(" 开始导出Excel文件 ");
182 
183         File f = new File("d:\qt.xls");
184         ExcelWriter e = new ExcelWriter();
185 
186         try {
187             e = new ExcelWriter(new FileOutputStream(f));
188         } catch (FileNotFoundException e1) {
189             e1.printStackTrace();
190         }
191 
192         e.createRow(0);
193         e.setCell(0, "试题编码 ");
194         e.setCell(1, "题型");
195         e.setCell(2, "分值");
196         e.setCell(3, "难度");
197         e.setCell(4, "级别");
198         e.setCell(5, "知识点");
199 
200         e.createRow(1);
201         e.setCell(0, "t1");
202         e.setCell(1, 1);
203         e.setCell(2, 3.0);
204         e.setCell(3, 1);
205         e.setCell(4, "重要");
206         e.setCell(5, "专业");
207 
208         try {
209             e.export();
210             System.out.println(" 导出Excel文件[成功] ");
211         } catch (IOException ex) {
212             System.out.println(" 导出Excel文件[失败] ");
213             ex.printStackTrace();
214         }
215     }
216 
217 }

ExcelReader

  1 /**
  2  * @author zuzhilong
  3  * @date 2013-10-10 下午08:02:22
  4  * @desc 读取xls工具类
  5  * @modify 
  6  * @version 1.0.0
  7  */
  8 package com.haoyisheng.util;
  9 
 10 import java.io.File;
 11 import java.io.FileInputStream;
 12 import java.io.IOException;
 13 import java.text.DecimalFormat;
 14 import java.text.SimpleDateFormat;
 15 import java.util.Date;
 16 
 17 import org.apache.poi.hssf.usermodel.HSSFCell;
 18 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
 19 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 20 import org.apache.poi.hssf.usermodel.HSSFRow;
 21 import org.apache.poi.hssf.usermodel.HSSFSheet;
 22 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 23 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 24 import org.apache.poi.ss.usermodel.CellStyle;
 25 
 26 public class ExcelReader {
 27     // 工作薄，也就是一个excel文件
 28     private HSSFWorkbook wb = null;// book [includes sheet]
 29     //一个excle文件可以有多个sheet
 30     private HSSFSheet sheet = null;
 31     // 代表了表的第一行，也就是列名
 32     private HSSFRow row = null;
 33     // 一个excel有多个sheet，这是其中一个
 34     private int sheetNum = 0; // 第sheetnum个工作表
 35     // 一个sheet中可以有多行，这里应该是给行数的定义
 36     private int rowNum = 0;
 37     // 文件输入流
 38     private FileInputStream fis = null;
 39     // 指定文件
 40     private File file = null;
 41 
 42     public ExcelReader() {
 43     }
 44 
 45     public ExcelReader(File file) {
 46         this.file = file;
 47     }
 48 
 49     public void setRowNum(int rowNum) {
 50         this.rowNum = rowNum;
 51     }
 52 
 53     public void setSheetNum(int sheetNum) {
 54         this.sheetNum = sheetNum;
 55     }
 56 
 57     public void setFile(File file) {
 58         this.file = file;
 59     }
 60 
 61     /**
 62      * 读取excel文件获得HSSFWorkbook对象
 63      */
 64     public void open() throws IOException {
 65         fis = new FileInputStream(file);
 66         wb = new HSSFWorkbook(new POIFSFileSystem(fis));
 67         fis.close();
 68     }
 69 
 70     /**
 71      * 返回sheet表数目
 72      * 
 73      * @return int
 74      */
 75     public int getSheetCount() {
 76         int sheetCount = -1;
 77         sheetCount = wb.getNumberOfSheets();
 78         return sheetCount;
 79     }
 80 
 81     /**
 82      * sheetNum下的记录行数
 83      * 
 84      * @return int
 85      */
 86     public int getRowCount() {
 87         if (wb == null)
 88             System.out.println("=============>WorkBook为空");
 89         HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
 90         int rowCount = -1;
 91         rowCount = sheet.getLastRowNum();
 92         return rowCount;
 93     }
 94 
 95     /**
 96      * 读取指定sheetNum的rowCount
 97      * 
 98      * @param sheetNum
 99      * @return int
100      */
101     public int getRowCount(int sheetNum) {
102         HSSFSheet sheet = wb.getSheetAt(sheetNum);
103         int rowCount = -1;
104         rowCount = sheet.getLastRowNum();
105         return rowCount;
106     }
107 
108     /**
109      * 得到指定行的内容
110      * 
111      * @param lineNum
112      * @return String[]
113      */
114     public String[] readExcelLine(int lineNum) {
115         return readExcelLine(this.sheetNum, lineNum);
116     }
117 
118     /**
119      * 指定工作表和行数的内容
120      * 
121      * @param sheetNum
122      * @param lineNum
123      * @return String[]
124      */
125     public String[] readExcelLine(int sheetNum, int lineNum) {
126         if (sheetNum < 0 || lineNum < 0)
127             return null;
128         String[] strExcelLine = null;
129         try {
130             sheet = wb.getSheetAt(sheetNum);
131             row = sheet.getRow(lineNum);
132 
133             int cellCount = row.getLastCellNum();
134             strExcelLine = new String[cellCount + 1];
135             for (int i = 0; i <= cellCount; i++) {
136                 strExcelLine[i] = readStringExcelCell(lineNum, i);
137             }
138         } catch (Exception e) {
139             e.printStackTrace();
140         }
141         return strExcelLine;
142     }
143 
144     /**
145      * 读取指定列的内容
146      * 
147      * @param cellNum
148      * @return String
149      */
150     public String readStringExcelCell(int cellNum) {
151         return readStringExcelCell(this.rowNum, cellNum);
152     }
153 
154     /**
155      * 指定行和列编号的内容
156      * 
157      * @param rowNum
158      * @param cellNum
159      * @return String
160      */
161     public String readStringExcelCell(int rowNum, int cellNum) {
162         return readStringExcelCell(this.sheetNum, rowNum, cellNum);
163     }
164 
165     /**
166      * 指定工作表、行、列下的内容
167      * 
168      * @param sheetNum
169      * @param rowNum
170      * @param cellNum
171      * @return String
172      */
173     public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
174         if (sheetNum < 0 || rowNum < 0)
175             return "";
176         String strExcelCell = "";
177         try {
178             sheet = wb.getSheetAt(sheetNum);
179             row = sheet.getRow(rowNum);
180 
181             if (row.getCell((short) cellNum) != null) { // add this condition
182                 // judge
183                 switch (row.getCell((short) cellNum).getCellType()) {
184                 case HSSFCell.CELL_TYPE_FORMULA:
185                     strExcelCell = "FORMULA ";
186                     break;
187                 case HSSFCell.CELL_TYPE_NUMERIC: {
188                     if (HSSFDateUtil.isCellDateFormatted(row.getCell((short) cellNum))) {// 处理日期格式、时间格式   
189                         SimpleDateFormat sdf = null;   
190                         if (row.getCell((short) cellNum).getCellStyle().getDataFormat() == HSSFDataFormat   
191                                 .getBuiltinFormat("h:mm")) {   
192                             sdf = new SimpleDateFormat("HH:mm");   
193                         } else {// 日期   
194                             sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");   
195                         }   
196                         Date date = row.getCell((short) cellNum).getDateCellValue();   
197                         strExcelCell = sdf.format(date);   
198                     } else if (row.getCell((short) cellNum).getCellStyle().getDataFormat() == 58) {   
199                         // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)   
200                         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");   
201                         double value = row.getCell((short) cellNum).getNumericCellValue();   
202                         Date date = org.apache.poi.ss.usermodel.DateUtil   
203                         .getJavaDate(value);   
204                         strExcelCell = sdf.format(date);   
205                     } else {   
206                         double value = row.getCell((short) cellNum).getNumericCellValue();   
207                         CellStyle style = row.getCell((short) cellNum).getCellStyle();   
208                         DecimalFormat format = new DecimalFormat("0.0");   
209                         String temp = style.getDataFormatString();   
210                         // 单元格设置成常规   
211                         if (temp.equals("General")) {   
212                             format.applyPattern("#.#");   
213                         }   
214                         strExcelCell = format.format(value);   
215                     }
216                 }
217                 break;
218                 case HSSFCell.CELL_TYPE_STRING:
219                     strExcelCell = row.getCell((short) cellNum)
220                     .getStringCellValue();
221                     break;
222                 case HSSFCell.CELL_TYPE_BLANK:
223                     strExcelCell = "";
224                     break;
225                 default:
226                     strExcelCell = "";
227                     break;
228                 }
229             }
230         } catch (Exception e) {
231             e.printStackTrace();
232         }
233         return strExcelCell;
234     }
235 
236     public static void main(String args[]) {
237         File file = new File("d:\无锡妇幼保健人员对应培训项目确认表(返）.xls");
238         ExcelReader readExcel = new ExcelReader(file);
239         try {
240             readExcel.open();
241         } catch (IOException e) {
242             e.printStackTrace();
243         }
244         readExcel.setSheetNum(0); // 设置读取索引为0的工作表
245         // 总行数
246         int count = readExcel.getRowCount();
247         for (int i = 0; i <= count; i++) {
248             String[] rows = readExcel.readExcelLine(i);
249             for (int j = 0; j < rows.length; j++) {
250                 System.out.print(rows[j] + " ");
251             }
252             System.out.print("
");
253         }
254     }
255 }