【JAVA】apache poi excel 文件读取，各种数据类型，不规则excel格式都可以读取

本文将通过实例来介绍apache poi  读取excel的原理，包括各种数据类型的处理，本文提供的代码非常通用，即使不规则的excel文件，也可以读取。

直接看代码吧

[java] view plain copy print ?

1. package poi.excel;  
2.   
3. import java.io.File;  
4. import java.text.DecimalFormat;  
5. import java.util.ArrayList;  
6. import java.util.List;  
7.   
8. import org.apache.commons.lang3.StringUtils;  
9. import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
10. import org.apache.poi.ss.usermodel.Cell;  
11. import org.apache.poi.ss.usermodel.Row;  
12. import org.apache.poi.ss.usermodel.Sheet;  
13. import org.apache.poi.ss.usermodel.Workbook;  
14. import org.apache.poi.ss.usermodel.WorkbookFactory;  
15.   
16. /** 
17.  * ClassName:ExcelFileParser <br/> 
18.  * Function: TODO ADD FUNCTION. <br/> 
19.  * Reason: TODO ADD REASON. <br/> 
20.  * Date: 2015年12月29日 上午9:26:21 <br/> 
21.  *  
22.  * @author chiwei 
23.  * @version 
24.  * @since JDK 1.6 
25.  * @see 
26.  */  
27. public class ExcelFileParser {  
28.   
29.     public static Workbook getWb(String path) {  
30.         try {  
31.             return WorkbookFactory.create(new File(path));  
32.         } catch (Exception e) {  
33.             throw new RuntimeException("读取EXCEL文件出错", e);  
34.         }  
35.     }  
36.   
37.     public static Sheet getSheet(Workbook wb, int sheetIndex) {  
38.         if (wb == null) {  
39.             throw new RuntimeException("工作簿对象为空");  
40.         }  
41.         int sheetSize = wb.getNumberOfSheets();  
42.         if (sheetIndex < 0 || sheetIndex > sheetSize - 1) {  
43.             throw new RuntimeException("工作表获取错误");  
44.         }  
45.         return wb.getSheetAt(sheetIndex);  
46.     }  
47.   
48.     public static List<List<String>> getExcelRows(Sheet sheet, int startLine, int endLine) {  
49.         List<List<String>> list = new ArrayList<List<String>>();  
50.         // 如果开始行号和结束行号都是-1的话，则全表读取  
51.         if (startLine == -1)  
52.             startLine = 0;  
53.         if (endLine == -1) {  
54.             endLine = sheet.getLastRowNum() + 1;  
55.         } else {  
56.             endLine += 1;  
57.         }  
58.         for (int i = startLine; i < endLine; i++) {  
59.             Row row = sheet.getRow(i);  
60.             if (row == null) {  
61.                 System.out.println("该行为空，直接跳过");  
62.                 continue;  
63.             }  
64.             int rowSize = row.getLastCellNum();  
65.             List<String> rowList = new ArrayList<String>();  
66.             for (int j = 0; j < rowSize; j++) {  
67.                 Cell cell = row.getCell(j);  
68.                 String temp = "";  
69.                 if (cell == null) {  
70.                     System.out.println("该列为空，赋值双引号");  
71.                     temp = "NULL";  
72.                 } else {  
73.                     int cellType = cell.getCellType();  
74.                     switch (cellType) {  
75.                     case Cell.CELL_TYPE_STRING:  
76.                         temp = cell.getStringCellValue().trim();  
77.                         temp = StringUtils.isEmpty(temp) ? "NULL" : temp;  
78.                         break;  
79.                     case Cell.CELL_TYPE_BOOLEAN:  
80.                         temp = String.valueOf(cell.getBooleanCellValue());  
81.                         break;  
82.                     case Cell.CELL_TYPE_FORMULA:  
83.                         temp = String.valueOf(cell.getCellFormula().trim());  
84.                         break;  
85.                     case Cell.CELL_TYPE_NUMERIC:  
86.                         if (HSSFDateUtil.isCellDateFormatted(cell)) {  
87.                             temp = DateUtil.parseToString(cell.getDateCellValue(),  
88.                                     DateUtil.FORMAT_DATE);  
89.                         } else {  
90.                             temp = new DecimalFormat("#.######").format(cell.getNumericCellValue());  
91.                         }  
92.                         break;  
93.                     case Cell.CELL_TYPE_BLANK:  
94.                         temp = "NULL";  
95.                         break;  
96.                     case Cell.CELL_TYPE_ERROR:  
97.                         temp = "ERROR";  
98.                         break;  
99.                     default:  
100.                         temp = cell.toString().trim();  
101.                         break;  
102.                     }  
103.                 }  
104.                 rowList.add(temp);  
105.             }  
106.             list.add(rowList);  
107.         }  
108.         return list;  
109.     }  
110.   
111.     public static void main(String a[]) {  
112.         String path = "D:\\test.xlsx";  
113.         Workbook wb = getWb(path);  
114.         List<List<String>> list = getExcelRows(getSheet(wb, 0), -1, -1);  
115.         for (int i = 0; i < list.size(); i++) {  
116.             List<String> row = list.get(i);  
117.             for (int j = 0; j < row.size(); j++) {  
118.                 System.out.print(row.get(j) + "\t");  
119.             }  
120.             System.out.println();  
121.         }  
122.     }  
123.   
124. }  

package poi.excel;

import java.io.File;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * ClassName:ExcelFileParser <br/>
 * Function: TODO ADD FUNCTION. <br/>
 * Reason: TODO ADD REASON. <br/>
 * Date: 2015年12月29日 上午9:26:21 <br/>
 * 
 * @author chiwei
 * @version
 * @since JDK 1.6
 * @see
 */
public class ExcelFileParser {

	public static Workbook getWb(String path) {
		try {
			return WorkbookFactory.create(new File(path));
		} catch (Exception e) {
			throw new RuntimeException("读取EXCEL文件出错", e);
		}
	}

	public static Sheet getSheet(Workbook wb, int sheetIndex) {
		if (wb == null) {
			throw new RuntimeException("工作簿对象为空");
		}
		int sheetSize = wb.getNumberOfSheets();
		if (sheetIndex < 0 || sheetIndex > sheetSize - 1) {
			throw new RuntimeException("工作表获取错误");
		}
		return wb.getSheetAt(sheetIndex);
	}

	public static List<List<String>> getExcelRows(Sheet sheet, int startLine, int endLine) {
		List<List<String>> list = new ArrayList<List<String>>();
		// 如果开始行号和结束行号都是-1的话，则全表读取
		if (startLine == -1)
			startLine = 0;
		if (endLine == -1) {
			endLine = sheet.getLastRowNum() + 1;
		} else {
			endLine += 1;
		}
		for (int i = startLine; i < endLine; i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				System.out.println("该行为空，直接跳过");
				continue;
			}
			int rowSize = row.getLastCellNum();
			List<String> rowList = new ArrayList<String>();
			for (int j = 0; j < rowSize; j++) {
				Cell cell = row.getCell(j);
				String temp = "";
				if (cell == null) {
					System.out.println("该列为空，赋值双引号");
					temp = "NULL";
				} else {
					int cellType = cell.getCellType();
					switch (cellType) {
					case Cell.CELL_TYPE_STRING:
						temp = cell.getStringCellValue().trim();
						temp = StringUtils.isEmpty(temp) ? "NULL" : temp;
						break;
					case Cell.CELL_TYPE_BOOLEAN:
						temp = String.valueOf(cell.getBooleanCellValue());
						break;
					case Cell.CELL_TYPE_FORMULA:
						temp = String.valueOf(cell.getCellFormula().trim());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							temp = DateUtil.parseToString(cell.getDateCellValue(),
									DateUtil.FORMAT_DATE);
						} else {
							temp = new DecimalFormat("#.######").format(cell.getNumericCellValue());
						}
						break;
					case Cell.CELL_TYPE_BLANK:
						temp = "NULL";
						break;
					case Cell.CELL_TYPE_ERROR:
						temp = "ERROR";
						break;
					default:
						temp = cell.toString().trim();
						break;
					}
				}
				rowList.add(temp);
			}
			list.add(rowList);
		}
		return list;
	}

	public static void main(String a[]) {
		String path = "D:\\test.xlsx";
		Workbook wb = getWb(path);
		List<List<String>> list = getExcelRows(getSheet(wb, 0), -1, -1);
		for (int i = 0; i < list.size(); i++) {
			List<String> row = list.get(i);
			for (int j = 0; j < row.size(); j++) {
				System.out.print(row.get(j) + "\t");
			}
			System.out.println();
		}
	}

}

该测试excel文件包括两个sheet

该代码是将excel的单元格内容全部按照返回string来处理，方便通用

maven 依赖如下：

[html] view plain copy print ?

1. <dependency>  
2.             <groupId>joda-time</groupId>  
3.             <artifactId>joda-time</artifactId>  
4.             <version>2.6</version>  
5.         </dependency>  

<dependency>
			<groupId>joda-time</groupId>
			<artifactId>joda-time</artifactId>
			<version>2.6</version>
		</dependency>

[html] view plain copy print ?

1. <dependency>  
2.             <groupId>org.apache.poi</groupId>  
3.             <artifactId>poi</artifactId>  
4.             <version>3.11</version>  
5.         </dependency>  
6.         <dependency>  
7.             <groupId>org.apache.poi</groupId>  
8.             <artifactId>poi-ooxml</artifactId>  
9.             <version>3.11</version>  
10.         </dependency>  

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>

joda-time是针对日期类型哪里转字符串用的，线程安全的类，建议大家不要再用SimpleDateFormat了

代码流程：

首先根据文件路径去创建一个工作簿对象Workbook

然后通过工作簿对象获取一个工作表对象Sheet

最后对工作表的row进行遍历

注意获取每行的列数，我用的row.getLastCellNum();而不是row.getPhysicalNumberOfCells()

这个方法就可以处理不规则的excel单元格内容了，即某一行3列，某一行4列，5列之类的

然后对行循环内存的单元列也要进行空判断，防止异常

代码提供的方法非常通用，只需传进去一个文件路径，和几个必要的参数即可，有其它需求的话，在此代码上二次开发非常简单

有问题，请留言探讨！

阅读全文

版权声明：本文为博主原创文章，未经博主允许不得转载。 

原文地址：http://blog.csdn.net/simonchi/article/details/50433219