导出excel表格，导入excel表格

1.将html输出成excel

如果有单元格合并。

　　　　protected void btnExportHtml_Click(object sender, EventArgs e)        {            string path = Request.MapPath("~/Downloaded/data.html");            Response.Clear();            Response.Buffer = true;            Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");            Response.ContentEncoding = System.Text.Encoding.UTF8;            Response.ContentType = "application/vnd.ms-excel";            string data;            using (StreamReader sr = new StreamReader(path))            {                data = sr.ReadToEnd();            }            Response.Write(data);            this.EnableViewState = false;           }

2.用oledb输出成excel

如果没有单元格合并。

　　<div>        <asp:RadioButtonList runat="server" ID="rblExtension">            <asp:ListItem Text="Excel 2003" Selected="True" Value="2003" />            <asp:ListItem Text="Excel 2007" Value="2007" />        </asp:RadioButtonList>    </div>    <asp:Button Text="导出" runat="server" ID="btnExport" OnClick="btnExport_Click" /><br />    <asp:HyperLink ID="hlDownload" runat="server"></asp:HyperLink>

 

代码：

　　　　　　　　protected DataTable RetrieveData()        {            DataTable dt = new DataTable();            using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))

            {                

                OracleDataAdapter da = new OracleDataAdapter("select ename,job from emp", conn);                da.Fill(dt);            }            return dt;        }　　　　protected void ExportToExcel(string strConn, DataTable dtOrcl)        {            using (OleDbConnection conn = new OleDbConnection(strConn))            {                OleDbCommand cmd = new OleDbCommand("create table emp(ename varchar(10),job varchar(9))", conn);                conn.Open();                cmd.ExecuteNonQuery();                cmd.CommandText = "Insert Into emp(ename,job) values (?,?)";                cmd.Parameters.Add("ename", OleDbType.VarChar, 10, "ename");                cmd.Parameters.Add("job", OleDbType.VarChar, 9, "job");
                OleDbDataAdapter da = new OleDbDataAdapter("select ename,job from emp", conn);                da.InsertCommand = cmd;                foreach (DataRow dr in dtOrcl.Rows)                {                    dr.SetAdded();                }                da.Update(dtOrcl);            }        }　　　　protected void btnExport_Click(object sender, EventArgs e)        {            string strDownloadFileName = "";            string strExcelConn = "";            if (rblExtension.SelectedValue == "2003")            {                // Excel 97-2003                strDownloadFileName = "~/Downloaded/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";                strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 8.0;HDR=Yes'";            }            else            {                // Excel 2007                strDownloadFileName = "~/Downloaded/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";                strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'";            }            // Retrieve data from SQL Server table.            DataTable dtSQL = RetrieveData();            // Export data to an Excel spreadsheet.            ExportToExcel(strExcelConn, dtSQL);            hlDownload.Text = "Click here to download file.";            hlDownload.NavigateUrl = strDownloadFileName;        }

 

避免‘0001‘这种的文字，自动转换为数字 ‘1’

（参考http://www.cnblogs.com/lovenets2008/archive/2008/05/06/1184234.html）

 

<table>        <thead>            <tr>                <th>No.</th>                <th>dept</th>                <th>Name</th>                <th>Job</th>            </tr>        </thead>        <tr>            <td style="vnd.ms-excel.numberformat:@">001</td>            <td rowspan="3">IT</td>            <td>Anny</td>            <td>Engineer</td>        </tr>        <tr>            <td style="vnd.ms-excel.numberformat:@">002</td>            <td>Jim</td>            <td>Engineer</td>        </tr>        <tr>            <td>003</td>            <td>Tonney</td>            <td>Engineer</td>        </tr>    </table>

 结果：

No.deptNameJob001ITAnnyEngineer002JimEngineer3TonneyEngineer

 

 另外参考：http://blog.csdn.net/llhhyy1989/article/details/6704378

使用DataTable导出Excel

private  StringWriter GetStringWriter(DataTable dt)    {        StringWriter sw = new StringWriter();        //读列名        foreach (DataColumn dc in dt.Columns)            sw.Write(dc.ColumnName + "\t");        //读列值        //重新的一行        sw.Write(sw.NewLine);              if (dt != null)        {            foreach (DataRow dr in dt.Rows)            {                for (int i = 0; i < dt.Columns.Count; i++)                {                    sw.Write(dr[i].ToString() + "\t");                }                sw.Write(sw.NewLine);            }        }        sw.Close();                    return sw;    }

 

protected void ExcelImport(DataTable dt, string ExportFileName)    {        StringWriter sw = GetStringWriter(dt);        //当前编码        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");        //把输出的文件名进行编码        string fileName = HttpUtility.UrlEncode (ExportFileName, System.Text.Encoding.UTF8);        //文件名        string str = "attachment;filename=" + fileName + ".xls";        //把文件头输出，此文件头激活文件下载框        HttpContext.Current.Response.AppendHeader("Content-Disposition", str);//http报头文件        HttpContext.Current.Response.ContentType = "application/ms-excel";        this.Page.EnableViewState = false;        Response.Write(sw);        Response.End(); }

 

 protected void Button1_Click(object sender, EventArgs e)    {        DataTable dt = new SelectCourseManager().SelectByCollegeAndProperty(ddlCourseProperty.Text, ddlCollege.SelectedValue);        if (dt.Rows.Count == 0)            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascriopt' defer>alert('没有数据，不需要导出哈');</script>");        else        {             //导出Excel            ExcelImport(dt, "课程");                     }    }

 

 导入excel 表格

<asp:Button ID="btnImport" runat="server" Text="导入" OnClick="btnImport_Click" />

 

protected DataTable RetrieveData(string strConn)        {            DataTable dtExcel = new DataTable();            using (OleDbConnection conn = new OleDbConnection(strConn))            {                OleDbDataAdapter da = new OleDbDataAdapter("select ename,job from  [emp$]", conn);                da.Fill(dtExcel);            }            return dtExcel;        }

 

protected void ImportExcelFile(DataTable dtExcel)        {            using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionStringTestDb"].ToString()))            {                                OracleCommand comm = new OracleCommand();                comm.Connection = conn;                OracleTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);                comm.Transaction = transaction;                comm.CommandType = CommandType.Text;                for (int i = 0; i < dtExcel.Rows.Count; i++)                {                    try                    {                        comm.CommandText = "insert into emp(ename,job) values(:ename,:job)";                        string ename = dtExcel.Rows[i]["ename"].ToString();                        string job = dtExcel.Rows[i]["job"].ToString();                        comm.Parameters.Add(":ename", ename);                        comm.Parameters.Add(":job", job);                        if (conn.State == ConnectionState.Closed)                        {                            conn.Open();                        }                        comm.ExecuteNonQuery();                    }                    catch (Exception e)                    {                        transaction.Rollback();                        conn.Close();                        throw e;                    }                                    }                transaction.Commit();                conn.Close();            }        }

 

 protected void btnImport_Click(object sender, EventArgs e)        {            string strUploadFileName = "~/UploadFiles/data.xlsx";　　　　　　　//Excel 2003 用："Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties='Excel 8.0;HDR=YES;'";　            string strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";            DataTable dtExcel1 = RetrieveData(strExcelConn);            ImportExcelFile(dtExcel1);        }