SpringBoot集成poi导入Excel，导出Excel

近期工作需要导入功能，就是将Excel表格中的数据读取出来再添加到数据库中，所以整理了这篇笔记。

1.所需jar

     <properties>
        <java.version>1.8</java.version>
        <poi.version>4.1.2</poi.version>
    </properties>

        <!-- poi start -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <!-- poi end -->

2.Excel导入工具类

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 导入Excel表格工具类
 */
public class ImportExcelUtil {

    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    /**
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象
     * @param in,fileName
     * @return
     * @throws IOException
     */
    public static List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
        List<List<Object>> list = null;
        //创建Excel工作薄
        Workbook work = getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}
            //遍历当前sheet中的所有行
            Row firstRow = sheet.getRow(0);
            if(firstRow==null){ continue; }
            int titleLastCellNum = sheet.getRow(0).getLastCellNum();
            int titleFirstCellNum = sheet.getRow(0).getFirstCellNum();
            //System.out.println("titleLastCellNum"+titleLastCellNum+"====titleFirstCellNum"+titleFirstCellNum);
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null||row.getFirstCellNum()==j){continue;}
                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = titleFirstCellNum; y < titleLastCellNum; y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    private static Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(excel2003L.equals(fileType)){
            wb = new HSSFWorkbook(inStr);  //2003-
        }else if(excel2007U.equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     * @param cell
     * @return
     */
    private static Object getCellValue(Cell cell){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
        if(cell==null){return null;}
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if("General".equals(cell.getCellStyle().getDataFormatString())){
                    cell.setCellType(CellType.forInt(1));
                    value = cell.getStringCellValue();
                }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
                    value = sdf.format(cell.getDateCellValue());
                }else{
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = "";
                break;
            default:
                value = "-";
                break;
        }
        return value;
    }

}

3.Excel导出工具类

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFont;

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

/**
 * Exexl导出工具类
 */
@Slf4j
public class ExportExcelUtil {

    //每个sheet最多导出多少条数据
    public static final int SHEET_COUNT = 30000;

    /**
     * SXSSFWorkbook 设置表头字体加粗 和 大小
     */
    private static CellStyle setTitleNameFont(SXSSFWorkbook workbook, CellStyle style, String fontName, short size){
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setFontHeightInPoints(size);
        font.setFontName(fontName);
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    /**
     * 设置字体并加外边框
     *
     * @param style 样式
     * @param style 字体名
     * @param style 大小
     * @return
     */
    public static CellStyle setFontAndBorder(HSSFWorkbook workbook, CellStyle style, String fontName, short size) {
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(size);
        font.setFontName(fontName);
        font.setBold(true);
        style.setFont(font);
        /*style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框*/
        return style;
    }

    /**
     * xlsx方式(适合导出大量数据，拆分多个sheet)
     * @param response 响应流
     * @param fileName 文件名称
     * @param titleColumn 标题列名称对象（如：name）
     * @param titleName 标题列名称对象描述（如：张三）
     * @param titleSize 标题大小
     * @param dataList  数据源
     */
    public static void writeBigExcel(HttpServletResponse response, String fileName, String titleColumn[],
                                     String titleName[], int titleSize[], List<?> dataList) {
        OutputStream out = null;
        try {
            SXSSFWorkbook workbook = new SXSSFWorkbook(100);
            out = response.getOutputStream();
            String lastFileName = fileName + ".xlsx";
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + URLEncoder.encode(lastFileName, "UTF-8"));
            int k = 0;
            int rowIndex;
            Sheet sheet = workbook.createSheet(fileName + (k + 1));
            //写入excel的表头
            Row titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
            //设置样式
            CellStyle titleStyle =  workbook.createCellStyle();
            titleStyle =  setTitleNameFont(workbook, titleStyle, "Arial Unicode MS", (short) 12);
            for (int i = 0; i < titleName.length; i++) {
                sheet.setColumnWidth(i, titleSize[i] * 256);    //设置宽度
                Cell cell = titleNameRow.createCell(i);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleName[i]);
            }
            //写入到excel中
            if (dataList != null && dataList.size() > 0) {
                if (titleColumn.length > 0) {
                    for (int index = 0; index < dataList.size(); index++) {
                        //每个sheet3W条数据
                        if (index != 0 && (index) % SHEET_COUNT == 0) {
                            k = k + 1;
                            sheet = workbook.createSheet(fileName + (k + 1));
                            //写入excel的表头
                            titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
                            for (int i = 0; i < titleName.length; i++) {
                                sheet.setColumnWidth(i, titleSize[i] * 256);    //设置宽度
                                Cell cell = titleNameRow.createCell(i);
                                cell.setCellStyle(titleStyle);
                                cell.setCellValue(titleName[i]);
                            }
                        }
                        if (index < SHEET_COUNT) {
                            rowIndex = index + 1;
                        } else {
                            rowIndex = index - SHEET_COUNT * ((index) / SHEET_COUNT) + 1;
                        }
                        Object obj = dataList.get(index);
                        Class clazz = obj.getClass();
                        Row dataRow = workbook.getSheet(fileName + (k + 1)).createRow(rowIndex);
                        for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
                            String title = titleColumn[columnIndex].trim();
                            if (!"".equals(title)) {
                                // 获取返回类型
                                String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length()); // 使其首字母大写;
                                String methodName = "get" + UTitle;
                                Method method = clazz.getDeclaredMethod(methodName);
                                String returnType = method.getReturnType().getName();
                                Object object = method.invoke(obj);
                                String data = method.invoke(obj) == null ? "" : object.toString();
                                Cell cell = dataRow.createCell(columnIndex);
                                if (data != null && !"".equals(data)) {
                                    if ("int".equals(returnType)) {
                                        cell.setCellValue(Integer.parseInt(data));
                                    } else if ("long".equals(returnType)) {
                                        cell.setCellValue(Long.parseLong(data));
                                    } else if (Date.class.getName().equals(returnType)) {
                                        cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
                                    } else {
                                        cell.setCellValue(data);
                                    }
                                    /* else if ("float".equals(returnType)) {
                                        cell.setCellValue(new DecimalFormat("0.00").format(Float.parseFloat(data)));
                                    } else if ("double".equals(returnType)) {
                                        cell.setCellValue(new DecimalFormat("0.00").format(Double.parseDouble(data)));
                                    }*/
                                }
                            }
                        }
                    }
                }
            }
            workbook.write(out);
            //out.flush();
            //out.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    log.debug("导出写Excel异常");
                }
            }
        }
    }

    /**
     * 写excel.
     * xls方式
     * @param response 响应流
     * @param fileName 文件名称
     * @param titleColumn 对应bean的属性名
     * @param titleName   excel要导出的列名
     * @param titleSize   列宽
     * @param dataList    数据
     */
    public static void writeExcel(HttpServletResponse response, String fileName, String titleColumn[], String titleName[], int titleSize[], List<?> dataList) {
        //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
        HSSFWorkbook workbook = new HSSFWorkbook();

        OutputStream out = null;
        try{
            //新建文件
            out = response.getOutputStream();
            String lastFileName = fileName + ".xls";
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + URLEncoder.encode(lastFileName, "UTF-8"));
            int k = 0;
            int rowIndex;
            Sheet sheet = workbook.createSheet(fileName + (k + 1));
            //写入excel的表头
            Row titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
            //设置样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle = setFontAndBorder(workbook, titleStyle, "宋体", (short) 12);
            for (int i = 0; i < titleName.length; i++) {
                sheet.setColumnWidth(i, titleSize[i] * 256);    //设置宽度
                Cell cell = titleNameRow.createCell(i);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleName[i]);
            }

            //通过反射获取数据并写入到excel中
            if (dataList != null && dataList.size() > 0) {
                if (titleColumn.length > 0) {
                    for (int index = 0; index < dataList.size(); index++) {
                        //设置多个sheet
                        //每个sheet3W条数据
                        if (index != 0 && (index) % SHEET_COUNT == 0) {
                            k = k + 1;
                            sheet = workbook.createSheet(fileName + (k + 1));
                            //写入excel的表头
                            titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
                            for (int i = 0; i < titleName.length; i++) {
                                sheet.setColumnWidth(i, titleSize[i] * 256);    //设置宽度
                                Cell cell = titleNameRow.createCell(i);
                                cell.setCellStyle(titleStyle);
                                cell.setCellValue(titleName[i]);
                            }
                        }
                        if (index < SHEET_COUNT) {
                            rowIndex = index + 1;
                        } else {
                            rowIndex = index - SHEET_COUNT * ((index) / SHEET_COUNT) + 1;
                        }

                        Object obj = dataList.get(index);     //获得该对象
                        Class clsss = obj.getClass();     //获得该对对象的class实例
                        Row dataRow = workbook.getSheet(fileName + (k + 1)).createRow(rowIndex);
                        for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
                            String title = titleColumn[columnIndex].toString().trim();
                            if (!"".equals(title)) {  //字段不为空
                                //使首字母大写
                                String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length()); // 使其首字母大写;
                                String methodName = "get" + UTitle;

                                // 设置要执行的方法
                                Method method = clsss.getDeclaredMethod(methodName);

                                //获取返回类型
                                String returnType = method.getReturnType().getName();
                                Object object = method.invoke(obj);
                                String data = method.invoke(obj) == null ? "" : object.toString();
                                Cell cell = dataRow.createCell(columnIndex);
                                if (data != null && !"".equals(data)) {
                                    if ("int".equals(returnType)) {
                                        cell.setCellValue(Integer.parseInt(data));
                                    } else if (Date.class.getName().equals(returnType)) {
                                        cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
                                    } else {
                                        cell.setCellValue(data);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    log.debug("导出写Excel异常");
                }
            }
        }
    }

    /**
     * 写excel.
     * xls方式
     * @param response 响应流
     * @param fileName 文件名称
     * @param titleColumn 对应bean的属性名
     * @param titleName   excel要导出的列名
     * @param titleSize   列宽
     * @param dataList    数据
     */
    public static void writeExcel1(HttpServletResponse response, String fileName, String titleColumn[], String titleName[], int titleSize[], List<?> dataList, String headValue) {
        //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
        HSSFWorkbook workbook = new HSSFWorkbook();

        OutputStream out = null;
        try{
            //新建文件
            out = response.getOutputStream();
            String lastFileName = fileName + ".xls";
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + URLEncoder.encode(lastFileName, "UTF-8"));
            int k = 0;
            int rowIndex;
            Sheet sheet = workbook.createSheet(fileName + (k + 1));

            //第一行表头
            HSSFRow headValueRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
            //设置样式
            CellStyle headValueRowStyle = workbook.createCellStyle();
            headValueRowStyle = setFontAndBorder(workbook, headValueRowStyle, "宋体", (short) 12);
            headValueRowStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            headValueRowStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            if(headValue!=null){
                // 第一行表头标题
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleColumn.length-1));
                HSSFRow row = (HSSFRow) sheet.createRow(0);
                row.setHeight((short) 0x349);
                Cell cell = headValueRow.createCell(0);
                cell.setCellStyle(headValueRowStyle);
                cell.setCellValue(headValue);
            }

            //写入第二行excel的表头
            HSSFRow titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(1);
            //设置样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle = setFontAndBorder(workbook, titleStyle, "宋体", (short) 12);
            for (int i = 0; i < titleName.length; i++) {
                sheet.setColumnWidth(i, titleSize[i] * 256);    //设置宽度
                Cell cell = titleNameRow.createCell(i);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleName[i]);
            }


            //通过反射获取数据并写入到excel中
            if (dataList != null && dataList.size() > 0) {
                if (titleColumn.length > 0) {
                    for (int index = 0; index < dataList.size(); index++) {
                        //设置多个sheet
                        //每个sheet3W条数据
                        if (index != 0 && (index) % SHEET_COUNT == 0) {
                            k = k + 1;
                            sheet = workbook.createSheet(fileName + (k + 1));
                            //写入excel的表头
                            titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
                            for (int i = 0; i < titleName.length; i++) {
                                sheet.setColumnWidth(i, titleSize[i] * 256);    //设置宽度
                                Cell cell = titleNameRow.createCell(i);
                                cell.setCellStyle(titleStyle);
                                cell.setCellValue(titleName[i]);
                            }
                        }
                        if (index < SHEET_COUNT) {
                            rowIndex = index + 2;
                        } else {
                            rowIndex = index - SHEET_COUNT * ((index) / SHEET_COUNT) + 2;
                        }

                        Object obj = dataList.get(index);     //获得该对象
                        Class clsss = obj.getClass();     //获得该对对象的class实例
                        Row dataRow = workbook.getSheet(fileName + (k + 1)).createRow(rowIndex);
                        for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
                            String title = titleColumn[columnIndex].toString().trim();
                            if (!"".equals(title)) {  //字段不为空
                                //使首字母大写
                                String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length()); // 使其首字母大写;
                                String methodName = "get" + UTitle;

                                // 设置要执行的方法
                                Method method = clsss.getDeclaredMethod(methodName);

                                //获取返回类型
                                String returnType = method.getReturnType().getName();
                                Object object = method.invoke(obj);
                                String data = method.invoke(obj) == null ? "" : object.toString();
                                Cell cell = dataRow.createCell(columnIndex);
                                if (data != null && !"".equals(data)) {
                                    if ("int".equals(returnType)) {
                                        cell.setCellValue(Integer.parseInt(data));
                                    } else if (Date.class.getName().equals(returnType)) {
                                        cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
                                    } else {
                                        cell.setCellValue(data);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    log.debug("导出写Excel异常");
                }
            }
        }
    }

    /*
    public static void main(String[] args) {
        //导出数组
        DateFormat df1 = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = "AIS" + df1.format(new Date());
        String titleColumn[] = new String[]{"mmsi", "name", "lon", "lat", "sog", "cog", "createTime"};
        String titleName[] = new String[]{"编号（MMSI）", "目标名称", "经度", "纬度", "航速（节）", "航向（度）", "创建时间"};
        int titleSize[] = new int[]{15, 25, 20, 20, 15, 15, 20};
        //执行导出
        //ExceExportlUtil.writeBigExcel(response,fileName,titleColumn,titleName,titleSize,exportList);
        //ExceExportlUtil.writeExcel(response, fileName, titleColumn, titleName, titleSize, exportList);
    }
    */

}

3.接口的调用

    @ApiOperation(value = "企业导入接口", notes = "企业导入接口")
    @PostMapping("/import")
    public GlobalResult importEntBaseInfo(MultipartFile file) {
        InputStream in = null;
        List<List<Object>> baseInfoList = null;
        try {
            if (file.isEmpty()) {
                return ResultUtil.fail("文件不存在！");
            }
            in = file.getInputStream();
            baseInfoList = ImportExcelUtil.getBankListByExcel(in, file.getOriginalFilename());
            in.close();
            if (!CollectionUtil.isEmpty(baseInfoList)) {
                String baseInfoScjyssqy = "210224";
                //调用service相应方法进行数据保存到数据库中，
                for (int i = 0; i < baseInfoList.size(); i++) {
                    List<Object> baseInfo = baseInfoList.get(i);
                    System.out.println("打印信息-->" + baseInfoList.get(i).toString());
                    //根据业务进行数据库添加
            }
            return ResultUtil.success();
        } catch (Exception e) {
            e.printStackTrace();
            return ResultUtil.fail("企业信息录入失败");
        }
    }


    @ApiOperation(value = "企业导出接口", notes = "企业导出接口")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "keyword", value = "keyword", paramType = "query", dataType = "String")})
    @GetMapping("/export")
    public void exportEntBaseInfo(@RequestParam(value = "keyword", required = false) String keyword,
                                  HttpServletResponse response) {

        //根据自己业务需求查询出数据
        List<ExportBaseInfoResp> exportList = null;

        //导出数组
        DateFormat df1 = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = "企业列表" + df1.format(new Date());
        String titleColumn[] = new String[]{"baseInfoQymc", "baseInfoQyzcdz", "baseInfoFddbrXm", "baseInfoFddbrYddh"};
        String titleName[] = new String[]{"企业名称", "地址", "联系人", "来年息电话"};
        int titleSize[] = new int[]{30, 30, 15, 15};
        //执行导出
        ExportExcelUtil.writeExcel(response, fileName, titleColumn, titleName, titleSize, exportList);
    }