| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 46417 人关注过本帖, 13 人收藏
标题:[转帖]将SQL Server中的数据导入到Excel
只看楼主 加入收藏
eastsnake
Rank: 1
等 级:新手上路
帖 子:127
专家分:0
注 册:2005-3-8
收藏
得分:0 
Sub 宏1()' 宏1 Macro' xx 记录的宏 2003-5-1 Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End WithEnd Sub

程序员是男孩,语言是女孩; 每个男孩都希望能交往更多的女孩; 但是却没有一个男孩真正了解一个女孩; 男孩总是不能专心一个女孩,而女孩却总是在变~
2005-04-12 10:41
eastsnake
Rank: 1
等 级:新手上路
帖 子:127
专家分:0
注 册:2005-3-8
收藏
得分:0 

图中表的数据都是供排版参考用的,结束前将实际内容删除掉,即只留下排好版的格式,包括标题、列标题等,将实际内容去掉。将文件保存到一个地方,如D:\Normal.xls,当然,实际开发时,可以放到执行文件所在目录下,为了防止用户随便修改,可以将文件名改为normal.rpt之类。

有了上面的准备,我们就可以在C#中使用了,添加“高级报表”按钮的响应代码。下面是全部代码清单。


程序员是男孩,语言是女孩; 每个男孩都希望能交往更多的女孩; 但是却没有一个男孩真正了解一个女孩; 男孩总是不能专心一个女孩,而女孩却总是在变~
2005-04-12 10:41
eastsnake
Rank: 1
等 级:新手上路
帖 子:127
专家分:0
注 册:2005-3-8
收藏
得分:0 
<!--StartFragment-->using System;

using System.Drawing;

using System.Collections;

using

using System.Windows.Forms;

using System.Data;

 

using

using System.Reflection;

 

namespace MyExcel

{

     /// <summary>

     /// Form1 的摘要说明。

     /// </summary>

     public class Form1 : System.Windows.Forms.Form

     {

         private System.Windows.Forms.Button btnNormal;

         private System.Windows.Forms.Button btnAdvace;

         /// <summary>

         /// 必需的设计器变量。

         /// </summary>

         private components = null;

 

         public Form1()

         {

              //

              // Windows 窗体设计器支持所必需的

              //

              InitializeComponent();

 

              //

              // TOD 在 InitializeComponent 调用后添加任何构造函数代码

              //

         }

 

         /// <summary>

         /// 清理所有正在使用的资源。

         /// </summary>

         protected override void Dispose( bool disposing )
         {

              if( disposing )

              {

                   if (components != null)

                   {

                       components.Dispose();

                   }

              }

              base.Dispose( disposing );

         }

 

         #region Windows Form Designer generated code

         /// <summary>

         /// 设计器支持所需的方法 - 不要使用代码编辑器修改

         /// 此方法的内容。

         /// </summary>

         private void InitializeComponent()

         {

              this.btnNormal = new System.Windows.Forms.Button();

              this.btnAdvace = new System.Windows.Forms.Button();

              this.SuspendLayout();

              //

              // btnNormal

              //

              this.btnNormal.Location = new System.Drawing.Point(49, 55);

              this.btnNormal.Name = "btnNormal";

              this.btnNormal.TabIndex = 0;

              this.btnNormal.Text = "普通报表";

              this.btnNormal.Click += new System.EventHandler(this.btnNormal_Click);

              //

              // btnAdvace

              //

              this.btnAdvace.Location = new System.Drawing.Point(169, 55);

              this.btnAdvace.Name = "btnAdvace";

              this.btnAdvace.TabIndex = 1;

              this.btnAdvace.Text = "高级报表";

              this.btnAdvace.Click += new System.EventHandler(this.btnAdvace_Click);

              //

              // Form1

              //

              this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);

              this.ClientSize = new System.Drawing.Size(292, 133);

              this.Controls.AddRange(new System.Windows.Forms.Control[] {
                    this.btnAdvace,this.btnNormal});

              this.Name = "Form1";

              this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;

              this.Text = "Form1";

              this.ResumeLayout(false);

 

         }

         #endregion

 

         /// <summary>

         /// 应用程序的主入口点。

         /// </summary>

         [STAThread]

         static void Main()

         {

              Application.Run(new Form1());

         }

 

         private string [,] myData=

         {

              {"车牌号","类型","品 牌","型 号","颜 色","附加费证号","车架号"},

              {"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},

              {"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},

              {"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},

              {"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},

              {"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},

              {"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},

              {"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},

              {"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},

              {"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},

              {"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}

         };

 

          //普通报表,即单纯的文件导出功能

         private void btnNormal_Click(object sender, System.EventArgs e)

         {

              //创建一个Excel文件

              Excel.Application myExcel = new Excel.Application ( ) ;

              myExcel.Application.Workbooks.Add ( true ) ;

              //让Excel文件可见

              myExcel.Visible=true;

              //第一行为报表名称

              myExcel.Cells[1,4]="普通报表";

              //逐行写入数据,

              for(int i=0;i<11;i++)
              {
                   for(int j=0;j<7;j++)
                   {
                       //以单引号开头,表示该单元格为纯文本
                       myExcel.Cells[2+i,1+j]="'"+myData[i,j];
                   }
              }
         }

         //高级报表,根据模板生成的报表

         private void btnAdvace_Click(object sender, System.EventArgs e)
         {

              string filename="";

              //将模板文件复制到一个新文件中

              SaveFileDialog mySave=new SaveFileDialog();

              mySave.Filter="Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";

              if(mySave.ShowDialog()!=DialogResult.OK)

              {

                   return;

              }

              else

              {

                   filename=mySave.FileName;

                   //将模板文件copy到新位置,建议实际开发时用相对路径,如
                   //Application.StartupPath.Trim()+"\\report\\normal.xls"

                  

                   string filenameold=mySave.FileName;

                   FileInfo mode=new FileInfo("d:\\normal.xls");

                   try

                   {

                       mode.CopyTo(filename,true);

                   }

                   catch(Exception ee)

                   {

                       MessageBox.Show(ee.Message);

                       return;

                   }

 

              }

 

              //打开复制后的文件

              object missing=Missing.Value;

              Excel.Application myExcel=new Excel.Application ( );

              //打开新文件

              myExcel.Application.Workbooks.Open(filename,missing,missing,missing,missing,
                missing,missing,missing,missing,missing,missing, missing,missing);

              //将Excel显示出来

              myExcel.Visible=true;
              //逐行写入数据,数组中第一行我列标题,忽略

              for(int i=1;i<11;i++)
              {
                   for(int j=0;j<7;j++)
                   {
                       //以单引号开头,表示该单元格为纯文本

                       myExcel.Cells[4+i,1+j]="'"+myData[i,j];

                   }

              }

              //将列标题和实际内容选中

              Excel.Workbook myBook=myExcel.Workbooks[1];

              Excel.Worksheet mySheet=(Excel.Worksheet)myBook.Worksheets[1];

              Excel.Range r=mySheet.get_Range(mySheet.Cells[3,1],mySheet.Cells[14,7]);

              r.Select();

              //=====通过执行宏来格表格加边框=======//

              try

              {

                   myExcel.Run("宏1",missing,missing,

 

                       missing,missing,missing,missing,missing,missing,missing,

 

                       missing,missing,missing,missing,missing,missing,missing,

 

                       missing,missing,missing,missing,missing,missing,missing,

 

                       missing,missing,missing,missing,missing,missing,missing);

              }

              catch

              {

              }

              //保存修改

              myBook.Save();
         }

     }//end of form
}

程序员是男孩,语言是女孩; 每个男孩都希望能交往更多的女孩; 但是却没有一个男孩真正了解一个女孩; 男孩总是不能专心一个女孩,而女孩却总是在变~
2005-04-12 10:52
eastsnake
Rank: 1
等 级:新手上路
帖 子:127
专家分:0
注 册:2005-3-8
收藏
得分:0 
很好的帖子,相信大家看了就明白了

程序员是男孩,语言是女孩; 每个男孩都希望能交往更多的女孩; 但是却没有一个男孩真正了解一个女孩; 男孩总是不能专心一个女孩,而女孩却总是在变~
2005-04-12 10:55
littleh
Rank: 1
等 级:新手上路
帖 子:22
专家分:0
注 册:2005-4-16
收藏
得分:0 
支持

2005-04-17 00:28
wu369
Rank: 1
等 级:新手上路
帖 子:2
专家分:0
注 册:2005-4-23
收藏
得分:0 
let me look look
2005-05-11 15:25
portman
Rank: 1
等 级:新手上路
帖 子:51
专家分:0
注 册:2005-4-22
收藏
得分:0 

庄子曰:“以有限的生命去学习无限的知识,危险啊!”
2005-05-11 17:28
live41
Rank: 10Rank: 10Rank: 10
等 级:贵宾
威 望:67
帖 子:12442
专家分:0
注 册:2004-7-22
收藏
得分:0 
增加了eastsnake 提供的例子。感谢eastsnake
2005-05-12 10:22
live41
Rank: 10Rank: 10Rank: 10
等 级:贵宾
威 望:67
帖 子:12442
专家分:0
注 册:2004-7-22
收藏
得分:0 
帖子加精了,并不是我的主帖精,而是eastsnake的回帖太精彩了,看得我都惭愧。
2005-05-12 10:34
luojianhua
Rank: 1
等 级:新手上路
帖 子:23
专家分:0
注 册:2005-4-24
收藏
得分:0 
好呀!!!
可以学不少东西呀!!!
2005-05-16 13:01
快速回复:[转帖]将SQL Server中的数据导入到Excel
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.043765 second(s), 7 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved