利用poi来向execl中写入对象

2018-06-18 01:54:48来源：未知阅读 ()

附上jar包下载链接:

附上百度网盘下载连接:

链接：https://pan.baidu.com/s/1t_jXUq3CuhZo9j_UI4URAQ 密码：r2qi

package com.wz.poi.execl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;

public class WriteExcel {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";

/**
*
* @param dataList 数据集合
* @param cloumnCount 对象的属性列数
* @param finalXlsxPath 文件路径
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public static void writeExcel(List<Map> dataList, int cloumnCount,String finalXlsxPath){
OutputStream out = null;
try {
// 获取总列数
int columnNumCount = cloumnCount;
// 读取Excel文档
File finalXlsxFile = new File(finalXlsxPath);
Workbook workBook = getWorkbok(finalXlsxFile);
// sheet 对应一个工作页
Sheet sheet = workBook.getSheetAt(0);
/**
* 删除原有数据，除了属性列
*/
int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
System.out.println("原始数据总行数，除属性列：" + rowNumber);
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
sheet.removeRow(row);
}
// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
Map map = dataList.get(0);
// 得到要插入的map集合
Collection coll = map.values();
// 将coll转化为Object数组
Object[] objectList = new Object[coll.size()];
coll.toArray(objectList);
/**
* 往Excel中写新数据
*/
System.out.println("================================" + objectList.length);
for (int j = 0; j < objectList.length; j++) {
// 创建一行：从第二行开始，跳过属性列
Row row = sheet.createRow(j + 1);

Object object = objectList[j];
System.out.println(object);

// 获取对象的属性名列表
String[] fields = getFiledName(object);
// 循环遍历属性列表
for(int x = 0;x < fields.length; x++) {
if(getFiledValueByName(fields[x], object) != null) {
row.createCell(x).setCellValue(getFiledValueByName(fields[x], object).toString());
System.out.println(getFiledValueByName(fields[x], object).toString()+"=="+x+"==");
} else {
System.out.println(x + "+++++");
row.createCell(x).setCellValue("");
}

}

}
// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(out != null){
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("数据导出成功");
}

/**
* 判断Excel的版本,获取Workbook
* @param in
* @param filename
* @return
* @throws IOException
*/
public static Workbook getWorkbok(File file) throws IOException{
Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if(file.getName().endsWith(EXCEL_XLS)){ //Excel 2003
wb = new HSSFWorkbook(in);
}else if(file.getName().endsWith(EXCEL_XLSX)){ // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}

/**
* 根据属性名获取属性值
*
*/
private static Object getFiledValueByName(String fieldName, Object o){
try {
String firstLetter = fieldName.substring(0,1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = o.getClass().getMethod(getter, new Class[]{});
Object value = method.invoke(o, new Object[]{});
return value;
} catch (Exception e) {
System.out.println(e.getMessage());
return null;
}
}

/**
* 获取属性名数组
*/
private static String[] getFiledName(Object o){
Field[] fields = o.getClass().getDeclaredFields();
String[] fieldNames = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
fieldNames[i] = fields[i].getName();
}
return fieldNames;
}

// 简单的测试
/*@SuppressWarnings("unchecked")
public static void main(String[] args) {
Student stu = new Student(1, "王智", "男", "18", "java工程师");
Student stu2 = new Student(2, "王智", "男", "18", "java工程师");
Map map = new HashMap<>();
map.put(1, stu);
map.put(2, stu2);
List<Map> dataList = new ArrayList<>();
dataList.add(map);
writeExcel(dataList, getFiledName(stu).length, "H:\\MyTest\\Java\\test3.xlsx");
}*/

}