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;
}
}
