2020-08-11

现在pom.xml引入

<!-- poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
</dependency>

 

 

import com.sgcloud.annotation.Export2Excel;
import com.sgcloud.util.page.PageBean;
import com.sgcloud.util.page.Query;
import com.xiaoleilu.hutool.date.DatePattern;
import com.xiaoleilu.hutool.date.DateUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * excel导入导出公告对象
 *
 * @autho
 */
public class PoiExcelHelper {

    private Logger logger = LoggerFactory.getLogger(PoiExcelHelper.class);

    private Query query;

    private IPage iPage;

    private List<String> exportFieldList;

    private Integer maxPageNum = Integer.MAX_VALUE;

    public interface IPage<T> {
        PageBean<T> listPage(Query queryObj);
    }

    private PoiExcelHelper() {
    }

    public PoiExcelHelper(IPage iPage, Query query) {
        this.iPage = iPage;
        this.query = query;
    }

    public PoiExcelHelper(IPage iPage, Query query, Integer maxPageNum) {
        this.iPage = iPage;
        this.query = query;
        this.maxPageNum = maxPageNum;
    }

    public PoiExcelHelper(IPage iPage, Query query, List<String> exportFieldList) {
        this.iPage = iPage;
        this.query = query;
        this.exportFieldList = exportFieldList;
    }

    public List<List<Object>> readExcel(InputStream is) throws IOException {
        List<List<Object>> results = new ArrayList<>();
        Workbook xssfWorkbook = new XSSFWorkbook(is);
        try {
            Sheet sheet = xssfWorkbook.getSheetAt(0); // 获取文件的第一个sheet
            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    continue;
                }
                List<Object> rows = new ArrayList<>();
                for (Cell cell : row) {
                    CellType cellType = cell.getCellTypeEnum();
                    switch (cellType) {
                        case STRING:
                            rows.add(cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            rows.add(cell.getNumericCellValue());
                            break;
                        default:
                            break;
                    }
                }
                results.add(rows);
            }
        } finally {
            logger.info("读取excel完毕!");
            is.close();
            xssfWorkbook.close();
        }
        return results;
    }

    /**
     * 导出Excel
     * @param sheetName
     * @param os
     * @throws Exception
     */
    public void writeExcel(String sheetName, OutputStream os) throws IOException {
        String filePath = this.writeExcel(sheetName);
        File file = new File(filePath);
        if (file.exists()) {
            BufferedInputStream bis = null;
            // 读取filename
            FileInputStream fis = new FileInputStream(file);
            bis = new BufferedInputStream(fis);
            int b = 0;
            byte[] buffer = new byte[1024];
            while ((b = bis.read(buffer)) != -1) {
                os.write(buffer, 0, b);
            }
        }
    }

    public String writeExcel(String sheetName) throws IOException {
        if (iPage != null) {
            // 写到服务器上
            String dirPath = System.getProperty("user.dir");
//          ApplicationHome home = new ApplicationHome(getClass());
//          File jarFile = home.getSource();
            File tmpDic = new File(dirPath + "/tmp/");
            if (!tmpDic.exists()) {
                tmpDic.mkdir();
            }
            String path = tmpDic.getPath() + "/" + java.util.UUID.randomUUID() + ".xlsx";
            File file = new File(path);
            OutputStream os = new FileOutputStream(file);
            // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
            Workbook wb = new XSSFWorkbook();
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            Sheet sheet = wb.createSheet(sheetName);
            sheet.autoSizeColumn(0);//列宽度自适应
            List<List<String>> rowsData = new ArrayList<>();
            try {
                PageBean pageBean = iPage.listPage(this.query);
                List items = pageBean != null ? pageBean.getItems() : null;
                if(items != null) {
                    this.loopWrite(items, wb, sheet, os, 0, rowsData);
                    long totalPage = maxPageNum > pageBean.getTotalPage() ? pageBean.getTotalPage() : maxPageNum;
                    for (int i = pageBean.getCurrentPage() + 1; i <= totalPage; i++) {
                        this.query.setPageNum(i);
                        pageBean = iPage.listPage(this.query);
                        items = pageBean.getItems();
                        if(items != null) {
                            os = new FileOutputStream(file);
                            this.loopWrite(items, wb, sheet, os, (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1, rowsData);
                        }
                    }
                }
                return file.getAbsolutePath();
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            } finally {
                wb.close();
            }
        }
        return null;
    }

    private void loopWrite(List items, Workbook wb, Sheet sheet, OutputStream os, int startRow, List<List<String>> rowsData) throws NoSuchMethodException,
            IllegalAccessException, InvocationTargetException, IOException {
        if (startRow == 0) {
            List<String> headers = new ArrayList<String>();
            if (items.size() > 0) {
                Object obj = items.get(0);
                Field[] fields = obj.getClass().getDeclaredFields();
                for (Field field : fields) {
                    Export2Excel export2Excel = field.getAnnotation(Export2Excel.class);
                    if(export2Excel != null && (CollectionUtils.isEmpty(this.exportFieldList) || this.exportFieldList.contains(field.getName()))){
                        headers.add(export2Excel.name());
                    }
                }
            }
            if(CollectionUtils.isEmpty(headers)){
                throw new RuntimeException("没有导出的列！");
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            Row row = sheet.createRow(startRow);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            CellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            Font font = wb.createFont();
            font.setBold(true);
            style.setFont(font);
            Cell cell = null;
            // 创建标题
            for (int i = 0; i < headers.size(); i++) {
                cell = row.createCell(i);
                cell.setCellValue(headers.get(i));
                cell.setCellStyle(style);
                sheet.setColumnWidth(i, headers.get(i).length() * 600);
            }
        }

//        List<List<String>> rowsData = new ArrayList<>();
        for (Object obj : items) {
            Class cls = obj.getClass();
            Field[] fields = cls.getDeclaredFields();
            List columnsData = new ArrayList();
            for (Field field : fields) {
                Export2Excel export2Excel = field.getAnnotation(Export2Excel.class);
                if (export2Excel != null && (CollectionUtils.isEmpty(this.exportFieldList) || this.exportFieldList.contains(field.getName()))) {
                    String fieldName = field.getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
                            + fieldName.substring(1);
                    Method getMethod = cls.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(obj, new Object[]{});
                    String textValue = "";
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        textValue = intValue + "";
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        textValue = fValue + "";
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        textValue = dValue + "";
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        textValue = longValue + "";
                    } else if (value instanceof Date) {
                        textValue = DateUtil.format((Date) value, DatePattern.NORM_DATETIME_PATTERN);
                    } else if (value != null) {
                        textValue = value.toString();
                    }
                    Class enumClas = export2Excel.enumCls();
                    if (enumClas.isEnum()) {
                        Object[] objects = enumClas.getEnumConstants();
                        Method getCode = enumClas.getMethod("getCode");
                        Method getName = enumClas.getMethod("getValue");
                        for (Object enumObj : objects) {
                            String code = getCode.invoke(enumObj).toString();
                            if (code.equals(textValue)) {
                                textValue = getName.invoke(enumObj).toString();
                                break;
                            }
                        }
                    }
                    columnsData.add(textValue);
                }
            }
            rowsData.add(columnsData);
        }

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
//       Row row = sheet.createRow(startRow);
        // 第四步，创建单元格，并设置值表头 设置表头居中
//       CellStyle style = wb.createCellStyle();
//       style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
//       Font font = wb.createFont();
//       font.setBold(true);
//       style.setFont(font);
//       Cell cell = null;
        // 创建内容
        for (int i = 0; i < rowsData.size(); i++) {
            int index = i + (startRow == 0 ? startRow + 1 : startRow);
            Row row = sheet.createRow(index);
            for (int j = 0; j < rowsData.get(i).size(); j++) {
                // 将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(rowsData.get(i).get(j));
            }
        }
        rowsData.clear();
        wb.write(os);
        os.flush();
        os.close();
    }

    public static void main(String[] args) {
        PoiExcelHelper helper = new PoiExcelHelper();
        File file = new File("/Users/tangxingchu/guowang/test.xlsx");
        if (file.exists()) {
            try {
//                List<List<Object>> results = helper.readExcel(new FileInputStream(file));
                FileInputStream is = new FileInputStream(file);
                Workbook xssfWorkbook = new XSSFWorkbook(is);
                try {
                    Sheet sheet = xssfWorkbook.getSheetAt(0); // 获取文件的第一个sheet
                    for (Row row : sheet) {
                        if (row.getRowNum() == 0) {
                            continue;
                        }
                        List<Object> rows = new ArrayList<>();
                        for (Cell cell : row) {
                            CellType cellType = cell.getCellTypeEnum();
                            switch (cellType) {
                                case STRING:
                                    rows.add(cell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    rows.add(cell.getNumericCellValue());
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                } finally {
                    is.close();
                    xssfWorkbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        String[] titles = new String[]{"title1", "title2", "title3"};
        String[] row = new String[]{"row1-c1", "row1-c2", "row1-c3"};
        String[] row2 = new String[]{"row2-c1", "row2-c2", "row2-c3"};
        String[][] values = new String[][]{row, row2};
//    try {
         OutputStream os = new FileOutputStream(new File("/Users/tangxingchu/guowang/test2.xlsx"));
         helper.writeExcel("/Users/tangxingchu/guowang", "aaa", titles, values);
         os.close();
//    } catch (IOException e) {
//       e.printStackTrace();
//    }

    }

    public String writeReviewExcel(String sheetName) throws IOException {
        if (iPage != null) {
            // 写到服务器上
            String dirPath = System.getProperty("user.dir");
//          ApplicationHome home = new ApplicationHome(getClass());
//          File jarFile = home.getSource();
            File tmpDic = new File(dirPath + "/tmp/");
            if (!tmpDic.exists()) {
                tmpDic.mkdir();
            }
            String path = tmpDic.getPath() + "/" + java.util.UUID.randomUUID() + ".xlsx";
            File file = new File(path);
            OutputStream os = new FileOutputStream(file);
            // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
            Workbook wb = new XSSFWorkbook();
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            Sheet sheet = wb.createSheet(sheetName);
            sheet.autoSizeColumn(0);//列宽度自适应
            List<List<String>> rowsData = new ArrayList<>();
            try {
                PageBean pageBean = iPage.listPage(this.query);
                List items = pageBean != null ? pageBean.getItems() : null;
                if(items != null) {
                    this.loopWrite(items, wb, sheet, os, 0, rowsData);
                    long totalPage = maxPageNum > pageBean.getTotalPage() ? pageBean.getTotalPage() : maxPageNum;
                    for (int i = pageBean.getCurrentPage() + 1; i <= totalPage; i++) {
                        this.query.setPageNum(i);
                        pageBean = iPage.listPage(this.query);
                        items = pageBean.getItems();
                        if(items != null) {
                            os = new FileOutputStream(file);
                            this.loopWrite(items, wb, sheet, os, (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1, rowsData);
                        }
                    }
                }
                return file.getAbsolutePath();
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            } finally {
                wb.close();
            }
        }
        return null;

    }

}

 

 

调用：

try {
   exportFieldList=

Lists.newArrayList("班级", "姓名", "成绩");

    // 设置默认查询条件
                                                    分页方法  参数对象   列名
    PoiExcelHelper excelHelper = new PoiExcelHelper(this,   query, exportFieldList);
    String filePath = excelHelper.writeExcel("Sheet 1");
    File file = new File(filePath);
    if (file.exists()) {
        BufferedInputStream bis = null;
        // 读取filename
        FileInputStream fis = new FileInputStream(file);
        bis = new BufferedInputStream(fis);
        int b = 0;
        byte[] buffer = new byte[1024];
        while ((b = bis.read(buffer)) != -1) {
            os.write(buffer, 0, b);
        }
        os.flush();
    }
} finally {
    os.close();
}