/** Writes a date with standard date format into a spreadsheet. * @param xSheet The XSpreadsheet interface of the spreadsheet. * @param aCellName The address of the cell (or a named range). * @param nDay The day of the date. * @param nMonth The month of the date. * @param nYear The year of the date. */ public void setDate( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aCellName, int nDay, int nMonth, int nYear) { // Set the date value. unoidl.com.sun.star.table.XCell xCell = xSheet.getCellRangeByName(aCellName).getCellByPosition(0, 0); String aDateStr = nMonth + "/" + nDay + "/" + nYear; xCell.setFormula(aDateStr); // Set standard date format. unoidl.com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier = (unoidl.com.sun.star.util.XNumberFormatsSupplier)getDocument(); unoidl.com.sun.star.util.XNumberFormatTypes xFormatTypes = (unoidl.com.sun.star.util.XNumberFormatTypes) xFormatsSupplier.getNumberFormats(); int nFormat = xFormatTypes.getStandardFormat( unoidl.com.sun.star.util.NumberFormat.DATE, new unoidl.com.sun.star.lang.Locale()); unoidl.com.sun.star.beans.XPropertySet xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCell; xPropSet.setPropertyValue( "NumberFormat", new uno.Any((Int32)nFormat)); }
/// <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); }
/** Draws a colored border around the range and writes the headline * in the first cell. * * @param xSheet The XSpreadsheet interface of the spreadsheet. * @param aRange The address of the cell range (or a named range). * @param aHeadline The headline text. */ public void prepareRange( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange, String aHeadline) { unoidl.com.sun.star.beans.XPropertySet xPropSet = null; unoidl.com.sun.star.table.XCellRange xCellRange = null; // draw border xCellRange = xSheet.getCellRangeByName(aRange); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange; unoidl.com.sun.star.table.BorderLine aLine = new unoidl.com.sun.star.table.BorderLine(); aLine.Color = 0x99CCFF; aLine.InnerLineWidth = aLine.LineDistance = 0; aLine.OuterLineWidth = 100; unoidl.com.sun.star.table.TableBorder aBorder = new unoidl.com.sun.star.table.TableBorder(); aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = aBorder.RightLine = aLine; aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true; aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true; xPropSet.setPropertyValue( "TableBorder", new uno.Any( typeof(unoidl.com.sun.star.table.TableBorder), aBorder)); // draw headline unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = (unoidl.com.sun.star.sheet.XCellRangeAddressable)xCellRange; unoidl.com.sun.star.table.CellRangeAddress aAddr = xAddr.getRangeAddress(); xCellRange = xSheet.getCellRangeByPosition( aAddr.StartColumn, aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange; xPropSet.setPropertyValue( "CellBackColor", new uno.Any((Int32)0x99CCFF)); // write headline unoidl.com.sun.star.table.XCell xCell = xCellRange.getCellByPosition(0, 0); xCell.setFormula(aHeadline); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCell; xPropSet.setPropertyValue( "CharColor", new uno.Any((Int32)0x003399)); xPropSet.setPropertyValue( "CharWeight", new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD)); }
// ________________________________________________________________ /// 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); }
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(); } }