Ako na automatiz�ciu Microsoft Excel z Microsoft Visual C#.NET

Tento �l�nok ukazuje ako vytvori� automatiz�cie klient pre program Microsoft Excel pomocou Microsoft Visual C#.NET.

Automatiz�cia je proces, ktor� umo��uje aplik�ci�, ktor� s� nap�san� v jazykoch ako napr�klad Visual C#.NET programovo ovl�da� in� aplik�cie. Automatiz�cia Excel umo��uje vykon�va� akcie ako vytvori� nov� zo�it, pridanie �dajov do zo�ita alebo vytv�ranie grafov. S Excel a �al��ch aplik�ci�ch bal�ka Microsoft Office, prakticky v�etky akcie �e m��ete vykona� manu�lne pomocou pou��vate�sk�ho rozhrania m��e uskuto�ni� aj pomocou programovania pomocou automatiz�cie.

Program Excel vystavuje tomto programov� funk�nos� prostredn�ctvom objektov�ho modelu. Objektov� model je kolekcia tried a met�d, ktor� sl��ia ako n�protivkami logick� komponentov Excel. Napr�klad, existuje Uplat�ovanie objekt, Zo�it objekt, a Funkcia pracovn�ho h�rka objekt, z ktor�ch ka�d� obsahova� funk�nos� tieto kusy programu Excel. Pr�stup k objektov�mu modelu z Visual C#.NET, m��ete nastavi� projektu odkaz na kni�nice typov.

Tento �l�nok ukazuje ako nastavi� riadne projektu odkaz na program Excel typ kni�nice pre Visual C#.NET a poskytuje vzorky k�d na automatiz�ciu programu Excel.

Vytvori� automatiza�n�ch klientov programu Microsoft Excel

1. Spusti� Microsoft Visual Studio.NET.
2. Na S�bor ponuky, kliknite na tla�idlo Nov�, a potom kliknite na tla�idlo Projekt. Vyberte Windows aplik�cie z typov Visual C# projektu. Form1 sa vytvor� na z�klade predvolen�ho nastavenia.
3. Prida� odkaz na Objektov� kni�nica programu Microsoft Excel. Chcete urobi�, postupujte nasledovne:
   1. Na Projekt ponuky, kliknite na tla�idlo Prida� odkaz.
   2. Na COM kartu, vyh�adajte Programu Microsoft Excel Objektov� kni�nica, a kliknite na tla�idlo Vyberte.
      
      Pozn�mka Microsoft Office 2003 obsahuje Primary Interop Assemblies (PIA). Microsoft Office XP neobsahuje Pia, ale m��u by� stiahnut�.�al�ie inform�cie o Office XP Pia, po kliknut� na nasledovn� ��slo �l�nku datab�zy Microsoft Knowledge Base:
      328912

      (http://support.microsoft.com/kb/328912/ )

      Microsoft Office XP primary objektov�mu assemblies (PIA) s� k dispoz�cii na prevzatie
   3. Kliknite na tla�idlo ok v Prida� odkazy dial�gov� okno prij�ma� va�e v�bery. Ak sa zobraz� v�zva na generova� medzivrstva pre kni�nice, ktor� ste vybrali, kliknite na tla�idlo �no.
4. Na Zobrazenie menu vyberte Toolbox zobrazenie panela s n�strojmi a potom prida� tla�idlo, na Form1.
5. Dvakr�t kliknite na Button1. Zobraz� sa okno k�d pre formul�r.
6. V okne k�d nahradi� nasleduj�ci k�d

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

   s:

   private void button1_Click(object sender, System.EventArgs e)
   {
   	Excel.Application oXL;
   	Excel._Workbook oWB;
   	Excel._Worksheet oSheet;
   	Excel.Range oRng;
   
   	try
   	{
   		//Start Excel and get Application object.
   		oXL = new Excel.Application();
   		oXL.Visible = true;
   
   		//Get a new workbook.
   		oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
   		oSheet = (Excel._Worksheet)oWB.ActiveSheet;
   
   		//Add table headers going cell by cell.
   		oSheet.Cells[1, 1] = "First Name";
   		oSheet.Cells[1, 2] = "Last Name";
   		oSheet.Cells[1, 3] = "Full Name";
   		oSheet.Cells[1, 4] = "Salary";
   
   		//Format A1:D1 as bold, vertical alignment = center.
   		oSheet.get_Range("A1", "D1").Font.Bold = true;
   		oSheet.get_Range("A1", "D1").VerticalAlignment = 
   			Excel.XlVAlign.xlVAlignCenter;
   		
   		// Create an array to multiple values at once.
   		string[,] saNames = new string[5,2];
   		
   		saNames[ 0, 0] = "John";
   		saNames[ 0, 1] = "Smith";
   		saNames[ 1, 0] = "Tom";
   		saNames[ 1, 1] = "Brown";
   		saNames[ 2, 0] = "Sue";
   		saNames[ 2, 1] = "Thomas";
   		saNames[ 3, 0] = "Jane";
   		saNames[ 3, 1] = "Jones";
   		saNames[ 4, 0] = "Adam";
   		saNames[ 4, 1] = "Johnson";
   
           	//Fill A2:B6 with an array of values (First and Last Names).
   	        oSheet.get_Range("A2", "B6").Value2 = saNames;
   
   		//Fill C2:C6 with a relative formula (=A2 & " " & B2).
   		oRng = oSheet.get_Range("C2", "C6");
   		oRng.Formula = "=A2 & \" \" & B2";
   
   		//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
   		oRng = oSheet.get_Range("D2", "D6");
   		oRng.Formula = "=RAND()*100000";
   		oRng.NumberFormat = "$0.00";
   
   		//AutoFit columns A:D.
   		oRng = oSheet.get_Range("A1", "D1");
   		oRng.EntireColumn.AutoFit();
   
   		//Manipulate a variable number of columns for Quarterly Sales Data.
   		DisplayQuarterlySales(oSheet);
   
   		//Make sure Excel is visible and give the user control
   		//of Microsoft Excel's lifetime.
   		oXL.Visible = true;
   		oXL.UserControl = true;
   	}
   	catch( Exception theException ) 
   	{
   		String errorMessage;
   		errorMessage = "Error: ";
   		errorMessage = String.Concat( errorMessage, theException.Message );
   		errorMessage = String.Concat( errorMessage, " Line: " );
   		errorMessage = String.Concat( errorMessage, theException.Source );
   
   		MessageBox.Show( errorMessage, "Error" );
   	}
   }
   
   private void DisplayQuarterlySales(Excel._Worksheet oWS)
   {
   	Excel._Workbook oWB;
   	Excel.Series oSeries;
   	Excel.Range oResizeRange;
   	Excel._Chart oChart;
   	String sMsg;
   	int iNumQtrs;
   
   	//Determine how many quarters to display data for.
   	for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--)
   	{
   		sMsg = "Enter sales data for ";
   		sMsg = String.Concat( sMsg, iNumQtrs );
   		sMsg = String.Concat( sMsg, " quarter(s)?");
   
   		DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?", 
   			MessageBoxButtons.YesNo );
   		if (iRet == DialogResult.Yes)
   			break;
   	}
   
   	sMsg = "Displaying data for ";
   	sMsg = String.Concat( sMsg, iNumQtrs );
   	sMsg = String.Concat( sMsg, " quarter(s)." );
   
   	MessageBox.Show( sMsg, "Quarterly Sales" );
   
   	//Starting at E1, fill headers for the number of columns selected.
   	oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs);
   	oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";
   
   	//Change the Orientation and WrapText properties for the headers.
   	oResizeRange.Orientation = 38;
   	oResizeRange.WrapText = true;
   
   	//Fill the interior color of the headers.
   	oResizeRange.Interior.ColorIndex = 36;
   
   	//Fill the columns with a formula and apply a number format.
   	oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs);
   	oResizeRange.Formula = "=RAND()*100";
   	oResizeRange.NumberFormat = "$0.00";
   
   	//Apply borders to the Sales data and headers.
   	oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs);
   	oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin;
   
   	//Add a Totals formula for the sales data and apply a border.
   	oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs);
   	oResizeRange.Formula = "=SUM(E2:E6)";
   	oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle 
   		= Excel.XlLineStyle.xlDouble;
   	oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight 
   		= Excel.XlBorderWeight.xlThick;
   
   	//Add a Chart for the selected data.
   	oWB = (Excel._Workbook)oWS.Parent;
   	oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value, 
   		Missing.Value, Missing.Value );
   
   	//Use the ChartWizard to create a new chart from the selected data.
   	oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize( 
   		Missing.Value, iNumQtrs);
   	oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,
   		Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value, 
   		Missing.Value, Missing.Value, Missing.Value, Missing.Value );
   	oSeries = (Excel.Series)oChart.SeriesCollection(1);
   	oSeries.XValues = oWS.get_Range("A2", "A6");
   	for( int iRet = 1; iRet <= iNumQtrs; iRet++)
   	{
   		oSeries = (Excel.Series)oChart.SeriesCollection(iRet);
   		String seriesName;
   		seriesName = "=\"Q";
   		seriesName = String.Concat( seriesName, iRet );
   		seriesName = String.Concat( seriesName, "\"" );
   		oSeries.Name = seriesName;
   	}														  
   	
   	oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );
   
   	//Move the chart so as not to cover your data.
   	oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );
   	oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;
   	oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );
   	oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
   }
   					

7. Prejdite na hornej �asti okna pre k�d. Dopln� sa nasleduj�ce riadok na koniec zoznamu pomocou smern�c:

   using Excel = Microsoft.Office.Interop.Excel;
   using System.Reflection; 
   					

Test automatiza�n�ch klientov

1. Stla�te kl�ves F5 stava� a spustite program.
2. Kliknite na tla�idlo Button1 vo formul�ri. Program spust� program Excel a vypln� �daje na novom Funkcia pracovn�ho h�rka.
3. Ke� sa zobraz� v�zva na zadanie �tvr�ro�n�ch �dajov o predaji, kliknite na tla�idlo �no. Graf, ktor� je prepojen� s �tvr�ro�n�ch �dajov sa prid� do Funkcia pracovn�ho h�rka.

Pre viac inform�ci� nav�t�vte nasleduj�ce Microsoft Developer Webov� lokalita spolo�nosti Network (MSDN):