package com.wz.poi; import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.Outpu ...
package com.wz.poi;
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);
/**
* 往Excel中寫新數據
*/
for (int j = 0; j < dataList.size(); j++) {
// 創建一行：從第二行開始，跳過屬性列
Row row = sheet.createRow(j + 1);
// 得到要插入的map集合
Collection coll = map.values();
// 將coll轉化為Object數組
Object[] objectList = new Object[coll.size()];
coll.toArray(objectList);
// 遍曆數組進行導入
for(int i = 0; i < objectList.length; i++) {
Object object = objectList[i];
System.out.println(object);
for (int k = 0; k <= columnNumCount; k++) {
// 獲取對象的屬性名列表
String[] fields = getFiledName(object);
// 迴圈遍歷屬性列表
for(int x = 0;x < fields.length; x++) {
row.createCell(x).setCellValue(getFiledValueByName(fields[x], object).toString());
}
}
}
}
// 創建文件輸出流，準備輸出電子錶格：這個必須有，否則你在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工程師");
Map map = new HashMap<>();
map.put("1", stu);
List<Map> dataList = new ArrayList<>();
dataList.add(map);
writeExcel(dataList, getFiledName(stu).length, "H:\\MyTest\\Java\\test3.xlsx");
}*/
}
還是上個文章的那個jar包,這裡面同樣用了反射,明天繼續java的設計模式.
