.NET中将数据导出（导入）Excel文件

               我们在WinForm应用程序或者Web程序中都会遇到需要将DataGridView或者DataSet中的数据导出成为Excel类型的.xls文件。下面就通过一个示例向大家演示在WinForm程序中如何将DataGridView中的数据导出为Excel文件，从DataSet中将数据导出为Excel的方法跟这个差不多的，这里就不再介绍了。

?

Private void DataGridViewToExcel(DataGridView dgv)

{

            SaveFileDialog sfDialog = new SaveFileDialog();//保存导出的excel对话框

            sfDialog.Filter = "Excel Files(*.xls)|*.xls";//文件过滤器，只能保存为.xls类型的文件

            sfDialog.CheckFileExists = false;//如果用户指定不存在的文件名是否提示

            sfDialog.CheckPathExists = false;//如果用户指定不存在的路径是否提示

            sfDialog.FilterIndex = 0;

            sfDialog.RestoreDirectory = true;

            sfDialog.CreatePrompt = true;//如果该文件不存在则提示用户创建新文件

            sfDialog.Title = "保存为Excel 文件！";

             if (sfDialog.ShowDialog() == DialogResult.OK)

            {

                Stream saveStream = sfDialog.OpenFile();//打开要保存的excel文件

                StreamWriter sw = new StreamWriter(saveStream, Encoding.GetEncoding(-0));//以特定的编码向流中插入字符，GetEncoding（-0）<br>首选编码的代码页标识符。- 或 - 0

                string columnTitle = "";

                try

                {

                    for (int i = 0; i < dgv.ColumnCount; i++)

                    {

                        if (i > 0)

                            columnTitle += "\t";

                        columnTitle += dgv.Columns[i].HeaderText;

                    }

                    sw.WriteLine(columnTitle);//写入标题行

                    for (int j = 0; j < dgv.RowCount; j++)

                    {

                        string columnValue="";

                        for(int k=0;k<dgv.ColumnCount;k++)

                        {

                            if(k>0)

                            columnValue += "\t";

                            if (dgv.Rows[j].Cells[k].Value.ToString() == "")

                            {

                                columnValue += "";

                            }

                            else

columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();

                        }

                        sw.WriteLine(columnValue);//将信息逐条的写入excel文件

                    }

                    sw.Close();

                    saveStream.Close();

                }

                catch(Exception e)

                {

                    MessageBox.Show(e.Message);

                }

            }

}

下面向大家演示一下如何在Asp.net中如何将数据导出为Excel 文件，这里只是做一个简单的演示，所以并没有太多的布局，页面只有一个按钮，我们在按钮里处理事件如下：

?

protected void btndstoexcel_Click(object sender, EventArgs e)

        {

            using (SqlConnection sqlConn = new SqlConnection("Initial Catalog=Manager;Integrated Security=true;data source=."))

            {

                sqlConn.Open();

                SqlCommand sqlCmd = new SqlCommand("select * from associator",sqlConn);

                SqlDataAdapter sdAdapter = new SqlDataAdapter(sqlCmd);

                DataSet ds = new DataSet();

                sdAdapter.Fill(ds, "Result");//连接数据库，并将信息读进DataSet中

                Response.ContentEncoding = Encoding.GetEncoding("GB2312");//设置编码格式

           Response.ContentType="application/vnd.ms-excel";//设置输入类型为Excel文件，指定返回的是一个不能被客户端读取的流，必须被下载

                Response.AddHeader("Content-Disposition", "attachment;filename=Test.xls");//添加Http表头，将文件保存为Test.xls

                string columnHeader = "";//保存表头字符

                string columnContent = "";//保存每行的数据内容

                DataTable dsTable = ds.Tables["Result"];

                for (int i = 0; i < dsTable.Columns.Count; i++)

                {

                        if (i == dsTable.Columns.Count - 1)

                            columnHeader += dsTable.Columns[i].Caption.ToString()+"\n";//当当前列为最后一列时要换行

                        else

                           columnHeader += dsTable.Columns[i].Caption.ToString()+"\t";    

                }

                Response.Write(columnHeader);

  //添加每行的数据信息           

    foreach(DataRow dr in dsTable.Rows)

                {

                    for(int j=0;j<dsTable.Columns.Count;j++)

                    {

                        if (j == dsTable.Columns.Count - 1)

                            columnContent += dr[j] + "\n";//当当前列为最后一列时换行

                        else

                            columnContent += dr[j] + "\t";

                    }

                    Response.Write(columnContent);

                    columnContent = "";

                }

                Response.End();  

            }

        }

说完了数据导出，我们再来谈一下数据的导入，这里演示从Excel文件中读取数据，代码很简单，在这里就不做过多的累述了。希望可以给大家有所启发

?

private void ExcelToDataGridView(DataGridView dgv)

       {

           OpenFileDialog dlg = new OpenFileDialog();

           dlg.Filter = "Execl files (*.xls)|*.xls";

           dlg.CheckFileExists = false;

           dlg.CheckPathExists = false;

           dlg.FilterIndex = 0;

           dlg.RestoreDirectory = true;

           dlg.Title = "将Excel文件数据导入到DataSet";

           dlg.ShowDialog();

 

           DataSet ds = new DataSet();

           string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dlg.FileName.Trim() + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";

           using (OleDbConnection OleConn = new OleDbConnection(strConn))

           {

               OleConn.Open();

               String sql = "SELECT * FROM [Sheet1$]";

               OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);

               OleDaExcel.Fill(ds);

               OleConn.Close();

           }

               dgv.DataSource = ds.Tables[0].DefaultView;

       }

　　好了这一节我们就到这里了，主要分别演示了在WinForm和web不同的环境中数据的导出为Excel和在WinForm中从Execel文件中读取数据，主要还是围绕Excel的数据导入导出，平时用的地方也有不少，希望能对大家有所启发吧，当然还有其他的方法，在这里就不再讲了，这里只是讲了比较简单常用的方法。有兴趣的朋友可以在下边自己研究