在项目中经常需要把数据导出，导入到Excel中

       导出excel有两种，一种导出Html格式，一种导出标准Excel格式

       导出Html格式，导出速度快，导出简单，显示和普通的Excel一样（但是选择打开方式用记事本打开你会发现它是个html页面）。但是以此为模板导入程序不行，以为excel不识别这种格式。

      导出标准Excel格式（本方法用模板副本导出，不是用excel组件）导出速度稍微慢一些，但是对于数据量不大的导出还是可以用的，优点：支持以此为模板直接导人程序。因为它是标准Excel（右击用记事本打开为乱码）。

      

     导出html格式Excel

     

public static void ToExcel(Control ctl, string filename, bool checkControl){    if (checkControl)    {        PrepareGridViewForExport(ctl);    }    HttpContext.Current.Response.Clear();    HttpContext.Current.Response.Buffer = true;    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";    HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", page.Server.UrlEncode(filename.Trim())));    HttpContext.Current.Response.Charset = Encoding.UTF8.EncodingName;    HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;    HttpContext.Current.Response.Write("
      ");    HttpContext.Current.Response.Write(writer.ToString());    HttpContext.Current.Response.Write("");    HttpContext.Current.Response.End();}

private static void PrepareGridViewForExport(Control ctl){    LinkButton button = new LinkButton();    Literal child = new Literal();    for (int i = 0; i < ctl.Controls.Count; i++)    {        if (ctl.Controls[i].GetType() == typeof(LinkButton))        {            child.Text = (ctl.Controls[i] as LinkButton).Text;            ctl.Controls.Remove(ctl.Controls[i]);            ctl.Controls.AddAt(i, child);        }        else if (ctl.Controls[i].GetType() == typeof(DropDownList))        {            child.Text = (ctl.Controls[i] as DropDownList).SelectedItem.Text;            ctl.Controls.Remove(ctl.Controls[i]);            ctl.Controls.AddAt(i, child);        }        else if (ctl.Controls[i].GetType() == typeof(CheckBox))        {            child.Text = (ctl.Controls[i] as CheckBox).Checked ? "True" : "False";            ctl.Controls.Remove(ctl.Controls[i]);            ctl.Controls.AddAt(i, child);        }        else if (ctl.Controls[i].GetType() == typeof(HyperLink))        {            child.Text = (ctl.Controls[i] as HyperLink).Text;            ctl.Controls.Remove(ctl.Controls[i]);            ctl.Controls.AddAt(i, child);        }        else if (ctl.Controls[i].GetType() == typeof(CheckBoxList))        {            CheckBoxList list = ctl.Controls[i] as CheckBoxList;            string str2 = string.Empty;            foreach (ListItem item in list.Items)            {                str2 = str2 + (item.Selected ? "(True)" : "(False)") + item.Text;            }            child.Text = str2;            ctl.Controls.Remove(list);            ctl.Controls.AddAt(i, child);        }        if (ctl.Controls[i].HasControls())        {            PrepareGridViewForExport(ctl.Controls[i]);        }    }    if (ctl is GridView)    {        GridView view = ctl as GridView;        foreach (GridViewRow row in view.Rows)        {            foreach (TableCell cell in row.Cells)            {                cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@");            }        }    }}

 导出Excel标准格式

 

/// 
        /// 导出标准格式Excel        /// 
        ///         ///         public void ExportOut(string filename)        {            //复制模板名称            string filePath = Server.MapPath("~/UploadExcel/" + Guid.NewGuid().ToString() +DateTime.Now.Millisecond+ ".xls");            //导出文件名称            filename = filename + DateTime.Now.Year + DateTime.Now.Month+DateTime.Now.Day+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+ ".xls";            //复制模板            File.Copy(Server.MapPath("~/UploadExcel/模板(请勿删除).xls"), filePath);            //连接字符串            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'");            using (conn)            {                conn.Open();                OleDbCommand cmd = null;                // 增加记录          string sql= " INSERT INTO [Sheet1$] ([产品编号], [产品名称], [批号],[当前库存],[盘点库存],[备注]) VALUES('" + 001+ "', '" + xxx+ "','" + 001+ "','" + 5+ "','" + 5 + "','" +00+ "'); ";                    cmd = new OleDbCommand(sql, conn);                    cmd.ExecuteNonQuery();            }            // 输出副本的二进制字节流            HttpContext.Current.Response.Clear();            Response.ContentType = "application/ms-excel";            //设置导出标题            Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + "\"");            Response.BinaryWrite(File.ReadAllBytes(filePath));            // 删除副本            File.Delete(filePath);        }

  注：1.你可以把sql语句利用程序根据业务组合，循环插入执行，达到数据批量导出的效果

        2.新建一个Excel文档， 把要导出的标题列写好，在excel中右击选中单元格格式设置=》选中文本。这样的话对于特殊字符就能正常的输出了。把excel添加到程序里，放到指定的文件夹里防止误删。

         3. File.Copy(Server.MapPath("~/UploadExcel/模板(请勿删除).xls"), filePath);其中""~/UploadExcel/模板(请勿删除).xls""就是excel模板位置

Excel导入

    

private DataSet xsldata()            //定义一个dataset并把excel数据读入dataset        {            OpenFileDialog openFile = new OpenFileDialog();//打开文件对话框。            openFile.Filter = ("Excel 文件(*.xls)|*.xls");//后缀名。            if (openFile.ShowDialog() == DialogResult.OK)            {                string filename = openFile.FileName;                int index = filename.LastIndexOf("\\");//截取文件的名字                filename = filename.Substring(index + 1);                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFile.FileName + ";Extended Properties='Excel 8.0;IMEX=1'";                System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);                Conn.Open();                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　                DataTable SheetName = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });                //包含excel中表名的字符串数组                string[] strTableNames = new string[SheetName.Rows.Count];                for (int k = 0; k < SheetName.Rows.Count; k++)                {                    strTableNames[k] = SheetName.Rows[k]["TABLE_NAME"].ToString();                }                //从指定的表明查询数据,可先把所有表明列出来供用户选择                string strCom = "select * from [" + strTableNames[0] + "] ";                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);                DataSet ds = new DataSet();                myCommand.Fill(ds);                Conn.Close();                return ds;    //返回dataset            }            else            {                return null;            }        }