JAVA导出MySQL数据库数据生成Excel表

最新推荐文章于 2024-01-30 12:01:54 发布

qq_42738387

最新推荐文章于 2024-01-30 12:01:54 发布

阅读量3.3k

点赞数 8

分类专栏：表的导入到处文章标签： java 数据库 mysql

本文链接：https://blog.csdn.net/qq_42738387/article/details/122440532

版权

表的导入到处专栏收录该内容

1 篇文章 0 订阅

订阅专栏

环境：JDK1.8、Maven3.6.3、Mysql8.0、poi4.1、Mybatis-plus3.4.3
话不多说，先上代码。后续需要自己整理一下分层，保持代码的干净整洁从自己做起。
Controller:如下`

package com.example.testexcel.controller;

import com.example.testexcel.dao.userDao;
import com.example.testexcel.entity.client;
import com.example.testexcel.util.ResponseUtil;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;

/**
 * Author: 于顺清
 * Coopyright(C),2019-2022
 * FileName：excelController
 * Date:     2022/1/7/00714:28
 * Description:表的导出
 */
@RestController
public class excelController {
    @Autowired
    userDao userdao;
    @RequestMapping("excel_down")
    public String Excel_Down(HttpServletResponse response){

        String table_url = this.getClass().getClassLoader().
                getResource("static/webapp/client_table.xls").getPath();
//        System.out.println(table_url);
        List<client> list = userdao.selectList(null);
        Workbook workbook = fillExcelDataWithTemplate(list,table_url);
        try {
            ResponseUtil.export(response,workbook,"客户表.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "成功";
    }

    private Workbook fillExcelDataWithTemplate(List<client> list, String templateUrl) {

        Workbook wb = null;
        try {
            POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(templateUrl));
            wb = new HSSFWorkbook(fileSystem);
            //wb = new XSSFWorkbook(String.valueOf(fileSystem));
            //取得模板的第一个首页
            Sheet sheet = wb.getSheetAt(0);
           //拿到sheet有多少列
            //int cellNums = sheet.getRow(0).getLastCellNum();
           //从第二行开始，下标是1
            int rowIndex = 1;
            Row row;
            for(client c : list){
                row = sheet.createRow(rowIndex);
                rowIndex++;
                row.createCell(0).setCellValue(c.getId());
                row.createCell(1).setCellValue(c.getNumber());
                row.createCell(2).setCellValue(c.getUsername());
                row.createCell(3).setCellValue(c.getPhone());
                row.createCell(4).setCellValue(c.getNote());
                row.createCell(5).setCellValue(DateFormatUtils.format(c.getCreattime(),"yyyy-MM-dd HH:mm:ss"));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }
}

DAO:

package com.example.testexcel.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.testexcel.entity.client;
import org.apache.ibatis.annotations.Mapper;

/**
 * Author: 于顺清
 * Coopyright(C),2019-2022
 * FileName：userDao
 * Date:     2022/1/8/00813:26
 * Description:用于Excel导入导出数据
 */
@Mapper
public interface userDao extends BaseMapper<client> {

}

entity:

package com.example.testexcel.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.util.Date;

/**
 * Author: 于顺清
 * Coopyright(C),2019-2022
 * FileName：client
 * Date:     2022/1/7/00715:11
 * Description:客户信息类
 */
@Data
@TableName(value = "sys_excel_test")
public class client {

    private Integer id;
    //@ExcelColumn(value="编号",col = 2)
    private String number;
    private String username;
    private String phone;
    private String note;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date creattime;

}

util:

package com.example.testexcel.util;

 import java.io.OutputStream;
 import java.io.PrintWriter;
 import javax.servlet.http.HttpServletResponse;
 import org.apache.poi.ss.usermodel.Workbook;
 public class ResponseUtil {
        public static void write(HttpServletResponse response,Object o) throws Exception{
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();
        out.println(o.toString());
        out.flush();
        out.close();
}

public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
        response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out=response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
        }
}

Pom:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>testexcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>testexcel</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--excel导入导出-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- 阿里数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- lombok工具 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <!-- 模板引擎 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--mybatis-plus依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3.4</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>

    </build>

</project>

到这里为止直接启动Application即可。在浏览器里面访问http://localhost:8080/excel_down这个地址就可以下载出来了。
在这里插入图片描述
不过有个小问题，如果我们在实际应用中出现一种情况–即客户想使用自己上传的模板进行导出数据呢。
这里要注意一个点，就是客户传进来的模板表是xls还是xlsx，一个代表2003一个代表2007版，这里容易出现一个问题，就是版本问题无法识别。错误如下：
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
我在CSDN上找到一个觉得挺有帮助的一段话。
在这里插入图片描述
所以我们在做这个功能的时候，应该先加一个判断判断所选择的模板是什么类型，再决定调用哪个方法。
以下是使用xlsx模板的代码（和上面代码是同一个项目结构，放在一起的）。没有和上面整合，下面是在代码里面直接写死了下载的位置，执行即直接下载到该位置。可以自己改成自己的位置。一般写桌面位置。
util包下Xlsx类：

package com.example.testexcel.util;

/**
 * Author: 于顺清
 * Coopyright(C),2019-2022
 * FileName：Xlsx
 * Date:     2022/1/11/01118:37
 * Description:yu1
 */
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Xlsx {
//标题，内容，下表名
public XSSFWorkbook getWorkBook(List<String> titleList, List<List<String>> contentlist, String sheetName) {
    XSSFWorkbook xwk = new XSSFWorkbook();
    XSSFDataFormat format = xwk.createDataFormat();
    XSSFCellStyle cellStyle = xwk.createCellStyle();
    XSSFSheet xssfSheet = xwk.createSheet(sheetName);
    cellStyle.setDataFormat(format.getFormat("@"));//文本格式             
    int j = 0;
    createHeader(xssfSheet, cellStyle, titleList, j);
    int size = contentlist.size();
    for (j = 0; j < size; j++) {
        List<String> oneRow = contentlist.get(j);
        createContent(xssfSheet, cellStyle, oneRow, j);
        oneRow = null;
    }
    return xwk;
}
private void createHeader(XSSFSheet xssfSheet, XSSFCellStyle cellStyle, List<String> titleList, int j) {

    XSSFRow rowTitle = xssfSheet.createRow(j);
    for (int cellTitle = 0; cellTitle < titleList.size(); cellTitle++) {
        Cell cellIndex = rowTitle.createCell(cellTitle);
        cellIndex.setCellStyle(cellStyle);
        cellIndex.setCellValue(titleList.get(cellTitle));
    }
}
private void createContent(XSSFSheet xssfSheet, XSSFCellStyle cellStyle, List<String> oneRow, int j) {
    XSSFRow rowContent = xssfSheet.createRow(j + 1);
    for (int cellContent = 0; cellContent < oneRow.size(); cellContent++) {
        Cell cellIndex = rowContent.createCell(cellContent);
        cellIndex.setCellStyle(cellStyle);
        cellIndex.setCellValue(oneRow.get(cellContent));
    }
  }
}

测试类：

package com.example.testexcel;

import com.example.testexcel.util.Xlsx;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import java.io.FileNotFoundException;
import java.util.List;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@SpringBootTest
class TestexcelApplicationTests {

    @Test
    void contextLoads() throws IOException {
        List<String> titleList = new  ArrayList<String>();
        titleList.add("NO");
        titleList.add("ID");
        titleList.add("Name");
        titleList.add("Age");
        List<String> content = null;
        List<List<String>> contentsList = new  ArrayList<List<String>>();
        content = new ArrayList<String>();
        content.add("1");
        content.add("180001");
        content.add("Jame");
        content.add("18");
        contentsList.add(content);
        content = new ArrayList<String>();
        content.add("1");
        content.add("180002");
        content.add("Lucky");
        content.add("18");
        contentsList.add(content);

        XSSFWorkbook workBook = null;
        FileOutputStream output = null;
        String sheetName = "student";
        String fileName = "D:/student.xlsx";

        if (contentsList.size() > 0) {
            try {
                Xlsx eu = new Xlsx();
                workBook = eu.getWorkBook(titleList, contentsList, sheetName);
                output =  new FileOutputStream(fileName);
                workBook.write(output);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally{
                if(output != null){
                    output.flush();
                    output.close();
                }
                if (workBook != null) {
                    workBook.close();
                }
            }
        }
    }
}

判断的话，之前看到过一个好的判断，没找到了，是直接判断表类型的。
先用着下面这个叭。
//获得文件名 String fileName =file.getOriginalFilename();
//判断文件是否是excel文件 if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx))

qq_42738387

关注

8
点赞
踩
22

收藏

觉得还不错? 一键收藏
0
评论
JAVA导出MySQL数据库数据生成Excel表

环境：JDK1.8、Maven3.6.3、Mysql8.0、poi4.1、Mybatis-plus3.4.3话不多说，先上代码。后续需要自己整理一下分层，保持代码的干净整洁从自己做起。Controller:如下`package com.example.testexcel.controller;import com.example.testexcel.dao.userDao;import com.example.testexcel.entity.client;import com.example
复制链接

扫一扫