文件读取util(excel,cvs,txt)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.cmos.ngoccontrol.util; import java.io.BufferedReader; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import com.cmos.core.logger.Logger; import com.cmos.core.logger.LoggerFactory; public class FileReadUtil { private static final Logger LOGGER = LoggerFactory.getActionLog(FileReadUtil.class); /** 获取单元格的值 * * @param cell * @return */ private static String getCellValue(Cell cell) { String cellValue = ""; DataFormatter formatter = new DataFormatter(); if (cell != null) { // 判断单元格数据的类型,不同类型调用不同的方法 switch (cell.getCellType()) { // 数值类型 case Cell.CELL_TYPE_NUMERIC: // 进一步判断 ,单元格格式是日期格式 if (DateUtil.isCellDateFormatted(cell)) { cellValue = formatter.formatCellValue(cell); } else { // 数值(手机号) double value = cell.getNumericCellValue(); DecimalFormat df = new DecimalFormat("#"); cellValue = df.format(value); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; // 判断单元格是公式格式,需要做一种特殊处理来得到相应的值 case Cell.CELL_TYPE_FORMULA: { try { cellValue = String.valueOf(cell.getNumericCellValue()); } catch (IllegalStateException e) { cellValue = String.valueOf(cell.getRichStringCellValue()); LOGGER.error("getCellValue() error",e); } } break; case Cell.CELL_TYPE_BLANK: cellValue = ""; break; case Cell.CELL_TYPE_ERROR: cellValue = ""; break; default: cellValue = cell.toString().trim(); break; } } return cellValue.trim(); } /** 读取Excel文件第一行字段 * * @param file * @return * @throws IOException */ public static List<String> readExcelTitle(File file) throws IOException { List<String> title = new ArrayList<String>(); FileInputStream inStream = null; Workbook workBook = null; Sheet sheet = null; try { inStream = new FileInputStream(file); workBook = WorkbookFactory.create(inStream); sheet = workBook.getSheetAt(0); // 获取第一行数据 Row row = sheet.getRow(0); for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); // 获取单元格的值 String str = getCellValue(cell); // 将得到的值放入链表中 title.add(str); } return title; } catch (Exception e) { LOGGER.error("readExcelTitle() error",e); } finally { if (inStream != null) { inStream.close(); } } return title; } /** 读取Txt或Csv文件第一行字段 * * @param file * @param splitStr * @return * @throws IOException */ public static List<String> readTxtOrCsvTitle(File file, String splitStr) throws IOException { List<String> title = new ArrayList<String>(); // 一行数据 String inString = ""; InputStream is = null; InputStreamReader isr = null; BufferedReader reader = null; try { is = new FileInputStream(file); isr = new InputStreamReader(is, "GBK"); reader = new BufferedReader(isr); inString = reader.readLine(); if (inString != null) { String[] split = inString.split(splitStr); for (int i = 0; i < split.length; i++) { title.add(split[i]); } } return title; } catch (IOException e) { LOGGER.error("readTxtOrCsvTitle() error",e); } finally { if (reader != null) { reader.close(); } if (isr != null) { isr.close(); } if (is != null) { is.close(); } } return title; } /** * 返回文件第一行字段 * * @param is * @param splitStr * 分割符 * @return * @throws IOException */ public static List<String> getFieldsWithTitle(String fileName, File file, String splitStr) throws IOException { String fileType = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1); if ("txt".equalsIgnoreCase(fileType) || "csv".equalsIgnoreCase(fileType)) { // csv、txt文件 return readTxtOrCsvTitle(file, splitStr); } else if ("xlsx".equalsIgnoreCase(fileType)) { // excel文件 return readExcelTitle(file); } else { return null; } } public static List<Map<String, Object>> readTxtOrCsvDataWithTitle(String filePath, String splitStr, List<String> sourceFields, List<String> errorList) throws IOException { // 存放文件数据 File file = new File(filePath); List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); // 一行数据 String inString = ""; InputStream is = null; InputStreamReader isr = null; BufferedReader reader = null; try { is = new FileInputStream(file); isr = new InputStreamReader(is, "GBK"); reader = new BufferedReader(isr); // 读取行数 int rowCount = 1; // 文件标题长度 int titleLength = 0; // 需要读取的索引列 List<Integer> needReadIndex = new ArrayList<Integer>(); // 需要读取的索引列对应的name名称 List<String> needReadKey = new ArrayList<String>(); while ((inString = reader.readLine()) != null) { if (rowCount == 1) { // 第一行 String[] split = inString.split(splitStr); titleLength = split.length; for (int i = 0; i < split.length; i++) { for (int j = 0; j < sourceFields.size(); j++) { if (sourceFields.get(j).equalsIgnoreCase(split[i])) { needReadIndex.add(i); needReadKey.add(sourceFields.get(j)); } } } } else { String[] split = inString.split(splitStr); if (titleLength != split.length) { errorList.add("文件第" + rowCount + "行错误,字段长度与第一行标题行不对应"); result.add(null); } else { Map<String, Object> rowData = new HashMap<String, Object>(); for (int i = 0; i < needReadIndex.size(); i++) { rowData.put(needReadKey.get(i), split[needReadIndex.get(i)]); } result.add(rowData); } } rowCount++; } return result; } catch (IOException e) { LOGGER.error("readTxtOrCsvDataWithTitle() error",e); } finally { if (reader != null) { reader.close(); } if (isr != null) { isr.close(); } if (is != null) { is.close(); } } return result; } public static List<Map<String, Object>> readTxtOrCsvDataWithTitle(InputStream is, String splitStr, List<String> sourceFields, List<String> errorList) throws IOException { // 存放文件数据 List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); // 一行数据 String inString = ""; InputStreamReader isr = null; BufferedReader reader = null; try { isr = new InputStreamReader(is, "GBK"); reader = new BufferedReader(isr); // 读取行数 int rowCount = 1; // 文件标题长度 int titleLength = 0; // 需要读取的索引列 List<Integer> needReadIndex = new ArrayList<Integer>(); // 需要读取的索引列对应的name名称 List<String> needReadKey = new ArrayList<String>(); while ((inString = reader.readLine()) != null) { if (rowCount == 1) { // 第一行 String[] split = inString.split(splitStr); titleLength = split.length; for (int i = 0; i < split.length; i++) { for (int j = 0; j < sourceFields.size(); j++) { if (sourceFields.get(j).equalsIgnoreCase(split[i])) { needReadIndex.add(i); needReadKey.add(sourceFields.get(j)); } } } } else { String[] split = inString.split(splitStr); if (titleLength != split.length) { errorList.add("文件第" + rowCount + "行错误,字段长度与第一行标题行不对应"); result.add(null); } else { Map<String, Object> rowData = new HashMap<String, Object>(); for (int i = 0; i < needReadIndex.size(); i++) { rowData.put(needReadKey.get(i), split[needReadIndex.get(i)]); } result.add(rowData); } } rowCount++; } return result; } catch (IOException e) { LOGGER.error("readTxtOrCsvDataWithTitle() error",e); } finally { if (reader != null) { reader.close(); } if (isr != null) { isr.close(); } if (is != null) { is.close(); } } return result; } /** * * @param filePath 临时文件路径 * @param sourceFields 源文件字段 * @param targetFields 目标文件字段 * @param errorList 错误数据(例如:第几行数据错误,错误原因:字段长度与文件标题长度不一致) * @param dispose 处理后前台需要展示的数据(文件总行数,实际行数(正确数据行数),错误行数) * @return * @throws IOException */ public static List<Map<String, Object>> readExcelWithTitle(String filePath, List<String> sourceFields, List<String> errorList, Map<String, String> dispose) throws IOException { //List<String> title = new ArrayList<String>(); FileInputStream inStream = null; Workbook workBook = null; Sheet sheet = null; List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); // 需要读取的索引列 List<Integer> needReadIndex = new ArrayList<Integer>(); // 需要读取的索引列对应的name名称 List<String> needReadKey = new ArrayList<String>(); try { inStream = new FileInputStream(filePath); workBook = WorkbookFactory.create(inStream); sheet = workBook.getSheetAt(0); // 获取总行数 int rowNum = sheet.getLastRowNum() + 1; for (int i = 0; i < rowNum; i++) { Row row = sheet.getRow(i); if (i == 0) { for (int j = 0; j < row.getLastCellNum(); j++) { // 读取第一行 存入标题 Cell cell = row.getCell(j); // 获取单元格的值 String str = getCellValue(cell); for (int k = 0; k < sourceFields.size(); k++) { if (sourceFields.get(j).equalsIgnoreCase(str)) { needReadIndex.add(i); needReadKey.add(sourceFields.get(k)); } } } } else { Map<String, Object> beanRow=new HashMap<String, Object>(); for(int j = 0; j <needReadIndex.size();j++){ // 读取数据行 Cell cell = row.getCell(needReadIndex.get(j)); // 获取单元格的值 String str = getCellValue(cell); beanRow.put(needReadKey.get(j), str); } result.add(beanRow); } } } catch (Exception e) { LOGGER.error("readExcelWithTitle() error",e); } finally { if (inStream != null) { inStream.close(); } } return result; } /** * 主方法:读取文件数据 * @param filePath * @param splitStr * @param sourceFields * @param targetFields * @param errorList * @param dispose * @return * @throws IOException */ public static List<Map<String, Object>> getFileData(String filePath, String splitStr, List<String> sourceFields, List<String> errorList, Map<String, String> dispose) throws IOException { String fileType = filePath.lastIndexOf(".") == -1 ? "" : filePath.substring(filePath.lastIndexOf(".") + 1); if ("txt".equalsIgnoreCase(fileType) || "csv".equalsIgnoreCase(fileType)) { // csv、txt文件 return readTxtOrCsvDataWithTitle(filePath, splitStr, sourceFields, errorList); } else if ("xlsx".equalsIgnoreCase(fileType)) { // excel文件 return readExcelWithTitle(filePath, sourceFields, errorList, dispose); } else { return null; } } /** * * @param oldPath * String原文件路径 如:c:/fqf.txt* * @param newPath * String * @throws IOException */ public static boolean copyFile(File oldfile, String newPath) throws IOException { File newFile = new File(newPath); // 判断目标文件所在的目录是否存在 if (!newFile.getParentFile().exists()) { newFile.getParentFile().mkdirs(); } try { newFile.createNewFile(); } catch (IOException e1) { LOGGER.error("createNewFile",e1); } boolean flag = false; int byteread = 0; FileInputStream inStream = null; FileOutputStream fs = null; if (oldfile.exists()) { // 文件存在时 try { inStream = new FileInputStream(oldfile); // 读入原文件 fs = new FileOutputStream(newPath); byte[] buffer = new byte[1444]; while ((byteread = inStream.read(buffer)) != -1) { fs.write(buffer, 0, byteread); } flag = true; } catch (Exception e) { LOGGER.error("copyFile() error",e); } finally { if(fs != null){ fs.close(); } if(inStream != null){ inStream.close(); } } } return flag; } /** * 删除单个文件 * @param sPath 被删除文件的文件名 * @return 单个文件删除成功返回true,否则返回false */ public static boolean deleteFile(String sPath) { boolean flag = false; File file = new File(sPath); // 路径为文件且不为空则进行删除 if (file.isFile() && file.exists()) { file.delete(); flag = true; } return flag; } /* *//** * 建立FTP连接 * @param host 地址 * @param port 端口 * @param username 用户名 * @param password 密码 * @return *//* public static ChannelSftp connect(String host, int port, String username,String password) { ChannelSftp sftp = null; try { JSch jsch = new JSch(); jsch.getSession(username, host, port); Session sshSession = jsch.getSession(username, host, port); LOGGER.info("Session created."); sshSession.setPassword(password); Properties sshConfig = new Properties(); sshConfig.put("StrictHostKeyChecking", "no"); sshSession.setConfig(sshConfig); sshSession.connect(); LOGGER.info("Session connected."); LOGGER.info("Opening Channel."); Channel channel = sshSession.openChannel("sftp"); channel.connect(); sftp = (ChannelSftp) channel; LOGGER.info("Connected to " + host + "."); } catch (Exception e) { LOGGER.error("Connected to " + host + "failed.",e); } return sftp; } *//** * 下载文件 * @param remotePath 下载目录 * @param fileName 下载的文件 * @param localPath 存在本地的路径 * @param sftp *//* public static boolean download(String remotePath, String fileName,String localPath, ChannelSftp sftp) { boolean result = false; try { sftp.cd(remotePath); File file=new File(localPath); sftp.get(fileName, new FileOutputStream(file)); LOGGER.info("下载成功!"); result = true; } catch (Exception e) { LOGGER.error("下载失败!"); LOGGER.error("download() error",e); } return result; }*/ public static Map<String, Object> readFile(String path, String splitStr, int controlParseNum){ Map<String, Object> mapR = new HashMap<String, Object>(); String fileType = path.lastIndexOf(".") == -1 ? "" : path.substring(path.lastIndexOf(".") + 1); if ("txt".equalsIgnoreCase(fileType) || "csv".equalsIgnoreCase(fileType)) { mapR = TxtOrCsvReaderUtil.readTxtOrCsv(path, splitStr, controlParseNum); } else if ("xlsx".equalsIgnoreCase(fileType)) { XlsxReaderUtil xlsxReaderUtil = new XlsxReaderUtil(path, controlParseNum); mapR = xlsxReaderUtil.process(); } else if ("xls".equalsIgnoreCase(fileType)) { mapR = XlsReaderUtil.readExcel(path, splitStr, controlParseNum); } return mapR; } }