Openoffice.org calc常用操作函数集锦

转载：http://blog.csdn.net/kdzxiaoli/article/details/4274351

/********************************************************   操作Open office.org Calc文档的常用函数  *   @author lishijin*   @date 2009-06-13 19:554*******************************************************/package com.lishijin.pm.common.base;import java.io.File;import java.io.FileNotFoundException;import java.io.IOException;import com.sun.star.beans.PropertyValue;import com.sun.star.comp.helper.Bootstrap;import com.sun.star.comp.helper.BootstrapException;import com.sun.star.container.XIndexAccess;import com.sun.star.document.XStorageBasedDocument;import com.sun.star.embed.ElementModes;import com.sun.star.embed.XStorage;import com.sun.star.frame.XComponentLoader;import com.sun.star.frame.XStorable;import com.sun.star.io.XInputStream;import com.sun.star.io.XOutputStream;import com.sun.star.io.XSeekable;import com.sun.star.io.XStream;import com.sun.star.io.XTruncate;import com.sun.star.lang.XComponent;import com.sun.star.lang.XMultiComponentFactory;import com.sun.star.sheet.XSpreadsheet;import com.sun.star.sheet.XSpreadsheetDocument;import com.sun.star.sheet.XSpreadsheets;import com.sun.star.table.XCell;import com.sun.star.table.XCellRange;import com.sun.star.table.XColumnRowRange;import com.sun.star.table.XTableColumns;import com.sun.star.table.XTableRows;import com.sun.star.text.XText;import com.sun.star.uno.Exception;import com.sun.star.uno.UnoRuntime;import com.sun.star.uno.XComponentContext;import com.sun.star.util.XMergeable;/*** Oracle from export data to openoffice Calc* @author lsj*/public class OOoCommon {    /** Constructor. */    public OOoCommon() {    }        /**     * openoffice程序加载     */    public static Object getBootstrap() throws Exception {        // 加载opernOffice        XComponentContext xcomponentcontext = null;        XMultiComponentFactory xmulticomponentfactory = null;        try {            xcomponentcontext = Bootstrap.bootstrap();            xmulticomponentfactory = xcomponentcontext.getServiceManager();        } catch (BootstrapException e) {            e.printStackTrace();        }        Object desktop = xmulticomponentfactory.createInstanceWithContext(                "com.sun.star.frame.Desktop", xcomponentcontext);        return desktop;    }        /**     * 读取模板文件生成组件对象     * @param templateFileUrl 模板文件URL     * @return 组件对象     */    public static XComponent getSpreadsheetComponent(Object desktop, String templateFileUrl) throws Exception {                XComponentLoader xComponentLoader     =(XComponentLoader)UnoRuntime.queryInterface(XComponentLoader.class, desktop);            PropertyValue[] loadProps = new PropertyValue[2];            loadProps[0] = new PropertyValue();            loadProps[0].Name = "Hidden";//隐示打开            loadProps[0].Value = new Boolean(true);            loadProps[1] = new PropertyValue();            loadProps[1].Name = "AsTemplate";//模板文件标志        loadProps[1].Value = new Boolean(true);            XComponent  xSpreadsheetComponent =                xComponentLoader.loadComponentFromURL(templateFileUrl, "_blank",0, loadProps);        return xSpreadsheetComponent;    }        /**     * 读取Calc文件文件取得组件对象     * @param fileUrl 模板文件URL     * @return 组件对象     */    public static XComponent readSpreadsheetComponent(Object desktop, String fileUrl) throws Exception {                XComponentLoader xComponentLoader =(XComponentLoader)UnoRuntime.queryInterface(XComponentLoader.class, desktop);            PropertyValue[] loadProps = new PropertyValue[1];            loadProps[0] = new PropertyValue();            loadProps[0].Name = "Hidden";//隐示打开            loadProps[0].Value = new Boolean(true);            XComponent  xSpreadsheetComponent =                xComponentLoader.loadComponentFromURL(fileUrl, "_blank",0, loadProps);        return xSpreadsheetComponent;    }        /**     * 取得Calc文件对象     * @param xSpreadsheetComponent 组件对象     * @return Calc文件对象     */    public static XSpreadsheetDocument getXSpreadsheetDocument(XComponent xSpreadsheetComponent) throws Exception {        XSpreadsheetDocument xSpreadsheetDocument =                (XSpreadsheetDocument)UnoRuntime.queryInterface(XSpreadsheetDocument.class,xSpreadsheetComponent);        return xSpreadsheetDocument;    }        /**     * 通过Sheet名称取得Sheet对象     * @param xSpreadsheetDocument Calc文件对象     * @param sheetName Sheet名称     * @return Sheet对象     */    public static XSpreadsheet getXSpreadsheetByName(XSpreadsheetDocument xSpreadsheetDocument, String sheetName) throws Exception {        XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();        Object sheet = xSpreadsheets.getByName(sheetName);        XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, sheet);        return xSpreadsheet;    }        /**     * 通过Sheet序号取得Sheet对象     * @param xSpreadsheetDocument Calc文件对象     * @param index Sheet序号     * @return Sheet对象     */    public static XSpreadsheet getXSpreadsheetByIndex(XSpreadsheetDocument xSpreadsheetDocument, int index) throws Exception {        XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();        // get via the index access the first sheet        XIndexAccess xElements = (XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets);        // specify the first sheet from the spreadsheet        XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(                XSpreadsheet.class, xElements.getByIndex(index));          return xSpreadsheet;    }        /**     * 取得表行对象     * @param spreadSheet Sheet对象     * @return 表行对象     */    public static XTableRows getXTableRows(XSpreadsheet spreadSheet) {        XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, spreadSheet);        XColumnRowRange xCRRange =            ( com.sun.star.table.XColumnRowRange ) UnoRuntime.queryInterface(                com.sun.star.table.XColumnRowRange.class, xSpreadsheet );              XTableRows xRows = xCRRange.getRows();        return xRows;    }        /**     * 取得表列对象     * @param spreadSheet Sheet对象     * @return 表列对象     */    public static XTableColumns getXTableColumns(XSpreadsheet spreadSheet) {        XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, spreadSheet);        XColumnRowRange xCRRange =            ( com.sun.star.table.XColumnRowRange ) UnoRuntime.queryInterface(                com.sun.star.table.XColumnRowRange.class, xSpreadsheet );              XTableColumns xColumns = xCRRange.getColumns();        return xColumns;    }        /**     * 插入列     * @param xColumns 列对象     * @param index 插入序号(0-Based)     * @param count 插入的列数     */    public static void insertColumns(XTableColumns xColumns, int index, int count) {        xColumns.insertByIndex(index,count);    }        /**     * 插入行     * @param xRows 行对象     * @param index 插入序号(0-Based)     * @param count 插入的行数     */    public static void insertRows(XTableRows xRows, int index, int count) {        xRows.insertByIndex(index,count);    }        /**     * 删除列     * @param xColumns 列对象     * @param index 删除序号(0-Based)     * @param count 删除的列数     */    public static void removeColumns(XTableColumns xColumns, int index, int count) {        xColumns.removeByIndex(index,count);    }        /**     * 删除行     * @param xColumns 行对象     * @param index 删除序号(0-Based)     * @param count 删除的行数     */    public static void removeRows(XTableRows xRows, int index, int count) {        xRows.removeByIndex(index,count);    }        /**     * 写入单元格     * @param  xSpreadsheet Sheet对象     * @param col 列番号     * @param row 行番号     * @param value 写入的字符串     */    public static void setTextValueOfXCellAtPosition(XSpreadsheet xSpreadsheet, int col,            int row, String value) throws Exception {        XCell xCell = xSpreadsheet.getCellByPosition(col, row);        XText xCellText = (XText) UnoRuntime.queryInterface(XText.class, xCell);        xCellText.setString(value);    }        /**     * 写入单元格整形数据类型     * @param  xSpreadsheet Sheet对象     * @param col 列番号     * @param row 行番号     * @param value 写入的字符串     */    public static void setNumValueOfXCellAtPosition(XSpreadsheet xSpreadsheet, int col,            int row, double value) throws Exception {        XCell xCell = xSpreadsheet.getCellByPosition(col, row);        xCell.setValue(value);    }        /**     * 取得单元格对象     * @param  xSpreadsheet Sheet对象     * @param col 列番号     * @param row 行番号     * @return 单元格对象     */    public static XCell getXCellByPosition(XSpreadsheet xSpreadsheet, int col, int row) throws Exception {        return xSpreadsheet.getCellByPosition(col, row);    }        /**     * 取得单元格（字符串类型）     * @param  xSpreadsheet Sheet对象     * @param col 列番号     * @param row 行番号     * @return 取得的字符串     */    public static String getTextValueOfXCellAtPosition(XSpreadsheet xSpreadsheet, int col,int row)    throws Exception {        XCell xCell = xSpreadsheet.getCellByPosition(col, row);        XText xCellText = (XText) UnoRuntime.queryInterface(XText.class, xCell);        return xCellText.getString();    }        /**     * 取得单元格（整形数据类型）     * @param  xSpreadsheet Sheet对象     * @param col 列番号     * @param row 行番号     * @return 取得的字符串     */    public static double getNumValueOfXCellAtPosition(XSpreadsheet xSpreadsheet, int col,int row)    throws Exception {        XCell xCell = xSpreadsheet.getCellByPosition(col, row);        double iCellValue = xCell.getValue();        return iCellValue;    }        /**     * 获取域     * @param xSpreadsheet Sheet对象     * @param col 开始列号     * @param row 开始行号     * @param col1 结束列号     * @param row1 结束行号     * @return 域对象     */    public static XCellRange getRange(XSpreadsheet xSpreadsheet, int col, int row, int col1,            int row1) throws Exception {        XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(col, row,                col1, row1);        return xCellRange;    }        /**     * 单元格合并     * @param xSpreadsheet sheet对象     * @param col 开始列号     * @param row 开始行号     * @param col1 结束列号     * @param row1 结束行号     */    public static void mergeCellRange(XSpreadsheet xSpreadsheet, int col, int row, int col1, int row1)    throws Exception {        XCellRange xCellRange = OOoCommon.getRange(xSpreadsheet,col,row,col1,row1);        XMergeable xMerge = (XMergeable)        UnoRuntime.queryInterface(XMergeable.class, xCellRange);        //合并单元格        xMerge.merge(true);    }    /**     * 将XComponent对象转换成byte[]数据类型     * @param xComponent 组件对象     * @param byte数据     */    public static byte[] getRawDataFromStream(XComponent xComponent) throws Exception {        XStorageBasedDocument xStorageBasedDocument = (XStorageBasedDocument) UnoRuntime                .queryInterface(XStorageBasedDocument.class, xComponent);        XStorage xStorage = xStorageBasedDocument.getDocumentStorage();        XStorage substorage = xStorage.openStorageElement("Versions",                ElementModes.READWRITE);        XStream stream = substorage.openStreamElement("0.0.1",                ElementModes.READWRITE);        XOutputStream os = stream.getOutputStream();        XTruncate truncate = (XTruncate) UnoRuntime.queryInterface(                XTruncate.class, os);        truncate.truncate();        PropertyValue[] argh = new PropertyValue[2];        argh[0] = new PropertyValue();        argh[0].Name = "FilterName";        argh[0].Value = "StarOffice XML (Calc)";        argh[1] = new PropertyValue();        argh[1].Name = "OutputStream";        argh[1].Value = os;        // create storable object from document component and store to stream        XStorable xStorable = (XStorable) UnoRuntime.queryInterface(                XStorable.class, xComponent);        xStorable.storeToURL("private:stream", argh);        // get input stream and skeekable so we can read the stream        XInputStream is = (XInputStream) UnoRuntime.queryInterface(                XInputStream.class, os);        XSeekable xSeekable = (XSeekable) UnoRuntime.queryInterface(                XSeekable.class, os);        xSeekable.seek(0);        // read and return the bytes        byte[][] t_bytes = new byte[1][(int) xSeekable.getLength()];        is.readBytes(t_bytes, (int) xSeekable.getLength());        return t_bytes[0];    }        /**     * 将绝对路径转换成URL     * @throws IOException IO异常     * @throws FileNotFoundException 文件找不到异常     * @return 文件URL     */    public static String fileToUrl(String strPath) throws IOException,FileNotFoundException {        File file = new File(strPath);        String sbTmp = "file:///";        String target = file.toURL().toString().substring(6);        return sbTmp + target;    }