Java导入导出Excel表

最新推荐文章于 2024-09-23 14:24:52 发布

你天一哥哥

最新推荐文章于 2024-09-23 14:24:52 发布

阅读量812

点赞数

分类专栏： RJ

本文链接：https://blog.youkuaiyun.com/jiahanghacker/article/details/95901864

版权

RJ 专栏收录该内容

41 篇文章

订阅专栏

本文主要介绍在Java中如何进行Excel文件的导入和导出。内容包括前端页面注意事项，强调前端name属性必须设为'file'以避免错误，以及后端的Excel格式获取和SpringBoot项目的配置步骤。

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

前端页面

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
		<title></title>
	</head>
	<body>
		
		
	  <form method="post" action="http://localhost:8080/excel/importExcel" enctype="multipart/form-data" onsubmit="return importData();"> 
       <input id="filepath" name="file" type="file" size="30" value=""  style="font-size:14px" />
       <button type="submit" style="height:25px" value="导入数据">导入数据</button>
	</body>
</html>

注意前端页面的name值必须和后台的一样，设置为file。不然会报错的，自己踩过的坑。

1：先获取Excel表的格式，按格式填写，再进行上传。

IDEA创建SpringBoot项目

pom.xml

        <!--Excel导入导出相关jar包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.7</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.7</version>
        </dependency>

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



        <!-- swagger -->
        <!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger-ui -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger2 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>

SwaggerConfig

package com.excel.demo.Config;



import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import springfox.documentation.builders.ApiInfoBuilder;

import springfox.documentation.builders.ParameterBuilder;

import springfox.documentation.builders.PathSelectors;

import springfox.documentation.builders.RequestHandlerSelectors;

import springfox.documentation.service.ApiInfo;

import springfox.documentation.service.Contact;

import springfox.documentation.service.Parameter;

import springfox.documentation.spi.DocumentationType;

import springfox.documentation.spring.web.plugins.Docket;

import springfox.documentation.swagger2.annotations.EnableSwagger2;



import java.util.ArrayList;

import java.util.HashSet;

import java.util.List;

import java.util.Set;



/**

 * @author djw

 * @create 2018-04-20 上午10:50

 * @desc swagger配置类

 **/

@Configuration

@EnableSwagger2

public class SwaggerConfig {



    @Bean

    public Docket restApi() {

        Docket docket = new Docket(DocumentationType.SWAGGER_2)

                .apiInfo(apiInfo())

                .select()

                .apis(RequestHandlerSelectors.basePackage("com.excel.demo.Controller"))

                .paths(PathSelectors.any())

                .build();



        return docket;

    }



    private ApiInfo apiInfo() {

        return new ApiInfoBuilder()

                .title("Excel操作相关接口")

                .description("Excel操作相关接口")

                .version("1.0.0")

                .build();

    }



}

ExcelController


package com.excel.demo.Controller;




import com.excel.demo.Pojo.User;
import com.excel.demo.Utils.ExcelExport;
import com.excel.demo.Utils.ExcelImport;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;



@RestController
@RequestMapping("excel")
@Api(value = "ecxel相关操作",description = "excel相关操作")
public class ExcelController {

/*
    @GetMapping("getExcel")
    @ApiOperation(value = "导出Excel",notes = "导出Excel")
    public void getExcel(HttpServletResponse response) {
        //excel标题
        String[] title = {"名称", "性别", "年龄", "学校", "班级"};
        //excel文件名
        String fileName = "学生信息表";
        String sheetName = "学生信息表";
        String[][] values = {{"张三", "男", "18", "家里蹲大学", "14电信1班"}, {"张三", "男", "18", "家里蹲大学", "14电信1班"}, {"张三", "男", "18", "家里蹲大学", "14电信1班"},
                {"张三", "男", "18", "家里蹲大学", "14电信1班"}, {"张三", "男", "18", "家里蹲大学", "14电信1班"}, {"张三", "男", "18", "家里蹲大学", "14电信1班"},
                {"张三", "男", "18", "家里蹲大学", "14电信1班"}};
        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelUtil.createWorkbook(sheetName, title, values);
        //响应到客户端
        try {
            //设置编码、输出文件格式
            response.reset();
            fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
*/



    @GetMapping("createExcel")
    @ApiOperation(value = "创建excel表格",notes = "创建Excel表格")
    public void createExcel(HttpServletResponse response){
        //添加测试数据
        List<User> userList = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            User user = new User();
            user.setUserId(i);
            user.setUserName("张三"+i);
            user.setSex("男");
            user.setAge(i);
            user.setSchool("学校"+i);
            userList.add(user);
        }
        //excel标题
        String[] title = {"id", "姓名", "性别", "年龄", "学校"};
        String[][] objects = new String[userList.size()][title.length];
        for (int i = 0; i < userList.size(); i++) {
            User user = userList.get(i);
            objects[i][0] = String.valueOf(user.getUserId());
            objects[i][1] = user.getUserName();
            objects[i][2] = user.getSex();
            objects[i][3] = String.valueOf(user.getAge());
            objects[i][4] = user.getSchool();
        }

        String fileName = "学生信息表";
        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelExport.createWorkbook(fileName, title, objects);
        //响应到客户端
        try {
            //设置编码、输出文件格式
            response.reset();
            fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //@GetMapping("importExcel")
    @PostMapping("importExcel")
    @ApiOperation(value = "导入Excel",notes = "将数据以Excel的形式导入到数据库中")
    public Boolean importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws Exception {
        //获取用户上传表格的地址，执行完程序后表格将会删除，避免占用内存
        String filePath = request.getSession().getServletContext().getRealPath("/");
        //根据地址和文件名准确获取用户上传的表格
        File tempFile = new File(filePath+file.getOriginalFilename());
        try {
            //将MultipartFile转换为File类型
            file.transferTo(tempFile);
            List<Map<Integer,String>> dataList = ExcelImport.read(tempFile,5);
            User user = new User();
            for (int i = 0; i < dataList.size(); i++) {
                //此处的取值顺序取决于Excel表的表头顺序，一一对应
                //学生id  姓名  性别  年龄  学校
                user.setUserId(Integer.valueOf(dataList.get(i).get(0)));
                user.setUserName(dataList.get(i).get(1));
                user.setSex(dataList.get(i).get(2));
                user.setAge(Integer.valueOf(dataList.get(i).get(3)));
                user.setSchool(dataList.get(i).get(4));
                //此处应该将user对象插入数据库中
                //UserService.insert(user);
                System.out.println(user+"666");
            }
            //执行完程序后删除用户上传文件
            tempFile.delete();
        } catch (Exception e) {
            tempFile.delete();
            throw new Exception("批量录入用户失败，请检查表格中的数据是否和数据库中的数据冲突！");
        }
        return true;
    }
}

User


package com.excel.demo.Pojo;



/**

 * @Author djw

 * @Description 演示所创建的对象

 * @Date 2018/9/5 14:52

 */

public class User {

    Integer userId;

    String userName;

    String sex;

    Integer age;

    String school;



    public Integer getUserId() {

        return userId;

    }



    public void setUserId(Integer userId) {

        this.userId = userId;

    }



    public String getUserName() {

        return userName;

    }



    public void setUserName(String userName) {

        this.userName = userName;

    }



    public String getSex() {

        return sex;

    }



    public void setSex(String sex) {

        this.sex = sex;

    }



    public Integer getAge() {

        return age;

    }



    public void setAge(Integer age) {

        this.age = age;

    }



    public String getSchool() {

        return school;

    }



    public void setSchool(String school) {

        this.school = school;

    }



    @Override

    public String toString() {

        return "User{" +

                "userId=" + userId +

                ", userName='" + userName + '\'' +

                ", sex='" + sex + '\'' +

                ", age=" + age +

                ", school='" + school + '\'' +

                '}';

    }

}

ExcelExport


package com.excel.demo.Utils;



import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;



/**

 * @Author djw

 * @Description //TODO

 * @Date 2018/9/5 15:52

 */

public class ExcelExport {



    /**

     * 导出Excel

     * @param sheetName sheet名称

     * @param title 标题

     * @param values 内容

     * @return

     */

    public static HSSFWorkbook createWorkbook(String sheetName,String []title,String [][]values){



        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件

        HSSFWorkbook wb = new HSSFWorkbook();



        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet

        HSSFSheet sheet = wb.createSheet(sheetName);



        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制

        HSSFRow row = sheet.createRow(0);



        // 第四步，创建单元格，并设置值表头 设置表头居中

        HSSFCellStyle style = wb.createCellStyle();

        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式



        //声明列对象

        HSSFCell cell = null;



        //创建标题

        for(int i=0;i<title.length;i++){

            cell = row.createCell(i);

            cell.setCellValue(title[i]);

            cell.setCellStyle(style);

        }



        //创建内容

        for(int i=0;i<values.length;i++){

            row = sheet.createRow(i + 1);

            for(int j=0;j<values[i].length;j++){

                //将内容按顺序赋给对应的列对象

                row.createCell(j).setCellValue(values[i][j]);

            }

        }

        return wb;

    }

}

ExcelImport


package com.excel.demo.Utils;



import org.apache.poi.hssf.usermodel.HSSFCell;

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.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;



import java.io.File;

import java.io.FileInputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;



/**

 * @Author djw

 * @Description //TODO

 * @Date 2018/9/5 16:16

 */

public class ExcelImport {



    /**

     * @param dest 表格文件

     * @param cellLength 一行多少个单元格

     * @return 返回list集合

     * @throws Exception

     */

    public static List<Map<Integer,String>> read(File dest, Integer cellLength) throws Exception{

        Workbook wookbook = null;

        FileInputStream fis = null;

        int cellType = 1;

        try {

            fis = new FileInputStream(dest);

            //用HSSF来处理，有异常即为xlsx格式，用XSSF处理

            wookbook = new HSSFWorkbook(fis);//得到工作簿

            cellType = HSSFCell.CELL_TYPE_STRING;

        } catch (Exception e) {

            try {

                fis = new FileInputStream(dest);//这里不创建输入流就会报错stream close

                wookbook = new XSSFWorkbook(fis);

                cellType = XSSFCell.CELL_TYPE_STRING;

            } catch (Exception e1) {

                //返回文件格式错误异常

                throw new Exception("This file is not in excel format");

            }//得到工作簿

        } finally {

            fis.close();

        }

        //得到一个工作表

        Sheet sheet = wookbook.getSheetAt(0);

        //获得数据的总行数

        int totalRowNum = sheet.getLastRowNum();

        //要获得属性

        List<Map<Integer,String>> list = new ArrayList<Map<Integer,String>>();

        Map<Integer,String> map = null;

        //获得所有数据

        //从第x行开始获取

        for(int x = 1 ; x <= totalRowNum ; x++){

            map = new HashMap<Integer,String>();

            //获得第i行对象

            Row row = sheet.getRow(x);

            //如果一行里的所有单元格都为空则不放进list里面

            int a = 0;

            for(int y=0;y<cellLength;y++){

                Cell cell = row.getCell(y);

                if(cell == null){

                    map.put(y,"");

                }else{

                    cell.setCellType(cellType);

                    map.put(y, cell.getStringCellValue().toString());

                }

                if(map.get(y)==null||"".equals(map.get(y))){

                    a++;

                }

            }

            if(a!=cellLength){

                list.add(map);

            }

        }

        return list;

    }

}