使用EasyUI的项目中DataGrid 导出 Excel方法

最近做的项目有个功能，需要把DataGrid中的数据导出，下面通过代码一步一步的介绍;
首先在js中写一个扩展类，主要的功能是把DataGrid中的数据转换成Excel的XML格式

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

'''将datagrid中的数据转换成Excel的XML格式'''

$.extend($.fn.datagrid.methods, {

getExcelXml: function (jq, param) {

var worksheet = this.createWorksheet(jq, param);

//alert($(jq).datagrid('getColumnFields'));

var totalWidth = 0;

var cfs = $(jq).datagrid('getColumnFields');

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

totalWidth += $(jq).datagrid('getColumnOption', cfs[i]).width;

}

//var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);

return '<xml version="1.0" encoding="utf-8">' +

'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">' +

'<o:DocumentProperties><o:Title>' + param.title + '</o:Title></o:DocumentProperties>' +

'<ss:ExcelWorkbook>' +

'<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +

'<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +

'<ss:ProtectStructure>False</ss:ProtectStructure>' +

'<ss:ProtectWindows>False</ss:ProtectWindows>' +

'</ss:ExcelWorkbook>' +

'<ss:Styles>' +

'<ss:Style ss:ID="Default">' +

'<ss:Alignment ss:Vertical="Top" />' +

'<ss:Font ss:FontName="arial" ss:Size="10" />' +

'<ss:Borders>' +

'<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +

'<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +

'<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +

'<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +

'</ss:Borders>' +

'<ss:Interior />' +

'<ss:NumberFormat />' +

'<ss:Protection />' +

'</ss:Style>' +

'<ss:Style ss:ID="title">' +

'<ss:Borders />' +

'<ss:Font />' +

'<ss:Alignment ss:Vertical="Center" ss:Horizontal="Center" />' +

'<ss:NumberFormat ss:Format="@" />' +

'</ss:Style>' +

'<ss:Style ss:ID="headercell">' +

'<ss:Font ss:Bold="1" ss:Size="10" />' +

'<ss:Alignment ss:Horizontal="Center" />' +

'<ss:Interior ss:Pattern="Solid" />' +

'</ss:Style>' +

'<ss:Style ss:ID="even">' +

'<ss:Interior ss:Pattern="Solid" />' +

'</ss:Style>' +

'<ss:Style ss:Parent="even" ss:ID="evendate">' +

'<ss:NumberFormat ss:Format="yyyy-mm-dd" />' +

'</ss:Style>' +

'<ss:Style ss:Parent="even" ss:ID="evenint">' +

'<ss:NumberFormat ss:Format="0" />' +

'</ss:Style>' +

'<ss:Style ss:Parent="even" ss:ID="evenfloat">' +

'<ss:NumberFormat ss:Format="0.00" />' +

'</ss:Style>' +

'<ss:Style ss:ID="odd">' +

'<ss:Interior ss:Pattern="Solid" />' +

'</ss:Style>' +

'<ss:Style ss:Parent="odd" ss:ID="odddate">' +

'<ss:NumberFormat ss:Format="yyyy-mm-dd" />' +

'</ss:Style>' +

'<ss:Style ss:Parent="odd" ss:ID="oddint">' +

'<ss:NumberFormat ss:Format="0" />' +

'</ss:Style>' +

'<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +

'<ss:NumberFormat ss:Format="0.00" />' +

'</ss:Style>' +

'</ss:Styles>' +

worksheet.xml +

'</ss:Workbook>';

'''创建worksheet，将dataGrid的标题放到里面'''

createWorksheet: function (jq, param) {

'''Calculate cell data types and extra class names which affect formatting'''

var cellType = [];

var cellTypeClass = [];

'''var cm = this.getColumnModel();'''

var totalWidthInPixels = 0;

var colXml = '';

var headerXml = '';

var visibleColumnCountReduction = 0;

var cfs = $(jq).datagrid('getColumnFields');

var colCount = cfs.length;

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

if (cfs[i] != '') {

var w = $(jq).datagrid('getColumnOption', cfs[i]).width;

totalWidthInPixels += w;

if (cfs[i] === "") {

cellType.push("None");

cellTypeClass.push("");

++visibleColumnCountReduction;

}

else {

colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="130" />';

headerXml += '<ss:Cell ss:StyleID="headercell">' +

'<ss:Data ss:Type="String">' + $(jq).datagrid('getColumnOption', cfs[i]).title + '</ss:Data>' +

'<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';

cellType.push("String");

cellTypeClass.push("");

}

var visibleColumnCount = cellType.length - visibleColumnCountReduction;

var result = {

height: 9000,

width: Math.floor(totalWidthInPixels * 30) + 50

};

var rows = $(jq).datagrid('getRows');

// Generate worksheet header details.

var t = '<ss:Worksheet ss:Name="' + param.title + '">' +

'<ss:Names>' +

'<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + param.title + '\'!R1:R2" />' +

'</ss:Names>' +

'<ss:Table x:FullRows="1" x:FullColumns="1"' +

' ss:ExpandedColumnCount="' + (visibleColumnCount + 2) +

'" ss:ExpandedRowCount="' + (rows.length + 2) + '">' +

colXml +

'<ss:Row ss:AutoFitHeight="1">' +

headerXml +

'</ss:Row>';

//将DataGrid的数据循环加入到Excel中

//Generate the data rows from the data in the Store

//for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {

for (var i = 0, it = rows, l = it.length; i < l; i++) {

t += '<ss:Row>';

var cellClass = (i & 1) ? 'odd' : 'even';

r = it[i];

var k = 0;

for (var j = 0; j < colCount; j++) {

//if ((cm.getDataIndex(j) != '')

if (cfs[j] != '') {

//var v = r[cm.getDataIndex(j)];

var v = r[cfs[j]];

if (cellType[k] !== "None") {

t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';

if (cellType[k] == 'DateTime') {

t += v.format('Y-m-d');

} else {

t += v;

}

t += '</ss:Data></ss:Cell>';

}

k++;

}

t += '</ss:Row>';

}

result.xml = t + '</ss:Table>' +

'<x:WorksheetOptions>' +

'<x:PageSetup>' +

'<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +

'<x:Footer x:Data="Page &P of &N" x:Margin="0.5" />' +

'<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +

'</x:PageSetup>' +

'<x:FitToPage />' +

'<x:Print>' +

'<x:PrintErrors>Blank</x:PrintErrors>' +

'<x:FitWidth>1</x:FitWidth>' +

'<x:FitHeight>32767</x:FitHeight>' +

'<x:ValidPrinterInfo />' +

'<x:VerticalResolution>600</x:VerticalResolution>' +

'</x:Print>' +

'<x:Selected />' +

'<x:DoNotDisplayGridlines />' +

'<x:ProtectObjects>False</x:ProtectObjects>' +

'<x:ProtectScenarios>False</x:ProtectScenarios>' +

'</x:WorksheetOptions>' +

'</ss:Worksheet>';

//alert(result.xml);

return result;

}

});

然后再新建一个一般处理程序

public void ProcessRequest(HttpContext context) {

'''给要下载的Excel赋一个初始的文件名'''

string fn = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";

'''获取前台传回来的值，应该是一个XML文件的内容''''

string data = context.Request.Form["data"];

'''将相对路径转换为绝对路径，数据，编码方式'''

File.WriteAllText(context.Server.MapPath(fn), data, Encoding.UTF8);'''如果是gb2312的xml申明，第三个编码参数修改为Encoding.GetEncoding(936)'''

context.Response.Write(fn);'''返回文件名提供下载'''

}

最后在写js事件

'''状态数据导出Excel'''

function StateExporterExcel() {'''导出Excel文件'''

'''getExcelXML有一个JSON对象的配置，配置项看了下只有title配置，为excel文档的标题'''

var data = $('#dgState').datagrid('getExcelXml', { title: 'datagrid import to excel' }); '''获取datagrid数据对应的excel需要的xml格式的内容'''

'''用ajax发动到动态页动态写入xls文件中'''

var url = 'DataGridToExcel.ashx'; '''如果为asp注意修改后缀'''

$.ajax({

url: url, data: { data: data }, type: 'POST', dataType: 'text',

success: function (fn) {

'''alert('导出excel成功！');'''

'''执行下载操作'''

window.location = fn;

error: function (xhr) {

alert('动态页有问题\nstatus：' + xhr.status + '\nresponseText：' + xhr.responseText)

}

});

return false;

}

导出的弹出窗口，看着还可以

导出来的数据是这样的

发表回复 取消回复

发表回复取消回复