springboot-poi-导入导出

可以参考的文章:

POI 导出Excel 并且根据内容设置列宽自适应

 

package com.hikvision.shiro.web.controller;

import com.hikvision.shiro.web.entity.SysUser;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author xionghao5
 * @Title: PoiController
 * @ProjectName shiro
 * @Description: TODO
 * @date 2019/5/14 15:40
 */
@Controller
@RequestMapping("/poi")
public class PoiController extends AbstractController {
    @RequestMapping("/index")
    public String toIndex() {
        return "upload";
    }

    @RequestMapping("/excel/upload")
    public String fileUpload(@RequestParam("file") MultipartFile file) throws IOException {
        String originalFilename = file.getOriginalFilename();
        if (!(originalFilename.contains("xls") || originalFilename.contains("xlsx"))) {
            System.out.println("文件必须是excel!");
            return null;
        }
        long size = file.getSize();
        if (originalFilename == null || originalFilename.equals("") || size == 0) {
            System.out.println("文件不能为空");
            return null;
        }
        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
        int numberOfSheets = workbook.getNumberOfSheets();//获得有多少sheet
        HSSFSheet sheet = workbook.getSheetAt(0);//默认只有一个sheet
        int rows = sheet.getPhysicalNumberOfRows();//获得sheet有多少行
        //遍历行
        for (int j = 0; j < rows; j++) {
            if (j == 0) {
                continue;//标题行(省略)
            }
            HSSFRow row = sheet.getRow(j);
            for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {
                HSSFCell cell = row.getCell(k);
                System.out.println(cell.toString());
            }
        }
        return "redirect:/poi/index";
    }

    //生成user表excel
    @GetMapping(value = "/excel/getUser")
    @ResponseBody
    public String getUser(HttpServletResponse response) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("用户表");
        createTitle(workbook, sheet);
        List<SysUser> rows = new ArrayList<>();//userService.getAll();
        SysUser sysUser = new SysUser();
        sysUser.setId(1L);
        sysUser.setUsername("杨过");
        sysUser.setPassword("123");
        sysUser.setCreateTime(new Date());
        sysUser.setMobile("18829037917");
        rows.add(sysUser);
        //设置日期格式
        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        for (SysUser user : rows) {
            HSSFRow row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getUsername());
            row.createCell(2).setCellValue(user.getPassword());
            HSSFCell cell = row.createCell(3);
            cell.setCellValue(user.getCreateTime());
            cell.setCellStyle(style);
            rowNum++;
        }
        String fileName = "用户.xls";
        //生成excel文件
        buildExcelFile(fileName, workbook);
        //浏览器下载excel
        buildExcelDocument(fileName, workbook, response);
        return "download excel";
    }

    //创建表头
    private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(2, 12 * 256);
        sheet.setColumnWidth(3, 17 * 256);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("ID");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("用户名");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("密码");
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue("创建时间");
        cell.setCellStyle(style);
    }

    //生成excel文件
    protected void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    //浏览器下载excel
    protected void buildExcelDocument(String filename, HSSFWorkbook workbook, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
}

html页面

<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>hello</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
</head>
<body>
<h1>导入用户信息</h1>
<form action="/poi/excel/upload" method="post" enctype="multipart/form-data">
    <input name="file" type="file"/>
    <input type="submit"/>
</form>
<h2><a th:href="@{/sys/index}">返回主页</a></h2>
</body>
</html>

首页

<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>hello</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
</head>
<body>

<h1>用户</h1>
<a th:href="@{/sys/user/list}">用户列表</a>
<a th:href="@{/sys/role/list}">角色列表</a>
<a th:href="@{/sys/menu/list}">菜单列表</a>
<a th:href="@{/logout}">退出系统</a>
<li>
    <table border="1">
        <tr>
            <th>序号</th>
            <th>用户名</th>
            <th>邮箱</th>
            <th>电话</th>
            <th>角色</th>
        </tr>
        <tr th:each="user,userStat : ${list}">
            <th th:text="${userStat.index}+1">1</th>
            <td th:text="${user.username}">杨过</td>
            <td th:text="${user.email}">xionghao5@test.com.cn</td>
            <td th:text="${user.mobile}">18829037917</td>
            <td>
                <table border="1">
                    <tr>
                        <th>序号</th>
                        <th>角色名称</th>
                        <th>菜单</th>
                    </tr>
                    <tr th:each="role,roleStat : ${user.roleDtoList}">
                        <th th:text="${roleStat.index}+1">1</th>
                        <td th:text="${role.roleName}">管理员</td>
                        <td>
                            <table border="1">
                                <tr>
                                    <th>序号</th>
                                    <th>菜单名称</th>
                                    <th>菜单页面</th>
                                    <th>功能url</th>
                                </tr>
                                <tr th:each="menu,menuStat : ${role.menuDtoList}">
                                    <th th:text="${menuStat.index}+1">1</th>
                                    <td th:text="${menu.name}">添加</td>
                                    <td th:text="${menu.url}">/add</td>
                                    <td th:text="${menu.perms}">/add</td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                </table>
            </td>

        </tr>
    </table>

</li>
</body>
</html>