C#实现导出和导入excel的代码

发布时间:2019-10-29编辑:脚本学堂
本文介绍下,C#实现的可以导出与导入excel的代码一例,有需要的朋友,参考下吧。

C#实现导出与导入excel

代码1:
 

复制代码 代码示例:
#region  导出excel 
/// <summary> 
/// 导出Excel 
/// </summary> 
/// <param name="page">请求的页面this</param> 
/// <param name="dataTable">导出的数据源</param> 
/// <param name="fileName">保存文件名称</param> 
/// <returns>布尔值</returns> 
public bool ExportExcel(Page page, DataTable dataTable, string fileName) 

    try 
    { 
        HttpContext.Current.Response.Clear(); 
        HttpContext.Current.Response.Buffer = true; 
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.numberformat:@"; 
        page.EnableViewState = false; 
        HttpContext.Current.Response.Charset = "UTF-8"; 
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//设置输出流为简体中文 
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); 
        //输出列名 
        for (int i = 0; i < dataTable.Columns.Count; i++) 
          HttpContext.Current.Response.Write(dataTable.Columns[i].ColumnName + "t"); 
          HttpContext.Current.Response.Write("rn"); 
          //输出数据 
        for (int i = 0; i < dataTable.Rows.Count; i++) 
        { 
for (int j = 0; j < dataTable.Columns.Count; j++) 

    HttpContext.Current.Response.Write(dataTable.Rows[i][j].ToString() + "t"); 

HttpContext.Current.Response.Write("rn"); 
        } 
        //输出当前缓存内容 
        //HttpContext.Current.Response.Flush(); 
        HttpContext.Current.Response.End(); 
        return true; 
    } 
    catch 
    { 
        return false; 
    } 

#endregion 

代码2,
 

复制代码 代码示例:
#region 导出Excel 自定义格式 
/// <summary> 
/// 导出Excel   
/// 1.文本:vnd.ms-excel.numberformat:@ 
/// 2.日期:vnd.ms-excel.numberformat:yyyy/mm/dd 
/// 3.数字:vnd.ms-excel.numberformat:#,##0.00 
/// 4.货币:vnd.ms-excel.numberformat:¥#,##0.00 
/// 5.百分比:vnd.ms-excel.numberformat: #0.00% 
/// </summary> 
/// <param name="fileName"></param> 
/// <param name="dt"></param> 
/// <returns></returns> 
public bool Export(string fileName, DataTable dt) 

   try 
   { 
       HttpResponse resp; 
       resp = System.Web.HttpContext.Current.Response; 
       resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); 
       resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); 
       resp.AppendHeader("Content-Type", "application/ms-excel"); 
     
       StringBuilder colHeaders = new StringBuilder(); 
       StringBuilder ls_item = new StringBuilder(); 
       DataRow[] myRow = dt.Select(); 
       int cl = dt.Columns.Count; 
     
       colHeaders.Append(" <html><head> n "); 
       colHeaders.Append(" <meta http-equiv='Content-Type' content='text/html; charset=gb2312' /> n "); 
       colHeaders.Append(" </head> n "); 
       colHeaders.Append(" <body> n "); 
       colHeaders.Append(" <table border='1'> "); 
       colHeaders.Append(" <tr> "); 
       //输出列名 
       for (int i = 0; i < dt.Columns.Count; i++) 
colHeaders.Append("<td  style='background-color:#CCCCCC'>" + dt.Columns[i].ColumnName + "</td>"); 
       colHeaders.Append("</tr> "); 
       resp.Write(colHeaders.ToString()); 
       foreach (DataRow row in myRow) 
       { 
ls_item.Append("<tr>"); 
for (int i = 0; i < cl; i++) 

   if (i == (cl - 1)) 
   { 
       ls_item.Append("<td style='vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>" + "n"); 
   } 
   else 
   { 
       ls_item.Append("<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>"); 
   } 

ls_item.Append("</tr>"); 
       } 
       ls_item.Append(" </table> n "); 
       ls_item.Append(" </body> n "); 
       ls_item.Append(" </html>"); 
       resp.Write(ls_item.ToString()); 
       resp.End(); 
       return true; 
   } 
   catch 
   { 
       return false; 
   } 

#endregion 

代码3,
 

复制代码 代码示例:
#region 导入Excel 
public string ImportExcel(string[] list, string filePath) 

        string isXls = System.IO.Path.GetExtension(filePath).ToLower();//System.IO.Path.GetExtension获得文件的扩展名 
        if (isXls != ".xls") 
        return "请选择Excel文件导入!"; 
        DataSet ds = ExecleDataSet(filePath);//调用自定义方法 
        DataRow[] dr = ds.Tables[0].Select();//定义一个DataRow数组 
        int rowsnum = ds.Tables[0].Rows.Count; 
        if (ds.Tables[0].Rows.Count == 0) 
        return "Excel无数据!"; 
        return ""; 
    } 
     
    //OleDB连接读取Excel中数据 
    public DataSet ExecleDataSet(string filePath) 
    { 
        string OleDbConnection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; 
        OleDbConnection conn = new OleDbConnection(OleDbConnection); 
        conn.Open(); 
        DataSet ds = new DataSet(); 
        OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn); 
        odda.Fill(ds); 
        conn.Close(); 
        return ds; 

#endregion