.NET中将数据导出(导入)Excel文件

[来源] 达内    [编辑] 达内   [时间]2012-11-01

我们在WinForm应用程序或者Web程序中都会遇到需要将DataGridView或者DataSet中的数据导出成为Excel类型的.xls文件

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

< div style="margin: 0px; padding: 0px; background-color: rgb(244, 237, 227); color: rgb(73, 73, 73); font-family: Arial, Helvetica, sans-serif; font-size: 14px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 22px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; " class="cnblogs_Highlighter">
< div style="margin: 0px !important; padding: 0px !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: none !important; bottom: auto !important; float: none !important; height: 11px !important; left: auto !important; line-height: 2em !important; outline: 0px !important; overflow: visible !important; position: absolute !important; right: 1px !important; text-align: left !important; top: 1px !important; vertical-align: baseline !i mportant; width: 11px !important; box-sizing: content-box !important; font-family: 'Courier New', Consolas, 'Bitstream Vera Sans Mono', Courier, monospace !important; font-weight: normal !important; font-style: normal !important; font-size: 10px !important; min-height: inherit !important; z-index: 10 !important; color: rgb(255, 255, 255) !important; background-position: initial initial !important; background-repeat: initial initial !important; " class="toolbar"> ?
< table cellspacing="0" cellpadding="0" border="0" style="margin: 0px !important; padding: 0px !important; border-spacing: 0px; border-collapse: collapse; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-rad ius: 0px !important; background-image: none !important; border: 1px solid rgb(192, 192, 192); bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 2em !important; outline: 0px !important; overflow: visible !important; position: static !importa nt; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: 690px; box-sizing: content-box !important; font-family: 'Courier New', Consolas, 'Bitstream Vera Sans Mono', Courier, monospace !important; font-weight: normal !important; font-style: normal !important; font-size: 12px !important; min-height: inherit !important; background-position: initial initial !important; background-repeat: initial initial !important; ">
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);
                 }
             }
}
< p style="margin: 0pt auto; padding: 0px; text-indent: 0px; color: rgb(73, 73, 73); font-family: Arial, Helvetica, sans-serif; font-size: 14px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 22px; orphans: 2; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: rgb(244, 237, 227); text-align: left; " class="p0"> 下面向大家演示一下如何在Asp.net中如何将数据导出为 Excel  文件,这里只是做一个简单的演示,所以并没有太多的布局,页面只有一个按钮,我们在按钮里处理事件如下:

< div style="margin: 0px; padding: 0px; background-color: rgb(244, 237, 227); color: rgb(73, 73, 73); font-family: Arial, Helvetica, sans-serif; font-size: 14px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 22px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; " class="cnblogs_Highlighter">
< div style="margin: 0px !important; padding: 0px !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: none !important; bottom: auto !important; float: none !important; height: 11px !important; left: auto !important; line-height: 2em !important; outline: 0px !important; overflow: visible !important; position: absolute !important; right: 1px !important; text-align: left !important; top: 1px !important; vertical-align: baseline !i mportant; width: 11px !important; box-sizing: content-box !important; font-family: 'Courier New', Consolas, 'Bitstream Vera Sans Mono', Courier, monospace !important; font-weight: normal !important; font-style: normal !important; font-size: 10px !important; min-height: inherit !important; z-index: 10 !important; color: rgb(255, 255, 255) !important; background-position: initial initial !important; background-repeat: initial initial !important; " class="toolbar"> ?
< table cellspacing="0" cellpadding="0" border="0" style="margin: 0px !important; padding: 0px !important; border-spacing: 0px; border-collapse: collapse; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-rad ius: 0px !important; background-image: none !important; border: 1px solid rgb(192, 192, 192); bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 2em !important; outline: 0px !important; overflow: visible !important; position: static !importa nt; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: 690px; box-sizing: content-box !important; font-family: 'Courier New', Consolas, 'Bitstream Vera Sans Mono', Courier, monospace !important; font-weight: normal !important; font-style: normal !important; font-size: 12px !important; min-height: inherit !important; background-position: initial initial !important; background-repeat: initial initial !important; ">
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();  
             }
         }
< p style="margin: 10px auto; padding: 0px; text-indent: 0px; color: rgb(73, 73, 73); font-family: Arial, Helvetica, sans-serif; font-size: 14px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 22px; orphans: 2; text-align: -webkit-auto; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: rgb(244, 237, 227); "> 说完了数据导出,我们再来谈一下数据的导入,这里演示从Excel文件中读取数据,代码很简单,在这里就不做过多的累述了。希望可以给大家有所启发

< div style="margin: 0px; padding: 0px; background-color: rgb(244, 237, 227); color: rgb(73, 73, 73); font-family: Arial, Helvetica, sans-serif; font-size: 14px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 22px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; " class="cnblogs_Highlighter">
< div style="margin: 0px !important; padding: 0px !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; background-image: none !important; border: none !important; bottom: auto !important; float: none !important; height: 11px !important; left: auto !important; line-height: 2em !important; outline: 0px !important; overflow: visible !important; position: absolute !important; right: 1px !important; text-align: left !important; top: 1px !important; vertical-align: baseline !i mportant; width: 11px !important; box-sizing: content-box !important; font-family: 'Courier New', Consolas, 'Bitstream Vera Sans Mono', Courier, monospace !important; font-weight: normal !important; font-style: normal !important; font-size: 10px !important; min-height: inherit !important; z-index: 10 !important; color: rgb(255, 255, 255) !important; background-position: initial initial !important; background-repeat: initial initial !important; " class="toolbar"> ?
< table cellspacing="0" cellpadding="0" border="0" style="margin: 0px !important; padding: 0px !important; border-spacing: 0px; border-collapse: collapse; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-rad ius: 0px !important; background-image: none !important; border: 1px solid rgb(192, 192, 192); bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 2em !important; outline: 0px !important; overflow: visible !important; position: static !importa nt; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: 690px; box-sizing: content-box !important; font-family: 'Courier New', Consolas, 'Bitstream Vera Sans Mono', Courier, monospace !important; font-weight: normal !important; font-style: normal !important; font-size: 12px !important; min-height: inherit !important; background-position: initial initial !important; background-repeat: initial initial !important; ">
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;
        }
< p style="margin: 10px auto; padding: 0px; text-indent: 0px; color: rgb(73, 73, 73); font-family: Arial, Helvetica, sans-serif; font-size: 14px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 22px; orphans: 2; text-align: -webkit-auto; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: rgb(244, 237, 227); ">   好了这一节我们就到这里了,主要分别演示了在WinForm和web不同的环境中数据的导出为Excel和在WinFor m中从Execel文件中读取数据,主要还是围绕Excel的数据导入导出,平时用的地方也有不少,希望能对大家有所启发吧,当然还有其他的方法,在这里就不再讲了,这里只是讲了比较简单常用的方法。有兴趣的朋友可以在下边自己研究

资源下载