Java读取Excel文件

1.jxl,poi简介

Java读取Excel文件有两种途径，jxl跟poi方式，其中jxl只能处理xls的文件，现已停止更新，而poi方式可以处理xls跟xlsx文件。推荐使用poi方式。

相关jar包及api文档、demo下载请参考 http://pan.baidu.com/s/1nvTiRf7

2.基于poi工具生成自己util类

[html] view plain copy

1. import java.io.ByteArrayOutputStream;  
2. import java.io.File;  
3. import java.io.FileInputStream;  
4. import java.io.FileOutputStream;  
5. import java.io.IOException;  
6. import java.io.OutputStream;  
7. import java.text.DecimalFormat;  
8. import java.text.SimpleDateFormat;  
9. import java.util.ArrayList;  
10.   
11. import org.apache.poi.hssf.usermodel.HSSFCell;  
12. import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
13. import org.apache.poi.hssf.usermodel.HSSFRow;  
14. import org.apache.poi.hssf.usermodel.HSSFSheet;  
15. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
16. import org.apache.poi.xssf.usermodel.XSSFCell;  
17. import org.apache.poi.xssf.usermodel.XSSFRow;  
18. import org.apache.poi.xssf.usermodel.XSSFSheet;  
19. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
20.   
21. public class ExcelUtil {  
22.     //默认单元格内容为数字时格式  
23.     private static DecimalFormat df = new DecimalFormat("0");  
24.     // 默认单元格格式化日期字符串   
25.     private static SimpleDateFormat sdf = new SimpleDateFormat(  "yyyy-MM-dd HH:mm:ss");   
26.     // 格式化数字  
27.     private static DecimalFormat nf = new DecimalFormat("0.00");    
28.     public static ArrayList<ArrayList<Object>> readExcel(File file){  
29.         if(file == null){  
30.             return null;  
31.         }  
32.         if(file.getName().endsWith("xlsx")){  
33.             //处理ecxel2007  
34.             return readExcel2007(file);  
35.         }else{  
36.             //处理ecxel2003  
37.             return readExcel2003(file);  
38.         }  
39.     }  
40.     /*  
41.      * @return 将返回结果存储在ArrayList内，存储结构与二位数组类似  
42.      * lists.get(0).get(0)表示过去Excel中0行0列单元格  
43.      */  
44.     public static ArrayList<ArrayList<Object>> readExcel2003(File file){  
45.         try{  
46.             ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();  
47.             ArrayList<Object> colList;  
48.             HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));  
49.             HSSFSheet sheet = wb.getSheetAt(0);  
50.             HSSFRow row;  
51.             HSSFCell cell;  
52.             Object value;  
53.             for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){  
54.                 row = sheet.getRow(i);  
55.                 colList = new ArrayList<Object>();  
56.                 if(row == null){  
57.                     //当读取行为空时  
58.                     if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行  
59.                         rowList.add(colList);  
60.                     }  
61.                     continue;  
62.                 }else{  
63.                     rowCount++;  
64.                 }  
65.                 for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){  
66.                     cell = row.getCell(j);  
67.                     if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){  
68.                         //当该单元格为空  
69.                         if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格  
70.                             colList.add("");  
71.                         }  
72.                         continue;  
73.                     }  
74.                     switch(cell.getCellType()){  
75.                      case XSSFCell.CELL_TYPE_STRING:    
76.                             System.out.println(i + "行" + j + " 列 is String type");    
77.                             value = cell.getStringCellValue();    
78.                             break;    
79.                         case XSSFCell.CELL_TYPE_NUMERIC:    
80.                             if ("@".equals(cell.getCellStyle().getDataFormatString())) {    
81.                                 value = df.format(cell.getNumericCellValue());    
82.                             } else if ("General".equals(cell.getCellStyle()    
83.                                     .getDataFormatString())) {    
84.                                 value = nf.format(cell.getNumericCellValue());    
85.                             } else {    
86.                                 value = sdf.format(HSSFDateUtil.getJavaDate(cell    
87.                                         .getNumericCellValue()));    
88.                             }    
89.                             System.out.println(i + "行" + j    
90.                                     + " 列 is Number type ; DateFormt:"    
91.                                     + value.toString());   
92.                             break;    
93.                         case XSSFCell.CELL_TYPE_BOOLEAN:    
94.                             System.out.println(i + "行" + j + " 列 is Boolean type");    
95.                             value = Boolean.valueOf(cell.getBooleanCellValue());  
96.                             break;    
97.                         case XSSFCell.CELL_TYPE_BLANK:    
98.                             System.out.println(i + "行" + j + " 列 is Blank type");    
99.                             value = "";    
100.                             break;    
101.                         default:    
102.                             System.out.println(i + "行" + j + " 列 is default type");    
103.                             value = cell.toString();    
104.                     }// end switch  
105.                     colList.add(value);  
106.                 }//end for j  
107.                 rowList.add(colList);  
108.             }//end for i  
109.               
110.             return rowList;  
111.         }catch(Exception e){  
112.             return null;  
113.         }  
114.     }  
115.       
116.     public static ArrayList<ArrayList<Object>> readExcel2007(File file){  
117.         try{  
118.             ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();  
119.             ArrayList<Object> colList;  
120.             XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));  
121.             XSSFSheet sheet = wb.getSheetAt(0);  
122.             XSSFRow row;  
123.             XSSFCell cell;  
124.             Object value;  
125.             for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){  
126.                 row = sheet.getRow(i);  
127.                 colList = new ArrayList<Object>();  
128.                 if(row == null){  
129.                     //当读取行为空时  
130.                     if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行  
131.                         rowList.add(colList);  
132.                     }  
133.                     continue;  
134.                 }else{  
135.                     rowCount++;  
136.                 }  
137.                 for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){  
138.                     cell = row.getCell(j);  
139.                     if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){  
140.                         //当该单元格为空  
141.                         if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格  
142.                             colList.add("");  
143.                         }  
144.                         continue;  
145.                     }  
146.                     switch(cell.getCellType()){  
147.                      case XSSFCell.CELL_TYPE_STRING:    
148.                             System.out.println(i + "行" + j + " 列 is String type");    
149.                             value = cell.getStringCellValue();    
150.                             break;    
151.                         case XSSFCell.CELL_TYPE_NUMERIC:    
152.                             if ("@".equals(cell.getCellStyle().getDataFormatString())) {    
153.                                 value = df.format(cell.getNumericCellValue());    
154.                             } else if ("General".equals(cell.getCellStyle()    
155.                                     .getDataFormatString())) {    
156.                                 value = nf.format(cell.getNumericCellValue());    
157.                             } else {    
158.                                 value = sdf.format(HSSFDateUtil.getJavaDate(cell    
159.                                         .getNumericCellValue()));    
160.                             }    
161.                             System.out.println(i + "行" + j    
162.                                     + " 列 is Number type ; DateFormt:"    
163.                                     + value.toString());   
164.                             break;    
165.                         case XSSFCell.CELL_TYPE_BOOLEAN:    
166.                             System.out.println(i + "行" + j + " 列 is Boolean type");    
167.                             value = Boolean.valueOf(cell.getBooleanCellValue());  
168.                             break;    
169.                         case XSSFCell.CELL_TYPE_BLANK:    
170.                             System.out.println(i + "行" + j + " 列 is Blank type");    
171.                             value = "";    
172.                             break;    
173.                         default:    
174.                             System.out.println(i + "行" + j + " 列 is default type");    
175.                             value = cell.toString();    
176.                     }// end switch  
177.                     colList.add(value);  
178.                 }//end for j  
179.                 rowList.add(colList);  
180.             }//end for i  
181.               
182.             return rowList;  
183.         }catch(Exception e){  
184.             System.out.println("exception");  
185.             return null;  
186.         }  
187.     }  
188.       
189.     public static void writeExcel(ArrayList<ArrayList<Object>> result,String path){  
190.         if(result == null){  
191.             return;  
192.         }  
193.         HSSFWorkbook wb = new HSSFWorkbook();  
194.         HSSFSheet sheet = wb.createSheet("sheet1");  
195.         for(int i = 0 ;i < result.size() ; i++){  
196.              HSSFRow row = sheet.createRow(i);  
197.             if(result.get(i) != null){  
198.                 for(int j = 0; j < result.get(i).size() ; j ++){  
199.                     HSSFCell cell = row.createCell(j);  
200.                     cell.setCellValue(result.get(i).get(j).toString());  
201.                 }  
202.             }  
203.         }  
204.         ByteArrayOutputStream os = new ByteArrayOutputStream();  
205.         try  
206.         {  
207.             wb.write(os);  
208.         } catch (IOException e){  
209.             e.printStackTrace();  
210.         }  
211.         byte[] content = os.toByteArray();  
212.         File file = new File(path);//Excel文件生成后存储的位置。  
213.         OutputStream fos  = null;  
214.         try  
215.         {  
216.             fos = new FileOutputStream(file);  
217.             fos.write(content);  
218.             os.close();  
219.             fos.close();  
220.         }catch (Exception e){  
221.             e.printStackTrace();  
222.         }             
223.     }  
224.       
225.     public static DecimalFormat getDf() {  
226.         return df;  
227.     }  
228.     public static void setDf(DecimalFormat df) {  
229.         ExcelUtil.df = df;  
230.     }  
231.     public static SimpleDateFormat getSdf() {  
232.         return sdf;  
233.     }  
234.     public static void setSdf(SimpleDateFormat sdf) {  
235.         ExcelUtil.sdf = sdf;  
236.     }  
237.     public static DecimalFormat getNf() {  
238.         return nf;  
239.     }  
240.     public static void setNf(DecimalFormat nf) {  
241.         ExcelUtil.nf = nf;  
242.     }  
243.       
244.       
245.       
246. }  

3.使用方式

[html] view plain copy

1. public static void main(String[] args) {  
2.         File file = new File("F:/excel/a.xlsx");  
3.         ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(file);  
4.         for(int i = 0 ;i < result.size() ;i++){  
5.             for(int j = 0;j<result.get(i).size(); j++){  
6.                 System.out.println(i+"行 "+j+"列  "+ result.get(i).get(j).toString());  
7.             }  
8.         }  
9.         ExcelUtil.writeExcel(result,"F:/excel/bb.xls");  
10.     }  

4.AndroidStudio 使用

   请 查看后面链接 https://github.com/sqyNick/CExcelUtil