Java实现Excel导入、Excel导出

这篇文章主要介绍了Java中的Excel导入、Excel导出功能的实现，在开发中，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下

---

1 :MySql数据库：user 表结构执行创建语句

CREATE TABLE `user` (
  `user_id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '用户唯一标识自增主键ID',
  `user_name` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '用户名称',
  `phone_num` varchar(11) COLLATE utf8_bin DEFAULT NULL COMMENT '用户手机号',
  `create_time` datetime DEFAULT NULL COMMENT '用户创建时间',
  `remark` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '备用字段',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2 :添加POM依赖：

代码如下依赖（示例）：

        <!-- apache_poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
        <!--HttpClient 4.3.6 使用MultipartEntityBuilder实现类似form表单提交方式的文件上传-->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.5</version>
        </dependency>

3 :User实体类

代码如下（示例）：

import lombok.Data;

import java.util.Date;
@Data
public class User {

    /** 用户唯一标识自增主键ID */
    private String userId;

    /** 用户名称 */
    private String userName;

    /** 用户手机号 */
    private String phoneNum;

    /** 用户创建时间 */
   // @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private Date createTime;

    /** 备用字段 */
    private String remark;

}

4 :Excel导入、Excel导出案例代码

4.1：Controller Excel导入、Excel导出

代码如下(所有的业务我在Controller 处理的、为了方便大家更直观的阅读代码示例)：

import com.wtl.springcloud.entity.User;
import com.wtl.springcloud.mapper.UserMapper;
import com.wtl.springcloud.util.ExportExcelUtils;
import com.wtl.springcloud.util.ImportExcelUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping("/file")
public class FileController {
    //Excel 标题
    private final static String[] titleMap = {"姓名", "手机号码", "备注"};
    //实体类字段信息
    private final static String[] columns = {"userName", "phoneNum", "remark"};

    @Autowired
    private UserMapper userMapper;


    /**
     * @param file 上传文件对象
     * @return java.lang.String
     * @description TODO Excel导入
     * @author WangTianLiang
     * @date 2021/6/19
     */
    @PostMapping("/excelImport")
    public String excelImport(MultipartFile file) {
        String fileNameUp = file.getOriginalFilename();
        //获取文件后缀
        String fileSuffix = fileNameUp.substring(fileNameUp.lastIndexOf(".") + 1);
        //step 1: 校验文件格式
        if ((!"xls".equalsIgnoreCase(fileSuffix)) && (!"xlsx".equalsIgnoreCase(fileSuffix))) {
            return "上传文件格式错误";
        }
        //step 2: 解析上传文档
        List<Map<String, Object>> list = ImportExcelUtils.excelImport(file, columns);
        //非空校验
        if (list == null || list.size() <= 0) {
            return "表格无数据";
        }
        Map<String, String> nameAndElements = new HashMap<String, String>();//以 name 为 key 存储合并后的数据
        //step 3: 循环遍历判断上传解析后的数据信息
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> temp = list.get(i);
            String userId = (UUID.randomUUID().toString()).replace("-", "");
            //姓名
            String userName = (String) temp.get("userName");
            if ("--".equals(userName) || userName.trim().length() <= 0) {
                return "第" + (i + 1) + "行数据:姓名不可以为空";
            }
            //手机号
            String phoneNum = (String) temp.get("phoneNum");
            if ("--".equals(phoneNum) || phoneNum.trim().length() <= 0) {
                return "第" + (i + 1) + "行数据:手机号码不可以为空";
            }
            if (phoneNum.trim().length() < 11 || phoneNum.trim().length() > 11) {
                return "第" + (i + 1) + "行数据:请输入11位的手机号";
            }
            //判断上传文档中的数据是否重复（判断key是否存在）
            if (nameAndElements.containsKey(phoneNum)) {
                return "在上传的模板数据中，第" + (i + 1) + "行数据:" + phoneNum + "该手机号码重复";
            } else {
                // 新记录
                nameAndElements.put(phoneNum, phoneNum);
            }
            //备注
            String remark = (String) temp.get("remark");
            if ("--".equals(remark)) {
                temp.remove("remark");
            }
            //step 4: 封装数据信息
            temp.put("userId", userId);
            temp.put("createTime", new Date());
        }
        //step 5: 批量添加用户信息
        Integer result = userMapper.insertAll(list);
        if (result == list.size()) {
            return "上传成功";
        }
        return "上传失败";
    }


    /**
     * @param response
     * @param userIdList 用户ID集合
     * @return void
     * @description TODO Excel导出
     * @author WangTianLiang
     * @date 2021/6/19
     */
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response, @RequestBody List<String> userIdList) {
        //step 1: 参数信息校验
        if (userIdList == null || userIdList.size() <= 0) {
            return;
        }
        //step 2: 生成文件名称
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String sheetName = sdf.format(new Date());
        //step 3: 批量查询用户信息
        List<User> userList = userMapper.foreachByIds(userIdList);
        //step 4: 用户信息数据校验
        if (userList == null || userIdList.size() <= 0) {
            return;
        }
        //step 5: Excel导出
        ExportExcelUtils.excelExport(userList, titleMap, columns, sheetName, response);
    }
}

4.2：Mapper

代码如下（示例）：

import com.wtl.springcloud.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {

    //批量添加用户信息
    Integer insertAll(@Param("list") List<Map<String, Object>> list);

    //批量查询用户信息
    List<User> foreachByIds(@Param("userIdList") List<String> userIdList);
}

4.3：Mapper.xml

代码如下（示例）：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.wtl.springcloud.mapper.UserMapper">

    <!--根据用户ID批量查询-->
    <select id="foreachByIds" resultType="com.wtl.springcloud.entity.User" parameterType="java.lang.String">
        select
        user_id userId,user_name userName,phone_num phoneNum,create_time createTime,REMARK remark
        from
        user
        where
        <foreach collection="userIdList" item="userId" index="index" open=" USER_ID IN (" close=")" separator=",">
            #{userId,jdbcType=VARCHAR}
        </foreach>
        order by
        create_time DESC
    </select>

    <!--批量添加用户信息-->
    <insert id="insertAll" parameterType="java.util.List">
        INSERT into user
        (user_id,user_name,phone_num,create_time,remark)
        VALUES
        <foreach item="item" collection="list" index="index" separator=",">
            (#{item.userId,jdbcType=VARCHAR},
            #{item.userName,jdbcType=VARCHAR},
            #{item.phoneNum,jdbcType=VARCHAR},
            #{item.createTime,jdbcType=TIMESTAMP},
            #{item.remark,jdbcType=VARCHAR})
        </foreach>
    </insert>

</mapper>

4.4：Excel导入、Excel导出 工具类

4.4.1：Excel导入工具类ImportExcelUtils

代码如下（示例）：

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import org.springframework.mock.web.MockMultipartFile;
import org.apache.http.entity.ContentType;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/***
 * 导入excel数据到List<Object>
 */
public class ImportExcelUtils {

    /**
    * @description TODO Excel导入
    * @param file 文件对象
    * @param columns
    * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
    * @author WangTianLiang
    * @date 2021/6/19
    */
    public static List<Map<String,Object>> excelImport(MultipartFile file, String[] columns){
        Workbook wb = readExcel(file);
        Sheet sheet = null;
        Row row = null;
        List<Map<String,Object>> list = null;
        String cellData = null;
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<Map<String,Object>>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            if (rownum <= 1){
                //表格无数据
                return null;
            }
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //日期
            SimpleDateFormat form = new SimpleDateFormat("yyyyMMdd");
            String nowTime = form.format(new Date());
            for (int i = 1; i<rownum; i++) {
                Map<String,Object> map = new LinkedHashMap<String,Object>();
                row = sheet.getRow(i);
                if(row !=null){
                    for (int j=0;j<colnum;j++){
                        cellData = (String) getCellFormatValue(row.getCell(j));
                        if (cellData == null && cellData.trim().length() <= 0){
                            cellData = "--";
                    // String cellDataResult = dataVerification(j);
                    // if (StringUtils.isNotEmpty(cellDataResult)){
                    //     return ServerResponse.createByErrorMessage(cellDataResult);
                    // }
                        }
                        map.put(columns[j], cellData);
                    }
                }else{
                    break;
                }
                list.add(map);
            }
        }
        return list;
    }

    /** 
    * @description TODO 读取excel
    * @param file 
    * @return org.apache.poi.ss.usermodel.Workbook
    * @author WangTianLiang 
    * @date 2021/6/20 
    */
    public static Workbook readExcel(MultipartFile file){
        Workbook wb = null;
        String originalFilename = file.getOriginalFilename();
        int fileLength = originalFilename.length();
        String extString = originalFilename.substring(originalFilename.lastIndexOf("."),fileLength);
        InputStream is = null;
        try {
            is = file.getInputStream();
            if(".xls".equals(extString)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                wb = new XSSFWorkbook(is);
            }else{
                wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
    * @description TODO 获取行中列值
    * @param cell
    * @return java.lang.Object
    * @author WangTianLiang
    * @date 2021/6/20
    */
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if (cell == null){
            return "";
        }
        if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
            return cellValue;
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {    //判断是日期类型
                SimpleDateFormat dateformat = new SimpleDateFormat("yyyy/MM/dd");
                Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());//获取成DATE类型
                cellValue = dateformat.format(dt);
            }else{
                DecimalFormat df = new DecimalFormat("0");
                cellValue = df.format(cell.getNumericCellValue());
            }
            return cellValue;
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }

    /**
    * @description TODO 测试用例
    * @author WangTianLiang
    * @date 2021/6/19
    */
    public static void main(String[] args) throws IOException {
        String[] mapper =  {"userName","phoneNum","remark"};
        File file = new File("D:\\用户信息.xls");
        FileInputStream inputStream = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(),
                ContentType.APPLICATION_OCTET_STREAM.toString(), inputStream);
        List<Map<String, Object>> list = excelImport(multipartFile, mapper);
        for(int i=0;i<list.size();i++) {
            Map<String, Object> temp = list.get(i);
            System.out.println(temp.toString());
        }
    }
}

4.4.2：Excel导出工具类ExportExcelUtils

代码如下（示例）：

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/***
 * 导出List<Object>数据到excel
 */
@Slf4j
public final class ExportExcelUtils {

    /***
     * 构造方法
     */
    private ExportExcelUtils() {

    }

    /***
     * 工作簿
     */
    private static HSSFWorkbook workbook;

    /***
     * sheet
     */
    private static HSSFSheet sheet;
    /***
     * 标题行开始位置
     */
//    private static final int TITLE_START_POSITION = 0;

    /***
     * 时间行开始位置
     */
//    private static final int DATEHEAD_START_POSITION = 1;

    /***
     * 表头行开始位置
     */
    private static final int HEAD_START_POSITION = 0;

    /***
     * 文本行开始位置
     */
    private static final int CONTENT_START_POSITION = 1;

    
    /**
    * @description TODO Excel导出
    * @param dataList 对象集合
    * @param titleMap 表头信息（要显示的标题值)[按顺序添加]
    * @param mapper 表头信息（对象属性名称)[按顺序添加]
    * @param sheetName sheet名称和表头值
    * @param response
    * @return void
    * @author WangTianLiang
    * @date 2021/6/20
    */
    public static void excelExport(List dataList, String[] titleMap, String[] mapper, String sheetName, HttpServletResponse response) {
        // 初始化workbook
        initHSSFWorkbook(sheetName);
        // 标题行
        // createTitleRow(titleMap, sheetName);
        // 时间行
        // createDateHeadRow(titleMap);
        // 表头行
        createHeadRow(titleMap);
        // 文本行
        createContentRow(dataList, mapper);
        // 写入处理结果
        try {
            //生成文件名称
            SimpleDateFormat format = new SimpleDateFormat("yyyyMMddhhmmss");

            //到这里，excel就已经生成了，然后就需要通过流来写出去
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            //将excel写入流
            workbook.write(byteArrayOutputStream);
            //设置文件标题
            String outFile = sheetName + format.format(new Date()) + ".xls";
            //设置返回的文件类型
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //对文件编码
            outFile = response.encodeURL(new String(outFile.getBytes("gb2312"), "iso8859-1"));
            //使用Servlet实现文件下载的时候，避免浏览器自动打开文件
            response.addHeader("Content-Disposition", "attachment;filename=" + outFile);
            //设置文件大小
            response.setContentLength(byteArrayOutputStream.size());
            //创建Cookie并添加到response中
            Cookie cookie = new Cookie("fileDownload", "true");
            cookie.setPath("/");
            response.addCookie(cookie);
            //将流写进response输出流中
            ServletOutputStream outputstream = response.getOutputStream();
            byteArrayOutputStream.writeTo(outputstream);

            byteArrayOutputStream.close();
            outputstream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
    * @param sheetName 文件名称
    * @return void
    * @author WangTianLiang 
    * @date 2021/6/20 
    */
    private static void initHSSFWorkbook(String sheetName) {
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
    }

    /** 
    * @description TODO 创建表头行（第二行创建）
    * @param titleMap 对象属性名称->表头显示名称
    * @return
    * @author WangTianLiang 
    * @date 2021/6/20 
    */
    private static void createHeadRow(String[] titleMap) {
        // 第1行创建
        HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);
        int i = 0;
        for (String entry : titleMap) {
            HSSFCell headCell = headRow.createCell(i);
            headCell.setCellValue(entry);
            i++;
        }
    }

    /**
    * @param dataList 对象数据集合
    * @param mapper 表头对应对象
    * @return void
    * @author WangTianLiang 
    * @date 2021/6/20 
    */
    private static void createContentRow(List dataList, String[] mapper) {
        try {
            int i = 0;
            for (Object obj : dataList) {
                HSSFRow textRow = sheet.createRow(CONTENT_START_POSITION + i);
                int j = 0;
                for (String entry : mapper) {
                    String method = "get" + entry.substring(0, 1).toUpperCase() + entry.substring(1);
                    Method m = obj.getClass().getMethod(method, null);
                    String value = m.invoke(obj, null) == null ? "" : m.invoke(obj, null).toString();
                    HSSFCell textcell = textRow.createCell(j);
                    textcell.setCellValue(value);
                    j++;
                }
                i++;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /** 
    * @description TODO <设置EXCEL表格>. <功能详细描述>
    * @param sheetName 文件名称
    * @param titleList
    * @param downloadDataList
    * @param pattern
    * @param subMapSize
    * @return org.apache.poi.hssf.usermodel.HSSFWorkbook
    * @author WangTianLiang 
    * @date 2021/6/20 
    */
    public static HSSFWorkbook constructHSSFWorkbook(String sheetName, List<String[]> titleList,
                                                     List<Map<String, Object>> downloadDataList, String pattern, int subMapSize)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);// 创建一个表
        // 表头标题样式
        HSSFCellStyle headStyle = getHeadStyle(workbook);
        // 普通单元格样式（中文）
        HSSFCellStyle cellStyle = getCellStyle(workbook);
        // 设置表头
        int headRowCount = setTableHead(titleList, pattern, sheet, headStyle);
        if (downloadDataList== null || downloadDataList.size() <= 0){
            return workbook;
        }
        // 设置数据
        insertDataList(downloadDataList, sheet, cellStyle, headRowCount, subMapSize);
        return workbook;
    }


    /** 
    * @description TODO  <设置数据>.<功能详细描述>
    * @param downloadDataList
    * @param sheet
    * @param cellStyle
    * @param headRowCount
    * @param subMapSize
    * @return void
    * @author WangTianLiang 
    * @date 2021/6/20 
    */
    public static void insertDataList(List<Map<String, Object>> downloadDataList, HSSFSheet sheet,
                                      HSSFCellStyle cellStyle, int headRowCount, int subMapSize)
    {
        Map<String, Object> rowData = null;
        // 从第二行开始遍历数据插入表中
        for (int i = 0; i < downloadDataList.size(); i++)
        {
            HSSFRow rowI = sheet.createRow(i + headRowCount);// 第i行
            rowData = downloadDataList.get(i);
            for (String key : rowData.keySet())
            {
                Object value = rowData.get(key);
                if (value instanceof String || value instanceof Integer)
                {
                    // 如果Map中的值是String类型
                    HSSFCell cellJ = rowI.createCell(Integer.parseInt(key));
                    cellJ.setCellValue((String)value);
                    cellJ.setCellStyle(cellStyle);
                }
                else if(value instanceof BigDecimal)
                {
                    HSSFCell cellB = rowI.createCell(Integer.parseInt(key));
                    cellB.setCellValue(value.toString());
                    cellB.setCellStyle(cellStyle);
                }
                else
                {
                    // 如果Map中的值是List类型
                    List<Map<String, String>> coList = (ArrayList<Map<String, String>>)value;
                    for (Map<String, String> subMap : coList)
                    {
                        Object obj = subMap.get("classify");
                        int classify = Integer.parseInt(obj.toString());
                        for (int mapIdx = 0; mapIdx < subMapSize ; mapIdx++)
                        {
                            HSSFCell cellL = rowI.createCell(mapIdx + Integer.parseInt(key) + (classify - 1) * subMapSize);
                            cellL.setCellValue(subMap.get(String.valueOf(mapIdx)));
                            cellL.setCellStyle(cellStyle);
                        }
                    }
                }
            }
            // 创建空白单元格的边框
            short lastCellNum = rowI.getLastCellNum();
            for (int cellNum = 0; cellNum < lastCellNum; cellNum++)
            {
                if (null == rowI.getCell(cellNum))
                {
                    rowI.createCell(cellNum).setCellStyle(cellStyle);
                }
            }
        }
    }

    /** 
    * @description TODO <设置表头>.<功能详细描述>
    * @param titleList
    * @param pattern
    * @param sheet
    * @param headStyle
    * @return int
    * @author WangTianLiang 
    * @date 2021/6/20 
    */
    public static int setTableHead(List<String[]> titleList, String pattern, HSSFSheet sheet, HSSFCellStyle headStyle)
    {
        HSSFRow tableHeadRow = null;
        HSSFCell tableHeadCell = null;
        String[] title = null;
        int headRowCount = titleList.size();
        for (int rowIndex = 0; rowIndex < headRowCount; rowIndex++)
        {
            tableHeadRow = sheet.createRow(rowIndex);
            title = titleList.get(rowIndex);
            for (int colIndex = 0; colIndex < title.length; colIndex++)
            {
                tableHeadCell = tableHeadRow.createCell(colIndex);
                tableHeadCell.setCellValue(title[colIndex]);
                tableHeadCell.setCellStyle(headStyle);
            }
        }
        if (pattern == null || pattern.trim().length() <= 0){
            // 根据传入的表头样式合并单元格
            String[] regions = pattern.split(";");
            for (String region : regions)
            {
                String[] positions = region.split("-");
                int firstRow = Integer.parseInt(positions[0]);
                int lastRow = Integer.parseInt(positions[1]);
                int firstCol = Integer.parseInt(positions[2]);
                int lastCol = Integer.parseInt(positions[3]);
                sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
            }
        }
        return headRowCount;
    }

    
    /** 
    * @description TODO <获取普通单元格样式>.<功能详细描述>
    * @param workbook 
    * @return org.apache.poi.hssf.usermodel.HSSFCellStyle 
    * @author WangTianLiang 
    * @date 2021/6/20
    */
    public static HSSFCellStyle getCellStyle(HSSFWorkbook workbook)
    {
        HSSFFont cellFont = workbook.createFont();
        cellFont.setFontName("微软雅黑");
        cellFont.setFontHeightInPoints((short)8);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框边框
        cellStyle.setFont(cellFont);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        cellStyle.setWrapText(true); // 换行
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        cellStyle.setShrinkToFit(false);
        return cellStyle;
    }


    /** 
    * @description TODO <获取EXCEL表格的表头样式>.<功能详细描述>
    * @param workbook 
    * @return org.apache.poi.hssf.usermodel.HSSFCellStyle 
    * @author WangTianLiang 
    * @date 2021/6/20
    */
    public static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook)
    {
        HSSFFont headFont = workbook.createFont();
        headFont.setFontName("微软雅黑");
        headFont.setFontHeightInPoints((short)8);// 字体大小
        headFont.setBold(true);
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFont(headFont);
        headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        headStyle.setLocked(true);
        headStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        headStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        headStyle.setBorderTop(BorderStyle.THIN);// 上边框
        headStyle.setBorderRight(BorderStyle.THIN);// 右边框
        headStyle.setLocked(true);
        return headStyle;
    }
}

5：Excel导入案例实图

Excel中的数据信息 如图（示例）：

Excel导入数据User表中数据 如图（示例）：

希望可以帮助到您…