Excel数据导出与导入技术详解-优快云博客

1.Oled

 /// </summary>    
        /// <param name="pathName">带路径的Excel文件名</param>    
        /// <param name="tbContainer">将数据存入的DataTable</param>
        /// <returns></returns> 
        public DataTable ExcelToDataTable(string pathName)
        {
            DataTable tbContainer = new DataTable();
            string strConn = string.Empty;
            string sheetName = "Sheet1";
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists)
            {
                throw new Exception("文件不存在");
            }
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            OleDbConnection cnnxls = new OleDbConnection(strConn);
            OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
            oda.Fill(tbContainer);
            return tbContainer;
        }

上面的那个必须规定sheetName，

 public DataTable ExcelToDataTable(string pathName)
        {
            DataTable tbContainer = new DataTable();
            string strConn = string.Empty;
            string sheetName = "Sheet0"; 
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists)
            {
                throw new Exception("文件不存在");
            }
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            OleDbConnection cnnxls = new OleDbConnection(strConn);
            //OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
            OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [$A1:I65536]", sheetName), cnnxls);
            oda.Fill(tbContainer);
            return tbContainer;
        }

http://blog.youkuaiyun.com/sunyou/article/details/6888148

http://blog.youkuaiyun.com/fangxinggood/article/details/397315

2.Microsoft.Office.Interop.Excel

http://www.cnblogs.com/yuanlei347/archive/2009/07/14/1523479.html

导出Excel

 protected void OnClick_btnExcel(object sender, EventArgs e)
        {
           List<ConfirmListExcelEntity> list= GetConfirmListExcel();
           Response.Clear();
           Response.Buffer = true;
           Response.Charset = "GB2312";
           Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("分销商列表", System.Text.Encoding.UTF8) + ".xls");
           Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");     //设置输出流为简体中文
           Response.ContentType = "application/ms-excel";    //设置输出文件类型为excel文件。 
           Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=GB2312\"></head><body><table>");
           Response.Write("<tr><th>AllianceID</th><th>AllianceName</th><th>Sid</th><th>站点名称</th><th>站点URL</th><th>站点电话</th><th>站点邮箱</th><th>联系人</th><th>联系人电话</th><th>联系人Email</th><th>备注</th><th>状态</th><th>佣金状态</th><th>规则状态</th></tr>");
           foreach (ConfirmListExcelEntity cee in list)
           {
               Response.Write("<tr><td>" +
                   cee.AllianceID + "</td><td>" +
                   cee.AllianceName + "</td><td>" +
                   cee.SID + "</td><td>" +
                   cee.SiteName + "</td><td>" +
                   cee.SiteURL + "</td><td>" +
                   cee.ContactTel + "</td><td>" +
                   cee.Email + "</td><td>" +
                   cee.Contact + "</td><td>" +
                   cee.AlliancePhone  + "</td><td>" +
                   cee.AllianceEmail + "</td><td>" +
                   cee.Memo + "</td><td>" +
                   cee.Status +"</td><td>"+
                   cee.CommissionStatus +"</td><td>"+
                   cee.RuleStatus + "</td></tr>");
           }
           
           Response.Write("</table></body></html>");
           Response.End();
        }

导出csv

 public bool DownloadBankOrder(DataTable dt,HttpContext context,string FileName)
        {
            try
            {
                //ExcelRender.RenderToExcel(dt, context, FileName+".xls");
                #region 构建csv文件
                StringBuilder result = new StringBuilder();
                if (dt == null || dt.Rows.Count < 1)
                {
                    result.AppendLine("");
                }
                else
                {
                    string strhead = "BGWTXNID,APPTXNID,APPTXNDATE,BANKCODE,ACQUIREID,";
                    strhead += "BANKTXNAMOUNT,TXNSTATUS,CHANNELID,APPCODE";
                    result.AppendLine(strhead);
                    string strdate = "";
                    foreach (DataRow dr in dt.Rows)
                    {
                        strdate = "";
                        strdate += dr["BGWTXNID"].ToString() + ",";
                        strdate += dr["APPTXNID"].ToString() + ",";
                        strdate += dr["APPTXNDATE"].ToString() + ",";
                        strdate += dr["BANKCODE"].ToString() + ",";
                        strdate += dr["ACQUIREID"].ToString() + ",";
                        strdate += dr["BANKTXNAMOUNT"].ToString() + ",";
                        strdate += dr["TXNSTATUS"].ToString() + ",";
                        strdate += dr["CHANNELID"].ToString() + ",";
                        strdate += dr["APPCODE"].ToString();
                        result.AppendLine(strdate);
                    }
                }
                #endregion

                context.Response.Clear();
                context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".csv");
                context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                context.Response.ContentType = "application/vnd.ms-excel";

                System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
                System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
                context.Response.Write(result.ToString());
                return true;
            }
            catch
            {
                return false;
            }
        }

导入csv（格式)

        public DataTable GetDataSetFromCSV(string filePath, string fileName)
        {

            string strConn = @"Provider=MICROSOFT.JET.OLEDB.4.0;Extended Properties=Text;DATA SOURCE=" + filePath;//另一可用的链接字符串,不过要引用的是OleDb命名空间;而不是Odbc了;
            OleDbConnection objConn = new OleDbConnection(strConn);
            DataSet dsCSV = new DataSet();
            try
            {
                string strSql = "select * from " + fileName;                     //fileName, For example: 1.csv
                OleDbDataAdapter odbcCSVDataAdapter = new OleDbDataAdapter(strSql, objConn);
                odbcCSVDataAdapter.Fill(dsCSV);
                return dsCSV.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }