Java使用excel工具類導(dǎo)出對象功能示例

package com.gcloud.common;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
/**
 * Created by charlin on 2017/9/7.
 */
public class ExcelExportUtil {
  // 1、定義工作表
  private SXSSFWorkbook workbook;
  // 2、定義sheet
  private Sheet sheet;
  // 3、定義保存在內(nèi)存中的數(shù)量,-1表示手動(dòng)控制
  private int flushRows;
  /**
   * 4、導(dǎo)出文件行數(shù)
   */
  private int rowNum;
  /**
   * 5、導(dǎo)出文件列數(shù)
   */
  private int colNum;
  /**
   * 6、導(dǎo)出文件的存放路徑
   */
  private String filePath;
  /**
   * 7、下載導(dǎo)出文件的路徑
   */
  private String fileWebPath;
  /**
   * 8、文件名稱前綴
   */
  private String filePrefix;
  /**
   * 9、導(dǎo)出文件全路徑
   */
  private String fileAllPath;
  /**
   * 10、導(dǎo)出文件列標(biāo)題
   */
  private List<String> fieldNames;
  /**
   * 11、導(dǎo)出文件每列代碼，用于反射獲取對象屬性值
   */
  private List<String> fieldCodes;
  //---構(gòu)造方法-----------------------------------------
  public ExcelExportUtil() {
  }
  public ExcelExportUtil(SXSSFWorkbook workbook) {
    this.workbook = workbook;
  }
  public static ExcelExportUtil start(String filePath, String fileWebPath, String filePrefix, List<String> fieldNames, List<String> fieldCodes, int flushRows) throws Exception {
    ExcelExportUtil excelExportUtil = new ExcelExportUtil();
    excelExportUtil.setFilePath(filePath);
    excelExportUtil.setFileWebPath(fileWebPath);
    excelExportUtil.setFilePrefix(filePrefix);
    excelExportUtil.setFieldNames(fieldNames);
    excelExportUtil.setFieldCodes(fieldCodes);
    //設(shè)置輸出行數(shù)
    excelExportUtil.setWorkbook(new SXSSFWorkbook(flushRows));
    //設(shè)置sheet
    excelExportUtil.setSheet(excelExportUtil.getWorkbook().createSheet());
    excelExportUtil.writeTitles();
    return excelExportUtil;
  }
  /**
   * 創(chuàng)建標(biāo)題
   *
   * @throws Exception
   */
  public void writeTitles() throws Exception {
    rowNum = 0;
    colNum = fieldNames.size();
    //創(chuàng)建行
    Row row = sheet.createRow(rowNum);
    //在每列第一行輸出標(biāo)題
    for (int i = 0; i < colNum; i++) {
      Cell cell = row.createCell(i);
      cell.setCellValue(fieldNames.get(i));
    }
  }
  /**
   * 寫入對象數(shù)據(jù)
   *
   * @param datalist
   * @throws Exception
   */
  public void writeDatas(List datalist) throws Exception {
    for (int i = 0; i < datalist.size(); i++) {
      rowNum++;
      //不斷創(chuàng)建行
      Row row = sheet.createRow(rowNum);
      for (int j = 0; j < fieldCodes.size(); j++) {
        Object obj = datalist.get(j);
        //獲得get方法返回的值
        Object value = invokeMethod(obj, fieldCodes.get(j), new Object[]{});
        Cell cell = row.createCell(j);
        cell.setCellValue(value != null ? value.toString() : "");
      }
    }
  }
  /**
   * 獲得get方法返回的值
   * @param owner
   * @param fieldname
   * @param args
   * @return
   * @throws Exception
   */
  private Object invokeMethod(Object owner, String fieldname, Object[] args) throws Exception {
    String methodName = "get" + fieldname.substring(0,1).toUpperCase() + fieldname.substring(1);
    Class ownerClass = owner.getClass();
    Class[] argsClass = new Class[args.length];
    for (int i = 0, j = argsClass.length ; i <j ; i++) {
      argsClass[i] = args[i].getClass();
    }
    Method method = ownerClass.getMethod(methodName, argsClass);
    return method.invoke(owner, args);
  }
  /**
   * 向?qū)С鑫募憯?shù)據(jù)
   *
   * @param datalist 存放字符串?dāng)?shù)組
   * @return
   */
  public void writeDatasByStr(List<String> datalist) throws Exception {
    rowNum++;
    Row row = sheet.createRow(rowNum);
    int dataSize = datalist.size();
    for (int i = 0; i < colNum; i++) {
      Cell cell = row.createCell(i);
      cell.setCellValue(dataSize > i ? datalist.get(i) : "");
    }
  }
  /**
   * 手動(dòng)刷新方法,如果flushRows為-1則需要使用此方法手動(dòng)刷新內(nèi)存
   * @param flushNum
   * @throws Exception
   */
  public void flush(int flushNum) throws Exception{
    ((SXSSFSheet)sheet).flushRows(flushNum);
  }
  /**
   * 導(dǎo)出文件
   * @return
   * @throws Exception
   */
  public String exportFile() throws Exception{
    String fileName = filePrefix + "_" + DateUtil.getCurrentTimeFileName() + ".xlsx";
    FileOutputStream fos = new FileOutputStream(filePath + fileName);
    workbook.write(fos);
    fos.close();
    setFileAllPath(fileWebPath + fileName);
    return fileWebPath + fileName;
  }
  /**
   * 導(dǎo)出excel通用方法
   * @param field
   * @param path
   * @param webpath
   * @param filePrefix
   * @param datas
   * @param flushRows
   * @return
   * @throws Exception
   */
  public ExcelExportUtil excelExport(String field,String path,String webpath,String filePrefix,List datas,int flushRows) throws Exception{
    //導(dǎo)出字段代碼和名稱
    String[] fieldArr = field.split(",");
    //獲取導(dǎo)出字段名稱
    List<String> fieldNames = new ArrayList<String>();
    //獲取導(dǎo)出字段代碼
    List<String> fieldCodes = new ArrayList<String>();
    for (int i = 0; i < fieldArr.length; i++) {
      String names = fieldArr[i];
      String[] nameArr = names.split("#");
      fieldNames.add(nameArr[1]);
      fieldCodes.add(nameArr[0]);
    }
    //開導(dǎo)出
    ExcelExportUtil exportUtil = ExcelExportUtil.start(path, webpath,filePrefix, fieldNames,fieldCodes, flushRows);
    //導(dǎo)數(shù)據(jù)
    exportUtil.writeDatas(datas);
    exportUtil.exportFile();
    return exportUtil;
  }
  public static void main(String[] args) {
    //使用方法，調(diào)用
    //excelExport
  }
  //----get set-------------------------------------------------
  public SXSSFWorkbook getWorkbook() {
    return workbook;
  }
  public void setWorkbook(SXSSFWorkbook workbook) {
    this.workbook = workbook;
  }
  public Sheet getSheet() {
    return sheet;
  }
  public void setSheet(Sheet sheet) {
    this.sheet = sheet;
  }
  public int getFlushRows() {
    return flushRows;
  }
  public void setFlushRows(int flushRows) {
    this.flushRows = flushRows;
  }
  public int getRowNum() {
    return rowNum;
  }
  public void setRowNum(int rowNum) {
    this.rowNum = rowNum;
  }
  public int getColNum() {
    return colNum;
  }
  public void setColNum(int colNum) {
    this.colNum = colNum;
  }
  public String getFilePath() {
    return filePath;
  }
  public void setFilePath(String filePath) {
    this.filePath = filePath;
  }
  public String getFileWebPath() {
    return fileWebPath;
  }
  public void setFileWebPath(String fileWebPath) {
    this.fileWebPath = fileWebPath;
  }
  public String getFilePrefix() {
    return filePrefix;
  }
  public void setFilePrefix(String filePrefix) {
    this.filePrefix = filePrefix;
  }
  public String getFileAllPath() {
    return fileAllPath;
  }
  public void setFileAllPath(String fileAllPath) {
    this.fileAllPath = fileAllPath;
  }
  public List<String> getFieldNames() {
    return fieldNames;
  }
  public void setFieldNames(List<String> fieldNames) {
    this.fieldNames = fieldNames;
  }
  public List<String> getFieldCodes() {
    return fieldCodes;
  }
  public void setFieldCodes(List<String> fieldCodes) {
    this.fieldCodes = fieldCodes;
  }
}