数据库表格数据导出到excel方法总结

写在前面

之前开发的时候需要实现excel表格文件的共享功能，并且是同一张大表格，不同的人只能看里面的一部分数据。由于数据每天更新，且每次都要手动筛选出给不同的人看的数据。很是繁琐。希望能有一个方便的方法来实现：维护这个数据大表的人只需要更新这张表，其他人就可以自己随时获取自己有权查看的那部分数据。

也许你有更好更简单的方法来实现。欢迎留言我。作为初学者写这篇文字只是记录一下我解决问题的过程。因为这里涉及到了Oracle数据库的操作，javaEE、jsp的编写，前台HTML、javascript、DOM 的操作，等方面。方便今后忘了的时候自己翻阅。

下面是正文。

方案一、使用数据库+plsqldev （.xlsx）

首先我想到的是将这个数据大表（一个.xlsx文件），转换成数据表，存入数据库中（我这里使用的是Oracle（这不是重点）），然后创建view，并设置列名为原 .xlsx 文件的列名。
维护数据的人只要在更新数据的时候使用 plsqldev 独立版（无需安装Oracle客户端即可使用的版本），执行下面的sql：

select * from TABLENAME order by ID for update;
-- 这里若不使用“order by ID”，有可能结果不是按照当初导入的顺序呈现的。

将自己的excel文件复制粘贴到数据库即可。然后想要查看的人查询自己的view，并使用 plsqldev 的导出到 .xlsx 功能即可导出。

优点：简单粗暴。使用者会使用 plsqldev 即可操作，而且实现了权限隔离，无法查看无权查看的数据的功能需求。

缺点：1.使用者不同的电脑环境 配置 plsqldev 时会出现各种情况，需要处理并且很棘手、2.用户体验差、3.使用时需要学习成本，学习如何使用 plsqldev 。

//无源码

方案二、使用数据库+jsp（.csv）

同样是使用数据库将表格数据迁移至数据库里，在服务器端使用 jsp 来实现 数据从数据库导出到 .csv 的功能。在 jsp 设置参数判断用户的权限，来导出对应是view数据。并呈现给用户。先说说这样做的优缺点。

优点：实现了权限隔离，无法查看无权查看的数据的功能需求。简单粗暴用户只需一个链接即可下载到自己所需的 .csv 文件。

缺点：1.呈现的为 .csv 文件，无发保存表格的样式，体验稍差。2.用户需要自己调节样式并另存为 .xlsx 才能保存表格的样式，然而毕竟会用户不会操作，造成体验差。

实现的源码如下：

<%@page import="com.tltable.jdbc.tltableDAO"%>
<%@ page language="java" pageEncoding="UTF-8"%>

<%!String sql = "select * from TABLENAME order by u_uid";
    // 这只是个demo，做测试。
    //可以通过传参实现执行不同的 sql 来查询不同的 table (view)

    String[][] value = tltableDAO.selectToArray(sql);
     // tltableDAO.selectToArray 函数执行 sql 并将返回结果转换为数组的格式。

    String csvStr = this.ArrayToCsvstr(value);

    public String ArrayToCsvstr(String[][] value) {
        String csvstr = "";
        for (int j = 0; j < value.length; j++) {
            if (value[j] == null) {
                csvstr = "获取值为空。";
                break;
            }
            for (int i = 0; i < value[j].length; i++) {
                csvstr += value[j][i] + ",";
            }
            csvstr += "\n";
        }
        return csvstr;
    }
%>
<%
    response.setHeader("Content-type", "application/octet-stream;charset=GBK");
    //之前没有加 charset=GBK ，默认的 UTF-8 下载下来用excel打开会是乱码。
    response.setHeader("Content-Disposition", "attachment; filename=\"my-data.csv\"");//生成的csv名为my-data.csv。也可以设置为动态的文件名。
    //String data = request.getParameter("csv_text");
    out.println(csvStr);//把数据写入到浏览器，以下载的方式
// 当然在输出之前可以在 csvStr 前添加一行表数据的列名。增加用户体验。
%>

tltableDAO.selectToArray 代码：

/**
     * 查询数据库，并转化成 String[][]
     * 
     * @param sql
     * @param args
     * @return
     */
    public static String[][] selectToArray(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultset = null;
        String[][] aaa = null;
        try {
            connection = JDBCTools.getConnection();     
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }       
            resultset = preparedStatement.executeQuery();
            aaa = tltableDAO.getColumnValues(resultset);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseDB(resultset, preparedStatement, connection);
        }

        return aaa;
    }
//这里涉及的 JDBCTools.java 我想网上会有很多的。我就不贴出来了

方案三、使用数据库+伪静态HTML（.csv 或 .xlsx）

这个方案我认为是最好的了。当然也是我花了很长时间才想到的。基于方案一 方案二的弊端，本方案做了尽可能的兼容并提升了用户体验。

在方案二中我们可以将数据库的数据转换成数组，本方案则是将数组推送到 web 前端。这样一来，可以实现

1、 以<table>的形式呈现出来

IE10+以及其他新版浏览器可以使用 handsontable 插件来实现大数据表格在 前端的完美呈现。 反正我觉得用户不需要。就没研究。感兴趣的可自己去官网查看。handsontable官网

用户可直接查看（我感觉没必要）。且客户端浏览器参差不齐，我公司基本使用的都是IE8。╮(╯▽╰)╭ IE8 太多效果没法呈现。

2、 使用 JavaScript 配合使用 IE 的 ActiveXObject 调用 Excel 导出数据到 Excel

前提是电脑上安装了 Office 的 excel 。源码如下：

<a href="#" onclick="downloaddata_ie('testtable')">IE导出</a>

function downloaddata_ie(tableid) {
        var curTbl = document.getElementById(tableid);
        var oXL = new ActiveXObject("Excel.Application");//创建AX对象excel 
        var oWB = oXL.Workbooks.Add();//获取workbook对象 
        var oSheet = oWB.ActiveSheet;//激活当前sheet 
        var Lenr = curTbl.rows.length;//取得表格行数 
        for (i = 0; i < Lenr; i++) {
            var Lenc = curTbl.rows(i).cells.length;//取得每行的列数 
            for (j = 0; j < Lenc; j++) {
                oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText;//赋值 
            }
        }
        oXL.Visible = true;//设置excel可见属性 
    }

或者下面的代码会自动在 excel 中弹出保存对话框，且使用的非赋值操作而是复制操作：

var idTmr;
function saveAsXlsx(tableid,filename) {// 整个表格拷贝到EXCEL中
    var curTbl = document.getElementById(tableid);
    var oXL = new ActiveXObject("Excel.Application");
    // 创建AX对象excel
    var oWB = oXL.Workbooks.Add();
    // 获取workbook对象
    var xlsheet = oWB.Worksheets(1);
    // 激活当前sheet
    var sel = document.body.createTextRange();
    sel.moveToElementText(curTbl);
    // 把表格中的内容移到TextRange中
    sel.select();
    // 全选TextRange中内容
    sel.execCommand("Copy");
    // 复制TextRange中内容
    xlsheet.Paste();
    // 粘贴到活动的EXCEL中
    oXL.Visible = true;
    // 设置excel可见属性

    try {
        var fname = oXL.Application.GetSaveAsFilename(filename+".xlsx",
                "Excel Spreadsheets (*.xlsx), *.xls11");
    } catch (e) {
        print("Nested catch caught " + e);
    } finally {
        oWB.SaveAs(fname);

        oWB.Close(savechanges = false);
        // xls.visible = false;
        oXL.Quit();
        oXL = null;
        // 结束excel进程，退出完成
        // window.setInterval("Cleanup();",1);
        idTmr = window.setInterval("Cleanup();", 1);

    }
}
function Cleanup() {
    window.clearInterval(idTmr);
    CollectGarbage();
}

3、 非 IE 浏览器 只能生成 .csv 文件供用户下载

可以继续使用 jsp 的方案，也可以使用 web 前端 的解决方案。

web 前端 解决方案 源码：

<a href="#" download="aaa.csv" onclick="downloaddata_notie(this)">非IE导出</a>

    function downloaddata_notie(aLink) {
        var str = "1,2,3,4,5\n9,8,7,5";//demo数据 
        str = encodeURIComponent(str);//防止乱码
        aLink.href = "data:text/csv;charset=utf-8,\ufeff" + str; 
        //  “\ufeff” 为 BOM 头
        aLink.click();
        return false;
    }

感谢您阅读到这里。其实写这篇文章的时候我还没有将这个功能实现，代码都是做的demo。

先写篇文章记录下来，不然我真的会忘。