import javassist.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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;
import java.io.*;
import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class HPoiUtils {
private static final String CLASSNAME = "genClass";
//file listField
public static List exe(File file, List listField) {
return exe(file, 0, listField, true);
}
//file listField hasHeader
public static List exe(File file, List listField, boolean hasHeader) {
return exe(file, 0, listField, hasHeader);
}
//file sheet listField
public static List exe(File file, Integer sheetIndex, List listField) {
return exe(file, sheetIndex, listField, true);
}
public static List exe(File file, String sheetName, List listField) {
return exe(file, sheetName, listField, true);
}
//file sheet listField hasHeader
public static List exe(File file, Integer sheetIndex, List listField, boolean hasHeader) {
//生成类
Class c = generateClass(listField);
return exe(file, sheetIndex, listField, c, hasHeader);
}
public static List exe(File file, String sheetName, List listField, boolean hasHeader) {
//生成类
Class c = generateClass(listField);
return exe(file, sheetName, listField, c, hasHeader);
}
//file listField class
public static List exe(File file, List listField, Class c){
return exe(file, 0, listField, c, true);
}
//file listField class hasHeader
public static List exe(File file, List listField, Class c, boolean hasHeader){
return exe(file, 0, listField, c, hasHeader);
}
//file sheet listField class
public static List exe(File file, Integer sheetIndex, List listField, Class c){
return exe(file, sheetIndex, listField, c, true);
}
public static List exe(File file, String sheetName, List listField, Class c){
return exe(file, sheetName, listField, c, true);//sheetName不区分大小写
}
//file sheet listField class hasHeader
public static List exe(File file, Integer sheetIndex, List listField, Class c, boolean hasHeader){
//参数检查
Boolean check = check(file, sheetIndex, listField, c);
if(!check){
return null;
}
//得到结果
List listResultObj = generateResult(file, sheetIndex, listField, c, hasHeader);
return listResultObj;
}
public static List exe(File file, String sheetName, List listField, Class c, boolean hasHeader){
//参数检查
Boolean check = check(file, sheetName, listField, c);
if(!check){
return null;
}
//得到结果
List listResultObj = generateResult(file, sheetName, listField, c, hasHeader);
return listResultObj;
}
private static List generateResult(File file, Integer sheetIndex, List listField, Class c, boolean hasHeader) {
Integer len = listField.size();
List listResultStr = getStr(file, sheetIndex, len); //得到字符串数据集合
return getObj(listField, listResultStr, c, hasHeader); //得到对象数据集合
}
private static List generateResult(File file, String sheetName, List listField, Class c, boolean hasHeader) {
Integer len = listField.size();
List listResultStr = getStr(file, sheetName, len); //得到字符串数据集合
return getObj(listField, listResultStr, c, hasHeader); //得到对象数据集合
}
private static Class generateClass(List listField) {
// 得到一个池
ClassPool pool = ClassPool.getDefault();
// 根据名称在运行期间动态生成一个类
String className = HPoiUtils.class.getPackage().getName() + "." + CLASSNAME + ".java";
// 根据类名 动态生成一个CtClass
CtClass ctClass = pool.makeClass(className);
// 制作字段
List fieldList = new ArrayList<>();
for (String name : listField) {
fieldList.add(new FieldDeclare(name));
}
// 向CtClass中添加字段和get set方法
for (FieldDeclare filed : fieldList) {
try {
addFieldProperty(ctClass, filed.getName(), pool.getCtClass(filed.getClair()));
} catch (CannotCompileException e) {
System.out.println("添加字段失败");
return null;
} catch (NotFoundException e) {
System.out.println("添加字段失败");
return null;
}
}
try {
return ctClass.toClass();
} catch (CannotCompileException e) {
System.out.println("无法转化类");
return null;
}
}
private static void addFieldProperty(CtClass ctClass, String fieldName, CtClass fieldClass) throws CannotCompileException {
//生成私有属性
CtField ctField = new CtField(fieldClass, fieldName, ctClass);
ctField.setModifiers(Modifier.PRIVATE);
//为类添加属性
ctClass.addField(ctField);
char[] cs = fieldName.toCharArray();
if (cs[0] >= 97 && cs[0] <= 122) {
cs[0] -= 32;
}
String getName = "get" + String.valueOf(cs);
String setName = "set" + String.valueOf(cs);
//添加get set方法
ctClass.addMethod(CtNewMethod.getter(getName, ctField));
ctClass.addMethod(CtNewMethod.setter(setName, ctField));
}
// 字段属性类
static class FieldDeclare{
private String clair; // 字段类型 可以通过字段类型名字得到字段类型的类 String -> Class
private String name; // 字段名
public FieldDeclare(String name) {
this.clair = "java.lang.String";
this.name = name;
}
public FieldDeclare(String clair, String name) {
this.clair = clair;
this.name = name;
}
public String getClair() {
return clair;
}
public void setClair(String clair) {
this.clair = clair;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
private static Boolean check(File file, Integer sheetIndex, List listField, Class c){
try {
//sheetIndex检查
if (sheetIndex < 0) {
System.out.println("sheet索引值不能为负数");
return false;
}
//1、listField集合校验
if (listField == null || listField.size() == 0) {
System.out.println("listField集合为空, 表头元素对应属性名字的集合是必须的");
return false;
}
for (String ex : listField) {
if(StringUtils.isEmpty(ex)){
System.out.println("listField中不允许出现空字符串");
return false;
}
}
//2、文件校验
String path = file.getPath();
try ( InputStream inTemp = new FileInputStream(path) ){
if (inTemp == null) {//有必要吗
System.out.println("无法得到文件流");
return false;
}
} catch (FileNotFoundException e) {
System.out.println("文件路径错误或在这个路径下找不到此文件");
return false;
}
if (c == null) {
System.out.println("c为空，Class是必须的");
return false;
}
return true;
}catch (Exception e){
System.out.println("HPoiUtils - - - 未知的初始化错误...");
}
return false;
}
private static Boolean check(File file, String sheetName, List listField, Class c){
try {
//sheetName检查
if (sheetName == null || "".equals(sheetName)) {
System.out.println("sheet名字不能为空");
return false;
}
//1、listField集合校验
if (listField == null || listField.size() == 0) {
System.out.println("listField集合为空, 表头元素对应属性名字的集合是必须的");
return false;
}
for (String ex : listField) {
if(StringUtils.isEmpty(ex)){
System.out.println("listField中不允许出现空字符串");
return false;
}
}
//2、文件校验
String path = file.getPath();
try ( InputStream inTemp = new FileInputStream(path) ){
if (inTemp == null) {//有必要吗
System.out.println("无法得到文件流");
return false;
}
} catch (FileNotFoundException e) {
System.out.println("文件路径错误或在这个路径下找不到此文件");
return false;
}
if (c == null) {
System.out.println("c为空，Class是必须的");
return false;
}
return true;
}catch (Exception e){
System.out.println("HPoiUtils - - - 未知的初始化错误...");
}
return false;
}
private static List getObj(List listField, List listResultStr, Class c, boolean hasHeader){
//字符串集合对象化
int fieldNum = listField.size();//属性个数
int lenResultStr = listResultStr.size();//Excel中的数据总量
List listResultObj = new ArrayList(lenResultStr / fieldNum);//结果对象, 设置的容量可能会多一个 hasHeader : true
int index = hasHeader ? fieldNum : 0;//从第哪个下标开始读取listResultStr中的数据
//循环读取ListS, 每一个for循环封装一个对象
for ( ; index < lenResultStr; index += fieldNum) {
try {
Object o = c.newInstance();//准备实例
//循环set对象的fieldNum个属性值。一个循环后则一个对象包装完成
for (int j = 0; j < fieldNum; j++) {
if (index + j < lenResultStr) {//大步长 + 小步长 确定了数据在ListS中的索引值
String dataStr = listResultStr.get(index + j);//拿到一个数据
if(StringUtils.isNotBlank(dataStr)){//如果数据为nul或者""那么直接跳过，最后对象中此属性为null
String filedName = listField.get(j);
invokeSetterByName(c, o, filedName, dataStr);
}//if
}//if
}
listResultObj.add(o);//装入集合之后该做什么了呢
}catch (Exception e){
e.printStackTrace();
}
}//for
return listResultObj;
}
private static void invokeSetterByName(Class c, Object o, String filedName, String dataStr) throws IllegalAccessException, InvocationTargetException {
String setterMethodName = getSetterMethodName(filedName);
Method[] methods = c.getDeclaredMethods();//拿到所有方法
for (Method method : methods) {
if(setterMethodName.equals(method.getName())){//必须得到同名的方法，然后获取到setter()参数类型，
//要先判断method的参数类型
Class[] types = method.getParameterTypes();
String typeName = types[0].getName();//类型名称 java.lang.Float
Object data = regression(typeName, dataStr);//根据类型名称将String数据转换成需要的类型的数据 sD已经判断过不为空了
method.invoke(o,data);//执行set方法
break;//跳出for循环
//应该加一个标识，sign = 1 如果sign == 0 那么说明没有进入for循环，那么就说明没有setXxx的方法，就说明这个表头字段错了
}
}//for
}
private static String getSetterMethodName(String filedName) {
StringBuilder sb = new StringBuilder();
sb.append("set");
return sb.append(Tran(filedName)).toString();
}
private static List getStr(File file, Integer sheetIndex, Integer len){
//分情况处理Excel
String path = file.getPath();
InputStream in = null;
try {
in = new FileInputStream(path);
} catch (FileNotFoundException e) {
System.out.println("文件路径错误或在这个路径下找不到此文件");
return null;
}
List listResultStr = null;
try {
String s = StringUtils.substringAfterLast(path, ".");//文件名，分割
if("xls".equals(s)){
listResultStr = doXls(in,sheetIndex,len);
}else if("xlsx".equals(s)){
listResultStr = doXlsx(in,sheetIndex,len);
}else{
System.out.println("文件格式不支持，请选择xls或xlsx格式的Excel文件");
}
} catch (IOException e) {
System.out.println("通过文件流获取Excel对象失败");
} finally {
try {
in.close();//关闭文件/流
} catch (IOException e) {
System.out.println("关闭文件流失败");
}
}
return listResultStr;
}
private static List getStr(File file, String sheetName, Integer len){
//分情况处理Excel
String path = file.getPath();
InputStream in = null;
try {
in = new FileInputStream(path);
} catch (FileNotFoundException e) {
System.out.println("文件路径错误或在这个路径下找不到此文件");
return null;
}
List listResultStr = null;
try {
String s = StringUtils.substringAfterLast(path, ".");//文件名，分割
if("xls".equals(s)){
listResultStr = doXls(in,sheetName,len);
}else if("xlsx".equals(s)){
listResultStr = doXlsx(in,sheetName,len);
}else{
System.out.println("文件格式不支持，请选择xls或xlsx格式的Excel文件");
}
} catch (IOException e) {
System.out.println("通过文件流获取Excel对象失败");
} finally {
try {
in.close();//关闭文件/流
} catch (IOException e) {
System.out.println("关闭文件流失败");
}
}
return listResultStr;
}
private static List doXls(InputStream in, Integer sheetIndex, Integer len) throws IOException {
List listResultStr = new ArrayList();
//获取Excel文件对象
HSSFWorkbook workbook = new HSSFWorkbook(in);
//获取sheet表对象
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);//只允许有一个sheet表
int nRow = sheet.getLastRowNum();//若excel表中有3行，那么nRow=2 : 0,1,2
for(int i=0; i <= nRow; i++){
HSSFRow row = sheet.getRow(i);//row:第i+1行
if(row != null)
for(int j = 0; j < len; j++){
HSSFCell cell = row.getCell(j);//cell:第i+1行第j+1列单元格
if(cell == null){
listResultStr.add("");
}else{
String str = cell.toString();
listResultStr.add(str);
}
}
}
return listResultStr;
}
private static List doXls(InputStream in, String sheetName, Integer len) throws IOException {
List listResultStr = new ArrayList();
//获取Excel文件对象
HSSFWorkbook workbook = new HSSFWorkbook(in);
//获取sheet表对象
final HSSFSheet sheet = workbook.getSheet(sheetName);
int nRow = sheet.getLastRowNum();//若excel表中有3行，那么nRow=2 : 0,1,2
for(int i=0; i <= nRow; i++){
HSSFRow row = sheet.getRow(i);//row:第i+1行
if(row != null)
for(int j = 0; j < len; j++){
HSSFCell cell = row.getCell(j);//cell:第i+1行第j+1列单元格
if(cell == null){
listResultStr.add("");
}else{
String str = cell.toString();
listResultStr.add(str);
}
}
}
return listResultStr;
}
private static List doXlsx(InputStream in, Integer sheetIndex, Integer len) throws IOException {
List listResultStr = new ArrayList();
XSSFWorkbook workbook = new XSSFWorkbook(in);
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
int nRow = sheet.getLastRowNum();
for(int i = 0; i <= nRow; i++){
XSSFRow row = sheet.getRow(i);
if(row != null) //不会读取空行
for(int j = 0; j < len; j++){
XSSFCell cell = row.getCell(j);
if(cell == null){
listResultStr.add("");
}else {
String str = cell.toString();
listResultStr.add(str);
}
}
}
return listResultStr;
}
private static List doXlsx(InputStream in, String sheetName, Integer len) throws IOException {
List listResultStr = new ArrayList();
XSSFWorkbook workbook = new XSSFWorkbook(in);
final XSSFSheet sheet = workbook.getSheet(sheetName);
int nRow = sheet.getLastRowNum();
for(int i = 0; i <= nRow; i++){
XSSFRow row = sheet.getRow(i);
if(row != null)
for(int j = 0; j < len; j++){
XSSFCell cell = row.getCell(j);
if(cell == null){
listResultStr.add("");
}else {
String str = cell.toString();
listResultStr.add(str);
}
}
}
return listResultStr;
}
private static Object regression(String typeName,String dataStr){
if (typeName.contains("Integer")) {
int n = dataStr.indexOf(".");
if(n > 0) dataStr = dataStr.substring(0, n);
}
try {
Class c = Class.forName(typeName);
Constructor constructor = c.getConstructor(String.class);//Float有一个传入String字符串的构造方法
Object o = constructor.newInstance(dataStr);
return o;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private static String Tran(String s){
char[] chars = s.toCharArray();
if (chars[0] >= 97 && chars[0] <= 122)
chars[0] -= 32;
return String.valueOf(chars);
}
public static List exeCsv(File file, List listField, Class c, boolean hasHeader,String codeType, int rowPar) {
//参数检查
Boolean check = check(file, 0, listField, c);
if(!check){
return null;
}
//得到结果
List listResultObj = generateResultCSV(file, listField, c, hasHeader,codeType,rowPar);
return listResultObj;
}
public static List exeCsv(File file, List listField, Class c,String codeType, int rowPar) {
return exeCsv(file, listField, c, true,codeType,rowPar);
}
public static List exeCsv(File file, List listField,String codeType, int rowPar) {
final Class c = generateClass(listField);
return exeCsv(file, listField, c, true,codeType,rowPar);
}
private static List generateResultCSV(File file, List listField, Class c, boolean hasHeader,String codeType, int rowPar) {
List listResultStr = readCSV(file,codeType,rowPar); //得到字符串数据集合
return getObj(listField, listResultStr, c, hasHeader); //得到对象数据集合
}
public static List readCSV(File file,String codeType, int rowPar){
List listResultStr = new ArrayList<>(10);
try ( BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(file),codeType)) ){
String line = null;
String[] items = null;
while((line = bufferedReader.readLine()) != null){
//数据行
items = line.split(",", rowPar);
listResultStr.addAll(Arrays.asList(items));
}
} catch (IOException e) {
e.printStackTrace();
}
return listResultStr;
}
}
调用
Listlist = new ArrayList<>(); List listField = genListField(); //执行 // poi解析文件 文件编码类型为GBK // 由于csv文件的特性，根据，进行，分割数据，每行分隔为8（文件的表头列数）部分 传错数值会导致程序运行失败（失败原因可能是某个数之中包含，导致之后的数据全部错误） list = HPoiUtils.exeCsv(new File(url), listField, QizhiVideoAccountStateModel.class,"GBK",8);
//准备属性集合list
private static List genListField() {
List listField = new ArrayList(10);//建议使用ArrayList，get(i)操作更快
listField.add("assets_name");//实体类中的字段
listField.add("assets_ip");
listField.add("sys_account_name");
listField.add("executor");
listField.add("operate_type");
listField.add("end_time");
listField.add("result_detail");
listField.add("detail");
return listField;
}
csv文件截图
org.apache.poi poi-ooxml3.17 org.javassist javassist3.18.2-GA org.apache.commons commons-lang33.5
