form1.cs

在.net平台中实现根据Excel模板来生成相同样式的Excel数据文件

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

using System.IO;
using System.Reflection;
using AxDSOFramer;
using Excel;

namespace test1
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.Button button1;
		private System.Windows.Forms.Button button2;
		private AxDSOFramer.AxFramerControl axFramerControl1;
		private System.Windows.Forms.Button button3;
		private System.Windows.Forms.Button button4;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public Form1()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
			axFramerControl1.Dispose();
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(Form1));
			this.button1 = new System.Windows.Forms.Button();
			this.button2 = new System.Windows.Forms.Button();
			this.axFramerControl1 = new AxDSOFramer.AxFramerControl();
			this.button3 = new System.Windows.Forms.Button();
			this.button4 = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.axFramerControl1)).BeginInit();
			this.SuspendLayout();
			// 
			// button1
			// 
			this.button1.Location = new System.Drawing.Point(16, 8);
			this.button1.Name = "button1";
			this.button1.Size = new System.Drawing.Size(80, 23);
			this.button1.TabIndex = 1;
			this.button1.Text = "dsoframer";
			this.button1.Click += new System.EventHandler(this.button1_Click);
			// 
			// button2
			// 
			this.button2.Location = new System.Drawing.Point(120, 8);
			this.button2.Name = "button2";
			this.button2.Size = new System.Drawing.Size(104, 23);
			this.button2.TabIndex = 2;
			this.button2.Text = "WebBrowser";
			this.button2.Click += new System.EventHandler(this.button2_Click);
			// 
			// axFramerControl1
			// 
			this.axFramerControl1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
				| System.Windows.Forms.AnchorStyles.Left) 
				| System.Windows.Forms.AnchorStyles.Right)));
			this.axFramerControl1.Enabled = true;
			this.axFramerControl1.Location = new System.Drawing.Point(0, 48);
			this.axFramerControl1.Name = "axFramerControl1";
			this.axFramerControl1.OcxState = ((System.Windows.Forms.AxHost.State)(resources.GetObject("axFramerControl1.OcxState")));
			this.axFramerControl1.Size = new System.Drawing.Size(568, 280);
			this.axFramerControl1.TabIndex = 0;
			// 
			// button3
			// 
			this.button3.Location = new System.Drawing.Point(240, 8);
			this.button3.Name = "button3";
			this.button3.Size = new System.Drawing.Size(104, 23);
			this.button3.TabIndex = 3;
			this.button3.Text = "Crystal Report";
			this.button3.Click += new System.EventHandler(this.button3_Click);
			// 
			// button4
			// 
			this.button4.Location = new System.Drawing.Point(368, 8);
			this.button4.Name = "button4";
			this.button4.Size = new System.Drawing.Size(112, 23);
			this.button4.TabIndex = 3;
			this.button4.Text = "Crystal Report1";
			this.button4.Click += new System.EventHandler(this.button4_Click);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(568, 325);
			this.Controls.Add(this.button3);
			this.Controls.Add(this.axFramerControl1);
			this.Controls.Add(this.button2);
			this.Controls.Add(this.button1);
			this.Controls.Add(this.button4);
			this.Name = "Form1";
			this.Text = "Form1";
			((System.ComponentModel.ISupportInitialize)(this.axFramerControl1)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			System.Windows.Forms.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 button1_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];
//				}
//			} 
//			myExcel.Save("d:\\temp.xls");

			Excel.ApplicationClass my=new Excel.ApplicationClass();   
			Excel.Workbook mybook=null;   
			Excel.Worksheet mysheet=null;   
			object objMissing=System.Reflection.Missing.Value;   
			mybook = my.Workbooks.Add(objMissing);
			mysheet = (Excel.Worksheet)mybook.Worksheets[1];//.Add(objMissing,objMissing,1,objMissing);   
    
			string fileName="";
			//excel.DisplayAlerts=false;objMissing,objMissing,objMissing,objMissing   
			try   
			{   
				//第一行为报表名称
				mysheet.Cells[1, 4] = "普通报表";
				//逐行写入数据，
				for (int i = 0; i < 11; i++)
				{
					for (int j = 0; j < 7; j++)
					{
						//以单引号开头，表示该单元格为纯文本
						mysheet.Cells[2 + i, 1 + j] = "'" + myData[i, j];
					}
				}
				fileName = "d:\\" + Guid.NewGuid().ToString() + ".xls";
				mysheet.SaveAs(fileName,objMissing,objMissing,objMissing,objMissing,objMissing,objMissing,objMissing,objMissing);
			}   
			catch(Exception ex)   
			{   
				MessageBox.Show(ex.Message.ToString());   
			}   
			finally   
			{
				my.Quit();
				//垃圾回收
				System.GC.Collect();   
			}   

			//string strFileName = @"d:\temp.xls";
           	//this.axFramerControl1.Open(strFileName,true,null,null,null);
			this.axFramerControl1.Open(fileName,true,null,null,null);
            //axFramerControl1.Dispose();
		}

		private void button2_Click(object sender, System.EventArgs e)
		{
			WebBrowser frm = new WebBrowser();
			frm.Show();
		}

		private void button3_Click(object sender, System.EventArgs e)
		{
			CrystalReport frm = new CrystalReport();
			frm.Show();		
		}

		private void button4_Click(object sender, System.EventArgs e)
		{
			CrystalReportTest1 frm = new CrystalReportTest1();
			frm.Show();	
		}
	}
}