读取Excel工具类
ExcelPublic.java，其中包含读取Excel第一行（一般为标题行），读取所有Excel中所有数据（包括标题行），读取表头字段在excel中的位置（列值）等方法。
poi版本为3.15，数据库为MySQL
测试Excel和s数据库结果如下：
ExcelPublic.java
package com.alphajuns.ssm.util; /** * ExcelPublic.java */ import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; /** * <p> * ClassName: excelPublic * </p> * <p> * Description: 负责excel的公共类 * </p> * <p> * Author: Administrator * </p> * <p> * Date: 2015-6-28 * </p> */ public class ExcelPublic { /** * <p> * Description: 一个提取excel文件内cell内容的方法 * </p> * . * * @param cell * 一个单元格内的内容 * @return 把cell转换成string */ @SuppressWarnings("deprecation") public static String getCellValue(Cell cell) { String cellStr = null; DecimalFormat DF = new DecimalFormat("0"); if (cell == null) { cellStr = ""; } else { switch (cell.getCellType()) { //case Cell.CELL_TYPE_BOOLEAN: case BOOLEAN: cellStr = String.valueOf(cell.getBooleanCellValue()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // 日期 final DateFormat FORMATER = new SimpleDateFormat("dd.MM.yyyy"); cellStr = FORMATER.format(cell.getDateCellValue()); } else { String number = String.valueOf(cell.getNumericCellValue()); if(number.indexOf(".") != -1){ DF = new DecimalFormat("#.###"); } cellStr = DF.format(cell.getNumericCellValue()); } break; case BLANK: cellStr = ""; break; case ERROR: cellStr = String.valueOf(cell.getErrorCellValue()); break; case FORMULA: // 首先判断一下是不是公式 cell.setCellType(CellType.NUMERIC); // 设置其单元格类型为数字 try { cellStr = DF.format(cell.getNumericCellValue()); // 获取数字值 } catch (Exception e) { throw new RuntimeException("行:" + (cell.getRowIndex() + 1) + ",列:" + (cell.getColumnIndex() + 1) + " 获取公式值失败," + e.toString()); } break; default: cellStr = cell.getStringCellValue(); break; } } return cellStr; } /** * <p> * Description: 一个检查excel的第一行是否有值的方法 * </p> * * @param path * path * @return boolean * @throws IOException * IO异常 */ public static boolean checkArray(String path) throws IOException { final FileInputStream FS = new FileInputStream(path); final String TYPE = path.substring(path.lastIndexOf(".") + 1).toLowerCase(); Workbook wb; if ("xlsx".equals(TYPE)) { wb = new XSSFWorkbook(FS); } else { wb = new HSSFWorkbook(FS); } Sheet sheet = wb.getSheetAt(0); boolean flag = sheet.getRow(0) != null ? true : false; // 添加一个检查表格第一行是否为正常数据的方法 gary 2016.9.19 int cellNum = 0; if (sheet.getRow(0) != null) { // 检查第一行是不是为空 cellNum = sheet.getRow(0).getPhysicalNumberOfCells(); } else { cellNum = 0; } Row row = null; Cell cell = null; final String[] STR = new String[cellNum]; if (flag) { row = sheet.getRow(0); for (int j = 1; j <= cellNum; j++) { cell = row.getCell(j - 1); if (cell == null) { STR[j - 1] = ""; } else { STR[j - 1] = getCellValue(cell); } } } sheet = null; wb.close(); wb = null; return flag; } public static String[][] dyadicArray(String path, int startNum, int endNum) throws IOException { String[][] str = null; FileInputStream fs = new FileInputStream(path); String type = path.substring(path.lastIndexOf(".") + 1).toLowerCase(); Workbook wb; if ("xlsx".equals(type)) { wb = new XSSFWorkbook(fs); } else { wb = new HSSFWorkbook(fs); } try { Sheet sheet = wb.getSheetAt(0); final int A = sheet.getPhysicalNumberOfRows(); // 行数 final int B; B = sheet.getRow(0).getPhysicalNumberOfCells(); // 列数 if (endNum == 0 && startNum == 1) { endNum = A; } else if (startNum == 0) { startNum = 1; } int rowNumber = endNum - startNum + 1; str = new String[rowNumber][B]; Row row = null; Cell cell = null; for (int i = 1; i <= rowNumber; i++) { row = sheet.getRow(startNum - 1); for (int j = 1; j <= B; j++) { cell = row.getCell(j - 1); if (cell == null) { str[i - 1][j - 1] = ""; } else { str[i - 1][j - 1] = getCellValue(cell); } cell = null; } startNum++; row = null; } sheet = null; } catch (Exception e) { throw new IOException(e); } finally { wb.close(); wb = null; fs = null; } return str; } /** * <p> * Description: 得到excel行数的方法 * </p> * * @param path * path * @return int * @throws IOException */ public static int getRowNumber(String path) throws IOException { final FileInputStream FS = new FileInputStream(path); final String TYPE = path.substring(path.lastIndexOf(".") + 1).toLowerCase(); Workbook wb; if ("xlsx".equals(TYPE)) { wb = new XSSFWorkbook(FS); } else { wb = new HSSFWorkbook(FS); } final Sheet SHEET = wb.getSheetAt(0); int firstRowNumber = SHEET.getFirstRowNum(); int lastRowNumber = SHEET.getLastRowNum(); int rowNumber = lastRowNumber - firstRowNumber + 1; // 行数 wb.close(); return rowNumber; } public static ArrayList<String> readExcelFirstline(String path) throws IOException { final ArrayList<String> FIRSTLINE = new ArrayList<String>(); if (checkArray(path)) { String[][] article = dyadicArray(path, 1, 1); final int LENGTH = article[0].length; for (int i = 0; i < LENGTH; i++) { FIRSTLINE.add(article[0][i]); } article = null; } return FIRSTLINE; } /** * <p> * Description: 将二维数组的每一行转成ArrayList * </p> * . * * @param path * 带文件名的路径 * @param dyadic * 二维数组，也就是给定的原文，目前已经指定了行数范围 * @return 返回该行 * @throws IOException * Signals that an I/O exception has occurred. */ public static HashMap<Integer, ArrayList<String>> readExcelLine(String path, String[][] dyadic) throws IOException { final HashMap<Integer, ArrayList<String>> READEXCEL = new HashMap<Integer, ArrayList<String>>(); final int LENGTH = dyadic.length; final int WIDTH = dyadic[0].length; String[][] article = null; article = dyadic; for (int i = 0; i < LENGTH; i++) { final ArrayList<String> ROW = new ArrayList<String>(); for (int j = 0; j < WIDTH; j++) { ROW.add(article[i][j]); } READEXCEL.put(i, ROW); } return READEXCEL; } public static Integer findTheWord(String path, String word) throws IOException { int wordLocation = 0; ArrayList<String> arr = null; arr = readExcelFirstline(path); if (arr.contains(word)) { wordLocation = arr.indexOf(word); } else { wordLocation = -1; } return wordLocation; } // /** // * <p> // * Description: 根据不同文本来适配不同字符编码，针对txt文本使用 // * </p>. // * // * @param fileName 带文件名的路径 // * @return 文件编码 // * @throws IOException Signals that an I/O exception has occurred. // */ // public static String getCharset(String fileName) throws IOException { // final BufferedInputStream BIN = new BufferedInputStream(new // FileInputStream(fileName)); // final int NUMBER = 8; // final int P = (BIN.read() << NUMBER) + BIN.read(); // final int UTF8 = 0xefbb; // final int UNICODE = 0xfffe; // final int UTF16BE = 0xfeff; // String code = null; // switch (P) { // case UTF8: // code = "UTF-8"; // break; // case UNICODE: // code = "Unicode"; // break; // case UTF16BE: // code = "UTF-16BE"; // break; // default: // code = "GBK"; // } // BIN.close(); // return code; // } /** * <p> * Description: 获取当前时间戳，格式到分 * </p> * . * * @return 当前时间 */ public static String getTime() { final Date DATE = new Date(); // 创建一个时间对象，获取到当前的时间 final SimpleDateFormat SDF = new SimpleDateFormat("yyyyMMddHHmm"); // 设置时间显示格式 final String STR = SDF.format(DATE); // 将当前时间格式化为String return STR; // 输出结果 } /** * <p> * Description: 在传入为list时的插入方法 * </p> * . * * @param outList * 输出的字段 * @param inList * 输入的字段 * @param value * 值 * @return outlist */ public static List<String> addValueMethod(List<String> outList, List<String> inList, Integer value) { String word = null; if (value == -1) { outList.add(""); } else if (value > inList.size()) { outList.add(""); } else if (inList.get(value) != null) { if (inList.get(value).trim().length() > 250) { outList.add(""); } else { if (inList.get(value).trim().contains("\'")) { word = inList.get(value).trim().replace("\'", "’"); } else if (inList.get(value).trim().contains("\"")) { word = inList.get(value).trim().replace("\"", "”"); } else { word = inList.get(value).trim(); } outList.add(word); } } else { outList.add(""); } return outList; } /** * <p> * Description: 在插入值时去掉“.”的方法 * </p> * * @param outList * 输出值 * @param inList * 输出值 * @param value * 位置 * @return 输出结果 */ public static List<String> addValueMethodHavePoint(List<String> outList, List<String> inList, Integer value) { if (value == -1) { outList.add(""); } else if (value > inList.size()) { outList.add(""); } else if (inList.get(value) != null) { String newList; newList = inList.get(value).replace(".", ""); outList.add(newList); } else { outList.add(""); } return outList; } /** * <p> * Description: 生成当前时间，精确到毫秒，格式为年月日小时分钟秒毫秒，没有空格 * </p> * . * * @return 时间string */ public static String haveCurrentTime() { final long CURRENTTIME = System.currentTimeMillis(); final SimpleDateFormat FORMATTER = new SimpleDateFormat("yyyyMMddHHmmssS"); final Date DATE = new Date(CURRENTTIME); final String TIME = FORMATTER.format(DATE); return TIME; } /** * <p> * Description: 在需要提取文件名并将之插入时的方法 * </p> * . * * @param outList * 输出数据 * @param filepath * 带文件名的路径 * @return 输出数据 * @throws IOException * Signals that an I/O exception has occurred. */ public static List<String> addValueFileName(List<String> outList, String filepath) throws IOException { final String PATH1 = filepath.substring(0, filepath.lastIndexOf("_")); // 根据“_”之前的位置来获取文件名 final String PATH = PATH1.substring(PATH1.lastIndexOf(File.separator) + 1).toUpperCase(); outList.add(PATH); return outList; } /** * 根据文件路径读取列所在位置 * * @param tempPath * excel路径 * @param columns * 列名数组 * @return Map<String,Integer> key为列名，value为列在excel中的第几列 * @throws IOException */ public static Map<String, Integer> readLocation(String tempPath, String... columns) throws IOException { Map<String, Integer> locationMap = new HashMap<String, Integer>(); for (String column : columns) { int location = ExcelPublic.findTheWord(tempPath, column); locationMap.put(column, location); } return locationMap; } }
junit测试类
package com.alphajuns.junit; import com.alphajuns.ssm.util.ExcelPublic; import org.junit.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import java.io.File; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.*; /** * @ClassName ExcelTest * @Description TODO * @Author AlphaJunS * @Date 2020/4/18 22:24 * @Version 1.0 */ public class ExcelTest extends SuperJunitTest { @Autowired private JdbcTemplate jdbcTemplate; @Test public void readExcel() { String result = null; String filePath = "C:\\TempFile\\temp\\导入商品信息.xlsx"; File file = new File(filePath); try { // 判断第一行是否有值 if (ExcelPublic.checkArray(filePath)) { // 读取第一行 List<String> firstLine = new ArrayList<>(); firstLine = ExcelPublic.readExcelFirstline(filePath); // 读取excel文件中的所有内容 String[][] content = ExcelPublic.dyadicArray(filePath, 1, 0); // 存放需要插入的数据的位置 Map<String, Integer> locationMap = new HashMap<String, Integer>(); // 存放需要插入的数据的位置 String[] finalFieldArr = { "商品ID", "商品标题", "商品卖点", "商品价格"}; // 读取标题字段及其所在列位置 locationMap = readLocation(filePath, finalFieldArr); if (locationMap.containsValue(-1)) { result = "表头不正确，正确表头为：" + Arrays.asList(finalFieldArr); throw new Exception(result); } // 模拟调用service方法 result = serviceHandleMethod(firstLine, content, locationMap); } } catch (Exception e) { e.printStackTrace(); } } /** * @Description 模拟service层方法 * @param firstLine * @param content * @param locationMap * @return java.lang.String */ private String serviceHandleMethod(List<String> firstLine, String[][] content, Map<String, Integer> locationMap) { // 保存指定字段，可用于检查数据库中是否已经存在，检验导入数据该字段是否重复等 List<String> itemIdList = new ArrayList<>(); // map中保存了所有excel中的数据，不含表头字段 Map<String, String[]> itemMap = new HashMap<>(); // excel中content下表从1开始到末尾 for (int i = 1; i < content.length; i++) { // 获取指定列的的值 String itemId = content[i][locationMap.get("商品ID")]; // 以指定列为键，该行数据作为值保存在map中 itemMap.put(itemId, content[i]); itemIdList.add(itemId); } // 校验数据库是否已经存在，校验重复等操作 // 批量操作，插入数据库等，可采用spring JdbcTemplate的批量更新操作 // 生成时间 String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()); // sql String sql = "insert into tb_item (id, title, sell_point, price, num , cid, status, created, updated) values(?, ?, ?, ?, 500, 560, 1, '" + date +"','" + date + "')"; List<Object[]> batchArgs = new ArrayList<Object[]>(); // 遍历，封装参数 /*for (int i = 0; i < itemIdList.size(); i++) { String itemId = itemIdList.get(i); String[] itemArray = itemMap.get(itemId); Object[] batchArg = new Object[]{}; for (int j = 0; j < itemArray.length; j++) { batchArg[j] = itemArray[j]; } batchArgs.add(batchArg); }*/ for (int i = 1; i < content.length; i++) { String[] itemArray = content[i]; Object[] batchArg = new Object[itemArray.length]; for (int j = 0; j < itemArray.length; j++) { batchArg[j] = itemArray[j]; } batchArgs.add(batchArg); } // 执行sql String result = ""; try { jdbcTemplate.batchUpdate(sql, batchArgs); result = "success"; } catch (DataAccessException e) { e.printStackTrace(); } return result; } /** * 读取字段位置 * @param tempPath * @param columns * @return * @throws IOException */ private Map<String,Integer> readLocation(String tempPath,String... columns) throws IOException { Map<String,Integer> locationMap = new HashMap<String,Integer>(); for(String column : columns){ int location = ExcelPublic.findTheWord(tempPath, column); locationMap.put(column,location); } return locationMap; } }
