poi---Excel导入

最新推荐文章于 2023-08-06 18:35:56 发布

单眼皮女孩i

最新推荐文章于 2023-08-06 18:35:56 发布

阅读量108

点赞数

文章标签： java

本文链接：https://blog.csdn.net/weixin_44889526/article/details/120184664

版权

Excel导入书籍列表实例

jsp界面
下载模板
Excel导入
Excel导出
CSRF攻击

jsp界面

导入的jar包：
在这里插入图片描述

<head>
    <base href="<%=basePath%>">
    <title>Title</title>
    <link rel="stylesheet" href="css/bootstrap.min.css"/>
</head>
<body>
<div class = "container">
    <div>
        <h3>第一步：请下载导入模板</h3>
        <a class = "btn btn-info" href="/downloadTemp">下载模板</a>
    </div>

    <div>
        <h3>第二步：请上传数据</h3>
        <form action="/import" method="post" enctype="multipart/form-data">
            点击上传文件：<input type="file" name="temp" id=""><br>
            <input class = "btn btn-success" type="submit" value="上传">
        </form>
    </div>
</div>
</body>

在这里插入图片描述

下载模板

@WebServlet("/downloadTemp")
public class DownloadTemplateServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //下载模板
        response.setContentType("application/vnd.ms-excel");

        response.setHeader("Content-Disposition", "attachment; filename=\"temp.xls\"" );
        File file = new File("e:/temp/temp.xls");
        response.setContentLength(Long.valueOf(file.length()).intValue());

        InputStream is = new FileInputStream(file);
        OutputStream os = response.getOutputStream();
        IOUtils.copy(is,os);
    }
}

Excel导入

BookDao：

    private DBHelp dbHelp = new DBHelp();
    //添加
    public void save(Book book){
        String sql = "insert into book (id,bookname,author,publisher) values (?,?,?,?)";
        dbHelp.executeUpdate(sql,book.getId(),book.getBookname(),book.getAuthor(),book.getPublisher());
    }

BookService：

public void save(Book book){
        bookDao.save(book);
    }

ImportServlet：

@WebServlet("/import")
@MultipartConfig
public class ImportServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.getRequestDispatcher("import.jsp").forward(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        response.setHeader("xx","yyy");
        BookService bookService = new BookService();

        Part temp = request.getPart("temp");

        InputStream is = temp.getInputStream();

        Workbook wb = new HSSFWorkbook(is);
        Sheet sheet = wb.getSheetAt(0);

        //getLastRowNum() 获取最后一行行号
        for(int i = 1;i <= sheet.getLastRowNum();i++){
            Row row = sheet.getRow(i);
            Book book = new Book();

            short lastCellNum = row.getLastCellNum();
            for(int j = 0;j < lastCellNum;j++){
                Cell cell = row.getCell(j);

                if(j == 1){
                    int id = (int) cell.getNumericCellValue();
                    book.setId(id);
                }
                if(j == 2){
                    String bookName = cell.getStringCellValue();
                    book.setBookname(bookName);
                }
                if(j == 3){
                    String author = cell.getStringCellValue();
                    book.setAuthor(author);
                }
                if(j == 4){
                    String publisher = cell.getStringCellValue();
                    book.setPublisher(publisher);
                }
            }
            bookService.save(book);
        }
    }
}

Excel导出

ExportBooksServlet：

@WebServlet("/export")
public class ExportBooksServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        BookService bookService = new BookService();
        List<Book> bookList = bookService.findAll();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        Sheet sheet = hssfWorkbook.createSheet("书籍列表");

        Row row = sheet.createRow(0);
        Cell c0 = row.createCell(0);
        c0.setCellValue("序号");
        Cell c1 = row.createCell(1);
        c1.setCellValue("ID");
        Cell c2 = row.createCell(2);
        c2.setCellValue("书籍名称");
        Cell c3 = row.createCell(3);
        c3.setCellValue("作者");
        Cell c4 = row.createCell(4);
        c4.setCellValue("出版社");

        for (int i = 0; i < bookList.size(); i++) {
            Row r = sheet.createRow(i + 1);
            Cell cell0 = r.createCell(0);
            cell0.setCellValue(i+1);
            Cell cell1 = r.createCell(1);
            cell1.setCellValue(bookList.get(i).getId());
            Cell cell2 = r.createCell(2);
            cell2.setCellValue(bookList.get(i).getBookname());
            Cell cell3 = r.createCell(3);
            cell3.setCellValue(bookList.get(i).getAuthor());
            Cell cell4 = r.createCell(4);
            cell4.setCellValue(bookList.get(i).getPublisher());
        }

        resp.setContentType("application/vnd.ms-excel");
        String name = new String("书籍列表.xls".getBytes("utf-8"),"ISO-8859-1");
        resp.setHeader("Content-Disposition","attachment;filename = \"" + name + "\"");
        OutputStream os = resp.getOutputStream();
        hssfWorkbook.write(os);

        os.close();
        hssfWorkbook.close();
    }
}