在SWT中使用OLE操纵Excel(2)

package com.jrkui.example.excel;

import org.eclipse.swt.SWT;
import org.eclipse.swt.layout.FillLayout;
import org.eclipse.swt.ole.win32.OLE;
import org.eclipse.swt.ole.win32.OleAutomation;
import org.eclipse.swt.ole.win32.OleClientSite;
import org.eclipse.swt.ole.win32.OleFrame;
import org.eclipse.swt.ole.win32.Variant;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Shell;

public class ColorRangeShell {
    public static void main(String[] args) {
        new ColorRangeShell().open();
    }
    
    public void open()
    {
        Display display = Display.getDefault();
        Shell shell = new Shell();
        shell.setText("Color Range Shell");
        shell.setSize(400, 300);
        shell.setLayout(new FillLayout());
        
        createExcelPart(shell);
        
        shell.open();
        while(!shell.isDisposed())
            if(!display.readAndDispatch())
                display.sleep();
        display.dispose();
    }
    
    private static final int SHEET_ID = 0x000001e5;
    
    private static final int CELL_ID =  0x000000c5;
    
    private void createExcelPart(Shell shell)
    {
        OleFrame frame = new OleFrame(shell,SWT.NONE);
        OleClientSite clientSite = new OleClientSite(frame,SWT.NONE,"Excel.Sheet");
        clientSite.doVerb(OLE.OLEIVERB_SHOW);
        
        OleAutomation workbook = new OleAutomation(clientSite);
        OleAutomation worksheet = workbook.getProperty(SHEET_ID, new Variant[]{new Variant(1)}).getAutomation();
        
        //获得单元格
        OleAutomation cellA1 = worksheet.getProperty(CELL_ID, new Variant[]{new Variant("A1")}).getAutomation();
        OleAutomation cellD1 = worksheet.getProperty(CELL_ID, new Variant[]{new Variant("D1")}).getAutomation();
        
        //获得单元格区域
        OleAutomation areaA3D5 = worksheet.getProperty(CELL_ID,new Variant[]{new Variant("A3"),new Variant("D5")}).getAutomation();
        
        colorRangeByRed(cellA1);
        colorRangeByRed(cellD1);
        colorRangeByRed(areaA3D5);
    }
    
    /**
     * 获得interior的方法在Range中的Id
     */
    private static final int INTERIOR = 0x00000081;
    
    /**
     * 为ColorIndex赋值的方法在interior中的Id
     */
    private static final int COLOR_INDEX = 0x00000061;
    
    /**
     * 红色在Excel的Index为3
     */
    private static final int RED = 3; 
    
    /**
     * 用红色作为Range的背景色
     * @param automation
     */
    private void colorRangeByRed(OleAutomation automation)
    {
        //获得interior
        OleAutomation interior = automation.getProperty(INTERIOR).getAutomation();
        //设置颜色
        interior.setProperty(COLOR_INDEX, new Variant(RED));
    }
}

运行效果：

原理：

ü         为Range设置背景色是通过Range的interior属性

ü         interior也是一个对象，为它的ColorIndex属性赋值可设置其颜色

ü         Excel里的颜色设置是通过其序列号设定的（不是通过RGB），红色的index是3。获得所有颜色的index的方法还没找到，可以一个个试，从0开始的整数。

在实际应用中，常常会遇到单元格的值比较长而被遮住，用户不得不手动调整宽度，如果能通过程序就自动调整宽度就会很方便了。实际上在通过OleView.exe这个工具查询得知Range有AutoFit的方法，它的Id是0x000000ed，那么如果获得了Range的引用，只要调用AutoFit这个方法，就可以自动调整宽度了。下面请看代码与示例效果：

package com.jrkui.example.excel;

import org.eclipse.swt.SWT;

import org.eclipse.swt.layout.FillLayout;

import org.eclipse.swt.ole.win32.OLE;

import org.eclipse.swt.ole.win32.OleAutomation;

import org.eclipse.swt.ole.win32.OleClientSite;

import org.eclipse.swt.ole.win32.OleFrame;

import org.eclipse.swt.ole.win32.Variant;

import org.eclipse.swt.widgets.Display;

import org.eclipse.swt.widgets.Shell;

publicclass AutofitShell {

publicstaticvoid main(String[] args) {

     new AutofitShell().open();

}  

publicvoid open()

{

     Display display = Display.getDefault();

     Shell shell = new Shell();

     shell.setText("Auto Fit");

     shell.setSize(700, 300);

     shell.setLayout(new FillLayout());

    

     createExcelPart(shell);

    

     shell.open();

     while(!shell.isDisposed())

        if(!display.readAndDispatch())

            display.sleep();

     display.dispose();

}

privatestaticfinalintSHEET_ID = 0x000001e5;

privatestaticfinalintCELL_ID = 0x000000c5;

privatestaticfinalintCELL_VALUE_ID = 0x00000006;

privatevoid createExcelPart(Shell shell)

{

     OleFrame frame = new OleFrame(shell,SWT.NONE);

     OleClientSite clientSite = new OleClientSite(frame,SWT.NONE,"Excel.Sheet");

     clientSite.doVerb(OLE.OLEIVERB_SHOW);

    

     OleAutomation workbook = new OleAutomation(clientSite);

     OleAutomation worksheet = workbook.getProperty(SHEET_ID,new Variant[]{new Variant(1)}).getAutomation();

     OleAutomation cellA3 = worksheet.getProperty(CELL_ID,new Variant[]{new Variant("A3")}).getAutomation();

    

     cellA3.setProperty(CELL_VALUE_ID, new Variant("if you don't fit the width of the cell, you couldn't see all."));

    

     autoFitWidth(cellA3);

//      autoFitWidth(getColumnOfCell(cellA3));

}

publicstaticfinalintAUTO_FIT_RANGE            = 0x000000ed;

/**

 *自适应宽度

 *@paramautomation

 */

privatevoid autoFitWidth(OleAutomation automation)

{

     //如果使用automation.getProperty(AUTO_FIT_RANGE)也是同样的效果

     automation.invoke(AUTO_FIT_RANGE);

}

publicstaticfinalintCOLUMN_OF_CELL       = 0x000000f6;

/**

 *获得单元格所在的列

 *@paramcell

 *@return

 */

private OleAutomation getColumnOfCell(OleAutomation cell)

{

     return cell.getProperty(COLUMN_OF_CELL).getAutomation();

}

}

运行效果（自动调整A3单元格的宽度）：

  

运行效果（去掉createExcelPart()方法中的autoFitWidth(getColumnOfCell(cellA3));的注释，自动调整A3单元格所在列的列宽）

  

说明：

•       调整列宽在本例中是通过autoFitWidth(OleAutomation automation)方法

•      在autoFitWidth(OleAutomation automation)中调用了OleAutomation的invoke()方法，参数是所代表ole对象的方法的id，意思是调用该方法，用  automation.getProperty(AUTO_FIT_RANGE)也是同样的效果

•          autoFitWidth(OleAutomation automation)的参数是一个Range对象，可以是一个单元格，也可以是一个单元格的区域，在本例中示范了两种效果（单元格、列）

•          获得一个单元格区域的的所在列的方法在Range中是Range* EntireColumn()，其id是0x000000f6

设置字体格式是常见的需求

package com.jrkui.example.excel;

 

import org.eclipse.swt.SWT;

import org.eclipse.swt.layout.FillLayout;

import org.eclipse.swt.ole.win32.OLE;

import org.eclipse.swt.ole.win32.OleAutomation;

import org.eclipse.swt.ole.win32.OleClientSite;

import org.eclipse.swt.ole.win32.OleFrame;

import org.eclipse.swt.ole.win32.Variant;

import org.eclipse.swt.widgets.Display;

import org.eclipse.swt.widgets.Shell;

 

public class FontStyleShell {

   public static void main(String[] args) {

      new FontStyleShell().open();

   }

  

   public void open()

   {

      Display display = Display.getDefault();

      Shell shell = new Shell();

      shell.setText("Font Style");

      shell.setSize(400, 300);

      shell.setLayout(new FillLayout());

     

      createExcelPart(shell);

     

      shell.open();

      while(!shell.isDisposed())

          if(!display.readAndDispatch())

             display.sleep();

      display.dispose();

   }

 

   private static final int SHEET_ID = 0x000001e5;

  

   private static final int CELL_ID =  0x000000c5;

  

   private static final int CELL_VALUE_ID = 0x00000006;

  

   private void createExcelPart(Shell shell)

   {

      OleFrame frame = new OleFrame(shell,SWT.NONE);

      OleClientSite clientSite = new OleClientSite(frame,SWT.NONE,"Excel.Sheet");

      clientSite.doVerb(OLE.OLEIVERB_SHOW);

     

      OleAutomation workbook = new OleAutomation(clientSite);

      OleAutomation worksheet = workbook.getProperty(SHEET_ID,new Variant[]{new Variant(1)}).getAutomation();

      OleAutomation cellA3 = worksheet.getProperty(CELL_ID,new Variant[]{new Variant("A3")}).getAutomation();

      cellA3.setProperty(CELL_VALUE_ID, new Variant("Hello OLE!"));

     

      setFontStyle(cellA3);

   }

  

   private static final int FONT_SIZE           = 0x00000068;

  

   private static final int FONT_NAME           = 0x0000006e;

  

   private static final int COLOR_INDEX         = 0x00000061;

  

   private static final int FONT                = 0x00000092;

  

   private static final int BOLD                = 0x00000060;

  

   private static final int RED                 = 3;

  

   private void setFontStyle(OleAutomation cell)

   {

      OleAutomation font = cell.getProperty(FONT).getAutomation();//获得单元格的font对象

     

      font.setProperty(FONT_SIZE, new Variant(32));//大小

      font.setProperty(FONT_NAME, new Variant("Arial Black"));//使用Arial Black的字体

      font.setProperty(BOLD, new Variant(true));//粗体

      font.setProperty(COLOR_INDEX, new Variant(RED));//颜色

   }

}