导出grid panel 数据到Excel
来源:互联网 发布:大连知润科技有限公司 编辑:程序博客网 时间:2024/05/16 10:23
前段时间有个项目需要做导出Excel的功能,后来在网上找到一些文章,但都是1.0版本的,自己改成了2.0版本
前台脚本:
/*||fomrat: 导出格式(excel, csv, xml)||grid:要导出的Grid Panel*/function doExportData(format, grid) { var vExportContent = getGridXml(grid, false); if (!Ext.fly('frmDummy')) { var frm = document.createElement('form'); frm.id = 'frmDummy'; frm.name = 'frmDummy'; frm.className = 'x-hidden'; document.body.appendChild(frm); } Ext.Ajax.useDefaultHeader = false; Ext.Ajax.request({ url: '/Handlers/ExportData.ashx', method: 'POST', form: Ext.fly('frmDummy'), callback: function (o, s, r) { alert(r.responseText); }, isUpload: true, params: { exportXml: Ext.util.Format.htmlEncode(vExportContent), exportFormat: format } });}/*||includeHidden: 包含隐藏列||grid:要导出的Grid Panel*/function getGridXml(grid, includeHidden) { var cellType = []; var cellTypeClass = []; var columns = grid.columns; var totalWidthInPixels = 0; var headerXml = ''; var dataXml = ''; var visibleColumnCountReduction = 0; var colCount = columns.length; headerXml = "<Columns>"; for (var i = 0; i < colCount; i++) { var col = columns[i]; if ((col.dataIndex != '') && (includeHidden || !col.isHidden())) { var w = col.getWidth(); totalWidthInPixels += w; if (col.text === "") { cellType.push("None"); cellTypeClass.push(""); ++visibleColumnCountReduction; } else { var dataType = ''; var fld = null; var fields = grid.store.model.getFields(); for (var k = 0, len = fields.length; k < len; k++) { if (col.dataIndex == fields[k].name) { fld = fields[k]; break; } } if (fld != null) { switch (fld.type.type) { case "int": dataType = "Integer"; cellType.push("Number"); cellTypeClass.push("int"); break; case "float": dataType = "Float"; cellType.push("Number"); cellTypeClass.push("float"); break; case "bool": case "boolean": dataType = "String"; cellType.push("String"); cellTypeClass.push(""); break; case "date": dataType = "DateTime"; cellType.push("DateTime"); cellTypeClass.push("date"); break; default: dataType = "String"; cellType.push("String"); cellTypeClass.push(""); break; } } else { dataType = "String"; cellType.push("String"); cellTypeClass.push(""); } headerXml += '<Column Name="' + escapeXmlName(col.dataIndex) + '" Header="' + escapeXmlName(col.text) + '" DataType="' + dataType + '" />'; } } } headerXml += "</Columns>"; var visibleColumnCount = cellType.length - visibleColumnCountReduction; dataXml = '<Records>'; // Generate the data rows from the data in the Store for (var i = 0, it = grid.store.data.items, l = it.length; i < l; i++) { dataXml += '<Record>'; var cellClass = (i & 1) ? 'odd' : 'even'; r = it[i].data; var k = 0; for (var j = 0; j < colCount; j++) { var col = columns[j]; if ((col.dataIndex != '') && (includeHidden || !col.isHidden())) { var v = r[col.dataIndex]; if (v == null) v = ""; if (cellType[k] !== "None") { dataXml += '<' + escapeXmlName(col.dataIndex) + '>'; if (cellType[k] == 'DateTime') { dataXml += v.format('Y-m-d H:i:s'); } else { dataXml += escapeXml(v); } dataXml += '</' + escapeXmlName(col.dataIndex) + '>'; } k++; } } dataXml += '</Record>'; } dataXml += '</Records>'; var resultXml = '<ExportData>' + headerXml + dataXml + '</ExportData>'; return resultXml;}
后台ashx:
/// <summary> /// Summary description for ExportExcel /// </summary> public class ExportData : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.AddHeader("Expires", "0"); context.Response.AddHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); context.Response.ContentType = "application/force-download"; context.Response.Charset="UTF-8"; string exportXml = context.Request["exportXml"]; string exportFormat = context.Request["exportFormat"]; string exportData = getExcelData(HttpUtility.HtmlDecode(exportXml)); switch (exportFormat.ToUpper()) { case "EXCEL": exportData = getExcelData(HttpUtility.HtmlDecode(exportXml)); context.Response.AddHeader("Content-type", "application/vnd.ms-excel"); context.Response.AddHeader("Content-Disposition", "attachment;filename=\"export.xls\""); break; case "CSV": exportData = getCsvData(HttpUtility.HtmlDecode(exportXml)); context.Response.AddHeader("Content-type", "text/csv"); context.Response.AddHeader("Content-Disposition", "attachment;filename=\"export.csv\""); break; case "XML": exportData = getXmlData(HttpUtility.HtmlDecode(exportXml)); context.Response.AddHeader("Content-type", "text/xml"); context.Response.AddHeader("Content-Disposition", "attachment;filename=\"export.xml\""); break; } context.Response.Write(exportData); } public string getExcelData(string exportXml) { StringBuilder sbExportData = new StringBuilder(); XElement xeExport = XElement.Parse(exportXml); var qryExportDefinition = from row in xeExport.Descendants("Column") select new { Name = row.Attribute("Name").Value, Header = row.Attribute("Header").Value, DataType = row.Attribute("DataType").Value }; sbExportData.Append("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>"); sbExportData.Append("<style>\n<!--"); sbExportData.Append(".exp_table { font-family:Tahoma,sans-serif,Verdana,Arial; font-size:10pt; }"); sbExportData.Append(".exp_columnheader { font-family:Tahoma,sans-serif,Verdana,Arial; text-align:center; background:silver; height:28px}"); sbExportData.Append(".exp_textdata { mso-number-format:\"\\@\"; height:25px}"); sbExportData.Append(".exp_datedata { mso-number-format:\"yyyy\\/mm\\/dd\"; text-align:center; height:25px}"); sbExportData.Append("td { height:28px }"); sbExportData.Append("-->\n</style>"); sbExportData.Append("<table cellpadding=0 cellspacing=0 border=1 class=\"exp_table\">"); string[] css = new string[qryExportDefinition.Count()]; int i = 0; sbExportData.Append("<tr>"); foreach (var exportDefinition in qryExportDefinition) { switch (exportDefinition.DataType) { case "String": css[i] = "exp_textdata"; break; case "DateTime": css[i] = "exp_datedata"; break; default: css[i] = "exp_textdata"; break; } sbExportData.AppendFormat("\t<td class=\"exp_columnheader\">{0}</td>", exportDefinition.Header); i++; } sbExportData.Append("\t</tr>"); var qryDataRows = from row in xeExport.Descendants("Record") select row; foreach (var dataRow in qryDataRows) { sbExportData.Append("<tr>"); i = 0; foreach (var exportDefinition in qryExportDefinition) { string val = dataRow.Element(exportDefinition.Name).Value; switch (exportDefinition.DataType) { case "String": css[i] = "exp_textdata"; break; case "DateTime": css[i] = "exp_datedata"; break; default: css[i] = String.Empty; break; } sbExportData.AppendFormat("\t<td class=\"{0}\">{1}</td>", css[i], val); i++; } sbExportData.Append("</tr>"); } sbExportData.Append("</table>"); return sbExportData.ToString(); } public string getCsvData(string exportXml) { StringBuilder sbExportData = new StringBuilder(); XElement xeExport = XElement.Parse(exportXml); var qryExportDefinition = from row in xeExport.Descendants("Column") select new { Name = row.Attribute("Name").Value, Header = row.Attribute("Header").Value, DataType = row.Attribute("DataType").Value }; string[] css = new string[qryExportDefinition.Count()]; //string[] header = new string[qryExportDefinition.Count()]; int i = 0; foreach (var exportDefinition in qryExportDefinition) { sbExportData.Append(CsvFomratter(exportDefinition.Header)); i++; if (i < qryExportDefinition.Count()) sbExportData.Append(","); } sbExportData.Append("\n"); var qryDataRows = from row in xeExport.Descendants("Record") select row; foreach (var dataRow in qryDataRows) { i = 0; foreach (var exportDefinition in qryExportDefinition) { string val = dataRow.Element(exportDefinition.Name).Value; sbExportData.AppendFormat(CsvFomratter(val)); i++; if (i < qryExportDefinition.Count()) sbExportData.Append(","); } sbExportData.Append("\n"); } return sbExportData.ToString(); } public string getXmlData(string exportXml) { StringBuilder sbExportData = new StringBuilder(); XElement xeExport = XElement.Parse(exportXml); var qryExportDefinition = from row in xeExport.Descendants("Column") select new { Name = row.Attribute("Name").Value, Header = row.Attribute("Header").Value, DataType = row.Attribute("DataType").Value }; sbExportData.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n"); sbExportData.Append("<Records>\n"); var qryDataRows = from row in xeExport.Descendants("Record") select row; foreach (var dataRow in qryDataRows) { sbExportData.Append("\t<Record>\n"); foreach (var exportDefinition in qryExportDefinition) { string val = dataRow.Element(exportDefinition.Name).Value; sbExportData.AppendFormat("\t\t<{0}>{1}</{0}>\n", XmlFomratter(exportDefinition.Header), XmlFomratter(val)); } sbExportData.Append("\t</Record>\n"); } sbExportData.Append("</Records>\n"); return sbExportData.ToString(); } private string XmlFomratter(string source) { XmlDocument doc = new XmlDocument(); var node = doc.CreateElement("root"); node.InnerText = source; return node.InnerXml; } private string CsvFomratter(string source) { if (source.IndexOf(',') > -1) return "\"" + source + "\""; else return source; } public bool IsReusable { get { return false; } } }
- 导出grid panel 数据到Excel
- 导出Ext.grid.Panel到excel
- Ext Grid数据导出到excel
- ASP.NET Grid数据导出到Excel
- 将GRID中数据导出到 EXCEL文件中
- delphi中GRID数据导出到EXCEL的操作
- TOAD 导出data grid到excel
- ExtJs Grid导出到Excel(修正版)
- ExtJS4.x Grid导出到Excel(详解)
- silverlight datagrid/grid导出到excel 方法
- ExtJs Grid导出到Excel(修正版)
- 数据导出到Excel
- 数据导出到Excel
- 导出数据到EXCEL
- 导出数据到Excel
- 导出数据到EXCEL
- 导出数据到excel
- 导出数据到Excel
- Linux 安装goagent
- .NET delegate 委托的使用
- sqlite3使用简介
- Java编程中“为了性能”尽量要做到的一些地方
- 堆和栈的区别 (转贴)
- 导出grid panel 数据到Excel
- CRYPTO学习入门
- STL学习之queue适配器
- oracle远程登录解决办法
- C#中Xml的Xpath
- CString 转CTime 的成功失败
- Linux 地址
- oninput,onpropertychange,onchange的用法和区别
- VisionMobile:虚拟助手(VA)- Siri背后的前沿UI技术(四)