Esempio n. 1
0
 /// <summary>
 /// Insert a TEXT CELL using the XText interface
 /// </summary>
 /// <param name="xSheet"></param>
 /// <param name="nColumn"></param>
 /// <param name="nRow"></param>
 /// <param name="strText"></param>
 public void InsertTextCell(unoidl.com.sun.star.sheet.XSpreadsheet xSheet, int nColumn, int nRow, string strText)
 {
     unoidl.com.sun.star.table.XCell xCell     = xSheet.getCellByPosition(nColumn, nRow);
     unoidl.com.sun.star.text.XText  xCellText =
         (unoidl.com.sun.star.text.XText)xCell;
     unoidl.com.sun.star.text.XTextCursor xTextCursor =
         xCellText.createTextCursor();
     xCellText.insertString(xTextCursor, strText, false);
 }
// ________________________________________________________________

    /// This sample function modifies cells and cell ranges.
    public void doSampleFunction()
    {
        // for common usage
        unoidl.com.sun.star.sheet.XSpreadsheet xSheet     = getSpreadsheet(0);
        unoidl.com.sun.star.beans.XPropertySet xPropSet   = null;
        unoidl.com.sun.star.table.XCell        xCell      = null;
        unoidl.com.sun.star.table.XCellRange   xCellRange = null;

        // *** Access and modify a VALUE CELL ***
        Console.WriteLine("*** Sample for service table.Cell ***");

        xCell = xSheet.getCellByPosition(0, 0);
        // Set cell value.
        xCell.setValue(1234);

        // Get cell value.
        double nDblValue = xCell.getValue() * 2;

        xSheet.getCellByPosition(0, 1).setValue(nDblValue);

        // *** Create a FORMULA CELL and query error type ***
        xCell = xSheet.getCellByPosition(0, 2);
        // Set formula string.
        xCell.setFormula("=1/0");

        // Get error type.
        bool bValid = (xCell.getError() == 0);
        // Get formula string.
        String aText = "The formula " + xCell.getFormula() + " is ";

        aText += bValid ? "valid." : "erroneous.";

        // *** Insert a TEXT CELL using the XText interface ***
        xCell = xSheet.getCellByPosition(0, 3);
        unoidl.com.sun.star.text.XText xCellText =
            (unoidl.com.sun.star.text.XText)xCell;
        unoidl.com.sun.star.text.XTextCursor xTextCursor =
            xCellText.createTextCursor();
        xCellText.insertString(xTextCursor, aText, false);

        // *** Change cell properties ***
        int nValue = bValid ? 0x00FF00 : 0xFF4040;

        xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCell;
        xPropSet.setPropertyValue(
            "CellBackColor", new uno.Any((Int32)nValue));


        // *** Accessing a CELL RANGE ***
        Console.WriteLine("*** Sample for service table.CellRange ***");

        // Accessing a cell range over its position.
        xCellRange = xSheet.getCellRangeByPosition(2, 0, 3, 1);

        // Change properties of the range.
        xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange;
        xPropSet.setPropertyValue(
            "CellBackColor", new uno.Any((Int32)0x8080FF));

        // Accessing a cell range over its name.
        xCellRange = xSheet.getCellRangeByName("C4:D5");

        // Change properties of the range.
        xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange;
        xPropSet.setPropertyValue(
            "CellBackColor", new uno.Any((Int32)0xFFFF80));


        // *** Using the CELL CURSOR to add some data below of
        // the filled area ***
        Console.WriteLine("*** Sample for service table.CellCursor ***");

        // Create a cursor using the XSpreadsheet method createCursorByRange()
        xCellRange = xSheet.getCellRangeByName("A1");
        unoidl.com.sun.star.sheet.XSheetCellRange xSheetCellRange =
            (unoidl.com.sun.star.sheet.XSheetCellRange)xCellRange;

        unoidl.com.sun.star.sheet.XSheetCellCursor xSheetCellCursor =
            xSheet.createCursorByRange(xSheetCellRange);
        unoidl.com.sun.star.table.XCellCursor xCursor =
            (unoidl.com.sun.star.table.XCellCursor)xSheetCellCursor;

        // Move to the last filled cell.
        xCursor.gotoEnd();
        // Move one row down.
        xCursor.gotoOffset(0, 1);
        xCursor.getCellByPosition(0, 0).setFormula(
            "Beyond of the last filled cell.");


        // *** Modifying COLUMNS and ROWS ***
        Console.WriteLine("*** Sample for services table.TableRows and " +
                          "table.TableColumns ***");

        unoidl.com.sun.star.table.XColumnRowRange xCRRange =
            (unoidl.com.sun.star.table.XColumnRowRange)xSheet;
        unoidl.com.sun.star.table.XTableColumns xColumns =
            xCRRange.getColumns();
        unoidl.com.sun.star.table.XTableRows xRows = xCRRange.getRows();

        // Get column C by index (interface XIndexAccess).
        uno.Any aColumnObj = xColumns.getByIndex(2);
        xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aColumnObj.Value;
        xPropSet.setPropertyValue("Width", new uno.Any((Int32)5000));

        // Get the name of the column.
        unoidl.com.sun.star.container.XNamed xNamed =
            (unoidl.com.sun.star.container.XNamed)aColumnObj.Value;
        aText = "The name of this column is " + xNamed.getName() + ".";
        xSheet.getCellByPosition(2, 2).setFormula(aText);

        // Get column D by name (interface XNameAccess).
        unoidl.com.sun.star.container.XNameAccess xColumnsName =
            (unoidl.com.sun.star.container.XNameAccess)xColumns;

        aColumnObj = xColumnsName.getByName("D");
        xPropSet   = (unoidl.com.sun.star.beans.XPropertySet)aColumnObj.Value;
        xPropSet.setPropertyValue(
            "IsVisible", new uno.Any((Boolean)false));

        // Get row 7 by index (interface XIndexAccess)
        uno.Any aRowObj = xRows.getByIndex(6);
        xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aRowObj.Value;
        xPropSet.setPropertyValue("Height", new uno.Any((Int32)5000));

        xSheet.getCellByPosition(2, 6).setFormula("What a big cell.");

        // Create a cell series with the values 1 ... 7.
        for (int nRow = 8; nRow < 15; ++nRow)
        {
            xSheet.getCellByPosition(0, nRow).setValue(nRow - 7);
        }
        // Insert a row between 1 and 2
        xRows.insertByIndex(9, 1);
        // Delete the rows with the values 3 and 4.
        xRows.removeByIndex(11, 2);

        // *** Inserting CHARTS ***
        Console.WriteLine("*** Sample for service table.TableCharts ***");

        unoidl.com.sun.star.table.XTableChartsSupplier xChartsSupp =
            (unoidl.com.sun.star.table.XTableChartsSupplier)xSheet;
        unoidl.com.sun.star.table.XTableCharts xCharts =
            xChartsSupp.getCharts();

        // The chart will base on the last cell series, initializing all values.
        String aName = "newChart";

        unoidl.com.sun.star.awt.Rectangle aRect =
            new unoidl.com.sun.star.awt.Rectangle();
        aRect.X     = 10000;
        aRect.Y     = 3000;
        aRect.Width = aRect.Height = 5000;
        unoidl.com.sun.star.table.CellRangeAddress[] aRanges =
            new unoidl.com.sun.star.table.CellRangeAddress[1];
        aRanges[0] = createCellRangeAddress(xSheet, "A9:A14");

        // Create the chart.
        xCharts.addNewByName(aName, aRect, aRanges, false, false);

        // Get the chart by name.
        uno.Any aChartObj = xCharts.getByName(aName);
        unoidl.com.sun.star.table.XTableChart xChart =
            (unoidl.com.sun.star.table.XTableChart)aChartObj.Value;

        // Query the state of row and column headers.
        aText  = "Chart has column headers: ";
        aText += xChart.getHasColumnHeaders() ? "yes" : "no";
        xSheet.getCellByPosition(2, 8).setFormula(aText);
        aText  = "Chart has row headers: ";
        aText += xChart.getHasRowHeaders() ? "yes" : "no";
        xSheet.getCellByPosition(2, 9).setFormula(aText);
    }
Esempio n. 3
0
        private bool ExportOOo()
        {
            try
            {
                SpreadSheetHelper helper = new SpreadSheetHelper(true);

                //Samples for service sheet.SheetCell
                unoidl.com.sun.star.sheet.XSpreadsheet xSheet = helper.getSpreadsheet(0);
                unoidl.com.sun.star.table.XCell        xCell  = null;

                //xCell = xSheet.getCellByPosition(1, 1);
                //// --- Insert two text paragraphs into the cell. ---
                //unoidl.com.sun.star.text.XText tText = (unoidl.com.sun.star.text.XText)xCell;
                //unoidl.com.sun.star.text.XTextCursor tTextCursor =tText.createTextCursor();
                //tText.insertString(tTextCursor, _title + "\r\n", false);

                if (_gridType == 0)
                {
                    for (int j = 0; j < _exportGrid.Columns.Count; j++)
                    {
                        xCell = xSheet.getCellByPosition(j + 1, 2);

                        // --- Insert two text paragraphs into the cell. ---
                        unoidl.com.sun.star.text.XText       xText       = (unoidl.com.sun.star.text.XText)xCell;
                        unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();

                        xText.insertString(xTextCursor, _exportGrid.Columns[j].HeaderText, false);
                    }
                    // --- Get cell B3 by position - (column, row) ---
                    for (int i = 0; i < _exportGrid.Rows.Count; i++)
                    {
                        if (_exportGrid.Rows[i].Visible)
                        {
                            for (int j = 0; j < _exportGrid.Columns.Count; j++)
                            {
                                xCell = xSheet.getCellByPosition(j + 1, i + 3);
                                unoidl.com.sun.star.text.XText       xText       = (unoidl.com.sun.star.text.XText)xCell;
                                unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
                                xText.insertString(xTextCursor, _exportGrid.Rows[i].Cells[j].Value.ToString(), false);
                            }
                        }
                    }
                }
                else if (_gridType == 1)
                {
                    for (int i = 0; i < _flexGrid.Rows; i++)
                    {
                        for (int j = 0; j < _flexGrid.Cols; j++)
                        {
                            xCell = xSheet.getCellByPosition(j + 1, i + 2);
                            unoidl.com.sun.star.text.XText       xText       = (unoidl.com.sun.star.text.XText)xCell;
                            unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
                            xText.insertString(xTextCursor, _flexGrid.get_TextMatrix(i, j), false);
                        }
                    }
                }
                else
                {
                    for (int i = 0; i < _listview.Items.Count; i++)
                    {
                        xCell = xSheet.getCellByPosition(1, i + 2);
                        unoidl.com.sun.star.text.XText       xText       = (unoidl.com.sun.star.text.XText)xCell;
                        unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
                        xText.insertString(xTextCursor, _listview.Items[i].Text, false);
                        for (int j = 1; j < _flexGrid.Cols; j++)
                        {
                            xCell       = xSheet.getCellByPosition(j + 1, i + 2);
                            xText       = (unoidl.com.sun.star.text.XText)xCell;
                            xTextCursor = xText.createTextCursor();
                            xText.insertString(xTextCursor, _listview.Items[i].SubItems[j].Text, false);
                        }
                    }
                }
                helper.storeDocComponent(_filename);
                helper.closeDocCompant();
                return(true);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return(false);
            }
            finally
            {
                //_exportGrid.Dispose();
            }
        }