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         }

posted on 2011-04-27 12:06 freedom831215 阅读(174) 评论(0) 编辑收藏举报