Excel开发系列一

微软对Excel的二次开发有多种模式，包括VBA(visual studio for application)，.net2003中的对象模型（com组件，excel object 11 library）以及vs.net2005中的vsto(visual studio tool for office).在这个项目中，我们现在采用的是使用.net2003种的对象模型。因为考虑到vsto中可能技术不是大成熟，并且技术文档方面缺乏参考，所以使用较成熟的vs.net2003office 对象模型来开发。

对象模型参考msdn上的Excel对象模型，首先是要建立一个层级的概念，并且对excel的使用，各种功能要知道。好，废话少说，现来个实际的例子。

在winform中，创建一个button，在这个按钮的点击事件方法中写下：

   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;

 

这个样，你就可以建立简单的生成excel程序，并写入数据，和建立图表。下节课，将综合各个Excel开发对象模型中的各个对象，来一个个讲解其中一些重要的属性和方法等。