C#数据导出输出Execl格式导出,完整源码例程

ASP.NET 数据管理系统 三层架构

Repeater控件显示出的数据表 导出Excel 形式保存（Microsoft Excel工作表）

下面通过，具体代码实现 数据导出 输出数据 格式导出 （很多时候都是感觉很乱看着看着就糊涂了，其实主要是其中的原理不清晰，下面的几段代码三层架构思路清晰明确）

 

SQL定义:存储过程 [dbo].[ProcGHSExcel]       注：避免风险 (Sql注入等)  能用存储过程就用存储过程

GO

CREATE PROCEDURE [dbo].[ProcGHSExcel]
AS
select Id as 供货商编号,GHSname as 供货商名称,GHSIname as 供货商姓名,Sex as 性别,Zhiwu as 职务,Phone as 电话,CellPhone as 手机,Enail as 电子邮箱,QQMSN as QQMSN,GHSCname as 公司名称,Url as 网址,Dizhi as 地址,YouBian as 邮编,Fax as 传真,Comment as 备注,Yewuyuan as 业务员 from dbo.GHSinfo

GO    --查询表内容

 

DAL层

 

      //导出Excel     存储过程(ProcGHSExcel)
      public static DataSet Excel()
      {
          DataSet ds = DbHelperSQL.Query("ProcGHSExcel");
          return ds;
      }

 

DbHelperSql类    注：自定义底层类文件      （此 DbHelperSql 类源代码是目前网络比较成熟应用的类库之一） 博客中已开源

		/// <summary>
		/// 执行查询语句，返回DataSet
		/// </summary>
		/// <param name="SQLString">查询语句</param>
		/// <returns>DataSet</returns>
		public static DataSet Query(string SQLString)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				DataSet ds = new DataSet();
				try
				{
					connection.Open();					
					SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);						
					command.Fill(ds,"ds");
                    connection.Close();
				}
				catch(System.Data.SqlClient.SqlException ex)
				{				
					throw new Exception(ex.Message);
				}			
				return ds;
			}			
		}

 

BLL层

      //导出Excel
      public static DataSet Excel()
      {
          return SanDAL.GHSServer.Excel();
      }

      public static void CreateExcel(DataSet ds, string fileName)
      {
          //Bll层必须引用System.Web.dll
          //输出形式为excel
         HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
          //指定下载的文件名称
          HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
          //指定字符编码格式（避免出现乱码）
          HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("gb2312");
          DataTable table = ds.Tables[0];
          string header = "";
          string content = "";
          int i;
          //输入标题
          for (i = 0; i < table.Columns.Count - 1; i++)
          {
              //
              header += table.Columns[i].Caption + "\t";
          }
          //标题最后一列要换行
          header += table.Columns[i].Caption + "\n";
          HttpContext.Current.Response.Write(header);
          //输出数据
          foreach (DataRow row in table.Rows)
          {
              for (i = 0; i < table.Columns.Count - 1; i++)
              {
                  content += row[i].ToString() + "\t";
              }
              content += row[i].ToString() + "\n";
          }
          //输出数据的内容
          HttpContext.Current.Response.Write(content);
         HttpContext.Current.Response.End();
      }

 

aspx.cs 表示层触发事件

        protected void Button2_Click(object sender, EventArgs e)
        {
            DataSet ds = SanBLL.GHSManager.Excel();//数据源
            string fileName = "Excel工作表.xls";//导出文件名
            SanBLL.GHSManager.CreateExcel(ds, fileName);
        }

 

aspx 

<span onclick="return confirm('确定导出Excel报表吗？');"> 
        <asp:Button ID="Button2" runat="server" Text="导出Excel" 
            onclick="Button2_Click" /></span>

 

导出数据Execl工作表 同理
下面是更详细的ContentType
'ez' => 'application/andrew-inset',
'hqx' => 'application/mac-binhex40',
'cpt' => 'application/mac-compactpro',
'doc' => 'application/msword',
'bin' => 'application/octet-stream',
'dms' => 'application/octet-stream',
'lha' => 'application/octet-stream',
'lzh' => 'application/octet-stream',
'exe' => 'application/octet-stream',
'class' => 'application/octet-stream',
'so' => 'application/octet-stream',
'dll' => 'application/octet-stream',
'oda' => 'application/oda',
'pdf' => 'application/pdf',
'ai' => 'application/postscript',
'eps' => 'application/postscript',
'ps' => 'application/postscript',
'smi' => 'application/smil',
'smil' => 'application/smil',
'mif' => 'application/vnd.mif',
'xls' => 'application/vnd.ms-excel',
'ppt' => 'application/vnd.ms-powerpoint',
'wbxml' => 'application/vnd.wap.wbxml',
'wmlc' => 'application/vnd.wap.wmlc',
'wmlsc' => 'application/vnd.wap.wmlscriptc',
'bcpio' => 'application/x-bcpio',
'vcd' => 'application/x-cdlink',
'pgn' => 'application/x-chess-pgn',
'cpio' => 'application/x-cpio',
'csh' => 'application/x-csh',
'dcr' => 'application/x-director',
'dir' => 'application/x-director',
'dxr' => 'application/x-director',
'dvi' => 'application/x-dvi',
'spl' => 'application/x-futuresplash',
'gtar' => 'application/x-gtar',
'hdf' => 'application/x-hdf',
'js' => 'application/x-javascript',
'skp' => 'application/x-koan',
'skd' => 'application/x-koan',
'skt' => 'application/x-koan',
'skm' => 'application/x-koan',
'latex' => 'application/x-latex',
'nc' => 'application/x-netcdf',
'cdf' => 'application/x-netcdf',
'sh' => 'application/x-sh',
'shar' => 'application/x-shar',
'swf' => 'application/x-shockwave-flash',
'sit' => 'application/x-stuffit',
'sv4cpio' => 'application/x-sv4cpio',
'sv4crc' => 'application/x-sv4crc',
'tar' => 'application/x-tar',
'tcl' => 'application/x-tcl',
'tex' => 'application/x-tex',
'texinfo' => 'application/x-texinfo',
'texi' => 'application/x-texinfo',
't' => 'application/x-troff',
'tr' => 'application/x-troff',
'roff' => 'application/x-troff',
'man' => 'application/x-troff-man',
'me' => 'application/x-troff-me',
'ms' => 'application/x-troff-ms',
'ustar' => 'application/x-ustar',
'src' => 'application/x-wais-source',
'xhtml' => 'application/xhtml+xml',
'xht' => 'application/xhtml+xml',
'zip' => 'application/zip',
'au' => 'audio/basic',
'snd' => 'audio/basic',
'mid' => 'audio/midi',
'midi' => 'audio/midi',
'kar' => 'audio/midi',
'mpga' => 'audio/mpeg',
'mp2' => 'audio/mpeg',
'mp3' => 'audio/mpeg',
'aif' => 'audio/x-aiff',
'aiff' => 'audio/x-aiff',
'aifc' => 'audio/x-aiff',
'm3u' => 'audio/x-mpegurl',
'ram' => 'audio/x-pn-realaudio',
'rm' => 'audio/x-pn-realaudio',
'rpm' => 'audio/x-pn-realaudio-plugin',
'ra' => 'audio/x-realaudio',
'wav' => 'audio/x-wav',
'pdb' => 'chemical/x-pdb',
'xyz' => 'chemical/x-xyz',
'bmp' => 'image/bmp',
'gif' => 'image/gif',
'ief' => 'image/ief',
'jpeg' => 'image/jpeg',
'jpg' => 'image/jpeg',
'jpe' => 'image/jpeg',
'png' => 'image/png',
'tiff' => 'image/tiff',
'tif' => 'image/tiff',
'djvu' => 'image/vnd.djvu',
'djv' => 'image/vnd.djvu',
'wbmp' => 'image/vnd.wap.wbmp',
'ras' => 'image/x-cmu-raster',
'pnm' => 'image/x-portable-anymap',
'pbm' => 'image/x-portable-bitmap',
'pgm' => 'image/x-portable-graymap',
'ppm' => 'image/x-portable-pixmap',
'rgb' => 'image/x-rgb',
'xbm' => 'image/x-xbitmap',
'xpm' => 'image/x-xpixmap',
'xwd' => 'image/x-xwindowdump',
'igs' => 'model/iges',
'iges' => 'model/iges',
'msh' => 'model/mesh',
'mesh' => 'model/mesh',
'silo' => 'model/mesh',
'wrl' => 'model/vrml',
'vrml' => 'model/vrml',
'css' => 'text/css',
'html' => 'text/html',
'htm' => 'text/html',
'asc' => 'text/plain',
'txt' => 'text/plain',
'rtx' => 'text/richtext',
'rtf' => 'text/rtf',
'sgml' => 'text/sgml',
'sgm' => 'text/sgml',
'tsv' => 'text/tab-separated-values',
'wml' => 'text/vnd.wap.wml',
'wmls' => 'text/vnd.wap.wmlscript',
'etx' => 'text/x-setext',
'xsl' => 'text/xml',
'xml' => 'text/xml',
'mpeg' => 'video/mpeg',
'mpg' => 'video/mpeg',
'mpe' => 'video/mpeg',
'qt' => 'video/quicktime',
'mov' => 'video/quicktime',
'mxu' => 'video/vnd.mpegurl',
'avi' => 'video/x-msvideo',
'movie' => 'video/x-sgi-movie',
'ice' => 'x-conference/x-cooltalk'