java导入excel（直接可执行）

package com.demo.excel; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; 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.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * Title: OperationExcelUtil * Description: 导入导出Excel表格 * Version:1.0.0 * @author liuxin * @date 2018年10月15日 * @param <T> */ @SuppressWarnings({"resource" }) public class OperationExcelUtil<T> { // 2007 版本以上 最大支持1048576行 public final static String EXCEl_FILE_2007 = "2007"; // 2003 版本 最大支持65536 行 public final static String EXCEL_FILE_2003 = "2003"; public static void main(String[] args) throws IOException { String fileName = "e:/D112-16CMNMG4210-201906-03.xls"; String fileName1 = "e:/D112-16CMNMG4210-201906-03.xlsx"; String fileName2 = "e:/D273-中国移动商城（全国）结算单-中移电商-201903.xlsx"; File file = new File(fileName2); List<Map<String, String>> listData = readExcel(file,22,0,0); for (Map<String, String> map : listData) { System.out.println(map); } } public static void readExcelPOI(File file) throws IOException { List<Map<String, String>> listData = readExcel(file,22,0,0); for (Map<String, String> map : listData) { System.out.println(map); } } /** * <p> * 导入Excel表格E <br> * </p> * * @param templetFile 文件 * @param startrow 开始行号 * @param startcol 开始列号 * @param sheetnum * @return * @throws IOException */ public static List<Map<String,String>> readExcel(File file, int startrow, int startcol, int sheetnum) throws IOException{ List<Map<String,String>> varList = new ArrayList<Map<String,String>>(); InputStream in = new FileInputStream(file); if(in != null && in.available() > 0){ String ofn = file.getName();// 文件名 String extName = ""; // 扩展名格式： if (ofn.lastIndexOf(".") >= 0){ extName = ofn.substring(ofn.lastIndexOf(".")); } if(".xls".equals(extName.toLowerCase())){ varList=readExcelByXls(in,startrow,startcol,sheetnum); }else if(".xlsx".equals(extName.toLowerCase())){ varList=readExcelByXlsx(in,startrow,startcol,sheetnum); } } return varList; } /** * 操作Excel2003以前（包括2003）的版本,扩展名是.xls * @param templetFile 文件 * @param startrow 开始行号 * @param startcol 开始列号 * @param sheetnum sheet * @return list */ public static List<Map<String, String>> readExcelByXls(InputStream in, int startrow, int startcol, int sheetnum) { List<Map<String, String>> varList = new ArrayList<Map<String, String>>(); try { HSSFWorkbook wb = new HSSFWorkbook(in); HSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始 int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号 for (int i = startrow; i < rowNum; i++) { // 行循环开始 Map<String, String> varpd = new HashMap<String, String>(); HSSFRow row = sheet.getRow(i); // 行 int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置 for (int j = startcol; j < cellNum; j++) { // 列循环开始 HSSFCell cell = row.getCell(Integer.parseInt(j + "")); String cellValue = null; if (null != cell) { switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库 case 0: if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString(); } else { cell.setCellType(1); cellValue = cell.getStringCellValue(); } break; case 1: cellValue = cell.getStringCellValue(); break; case 2: // cell.setCellType(1); // cellValue = cell.getStringCellValue(); // cellValue = cell.getNumericCellValue() + ""; cellValue = String.valueOf(cell.getDateCellValue()); break; case 3: cellValue = ""; break; case 4: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case 5: cellValue = String.valueOf(cell.getErrorCellValue()); break; } } else { cellValue = ""; } varpd.put("var" + j, cellValue); } varList.add(varpd); } } catch (Exception e) { e.printStackTrace(); System.out.println(e); } return varList; } /** * 是操作Excel2007的版本，扩展名是.xlsx * @param templetFile 文件 * @param startrow 开始行号 * @param startcol 开始列号 * @param sheetnum sheet * @return list */ public static List<Map<String, String>> readExcelByXlsx(InputStream in, int startrow, int startcol, int sheetnum) { List<Map<String, String>> varList = new ArrayList<Map<String, String>>(); try { XSSFWorkbook wb = new XSSFWorkbook(in); XSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始 int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号 for (int i = startrow; i < rowNum; i++) { // 行循环开始 Map<String, String> varpd = new HashMap<String, String>(); XSSFRow row = sheet.getRow(i); // 行 int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置 for (int j = startcol; j < cellNum; j++) { // 列循环开始 XSSFCell cell = row.getCell(Integer.parseInt(j + "")); String cellValue = null; if (null != cell) { cell.getCellTypeEnum(); switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库 /** * Numeric cell type (whole numbers, fractional numbers, dates) */ case 0: if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString(); } else { cell.setCellType(1); cellValue = cell.getStringCellValue(); } break; /** String (text) cell type */ case 1: cellValue = cell.getStringCellValue(); break; /** * Formula cell type * @see FormulaType */ case 2: try { cellValue = String.valueOf(cell.getNumericCellValue()); } catch (IllegalStateException e) { cellValue = String.valueOf(cell.getRichStringCellValue()); } break; /** * Blank cell type */ case 3: cellValue = ""; break; /** * Boolean cell type */ case 4: cellValue = String.valueOf(cell.getBooleanCellValue()); break; /** * Error cell type * @see FormulaError */ case 5: cellValue = String.valueOf(cell.getErrorCellValue()); break; } } else { cellValue = ""; } varpd.put("var" + j, cellValue); } varList.add(varpd); } } catch (Exception e) { System.out.println(e); } return varList; } }