com.github.xiaoyuge5201.excel.CSVUtils Maven / Gradle / Ivy
package com.github.xiaoyuge5201.excel;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 读取CSV格式的文件
* @author xiaoyuge
*/
public class CSVUtils {
private static Logger logger = LoggerFactory.getLogger(CSVUtils.class);
private BufferedReader fr = null;
private BufferedReader br = null;
public CSVUtils(String f, String charset) throws IOException {
DataInputStream in = new DataInputStream(new FileInputStream(new File(f)));
// fr = new InputStreamReader(new FileInputStream(f));
fr = new BufferedReader(new InputStreamReader(in, charset));
}
/**
* 解析csv文件 到一个list中 每个单元个为一个String类型记录，每一行为一个list。 再将所有的行放到一个总list中
*/
public List> readCSVFile() throws IOException {
br = new BufferedReader(fr);
String rec = null;// 一行
String str;// 一个单元格
List> listFile = new ArrayList>();
try {
// 读取一行
while ((rec = br.readLine()) != null) {
Pattern pCells = Pattern.compile("(\"[^\"]*(\"{2})*[^\"]*\")*[^,]*,");
Matcher mCells = pCells.matcher(rec);
List cells = new ArrayList();// 每行记录一个list
// 读取每个单元格
while (mCells.find()) {
str = mCells.group();
str = str.replaceAll("(?sm)\"?([^\"]*(\"{2})*[^\"]*)\"?.*,", "$1");
str = str.replaceAll("(?sm)(\"(\"))", "$2");
cells.add(str);
}
listFile.add(cells);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fr != null) {
fr.close();
}
if (br != null) {
br.close();
}
}
return listFile;
}
/**
* 解析csv数据
*
* @return
* @throws IOException
*/
public List> read() throws IOException {
String line;
List headlist = new ArrayList();
List datalist = new ArrayList();
List> list = new ArrayList<>();
br = new BufferedReader(fr);
Integer count = 0;
int headCount = 0;
while ((line = br.readLine()) != null) {
if (count == 0) {
String[] info = line.split(",");
for (int i = 0; i < info.length; i++) {
headlist.add(new String(info[i].trim().getBytes(), "UTF-8"));
}
list.add(headlist);
headCount = headlist.size();
count++;
} else {
String[] info = line.split(",");
for (int i = 0; i < info.length; i++) {
datalist.add(info[i].trim());
}
if (info.length != headCount) {
datalist.add("");
}
list.add(datalist);
}
}
return list;
}
/**
* 将结果集存储为CSV文件
*
* @param rs 结果集
* @param pathWithName 要存储到文件的路径
*/
public static void toCSV(ResultSet rs, String pathWithName, String charset) {
try {
PrintWriter writer = FileUtil.getPrintWriter(pathWithName, charset, false);
while (rs.next()) {
int count = rs.getMetaData().getColumnCount();
// 处理一行
StringBuffer sb = new StringBuffer();
for (int i = 1; i <= count; i++) {
sb.append(rs.getObject(i));
if (i == count)
break;
sb.append(",");
}
String line = sb.toString();
logger.debug("写入：" + line);
writer.println(line);
}
writer.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 将集合中的内容写入CSV文件
*
* @param collection 要写入文件的集合集合
* @param pathWithName CSV文件路径，带文件名
* @param charset 字符集
*/
public static void toCSV(Collection collection, String pathWithName, String charset) {
try {
PrintWriter writer = FileUtil.getPrintWriter(pathWithName, charset, false);
for (String line : collection) {
logger.debug("写入：" + line);
writer.println(line);
}
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws Throwable {
CSVUtils test = new CSVUtils("C:\\Users\\小余哥\\Desktop\\2222.csv", "gbk");
List> csvlist = test.read();
if (csvlist != null && csvlist.size() > 0) {
List columnlist = csvlist.get(0);
List datalist = csvlist.get(1);
int size = datalist.size() / columnlist.size();
columnlist.get(0).substring(1).length();
int iccid_index = 1;
int code_index = 0;
int state_index = 4;
int imsi_index = 2;
for (int i = 0; i < size; i++) {
String iccid = null;
String code = null;
String cardState = null;
if (iccid_index != -1) {
iccid = datalist.get(iccid_index + columnlist.size() * i).replaceAll("\"", "").trim();
}
if (code_index != -1) {
code = datalist.get(code_index + columnlist.size() * i).replaceAll("\"", "").trim();
}
Integer cardStatus = -1;
if (state_index != -1) {
cardState = datalist.get(state_index + columnlist.size() * i).replaceAll("\"", "").trim();
}
String imsi = new BigDecimal(datalist.get(imsi_index + columnlist.size() * i)).toString().replaceAll("\"", "").trim();
System.out.println(iccid + "---" + code + "=====" + imsi + "=====" + cardState + "=====" + cardStatus);
}
}
}
}