GridPanel数据导出成xls

花了些时间整理下数据导出成xls，方便以后调用。该方法是在Extjs实现的，功能都可以调用。

数据封闭成json：escape(Ext.encode(record))

json解除为object数组：context.Server.UrlDecode(context.Request.Params["staff"])

//js:开头按钮事件

{ secureTag: '1', text: '导出', iconCls: 'TAIconexport', scale: 'medium', handler: this.001ConPanel_export, scope: this }

//事件方法

 1 001ConPanel_export: function () {
 2         var rec = this._dataGrid.getStore().getRange();
 3         if (rec.length > 0) {
 4             var record = new Array();
 5             for (var i = 0; i < rec.length; i++) {
 6                 record.push(rec[i].data);
 7             }
 8             if (!Ext.isIE6 || Ext.isIE7 || Ext.isIE8 || Ext.isIE11 || Ext.isSafari || Ext.isSafari2 || Ext.isSafari3) {
 9                 var myExport = Ext.get('myExport');
10                 var myUrl = '001.ashx?action=export';
11                 if (!myExport) {
12                     myExport = Ext.DomHelper.append(Ext.getBody(), {
13                         tag: 'form',
14                         method: 'post',
15                         id: 'myExport',
16                         action: '',
17                         target: '_blank',
18                         name: 'myExport',
19                         cls: 'x-hidden',
20                         cn: [
21                                 { tag: 'input', name: 'staff', id: 'staff', type: 'hidden' }
22                             ]
23                     }, true);
24                 }
25                 myExport.dom.action = myUrl;
26                 myExport.child('#staff').set({ value: escape(Ext.encode(record)) });
27                 myExport.dom.submit();
28             } else {
29                 Ext.Msg.alert('提示','浏览器版本不对，换一个吧。');
30             }
31         }
32     }

//执行后台001.ashx?action=export

 1         public void export(HttpContext context) 
 2         {
 3             using ( var obj = new dbCSR_Activity())
 4             {
 6                 string strStaff = context.Server.UrlDecode(context.Request.Params["staff"]);
 7                 var lstStaff = strStaff.JsonStrToObject<List<CActivity>>();
 8 
 9                 XmlDocument xmld = new XmlDocument();
10                 var dtStaff = new DataTable();
11                 dtStaff = ConvertToDatatable(lstStaff);
12                 
13                 var dtFamily = new DataTable();
14                 dtFamily = ConvertToDatatable(lstStaff);
15 
16                 var dsAll = new DataSet();
17                 dsAll.Tables.Add(dtStaff);
18                 dsAll.Tables[0].TableName = "Staff";
19 
20                 dsAll.Tables.Add(dtFamily);
21                 dsAll.Tables[1].TableName = "Family123";
22 
23                 var strXml = dsAll.GetXml();
24                 xmld.LoadXml(strXml);
25                 context.Response.Clear();
26                 context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", HttpUtility.UrlEncode(DateTime.Now.ToShortDateString(), System.Text.Encoding.UTF8)));
27                 context.Response.ContentEncoding = Encoding.GetEncoding("gb2312");
28                 context.Response.ContentType = "application/vnd.ms-excel";
29                 XslCompiledTransform xtExcel = new XslCompiledTransform();
30                 xtExcel.Load(context.Server.MapPath("xslt/applyActivity.xslt"));
31                 xtExcel.Transform(xmld, null, context.Response.OutputStream);
32                 context.Response.Flush();
33                 context.Response.Close();
34                 context.Response.End();
35             }
36         
37         
38         }

//由于这里有定义一个新字段，取自类CActivity，其他字段是继承CSR_Activity类。

class CActivity : CSR_Activity 
        {
            public string status {set ;get;}
        }

//取出来的lstStaff object集合转化为DataTable，再转换为XML

 1 private DataTable ConvertToDatatable<T>(List<T> data)
 2         {
 3             PropertyDescriptorCollection props =
 4                 TypeDescriptor.GetProperties(typeof(T));
 5             DataTable table = new DataTable();
 6             for (int i = 0; i < props.Count; i++)
 7             {
 8                 PropertyDescriptor prop = props[i];
 9                 if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
10                     table.Columns.Add(prop.Name, prop.PropertyType.GetGenericArguments()[0]);
11                 else
12                     table.Columns.Add(prop.Name, prop.PropertyType);
13             }
14             object[] values = new object[props.Count];
15             foreach (T item in data)
16             {
17                 for (int i = 0; i < values.Length; i++)
18                 {
19                     values[i] = props[i].GetValue(item);
20                 }
21                 table.Rows.Add(values);
22             }
23             return table;
24         }

这里有加载了一个自定义的xslt模板，如下内容，我这里有两个sheet，分别加载了两个xml传过来的内容：

  1 <?xml version="1.0" encoding="utf-8"?>
  2 <?mso-application progid="Excel.Sheet"?>
  3 <xsl:stylesheet version="1.0"
  4     xmlns:html="http://www.w3.org/TR/REC-html40"
  5     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  6     xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  7     xmlns:o="urn:schemas-microsoft-com:office:office"
  8     xmlns:x="urn:schemas-microsoft-com:office:excel"
  9     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 10   <xsl:template match="/">
 11     <Workbook>
 12       <Styles>
 13         <Style ss:ID="Default" ss:Name="Normal">
 14           <Alignment ss:Horizontal="Left" ss:Vertical="Center" />
 15           <Borders />
 16           <Font />
 17           <Interior />
 18           <NumberFormat />
 19           <Protection />
 20         </Style>
 21         <Style ss:ID="s62" ss:Name="typelink">
 22           <Alignment ss:Horizontal="Left" ss:Vertical="Center" />
 23           <Borders>
 24             <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
 25             <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
 26             <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
 27             <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
 28           </Borders>
 29           <Font ss:FontName="Arial" x:CharSet="134" ss:Size="11" ss:Color="#0000FF"
 30                      ss:Underline="Single"/>
 31         </Style>
 32         <Style ss:ID="s63">
 33           <Alignment ss:Horizontal="Left" ss:Vertical="Center" />
 34           <Borders>
 35             <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
 36             <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
 37             <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
 38             <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
 39           </Borders>
 40         </Style>
 41         <Style ss:ID="s67">
 42           <Alignment ss:Horizontal="Left" ss:Vertical="Center" />
 43           <Borders>
 44             <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
 45             <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
 46             <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
 47             <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
 48           </Borders>
 49           <Font ss:FontName="Arial" x:CharSet="134" ss:Size="11" ss:Bold="1"/>
 50           <Interior ss:Color="#DDD9C4" ss:Pattern="Solid"/>
 51         </Style>
 52         <Style ss:ID="s68">
 53           <Alignment ss:Horizontal="Left" ss:Vertical="Center" />
 54           <Borders>
 55             <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
 56             <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
 57             <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
 58             <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
 59           </Borders>
 60         </Style>
 61       </Styles>
 62       <Worksheet ss:Name="员工">
 63         <Table>
 64           <Column ss:Width="65.25"/>
 65           <Column ss:Width="66.75"/>
 66           <Column ss:Width="36"/>
 67           <Column ss:Width="109.5"/>
 68           <Column ss:Index="8" ss:Width="139.5"/>
 69           <Column ss:Width="54"/>
 70           <Column ss:Width="42.75"/>
 71           <Column ss:Width="54"/>
 72           <Column ss:Width="66.75"/>
 73           <Column ss:Width="31.5"/>
 74           <Row>
 75             <Cell ss:StyleID="s67">
 76               <Data ss:Type="String">活动号</Data>
 77             </Cell>
 78             <Cell ss:StyleID="s67">
 79               <Data ss:Type="String">活动主题</Data>
 80             </Cell>
 81             <Cell ss:StyleID="s67">
 82               <Data ss:Type="String">活动时数</Data>
 83             </Cell>
 84             <Cell ss:StyleID="s67">
 85               <Data ss:Type="String">活动状态</Data>
 86             </Cell>
 87             <Cell ss:StyleID="s67">
 88               <Data ss:Type="String">活动日期</Data>
 89             </Cell>
 90             <Cell ss:StyleID="s67">
 91               <Data ss:Type="String">活动人数</Data>
 92             </Cell>
 93             <Cell ss:StyleID="s67">
 94               <Data ss:Type="String">截止日期</Data>
 95             </Cell>
 96             <Cell ss:StyleID="s67">
 97               <Data ss:Type="String">备注</Data>
 98             </Cell>
 99             <Cell ss:StyleID="s67">
100               <Data ss:Type="String">发起人</Data>
101             </Cell>
102             <Cell ss:StyleID="s67">
103               <Data ss:Type="String">提交时间</Data>
104             </Cell>
105             
106           </Row>
107           <xsl:call-template name="staff" />
108         </Table>
109         <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
110           <DoNotDisplayGridlines/>
111         </WorksheetOptions>
112       </Worksheet>
113 
114       <Worksheet ss:Name="家属2">
115         <Table>
116           <Column ss:Width="65.25"/>
117           <Column ss:Width="66.75"/>
118           <Column ss:Width="36"/>
119           <Column ss:Width="109.5"/>
120           <Column ss:Index="8" ss:Width="139.5"/>
121           <Column ss:Width="54"/>
122           <Column ss:Width="42.75"/>
123           <Column ss:Width="54"/>
124           <Column ss:Width="66.75"/>
125           <Column ss:Width="31.5"/>
126           <Row>
127             <Cell ss:StyleID="s67">
128               <Data ss:Type="String">活动号</Data>
129             </Cell>
130             <Cell ss:StyleID="s67">
131               <Data ss:Type="String">活动主题</Data>
132             </Cell>
133             <Cell ss:StyleID="s67">
134               <Data ss:Type="String">活动时数</Data>
135             </Cell>
136             <Cell ss:StyleID="s67">
137               <Data ss:Type="String">活动状态</Data>
138             </Cell>
139             <Cell ss:StyleID="s67">
140               <Data ss:Type="String">活动日期</Data>
141             </Cell>
142             <Cell ss:StyleID="s67">
143               <Data ss:Type="String">活动人数</Data>
144             </Cell>
145            
146 
147           </Row>
148           <xsl:call-template name="Family123" />
149         </Table>
150         <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
151           <DoNotDisplayGridlines/>
152         </WorksheetOptions>
153       </Worksheet>
154     </Workbook>
155   </xsl:template>
156   <xsl:template name="staff">
157     <xsl:for-each select="//NewDataSet/Staff">
158       <Row>
159         <Cell ss:StyleID="s68">
160           <Data ss:Type="String">
161             <xsl:value-of select="CSRNo" />
162           </Data>
163         </Cell>
164         <Cell ss:StyleID="s68">
165           <Data ss:Type="String">
166             <xsl:value-of select="ActivityTheme" />
167           </Data>
168         </Cell>
169         <Cell ss:StyleID="s68">
170           <Data ss:Type="String">
171             <xsl:value-of select="ActivityHours" />
172           </Data>
173         </Cell>
174         <Cell ss:StyleID="s68">
175           <Data ss:Type="String">
176             <xsl:value-of select="status" />
177           </Data>
178         </Cell>
179         <Cell ss:StyleID="s68">
180           <Data ss:Type="String">
181             <xsl:value-of select="ActivityDate" />
182           </Data>
183         </Cell>
184         <Cell ss:StyleID="s68">
185           <Data ss:Type="String">
186             <xsl:value-of select="TotalMembers" />
187           </Data>
188         </Cell>
189         <Cell ss:StyleID="s68">
190           <Data ss:Type="String">
191             <xsl:value-of select="ApplyDeadLine" />
192           </Data>
193         </Cell>
194         <Cell ss:StyleID="s68">
195           <Data ss:Type="String">
196             <xsl:value-of select="Remark" />
197           </Data>
198         </Cell>
199         <Cell ss:StyleID="s68">
200           <Data ss:Type="String">
201             <xsl:value-of select="Originator" />
202           </Data>
203         </Cell>
204         <Cell ss:StyleID="s68">
205           <Data ss:Type="String">
206             <xsl:value-of select="SubmitTime" />
207           </Data>
208         </Cell>
209         
210        
211       </Row>
212     </xsl:for-each>
213   </xsl:template>
214 
215   <xsl:template name="Family123">
216     <xsl:for-each select="//NewDataSet/Family123">
217       <Row>
218         <Cell ss:StyleID="s68">
219           <Data ss:Type="String">
220             <xsl:value-of select="CSRNo" />
221           </Data>
222         </Cell>
223         <Cell ss:StyleID="s68">
224           <Data ss:Type="String">
225             <xsl:value-of select="ActivityTheme" />
226           </Data>
227         </Cell>
228         <Cell ss:StyleID="s68">
229           <Data ss:Type="String">
230             <xsl:value-of select="ActivityHours" />
231           </Data>
232         </Cell>
233         <Cell ss:StyleID="s68">
234           <Data ss:Type="String">
235             <xsl:value-of select="status" />
236           </Data>
237         </Cell>
238         <Cell ss:StyleID="s68">
239           <Data ss:Type="String">
240             <xsl:value-of select="ActivityDate" />
241           </Data>
242         </Cell>
243         <Cell ss:StyleID="s68">
244           <Data ss:Type="String">
245             <xsl:value-of select="TotalMembers" />
246           </Data>
247         </Cell>
248         
249 
250 
251       </Row>
252     </xsl:for-each>
253   </xsl:template>
254   
255 </xsl:stylesheet>

最后的效果为：

posted @ 2016-03-11 16:44 iDennis 阅读(200) 评论(0) 收藏举报

刷新页面返回顶部

GridPanel数据导出成xls

公告