自定义写入excel文件
List list：写入excel文件的数据list
saveFile：写入文件的地址
此方法会根据当前的实体类名创建sheet，调用多次该方法，即可实现不同的数据集写入到同一个文件中，以sheet区分
public boolean writeExcel(List<T> list, String saveFile){
boolean isWrite = false;
if (list==null||list.size()==0){
return isWrite;
}
T t = list.get(0);
File file = new File(saveFile);
FileOutputStream outputStream = null;
FileInputStream fileInputStream = null;
try {
XSSFWorkbook xssfWorkbook = null;
if (file.exists()){
fileInputStream = new FileInputStream(file);
xssfWorkbook = new XSSFWorkbook(fileInputStream);
}else{
xssfWorkbook = new XSSFWorkbook();
}
int sheets = xssfWorkbook.getNumberOfSheets();
String className = t.getClass().getSimpleName();
XSSFSheet sheet = null;
sheet = xssfWorkbook.createSheet(className);
XSSFRow row0 = sheet.createRow(0);
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
row0.createCell(i).setCellValue(fields[i].getName());
}
outputStream = new FileOutputStream(file);
for (int i = 0; i < list.size(); i++){
T eachT = list.get(i);
XSSFRow row = sheet.createRow(i + 1);
for (int j = 0; j < fields.length; j++) {
String fieldName = fields[j].getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Class aClass = eachT.getClass();
Method method = aClass.getMethod(getMethodName, new Class[]{});
Object value = method.invoke(eachT, new Object[]{});
String textValue = null;
if (value instanceof Date) {
Date date1 = (Date) value;
SimpleDateFormat sdff = new SimpleDateFormat(
"yyyy-MM-dd");
textValue = sdff.format(date1);
} else if (value == null) {
textValue = "";
} else {
textValue = value.toString();
}
XSSFCell cell = row.createCell(j);
cell.setCellValue(textValue);
}
}
xssfWorkbook.write(outputStream);
outputStream.flush();
isWrite = true;
}catch(FileNotFoundException e) {
logger.error("无文件！！！");
e.printStackTrace();
}catch (Exception e){
logger.error("生成excel失败！！！");
e.printStackTrace();
}finally {
IoUtils.closeInputStram(fileInputStream);
IoUtils.closeOutputStram(outputStream);
}
return isWrite;
}
自定义读取excel文件
fileArr：文件地址
此方法会根据参数文件地址读取文件，获取map对象，该map对象为——sheet名（写入时的类名）：list集合
获取map后判断sheet类名是哪个，在对list集合进行操作
public static Map<String,List<Object>> readExcel(String fileArr){
//sheet---里面的数据内容
Map<String,List<Object>> map = new HashMap();
File file = new File(fileArr);
FileInputStream fileInputStream = null;
XSSFWorkbook xssfWorkbook = null;
try {
if (file.exists()){
fileInputStream = new FileInputStream(file);
xssfWorkbook = new XSSFWorkbook(fileInputStream);
int sheets = xssfWorkbook.getNumberOfSheets();
for (int i=0;i<sheets;i++){
List<Object> lists = new ArrayList();
Sheet sheet = xssfWorkbook.getSheetAt(i);
String sheetName = xssfWorkbook.getSheetName(i);
String className = "com.waypolice.pojo."+sheetName;
Class clazz = Class.forName(className);
Field[] fields = clazz.getDeclaredFields();
//获取行数，每一行为每一个对象的数据
int row = sheet.getLastRowNum();
//第一行
Row row2 = sheet.getRow(0);
for (int j = 1; j <= row; j++){
Object obj = clazz.newInstance();
Row row1 = sheet.getRow(j);
if(row1 != null && !row1.toString().isEmpty()){
short lastCellNum = row2.getLastCellNum();
for (short k = 0; k < lastCellNum; k++) {
Cell cell2 = row2.getCell(k);
String cell1Str = cell2 == null ? "" : cell2.toString();
if (cell1Str != null && !cell1Str.equals("")) {
String methodName = "set" + cell1Str.substring(0, 1).toUpperCase() + cell1Str.substring(1);
String value = "";
Cell cell = row1.getCell(k);
value = cell == null ? "" : cell.toString();
//根据实例化的对象的Class对象获取实例对象的方法 ,getMethod的参数为要获取的方法的方法名，和参数的类型也即是要赋值的属性的类型
Method method = null;
Class<?> type = null;
if (cell1Str.equals(fields[k].getName())) {
method = clazz.getMethod(methodName, fields[k].getType());
type = fields[k].getType();
} else {
continue;
}
//在excel中获取的值都是String,所以要根据属性的类型进行值的转换
if (type.toString().equals("class java.lang.String")) {
//调用实例对象obj的set方法进行对属性的数值
method.invoke(obj, value);
} else if (type.toString().equals("int") || type.toString().equals("class java.lang.Integer")) {
if (value != null && !"".equals(value)) {
method.invoke(obj, Integer.parseInt(value.replace(".0", "")));
} else {
method.invoke(obj, new Object[]{null});
}
}else if (type.toString().equals("long") || type.toString().equals("class java.lang.Long")) {
if (value != null && !"".equals(value)) {
method.invoke(obj, Long.parseLong(value.replace(".0", "")));
} else {
method.invoke(obj, new Object[]{null});
}
} else if (type.toString().equals("class java.util.Date")) {
if (value != null && !"".equals(value)) {
method.invoke(obj, new SimpleDateFormat("yyyy-MM-dd").parse(value));
} else {
method.invoke(obj, new Object[]{null});
}
} else if (type.toString().equals("class java.lang.Float")) {
if (value != null && !"".equals(value)) {
method.invoke(obj, Float.parseFloat(value));
} else {
method.invoke(obj, new Object[]{null});
}
}
}
}
lists.add(obj);
}
}
map.put(sheetName,lists);
}
}
} catch (FileNotFoundException e) {
logger.error("文件读取失败！！");
e.printStackTrace();
} catch (IOException e) {
logger.error("excel POI失败...");
e.printStackTrace();
} catch (ClassNotFoundException e) {
logger.error("类 反射失败...");
e.printStackTrace();
} catch (NoSuchMethodException e) {
logger.error("读写文件 赋值属性时失败...");
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (InstantiationException e) {
logger.error("创建对象失败...");
e.printStackTrace();
}
return map;
}
精准读取excel对象（确定该文件中只存在一个sheet时使用）
fileArr：文件地址
className：类名
获取map对象，该map对象为—— className：list集合（获取的list不需要转义）
public Map<String,List<T>> readExcel(String fileArr, String className){
//sheet---里面的数据内容
Map<String,List<T>> map = new HashMap();
File file = new File(fileArr);
FileInputStream fileInputStream = null;
XSSFWorkbook xssfWorkbook = null;
try {
if (file.exists()){
fileInputStream = new FileInputStream(file);
xssfWorkbook = new XSSFWorkbook(fileInputStream);
int sheets = xssfWorkbook.getNumberOfSheets();
for (int i=0;i<sheets;i++){
List<T> lists = new ArrayList();
Sheet sheet = xssfWorkbook.getSheetAt(i);
//判断当前的sheet的名字是否与className相同
String sheetName = sheet.getSheetName();
if(className.equals(sheetName)||className.contains("ExcelVo")){
String classPath = "";
if(className.contains("ExcelVo")){
classPath = "com.waypolice.pojo.handwriting."+className;
}else{
classPath = "com.waypolice.pojo."+className;
}
Class clazz = Class.forName(classPath);
Field[] fields = clazz.getDeclaredFields();
//获取行数，每一行为每一个对象的数据
int row = sheet.getLastRowNum();
//第一行
Row row2 = sheet.getRow(0);
for (int j = 1; j <= row; j++){
T obj = (T)clazz.newInstance();
Row row1 = sheet.getRow(j);
if(row1 != null && !row1.toString().isEmpty()){
short lastCellNum = row2.getLastCellNum();
for (short k = 0; k < lastCellNum; k++) {
Cell cell2 = row2.getCell(k);
String cell1Str = cell2 == null ? "" : cell2.toString();
if (cell1Str != null && !cell1Str.equals("")) {
String methodName = "set" + cell1Str.substring(0, 1).toUpperCase() + cell1Str.substring(1);
String value = "";
Cell cell = row1.getCell(k);
value = cell == null ? "" : cell.toString();
//根据实例化的对象的Class对象获取实例对象的方法 ,getMethod的参数为要获取的方法的方法名，和参数的类型也即是要赋值的属性的类型
Method method = null;
Class<?> type = null;
if (cell1Str.equals(fields[k].getName())) {
method = clazz.getMethod(methodName, fields[k].getType());
type = fields[k].getType();
} else {
continue;
}
//在excel中获取的值都是String,所以要根据属性的类型进行值的转换
if (type.toString().equals("class java.lang.String")) {
//调用实例对象obj的set方法进行对属性的数值
method.invoke(obj, value);
} else if (type.toString().equals("int") || type.toString().equals("class java.lang.Integer")) {
if (value != null && !"".equals(value)) {
method.invoke(obj, Integer.parseInt(value.replace(".0", "")));
} else {
method.invoke(obj, new Object[]{null});
}
}else if (type.toString().equals("long") || type.toString().equals("class java.lang.Long")) {
if (value != null && !"".equals(value)) {
method.invoke(obj, Long.parseLong(value.replace(".0", "")));
} else {
method.invoke(obj, new Object[]{null});
}
} else if (type.toString().equals("class java.util.Date")) {
if (value != null && !"".equals(value)) {
method.invoke(obj, new SimpleDateFormat("yyyy-MM-dd").parse(value));
} else {
method.invoke(obj, new Object[]{null});
}
} else if (type.toString().equals("class java.lang.Float")) {
if (value != null && !"".equals(value)) {
method.invoke(obj, Float.parseFloat(value));
} else {
method.invoke(obj, new Object[]{null});
}
}
}
}
lists.add(obj);
}
}
map.put(className,lists);
}else{
continue;
}
}
}
} catch (FileNotFoundException e) {
logger.error("文件读取失败！！");
e.printStackTrace();
} catch (IOException e) {
logger.error("excel POI失败...");
e.printStackTrace();
} catch (ClassNotFoundException e) {
logger.error("类 反射失败...");
e.printStackTrace();
} catch (NoSuchMethodException e) {
logger.error("读写文件 赋值属性时失败...");
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (InstantiationException e) {
logger.error("创建对象失败...");
e.printStackTrace();
}
return map;
}
扫一扫
628
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
