apache poi处理excel表格

最新推荐文章于 2024-06-26 19:21:55 发布

转载最新推荐文章于 2024-06-26 19:21:55 发布 · 137 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/gwlCode/blog/3066897

本文介绍如何利用Apache POI库在Java中实现Excel文件的上传、读取和保存到数据库，同时展示了如何从数据库中读取数据并导出到Excel文件。文章通过具体的代码示例，详细讲解了处理Excel表格数据的方法。

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

导入 apache poi jar包

导入

excel文件

jsp上传excel文件

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery-1.8.3.js"></script>
    <script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery.ocupload-1.1.2.js"></script>
    <title>Title</title>

    <script type="text/javascript">
        $(function () {
            $("#button-import").upload({
                action: "${pageContext.request.contextPath}/regionAction_importExcel",
                name: "excelFile"
            })
        });
    </script>

</head>
<body>

<input type="button" id="uploadbtn" value="一键上传">

</body>
</html>

读取保存数据库

public class RegionAction extends BaseAction<Region> {

    @Autowired
    private RegionService regionService;

    private File excelFile;

    public RegionAction() throws IllegalAccessException, InstantiationException {
    }

    public void setExcelFile(File excelFile) {
        this.excelFile = excelFile;
    }

    public String importExcel() throws IOException {

        //System.out.println(excelFile.getAbsolutePath());

        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelFile));

        HSSFSheet sheet = workbook.getSheetAt(0);
        List<Region> regions = new ArrayList<>();

        for (Row row : sheet) {

            String id = row.getCell(0).getStringCellValue();
            String province = row.getCell(1).getStringCellValue();
            String city = row.getCell(2).getStringCellValue();
            String district = row.getCell(3).getStringCellValue();
            String postcode = row.getCell(4).getStringCellValue();

            Region region = new Region(id, province, city, district, postcode);
            regions.add(region);
        }

        regions.remove(0);
        //保存数据库
        regionService.saveAll(regions);
        return SUCCESS;
    }
}

导出

    public void exportExcel() throws IOException {

        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFSheet sheet = workbook.createSheet("用户表1");

        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("姓名");
        row.createCell(2).setCellValue("密码");
        row.createCell(3).setCellValue("电话");

        List<User> users = userService.getAll();
        for (User user : users) {
            row = sheet.createRow(sheet.getLastRowNum() + 1);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getUsername());
            row.createCell(2).setCellValue(user.getPassword());
            row.createCell(3).setCellValue(user.getTel());
        }

        String fileName = URLEncoder.encode("全部用户.xls", "utf-8");

        HttpServletResponse response = ServletActionContext.getResponse();
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        String contentType = ServletActionContext.getServletContext().getMimeType(fileName);
        //response.setContentType("application/octet-stream");
        response.setContentType(contentType);

        OutputStream outputStream = response.getOutputStream();

        workbook.write(outputStream);
    }

转载于:https://my.oschina.net/gwlCode/blog/3066897