使用POI读写EXCEL文件

读SHEET内容：

写入红框中内容：

 

执行结果：

 

导入jar包：

 

类和代码：

[java]  view plain copy print ?

1. package com.itcast.poi.helloworld;  
2.   
3. import java.io.File;  
4. import java.io.FileInputStream;  
5. import java.io.FileOutputStream;  
6. import java.io.IOException;  
7. import java.math.BigDecimal;  
8. import java.text.SimpleDateFormat;  
9. import java.util.Date;  
10. import java.util.HashMap;  
11. import java.util.LinkedList;  
12. import java.util.List;  
13. import java.util.Map;  
14.   
15. import org.apache.poi.ss.usermodel.Cell;  
16. import org.apache.poi.ss.usermodel.CellStyle;  
17. import org.apache.poi.ss.usermodel.DataFormat;  
18. import org.apache.poi.ss.usermodel.DateUtil;  
19. import org.apache.poi.ss.usermodel.Row;  
20. import org.apache.poi.ss.usermodel.Sheet;  
21. import org.apache.poi.ss.usermodel.Workbook;  
22. import org.apache.poi.ss.usermodel.WorkbookFactory;  
23.   
24. import com.itcast.poi.util.NumberUtils;  
25.   
26. /** 
27.  * POI3.9版本 工具类 版本01 
28.  * @author 吕鹏 
29.  * @date 2012-12-22 
30.  */  
31. public class PoiHelloworld {  
32.     static SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd");  
33.     static Map<String,CellStyle> styleMap = new HashMap<String,CellStyle>(); //存储单元格样式的Map  
34.       
35.       
36.     public static void main(String[] args) {  
37.         /** 
38.          * 读文件 
39.          */  
40.         readExcel("D:/a.xlsx");  
41.           
42.         /** 
43.          * 写文件 
44.          */  
45.         testWrite("D:/a.xlsx","D:/b.xlsx");  
46.     }  
47.       
48.     /** 
49.      * 读excel  
50.      * @param filePath excel路径 
51.      */  
52.     public static  void readExcel(String filePath){  
53.         Workbook book = null;  
54.         try {  
55.             book = getExcelWorkbook(filePath);  
56.             Sheet sheet = getSheetByNum(book,1);  
57.             System.out.println("sheet名称是："+sheet.getSheetName());  
58.               
59.             int lastRowNum = sheet.getLastRowNum();  
60.               
61.             Row row = null;  
62.             for(int i=0;i<=lastRowNum;i++){  
63.                 row = sheet.getRow(i);  
64.                 if(row != null){  
65.                     System.out.println("正在读第"+(i+1)+"行：");  
66.                     int lastCellNum = row.getLastCellNum();  
67.                     Cell cell = null;  
68.                     StringBuilder sb = null;  
69.                     for(int j=0;j<lastCellNum;j++){  
70.                         cell = row.getCell(j);  
71.                         if(cell != null){  
72.                             sb = new StringBuilder("第"+(j+1)+"列的单元格内容是：");  
73.                             String type_cn = null;  
74.                             String type_style = cell.getCellStyle().getDataFormatString().toUpperCase();  
75.                             String type_style_cn = getCellStyleByChinese(type_style);  
76.                             int type = cell.getCellType();  
77.                             String value = "";  
78.                             switch (type) {  
79.                                 case 0:  
80.                                     if(DateUtil.isCellDateFormatted(cell)){  
81.                                         type_cn = "NUMBER-DATE";  
82.                                         Date date = cell.getDateCellValue();  
83.                                         value = sFormat.format(date);  
84.                                     }else {  
85.                                         type_cn = "NUMBER";  
86.                                         double tempValue = cell.getNumericCellValue();  
87.                                         value = String.valueOf(tempValue);  
88.                                     }  
89.                                     break;  
90.                                 case 1:  
91.                                     type_cn = "STRING";  
92.                                     value = cell.getStringCellValue();  
93.                                     break;  
94.                                 case 2:  
95.                                     type_cn = "FORMULA";  
96.                                     value = cell.getCellFormula();  
97.                                     break;  
98.                                 case 3:  
99.                                     type_cn = "BLANK";  
100.                                     value = cell.getStringCellValue();  
101.                                     break;  
102.                                 case 4:  
103.                                     type_cn = "BOOLEAN";  
104.                                     boolean tempValue = cell.getBooleanCellValue();  
105.                                     value = String.valueOf(tempValue);  
106.                                     break;  
107.                                 case 5:  
108.                                     type_cn = "ERROR";  
109.                                     byte b = cell.getErrorCellValue();  
110.                                     value = String.valueOf(b);  
111.                                 default:  
112.                                     break;  
113.                             }  
114.                             sb.append(value + ",内容类型是："+type_cn+",单元格的格式是："+type_style_cn);  
115.                             System.out.println(sb.toString());  
116.                         }  
117.                     }  
118.                 }  
119.             }  
120.         } catch (Exception e) {  
121.             e.printStackTrace();  
122.         }   
123.     }  
124.   
125.       
126.     /** 
127.      * 根据单元格的格式 返回单元格的格式中文 
128.      * @param type_style 
129.      * @return 
130.      */  
131.     private static String getCellStyleByChinese(String type_style) {  
132.         String cell_style_cn = "";  
133.         if(type_style.contains("GENERAL")){  
134.             cell_style_cn = "常规";  
135.         }else if(type_style.equals("_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * \"-\"??_ ;_ @_ ")){  
136.             cell_style_cn = "会计专用";  
137.         }else if(type_style.equals("0")){  
138.             cell_style_cn = "整数";  
139.         }else if(type_style.contains("YYYY/MM") || type_style.contains("YYYY\\-MM")){  
140.             cell_style_cn = "日期";  
141.         }else if(type_style.equals("0.00%")){  
142.             cell_style_cn = "百分比";  
143.         }else {  
144.             cell_style_cn = "不符合规定格式类型:"+type_style;  
145. //          cell_style_cn = type_style;  
146.         }  
147.         return cell_style_cn;  
148.     }  
149.       
150.       
151.     /** 
152.      * 写内容到excel中 
153.      * @throws IOException  
154.      */  
155.     public static void testWrite(String srcFilePath,String tarFilePath){  
156.         FileOutputStream out = null;  
157.         try {  
158.             Workbook book = getExcelWorkbook(srcFilePath);  
159.             Sheet sheet = getSheetByNum(book,1);  
160.               
161.             Map<String,String> map = new HashMap<String, String>();  
162.             List<Map<String,String>> list = new LinkedList<Map<String,String>>();  
163.             map.put("A", "4,INT");  
164.             map.put("B", "小红,GENERAL");  
165.             map.put("C", "18,INT");  
166.             map.put("D", "1990-03-10,DATE");  
167.             map.put("E", "0.056,PERCENT");  
168.             map.put("F", "4800,DOUBLE");  
169.             list.add(map);  
170.               
171.             int startRow = 6;  
172.             boolean result = writeToExcel(list, sheet,startRow);  
173.             if(result){  
174.                 out = new FileOutputStream(tarFilePath);  
175.                 book.write(out);  
176.                 System.out.println("文件写入完成！");  
177.             }  
178.         } catch (Exception e) {  
179.             e.printStackTrace();  
180.         } finally {  
181.             try {  
182.                 out.close();  
183.             } catch (IOException e) {  
184.                 e.printStackTrace();  
185.             }  
186.         }  
187.     }  
188.       
189.     /** 
190.      * 将传入的内容写入到excel中sheet里 
191.      * @param list 
192.      */  
193.     public static boolean writeToExcel(List<Map<String,String>> list,Sheet sheet,int startRow){  
194.         boolean result = false;  
195.         try {  
196.             Map<String,String> map = null;  
197.             Row row = null;  
198.             for(int i=0;i<list.size();i++){  
199.                 map = list.get(i);  
200.                 row = sheet.getRow(startRow-1);  
201.                 if(row == null){  
202.                     row = sheet.createRow(startRow-1);  
203.                 }  
204.                 startRow ++;  
205.                 Cell cell = null;  
206.                   
207.                 BigDecimal db = null;  
208.                 for(Map.Entry<String,String> entry : map.entrySet()){  
209.                     String key = entry.getKey();  
210.                     int colNum = NumberUtils.toNum_new(key)-1;  
211.                       
212.                     String value_type = entry.getValue();  
213.                     String value = value_type.split(",")[0];  
214.                     String style = value_type.split(",")[1];  
215.                       
216.                     cell = row.getCell(colNum);  
217.                     if(cell == null){  
218.                         cell = row.createCell(colNum);  
219.                     }  
220.                     if(style.equals("GENERAL")){  
221.                         cell.setCellValue(value);  
222.                     }else{  
223.                         if(style.equals("DOUBLE") || style.equals("INT")){  
224.                             db = new BigDecimal(value,java.math.MathContext.UNLIMITED);  
225.                             cell.setCellValue(db.doubleValue());  
226.                         }else if(style.equals("PERCENT")){  
227.                             db = new BigDecimal(value,java.math.MathContext.UNLIMITED);  
228.                             cell.setCellValue(db.doubleValue());  
229.                         }else if(style.equals("DATE")){  
230.                             java.util.Date date = sFormat.parse(value);  
231.                             cell.setCellValue(date);  
232.                         }  
233.                         cell.setCellStyle(styleMap.get(style));  
234.                     }  
235.                 }  
236.             }  
237.             result = true;  
238.         } catch (Exception e) {  
239.             e.printStackTrace();  
240.             throw new RuntimeException(e.getMessage());  
241.         }  
242.         return result;  
243.     }  
244.     /** 
245.      * 获取excel的Workbook 
246.      * @throws IOException  
247.      */  
248.     public static Workbook getExcelWorkbook(String filePath) throws IOException{  
249.         Workbook book = null;  
250.         File file  = null;  
251.         FileInputStream fis = null;   
252.           
253.         try {  
254.             file = new File(filePath);  
255.             if(!file.exists()){  
256.                 throw new RuntimeException("文件不存在");  
257.             }else{  
258.                 fis = new FileInputStream(file);  
259.                 book = WorkbookFactory.create(fis);  
260.                 initStyleMap(book);  
261.             }  
262.         } catch (Exception e) {  
263.             throw new RuntimeException(e.getMessage());  
264.         } finally {  
265.             if(fis != null){  
266.                 fis.close();  
267.             }  
268.         }  
269.         return book;  
270.     }  
271.       
272.     /** 
273.      * 根据索引 返回Sheet 
274.      * @param number 
275.      */  
276.     public static Sheet getSheetByNum(Workbook book,int number){  
277.         Sheet sheet = null;  
278.         try {  
279.             sheet = book.getSheetAt(number-1);  
280. //          if(sheet == null){  
281. //              sheet = book.createSheet("Sheet"+number);  
282. //          }  
283.         } catch (Exception e) {  
284.             throw new RuntimeException(e.getMessage());  
285.         }  
286.         return sheet;  
287.     }  
288.       
289.     /** 
290.      * 初始化格式Map 
291.      */  
292.       
293.       
294.     public static void initStyleMap(Workbook book){  
295.         DataFormat hssfDF = book.createDataFormat();  
296.           
297.         CellStyle doubleStyle = book.createCellStyle(); //会计专用  
298.         doubleStyle.setDataFormat(hssfDF.getFormat("_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * \"-\"??_ ;_ @_ ")); //poi写入后为会计专用  
299.         styleMap.put("DOUBLE", doubleStyle);  
300.           
301.         CellStyle intStyle = book.createCellStyle(); //会计专用  
302.         intStyle.setDataFormat(hssfDF.getFormat("0")); //poi写入后为会计专用  
303.         styleMap.put("INT", intStyle);  
304.           
305.         CellStyle yyyyMMddStyle = book.createCellStyle();//日期yyyyMMdd  
306.         yyyyMMddStyle.setDataFormat(hssfDF.getFormat("yyyy-MM-dd"));  
307.         styleMap.put("DATE", yyyyMMddStyle);  
308.           
309.         CellStyle percentStyle = book.createCellStyle();//百分比  
310.         percentStyle.setDataFormat(hssfDF.getFormat("0.00%"));  
311.         styleMap.put("PERCENT", percentStyle);  
312.     }  
313. }  

 

 附加上 数字转字母的方法：

[java]  view plain copy print ?

1. public static String toLetterString(int number) {  
2.         if (number < 1) {//   
3.             return null;  
4.         }  
5.         if (number < 27) {  
6.             return String.valueOf((char) ('A' + number - 1));  
7.         }  
8.         if (number % 26 == 0) {  
9.             return toLetterString(number / 26 - 1) + "Z";  
10.         }  
11.         return toLetterString(number / 26)+ String.valueOf((char) ('A' + number % 26 - 1));  
12.     }  

 

源代码地址：

http://download.csdn.net/detail/qq522935502/4986839 

文章来自：http://blog.csdn.net/qq522935502/article/details/8374118