首页 > 代码库 > .net 自己写的操作Excel 导入导出 类(以供大家参考和自己查阅)
.net 自己写的操作Excel 导入导出 类(以供大家参考和自己查阅)
由于现在网页很多都关系到Excel 的操作问题,其中数据的导入导出更是频繁,作为一个菜鸟,收集网上零散的知识,自己整合,写了一个Excel导入到GridView ,以及将GridView的数据导出到EXCEL的类方法,以供参考和方便自己以后查阅。
1 #region 引用部分 2 using System; 3 using System.Collections.Generic; 4 using System.Linq; 5 using System.Web; 6 using System.Data; 7 using System.Data.OleDb; 8 using System.IO; 9 #endregion 10 /// <summary> 11 ///Excel_DataAcess 的摘要说明 12 /// </summary> 13 public class Excel_DataAcess 14 { 15 private string errors; 16 17 /// <summary> 18 /// 获取方法错误提示 19 /// </summary> 20 public string Get_errors { 21 get { 22 return errors; 23 } 24 } 25 26 /// <summary> 27 /// 由于版本问题,OIEDB的连接参数会有不同,所以这个字段如果拓展写,方便存储当下文件的OLEDB 的连接; 28 /// </summary> 29 private OleDbConnection strconnect = null; 30 31 /// <summary> 32 /// 构造函数 33 /// </summary> 34 public Excel_DataAcess() 35 { 36 // 37 //TODO: 在此处添加构造函数逻辑 38 // 39 } 40 41 /// <summary> 42 /// 获取OLEDB的连接 43 /// </summary> 44 /// <param name="_path">EXCEL文件路径</param> 45 /// <param name="flag">用于判断版本,07以下或07以上有不同的连接参数</param> 46 /// <returns></returns> 47 private OleDbConnection Get_Connect(string _path, int flag) 48 { 49 string connect; 50 if (flag <= 7) 51 { 52 connect = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + _path + ";Extended Properties=‘Excel 8.0;HDR=NO;IMEX=1‘;"; 53 } 54 else 55 { 56 connect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _path + ";Extended Properties=‘Excel 8.0;HDR=NO;IMEX=1‘;"; 57 } 58 OleDbConnection _con = new OleDbConnection(connect); 59 return _con; 60 61 } 62 63 /// <summary> 64 /// 导入EXCEL数据到GRIDVIEW中 65 /// </summary> 66 /// <param name="_path">文件路径</param> 67 /// <param name="dt">导入的数据表</param> 68 /// <returns>返回成功与否</returns> 69 public bool ReportExce(string _path, out DataTable dt) 70 { 71 72 bool result = false; 73 dt = null; 74 75 try 76 { 77 OleDbConnection _con = Get_Connect(_path, 8); 78 try 79 { 80 _con.Open(); 81 } 82 catch { 83 _con = Get_Connect(_path, 7); 84 _con.Open(); 85 } 86 Dictionary<int,string> test= Get_Sheet(_con); 87 88 string sql = string.Format("select * from [{0}]", test[0]); 89 90 OleDbDataAdapter _date = new OleDbDataAdapter(sql, _con); 91 DataSet _set = new DataSet(); 92 _date.Fill(_set, "table"); 93 dt = _set.Tables[0]; 94 _con.Close(); 95 result = true; 96 strconnect = _con; 97 } 98 catch(Exception ex) { 99 string err = ex.Message; 100 } 101 102 return result; 103 104 105 } 106 107 /// <summary> 108 /// 获取当前EXCEL文件的所有SHEET 109 /// </summary> 110 /// <param name="_con">当前连接</param> 111 /// <returns>返回一个集合</returns> 112 public Dictionary<int, string> Get_Sheet(OleDbConnection _con) 113 { 114 Dictionary<int, string> Allsheet = new Dictionary<int, string>(); 115 116 DataTable dt = _con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 117 118 for (int i = 0; i < dt.Rows.Count;i++ ) { 119 Allsheet.Add(i, dt.Rows[i][2].ToString()); 120 } 121 122 return Allsheet; 123 } 124 125 /// <summary> 126 /// 导出GRIDVIEW数据到EXCEL文件中 127 /// </summary> 128 /// <param name="obj">传入的GRIDVIEW控件</param> 129 /// <returns>返回成功与否</returns> 130 public bool OutExcel(object obj) { 131 System.Web.UI.WebControls.GridView gid = (System.Web.UI.WebControls.GridView)obj; 132 133 bool result = false; 134 135 //string style = ""; 136 //if (gid.Rows.Count > 0) { 137 // style=@"<style> .text { mso-number-format:\@; } </script> "; 138 //} 139 try 140 { 141 string filename = DateTime.Now.ToString(); 142 143 HttpContext.Current.Response.ClearContent(); 144 145 HttpContext.Current.Response.Buffer = true; 146 //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 147 HttpContext.Current.Response.Charset = "GB2312"; 148 //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); 149 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 150 //Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); 151 //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 152 HttpContext.Current.Response.AddHeader("Content-disposition", "attachment;filename=" + filename + ".xls"); 153 //Response.AddHeader("content-disposition", "attachment; filename=" + fileName); 154 HttpContext.Current.Response.ContentType = "application/excel"; 155 //StreamWriter sw = new StreamWriter(); 156 StringWriter sw = new StringWriter(); 157 System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw); 158 gid.RenderControl(htw); 159 HttpContext.Current.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>"+sw.ToString()); 160 161 result = true; 162 } 163 catch (Exception ex) 164 { 165 errors = ex.Message; 166 } 167 finally { 168 HttpContext.Current.Response.End(); 169 } 170 return result; 171 172 } 173 }
.net 自己写的操作Excel 导入导出 类(以供大家参考和自己查阅)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。