如何建立/格式使用 Visual J++ 自動化 Excel 活頁簿

文章翻譯 文章翻譯
文章編號: 219430 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

結論

本文將告訴您,自動化 Excel 97 或更新版本從用 Visual J++ 戶端使用 [Java 程式語言。專案會使用 Excel 型別程式庫,並說明了物件導向程式設計。

其他相關資訊

這份文件與並行描述相同的處理程序使用 Microsoft Visual C++ 和 MFC 的微軟知識庫文件。取得更多資訊有關自動化的 Excel 從 Visual C++ 按一下 [下面的文件編號,檢視 「 Microsoft 知識庫 」 中的發行項]:
179706如何使用 MFC 來自動化 Excel 和建立/格式新的活頁簿
  1. 啟動 Visual J++ 6.0。建立新的主控台應用程式專案,並命名 XLJava1。
  2. 在 [專案總管] 視窗中,開啟您的專案樹狀目錄,並連按兩下 Class1.java 檔案為您建立。
  3. 從 [專案] 功能表選擇 [加入 COM 包裝函式、 選取 Microsoft Excel 8.0、 9.0,或 10.0 (視需要) 物件程式庫,] 然後按一下 [確定]]。這將衍生自 Excel 型別程式庫專案 Java COM 包裝函式。
  4. Class1.Java 檔案頂端加入下列的匯入陳述式:
    
       import Excel8.*; // Change the 8 to 9 for Excel 2000, or 10 for 2002 or later
       // For Excel 2000 the statement should read
       // import Excel9.*; // Excel support
       import com.ms.com.*; // Variant & exception support. 
       import java.lang.InterruptedException; // Needed for Thread.sleep().
       (These are illustrated in the code sample below.)
    					
  5. 在類別 1 進入點和 main () 函式,加入程式碼,這樣程式就會出現,如下所示:
         /**
     * This class can take a variable number of parameters on the command
     * line. Program execution begins with the main() method. The class
     * constructor is not invoked unless an object of type 'Class1'
     * created in the main() method.
     */ 
    import excel.*; // for Excel 2002, or - import excel9.*; for Excel 2000 or later
                      // or - import excel8.*; for Excel 97
    import java.lang.InterruptedException; // needed for thread.sleep
    import com.ms.com.*; // Variant & exception support<BR/>
    public class Class1
    {
    	/**
    	 * The main entry point for the application. 
    	 *
    	 * @param args Array of parameters passed to the application
    	 * via the command line.
    	 */ 
    	//==============================================================
    	// Add the following static member function declaration 
        // just before main()...
        // J/Direct declarations... to use Windows MessageBox
        /** @dll.import("USER32") */ 
        private static native int MessageBox(int hwndOwner, String text, 
                                             String title, int fuStyle);
        // Here is an example of its use.
        // MessageBox(0, "Hello, World", "Title", 0); //fuStyle switch
        //    values are defined in Winuser.h. Search on 'MB_OK'
        //==============================================================
    	public static void main (String[] args)
    	{
    		// Force COM objects to be created on the current thread.
          // Otherwise, older VMs might not release all references
          // and Excel might continue to run after you shutdown.
          ComLib.declareMessagePumpThread();
          // Launch Excel 
          Variant vEmpty = new Variant();
          vEmpty.noParam();
          Application xlApp = new Application();
          //xlApp.setVisible(0,true); // Excel 2000 & 97 1st param is LCID, Locale ID
    	  xlApp.setVisible(true); // One argument for Excel 2002 or later
       	
          // Get the workbook object via the object model.
          Workbooks books = xlApp.getWorkbooks();
          _Workbook book = books.Add(vEmpty,0);
          // Get the first sheet.		
          Sheets sheets = (Sheets)book.getSheets();
          _Worksheet sheet = (_Worksheet)sheets.getItem(new Variant(1));
          // Fill cells A1, B1, C1, and D1, one cell at a time, with "headers".
          Range range = sheet.getRange(new Variant("A1"),new Variant("A1"));
          //range.setValue(new Variant("First Name")); // Throughout, the SetValue
          // of Excel 97 and Excel 2000 no longer works in Excel 2002 or later.
    	  range.setValue2(new Variant("First Name"));
          range = sheet.getRange(new Variant("B1"),new Variant("B1"));
          //range.setValue(new Variant("Last Name"));
    	  range.setValue2(new Variant("Last Name"));
          range = sheet.getRange(new Variant("C1"),new Variant("C1"));
          //range.setValue(new Variant("Full Name"));
    	  range.setValue2(new Variant("Full Name"));
          range = sheet.getRange(new Variant("D1"),new Variant("D1"));
          //range.setValue(new Variant("Salary"));
    	  range.setValue2(new Variant("Salary"));
          // Format A1:D1 as bold, vertical alignment = center
          range = sheet.getRange(new Variant("A1"),new Variant("D1"));
          Font font = range.getFont();
          font.setBold(new Variant(true));
          range.setVerticalAlignment(new Variant(-4108));
          // Fill range A2:B6 with first and last names, 
          range = sheet.getRange(new Variant("A2"),new Variant("A2"));
          //range.setValue(new Variant("John"));
    	  range.setValue2(new Variant("John"));
          range = sheet.getRange(new Variant("B2"),new Variant("B2"));
          //range.setValue(new Variant("Smith"));
    	  range.setValue2(new Variant("Smith"));
          range = sheet.getRange(new Variant("A3"),new Variant("A3"));
          //range.setValue(new Variant("Tom"));
    	  range.setValue2(new Variant("Tom"));
          range = sheet.getRange(new Variant("B3"),new Variant("B3"));
          //range.setValue(new Variant("Brown"));
    	  range.setValue2(new Variant("Brown"));
          range = sheet.getRange(new Variant("A4"),new Variant("A4"));
          //range.setValue(new Variant("Sue"));
    	  range.setValue2(new Variant("Sue"));
          range = sheet.getRange(new Variant("B4"),new Variant("B4"));
          //range.setValue(new Variant("Thomas"));
    	  range.setValue2(new Variant("Thomas"));
          range = sheet.getRange(new Variant("A5"),new Variant("A5"));
          //range.setValue(new Variant("Jane"));
    	  range.setValue2(new Variant("Jane"));
          range = sheet.getRange(new Variant("B5"),new Variant("B5"));
          //range.setValue(new Variant("Jones"));
    	  range.setValue2(new Variant("Jones"));
          range = sheet.getRange(new Variant("A6"),new Variant("A6"));
          //range.setValue(new Variant("Adam"));
    	  range.setValue2(new Variant("Adam"));
          range = sheet.getRange(new Variant("B6"),new Variant("B6"));
          //range.setValue(new Variant("Johnson"));
    	  range.setValue2(new Variant("Johnson"));
          // Fill range C2:C6 with a relative formula(=A2 & " " & B2")
          range = sheet.getRange(new Variant("C2"),new Variant("C6"));
          range.setFormula(new Variant("=A2 & \" \" & B2"));
          // Fill D2:D6 with a formula (=RAND()*100000) and apply a 
          //  number format
          range = sheet.getRange(new Variant("D2"),new Variant("D6"));
          range.setFormula(new Variant("=RAND()*100000"));
          range.setNumberFormat(new Variant("$0.00"));
          // AutoFit columns A:D
          range = sheet.getRange(new Variant("A1"),new Variant("D1"));
          Range columns = range.getEntireColumn();
          columns.AutoFit();
          // Manipulate a variable number of columns
          //  for Quarterly Sales Data
          short NumQtrs;
          String msg;
          String msg2;
          msg2 = " "; // Initialize it to avoid compile error
          int reply;
          Range resizedrange;
          Interior interior;
          Borders borders;
          // Determine how many quarters to display data for.
          for(NumQtrs = 1; NumQtrs<=4; NumQtrs++)
          {
             if (NumQtrs == 1)
             {
              msg = "Enter sales data for " + NumQtrs + " quarter?";
              msg2 = "Displaying data for " + NumQtrs + " quarter";
             }
             else
             {
              msg = "Enter sales data for " + NumQtrs + " quarters?";
              msg2 = "Displaying data for " + NumQtrs + " quarters";
             }
             reply = MessageBox(0, msg, "Indicate Quarters", 36);
             if(6 == reply) break;			
          } // end For loop
       		
          MessageBox(0, msg2, "Data Setting", 0);
          if(5 == NumQtrs) // Limit display to <=4 quarters
          {
           NumQtrs = 4;
          }
          // Starting at E1, fill headers for number of columns selected.
          range = sheet.getRange(new Variant("E1"),new Variant("E1"));
          resizedrange = range.getResize(vEmpty, new Variant(NumQtrs));
          resizedrange.setFormula(
             new Variant("= \"Q\" & COLUMN()-4 & CHAR(10)& \"Sales\""));
          // Change the Orientation and Wrap Text properties for the headers.
          resizedrange.setOrientation(new Variant(38));
          resizedrange.setWrapText(new Variant(true));
          // Fill the interior colors of the headers.
          interior = resizedrange.getInterior();
          interior.setColorIndex(new Variant(36));
          // Fill the columns with a formula and apply a number format.
          range = sheet.getRange(new Variant("E2"),new Variant("E6"));
          resizedrange = range.getResize(vEmpty, new Variant(NumQtrs));
          resizedrange.setFormula(new Variant("=RAND()*100"));
          resizedrange.setNumberFormat(new Variant("$0.00"));
          // Apply Borders to the Sales data and headers
          range = sheet.getRange(new Variant("E1"), new Variant("E6"));
          resizedrange = range.getResize(vEmpty, new Variant(NumQtrs));
          borders = resizedrange.getBorders();
          borders.setWeight(new Variant(2)); // xlThin = 2
          // Add Totals formula for Quarterly sales data, apply a border.
          range = sheet.getRange(new Variant("E8"),new Variant("E8"));
          resizedrange = range.getResize(vEmpty, new Variant(NumQtrs));
          resizedrange.setFormula(new Variant("=SUM(E2:E6)"));
          borders = resizedrange.getBorders();
          Border bottomborder;
          bottomborder = borders.getItem(9); // Bottom border
          bottomborder.setLineStyle(new Variant(-4119)); // xlDouble = -4119
          bottomborder.setWeight(new Variant(4));  // xlThick = 4
          // Some more features: Add comment to cell C3 - Full 
          //    name of Tom Brown
          range = sheet.getRange(new Variant("C3"),new Variant("C3"));
          range.AddComment(new Variant("Tom was last month's top performer"));
          // Chart the Salaries.
          double Left = 72.; // location in points
          double Top = 144.;
          double Width = 350.;
          double Height = 250.;
          ChartObjects chartobjects = 
            (ChartObjects)sheet.ChartObjects(vEmpty,0); // explicit typecast
          ChartObject chartobject = chartobjects.Add(Left, Top, Width, Height);
          // Set the location and size.
          Chart chart = chartobject.getChart();
          range = sheet.getRange(new Variant("C2"), new Variant("D6"));
          // Full name and salary.
          range.Select();
          chart.ChartWizard(new Variant(range), // Source
                            new Variant(11),    // Gallery
                            vEmpty,             // Format
                            new Variant(1),     // PlotBy
                            new Variant(0),     // CategoryLabels
                            new Variant(1),     // SeriesLabels
                            new Variant(true),  // HasLegend
                            new Variant("Salary by Employee"), //Title
                            new Variant("Employee"), //CategoryTitle
                            new Variant("Salary"), //ValueTitle
                            vEmpty,             // ExtraTitle
                            0                   // LCID(localeID)
                            );
       		
          // Make the application visible and give the user control of 
          // Microsoft Excel.
          // xlApp.setVisible(0,true); // 1st param is LCID, Locale ID. 
          // The preceding was done at beginning of the program.
          xlApp.setUserControl(true);
       		
          MessageBox(0, "When Excel hides, Minimize VJ++ IDE to see Excel",
                     "Focus on Excel", 327744);
          //book.setSaved(0,true); // Avoid "Save changes" dialog
          //xlApp.Quit();  // Not if using UserControl		
    	} // End of Main()
    } // End of Class
    
    
    					
  6. 偵錯] 功能表按一下 [開始]。程式建置套件,並執行。它執行時,視窗便會出現一個主控台和某些時間更新版本的 Excel 就會出現。對話方塊,提示您如何可能要繪製圖表的季別都會出現在最上面,除非您任何地方按一下螢幕上之後在程式啟動。如果您已經按了 [] 對話方塊將會縮小在 Windows 任務列的螢幕。如果程式似乎停止回應,還原到 '一般' 是在任務列的對話方塊。

?考

如其他有關 Visual J++ 和自動化,按一下下面的文件編號,檢視 「 Microsoft 知識庫 」 中 「 文件:
169173資訊: 常見問題的 Visual J++
215484如何自動化 PowerPoint 使用 Visual J++
179706如何使用 MFC 來自動化 Excel 和建立/格式新的活頁簿
219151如何自動化 Microsoft Excel 從 Visual Basic

屬性

文章編號: 219430 - 上次校閱: 2004年6月30日 - 版次: 5.4
這篇文章中的資訊適用於:
  • Microsoft Visual J++ 6.0 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Office XP Developer Edition
  • Microsoft Office 2000 Developer Edition
關鍵字:?
kbmt kbautomation kbhowto KB219430 KbMtzh
機器翻譯
重要:本文是以 Microsoft 機器翻譯軟體翻譯而成,而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章,讓使用者可以依其使用語言使用知識庫中的所有文章。但是,機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤,就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。
按一下這裡查看此文章的英文版本:219430
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com