导出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.	dept	Name	Job
001	IT	Anny	Engineer
002		Jim	Engineer
3		Tonney	Engineer

 

 另外参考：http://blog.youkuaiyun.com/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);
        }