Excel模板导出工具类

Maven依赖

导入poi maven依赖
		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>

在这里插入代码片
package com.lipeng.fanshe.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelExportUtils {
	//parme1:任意的可以是获取的模板workbook parme2：对应的英文列
	//parme3:要填入的数据   parme4：要填入数据的对象字节码文件   
	//parme5:是否导出标题   parme5：从第几行开始写入excel数据  
    public static void createExcel(Workbook workbook, String columnTitleNameEn, List<?> list, Class<?> clazz, boolean isWriteTile, int rowIndex) throws InvocationTargetException, IllegalAccessException {
        Sheet sheetAt = workbook.getSheetAt(0);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        if (StringUtils.isNotEmpty(columnTitleNameEn)) {
            String[] splitTitleNameEn = columnTitleNameEn.split(",");
            //是否写入标题行
            if (isWriteTile) {
                Row row = sheetAt.createRow(rowIndex++);
                for (int i = 0; i < splitTitleNameEn.length; i++) {
                    ExcelExportUtils.setRowValue(row, i, splitTitleNameEn[i]);
                }
            }
            //写入数据行
            for (Object o : list) {
                int columnIndex = 0;
                Row row = sheetAt.createRow(rowIndex++);
                for (String s : splitTitleNameEn) {
                    for (Method declaredMethod : clazz.getDeclaredMethods()) {
                        if (("get" + s).equalsIgnoreCase(declaredMethod.getName())) {
                            //getId 执行该方法，得到 value =1
                            Object invoke = declaredMethod.invoke(o);
                            if (invoke == null) {
                                ExcelExportUtils.setRowValue(row, columnIndex++, null);
                            } else {
                                if (declaredMethod.getReturnType().isAssignableFrom(Date.class)) {
                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
                                    ExcelExportUtils.setRowValue(row, columnIndex++, simpleDateFormat.format(invoke));
                                } else {
                                    ExcelExportUtils.setRowValue(row, columnIndex++, invoke.toString());
                                }
                            }
                        }

                    }

                }

            }

        }

    }

    public static void setRowValue(Row row, int index, String value) {
        if (row.getCell(index) == null) {
            row.createCell(index).setCellValue(value);
        } else {
            row.getCell(index).setCellValue(value);
        }
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment:filename=" + URLEncoder.encode(
                    fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static List<List<String[]>> getLists(Workbook workbook) {
        List<List<String[]>> resultList = new ArrayList<>();
        if (workbook != null) {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                List<String[]> oneSheetData = new ArrayList<>();
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                //获取当前sheet 的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //获取当前sheet的总行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                //找出隐藏行
                int hidenRowIndex = getHidenRowIndex(sheet);
                //取隐藏行或第一行的列数作为表格 的最大列数
                int lastCellNum = sheet.getRow(hidenRowIndex < 0 ? 0 : hidenRowIndex).getPhysicalNumberOfCells();
                for (int rowNum = hidenRowIndex<0?firstRowNum:hidenRowIndex; rowNum<=lastRowNum; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row==null){
                        break;
                    }
                    int firstCellNum = row.getFirstCellNum();

                    String[] cells = new String[lastCellNum];
                    for (int cellNum= firstCellNum; cellNum <lastCellNum; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    oneSheetData.add(cells);
                }
                if (firstRowNum!=lastRowNum){
                    resultList.add(oneSheetData);
                }
            }

        }
        return resultList;
    }

    private static String getCellValue(Cell cell) {
        String cellValue="";
        if (cell ==null){
            return cellValue;
        }
        if (cell.getCellType() == CellType.NUMERIC){
            cell.setCellType(CellType.STRING);
        }
       switch (cell.getCellType()){
           case NUMERIC:
               cellValue=String.valueOf(cell.getNumericCellValue());
               break;
           case STRING:
               cellValue=String.valueOf(cell.getStringCellValue());
               break;
           case BOOLEAN:
               cellValue=String.valueOf(cell.getBooleanCellValue());
               break;
           case FORMULA:
               cellValue=String.valueOf(cell.getCellFormula());
               break;
           case BLANK:
               cellValue="";
               break;
           case ERROR:
               cellValue="非法字符串";
               break;
            default:
                cellValue="未知类型";
                break;
       }
        return cellValue;
    }

    public static int getHidenRowIndex(Sheet sheet) {
        int hidenRowIndex = -1;
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                if (row.getZeroHeight()){
                    hidenRowIndex=i;
                    break;
                }
            }
        }
        if (hidenRowIndex<0){
            try {
                throw  new Exception("模板格式不正确");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  hidenRowIndex;
    }
}