import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.Iterator;

import java.util.List;

import java.util.Properties;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.lang.reflect.Field;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class ReadWriteExcelFile {

    private static Properties props = new Properties();

    static {

        try {

            InputStream is = ReadWriteExcelFile.class.getClassLoader().getResourceAsStream("META-INF/ExcelHeader.properties");

            props.load(is);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public static String getValue(String key) {

        String value = "";

        if (props.containsKey(key)) {

            value = props.getProperty(key, "");

        }

        return value;

    }

    private final static Logger logger = LoggerFactory.getLogger(ReadWriteExcelFile.class);

    @SuppressWarnings({ "resource", "rawtypes" })

    public static void readXLSFile() throws IOException

    {

        InputStream ExcelFileToRead = new FileInputStream("E:/source/Test.xls");

        HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);

        HSSFSheet sheet=wb.getSheetAt(0);

        HSSFRow row;

        HSSFCell cell;

        Iterator rows = sheet.rowIterator();

        while (rows.hasNext())

        {

            row=(HSSFRow) rows.next();

            Iterator cells = row.cellIterator();

            while (cells.hasNext())

            {

                cell=(HSSFCell) cells.next();

                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)

                {

                    System.out.print(cell.getStringCellValue()+" ");

                }

                else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)

                {

                    System.out.print(cell.getNumericCellValue()+" ");

                }

                else

                {

                    //U Can Handel Boolean, Formula, Errors

                }

            }

            System.out.println();

        }

    }

    public static void writeXLSFile(List<? extends Recording> records) throws IOException{

        //String directory=getValue("excel.file.directory");

        String directory="E:/source";

        Date d = new Date();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        String date = sdf.format(d);

        Recording record=records.get(0);

        Class<? extends Recording> cls=record.getClass();

        String className=cls.getCanonicalName();

        String[] nameAlias=className.split("\\.");

        String excelFileName=directory+File.separator+nameAlias[nameAlias.length-1]+date+".xls";

        writeXLSFile(records,excelFileName);

    }

    @SuppressWarnings("resource")

    public static void writeXLSFile(List<? extends Recording> records,String excelFileName) throws IOException{

        //String excelFileName = "E:/source/Test.xls";

        String sheetName = "Sheet1";//name of sheet

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet sheet = wb.createSheet(sheetName) ;

        Recording record;

        //excel header

        HSSFRow row = sheet.createRow(0);

        record=records.get(0);

        Class<? extends Recording> cls=record.getClass();

        String className=cls.getCanonicalName();

        String[] nameAlias=className.split("\\.");

        Field[] fields=cls.getDeclaredFields();

        //去除serialVersionUID列,

        List<Field> fieldsNoSer=new ArrayList<Field>();

        for(int i=0;i<fields.length;i++){

            String fieldName=fields[i].getName();

            if(fieldName.equalsIgnoreCase("serialVersionUID")){

                continue;

            }else{

                fieldsNoSer.add(fields[i]);

            }

        }

        for(int i=0;i<fieldsNoSer.size();i++){

            HSSFCell cell = row.createCell(i);

            String fieldName=fieldsNoSer.get(i).getName();

            cell.setCellValue(getValue(nameAlias[nameAlias.length-1]+"."+fieldName));

        }

        //iterating r number of rows

        for (int r=0;r < records.size(); r++ )

        {

            row = sheet.createRow(r+1);

            record=records.get(r);

            //table content

            for (int c=0;c < fieldsNoSer.size(); c++ )

            {

                HSSFCell cell = row.createCell(c);

                //加header,方法总变量的首字母大写

                String fieldName=fieldsNoSer.get(c).getName();

                try {

                    Method method=cls.getDeclaredMethod("get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1));

                    Object ret=method.invoke(record);

                    if(null!=ret){

                        cell.setCellValue(method.invoke(record).toString());

                    }

                } catch (Exception e) {

                    logger.info("write xls error,please check it");

                }

            }

        }

        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        //write this workbook to an Outputstream.

        wb.write(fileOut);

        fileOut.flush();

        fileOut.close();

    }

    @SuppressWarnings({ "resource", "unused", "rawtypes" })

    public static void readXLSXFile() throws IOException

    {

        InputStream ExcelFileToRead = new FileInputStream("E:/source/Test1.xlsx");

        XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);

        XSSFWorkbook test = new XSSFWorkbook(); 

        XSSFSheet sheet = wb.getSheetAt(0);

        XSSFRow row;

        XSSFCell cell;

        Iterator rows = sheet.rowIterator();

        while (rows.hasNext())

        {

            row=(XSSFRow) rows.next();

            Iterator cells = row.cellIterator();

            while (cells.hasNext())

            {

                cell=(XSSFCell) cells.next();

                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)

                {

                    System.out.print(cell.getStringCellValue()+" ");

                }

                else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)

                {

                    System.out.print(cell.getNumericCellValue()+" ");

                }

                else

                {

                    //U Can Handel Boolean, Formula, Errors

                }

            }

            System.out.println();

        }

    }

    @SuppressWarnings("resource")

    public static void writeXLSXFile() throws IOException {

        String excelFileName = "E:/source/Test1.xlsx";//name of excel file

        String sheetName = "Sheet1";//name of sheet

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFSheet sheet = wb.createSheet(sheetName) ;

        //iterating r number of rows

        for (int r=0;r < 5; r++ )

        {

            XSSFRow row = sheet.createRow(r);

            //iterating c number of columns

            for (int c=0;c < 5; c++ )

            {

                XSSFCell cell = row.createCell(c);

                cell.setCellValue("Cell "+r+" "+c);

            }

        }

        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        //write this workbook to an Outputstream.

        wb.write(fileOut);

        fileOut.flush();

        fileOut.close();

    }

    public static void main(String[] args) throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {

        List<Log> logs=new ArrayList<Log>();

        for(int i=1;i<2;i++){

            Log log=new Log();

            log.setId(Long.parseLong(""+(i+6)));

            log.setUserId(Long.parseLong(""+i));

            log.setUserName("www"+i);

            logs.add(log);

        }

        writeXLSFile(logs,"E:/source/aa.xls");

    }