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包下载
POI相关联jar包下载
