DataTable导入导出Excel

最新推荐文章于 2016-03-30 20:58:00 发布

转载最新推荐文章于 2016-03-30 20:58:00 发布 · 153 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/myssh/archive/2009/08/10/1543157.html

本文介绍了一种使用C#进行Excel文件读写的实现方法。导入部分展示了如何通过OleDb连接并读取Excel文件中的特定数据，同时进行了必要的字段映射。导出部分则演示了如何将数据库中的数据导出为Excel文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//导入
protected DataSet GetExcelContent(string filepath)
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
        System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);

        string strCom = "SELECT 0 as ID , ID as GoodsID,编码 as Code,名称 as Name,产品型号 as Model,单位 as Unit,账面库存 as BQ,0 as Quantity,'' as OI,货号 as ImgNum,'0' AS rIndex FROM [盘点单$]";
        myConn.Open();
        System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
        //创建一个DataSet对象
        DataSet myDataSet = new DataSet();
        //得到自己的DataSet对象
        myCommand.Fill(myDataSet, "table1");
        //关闭此数据链接
        myConn.Close();
        for (int i = 0; i < myDataSet.Tables["table1"].Rows.Count; i++)
        {
            myDataSet.Tables["table1"].Rows[i]["rIndex"] = i.ToString();
        }
        return myDataSet;
    }

//导出
protected void lblUxToExcel_Click(object sender, EventArgs e)
    {
        StringWriter sw = new StringWriter();
        sw.WriteLine("编号\t名称\t密码\t性别\tEmail\t城市\t地址\t登陆IP");
        DataTable dt = userManager.FindAlluser();
        foreach (DataRow dr in dt.Rows)
        {
            sw.WriteLine(dr["userId"] + "\t" + dr["userName"] + "\t" + dr["password"] + "\t" + dr["sex"] + "\t" + dr["email"] + "\t" + dr["city"] + "\t" + dr["address"] + "\t" + dr["loginIP"]);
        }
        sw.Close();
        Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
        Response.ContentType = "application/ms-excel";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.Write(sw);
        Response.End();
    }

转载于:https://www.cnblogs.com/myssh/archive/2009/08/10/1543157.html