C#读取Excel，导出Excel

 

C#读取Excel，导出Excel

 

C#读取Excel

方法一：OLEDB方式

1、定义连接字符串：
   string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Exce文件路径及名字+ ";" + "Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
2、定义连接对象
   OleDbConnection conn = new OleDbConnection(strConn);
3、打开连接
   conn.Open();
4、定义查询语句
   string strExcel = "select * from [" + Excel工作表的名称+ "]";
5、定义OleDbDataAdapter
   OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
6、定义DataSet
   DataSet ds = new DataSet();
7、读取数据填充到Dataset中
   myCommand.Fill(ds, "table1");

方法二：Appliction对象方式

1、引用命名空间
   using Excel = Microsoft.Office.Interop.Excel;
   using System.Reflection;
2、定义Application对象
   Excel.Application objApp = new Excel.ApplicationClass();
   objApp.Visible = false;
3、定义Workbook对象，打开Excel
   Excel.Workbook objBook = objApp.Workbooks._Open(Exce文件路径及名字,
        Missing.Value, Missing.Value, Missing.Value, Missing.Value
        , Missing.Value, Missing.Value, Missing.Value, Missing.Value
        , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
4、打开工作表
   Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Sheets[1];
5、获得Excel有效行数和列数
   int intCol = objSheet.UsedRange.Columns.Count;
   int intRow = objSheet.UsedRange.Rows.Count;
6、循环读取Excel表中单元格数据
   for (int i = 1; i < intRow; i++)
   {
        for (int j = 0; j < intCol; j++)
        {
            //定义单元格对象
            Excel.Range column = ((Excel.Range)objSheet.get_Range(((char)(65+j)).ToString() + i.ToString(), Type.Missing));
            //取得单元格值
            string strValue = column.Value2.ToString();
        }
   }

 

导出Excel

方法一：

/// <summary>
/// GrideView控件导出Excel
/// </summary>
protected void btnExcelOut_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "GB2312";
    Response.AppendHeader("Content-Disposition", "attachment;filename=Excel文件名称.xls");
    Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
    Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
    this.EnableViewState = false;
    System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
    System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
    System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
    Grd控件名称.RenderControl(oHtmlTextWriter);//GrideView控件
    Response.Write(oStringWriter.ToString());
    Response.End();
}

 

方法二：Application方式导出后，直接弹出下载对话框

protected void teaher_out_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            StringBuilder strSql = new StringBuilder();
            strSql.Append("Sql查询语句");
            DataTable dt = DataBaseClass.ExecuteDataSetReader(DataBaseClass.DataBaseConn, CommandType.Text, strSql.ToString(), parameters).Tables[0];

            //写入字段
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
            }
            //写入数值

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                }
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

            try
            {
            string strName = @"D:/DownLoad/TeacherOut" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
                workbook.Saved = true;
                workbook.SaveCopyAs(strName);
                //ClientScript.RegisterStartupScript(this.GetType(), "this", "<script>alert('导出完成！')</script>");
                DownloadFile(strName);
            }
            catch (Exception ex)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "this", "<script>alert('导出文件时出错,文件可能正被打开！')</script>");

            }
            finally
            {
                workbook.Close(null, null, null);
                xlApp.Workbooks.Close();
                xlApp.Quit();
                GC.Collect();//强行销毁 
            }
        }

 

private void DownloadFile(string filename)
        {

            //打开要下载的文件
            System.IO.FileStream r = new System.IO.FileStream(filename, System.IO.FileMode.Open);
            //设置基本信息
            Response.Buffer = false;
            Response.AddHeader("Connection", "Keep-Alive");
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + System.IO.Path.GetFileName(filename));
            Response.AddHeader("Content-Length", r.Length.ToString());

            while (true)
            {
                //开辟缓冲区空间
                byte[] buffer = new byte[1024];
                //读取文件的数据
                int leng = r.Read(buffer, 0, 1024);
                if (leng == 0)//到文件尾，结束
                    break;
                if (leng == 1024)//读出的文件数据长度等于缓冲区长度，直接将缓冲区数据写入
                    Response.BinaryWrite(buffer);
                else
                {
                    //读出文件数据比缓冲区小，重新定义缓冲区大小，只用于读取文件的最后一个数据块
                    byte[] b = new byte[leng];
                    for (int i = 0; i < leng; i++)
                        b[i] = buffer[i];
                    Response.BinaryWrite(b);
                }
            }
            r.Close();//关闭下载文件
            Response.End();//结束文件下载
        }