Excel与MySQL数据库的导入与导出

2022-01-03 01:36:44

应用场景

在许多时候，我们希望数据能够很好地在各个系统之间转移，同时便于非专业人员阅读，如果程序员一点点打字导出的话，不知道要打到什么时候，于是我们便采用日常工作中常用的Excel表格来作为媒介，将数据导入MySQL或将数据导出至Excel。

选择jar包

jxl && POI
jxl是一个开源的Java Excel API项目，通过Jxl，Java可以很方便的操作微软的Excel文档。除了Jxl之
外，还有Apache的一个POI项目，也可以操作Excel，两者相比之下：Jxl使用方便，但功能相对POI比较
弱,很多时候，一个软件应用程序需要生成Microsoft Excel文件格式的报告。有时，一个应用程序甚至希
望将Excel文件作为输入数据。例如，一个公司开发的应用程序将财务部门需要所有输出生成自己的
Excel。
Apache POI是一种流行的API，它允许程序员使用Java程序创建，修改和显示MS Office文件。这由
Apache软件基金会开发使用Java分布式设计或修改Microsoft Office文件的开源库。它包含类和方法对
用户输入数据或文件到MS Office文档进行解码。

使用jxl包导入导出

首先添加依赖

<dependency>
	<groupId>net.sourceforge.jexcelapi</groupId>
	<artifactId>jxl</artifactId>
	<version>2.6.12</version>
</dependency>

将MySQL数据导出

在jsp中创建一个按钮
Controller层

@Autowired
    @Qualifier("BookServiceImpl")
    private BookService bookService;

    //==========导出Excel===========
    @RequestMapping("/underwrite_export")
    @ResponseBody
    public void exportFile(HttpServletResponse response) throws Exception {
        // 1.文件下载响应头
        response.setHeader("Content-Disposition", "attachment;filename=underwrite.xls");
        // 2.响应到浏览器
        WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream());
        // 创建工作簿sheet
        WritableSheet sheet = workbook.createSheet("underwrite", 0);
        // 3.设置column名
        sheet.addCell(new Label(0, 0, "书籍号"));
        sheet.addCell(new Label(1, 0, "书籍名称"));
        sheet.addCell(new Label(2, 0, "书籍数量"));
        sheet.addCell(new Label(3, 0, "详情"));
        // 4.把核保的数据填充到工作簿中 service调用selectExport()查询数据库
        List<Books> list = bookService.queryAllBook();
        System.out.println(list.toArray());
        try{
            for (int i = 0, j = 1; i < list.size(); i++, j++) {
//                UnderwriteWait underwrite = list.get(i);
                Books underwrite = list.get(i);
                //System.out.println(underwrite);
                //设置列宽
                sheet.setColumnView(i, 16);
                //重新设置部分列宽
                sheet.setColumnView(3, 14);
                sheet.setColumnView(6, 10);
                sheet.setColumnView(7, 10);
                //设置行高
                sheet.setRowView(i, 350);
                //设置字体的attribute
                WritableFont font1=new WritableFont(WritableFont.createFont("楷体 _GB2312"), 12, WritableFont.NO_BOLD);
                WritableCellFormat format1=new WritableCellFormat(font1);
                System.out.println(underwrite.getBookID());
                sheet.addCell(new Label(0, j, underwrite.getBookID()+"",format1));
                sheet.addCell(new Label(1, j, underwrite.getBookName(),format1));
                sheet.addCell(new Label(2, j, underwrite.getBookCounts()+"",format1));
                sheet.addCell(new Label(3, j, underwrite.getDetail(),format1));
            }}catch (Exception e){
            e.printStackTrace();
        }
        // 5.写入数据
        workbook.write();
        // 6.关闭资源
        workbook.close();
    }

导入Excel至数据库

jsp中创建文件上传表单
Controller层

 @RequestMapping("/underwrite_import")
    public String importFile(MultipartFile file) throws Exception {
        // 1.获取用户上传的文件
        Workbook workbook = Workbook.getWorkbook(file.getInputStream());
        // 2.获取工作簿sheet
        Sheet sheet = workbook.getSheet(0);
        // 3.获取总行数
        int rows = sheet.getRows();
        for (int i = 1; i < rows; i++) {
            Books underwrite = new Books();
            underwrite.setBookID(Integer.parseInt(sheet.getCell(0, i).getContents()));
            underwrite.setBookName(sheet.getCell(1, i).getContents());
            underwrite.setBookCounts(Integer.parseInt(sheet.getCell(2, i).getContents()));
            underwrite.setDetail(sheet.getCell(3, i).getContents());
            // 4.添加到数据库中
            bookService.addBook(underwrite);
        }
        // 5.关闭资源
        workbook.close();
        return "redirect:/book/allBook";
    }

注：不要忘记在dao.xml中添加文件传输类

<bean id="multipartResolver"  class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
	<!-- 请求的编码格式，必须和jSP的pageEncoding属性一致，以便正确读取表单的内容，默认为ISO-8859-1 -->
	<property name="defaultEncoding" value="utf-8"/>
	<!-- 上传文件大小上限，单位为字节（10485760=10M） -->
	<property name="maxUploadSize" value="10485760"/>
	<property name="maxInMemorySize" value="40960"/>
</bean>

码农公寓