Excel分xls和xlsx,通过POI读取出来,拿个xlsx文件做个示例
读取区别:
*.xls:通过org.apache.poi.hssf.usermodel.HSSFWorkbook
*.xlsx:通过org.apache.poi.xssf.usermodel.XSSFWorkbook
详见代码
1、ExcelUtil.java 读取工具类
package lv.you.util;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
/**
* Excel读取工具类
* @author liuxl
*
*/
public class ExcelUtil {
/**
* 获取excel单元格中的字符串数据
* @param cell excel中的单元格对象
* @return
*/
public static String getStringCellValue(HSSFCell cell){
String result = null;
if(cell==null){
return null;
}
try {
switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
result = String.valueOf(cell.getNumericCellValue());
int index = result.indexOf(".0");
if(index!=-1){
result = result.substring(0,index);
}
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
default :
result = null;
break;
}
if(result!=null){
result = result.trim();
}
} catch (Exception e) {
result = null;
}
return result;
}
/**
* 获取excel单元格中的时间数据(以字符串表示)
* @param cell
* @return
* @throws ParseException
*/
public static Timestamp getDateCellValue(HSSFCell cell){
java.sql.Timestamp result = null;
if(cell==null){
return null;
}
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
String value = cell.getStringCellValue();
if(value!=null){
value = value.trim();
}
if(value.equals("")){
result = null;
}else{
value = value.replaceAll("[年月日./\\\\]", "-");
result = new java.sql.Timestamp(sdf.parse(value).getTime());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
result = new java.sql.Timestamp(cell.getDateCellValue().getTime());
break;
default :
result = null;
break;
}
} catch (Exception e) {
result = null;
}
return result;
}
/**
* 获取excel单元格中的小数
* @param cell
* @return
*/
public static Double getNumberCellValue(HSSFCell cell){
Double result = null;
if(cell==null){
return result;
}
try {
switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
String value = cell.getStringCellValue();
if(value!=null){
value = value.trim();
}
if(value.equals("")){
result = null;
}
value = value.replaceAll("[^0-9.]","");
result = Double.parseDouble(value);
break;
case HSSFCell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue();
break;
default :
result = null;
break;
}
} catch (Exception e) {
result = null;
}
return result;
}
public static int getRightRow(HSSFSheet p,String jz_name){
if(p!=null){
for(int i2=1;i2<p.getLastRowNum();i2++){
if(p.getRow(i2)==null){
continue;
}
String name = ExcelUtil.getStringCellValue(p.getRow(i2).getCell(0));
if(jz_name.equals(name)){
return i2;
}
}
}
return 999;
}
/**
* 获取excel单元格中的字符串数据
* @param cell excel中的单元格对象
* @return
*/
public static String getStringCellValue(XSSFCell cell){
String result = null;
if(cell==null){
return null;
}
try {
switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
result = String.valueOf(cell.getNumericCellValue());
int index = result.indexOf(".0");
if(index!=-1){
result = result.substring(0,index);
}
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
default :
result = null;
break;
}
if(result!=null){
result = result.trim();
}
} catch (Exception e) {
result = null;
}
return result;
}
/**
* 获取excel单元格中的时间数据(以字符串表示)
* @param cell
* @return
* @throws ParseException
*/
public static Timestamp getDateCellValue(XSSFCell cell){
java.sql.Timestamp result = null;
if(cell==null){
return null;
}
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
String value = cell.getStringCellValue();
if(value!=null){
value = value.trim();
}
if(value.equals("")){
result = null;
}else{
value = value.replaceAll("[年月日./\\\\]", "-");
result = new java.sql.Timestamp(sdf.parse(value).getTime());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
result = new java.sql.Timestamp(cell.getDateCellValue().getTime());
break;
default :
result = null;
break;
}
} catch (Exception e) {
result = null;
}
return result;
}
/**
* 获取excel单元格中的小数
* @param cell
* @return
*/
public static Double getNumberCellValue(XSSFCell cell){
Double result = null;
if(cell==null){
return result;
}
try {
switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
String value = cell.getStringCellValue();
if(value!=null){
value = value.trim();
}
if(value.equals("")){
result = null;
}
value = value.replaceAll("[^0-9.]","");
result = Double.parseDouble(value);
break;
case HSSFCell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue();
break;
default :
result = null;
break;
}
} catch (Exception e) {
result = null;
}
return result;
}
}
2、ExcelDemo.java 测试Demo
package lv.you.util;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelDemo {
//测试读取
@SuppressWarnings("resource")
public static void main(String[] args) throws Exception {
String excel_file_path = "E:/cheliang.xlsx";
//存储EXCEL所有数据
List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();
ExcelDemo ed = new ExcelDemo();
//不同版本的EXCEL读取方式不一样
if(excel_file_path.endsWith("xls")){
HSSFWorkbook workBook_xls = new HSSFWorkbook(new FileInputStream(new File(excel_file_path)));
//根据页名称读取指定sheet页
HSSFSheet sheet1 = workBook_xls.getSheet("sheet1");
//循环行
for(int i=1;i<=sheet1.getLastRowNum();i++){
if(sheet1.getRow(i)==null){
continue;
}
dataList.add(ed.getDataList_Xls(sheet1.getRow(i)));
}
}else if(excel_file_path.endsWith("xlsx")){
XSSFWorkbook workBook_xlsx = new XSSFWorkbook(new FileInputStream(new File("E:/cheliang.xlsx")));
//根据页名称读取指定sheet页
XSSFSheet sheet1 = workBook_xlsx.getSheet("sheet1");
//循环行
for(int i=1;i<=sheet1.getLastRowNum();i++){
if(sheet1.getRow(i)==null){
continue;
}
dataList.add(ed.getDataList_Xlsx(sheet1.getRow(i)));
}
}
//模仿输入车架号查询记录
Scanner sc = new Scanner(System.in);
System.out.print("请输入车架号进行查询(模糊搜索):");
String cjh = sc.nextLine();
int find_count = 0;
if(cjh!=null && !"".equals(cjh)){
for(Map<String,Object> dataMap : dataList){
if(dataMap.get("车架号")!=null){
String _cjh = dataMap.get("车架号").toString();
if(_cjh.toLowerCase().contains(cjh) || _cjh.toUpperCase().contains(cjh)){
System.out.print("匹配记录"+(find_count+1)+": ");
for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
System.out.print(entry.getKey() + ": " + entry.getValue()+" ");
}
find_count++;
System.out.println();
}
}
}
System.out.println("共找到"+find_count+"条记录");
}else{
System.out.print("车架号不能为空");
}
}
public Map<String,Object> getDataList_Xls(HSSFRow row){
Map<String,Object> dataMap = new HashMap<String,Object>();
dataMap.put("序号", ExcelUtil.getStringCellValue(row.getCell(0)));
dataMap.put("承保日期", ExcelUtil.getDateCellValue(row.getCell(1)));
dataMap.put("被保险人", ExcelUtil.getStringCellValue(row.getCell(2)));
dataMap.put("车牌号", ExcelUtil.getStringCellValue(row.getCell(3)));
dataMap.put("厂牌型号", ExcelUtil.getStringCellValue(row.getCell(4)));
dataMap.put("车架号", ExcelUtil.getStringCellValue(row.getCell(5)));
dataMap.put("保险止期", ExcelUtil.getDateCellValue(row.getCell(6)));
dataMap.put("承保公司", ExcelUtil.getStringCellValue(row.getCell(7)));
dataMap.put("装载方数", ExcelUtil.getNumberCellValue(row.getCell(8)));
dataMap.put("存档内容", ExcelUtil.getStringCellValue(row.getCell(9)));
dataMap.put("登记日期", ExcelUtil.getDateCellValue(row.getCell(10)));
dataMap.put("发动机号", ExcelUtil.getStringCellValue(row.getCell(11)));
dataMap.put("备注", ExcelUtil.getStringCellValue(row.getCell(12)));
return dataMap;
}
public Map<String,Object> getDataList_Xlsx(XSSFRow row){
Map<String,Object> dataMap = new HashMap<String,Object>();
dataMap.put("序号", ExcelUtil.getStringCellValue(row.getCell(0)));
dataMap.put("承保日期", ExcelUtil.getDateCellValue(row.getCell(1)));
dataMap.put("被保险人", ExcelUtil.getStringCellValue(row.getCell(2)));
dataMap.put("车牌号", ExcelUtil.getStringCellValue(row.getCell(3)));
dataMap.put("厂牌型号", ExcelUtil.getStringCellValue(row.getCell(4)));
dataMap.put("车架号", ExcelUtil.getStringCellValue(row.getCell(5)));
dataMap.put("保险止期", ExcelUtil.getDateCellValue(row.getCell(6)));
dataMap.put("承保公司", ExcelUtil.getStringCellValue(row.getCell(7)));
dataMap.put("装载方数", ExcelUtil.getNumberCellValue(row.getCell(8)));
dataMap.put("存档内容", ExcelUtil.getStringCellValue(row.getCell(9)));
dataMap.put("登记日期", ExcelUtil.getDateCellValue(row.getCell(10)));
dataMap.put("发动机号", ExcelUtil.getStringCellValue(row.getCell(11)));
dataMap.put("备注", ExcelUtil.getStringCellValue(row.getCell(12)));
return dataMap;
}
}
3、EXCEL数据
4、效果
通过车架号查询EXCEL中的记录数据
5、POI相关jar包下载