导入导出

//引用

using System.Data.SqlClient;
using System.Data.OleDb;

//导入

 public DataSet excel(string filename)
    {
        //string name = Request.PhysicalApplicationPath + "/" + filename;
        string name = File1.PostedFile.FileName;
        string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + name + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";

        OleDbConnection Conn = new OleDbConnection(ConnStr);
        Conn.Open();
        string SQL = "select * from [sheet1$]";
        OleDbDataAdapter da = new OleDbDataAdapter(SQL, ConnStr);
        DataSet ds = new DataSet();
        da.Fill(ds);
        Conn.Close();
        return ds;
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        if (File1.PostedFile.FileName != "")
        {
            string name = File1.PostedFile.FileName;
            int q = name.LastIndexOf(".");
            string kz = name.Substring(q);
            if (kz == ".xls")
            {
                File1.PostedFile.SaveAs(Server.MapPath("TransPrice.xls"));
                DataSet ds = excel("TransPrice.xls");
              
                if (ds.Tables[0].Columns.Count == 5)
                {
                    DataTable dt = new DataTable();
                    dt.Columns.Add("11");
                    dt.Columns.Add("22");
                    dt.Columns.Add("33");
                    dt.Columns.Add("44");
                    dt.Columns.Add("55");
                    DataRow dr;
                    string str = "<table border='1' width='400'>";
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        dr = dt.NewRow();
                        str += "<tr>";
                        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        {
                         string  ss= ds.Tables[0].Rows[i][j].ToString();
                         
                          dr[j] = ss;
                          str += "<td>" + ss + "</td>";
                        }
                        str += "</tr>";
                        dt.Rows.Add(dr);

                    }
                    str += "</table>";
                    lit1.Text = str;
                }
               
            }
        

        }
    }

 // 导出

public void OutPutExcel()
    {
        //定义文档类型、字符编码
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        //下面这行很重要， attachment 参数表示作为附件下载，您可以改成 online在线打开
        //filename=FileFlow.xls 指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为：.doc 　　 .xls 　　 .txt 　　.htm
        Response.AppendHeader("Content-Disposition", "attachment;filename=FileFlow.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        //Response.ContentType指定文件类型 可以为application/ms-excel、word、txt、html 或其他浏览器可直接支持文档
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        //　定义一个输入流
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

        this.RenderControl(oHtmlTextWriter);
        //this 表示输出本页，你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件
        Response.Write(oStringWriter.ToString());
        Response.End();
    }

//DataTable导出

 /// <summary>
    /// 将DataTable导出到Excel
    /// </summary>
    /// <param name="dt">要导出的DataTable</param>
    private void m_ExportExcel(DataTable dt)
    {
        System.IO.StringWriter stringWriter = new System.IO.StringWriter();
        HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
        DataGrid excel = new DataGrid();
        System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();
        System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
        System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();
        AlternatingStyle.BackColor = System.Drawing.Color.LightGray;
        headerStyle.BackColor = System.Drawing.Color.LightGray;
        headerStyle.Font.Bold = true;
        headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
        itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; ;

        excel.AlternatingItemStyle.MergeWith(AlternatingStyle);
        excel.HeaderStyle.MergeWith(headerStyle);
        excel.ItemStyle.MergeWith(itemStyle);
        excel.GridLines = GridLines.Both;
        excel.HeaderStyle.Font.Bold = true;
        excel.DataSource = dt.DefaultView;   //输出DataTable的内容
        excel.DataBind();
        //设置成字符格式
        for (int i = 0; i < excel.Items.Count; i++)
        {
            excel.Items[i].Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
    
        }

        excel.RenderControl(htmlWriter);

        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls");
        HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
        HttpContext.Current.Response.ContentType = ".xls";
        HttpContext.Current.Response.Write(stringWriter.ToString());
        HttpContext.Current.Response.End();
    }

/// <summary>
  /// 将DataSet里所有数据导入Excel.
  /// 需要添加COM: Microsoft Excel Object Library.
  /// using Excel;
  /// </summary>
  /// <param name="filePath"></param>
  /// <param name="ds"></param>
 private void ExportToExcel(string filePath, DataSet ds)
        {
            object oMissing = System.Reflection.Missing.Value;
            Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
            try
            {
                //打开EXCEL文件
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,oMissing,oMissing);
               // Excel.Workbook xlWorkbook=xlApp.Workbooks.只有Open属性，没有Write属性
                Excel.Worksheet xlWorksheet;
                //循环所有DataTable
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    //添加入一个新的Sheel页
                    xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing, oMissing, 1, oMissing);
                    //以TableName作为新加的sheel页名
                    xlWorksheet.Name = ds.Tables[i].TableName;
                    //取出这个DataTable中的所有值，暂时存于stringBuffer中
                    string stringBuffer = "";
                  //  for(int m=0;m<ds.Tab
                    for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
                    {
                        for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
                        {
                            stringBuffer += ds.Tables[i].Rows[j][k].ToString();
                            if (k < ds.Tables[i].Columns.Count - 1)
                                stringBuffer += "/t";
                        }
                        stringBuffer += "/n";
                    }
                    //利用系统剪贴板
                    System.Windows.Forms.Clipboard.SetDataObject("");
                    //将stringBuffer放入剪贴板
                    System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
                    //选中这个sheel页中的第一个单元格
                    ((Excel.Range)xlWorksheet.Cells[1, 1]).Select();
                    //粘贴
                    xlWorksheet.Paste(oMissing, oMissing);
                    //清空系统剪贴板
                    System.Windows.Forms.Clipboard.SetDataObject("");
                }
                //保存并关闭这个工作薄
                xlWorkbook.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                xlWorkbook = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //释放...
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
                GC.Collect();
            }
        }

}