Excel生成与导出工具类

<!-- pom 使用 xls 格式 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>
<!-- 使用 xlsx 格式 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>

import cn.ecpay.jft.payroll.bean.PayInfoBean;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

   private static String VERSION = "Version 3.0";

   private static String[] header = { "日期", "明细标志" };

   private static String[] fieldNames = { "payDate", "mark"};

   public static void main(String[] args) throws Exception {
//    PayInfoBean payInfoBean = new PayInfoBean();
//    payInfoBean.setPayDate(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
//    payInfoBean.setMark("");

//    List<PayInfoBean> list = new ArrayList<>();

//    list.add(payInfoBean);
//    export(list,"111.xls");
//    System.out.println("finish");

      readExcel("111.xls");
   }

   public static void export(List<PayInfoBean> data,String fileName) {
//        String[] fieldNames = fieldName(T.class);
      Workbook wb = new HSSFWorkbook();
      int rowSize = 0;
      Sheet sheet = wb.createSheet();
      Row row = sheet.createRow(rowSize);
      row.createCell(0).setCellValue(VERSION);

      row = sheet.createRow(rowSize + 1);
      for (int i = 0; i < header.length; i++) {
         row.createCell(i).setCellValue(header[i]);
      }

      try {
         for (int x = 0; x < data.size(); x++) {
            rowSize = 2;
            Row rowNew = sheet.createRow(rowSize + x);
            for (int i = 0; i < header.length; i++) {
               PayInfoBean payInfoBean = data.get(x);
               for (int i1 = 0; i1 < fieldNames.length; i1++) {
                  String methodName = "get" + fieldNames[i].substring(0, 1).toUpperCase()
                        + fieldNames[i].substring(1);// 获取属性的get方法名
                  Method method = payInfoBean.getClass().getMethod(methodName);
                  Object invoke = method.invoke(payInfoBean);// 获取属性值
                  rowNew.createCell(i).setCellValue(invoke == null ? "" : invoke.toString());
               }
            }
         }
      } catch (Exception e) {

      }
      OutputStream outputStream = null;
      try {
         File file = new File(fileName); //实例化File对象
         file.getParentFile().mkdirs(); //创建目录
         try {
             file.createNewFile(); //创建文件
         }catch (Exception e){
             e.printStackTrace();
         }

         outputStream = new FileOutputStream(fileName);
         wb.write(outputStream);
      } catch (Exception e) {

      } finally {
         try {
            if (outputStream != null) {
               outputStream.flush();
               outputStream.close();
            }
         } catch (Exception e) {

         }
         try {
            if (wb != null) {
               wb.close();
            }
         } catch (Exception e) {

         }
      }

   }

/**
* 读取excel
*/
   public static List readExcel(String path) throws Exception {
      List<Map<Integer, String>> list = new ArrayList<>();

      //加载数据源文件
      //把目标文件转化为文件流
      File excelFile = new File(path);

      //判断是否是文件并且是否存在
      if (excelFile.isFile() && excelFile.exists()) {

         //创建输出流对象
         FileInputStream fls = new FileInputStream(excelFile);
         //将输出的流对象引入到解析excel文件的对象中
         Workbook wb = null;
         /*判断文件是xlsx结尾还是xls结尾 声明XSSF或HSSF对象*/
         String[] split = excelFile.getName().split("\\.");
         if (split[1].equals("xlsx")) {
            wb = new XSSFWorkbook(fls);
         } else if (split[1].equals("xls")) {
            wb = new HSSFWorkbook(fls);
         }

         //获取工作表页数据
         //读取第1页的数据
         Sheet sheet = wb.getSheetAt(0);
         //获取工作表页中行数据
         //读取的行，如果sheet中一行数据没有返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回n-1
         int firstRowIndex = sheet.getFirstRowNum();
         //读取的总的行数
         int lastRowIndex = sheet.getLastRowNum();

         //可以通过for循环遍历每一行的数据再在循环内对列的数据进行处理
         for (int i = firstRowIndex; i <= lastRowIndex; i++) {
            Map<Integer, String> map = new HashMap<>();
            //获取指定行的数据
            Row row = sheet.getRow(i);
            //获取工作表页中指定行的指定列数据
            //当前行的第一个列的数据的下标
            int firstCellIndex = row.getFirstCellNum();
            //row中一列数据都没有则返回-1，只有第一列有数据则返回-1，最后有数据的列是第n列则返回n
            int lastCeeIndex = row.getLastCellNum();
            //获取当前下标（列）的单元格数据
            for (int j = firstCellIndex; j < lastCeeIndex; j++) {
               Cell cell = row.getCell(j);
               map.put(j, cell.getStringCellValue());
            }
            list.add(map);
         }
      } else {
         System.out.println("文件类型错误");
      }
      System.out.println("list: " + list);
      return list;
   }
//    private static String[] fieldName(Class clazz) {
//        Field[] declaredFields = clazz.getDeclaredFields();
//        String[] fieldNames = new String[declaredFields.length];
//        for (int i = 0; i < declaredFields.length; i++) {
//            fieldNames[i] = declaredFields[i].getName(); //通过反射获取属性名
//        }
//        return fieldNames;
//    }
}