本文目录一览：
js中的数据怎样导出到excel
简单的办法：使用js生成一个table，可以直接复制到excel中，网上有很多表格插件
复杂的办法：js传递数据到服务器，服务器生成表格后返回一个下载链接
js如何把table转成excel并下载
无非是这样的一个过程。先通过数据库查询出数据，放到List里，然后把这个List发往页面，然后遍历这个List把数据显示到这个表格里。 要想把数据导出到execel，很简单，把页面接受的这个List用jxl写到Execel就行了。具体将List导出到Execel的类如下：
package cms.dao;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import cms.utlis.DbUtils;
public class ToExecelByQuery {
//方法接受两个参数，一个是list，这个地方我用了泛型。另一个参数是HttpServletResponse response
public static void toExcelBy(ListAccessLog list,HttpServletResponse response) {
// 创建工作表
WritableWorkbook book=null;
response.reset();
// 创建工作流
OutputStream os =null;
try {
// 设置弹出对话框
response.setContentType("application/DOWLOAD");
// 设置工作表的标题
response.setHeader("Content-Disposition",
"attachment; filename=****.xls");//设置生成的文件名字
os = response.getOutputStream();
// 初始化工作表
book = Workbook.createWorkbook(os);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try{
//以下是我做的导出日志的一个模版
int nCount = list.size();
WritableSheet sheet = book.createSheet("访问日志", 0);
// 生成名为"商品信息"的工作表，参数0表示这是第一页
int nI = 1;
// 表字段名
sheet.addCell(new jxl.write.Label(0, 0, "日志编号"));
sheet.addCell(new jxl.write.Label(1, 0, "用户ID"));
sheet.addCell(new jxl.write.Label(2, 0, "用户姓名"));
sheet.addCell(new jxl.write.Label(3, 0, "访问日期"));
sheet.addCell(new jxl.write.Label(4, 0, "访问时间"));
sheet.addCell(new jxl.write.Label(5, 0, "名片ID"));
sheet.addCell(new jxl.write.Label(6, 0, "名片名称"));
sheet.addCell(new jxl.write.Label(7, 0, "创建日期"));
sheet.addCell(new jxl.write.Label(8, 0, "更新日期"));
// 将数据追加
for(int i=1;ilist.size();i++){
sheet.addCell(new jxl.write.Label(0, i, list.get(i).toString()));
sheet.addCell(new jxl.write.Label(1, i, list.get(i).getUserId()));
sheet.addCell(new jxl.write.Label(2, i, list.get(i).getUsername()));
sheet.addCell(new jxl.write.Label(3, i, list.get(i).getCrtTim()));
sheet.addCell(new jxl.write.Label(4, i, list.get(i).getComplTime()));
sheet.addCell(new jxl.write.Label(5, i, list.get(i).getCopId()));
sheet.addCell(new jxl.write.Label(6, i, list.get(i).getFirstname()));
sheet.addCell(new jxl.write.Label(7, i, list.get(i).getCrtTim()));
sheet.addCell(new jxl.write.Label(8, i, list.get(i).getUpdTim()));
}
book.write();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
如何用js导出数据Excel
(function ($) {
Date.prototype.Format = function (fmt) {
var o = {
"M+": this.getMonth() + 1, //月份
"d+": this.getDate(), //日
"h+": this.getHours(), //小时
"m+": this.getMinutes(), //分
"s+": this.getSeconds(), //秒
"q+": Math.floor((this.getMonth() + 3) / 3), //季度
"S": this.getMilliseconds() //毫秒
};
if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
for (var k in o)
if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
return fmt;
}
$.fn.ExportExcel = function (thread_id,tab_id, options) {
var defaults = {
height: '24px',
'line-height': '24px',
margin: '0 5px',
padding: '0 11px',
color: '#000',
background: '#02bafa',
border: '1px #26bbdb solid',
'border-radius': '3px',
/*color: #fff;*/
display: 'inline-block',
'text-decoration': 'none',
'font-size': '12px',
outline: 'none',
cursor: 'pointer'
}
var options = $.extend(defaults, options);
return this.each(function () {
var currentObject = $(this); //获取当前对象
currentObject.css(defaults);
currentObject.onmouseover = function () {
$(this).css('cursor', 'hand');
};
currentObject.click(function () {
//From:jsfiddle.net/h42y4ke2/16/
var tab_text = 'html xmlns:x="urn:schemas-microsoft-com:office:excel"';
tab_text = tab_text + 'headxmlx:ExcelWorkbookx:ExcelWorksheetsx:ExcelWorksheet';
tab_text = tab_text + 'x:NameTest Sheet/x:Name';
tab_text = tab_text + 'x:WorksheetOptionsx:Panes/x:Panes/x:WorksheetOptions/x:ExcelWorksheet';
tab_text = tab_text + '/x:ExcelWorksheets/x:ExcelWorkbook/xml/headbody';
tab_text = tab_text + "table border='1px'";
tab_text = tab_text + $('#' + thread_id).html();
tab_text = tab_text + $('#' + tab_id).html();
tab_text = tab_text + '/table/body/html';
var data_type = 'data:application/vnd.ms-excel';
var timeStr = new Date().Format('yyyyMMddhhmmss');
$(this).attr('href', data_type + ', ' + encodeURIComponent(tab_text));
$(this).attr('download', '日常数据报表' + timeStr + '.xls');
});
})
}
})(jQuery);
html
a href="#" id="export"导出/a
table
thead id="theadDate"
tr
th姓名/th
th班级/th
th年龄/th
/tr
/thead
tbody id="tbodyDate"
tr td张三/td
td高二/td
td18/td
/tr
tr
td李四/td
td高三/td
td20/td
/tr
/tbody
/table
script src="assets/javascripts/autotest/export-excel.js" /script!-- 引入js文件--
script type="text/javascript"
//导出 调用
$(function () {
$('#export').ExportExcel('theadDate','tbodyDate'); //tbodyDate为table的id,export为a标签。
});
/script
/html
js如何导出exel文件
一。导出Excel有两种：
1.
function ExportExcel(tableid){//读取表格中每个单元到EXCEL中
try
{
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可见属性
}
catch(e)
{
alert(e.message);
}
}
2：
function dataToExcel(tableid) {//整个表格拷贝到EXCEL中
var curTbl = document.getElementById(tableid);
var oXL;
try {
oXL = GetObject("", "Excel.Application");
}
catch (E) {
try {
oXL = new ActiveXObject("Excel.Application");
}
catch (E2) {
//alert("Please confirm:/n1.Microsoft Excel has been installed./n2.Internet Options=Security=Setting /"Enable unsafe ActiveX/"");
alert("请确认:/n1.机器上Excel已经安装./n2.Internet 选项=安全=Internet /"对没有标记为安全的ActiveX控件进行初始化和脚本运行，设定为启用/"");
return;
}
}
//创建AX对象excel
var oWB = oXL.Workbooks.Add();
//获取workbook对象
var oSheet = oWB.ActiveSheet;
//激活当前sheet
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
//把表格中的内容移到TextRange中
sel.select();
//全选TextRange中内容
sel.execCommand("Copy");
//复制TextRange中内容
oSheet.Paste();
//粘贴到活动的EXCEL中
oXL.Visible = true;
//设置excel可见属性
}
js 导出大数据到excel
完整代码：
//导出excelfunction tableToExcel(){
var arrSor = ["sorttable10","sorttable30","sorttable60","sorttable120"]
let str = "";
for (var k = 0; k arrSor.length; k++) {
var tab=document.getElementById(arrSor[k]);
var rows=tab.rows;
const jsonData = [];
for(var i=2;irows.length;i++){ //遍历表格的行
var json = {};
for(var j=0;jrows[i].cells.length;j++){ //遍历每行的列
json[(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML;
}
jsonData.push(json);
}
//列标题
let str1 = "trtd align='center' colspan='5'b最大"+arrSor[k].substring(9,arrSor[k].length)+"分钟雨量/b/td/tr";
let str2 = "tr align='center'th站点/thth站名/thth雨量最大值/thth降水时段/thth落入最大降水时段/th/tr";
//循环遍历，每行加入tr标签，每个单元格加td标签
for(let i = 0 ; i jsonData.length ; i++ ){
str2+='tr';
for(let item in jsonData[i]){
//增加/t为了不让表格显示科学计数法或者其他格式
var itemTem= jsonData[i][item];
if (itemTem == "暂无数据") {
str2+=`td colspan='5' align='center'${ itemTem + '/t'}/td`;
}else {
str2+=`td align='center'${ itemTem + '/t'}/td`;
}
}
str2+='/tr';
}
let str3 = "trtd colspan='5'/td/tr";
str += (str1 + str2 + str3);
}
let worksheet = '雨量最大值'
let uri = 'data:application/vnd.ms-excel;base64,';
//下载的表格模板数据
let template = `html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns=""headmeta charset='UTF-8'!--[if gte mso 9]xml
x:ExcelWorkbookx:ExcelWorksheetsx:ExcelWorksheet
x:Name${worksheet}/x:Namex:WorksheetOptionsx:DisplayGridlines//x:WorksheetOptions/x:ExcelWorksheet/x:ExcelWorksheets/x:ExcelWorkbook/xml![endif]--
style type="text/css"
table {border: 1px solid #000000;}
table tr td b {background:#FFFFFF;color:#3D3D3D;font-size:24px;border: 1px solid #000000;}
table th {background:#AEE1FE;color:#3D3D3D;font-size:20px;border: 1px solid #000000;}
table td {background:#FFFFFF;color:#3D3D3D;font-size:20px;border: 1px solid #000000;}/style
/headbodytable${str}/table/body/html`;
//下载模板
// window.location.href = uri + this.base64(template)
var link = document.createElement("a");
link.href = uri + this.base64(template);
link.download = "雨量最大值-" +new Date().format("yyyy年MM月dd日 h时")+ ".xls";
link.style = "visibility:hidden";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);}//输出base64编码function base64 (template) {
return window.btoa(unescape(encodeURIComponent(template))) }
解析：
遍历取出表，顺序是行从上往下，列从左往右，将数据存进数组，下面再拼接成表。
image.png
参考文章：
第一种方法（大量数据导出）
//导出excelfunction tableToExcel() {
var arrSor = ["sorttable10","sorttable30","sorttable60","sorttable120"]
let str = "";
for (var k = 0; k arrSor.length; k++) {
var tab=document.getElementById(arrSor[k]);
var rows=tab.rows;
const jsonData = [];
for(var i=2;irows.length;i++){ //遍历表格的行
var json = {};
for(var j=0;jrows[i].cells.length;j++){ //遍历每行的列
if (rows[i].cells[j].outerHTML.indexOf("rgb(255, 255, 0)") != -1) {
json["yellow"+(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML;
}else {
json[(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML;
}
}
jsonData.push(json);
}
//列标题
let str1 = "trtd colspan='5' align='center' style='background-color:#FFFFFF;font-size:24px;border: 1px solid #000000;'b最大"
+arrSor[k].substring(9,arrSor[k].length)+"分钟雨量/b/td/tr";
let str2 = "tr" +
"th style='background-color:#AEE1FE;font-size:22px;border: 1px solid #000000;' align='center'站点/th" +
"th style='background-color:#AEE1FE;font-size:22px;border: 1px solid #000000;' align='center'站名/th" +
"th style='background-color:#AEE1FE;font-size:22px;border: 1px solid #000000;' align='center'雨量最大值/th" +
"th style='background-color:#AEE1FE;font-size:22px;border: 1px solid #000000;' align='center'降水时段/th" +
"th style='background-color:#AEE1FE;font-size:22px;border: 1px solid #000000;' align='center'落入最大降水时段/th/tr";
//循环遍历，每行加入tr标签，每个单元格加td标签
for(let i = 0 ; i jsonData.length ; i++ ){
str2+="tr align='center'";
for(let item in jsonData[i]){
if (item.indexOf("yellow") != -1) {//取列数等于3
//增加/t为了不让表格显示科学计数法或者其他格式
var itemTem= jsonData[i][item];
if (itemTem == "暂无数据") {
str2+=`td colspan='5' style='background-color:#FFFF00;font-size:22px;border: 1px solid #000000;' ${ itemTem + '/t'}/td`;
}else {
str2+=`td style='background-color:#FFFF00;font-size:22px;border: 1px solid #000000;' ${ itemTem + '/t'}/td`;
}
}else {
//增加/t为了不让表格显示科学计数法或者其他格式
var itemTem= jsonData[i][item];
if (itemTem == "暂无数据") {
str2+=`td colspan='5' style='background-color:#FFFFFF;font-size:22px;border: 1px solid #000000;' ${ itemTem + '/t'}/td`;
}else {
str2+=`td style='background-color:#FFFFFF;font-size:22px;border: 1px solid #000000;' ${ itemTem + '/t'}/td`;
}
}
}
str2+='/tr';
}
let str3 = "trtd colspan='5' style='background-color:#FFFFFF;border: 1px solid #000000;'/td/tr";
str += (str1 + str2 + str3);
}
var tableHtml="htmlheadmeta charset='UTF-8'/headbodytable"+str+"/body/html"
var excelBlob = new Blob([tableHtml], {type: 'application/vnd.ms-excel'});
var fileName = "雨量最大值-"+new Date().format("yyyy年MM月dd日 h时")+".xls";
if(isIE()){
window.navigator.msSaveOrOpenBlob(excelBlob,fileName);
}else{
var oa = document.createElement('a');
oa.href = URL.createObjectURL(excelBlob);
oa.download = fileName;
document.body.appendChild(oa);
oa.click();
}} //判断是否IE浏览器function isIE() {
if (!!window.ActiveXObject || "ActiveXObject" in window) {
return true;
} else {
return false;
}}
参考文章：
js如何导出excel
简单的办法：使用js生成一个table，可以直接复制到excel中，网上有很多表格插件
复杂的办法：js传递数据到服务器，服务器生成表格后返回一个下载链接