datalist生成excel

DataView dataView1 = dsExcelList.Tables[ 0 ].DefaultView;
DataList7.DataSource = dataView1;
DataList7.DataBind();

string fileName = DateTime.Now.ToString( " yyyyMMddHHmmss-- " ) + userLogin.E_NAME + " .xls " ;
string path = Server.MapPath( " Document/Excel/ " + fileName);

HyperLink14142.NavigateUrl = " ~/Headhunter/SQL " + FreedomManager.InteropExcel(GetDataSet(DataList7), path).Replace( " \\ " , " / " ); ;
HyperLink14142.Text = " 下载文档 " ;

public DataSet GetDataSet(DataList dl)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();


DataListItem col = dl.Items[ 0 ];
int colcount = col.Controls.Count;

int colnum = 0 ;
for ( int j = 0 ; j < colcount; j ++ )
{
if (col.Controls[j] is Label || col.Controls[j] is HyperLink)
{
dt.Columns.Add( " column " + colnum, System.Type.GetType( " System.String " ));
colnum ++ ;
}
}




int dataListCount = dl.Items.Count;
for ( int i = 0 ; i < dataListCount; i ++ )
{
DataRow dr = dt.NewRow();
colnum = 0 ;

DataListItem dli = dl.Items[i];
int count = dli.Controls.Count;
for ( int j = 0 ; j < count; j ++ )
{
if (dli.Controls[j] is Label || dli.Controls[j] is HyperLink)
{
string column = " column " + colnum;
dr[column] = getConttrilText(dli.Controls[j]);
colnum ++ ;
}
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
return ds;
}

View Code

1 /// <summary>
2 /// 生成excel
3 /// </summary>
4 /// <param name="ds"> DataSet </param>
5 /// <param name="strFileName"> 绝对路径 </param>
6 /// <returns> string </returns>
7   public static string InteropExcel(DataSet ds, string strFileName)
8 {
9 Microsoft.Office.Interop.Excel.Application Ex = new Microsoft.Office.Interop.Excel.Application();
10 Microsoft.Office.Interop.Excel.Workbooks workbooks = Ex.Workbooks;
11 // Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(strFileName+".xls"); // 这里的Add方法里的参数就是模板的路径
12  
13
14
15 Microsoft.Office.Interop.Excel.Worksheet Ws;
16 Microsoft.Office.Interop.Excel._Workbook workbook
17 = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
18 Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
19 Ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item( 1 );
20
21 int Row = 0 ;
22 int Col = 0 ;
23 // pb.Maximum = Ds.Tables[0].Rows.Count;
24 Row = 1 ;
25 Col = 1 ;
26 // 'For Heading
27 for ( int i = 0 ; i <= ds.Tables[ 0 ].Columns.Count - 1 ; i ++ )
28 {
29 Ws.Cells[Row, Col] = getColName(ds.Tables[ 0 ].Columns[i].Caption);
30 Col += 1 ;
31 }
32 Row = 2 ;
33 Col = 1 ;
34 // pb1.Maximum = Ds.Tables[0].Columns.Count;
35 // lblCount.Text = "Preparing for Export Data.";
36 for ( int i = 0 ; i <= ds.Tables[ 0 ].Rows.Count - 1 ; i ++ )
37 {
38 //
39 // FOR ALL DATA
40 //
41
42 // pb1.Value = 0;
43 for ( int j = 0 ; j <= ds.Tables[ 0 ].Columns.Count - 1 ; j ++ )
44 {
45 Ws.Cells[Row, Col] = ds.Tables[ 0 ].Rows[i][j].ToString();
46 Col += 1 ;
47 // pb1.Value += 1;
48 }
49 // 'If data is more than 65500 then set ws to next sheet
50 // if (Row == 65500)
51 if (Row == 50000 )
52 {
53 Row = 1 ;
54 Ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item( 2 );
55 }
56
57 Col = 1 ;
58 Row += 1 ;
59 // lblCount.Text = i + 1 + " : Exported";
60 // lblCount.Refresh();
61 // pb.Value += 1;
62 }
63 // pb.Value = 0;
64 Ex.Visible = false ;
65 // MessageBox.Show(Ds.Tables[0].Rows.Count + " : Records Exported. ");
66 // Ex.Visible = true;
67 workbook.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
68
69 Ex.Quit();
70 Ex = null ;
71 Ws = null ;
72
73 CloseProcess( " excel " );
74 string url = strFileName.Substring(strFileName.IndexOf( " \\Document " ));
75 return url;
76 }
77
78 /// <summary>
79 /// 返回excel列名
80 /// </summary>
81 /// <param name="colName"> 旧列名 </param>
82 /// <returns> string </returns>
83 static string getColName( string colName)
84 {
85 if (colName == " column0 " )
86 {
87 return " 简历编号 " ;
88 }
89 else if (colName == " column1 " )
90 {
91 return " 客户名称 " ;
92 }
93 else if (colName == " column2 " )
94 {
95 return " 职位名称 " ;
96 }
97 else if (colName == " column3 " )
98 {
99 return " 顾问姓名 " ;
100 }
101 else if (colName == " column4 " )
102 {
103 return " 沟通状态 " ;
104 }
105 else if (colName == " column5 " )
106 {
107 return " 编辑日期 " ;
108 }
109 else if (colName == " column6 " )
110 {
111 return " 沟通内容 " ;
112 }
113 else if (colName == " column7 " )
114 {
115 return " 候选人 " ;
116 }
117 else if (colName == " column8 " )
118 {
119 return " 现居住地 " ;
120 }
121 else if (colName == " column9 " )
122 {
123 return " 现任公司 " ;
124 }
125 else if (colName == " column10 " )
126 {
127 return " 现职位 " ;
128 }
129 return "" ;
130 }

转载于:https://www.cnblogs.com/freedom831215/archive/2011/04/27/2030235.html