poi

Excel导出

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;


/**
 * @Describution:将数据库中的数据以Excel文件的方式导出到本地
 * @Author: Yang Yong
 * @Date: 2020-12-31 14:59
 * @Version: 1.0
 **/
public class ExportExcel<T> {

    /**
     * 生成Excel文件
     *
     * @param <T>
     * @param data
     * @param columns
     * @param sheetTitle
     * @throws IOException
     */
    public static <T> void generateExcelFile(List<T> data, List<String> columns, OutputStream stream, String sheetTitle) throws IOException {
        Workbook workbook = generateExcelFile(data, columns, sheetTitle);
        workbook.write(stream);
    }

    /**
     * 生成Excel文件
     *
     * @param <T>
     * @param data
     * @param columns
     * @return
     */
    public static <T> Workbook generateExcelFile(List<T> data, List<String> columns,String sheetTitle) {
        Workbook workbook = new HSSFWorkbook();
        generateExcelSheet(workbook,data,columns,sheetTitle);
        return workbook;
    }

    /**
     * 生成Excel文件的Sheet页面 （拆分自generateExcelFile，便于复用）
     * @param workbook     Excel文件
     * @param data         数据
     * @param columns      列集合
     * @param sheetTitle   sheet页标题
     */
    private static <T> void generateExcelSheet(Workbook workbook,List<T> data, List<String> columns, String sheetTitle){
        Sheet sheet = workbook.createSheet(sheetTitle);
        {
            //产生表格标题行
            Row headerRow = sheet.createRow(0);
            for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
                Cell cell = headerRow.createCell(columnIndex);
                HSSFRichTextString text = new HSSFRichTextString(columns.get(columnIndex));
                cell.setCellValue(text);
            }
        }
        //遍历产生数据行
        for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
            T dataItem = data.get(rowIndex);
            Row row = sheet.createRow(1 + rowIndex);


            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = dataItem.getClass().getDeclaredFields();

            try {
                //遍历产生单元格
                for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
                    //Cell cell = row.createCell(columnIndex);

                    Cell cell = row.createCell(columnIndex);
                    Field field = fields[columnIndex];
                    String fieldName = field.getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Class tCls = dataItem.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
                    Object value = getMethod.invoke(dataItem, new Object[] {});
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    // 其它数据类型都当作字符串简单处理
                    if(value != null && value != ""){
                        textValue = value.toString();
                    }
                    if (textValue != null) {
//                        XSSFRichTextString richString = new XSSFRichTextString(textValue);
                        cell.setCellValue(textValue);
                    }


    //                ColumnDef columnDef = columns.get(columnIndex);
    //                Object objValue = columnDef.toDisplayString(columnDef.getPropertyValue(dataItem, rowIndex, columnIndex));
    //                if (objValue == null) {
    //                    continue;
    //                }
    //                if (objValue instanceof Byte) {
    //                    cell.setCellValue(((Byte) objValue).doubleValue());
    //                } else if (objValue instanceof Short) {
    //                    cell.setCellValue(((Short) objValue).doubleValue());
    //                } else if (objValue instanceof Integer) {
    //                    cell.setCellValue(((Integer) objValue).doubleValue());
    //                } else if (objValue instanceof Long) {
    //                    cell.setCellValue(((Long) objValue).doubleValue());
    //                } else if (objValue instanceof Float) {
    //                    cell.setCellValue(((Float) objValue).doubleValue());
    //                } else if (objValue instanceof Character) {
    //                    cell.setCellValue(objValue.toString());
    //                } else {
    //                        cell.setCellValue(objValue.toString());
    //                }
                }
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
    }
}

测试

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        //List<T> data, List<String> columns, OutputStream stream, String sheetTitle

        //封装表头数据
        List<String> columns = new ArrayList<String>();
        columns.add("id");
        columns.add("name");
        columns.add("age");
        columns.add("birthday");
 //       columns.add("other");

        //创建表数据集合
        List<Object> dateList = new ArrayList<Object>();

        //封装表数据,20条
        for (int i = 0; i < 20; i++) {
            Student stu = new Student();

            stu.setId(i);
            stu.setName(i+":name");
            stu.setAge(i);
            stu.setBirthday(new Date());
 //           stu.setOther(new Te("chi","sleep"));

//            List<Object> rowList = new ArrayList<Object>();
//            for (int columnsIndex = 0; columnsIndex < columns.size(); columnsIndex++) {
//                String s = columns.get(columnsIndex);
//                rowList.add(i+s);
//            }
            dateList.add(stu);
        }

        File file = new File("G:\\demo\\demo03.xlsx");
        try {
            OutputStream stream = new FileOutputStream(file);

            ExportExcel.generateExcelFile(dateList,columns,stream,"demo001");
            stream.close();
            System.out.println("ok");
        } catch (Exception e) {
            e.printStackTrace();
        }




    }
}