list泛型如何导出excel!

Question

RT,求个能用的源码,感激不尽!

Answer 1

遍历，一样如同datatable
http://topic.csdn.net/u/20100721/15/87625976-1489-4753-aade-4290211bd1d0.html

Answer 2

方法1: 通过剪切板copy到Excel里，需要用到Excel COM组件
方法2：做成CSV文件(不是真正的Excel，但可以用Excel打开，效率高无EXCEL版本差异）
方法3：通过Excel COM组件一个个单元格赋值(效率低，不推荐)
public static void Export2Xls<T>(List<T> data, string filename, bool exportHeader = true)    
        {   
            if (File.Exists(filename))   
                File.Delete(filename);   
  
            Excel._Application xlsApp = null;   
            Excel._Workbook xlsBook = null;   
            Excel._Worksheet xstSheet = null;   
  
            var type = typeof(T);   
            var properties = type.GetProperties();   
            var buffer = new StringBuilder();   
            if (exportHeader)   
            {   
                xlsApp = new Excel.ApplicationClass();   
  
                xlsBook = xlsApp.Workbooks.Add();   
                xstSheet = (Excel._Worksheet)xlsBook.Worksheets[1];   
                   
                if (exportHeader)   
                {   
                    foreach (var property in properties)   
                        buffer.Append(property.Name + "\t");   
  
                    buffer.AppendLine();   
                }   
                foreach (var row in data)   
                {   
                    foreach (var property in properties)   
                        buffer.Append(property.GetValue(row, null) + "\t");   
  
                    buffer.AppendLine();   
                }   
                System.Windows.Forms.Clipboard.SetDataObject("");   
                // 放入剪切板   
                System.Windows.Forms.Clipboard.SetDataObject(buffer.ToString());   
                var range = (Excel.Range)xstSheet.Cells[1, 1];   
                range.Select();   
                xstSheet.Paste();   
                // 清空剪切板   
                System.Windows.Forms.Clipboard.SetDataObject("");   
  
                xlsBook.SaveAs(filename);   
            }   
        }   
  
        public static void Export2CSV<T>(List<T> data, string filename, bool exportHeader = true)   
        {   
            if (File.Exists(filename))   
                File.Delete(filename);   
  
            var type = typeof(T);   
            var properties = type.GetProperties();   
            var buffer = new StringBuilder();   
  
            if (exportHeader)   
            {   
                for (var i = 0; i < properties.Length; i++)   
                {   
                    buffer.AppendFormat("\"{0}\"", properties[i].Name);   
                    if (i < properties.Length - 1)   
                        buffer.Append(",");   
                }   
                buffer.AppendLine();   
            }   
  
            for (var i = 0; i < data.Count; i++)   
            {   
                for (var j = 0; j < properties.Length; j++)   
                {   
                    buffer.AppendFormat("\"{0}\"", properties[j].GetValue(data[i], null).ToString());   
                    if (j < properties.Length - 1)   
                        buffer.Append(",");   
                }   
                buffer.AppendLine();   
            }   
  
            File.WriteAllText(filename, buffer.ToString());   
        }   
    }   
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fangxinggood/archive/2010/12/26/6099621.aspx

Answer 3

http://blog.csdn.net/fangxinggood/archive/2010/12/26/6099621.aspx

Answer 4

 /// <summary>
        ///将list泛型数组转换成datatable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="header"></param>
        /// <returns></returns>
        public DataTable ListToDataTable<T>(IList<T> list)
        {
            DataTable dt = new DataTable();
            DataRow dr;
            Boolean columnHeader = true;
            foreach (T t in list)
            {
                Type ut = t.GetType();
                PropertyInfo[] plist = ut.GetProperties();
                dr = dt.NewRow();
                foreach (PropertyInfo p in plist)
                {
                    //填充列头
                    if (columnHeader)
                    {
                        if (!dt.Columns.Contains(p.Name))
                        {
                            dt.Columns.Add(p.Name);
                        }
                    }
                  
                    Object value = ut.GetProperty(p.Name).GetValue(t, null); 
                    dr[p.Name] =value.ToString() ;
                }
                dt.Rows.Add(dr);
                if (columnHeader)
                {
                    columnHeader = false;
                }
              
            }
            return dt;
        }        /// <summary>
        /// 下载EXCEL 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        public void DownExcel(DataTable dt,string fileName)
        {
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
            sheet.FreezePanes(1, 1, 1, 0);//冻结第一行
            AddHeader(sheet, dt);
            AddBody(sheet, dt);
            sheet.AutoFitColumns();//让各列自适应宽度
            System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
            workbook.Save(DateTime.Now.ToShortDateString() + fileName + DateTime.Now.ToShortDateString() + ".xls", SaveType.OpenInBrowser, FileFormatType.Default, response);
        }

Answer 5

或者这样导出EXCEL
 /// <summary>
        /// 导出Execl（带表头）
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="FileName"></param>
        /// <param name="response"></param>
        public void CreateExcel(DataTable dt, string FileName, HttpResponse response)
        {
            HttpResponse resp;
            resp = response;
            FileName = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(FileName));
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
            string colHeaders = "", ls_item = "";            //定义表对象与行对象，同时用DataSet对其值进行初始化             DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;            //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加n
                {
                    colHeaders += dt.Columns[i].Caption.ToString().Replace("\n", " ").Replace("\"", "\"\"") + "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString().Replace("\n", " ").Replace("\"", "\"\"") + "\t";
                }            }
            resp.Write(colHeaders.Replace("\r", " "));
            //向HTTP输出流中写入取得的数据信息             //逐行处理数据   
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据     
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加n
                    {
                        ls_item += row[i].ToString().Replace("\n", " ").Replace("\"", "\"\"") + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString().Replace("\n", " ").Replace("\"", "\"\"") + "\t";
                    }                }
                resp.Write(ls_item.Replace("\r", " "));
                ls_item = "";            }
            resp.End();
        }

Answer 6

这样转换到Datatble比较灵活，可以修改表头，排序神马的。