利用POI框架操作Excel表

最新推荐文章于 2022-05-23 14:39:08 发布

原创最新推荐文章于 2022-05-23 14:39:08 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

小的技术专栏收录该内容

2 篇文章

订阅专栏

本文分享了如何在Java中使用Apache POI库实现Excel数据的导入和导出功能，包括单元格样式的定制、数据读写的具体实现，以及如何在Spring MVC中处理文件上传和下载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天公司里面要求我们做一个功能，把部分查到的数据以Excel表中的数据导出，并且有的功能需要实现从excel表中的数据导入到数据库中。

对于这名词都没听过的人，只能在网上查找写视频或者教学的博客之类的。

话不多说，先奉上我查找感觉有用的资料

视频：（可以作为入门，做些简单正常的Excel表操作的没有问题）

https://www.bilibili.com/video/av38893199

博客：可以提升提升

https://www.cnblogs.com/Damon-Luo/p/5919656.html

https://blog.youkuaiyun.com/vbirdbest/article/details/72870714

https://www.cnblogs.com/huajiezh/p/5467821.html

https://www.cnblogs.com/LiZhiW/p/4313789.html

https://www.cnblogs.com/dyh2025/p/9311118.html

先展示下我所实现的Excel表的导入和导出

先导入依赖

    <!--POI-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>

单元格式的工具类（其实，也算不上什么工具类，因为要重复写很多遍，就干脆封装了起来）

package com.crm.utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * @description:   单元格的样式工具类
 * @auther:田坤
 * @date 2019/7/6 18:45
 **/
public class ExcelCellStyle {
    /**
     * 获取标题的单元格样式
     * @param wb
     * @return
     */
    public  static HSSFCellStyle getTitleStyle(HSSFWorkbook wb){
        //创建单元格标题的样式
        HSSFCellStyle hcs =  wb.createCellStyle();
        //设置单元格的边框
        hcs.setBorderBottom(BorderStyle.THICK);      //下
        hcs.setBorderLeft(BorderStyle.THICK);        //左
        hcs.setBorderRight(BorderStyle.THICK);       //右
        hcs.setBorderTop(BorderStyle.THICK);         //上
        //水平居住
        hcs.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        hcs.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体的边框
        HSSFFont hf = wb.createFont();
        hf.setBold(true);
        hcs.setFont(hf);
        //可换行
        hcs.setWrapText(true);
        return hcs;
    }

    /**
     * 获取内容的单元格样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle getContentStyle(HSSFWorkbook wb){
        //创建单元格的样式
        HSSFCellStyle hcsc =  wb.createCellStyle();
        //设置单元格的边框
        hcsc.setBorderBottom(BorderStyle.THIN);      //下
        hcsc.setBorderLeft(BorderStyle.THIN);        //左
        hcsc.setBorderRight(BorderStyle.THIN);       //右
        hcsc.setBorderTop(BorderStyle.THIN);         //上
        //水平居住
        hcsc.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        hcsc.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体的边框
        HSSFFont hfc = wb.createFont();
        hfc.setBold(false);
        hcsc.setFont(hfc);
        //可换行
        hcsc.setWrapText(true);
        return hcsc;
    }
}

服务层接口

    /**
     *  将基础数据的信息以excel表的形式导出
     * @param os
     * @param list
     */
    HSSFWorkbook exportExcel(OutputStream os, List<CrmDataPojo> list);

    /**
     * 导入Excel表中的数据添加到基础数据表中
     * @param is  需要导入的Excel的文件输入流
     */
    void importExcel(InputStream is);

服务层实现类

public HSSFWorkbook exportExcel(OutputStream os, List<CrmDataPojo> list) {
        //创建一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建一个工作表（表名为 EXCEL_NAME
        HSSFSheet sheet = wb.createSheet(EXCEL_NAME);
        //获取标题单元格样式
        HSSFCellStyle hcs = ExcelCellStyle.getTitleStyle(wb);
        //设置列宽
        sheet.setColumnWidth(0,4000);
        sheet.setColumnWidth(1,5000);
        sheet.setColumnWidth(2,6000);
        sheet.setColumnWidth(3,6000);
        sheet.setColumnWidth(4,3000);
        //创建一行，行的索引是从0开始的  ===》写标题
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
        for(int i = 0 ;i < EXCEL_HEAD_NAME.length; i++){
            cell = row.createCell(i);
            cell.setCellStyle(hcs);
            cell.setCellValue(EXCEL_HEAD_NAME[i]);
        }
        //导出的内容
        int rowCount = 1;

        //获取内容单元格样式
        HSSFCellStyle hcsc = ExcelCellStyle.getContentStyle(wb);

        for (CrmDataPojo data:
             list) {
            row = sheet.createRow(rowCount);
            HSSFCell cell1 = row.createCell(0);
            cell1.setCellStyle(hcsc);
            HSSFCell cell2 = row.createCell(1);
            cell2.setCellStyle(hcsc);
            HSSFCell cell3 = row.createCell(2);
            cell3.setCellStyle(hcsc);
            HSSFCell cell4 = row.createCell(3);
            cell4.setCellStyle(hcsc);
            HSSFCell cell5 = row.createCell(4);
            cell5.setCellStyle(hcsc);
            cell1.setCellValue(data.getData_Num());
            cell2.setCellValue(data.getData_Sort_());
            cell3.setCellValue(data.getData_Tiaomu());
            cell4.setCellValue(data.getData_Value());
            cell5.setCellValue(data.getData_State());
            rowCount++;
        }
        return wb;
    }

    @Override
    public void importExcel(InputStream is) {
        //创建一个Excel工作簿对象
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(is);
            //获取工作表
            HSSFSheet sheet = wb.getSheetAt(0);
            //获取有效的行数
            int numberOfRows = sheet.getPhysicalNumberOfRows();
            CrmDataPojo crmDataPojo = null ;
            CrmDataPojo temp = null;

            for (int i = 1 ; i < numberOfRows; i++){
                crmDataPojo = new CrmDataPojo();

                int data_Num = (int)sheet.getRow(i).getCell(0).getNumericCellValue();
                String data_Sort = sheet.getRow(i).getCell(1).getStringCellValue();
                String data_Tiaomu = sheet.getRow(i).getCell(2).getStringCellValue();
                String data_Value = sheet.getRow(i).getCell(3).getStringCellValue();
                String data_State = sheet.getRow(i).getCell(4).getStringCellValue();

                crmDataPojo.setData_Num(data_Num);
                crmDataPojo.setData_Sort_(data_Sort);
                crmDataPojo.setData_Tiaomu(data_Tiaomu);
                crmDataPojo.setData_Value(data_Value);
                crmDataPojo.setData_State(data_State);

                //如果里面存在id数据就进行修改    不存在就进行增加
                if(crmDataMapper.selDataById(crmDataPojo.getData_Num())!=null){
                    crmDataMapper.updDataType(crmDataPojo);
                }else{
                    crmDataMapper.addDataType(crmDataPojo);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

控制层

/**
     * 将查询出的基础数据表的信息以Exceld的格式导出
     * @param resp
     */
    @RequestMapping("exExcelCrmData")
    @ResponseBody
    public void excelCrmData(HttpServletResponse resp){
        try {
            String filename = "基础数据表.xls";
            //清空缓存
            resp.reset();
            //定义下载的类型，标明是excel文件
            resp.setContentType("application/vnd.ms-excel");

            //设置输出流实现下载页面" Content-Disposition"
            resp.setHeader("Content-Disposition","attachment;filename="+ new String(filename.getBytes(),"iso-8859-1"));
            //相应对象

            ServletOutputStream outputStream = resp.getOutputStream();

            List<CrmDataPojo> crmDataPojos = crmDataService.selAllData();
            //返回生成的EXCEL表
            HSSFWorkbook wb = crmDataService.exportExcel(outputStream, crmDataPojos);
            wb.write(outputStream);
            wb.close();

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


    @RequestMapping("imExcelCrmData")
    @ResponseBody
    public boolean imExcelCrmData(MultipartFile file, HttpServletRequest req){
        try {
            System.out.println(file.getName());
            System.out.println(file.getSize());
            crmDataService.importExcel(file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }finally {
            return true;
        }
    }

导入文件===>也就是文件的上传需要导入依赖和配置springmvc

    <!-- commons - fileupload  -->
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>${commons-fileupload-version}</version>
    </dependency>

fileupload依赖 commons-io包，所以只导入fileupload包

springMVC配置文件

!-- MutipartResovler的解析器 -->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <!-- 上传文件的最大  单位字节 -->
        <property name="maxUploadSize" value="500000"></property>
    </bean>

    <!-- 异常解析类 -->
    <bean id="exceptionResolver" class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver">
        <property name="exceptionMappings">
            <props>
                <!-- 文件如果超过指定大小报异常，  让其跳转到error.jsp页面里 -->
                <prop key="org.springframework.web.multipart.MaxUploadSizeExceededException">/error.jsp</prop>
            </props>
        </property>
    </bean>

前端就利用一个a标签进行下载；

注意：不能使用js去访问该控制器，不会下载下来（这个问题困扰了我俩三个小时，我是利用 ajxa的方式去下载）

网上对于这个也进行了封装：那就是 EsayPoi

可参考：

http://easypoi.mydoc.io/#text_197835