mysql 数据表和excel的导入导出（springboot）

mysql 数据表和excel的导入导出（springboot）

1. 先说一下遇到的问题 ：
服务上上传报 ：Your file appears not to be a valid OLE2 document 错误
解决办法 ：
1.先查看服务上是否有excle工作簿 （有的是因为没有）
2. 把文件另存为修改一下文件类型（最后结果试了一下不用修改也可以上传了）

下面来贴出代码

第一步：先导入jar

 <!-- POI -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.16</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-excelant</artifactId>
        <version>3.16</version>
    </dependency>

第二步：前提准备

2.1 数据库字段

   CREATE TABLE `admin` (
      `id` INT(20) NOT NULL AUTO_INCREMENT,
      `userName` VARCHAR(50) DEFAULT NULL,
      `password` VARCHAR(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

2.2 实体类相对应即可
2.3 mapeer

public interface AdminMapper {

    List<Admin> selAll();
    int insertAdmin(Admin admin);
}

mapper映射

 <--插入-->
 <insert id="insertAdmin" parameterType="java.util.List">
		INSERT INTO `admin` (userName,password) VALUE(#{userName},#{password})
 </insert>
   <--查询-->
<select id="selAll" resultType="cn.mmn.manager.pojo.Admin">
    SELECT id, userName, password
    FROM `admin`
</select>

2.4 service此处省略

第三步 ：controller 【核心代码】

package cn.mmn.manager.controller.export;

import cn.mmn.manager.pojo.Admin;
import cn.mmn.manager.service.AdminService;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.List;

@Controller
public class ExcelController {


@Resource
AdminService adminService ;


/**
 * 访问
 * @return
 */
  @RequestMapping("/a")
    public String show(){
        return "aaa";
    }
/**
 * excel信息 导入到数据库
 * @param file
 * @param request
 * @param model
 * @return
 */
@RequestMapping(value="/importexcel",method= RequestMethod.POST)
public String uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request, Model model) {
    //获取服务器端路径
//  String path = "服务路经此处";
//本地路径
    String path = "D:\\excel";
    //获取到上传文件名称
    String fileName = file.getOriginalFilename();
    //创建目标File
    File targetFile = new File(path + "\\" + fileName);
    //创建存储目录
    File targetPath = new File(path);

    //判断服务器端目录是否存在,如果不存在创建目录
    if (!targetPath.exists()) {
        targetPath.mkdir();
    }
    //把上传的文件存储到服务器端
    try {
        file.transferTo(targetFile);
    } catch (IllegalStateException e) {

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

        e.printStackTrace();
    }
    //读取上传到服务器端的文件,遍历excel
    try {
        Workbook workbook = WorkbookFactory.create(targetFile);
        Sheet sheet = workbook.getSheet("Sheet1");
        //判断行数
        int rownum = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rownum; i++) {    // 此处从1 开始，可以把标签头省略
            Row row = sheet.getRow(i);
            //判断单元格数量
            int cellnum = row.getPhysicalNumberOfCells();
            StringBuffer buf = new StringBuffer();
            for (int j = 0; j < cellnum; j++) {
                Cell cell = row.getCell(j);
                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                    buf.append(cell.getStringCellValue() + "~");
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    //创建数字格式化工具类
                    DecimalFormat df = new DecimalFormat("####");
                    //把从cell单元格读取到的数字,进行格式化防止科学计数法形式显示
                    buf.append(df.format(cell.getNumericCellValue()) + "~");
                }
            }
            //单元格循环完成后读取到的是一行内容  1   aa  bb
            String hang = buf.toString();
            String[] rows = hang.split("~");
            Admin admin = new Admin();
            admin.setUserName(rows[1]);
            admin.setPassword(rows[2]);
          //  admin.setId(Integer.valueOf(rows[2]));    // int类型时转换
            System.out.println("上传管理员信息:" + admin);
          adminService.insertAdmin(admin);
        }
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return "success";

}

/**
 * 数据库信息 导出到excel
 * @param request
 * @param response
 * @param model
 * @return
 * @throws IOException
 */
@RequestMapping(value = "/downloadexcel")
public ResponseEntity<byte[]> down(HttpServletRequest request, HttpServletResponse response, Model model)
        throws IOException {
    // 从数据库读取数据
    List<Admin> allStu = adminService.selAll();
    // 获取服务路径
   // String path = “服务路径”;
   //本地路径
    String path = "D:\\excel\\upload";
    String filename = "bbb.xls";     // 也可修改为 xls
    // 存储File
    File tfile = new File(path + "\\" + filename);
    // 目录
    File mfile = new File(path);
    if (!tfile.exists()) {
        mfile.mkdir();
    }
    // 生成excel
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("管理员信息表");
    int rownum = 0;
    XSSFRow row1 = sheet.createRow(rownum);
    row1.createCell(0).setCellValue("id");
    row1.createCell(1).setCellValue("姓名");
    row1.createCell(2).setCellValue("密码");
    rownum =1;   //这里再次赋值 是为了把标题头加上
    for (Admin admin : allStu) {
        XSSFRow row = sheet.createRow(rownum);
        row.createCell(0).setCellValue(admin.getId());
        row.createCell(1).setCellValue(admin.getUserName());
        row.createCell(2).setCellValue(admin.getPassword());
        rownum++;
    }
    // 保存workbook
    try {
        workbook.write(new FileOutputStream(tfile));
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    // 创建请求头对象
    HttpHeaders headers = new HttpHeaders();
    // 下载显示的文件名，解决中文名称乱码问题
    String downloadFileName = new String(filename.getBytes("UTF-8"), "iso-8859-1");
    // 通知浏览器以attachment(下载方式)打开
    headers.setContentDispositionFormData("attachment", downloadFileName);
    // application/octet-stream:二进制流数据（最常见的文件下载）
    headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(tfile),
            headers, HttpStatus.CREATED);
    return responseEntity;
}

}

四 ：页面

aaa.html页面 ：

 <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Title</title>
    </head>
    <body>
 
 
    <form action="/importexcel" method="post" enctype="multipart/form-data">
    
        <input type="file" name="file"/>
    
        <input type="submit" value="上传"/>
    </form>
    
    <!--<input type='button' value='导出' class='s_button' οnclick='doExportExcel()'/>&nbsp;-->
    
    <a href="/downloadexcel">导出Excel</a>
    </body>
    </html>

success.html页面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Insert title here</title>
</head>
<body>
<div  style="text-align:center">
    上传文件成功!</div>
</body>

完结 ！！！
第一次做这个导入导出,中间也参考了很多文档

参考文档 ：https://blog.csdn.net/weixin_42548384/article/details/83507612

关注公众号、每天更多内容分享哦