poi-3.7-20101029.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107089
geronimo-stax-api_1.0_spec-1.0.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107083
xmlbeans-2.3.0.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107140
poi-ooxml-3.7-20101029.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107145
poi-ooxml-schemas-3.7-20101029.jar,下载地址:http://download.csdn.net/detail/evangel_z/4108997
以上5个jar,就可读取Excel 2003;
-----------------------------------------------------------------------------------------------------------------------
读取Excel 2007,请加上dom4j-1.6.1.jar,下载地址:http://download.csdn.net/detail/evangel_z/6739735
-----------------------------------------------------------------------------------------------------------------------
poi-3.6-20091214.jar,下载地址:http://download.csdn.net/detail/evangel_z/3895051
poi-contrib-3.6-20091214.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107197
poi-scratch.6-20091214.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107204
geronimo-stax-api_1.0_spec-1.0.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107083
xmlbeans-2.3.0.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107140
poi-ooxml-3.7-20101029.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107145
poi-ooxml-schemas-3.7-20101029.jar,下载地址:http://download.csdn.net/detail/evangel_z/4108997
以上5个jar,就可读取Excel 2003;
-----------------------------------------------------------------------------------------------------------------------
读取Excel 2007,请加上dom4j-1.6.1.jar,下载地址:http://download.csdn.net/detail/evangel_z/6739735
-----------------------------------------------------------------------------------------------------------------------
poi-3.6-20091214.jar,下载地址:http://download.csdn.net/detail/evangel_z/3895051
poi-contrib-3.6-20091214.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107197
poi-scratch.6-20091214.jar,下载地址:http://download.csdn.net/detail/evangel_z/4107204
读取excel 文件的 java 代码:
001 | import java.io.File; |
002 | import java.io.FileInputStream; |
003 | import java.io.FileNotFoundException; |
004 | import java.io.IOException; |
005 | import java.text.DecimalFormat; |
006 | import java.text.SimpleDateFormat; |
007 | import java.util.LinkedList; |
008 | import java.util.List; |
009 | import org.apache.poi.hssf.usermodel.HSSFCell; |
010 | import org.apache.poi.hssf.usermodel.HSSFDateUtil; |
011 | import org.apache.poi.hssf.usermodel.HSSFRow; |
012 | import org.apache.poi.hssf.usermodel.HSSFSheet; |
013 | import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
014 | import org.apache.poi.xssf.usermodel.XSSFCell; |
015 | import org.apache.poi.xssf.usermodel.XSSFRow; |
016 | import org.apache.poi.xssf.usermodel.XSSFSheet; |
017 | import org.apache.poi.xssf.usermodel.XSSFWorkbook; |
018 | public class ReadExcel { |
019 | /** |
020 | * 对外提供读取excel 的方法 |
021 | * */ |
022 | public static List<List<Object>> readExcel(File file) throws IOException{ |
023 | String fileName = file.getName(); |
024 | String extension = fileName.lastIndexOf( "." )==- 1 ? "" :fileName.substring(fileName.lastIndexOf( "." )+ 1 ); |
025 | if ( "xls" .equals(extension)){ |
026 | return read2003Excel(file); |
027 | } else if ( "xlsx" .equals(extension)){ |
028 | return read2007Excel(file); |
029 | } else { |
030 | throw new IOException( "不支持的文件类型" ); |
031 | } |
032 | } |
033 | /** |
034 | * 读取 office 2003 excel |
035 | * @throws IOException |
036 | * @throws FileNotFoundException */ |
037 | private static List<List<Object>> read2003Excel(File file) throws IOException{ |
038 | List<List<Object>> list = new LinkedList<List<Object>>(); |
039 | HSSFWorkbook hwb = new HSSFWorkbook( new FileInputStream(file)); |
040 | HSSFSheet sheet = hwb.getSheetAt( 0 ); |
041 | Object value = null ; |
042 | HSSFRow row = null ; |
043 | HSSFCell cell = null ; |
044 | for ( int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows();i++){ |
045 | row = sheet.getRow(i); |
046 | if (row == null ) { |
047 | continue ; |
048 | } |
049 | List<Object> linked = new LinkedList<Object>(); |
050 | for ( int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { |
051 | cell = row.getCell(j); |
052 | if (cell == null ) { |
053 | continue ; |
054 | } |
055 | DecimalFormat df = new DecimalFormat( "0" ); // 格式化 number String 字符 |
056 | SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); // 格式化日期字符串 |
057 | DecimalFormat nf = new DecimalFormat( "0.00" ); // 格式化数字 |
058 | switch (cell.getCellType()) { |
059 | case XSSFCell.CELL_TYPE_STRING: |
060 | System.out.println(i+ "行" +j+ " 列 is String type" ); |
061 | value = cell.getStringCellValue(); |
062 | break ; |
063 | case XSSFCell.CELL_TYPE_NUMERIC: |
064 | System.out.println(i+ "行" +j+ " 列 is Number type ; DateFormt:" +cell.getCellStyle().getDataFormatString()); |
065 | if ( "@" .equals(cell.getCellStyle().getDataFormatString())){ |
066 | value = df.format(cell.getNumericCellValue()); |
067 | } else if ( "General" .equals(cell.getCellStyle().getDataFormatString())){ |
068 | value = nf.format(cell.getNumericCellValue()); |
069 | } else { |
070 | value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); |
071 | } |
072 | break ; |
073 | case XSSFCell.CELL_TYPE_BOOLEAN: |
074 | System.out.println(i+ "行" +j+ " 列 is Boolean type" ); |
075 | value = cell.getBooleanCellValue(); |
076 | break ; |
077 | case XSSFCell.CELL_TYPE_BLANK: |
078 | System.out.println(i+ "行" +j+ " 列 is Blank type" ); |
079 | value = "" ; |
080 | break ; |
081 | default : |
082 | System.out.println(i+ "行" +j+ " 列 is default type" ); |
083 | value = cell.toString(); |
084 | } |
085 | if (value == null || "" .equals(value)) { |
086 | continue ; |
087 | } |
088 | linked.add(value); |
089 | } |
090 | list.add(linked); |
091 | } |
092 | return list; |
093 | } |
094 | /** |
095 | * 读取Office 2007 excel |
096 | * */ |
097 | private static List<List<Object>> read2007Excel(File file) throws IOException { |
098 | List<List<Object>> list = new LinkedList<List<Object>>(); |
099 | // 构造 XSSFWorkbook 对象,strPath 传入文件路径 |
100 | XSSFWorkbook xwb = new XSSFWorkbook( new FileInputStream(file)); |
101 | // 读取第一章表格内容 |
102 | XSSFSheet sheet = xwb.getSheetAt( 0 ); |
103 | Object value = null ; |
104 | XSSFRow row = null ; |
105 | XSSFCell cell = null ; |
106 | for ( int i = sheet.getFirstRowNum(); i <= sheet |
107 | .getPhysicalNumberOfRows(); i++) { |
108 | row = sheet.getRow(i); |
109 | if (row == null ) { |
110 | continue ; |
111 | } |
112 | List<Object> linked = new LinkedList<Object>(); |
113 | for ( int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { |
114 | cell = row.getCell(j); |
115 | if (cell == null ) { |
116 | continue ; |
117 | } |
118 | DecimalFormat df = new DecimalFormat( "0" ); // 格式化 number String 字符 |
119 | SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); // 格式化日期字符串 |
120 | DecimalFormat nf = new DecimalFormat( "0.00" ); // 格式化数字 |
121 | switch (cell.getCellType()) { |
122 | case XSSFCell.CELL_TYPE_STRING: |
123 | System.out.println(i+ "行" +j+ " 列 is String type" ); |
124 | value = cell.getStringCellValue(); |
125 | break ; |
126 | case XSSFCell.CELL_TYPE_NUMERIC: |
127 | System.out.println(i+ "行" +j+ " 列 is Number type ; DateFormt:" +cell.getCellStyle().getDataFormatString()); |
128 | if ( "@" .equals(cell.getCellStyle().getDataFormatString())){ |
129 | value = df.format(cell.getNumericCellValue()); |
130 | } else if ( "General" .equals(cell.getCellStyle().getDataFormatString())){ |
131 | value = nf.format(cell.getNumericCellValue()); |
132 | } else { |
133 | value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); |
134 | } |
135 | break ; |
136 | case XSSFCell.CELL_TYPE_BOOLEAN: |
137 | System.out.println(i+ "行" +j+ " 列 is Boolean type" ); |
138 | value = cell.getBooleanCellValue(); |
139 | break ; |
140 | case XSSFCell.CELL_TYPE_BLANK: |
141 | System.out.println(i+ "行" +j+ " 列 is Blank type" ); |
142 | value = "" ; |
143 | break ; |
144 | default : |
145 | System.out.println(i+ "行" +j+ " 列 is default type" ); |
146 | value = cell.toString(); |
147 | } |
148 | if (value == null || "" .equals(value)) { |
149 | continue ; |
150 | } |
151 | linked.add(value); |
152 | } |
153 | list.add(linked); |
154 | } |
155 | return list; |
156 | } |
157 | } |
Q:excel的第1,2行有数据,第3,4行没数据,第5行有数据,第5行数据显示不出来。
A:sheet.getPhysicalNumberOfRows()获取有效的行数,多谢热心网友的指正,改进后代码如下:
001 | import java.io.File; |
002 | import java.io.FileInputStream; |
003 | import java.io.FileNotFoundException; |
004 | import java.io.IOException; |
005 | import java.text.DecimalFormat; |
006 | import java.text.SimpleDateFormat; |
007 | import java.util.LinkedList; |
008 | import java.util.List; |
009 | import org.apache.poi.hssf.usermodel.HSSFCell; |
010 | import org.apache.poi.hssf.usermodel.HSSFDateUtil; |
011 | import org.apache.poi.hssf.usermodel.HSSFRow; |
012 | import org.apache.poi.hssf.usermodel.HSSFSheet; |
013 | import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
014 | import org.apache.poi.xssf.usermodel.XSSFCell; |
015 | import org.apache.poi.xssf.usermodel.XSSFRow; |
016 | import org.apache.poi.xssf.usermodel.XSSFSheet; |
017 | import org.apache.poi.xssf.usermodel.XSSFWorkbook; |
018 | public class ReadExcel { |
019 | /** |
020 | * 对外提供读取excel 的方法 |
021 | * */ |
022 | public static List<List<Object>> readExcel(File file) throws IOException { |
023 | String fileName = file.getName(); |
024 | String extension = fileName.lastIndexOf( "." ) == - 1 ? "" : fileName |
025 | .substring(fileName.lastIndexOf( "." ) + 1 ); |
026 | if ( "xls" .equals(extension)) { |
027 | return read2003Excel(file); |
028 | } else if ( "xlsx" .equals(extension)) { |
029 | return read2007Excel(file); |
030 | } else { |
031 | throw new IOException( "不支持的文件类型" ); |
032 | } |
033 | } |
034 | /** |
035 | * 读取 office 2003 excel |
036 | * @throws IOException |
037 | * @throws FileNotFoundException |
038 | */ |
039 | private static List<List<Object>> read2003Excel(File file) |
040 | throws IOException { |
041 | List<List<Object>> list = new LinkedList<List<Object>>(); |
042 | HSSFWorkbook hwb = new HSSFWorkbook( new FileInputStream(file)); |
043 | HSSFSheet sheet = hwb.getSheetAt( 0 ); |
044 | Object value = null ; |
045 | HSSFRow row = null ; |
046 | HSSFCell cell = null ; |
047 | int counter = 0 ; |
048 | for ( int i = sheet.getFirstRowNum(); counter < sheet |
049 | .getPhysicalNumberOfRows(); i++) { |
050 | row = sheet.getRow(i); |
051 | if (row == null ) { |
052 | continue ; |
053 | } else { |
054 | counter++; |
055 | } |
056 | List<Object> linked = new LinkedList<Object>(); |
057 | for ( int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { |
058 | cell = row.getCell(j); |
059 | if (cell == null ) { |
060 | continue ; |
061 | } |
062 | DecimalFormat df = new DecimalFormat( "0" ); // 格式化 number String 字符 |
063 | SimpleDateFormat sdf = new SimpleDateFormat( |
064 | "yyyy-MM-dd HH:mm:ss" ); // 格式化日期字符串 |
065 | DecimalFormat nf = new DecimalFormat( "0.00" ); // 格式化数字 |
066 | switch (cell.getCellType()) { |
067 | case XSSFCell.CELL_TYPE_STRING: |
068 | System.out.println(i + "行" + j + " 列 is String type" ); |
069 | value = cell.getStringCellValue(); |
070 | break ; |
071 | case XSSFCell.CELL_TYPE_NUMERIC: |
072 | System.out.println(i + "行" + j |
073 | + " 列 is Number type ; DateFormt:" |
074 | + cell.getCellStyle().getDataFormatString()); |
075 | if ( "@" .equals(cell.getCellStyle().getDataFormatString())) { |
076 | value = df.format(cell.getNumericCellValue()); |
077 | } else if ( "General" .equals(cell.getCellStyle() |
078 | .getDataFormatString())) { |
079 | value = nf.format(cell.getNumericCellValue()); |
080 | } else { |
081 | value = sdf.format(HSSFDateUtil.getJavaDate(cell |
082 | .getNumericCellValue())); |
083 | } |
084 | break ; |
085 | case XSSFCell.CELL_TYPE_BOOLEAN: |
086 | System.out.println(i + "行" + j + " 列 is Boolean type" ); |
087 | value = cell.getBooleanCellValue(); |
088 | break ; |
089 | case XSSFCell.CELL_TYPE_BLANK: |
090 | System.out.println(i + "行" + j + " 列 is Blank type" ); |
091 | value = "" ; |
092 | break ; |
093 | default : |
094 | System.out.println(i + "行" + j + " 列 is default type" ); |
095 | value = cell.toString(); |
096 | } |
097 | if (value == null || "" .equals(value)) { |
098 | continue ; |
099 | } |
100 | linked.add(value); |
101 | } |
102 | list.add(linked); |
103 | } |
104 | return list; |
105 | } |
106 | /** |
107 | * 读取Office 2007 excel |
108 | * */ |
109 | private static List<List<Object>> read2007Excel(File file) |
110 | throws IOException { |
111 | List<List<Object>> list = new LinkedList<List<Object>>(); |
112 | // 构造 XSSFWorkbook 对象,strPath 传入文件路径 |
113 | XSSFWorkbook xwb = new XSSFWorkbook( new FileInputStream(file)); |
114 | // 读取第一章表格内容 |
115 | XSSFSheet sheet = xwb.getSheetAt( 0 ); |
116 | Object value = null ; |
117 | XSSFRow row = null ; |
118 | XSSFCell cell = null ; |
119 | int counter = 0 ; |
120 | for ( int i = sheet.getFirstRowNum(); counter < sheet |
121 | .getPhysicalNumberOfRows(); i++) { |
122 | row = sheet.getRow(i); |
123 | if (row == null ) { |
124 | continue ; |
125 | } else { |
126 | counter++; |
127 | } |
128 | List<Object> linked = new LinkedList<Object>(); |
129 | for ( int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { |
130 | cell = row.getCell(j); |
131 | if (cell == null ) { |
132 | continue ; |
133 | } |
134 | DecimalFormat df = new DecimalFormat( "0" ); // 格式化 number String 字符 |
135 | SimpleDateFormat sdf = new SimpleDateFormat( |
136 | "yyyy-MM-dd HH:mm:ss" ); // 格式化日期字符串 |
137 | DecimalFormat nf = new DecimalFormat( "0.00" ); // 格式化数字 |
138 | switch (cell.getCellType()) { |
139 | case XSSFCell.CELL_TYPE_STRING: |
140 | System.out.println(i + "行" + j + " 列 is String type" ); |
141 | value = cell.getStringCellValue(); |
142 | break ; |
143 | case XSSFCell.CELL_TYPE_NUMERIC: |
144 | System.out.println(i + "行" + j |
145 | + " 列 is Number type ; DateFormt:" |
146 | + cell.getCellStyle().getDataFormatString()); |
147 | if ( "@" .equals(cell.getCellStyle().getDataFormatString())) { |
148 | value = df.format(cell.getNumericCellValue()); |
149 | } else if ( "General" .equals(cell.getCellStyle() |
150 | .getDataFormatString())) { |
151 | value = nf.format(cell.getNumericCellValue()); |
152 | } else { |
153 | value = sdf.format(HSSFDateUtil.getJavaDate(cell |
154 | .getNumericCellValue())); |
155 | } |
156 | break ; |
157 | case XSSFCell.CELL_TYPE_BOOLEAN: |
158 | System.out.println(i + "行" + j + " 列 is Boolean type" ); |
159 | value = cell.getBooleanCellValue(); |
160 | break ; |
161 | case XSSFCell.CELL_TYPE_BLANK: |
162 | System.out.println(i + "行" + j + " 列 is Blank type" ); |
163 | value = "" ; |
164 | break ; |
165 | default : |
166 | System.out.println(i + "行" + j + " 列 is default type" ); |
167 | value = cell.toString(); |
168 | } |
169 | if (value == null || "" .equals(value)) { |
170 | continue ; |
171 | } |
172 | linked.add(value); |
173 | } |
174 | list.add(linked); |
175 | } |
176 | return list; |
177 | } |
178 | public static void main(String[] args) { |
179 | try { |
180 | readExcel( new File( "D:\\test.xlsx" )); |
181 | // readExcel(new File("D:\\test.xls")); |
182 | } catch (IOException e) { |
183 | e.printStackTrace(); |
184 | } |
185 | } |
186 | } |