.Net Excel导出标准格式，Excel导入

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

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

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

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

导出html格式Excel

View Code

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("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
    HttpContext.Current.Response.Write(writer.ToString());
    HttpContext.Current.Response.Write("</body></html>");
    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标准格式

View Code

 /// <summary>
        /// 导出标准格式Excel
        /// </summary>
        /// <param name="InsertSql"></param>
        /// <param name="filename"></param>
        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导入

View Code

    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;
            }

        }