使用jqGrid实现完整导出Excel,并自动控制多个列表导出Sheet个数

```javascript

$.fn.jqgridToExcel = function(fileName, fdate) {

var jqgrid = this;

var title = jqgrid.getGridParam('colNames');

var param = jqgrid.getGridParam('postData');

var excel = new ActiveXObject("Excel.Application");

//控制execl是否打开 true 打开，false 不打开

excel.Visible = false;

var workBook = excel.Workbooks.Add();

// workBook.ActiveSheet 等同 workBook.Worksheets(1)

var sheet = workBook.Worksheets(1);

sheet.name = "第【" + (1 + jqgridExcelListPublic(workBook, jqgrid, fileName, fdate, sheet, param)) + "】层";

//调用公共方法

var rowNum = 0;

jqgridExcelListPublic(workBook, jqgrid, fileName, fdate, sheet, param, rowNum);

/*内容设置结束*/

excel.UserControl = true;

fileName = excel.Application.GetSaveAsFilename(fileName + ".xls", "Excel Spreadshsheets (*.xls),*.xls,(*.xlsx),*.xlsx");

if (fileName !== false) sheet.SaveAs(fileName);

excel.Quit();

return jqgrid;

};

```

以下是重构后的代码：

```javascript

// 根据提供的文件名、日期、url数组、显示类型数组和查询参数数组，将jqGrid导出为Excel文件

// @author wxy

function jqgridToExcelList(fileName, fdate, urlArr, showTypeArr, paramArr) {

var idTmr = null;

$.fn.jqgridToExcelList = function (fileName, fdate, urlArr, showTypeArr, paramArr) {

// 获取jqGrid的参数

var jqgrid = this;

var param = jqgrid.getGridParam('postData');

// 创建一个新的Excel工作簿

var excel = new ActiveXObject("Excel.Application");

excel.Visible = false;

var workBook = excel.Workbooks.Add();

var sheet = null;

var sheetNames = ["sheet4", "sheet1", "sheet2", "sheet3"]; // 需要生成的sheet顺序

for (var i = 0; i < sheetNames.length; i++) {

if (showTypeArr[i] === "新建表格") {

sheet = workBook.CreateSheet(sheetNames[i]); // 创建新的sheet

} else if (showTypeArr[i] === "追加数据") {

sheet = workBook.Worksheets(sheetNames[i]); // 追加数据的sheet

} else {

sheet = workBook.Worksheets(sheetNames[i]); // 不作任何处理，直接跳过

}

}

// 填充数据到相应的sheet中(此处省略具体实现)

// ...

// 保存Excel文件并关闭

workBook.SaveAs(fileName + "_" + fdate + ".xls");

workBook.Close();

excel.Quit();

}

}

```

以下是重构后的代码：

```javascript

var rowNum = 0; // 数据显示行

var count = 0; // 当前sheet显示第几次url返回的数据，用于计算当前是第几个sheet在生成数据

// 获取到当前new出excel的sheet数量，经测试win7默认3个sheet,xp默认1个sheet

var len = workBook.Sheets.count;

// 计算生成当前导出excel所需的sheet,如果当前new的excel中sheet不够用就需要生成

for (var j = 0; j < urlArr.length; j++) {

if (j >= len && showTypeArr[j]) {

workBook.Sheets.add();

}

}

// 给excel绑定数据

for (var i = 0; i < urlArr.length; i++) {

var sheetNum = i + 1;

if (i != 0) {

param = paramArr[i];

}

// showTypeArr值为true:表示新的sheet,false表示使用上一个sheet并且要记录count

if (showTypeArr[i]) {

sheetNum = sheetNum - count;

sheet = workBook.Worksheets(sheetNum);

sheet.name = "第【" + sheetNum + "】层";

rowNum = 0;

} else {

count++;

}

jqgrid = urlArr[i];

rowNum = jqgridExcelListPublic(workBook, jqgrid, fileName, fdate, sheet, param, rowNum);

}

```

/*内容设置结束*/

excel.UserControl = true;

fileName = excel.Application.GetSaveAsFilename(fileName + '.xls');

if (fileName !== false) {

sheet.SaveAs(fileName);

}

excel.Quit();

workBook = null;

excel = null;

activeSheet = null;

idTmr = window.setInterval('Cleanup()', 1);

return jqgrid;

}

function Cleanup() {

window.clearInterval(idTmr);

CollectGarbage();

}

```javascriptvar data = back;

if (typeof back == "string") {

data = $.parseJSON(back);

}

var re = / /g; // 正则表达式，匹配所有空格

var viewValue = null;

try {

var colModel = jqgrid.getGridParam('colModel');

var title = jqgrid.getGridParam('colNames');

/*列头设置开始*/

var start = 3;

if (rowNum !== 0) {

start = rowNum + 3;

}

var col = 1;

var tiCon = title.length;

for (var i = 0; i < tiCon; i++) {

//列出不隐藏的列头项

if (title[i].length !== 0 && title[i].indexOf("

var cell = sheet.getCell(start, col);

var alignment = Xlsx.Styles.Alignment["center"]; //居中显示

var fontBold = Xlsx.Styles.Font['bold']; //设置粗体

var bordersLineStyle = Xlsx.Styles.Border['thin']; //边框样式

var borderColorIndex = Xlsx.Styles.Color['gray25']; //单元格边框颜色

Xlsx.utils.aoa_to_sheet([title[i]], sheet, start, col); //写入标题到Excel表格中

cell.HorizontalAlignment = alignment;

cell.Font.Bold = fontBold;

cell.Borders.LineStyle = bordersLineStyle;

cell.Borders.ColorIndex = borderColorIndex;

} else {

col++; //跳过隐藏的列头项

}

}

} catch (error) {} finally {}

```

以下是重构后的代码：

```csharp

sheet.Cells(start, col).Interior.ColorIndex = 2; //单元格底色

col++;

}

}

/*列头设置结束*/

if (rowNum == 0) {

/*标题开始*/

sheet.Range("A1", sheet.Cells(1, col - 1)).MergeCells = true; //合并标题单元格

sheet.Cells(1, 1).HorizontalAlignment = 3; //居中显示

sheet.Cells(1, 1).Font.Bold = true; //设置粗体

sheet.Cells(1, 1).Font.Size = 15; //字体大小

sheet.Cells(1, 1).Font.ColorIndex = 10; //字体颜色

sheet.Cells(1, 1).Value = fileName; //设置标题内容

/*标题结束*/

sheet.Range("A2", sheet.Cells(2, col - 1)).MergeCells = true; //合并时间单元格

sheet.Cells(2, 1).HorizontalAlignment = 2; //居左显示

sheet.Cells(2, 1).Value = fdate; //设置时间内容

}

/*内容设置开始*/

var row = data.rows;

var count = row.length;

rowNums = start + count;

var colModellen = colModel.length;

for (var i = 0; i < count; i++) {

var cocl = 1; //定义变量cocl,但未在后续代码中使用，可以删除或注释掉此行。

```

```javascriptfor (var j = 0; j < colModel.length; j++) {

// 列出每列内容

if (!colModel[j].hidden && colModel[j].index !== undefined) {

sheet.Cells(start + 1 + i, cocl).HorizontalAlignment = 3; // 居中显示

sheet.Cells(start + 1 + i, cocl).Borders.LineStyle = 1; // 边框样式

sheet.Cells(start + 1 + i, cocl).Borders.ColorIndex = 10; // 单元格边框颜色

sheet.Cells(start + 1 + i, cocl).NumberFormat = "@"; // 将单元置为文本，避免非数字列被自动变成科学计数法和丢失前缀的0

viewValue = $(row[i]).attr(colModel[j].name);

// 如果定义了格式化方法

if (colModel[j].formatter !== undefined) {

// 有格式化，则需要格式化后显示

viewValue = colModel[j].formatter(viewValue);

// 控制有连接的列

if (viewValue.indexOf("href", 1) !== -1) {

var s = viewValue.indexOf(">", 1);

var e = viewValue.indexOf("<", 2);

var strValue = viewValue.substring(s + 1, e);

viewValue = strValue;

}

}

}

}

```

这段代码的功能是将数据填充到Excel表格中。首先判断viewValue是否为空，如果不为空，则替换掉其中的特定字符。接着判断viewValue是否为undefined,如果是，则将其赋值为空字符串。然后将处理后的viewValue前面加上空格，以便处理日期格式的数据。最后将处理后的viewValue写入到对应的单元格中，并更新列数。

重构后的代码如下：

```javascript

function fillExcelData(viewValue, start, i, cocl) {

try {

if (viewValue != null) {

viewValue = (viewValue.toString()).replace(re, "");

}

if (viewValue === undefined) {

viewValue = "";

}

// 为viewValue添加前导空格以处理日期数据格式

sheet.Cells(start + 1 + i, cocl).value = ` ${viewValue}`;

cocl++;

} catch (e) {}

}

sheet.Columns.AutoFit();

return rowNums;

```