OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

Delphi: Interact with Calc docs from OpenOffice or Excel

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
bitfarmer
General User
General User


Joined: 06 May 2004
Posts: 36
Location: Murcia, Spain
PostPosted: Fri May 14, 2004 3:54 am    Post subject: Delphi: Interact with Calc docs from OpenOffice or Excel Reply with quote
I had to make a non visual delphi object capable of interacting with spread sheets in general, first it was able to use Excel docs only, but now I have made it dual, so you can manage OpenOffice Calc docs or Excel docs transparently.

The code it self contains a example of use and a to-do list, as long as I improve it I will edit this post to reflect the new capabilities.

Hope it help to all delphi programers trying to master OpenOffice, it was dificult to work all this out without decent examples!

UPDATED: V0.95 (10-2009) with changes by Romulo merged with my own ones.
NOTE: This version works OK with OOo up to V3.1

BitFarmer.

Code:

// ********************************************
// ** Object for dual  SpreadSheet  managing **
// ** using Excel or OpenOffice automaticaly **
// ** By: Sergio Hernandez                   **
// ** oficina(at)hcsoft.net, CopyLeft 2009   **
// ** Version 0.96 20-01-2010 (DDMMYYYY)     **
// ** Use it freely, change it, etc.         **
// ********************************************

{EXAMPLE OF USE
  //Create object: We have two flavours:
  //(A) from an existing file...
  HCalc:= THojaCalc.create(OpenDialog.FileName, false);
  //(B) from a blank document...
  HCalc:= THojaCalc.create(thcOpenOffice, true); //OpenOffice doc if possible, please
  HCalc.FileName:= 'C:\MyNewDoc'; //Needs a file name before you SaveDoc!
  //--end of creation.
  HCalc.ActivateSheetByIndex(2); //Activate second sheet
  if HCalc.IsActiveSheetProtected then
    ShowMessage('2nd sheet of name "'+HCalc.ActiveSheetName+'" IS protected');
  //Change a cell value (well, change formula, not the double float value)
  if HCalc.CellText[i,2] = '' then HCalc.CellText[i,2] := 'Hello world!';
  HCalc.AddNewSheet('New Sheet');
  HCalc.PrintDoc;
  HCalc.SaveDoc;
  HCalc.Free;
}

{TODO LIST:
  -PrintActiveSheet is not working for OpenOffice (is it even possible?)
  -A way to write a date in a cell changing also the format (Excel is herratic in that)
  -Listener for OpenOffice so I can be noticed if user visually close the doc.
}

{CHANGE LOG:
 V0.96:
   -Added PrintSheetsUntil(LastSheetName: string) -only works on excel- to print
   out all tabs from 1 until -excluded- the one with the given name in such a
   way that only one print job is created instead of one per tab (only way to do
   this in previous versions, so converting part of a excel to a single PDF
   using a printer like PDFCreator was not posible).
 V0.95:
   -ActivateSheetByIndex detect imposible index and allows to insert sheet 100 (it will create all necesary sheets)
   -SaveDocAs added a second optional parameter for OOo to use Excel97 format (rescued from V0.93 by Rômulo)
   -A little stronger ValidateSheetName() (filter away \ and " too).
 V0.94:
   -OpenOffice V2 compatible (small changes)
   -A lot of "try except" to avoid silly errors.
   -SaveDocAs(Name: string): boolean; (Added by Massimiliano Gozzi)
   -New function FileName2URL(Name) to convert from FileName to URL (OOo SaveDosAs)
   -New function ooCreateValue to hide all internals of OOo params creation
 V0.93:
   ***************************
   ** By Rômulo Silva Ramos **
   ***************************
   -FontSize(Row, Col, Size): change font size in that cell.
   -BackgroundColor(row, col: integer; color:TColor);
   -Add ValidateSheetName to validate sheet names when adding or renaming a sheet
   REVERTED FUNCTIONS (not neccesary in newer version V0.95 anymore)
   -Change AddNewSheet to add a new sheet in end at sheet list
   *REVERTED IN V0.95*
       It creates sheet following the active one, so to add at the end:
       ActivateSheetByIndex(CountSheets);
       AddNewSheet('Sheet '+IntToStr(CountSheets+1));
   -Change in SaveDoc to use SaveAs/StoreAsUrl
   *REVERTED V0.95*
       Use SaveDocAs(Name, true) for StoreAsUrl in Excel97 format.
 V0.92:
   -SetActiveSheetName didn't change the name to the right sheet on OpenOffice.
   -PrintPreview: New procedure to show up the print preview window.
   -Bold(Row, Col): Make bold the text in that cell.
   -ColumnWidth(col, width): To change a column width.
 V0.91:
   -NewDoc: New procedure for creating a blank doc (used in create)
   -Create from empty doc adds a blank document and take visibility as parameter.
   -New functions ooCreateValue and ooDispatch to clean up the code.
   -ActiveSheetName: Now is a read-write property, not a read-only function.
   -Visible: Now is a read-write property instead of a create param only.
 V0.9:
  -Create from empty doc now tries both programs (if OO fails try to use Excel).
  -CellTextByName: Didn't work on Excel docs.
}

unit UHojaCalc;

interface

uses Variants, SysUtils, ComObj, Classes, Graphics;

//thcError: Tried to open but both failes
//thcNone:  Haven't tried still to open any
type TTipoHojaCalc = (thcError, thcNone, thcExcel, thcOpenOffice);

type THojaCalc = class(TObject)
private
  fVisible:  boolean;
  //Program loaded stuff...
  procedure  LoadProg;
  procedure  CloseProg;
  function   GetProgLoaded: boolean;
  procedure  NewDoc;
  procedure  LoadDoc;
  procedure  CloseDoc;
  function   GetDocLoaded: boolean;
  function   GetIsExcel: boolean;
  function   GetIsOpenOffice: boolean;
  procedure  SetVisible(v: boolean);
  //Sheets stuff..
  function   GetCountSheets: integer;
  function   GetActiveSheetName: string;
  procedure  SetActiveSheetName(NewName: string);
  //Cells stuff...
  function   GetCellText(row,col: integer): string;
  procedure  SetCellText(row,col: integer; Txt: string);
  function   GetCellTextByName(Range: string): string;
  procedure  SetCellTextByName(Range: string; Txt: string);
  //OpenOffice only stuff...
  function   FileName2URL(FileName: string): string;
  procedure  ooDispatch(ooCommand: string; ooParams: variant);
  function   ooCreateValue(ooName: string; ooData: variant): variant;
  //Aux functions
  function   ValidateSheetName(Name:string): string;
public
  Tipo: TTipoHojaCalc;    //Witch program was used to manage the doc?
  FileName:    string;    //In windows FileName format C:\MyDoc.XXX
  Programa:    variant;   //Excel or OpenOfice instance created.
  DeskTop:     variant;   //OpenOffice desktop reference (not used now).
  Document:    variant;   //Document opened.
  ActiveSheet: variant;   //Active sheet.
  //Object internals...
  constructor  Create(Name: string; MakeVisible: boolean); overload;
  constructor  Create(MyTipo: TTipoHojaCalc; MakeVisible: boolean); overload;
  destructor   Destroy; override;
  //Program loaded stuff...
  function     SaveDoc: boolean;
  function     SaveDocAs(Name: string; AsExcel97: boolean = false): boolean;
  function     PrintDoc: boolean;
  procedure    ShowPrintPreview;
  property     ProgLoaded: boolean     read GetProgLoaded;
  property     DocLoaded:  boolean     read GetDocLoaded;
  property     IsExcel: boolean        read GetIsExcel;
  property     IsOpenOffice: boolean   read GetIsOpenOffice;
  property     Visible: boolean        read fVisible           write SetVisible;
  //Sheets stuff...
  function     ActivateSheetByIndex(nIndex: integer): boolean;
  function     ActivateSheetByName(SheetName: string; CaseSensitive: boolean): boolean;
  function     IsActiveSheetProtected: boolean;
  function     PrintActiveSheet: boolean;

  function     PrintSheetsUntil(LastSheetName: string): boolean;

  procedure    AddNewSheet(NewName: string);
  property     CountSheets:  integer   read GetCountSheets;
  property     ActiveSheetName: string read GetActiveSheetName write SetActiveSheetName;
  //Cells stuff...
  procedure    Bold(row,col: integer);
  procedure    BackgroundColor(row, col: integer; color: TColor);
  procedure    FontSize(row, col, size: integer);
  procedure    ColumnWidth(col, width: integer); //Width in 1/100 of mm.
  property     CellText[f,c: integer]: string read GetCellText write SetCellText;
  property     CellTextByName[Range: string]: string read GetCellTextByName write SetCellTextByName;
end;

implementation

// ************************
// ** Create and destroy **
// ************************

//Create with an empty doc of requested type (use thcExcel or thcOpenOffice)
//Remember to define FileName before calling to SaveDoc
constructor THojaCalc.Create(MyTipo: TTipoHojaCalc; MakeVisible: boolean);
var
  i: integer;
  IsFirstTry: boolean;
begin
  //Close all opened things first...
  CloseDoc;
  CloseProg;
  //I will try to open twice, so if Excel fails, OpenOffice is used instead
  IsFirstTry:= true;
  for i:= 1 to 2 do begin
    //Try to open OpenOffice...
    if (MyTipo = thcOpenOffice) or (MyTipo = thcNone)then begin
      try
        Programa:= CreateOleObject('com.sun.star.ServiceManager');
      except
      end;
      if ProgLoaded then begin
        Tipo:= thcOpenOffice;
        break;
      end else begin
        if IsFirstTry then begin
          //Try Excel as my second choice
          MyTipo:= thcExcel;
          IsFirstTry:= false;
        end else begin
          //Both failed!
          break;
        end;
      end;
    end;
    //Try to open Excel...
    if (MyTipo = thcExcel) or (MyTipo = thcNone) then begin
      try
        Programa:= CreateOleObject('Excel.Application');
      except
      end;
      if ProgLoaded then begin
        Tipo:= thcExcel;
        break;
      end else begin
        if IsFirstTry then begin
          //Try OpenOffice as my second choice
          MyTipo:= thcOpenOffice;
          IsFirstTry:= false;
        end else begin
          //Both failed!
          break;
        end;
      end;
    end;
  end;
  //Was it able to open any of them?
  if Tipo = thcNone then begin
    Tipo:= thcError;
    raise Exception.Create('THojaCalc.create failed, may be no Office is installed?');
  end;
  //Add a blank document...
  fVisible:= MakeVisible;
  NewDoc;
end;

constructor THojaCalc.Create(Name: string; MakeVisible: boolean);
begin
  Tipo:= thcNone;
  //Store values...
  FileName:= Name;
  fVisible:=  MakeVisible;
  //Open program and document...
  LoadProg;
  LoadDoc;
end;

destructor THojaCalc.Destroy;
begin
  CloseDoc;
  CloseProg;
  inherited;
end;

// *************************
// ** Loading the program **
// ** Excel or OpenOffice **
// *************************

procedure THojaCalc.LoadProg;
begin
  if ProgLoaded then CloseProg;
  if (UpperCase(ExtractFileExt(FileName))='.XLS') then begin
    //Excel is the primary choice...
    try
      Programa:= CreateOleObject('Excel.Application');
    except end;
    if ProgLoaded then Tipo:= thcExcel;
  end;
  //Not lucky with Excel? Another filetype? Let's go with OpenOffice...
  if Tipo = thcNone then begin
    //Try with OpenOffice...
    try
      Programa:= CreateOleObject('com.sun.star.ServiceManager');
    except end;
    if ProgLoaded then Tipo:= thcOpenOffice;
  end;
  //Still no program loaded?
  if not ProgLoaded then begin
    Tipo:= thcError;
    raise Exception.Create('THojaCalc.create failed, may be no Office is installed?');
  end;
end;

procedure THojaCalc.CloseProg;
begin
  if not Visible then CloseDoc;
  if ProgLoaded then begin
    try
      if IsExcel then      Programa.Quit;
      //Next line made OO V2 not to work anymore as the next call to
      //CreateOleObject('com.sun.star.ServiceManager') failed.
      //if IsOpenOffice then Programa.Dispose;
      Programa:= Unassigned;
    finally end;
  end;
  Tipo:= thcNone;
end;

//Is there any prog loaded? Witch one?
function THojaCalc.GetProgLoaded: boolean;
begin
  result:= not (VarIsEmpty(Programa) or VarIsNull(Programa));
end;
function  THojaCalc.GetIsExcel: boolean;
begin
  result:= (Tipo=thcExcel);
end;
function  THojaCalc.GetIsOpenOffice: boolean;
begin
  result:= (Tipo=thcOpenOffice);
end;

// ************************
// ** Loading a document **
// ************************

procedure THojaCalc.NewDoc;
var ooParams: variant;
begin
  //Is the program running? (Excel or OpenOffice)
  if not ProgLoaded then raise Exception.Create('No program loaded for the new document.');
  //Is there a doc already loaded?
  CloseDoc;
  DeskTop:= Unassigned;
  //OK, now try to create the doc...
  if IsExcel then begin
    Programa.WorkBooks.Add;
    Programa.Visible:= Visible;
    Document:= Programa.ActiveWorkBook;
    ActiveSheet:= Document.ActiveSheet;
  end;
  if IsOpenOffice then begin
    Desktop:=  Programa.CreateInstance('com.sun.star.frame.Desktop');
    //Optional parameters (visible)...
    ooParams:=    VarArrayCreate([0, 0], varVariant);
    ooParams[0]:= ooCreateValue('Hidden', not Visible);
    //Create the document...
    Document:= Desktop.LoadComponentFromURL('private:factory/scalc', '_blank', 0, ooParams);
    ActivateSheetByIndex(1);
  end;
end;

procedure THojaCalc.LoadDoc;
var ooParams: variant;
begin
  if FileName='' then exit;
  //Is the program running? (Excel or OpenOffice)
  if not ProgLoaded then LoadProg;
  //Is there a doc already loaded?
  CloseDoc;
  DeskTop:= Unassigned;
  //OK, now try to open the doc...
  if IsExcel then begin
    Programa.WorkBooks.Open(FileName,3);
    Programa.Visible:= Visible;
    Document:= Programa.ActiveWorkBook;
    ActiveSheet:= Document.ActiveSheet;
  end;
  if IsOpenOffice then begin
    Desktop:=  Programa.CreateInstance('com.sun.star.frame.Desktop');
    //Optional parameters (visible)...
    ooParams:=    VarArrayCreate([0, 0], varVariant);
    //Next line stop working OK on OOo V2: Created blind, always blind!
    //so now it is create as visible, then set to non visible if requested
    //ooParams[0]:= ooCreateValue('Hidden', not Visible);
    ooParams[0]:= ooCreateValue('Hidden', false); //Create as visible, then make it not visible if necesary
    //Open the document...
    Document:= Desktop.LoadComponentFromURL(FileName2URL(FileName), '_blank', 0, ooParams);
    ActiveSheet:= ActivateSheetByIndex(1);
    //If has to be non visible, set it now...
    if not visible then
      Document.getCurrentController.getFrame.getContainerWindow.setVisible(false);
  end;
  if Tipo=thcNone then
    raise Exception.Create('No puedo leer el fichero "'+FileName+'" al no estar presente el programa necesario.');
end;

function THojaCalc.SaveDoc: boolean;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      Document.Save;
      result:= true;
    end;
    if IsOpenOffice then begin
      Document.Store;
      result:= true;
    end;
  end;
end;

//Added by Massimiliano Gozzi V0.92
//AsEXcel97 taken form V0.93 by Rômulo Silva Ramos
function THojaCalc.SaveDocAs(Name: string; AsExcel97: boolean = false): boolean;
var ooParams: variant;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      Document.Saveas(Name);
      FileName:= Name;
      result:= true;
    end;
    if IsOpenOffice then begin
      //I may need 1 or 2 params...
      if AsExcel97 then
        ooParams:= VarArrayCreate([0, 1], varVariant)
      else
        ooParams:= VarArrayCreate([0, 0], varVariant);
      //First one for prompting on overwrite (good idea!)
      ooParams[0]:= ooCreateValue('Overwrite', false);
      //Optionally tell OpenOffiec to use Excel97 .xls format
      if AsExcel97 then
        ooParams[1]:= ooCreateValue('FilterName', 'MS Excel 97');
      //Do the save!
      Document.StoreAsUrl(FileName2URL(Name), ooParams);
      FileName:= Name;
      result:= true;
    end;
  end;
end;

//Print the Doc...
function THojaCalc.PrintDoc: boolean;
var ooParams: variant;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      Document.PrintOut;
      result:= true;
    end;
    if IsOpenOffice then begin
      //NOTE: OpenOffice will print all sheets with Printable areas, but if no
      //printable areas are defined in the doc, it will print all entire sheets.
      //Optional parameters (wait until fully sent to printer)...
      ooParams:=   VarArrayCreate([0, 0], varVariant);
      ooParams[0]:= ooCreateValue('Wait', true);
      Document.Print(ooParams);
      result:= true;
    end;
  end;
end;

procedure THojaCalc.ShowPrintPreview;
begin
  if DocLoaded then begin
    //Force visibility of the doc...
    Visible:= true;
    if IsExcel then
      Document.PrintOut(,,,true);
    if IsOpenOffice then
      ooDispatch('.uno:PrintPreview', Unassigned);
  end;
end;

procedure THojaCalc.SetVisible(v: boolean);
begin
  if DocLoaded and (v<>fVisible) then begin
    if IsExcel then
      Programa.Visible:= v;
    if IsOpenOffice then
      Document.getCurrentController.getFrame.getContainerWindow.setVisible(v);
    fVisible:= v;
  end;
end;

procedure THojaCalc.CloseDoc;
begin
  if DocLoaded then begin
    //Close it...
    try
      if IsOpenOffice then Document.Dispose;
      if IsExcel      then Document.close;
    finally end;
    //Clean up both "pointer"...
    Document:= Null;
    ActiveSheet:= Null;
  end;
end;

function THojaCalc.GetDocLoaded: boolean;
begin
  result:= not (VarIsEmpty(Document) or VarIsNull(Document));
end;

// *********************
// ** Managing sheets **
// *********************

function THojaCalc.GetCountSheets: integer;
begin
  result:= 0;
  if DocLoaded then begin
    if IsExcel      then result:= Document.Sheets.count;
    if IsOpenOffice then result:= Document.getSheets.GetCount;
  end;
end;

//Index is 1 based in Excel, but OpenOffice uses it 0-based
//Here we asume 1-based so OO needs to activate (nIndex-1)
function THojaCalc.ActivateSheetByIndex(nIndex: integer): boolean;
begin
  result:= false;
  if DocLoaded then begin
    //Exists this sheet number?
    if (nIndex<1) then
      raise Exception.Create('Can not activate sheet #'+IntToStr(nIndex));
    while (nIndex>CountSheets) do begin
      ActivateSheetByIndex(CountSheets);
      AddNewSheet('Nueva hoja '+IntToStr(CountSheets+1));
      sleep(100); //Needs time to do it!
    end;
    //Activate it now...
    if IsExcel then begin
      Document.Sheets[nIndex].activate;
      ActiveSheet:= Document.ActiveSheet;
      result:= true;
    end;
    if IsOpenOffice then begin
      ActiveSheet:= Document.getSheets.getByIndex(nIndex-1);
      result:= true;
    end;
    sleep(100); //Asyncronus, so better give it time to make the change
  end;
end;

//Find a sheet by its name...
function THojaCalc.ActivateSheetByName(SheetName: string; CaseSensitive: boolean): boolean;
var
  OldActiveSheet: variant;
  i: integer;
begin
  result:= false;
  if DocLoaded then begin
    if CaseSensitive then begin
      //Find the EXACT name...
      if IsExcel then begin
        Document.Sheets[SheetName].Select;
        ActiveSheet:= Document.ActiveSheet;
        result:= true;
      end;
      if IsOpenOffice then begin
        ActiveSheet:= Document.getSheets.getByName(SheetName);
        result:= true;
      end;
    end else begin
      //Find the Sheet regardless of the case...
      OldActiveSheet:= ActiveSheet;
      for i:= 1 to GetCountSheets do begin
        ActivateSheetByIndex(i);
        if UpperCase(ActiveSheetName)=UpperCase(SheetName) then begin
          result:= true;
          Exit;
        end;
      end;
      //If not found, let the old active sheet active...
      ActiveSheet:= OldActiveSheet;
    end;
  end;
end;

//Name of the active sheet?
function THojaCalc.GetActiveSheetName: string;
begin
  if DocLoaded then begin
    if IsExcel then
      result:= ActiveSheet.Name;
    if IsOpenOffice then
      result:= ActiveSheet.GetName;
  end;
end;
procedure THojaCalc.SetActiveSheetName(NewName: string);
begin
  if DocLoaded then begin
    //Clean name first...
    NewName := ValidateSheetName(NewName);
    if IsExcel then
      Programa.ActiveSheet.Name:= NewName;
    if IsOpenOffice then begin
      ActiveSheet.setName(NewName);
      //This code always changes the name of "visible" sheet, not active one!
      //ooParams:= VarArrayCreate([0, 0], varVariant);
      //ooParams[0]:= ooCreateValue('Name', NewName);
      //ooDispatch('.uno:RenameTable', ooParams);
    end;
  end;
end;

//Check for sheet protection (password)...
function THojaCalc.IsActiveSheetProtected: boolean;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then
      result:= ActiveSheet.ProtectContents;
    if IsOpenOffice then
      result:= ActiveSheet.IsProtected;
  end;
end;

//WARNING: This function is NOT dual, only works for Excel docs!
//Send active sheet to default printer (as seen in preview window)...
function THojaCalc.PrintActiveSheet: boolean;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      ActiveSheet.PrintOut;
      result:= true;
    end;
    if IsOpenOffice then begin
      raise Exception.Create('Function "PrintActiveSheet" still not working in OpenOffice!');
      //ActiveSheet.Print;
      result:= false;
    end;
  end;
end;

//WARNING: This function is NOT dual, only works for Excel docs!
//Select and print sheets from 1 upto -excluded- the one with that name.
//It is interesting for understanding how to pass an array of objects to excel.
function THojaCalc.PrintSheetsUntil(LastSheetName: string): boolean;
var
  i, last: integer;
  Hojas: variant;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      //Macro from Excel:
      //  Sheets(Array("Hoja1", "Hoja2")).Select
      //  ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
      //
      //Witch sheet number correspond to the one previous to "LastSheetName"?
      Last:= 0;
      for i:= 2 to CountSheets do begin
        ActivateSheetByIndex(i);
        if UpperCase(ActiveSheetName)=UpperCase(LastSheetName) then begin
          Last:= i-1;
          break;
        end;
      end;
      //Not found?
      if Last=0 then exit;
      //Create an array of variants -windows standard type- this big...
      Hojas:= VarArrayCreate([1, Last], varVariant);
      //Fill it with the Sheet names...
      for i:= 1 to Last do begin
        ActivateSheetByIndex(i);
        Hojas[i]:= ActiveSheetName;
      end;
      //Print all this array of sheets...
      Programa.Sheets[Hojas].Select;
      Programa.ActiveWindow.SelectedSheets.PrintOut;
      //Done!
      result:= true;
    end;
    if IsOpenOffice then begin
      raise Exception.Create('Function "PrintSheetsUntil" not working in OpenOffice!');
      result:= false;
    end;
  end;
end;

//Add a new sheet, name it, and make it the active sheet...
procedure THojaCalc.AddNewSheet(NewName: string);
var
  ooSheets: variant;
begin
  NewName := ValidateSheetName(NewName);
  if DocLoaded then begin
    if IsExcel then begin
      Document.WorkSheets.Add(null, Document.ActiveSheet, 1);
      Document.ActiveSheet.Name:= NewName;
      //Active sheet has move to this new one, so I need to update the var
      ActiveSheet:= Document.ActiveSheet;
    end;
    if IsOpenOffice then begin
      ooSheets:= Document.getSheets;
      ooSheets.insertNewByName(NewName, 1);
      //Redefine active sheet to this new one
      ActiveSheet:= ooSheets.getByName(NewName);
    end;
  end;
end;

//Clean a Sheet name so it will not cause problems
function THojaCalc.ValidateSheetName(Name: string): string;
begin
  result := StringReplace(name,   ':', '_',[rfReplaceAll]);
  result := StringReplace(result, '/', '_',[rfReplaceAll]);
  result := StringReplace(result, '\', '_',[rfReplaceAll]);
  result := StringReplace(result, '?', '_',[rfReplaceAll]);
  result := StringReplace(result, '*', '_',[rfReplaceAll]);
  result := StringReplace(result, '[', '_',[rfReplaceAll]);
  result := StringReplace(result, ']', '_',[rfReplaceAll]);
  result := StringReplace(result, '"', '_',[rfReplaceAll]);
  if (Trim(result)='') then
    result:= 'Plan' + IntToStr(CountSheets);
  result:= Copy(result, 1, 31);
end;

// ************************
// ** Manage  the  cells **
// ** in the ActiveSheet **
// ************************

//Read/Write cell text (formula en Excel) by index
//OpenOffice start at cell (0,0) while Excel at (1,1)
//Also, Excel uses (row, col) and OpenOffice uses (col, row)
function THojaCalc.GetCellText(row, col: integer): string;
begin
  if DocLoaded then begin
    if IsExcel then      result:= ActiveSheet.Cells[row, col].Text;
    if IsOpenOffice then result:= ActiveSheet.getCellByPosition(col-1, row-1).getFormula;
  end;
end;
procedure  THojaCalc.SetCellText(row, col: integer; Txt: string);
begin
  if DocLoaded then begin
    if IsExcel then begin
      ActiveSheet.Cells[row, col].Select;
      Programa.ActiveCell.Value:= Txt;
    end;
    if IsOpenOffice then
      ActiveSheet.getCellByPosition(col-1, row-1).setFormula(Txt);
  end;
end;

//Read/Write cell text (formula in excel) by name instead of position
//For instance, you can set the value for cell 'NewSheet!A12' or similar
//NOTE: If range contains several cells, first one will be used.
function THojaCalc.GetCellTextByName(Range: string): string;
var OldActiveSheet: variant;
begin
  if DocLoaded then begin
    if IsExcel then begin
      result:=  Programa.Range[Range].Text; //Set 'Formula' but Get 'Text';
    end;
    if IsOpenOffice then begin
      OldActiveSheet:= ActiveSheet;
      //If range is in the form 'NewSheet!A1' then first change sheet to 'NewSheet'
      if pos('!', Range) > 0 then begin
        //Activate the proper sheet...
        if not ActivateSheetByName(Copy(Range, 1, pos('!', Range)-1), false) then
          raise Exception.Create('Sheet "'+Copy(Range, 1, pos('!', Range)-1)+'" not present in the document.');
        Range:= Copy(Range, pos('!', Range)+1, 999);
      end;
      result:= ActiveSheet.getCellRangeByName(Range).getCellByPosition(0,0).getFormula;
      ActiveSheet:= OldActiveSheet;
    end;
  end;
end;
procedure  THojaCalc.SetCellTextByName(Range: string; Txt: string);
var OldActiveSheet: variant;
begin
  if DocLoaded then begin
    if IsExcel then begin
      Programa.Range[Range].formula:= Txt;
    end;
    if IsOpenOffice then begin
      OldActiveSheet:= ActiveSheet;
      //If range is in the form 'NewSheet!A1' then first change sheet to 'NewSheet'
      if pos('!', Range) > 0 then begin
        //Activate the proper sheet...
        if not ActivateSheetByName(Copy(Range, 1, pos('!', Range)-1), false) then
          raise Exception.Create('Sheet "'+Copy(Range, 1, pos('!', Range)-1)+'" not present in the document.');
        Range:= Copy(Range, pos('!', Range)+1, 999);
      end;
      ActiveSheet.getCellRangeByName(Range).getCellByPosition(0,0).SetFormula(Txt);
      ActiveSheet:= OldActiveSheet;
    end;
  end;
end;

procedure THojaCalc.BackgroundColor(row,col: integer; color: TColor);
begin
  if DocLoaded then begin
    if IsExcel then begin
      Programa.ActiveSheet.Cells[row,col].Interior.Color := color;
    end;
    if IsOpenOffice then begin
      ActiveSheet.getCellByPosition(col-1, row-1).CellBackColor := color;
    end;
  end;
end;

procedure THojaCalc.FontSize(row, col, size: integer);
begin
  if DocLoaded then begin
    if IsExcel then begin
      Programa.ActiveSheet.Cells[row,col].Font.Size := size;
    end;
    if IsOpenOffice then begin
      ActiveSheet.getCellByPosition(col-1, row-1).getText.createTextCursor.CharHeight:= size;
    end;
  end;
end;

procedure THojaCalc.Bold(row,col: integer);
const ooBold: integer = 150; //150 = com.sun.star.awt.FontWeight.BOLD
begin
  if DocLoaded then begin
    if IsExcel then begin
      Programa.ActiveSheet.Cells[row,col].Font.Bold:= true;
    end;
    if IsOpenOffice then begin
      ActiveSheet.getCellByPosition(col-1, row-1).getText.createTextCursor.CharWeight:= ooBold;
    end;
  end;
end;

procedure THojaCalc.ColumnWidth(col, width: integer); //Width in 1/100 of mm.
begin
  if DocLoaded then begin
    if IsExcel then begin
      //Excel use the width of '0' as the unit, we do an aproximation: Width '0' = 2 mm.
      Programa.ActiveSheet.Cells[1, col].ColumnWidth:= width/100/3;
    end;
    if IsOpenOffice then begin
      ActiveSheet.getCellByPosition(col-1, 0).getColumns.getByIndex(0).Width:= width;
    end;
  end;
end;

// ***************************
// ** OpenOffice only stuff **
// ***************************

//Change 'C:\File.txt' into 'file:///c:/File.txt' (for OpenOffice OpenURL)
function THojaCalc.FileName2URL(FileName: string): string;
begin
  result:= '';
  if LowerCase(copy(FileName,1,8))<>'file:///' then
    result:= 'file:///';
  result:= result + StringReplace(FileName, '\', '/', [rfReplaceAll, rfIgnoreCase]);
end;

function THojaCalc.ooCreateValue(ooName: string; ooData: variant): variant;
var
  ooReflection: variant;
begin
  if IsOpenOffice then begin
    ooReflection:= Programa.createInstance('com.sun.star.reflection.CoreReflection');
    ooReflection.forName('com.sun.star.beans.PropertyValue').createObject(result);
    result.Name := ooName;
    result.Value:= ooData;
  end else begin
    raise Exception.Create('ooValue imposible to create, load OpenOffice first!');
  end;
end;

procedure THojaCalc.ooDispatch(ooCommand: string; ooParams: variant);
var
  ooDispatcher, ooFrame: variant;
begin
  if DocLoaded and IsOpenOffice then begin
    if (VarIsEmpty(ooParams) or VarIsNull(ooParams)) then
      ooParams:= VarArrayCreate([0, -1], varVariant);
    ooFrame:= Document.getCurrentController.getFrame;
    ooDispatcher:= Programa.createInstance('com.sun.star.frame.DispatchHelper');
    ooDispatcher.executeDispatch(ooFrame, ooCommand, '', 0, ooParams);
  end else begin
    raise Exception.Create('Dispatch imposible, load a OpenOffice doc first!');
  end;
end;

end.


Last edited by bitfarmer on Wed Jan 20, 2010 2:56 am; edited 3 times in total
Back to top
View user's profile Send private message Visit poster's website
Petr
Newbie
Newbie


Joined: 03 May 2006
Posts: 2
PostPosted: Wed May 03, 2006 3:36 am    Post subject: OpenOffice Reply with quote
Hi,
the code is Ok. I have only problems by using function SaveDoc. Using this function causes : com.sun..star.ErrorIOException, what is the problem ? Can you please send any advise ?
Thank you
Petr
Back to top
View user's profile Send private message
nisbus
Newbie
Newbie


Joined: 01 Jun 2006
Posts: 1
PostPosted: Thu Jun 01, 2006 11:56 am    Post subject: CellCount Reply with quote
Hi,

Thanks alot for cracking this.
I'm using this to export StringGrids from Delphi to OpenOffice and Excel.
I would like to be able to read OpenOffice Calc and Excel documents into a StringGrid as well but I don't know how to count the rows and columns within the open document.

Thank again,
nisbus
Back to top
View user's profile Send private message
Petr
Newbie
Newbie


Joined: 03 May 2006
Posts: 2
PostPosted: Thu Jun 01, 2006 10:16 pm    Post subject: Reply with quote
Hi

my opinien is, that the only possible way is to open the document as non visible then count the rows and columns and then the document close. If you know any other better solution, I am interested in it too.

Thank
Petr
Back to top
View user's profile Send private message
ChrBlack
General User
General User


Joined: 01 Jun 2006
Posts: 7
Location: Hungary
PostPosted: Tue Jun 06, 2006 10:47 pm    Post subject: OOo2.0.2 Reply with quote
Hi!

This code works with OpenOffice 2.x? I have some problems with OO functions/procedures in the code above, while Excel code works fine. For examlpe ActiveSheetByName, ActiveSheetByIndex, OpenDoc,SaveDoc. May something changed in OO 2.x, while the code works with OO 1.x ?

Cheers:
ChrBlack
Back to top
View user's profile Send private message
RomuloRamos
Newbie
Newbie


Joined: 02 Oct 2009
Posts: 1
PostPosted: Fri Oct 02, 2009 9:28 am    Post subject: Reply with quote
I made some modification on the code, this is my way to thanks the original code:

Code:


// ********************************************
// ** Object for dual  SpreadSheet  managing **
// ** using Excel or OpenOffice automaticaly **
// ** By: Sergio Hernandez                   **
// ** oficina(at)hcsoft.net, CopyLeft 2004   **
// ** Version 0.92 18-05-2004 (DDMMYYYY)     **
// ** Use it freely, change it, etc.         **
// ********************************************

// ********************************************
// ** Modify By: Rômulo Silva Ramos          **
// ** Version 0.93 01-10-2009 (DDMMYYYY)     **
// ********************************************

{EXAMPLE OF USE
  //Create object: We have two flavours:
  //(A) from an existing file...
  HCalc:= TSheetCalc.create(OpenDialog.FileName, false);
  //(B) from a blank document...
  HCalc:= TSheetCalc.create(thcOpenOffice); //OpenOffice doc if possible, please
  HCalc.FileName:= 'C:\MyNewDoc'; //Needs a file name before you SaveDoc!
  //--end of creation.
  HCalc.ActivateSheetByIndex(2); //Activate second sheet
  if HCalc.IsActiveSheetProtected then
    ShowMessage('2nd sheet of name "'+HCalc.ActiveSheetName+'" IS protected');
  //Change a cell value (well, change formula, not the double float value)
  if HCalc.CellText[i,2] = '' then HCalc.CellText[i,2] := 'Hello world!';
  HCalc.AddNewSheet('New Sheet');
  HCalc.PrintDoc;
  HCalc.SaveDoc;
  HCalc.Free;

  Other exemple:
       var
          Plan : TSheetCalc;
       begin
          Plan := TSheetCalc.Create(thcExcel,false);
          Plan.FileName := arquivo;

          Plan.ActivateSheetByIndex(1);

          Plan.CellText[1,1] := 'Abstraction complete';

          Plan.SaveDoc;
          Plan.Visible := True;
       begin
}

{TODO LIST:
  -PrintActiveSheet is not working for OpenOffice (is it even possible?)
  -A way to write a date in a cell changing also the format (Excel is herratic in that)
}

{CHANGE LOG:
 V0.93:
   -FontSize(Row, Col, Size): change font size in that cell.
   -Change AddNewSheet to add a new sheet in end at sheet list
   -Add ValidateSheetName to validate sheet names when adding or renaming a sheet
   -Change in SaveDoc to use SaveAs/StoreAsUrl
   -Change name 'Hoja' para 'Sheet'
   -Change name 'Programa' para 'SheetSoftware'
   -Change exception.create messages:
       'No puedo leer el fichero "'+FileName+'" al no estar presente el programa necesario.'
       to
       'Not able to read the file "'+FileName+'". Probably not exist a software installed to open it.'
   -Add other exemple of utilization   
 V0.92:
   -SetActiveSheetName didn't change the name to the right sheet on OpenOffice.
   -PrintPreview: New procedure to show up the print preview window.
   -Bold(Row, Col): Make bold the text in that cell.
   -ColumnWidth(col, width): To change a column width.
 V0.91:
   -NewDoc: New procedure for creating a blank doc (used in create)
   -Create from empty doc adds a blank document and take visibility as parameter.
   -New functions ooCreateValue and ooDispatch to clean up the code.
   -ActiveSheetName: Now is a read-write property, not a read-only function.
   -Visible: Now is a read-write property instead of a create param only.
 V0.9:
  -Create from empty doc now tries both programs (if OO fails try to use Excel).
  -CellTextByName: Didn't work on Excel docs.
}

unit uSCPPlanilhaEletronica;

interface

uses Variants, SysUtils, ComObj,Graphics;

//thcError: Tried to open but both failes
//thcNone:  Haven't tried still to open any
type TTipoSheetCalc = (thcError, thcNone, thcExcel, thcOpenOffice);

type TSheetCalc = class(TObject)
private
  fVisible:  boolean;
  //Program loaded stuff...
  procedure  LoadProg;
  procedure  CloseProg;
  function   GetProgLoaded: boolean;
  procedure  NewDoc;
  procedure  LoadDoc;
  procedure  CloseDoc;
  function   GetDocLoaded: boolean;
  function   GetIsExcel: boolean;
  function   GetIsOpenOffice: boolean;
  procedure  SetVisible(v: boolean);
  //Sheets stuff..
  function   GetCountSheets: integer;
  function   GetActiveSheetName: string;
  procedure  SetActiveSheetName(NewName: string);
  //Cells stuff...
  function   GetCellText(row,col: integer): string;
  procedure  SetCellText(row,col: integer; Txt: string);
  function   GetCellTextByName(Range: string): string;
  procedure  SetCellTextByName(Range: string; Txt: string);
  //OpenOffice only stuff...
  function   FileName2URL(FileName: string): string;
  procedure  ooDispatch(ooCommand: string; ooParams: variant);
  function   ooCreateValue(ooName: string; ooData: variant): variant;
  //aux functions
  function   ValidateSheetName(Name:string): string;

public
  Tipo: TTipoSheetCalc;    //Witch program was used to manage the doc?
  FileName:    string;    //In windows FileName format C:\MyDoc.XXX
  SheetSoftware:    variant;   //Excel or OpenOfice instance created.
  DeskTop:     variant;   //OpenOffice desktop reference (not used now).
  Document:    variant;   //Document opened.
  ActiveSheet: variant;   //Active sheet.
  //Object internals...     
  constructor  Create(Name: string; MakeVisible: boolean); overload;
  constructor  Create(MyTipo: TTipoSheetCalc; MakeVisible: boolean); overload;
  destructor   Destroy; override;
  //Program loaded stuff...
  function     SaveDoc: boolean;
  function     PrintDoc: boolean;
  procedure    ShowPrintPreview;
  property     ProgLoaded: boolean     read GetProgLoaded;
  property     DocLoaded:  boolean     read GetDocLoaded;
  property     IsExcel: boolean        read GetIsExcel;
  property     IsOpenOffice: boolean   read GetIsOpenOffice;
  property     Visible: boolean        read fVisible           write SetVisible;
  //Sheets stuff...
  function     ActivateSheetByIndex(nIndex: integer): boolean;
  function     ActivateSheetByName(SheetName: string; CaseSensitive: boolean): boolean;
  function     IsActiveSheetProtected: boolean;
  function     PrintActiveSheet: boolean;
  procedure    AddNewSheet(NewName: string);
  property     CountSheets:  integer   read GetCountSheets;
  property     ActiveSheetName: string read GetActiveSheetName write SetActiveSheetName;
  //Cells stuff...
  procedure    BackgroundColor(row,col: integer; color:TColor); 
  procedure    FontSize(row,col,size: integer);
  procedure    Bold(row,col: integer);
  procedure    ColumnWidth(col, width: integer); //Width in 1/100 of mm.
  property     CellText[f,c: integer]: string read GetCellText write SetCellText;
  property     CellTextByName[Range: string]: string read GetCellTextByName write SetCellTextByName;

end;

implementation

// ************************
// ** Create and destroy **
// ************************

//Create with an empty doc of requested type (use thcExcel or thcOpenOffice)
//Remember to define FileName before calling to SaveDoc
constructor TSheetCalc.Create(MyTipo: TTipoSheetCalc; MakeVisible: boolean);
var
  i: integer;
  IsFirstTry: boolean;
begin
  //Close all opened things first...
  if DocLoaded  then CloseDoc;
  if ProgLoaded then CloseProg;
  //I will try to open twice, so if Excel fails, OpenOffice is used instead
  IsFirstTry:= true;
  for i:= 1 to 2 do begin
    //Try to open OpenOffice...
    if (MyTipo = thcOpenOffice) or (MyTipo = thcNone)then begin
      SheetSoftware:= CreateOleObject('com.sun.star.ServiceManager');
      if ProgLoaded then begin
        Tipo:= thcOpenOffice;
        break;
      end else begin
        if IsFirstTry then begin
          //Try Excel as my second choice
          MyTipo:= thcExcel;
          IsFirstTry:= false;
        end else begin
          //Both failed!
          break;
        end;
      end;
    end;
    //Try to open Excel...
    if (MyTipo = thcExcel) or (MyTipo = thcNone) then begin
      SheetSoftware:= CreateOleObject('Excel.Application');
      if ProgLoaded then begin
        Tipo:= thcExcel;
        break;
      end else begin
        if IsFirstTry then begin
          //Try OpenOffice as my second choice
          MyTipo:= thcOpenOffice;
          IsFirstTry:= false;
        end else begin
          //Both failed!
          break;
        end;
      end;
    end;
  end;
  //Was it able to open any of them?
  if Tipo = thcNone then begin
    Tipo:= thcError;
    raise exception.create('TSheetCalc.create failed, may be no Office is installed?');
  end;
  //Add a blank document...
  fVisible:= MakeVisible;
  NewDoc;
end;

constructor TSheetCalc.Create(Name: string; MakeVisible: boolean);
begin
  //Store values...
  FileName:= Name;
  fVisible:=  MakeVisible;
  //Open program and document...
  LoadProg;
  LoadDoc;
end;

destructor TSheetCalc.Destroy;
begin
  CloseDoc;
  CloseProg;
  inherited;
end;

// *************************
// ** Loading the program **
// ** Excel or OpenOffice **
// *************************

procedure TSheetCalc.LoadProg;
begin
  if ProgLoaded then CloseProg;
  if (UpperCase(ExtractFileExt(FileName))='.XLS') then begin
    //Excel is the primary choice...
    SheetSoftware:= CreateOleObject('Excel.Application');
    if ProgLoaded then Tipo:= thcExcel;
  end;
  //Not lucky with Excel? Another filetype? Let's go with OpenOffice...
  if Tipo = thcNone then begin
    //Try with OpenOffice...
    SheetSoftware:= CreateOleObject('com.sun.star.ServiceManager');
    if ProgLoaded then Tipo:= thcOpenOffice;
  end;
  //Still no program loaded?
  if not ProgLoaded then begin
    Tipo:= thcError;
    raise Exception.create('TSheetCalc.create failed, may be no Office is installed?');
  end;
end;

procedure TSheetCalc.CloseProg;
begin
  if DocLoaded then CloseDoc;
  if ProgLoaded then begin
    try
      if IsExcel then SheetSoftware.Quit;
      SheetSoftware:= Unassigned;
    finally end;
  end;
  Tipo:= thcNone;
end;

//Is there any prog loaded? Witch one?
function TSheetCalc.GetProgLoaded: boolean;
begin
  result:= not (VarIsEmpty(SheetSoftware) or VarIsNull(SheetSoftware));
end;
function  TSheetCalc.GetIsExcel: boolean;
begin
  result:= (Tipo=thcExcel);
end;
function  TSheetCalc.GetIsOpenOffice: boolean;
begin
  result:= (Tipo=thcOpenOffice);
end;

// ************************
// ** Loading a document **
// ************************

procedure TSheetCalc.NewDoc;
var ooParams: variant;
begin
  //Is the program running? (Excel or OpenOffice)
  if not ProgLoaded then raise exception.create('No program loaded for the new document.');
  //Is there a doc already loaded?
  if DocLoaded then CloseDoc;
  DeskTop:= Unassigned;
  //OK, now try to create the doc...
  if IsExcel then begin
    SheetSoftware.WorkBooks.Add;
    SheetSoftware.Visible:= Visible;
    Document:= SheetSoftware.ActiveWorkBook;
    ActiveSheet:= Document.ActiveSheet;
  end;
  if IsOpenOffice then begin
    Desktop:=  SheetSoftware.CreateInstance('com.sun.star.frame.Desktop');
    //Optional parameters (visible)...
    ooParams:=    VarArrayCreate([0, 0], varVariant);
    ooParams[0]:= ooCreateValue('Hidden', not Visible);
    //Create the document...
    Document:= Desktop.LoadComponentFromURL('private:factory/scalc', '_blank', 0, ooParams);
    ActivateSheetByIndex(1);
  end;
end;

procedure TSheetCalc.LoadDoc;
var ooParams: variant;
begin
  if FileName='' then exit;
  //Is the program running? (Excel or OpenOffice)
  if not ProgLoaded then LoadProg;
  //Is there a doc already loaded?
  if DocLoaded then CloseDoc;
  DeskTop:= Unassigned;
  //OK, now try to open the doc...
  if IsExcel then begin
    SheetSoftware.WorkBooks.Open(FileName,3);
    SheetSoftware.Visible:= Visible;
    Document:= SheetSoftware.ActiveWorkBook;
    ActiveSheet:= Document.ActiveSheet;
  end;
  if IsOpenOffice then begin
    Desktop:=  SheetSoftware.CreateInstance('com.sun.star.frame.Desktop');
    //Optional parameters (visible)...
    ooParams:=    VarArrayCreate([0, 0], varVariant);
    ooParams[0]:= ooCreateValue('Hidden', not Visible);
    //Open the document...
    Document:= Desktop.LoadComponentFromURL(FileName2URL(FileName), '_blank', 0, ooParams);
    ActiveSheet:= ActivateSheetByIndex(1);
  end;
  if Tipo=thcNone then
    raise exception.create('Not able to read the file "'+FileName+'". Probably not exist a software installed to open it.');
end;

function TSheetCalc.SaveDoc: boolean;
var ooParams: variant;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      Document.SaveAs(FileName);
      result:= true;
    end;
    if IsOpenOffice then begin
      ooParams:=    VarArrayCreate([0, 0], varVariant);
      ooParams[0]:= ooCreateValue('FilterName', 'MS Excel 97');
      Document.StoreAsUrl('file:///'+StringReplace(FileName,'\','/',[rfReplaceAll]),ooParams);
      result:= true;
    end;
  end;
end;

//Print the Doc...
function TSheetCalc.PrintDoc: boolean;
var ooParams: variant;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      Document.PrintOut;
      result:= true;
    end;
    if IsOpenOffice then begin
      //NOTE: OpenOffice will print all sheets with Printable areas, but if no
      //printable areas are defined in the doc, it will print all entire sheets.
      //Optional parameters (wait until fully sent to printer)...
      ooParams:=   VarArrayCreate([0, 0], varVariant);
      ooParams[0]:= ooCreateValue('Wait', true);
      Document.Print(ooParams);
      result:= true;
    end;
  end;
end;

procedure TSheetCalc.ShowPrintPreview;
begin
  if DocLoaded then begin
    //Force visibility of the doc...
    Visible:= true;
    if IsExcel then
      Document.PrintOut(,,,true);
    if IsOpenOffice then
      ooDispatch('.uno:PrintPreview', Unassigned);
  end;
end;

procedure TSheetCalc.SetVisible(v: boolean);
begin
  if DocLoaded and (v<>fVisible) then begin
    if IsExcel then
      SheetSoftware.Visible:= v;
    if IsOpenOffice then
      Document.getCurrentController.getFrame.getContainerWindow.setVisible(v);
    fVisible:= v;
  end;
end;

procedure TSheetCalc.CloseDoc;
begin
  if DocLoaded then begin
    //Close it...
    try
      if IsOpenOffice then Document.Dispose;
      if IsExcel      then Document.close;
    finally end;
    //Clean up both "pointer"...
    Document:= Null;
    ActiveSheet:= Null;
  end;
end;

function TSheetCalc.GetDocLoaded: boolean;
begin
  result:= not (VarIsEmpty(Document) or VarIsNull(Document));
end;

// *********************
// ** Managing sheets **
// *********************

function TSheetCalc.GetCountSheets: integer;
begin
  result:= 0;
  if DocLoaded then begin
    if IsExcel      then result:= Document.Sheets.count;
    if IsOpenOffice then result:= Document.getSheets.GetCount;
  end;
end;

//Index is 1 based in Excel, but OpenOffice uses it 0-based
//Here we asume 1-based so OO needs to activate (nIndex-1)
function TSheetCalc.ActivateSheetByIndex(nIndex: integer): boolean;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      Document.Sheets[nIndex].activate;
      ActiveSheet:= Document.ActiveSheet;
      result:= true;
    end;
    if IsOpenOffice then begin
      ActiveSheet:= Document.getSheets.getByIndex(nIndex-1);
      result:= true;
    end;
    sleep(100); //Asyncronus, so better give it time to make the change
  end;
end;

//Find a sheet by its name...
function TSheetCalc.ActivateSheetByName(SheetName: string; CaseSensitive: boolean): boolean;
var
  OldActiveSheet: variant;
  i: integer;
begin
  result:= false;
  if DocLoaded then begin
    if CaseSensitive then begin
      //Find the EXACT name...
      if IsExcel then begin
        Document.Sheets[SheetName].Select;
        ActiveSheet:= Document.ActiveSheet;
        result:= true;
      end;
      if IsOpenOffice then begin
        ActiveSheet:= Document.getSheets.getByName(SheetName);
        result:= true;
      end;
    end else begin
      //Find the Sheet regardless of the case...
      OldActiveSheet:= ActiveSheet;
      for i:= 1 to GetCountSheets do begin
        ActivateSheetByIndex(i);
        if UpperCase(ActiveSheetName)=UpperCase(SheetName) then begin
          result:= true;
          Exit;
        end;
      end;
      //If not found, let the old active sheet active...
      ActiveSheet:= OldActiveSheet;
    end;
  end;
end;

//Name of the active sheet?
function TSheetCalc.GetActiveSheetName: string;
begin
  if DocLoaded then begin
    if IsExcel then
      result:= ActiveSheet.Name;
    if IsOpenOffice then
      result:= ActiveSheet.GetName;
  end;
end;
procedure TSheetCalc.SetActiveSheetName(NewName: string);
begin
  NewName := ValidateSheetName(NewName);

  if DocLoaded then begin
    if IsExcel then
      SheetSoftware.ActiveSheet.Name:= NewName;
    if IsOpenOffice then begin
      ActiveSheet.setName(NewName);
      //This code always changes the name of "visible" sheet, not active one!
      //ooParams:= VarArrayCreate([0, 0], varVariant);
      //ooParams[0]:= ooCreateValue('Name', NewName);
      //ooDispatch('.uno:RenameTable', ooParams);
    end;
  end;
end;

//Check for sheet protection (password)...
function TSheetCalc.IsActiveSheetProtected: boolean;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then
      result:= ActiveSheet.ProtectContents;
    if IsOpenOffice then
      result:= ActiveSheet.IsProtected;
  end;
end;

//WARNING: This function is NOT dual, only works for Excel docs!
//Send active sheet to default printer (as seen in preview window)...
function TSheetCalc.PrintActiveSheet: boolean;
begin
  result:= false;
  if DocLoaded then begin
    if IsExcel then begin
      ActiveSheet.PrintOut;
      result:= true;
    end;
    if IsOpenOffice then begin
      raise exception.create('Function "PrintActiveSheet" still not working in OpenOffice!');
      //ActiveSheet.Print;
      result:= false;
    end;
  end;
end;

//Add a new sheet, name it, and make it the active sheet...
procedure TSheetCalc.AddNewSheet(NewName: string);
var
  ooSheets: variant;
begin
  NewName := ValidateSheetName(NewName);

  if DocLoaded then begin
    if IsExcel then begin
      Document.WorkSheets.Add;
      Document.ActiveSheet.Move(After:=Document.Sheets[CountSheets]);
      Document.ActiveSheet.Name:= NewName;
      //Active sheet has move to this new one, so I need to update the var
      ActiveSheet:= Document.ActiveSheet;
    end;
    if IsOpenOffice then begin
      ooSheets:= Document.getSheets;
      ooSheets.insertNewByName(NewName, CountSheets);
      //Redefine active sheet to this new one
      ActiveSheet:= ooSheets.getByName(NewName);
    end;
  end;
end;

// ************************
// ** Manage  the  cells **
// ** in the ActiveSheet **
// ************************

//Read/Write cell text (formula en Excel) by index
//OpenOffice start at cell (0,0) while Excel at (1,1)
//Also, Excel uses (row, col) and OpenOffice uses (col, row)
function TSheetCalc.GetCellText(row, col: integer): string;
begin
  if DocLoaded then begin
    if IsExcel then      result:= ActiveSheet.Cells[row, col].Formula; //.Text;
    if IsOpenOffice then result:= ActiveSheet.getCellByPosition(col-1, row-1).getFormula;
  end;
end;
procedure  TSheetCalc.SetCellText(row, col: integer; Txt: string);
begin
  if DocLoaded then begin
    if IsExcel then      ActiveSheet.Cells[row, col].Formula:= Txt;
    if IsOpenOffice then ActiveSheet.getCellByPosition(col-1, row-1).setFormula(Txt);
  end;
end;

//Read/Write cell text (formula in excel) by name instead of position
//For instance, you can set the value for cell 'NewSheet!A12' or similar
//NOTE: If range contains several cells, first one will be used.
function TSheetCalc.GetCellTextByName(Range: string): string;
var OldActiveSheet: variant;
begin
  if DocLoaded then begin
    if IsExcel then begin
      result:=  SheetSoftware.Range[Range].Text; //Set 'Formula' but Get 'Text';
    end;
    if IsOpenOffice then begin
      OldActiveSheet:= ActiveSheet;
      //If range is in the form 'NewSheet!A1' then first change sheet to 'NewSheet'
      if pos('!', Range) > 0 then begin
        //Activate the proper sheet...
        if not ActivateSheetByName(Copy(Range, 1, pos('!', Range)-1), false) then
          raise exception.create('Sheet "'+Copy(Range, 1, pos('!', Range)-1)+'" not present in the document.');
        Range:= Copy(Range, pos('!', Range)+1, 999);
      end;
      result:= ActiveSheet.getCellRangeByName(Range).getCellByPosition(0,0).getFormula;
      ActiveSheet:= OldActiveSheet;
    end;
  end;
end;
procedure  TSheetCalc.SetCellTextByName(Range: string; Txt: string);
var OldActiveSheet: variant;
begin
  if DocLoaded then begin
    if IsExcel then begin
      SheetSoftware.Range[Range].formula:= Txt;
    end;
    if IsOpenOffice then begin
      OldActiveSheet:= ActiveSheet;
      //If range is in the form 'NewSheet!A1' then first change sheet to 'NewSheet'
      if pos('!', Range) > 0 then begin
        //Activate the proper sheet...
        if not ActivateSheetByName(Copy(Range, 1, pos('!', Range)-1), false) then
          raise exception.create('Sheet "'+Copy(Range, 1, pos('!', Range)-1)+'" not present in the document.');
        Range:= Copy(Range, pos('!', Range)+1, 999);
      end;
      ActiveSheet.getCellRangeByName(Range).getCellByPosition(0,0).SetFormula(Txt);
      ActiveSheet:= OldActiveSheet;
    end;
  end;
end;

procedure TSheetCalc.BackgroundColor(row,col: integer; color:TColor);
begin
  if DocLoaded then begin
    if IsExcel then begin
      SheetSoftware.ActiveSheet.Cells[row,col].Interior.Color := color;
    end;
    if IsOpenOffice then begin
      ActiveSheet.getCellByPosition(col-1, row-1).CellBackColor := color;
    end;
  end;
end;

procedure TSheetCalc.FontSize(row,col,size: integer);
begin
  if DocLoaded then begin
    if IsExcel then begin
      SheetSoftware.ActiveSheet.Cells[row,col].Font.Size := size;
    end;
    if IsOpenOffice then begin
      ActiveSheet.getCellByPosition(col-1, row-1).getText.createTextCursor.CharHeight:= size;
    end;
  end;
end;

procedure TSheetCalc.Bold(row,col: integer);
const ooBold: integer = 150; //150 = com.sun.star.awt.FontWeight.BOLD
begin
  if DocLoaded then begin
    if IsExcel then begin
      SheetSoftware.ActiveSheet.Cells[row,col].Font.Bold := true;
    end;
    if IsOpenOffice then begin
      ActiveSheet.getCellByPosition(col-1, row-1).getText.createTextCursor.CharWeight:= ooBold;
    end;
  end;
end;

procedure TSheetCalc.ColumnWidth(col, width: integer); //Width in 1/100 of mm.
begin
  if DocLoaded then begin
    if IsExcel then begin
      //Excel use the width of '0' as the unit, we do an aproximation: Width '0' = 2 mm.
      SheetSoftware.ActiveSheet.Cells[1, col].ColumnWidth:= width/100/3;
    end;
    if IsOpenOffice then begin
      ActiveSheet.getCellByPosition(col-1, 0).getColumns.getByIndex(0).Width:= width;
    end;
  end;
end;

// ***************************
// ** OpenOffice only stuff **
// ***************************

//Change 'C:\File.txt' into 'file:///c:/File.txt' (for OpenOffice OpenURL)
function TSheetCalc.FileName2URL(FileName: string): string;
begin
  result:= '';
  if LowerCase(copy(FileName,1,8))<>'file:///' then
    result:= 'file:///';
  result:= result + StringReplace(FileName, '\', '/', [rfReplaceAll, rfIgnoreCase]);
end;

function TSheetCalc.ooCreateValue(ooName: string; ooData: variant): variant;
var
  ooReflection: variant;
begin
  if IsOpenOffice then begin
    ooReflection:= SheetSoftware.createInstance('com.sun.star.reflection.CoreReflection');
    ooReflection.forName('com.sun.star.beans.PropertyValue').createObject(result);
    result.Name := ooName;
    result.Value:= ooData;
  end else begin
    raise exception.create('ooValue imposible to create, load OpenOffice first!');
  end;
end;

procedure TSheetCalc.ooDispatch(ooCommand: string; ooParams: variant);
var
  ooDispatcher, ooFrame: variant;
begin
  if DocLoaded and IsOpenOffice then begin
    if (VarIsEmpty(ooParams) or VarIsNull(ooParams)) then
      ooParams:= VarArrayCreate([0, -1], varVariant);
    ooFrame:= Document.getCurrentController.getFrame;
    ooDispatcher:= SheetSoftware.createInstance('com.sun.star.frame.DispatchHelper');
    ooDispatcher.executeDispatch(ooFrame, ooCommand, '', 0, ooParams);
  end else begin
    raise exception.create('Dispatch imposible, load a OpenOffice doc first!');
  end;
end;

function TSheetCalc.ValidateSheetName(Name: string): string;
begin
  result := Name;
  if trim(result)= '' then result := 'Plan' + IntToStr(CountSheets);
  if Pos(':',result) > 0 then result := StringReplace(result,':',' ',[rfReplaceAll]);
  if Pos('/',result) > 0 then result := StringReplace(result,'/',' ',[rfReplaceAll]);
  if Pos('?',result) > 0 then result := StringReplace(result,'?',' ',[rfReplaceAll]);
  if Pos('*',result) > 0 then result := StringReplace(result,'*',' ',[rfReplaceAll]);
  if Pos('[',result) > 0 then result := StringReplace(result,'[',' ',[rfReplaceAll]);
  if Pos(']',result) > 0 then result := StringReplace(result,']',' ',[rfReplaceAll]);
  if Length(result) > 31 then result := Copy(result,1,31);
end;

end.

Back to top
View user's profile Send private message
bitfarmer
General User
General User


Joined: 06 May 2004
Posts: 36
Location: Murcia, Spain
PostPosted: Mon Oct 05, 2009 1:37 am    Post subject: Reply with quote
Hi Romulo, thanxs a lot for posting your enhacements!

I have merged them with my actual version (it was some time ago I update it here, so small changes were only on my version, like OOo V2 compatibility, sorry for this), you can grab it on the edited first post.

It was unfortunate that you changed some object names, I had to revert them to theirs original ones in order to compile on all my existing proyects. I think it was a bad idea, we must keep our code as interchangeable as possible in order to cooperate more easily.

New version V0.95 works OK with OOo V2.x, the changes were small, but tricky to find out what was not working and how to fix it, so I left all changes commented so the info is there if other one is trying to do the same porting to OOo V2.

A new function SaveDocAs(Name: string): boolean; (Added by Massimiliano Gozzi) has been enhaced to allow you to force OOo to save on Excel97 xls format, and internally use URL save mode, so SaveDoc has been reverted to its original form.

Also AddNewSheet has been reverted so it again creates it following the actual sheet, instructions on how to force it to be created at the end of the list have been added so you can chose using it in both flavours (my code needs to create sheets not at the end sometimes).

A function to change color background was on your new code, but was uncommented on the changelist, I added it for you. You are wellcome!

Well, thats all folks!
Sergio Hernandez.
Back to top
View user's profile Send private message Visit poster's website
yapt
General User
General User


Joined: 30 Jan 2007
Posts: 30
PostPosted: Wed Jan 20, 2010 2:27 am    Post subject: Reply with quote
Sergio,

where is the last version of this component ?

Thanks and congratulations for your work.

Un saludo....
Back to top
View user's profile Send private message
bitfarmer
General User
General User


Joined: 06 May 2004
Posts: 36
Location: Murcia, Spain
PostPosted: Wed Jan 20, 2010 2:47 am    Post subject: Reply with quote
Hi yapt, the last version submited of the code is always in the first post of this thread, when something chages, I edit it and copy/paste my actual unit there... if i remember to do it!

I had a newer version on my PC, I had commented the change (small one) and updated the first post, so now you have V0.96 instead of V0.95
Back to top
View user's profile Send private message Visit poster's website
yapt
General User
General User


Joined: 30 Jan 2007
Posts: 30
PostPosted: Wed Jan 20, 2010 3:18 am    Post subject: Reply with quote
Ok Sergio,

muchas gracias.
Back to top
View user's profile Send private message
PauLita
Newbie
Newbie


Joined: 03 Feb 2010
Posts: 2
Location: Vilnius
PostPosted: Wed Feb 03, 2010 10:41 am    Post subject: Reply with quote
Hi,

can anybody help me to perfom Excel sheet row replication:

for Microsoft Excel it was

Code:

Sheet.Rows[r].Copy;
Sheet.Rows[r+1].Insert(xlDown);


how this could be done with OpenOffice Calc?

TIA,
Rimvydas
Back to top
View user's profile Send private message
PauLita
Newbie
Newbie


Joined: 03 Feb 2010
Posts: 2
Location: Vilnius
PostPosted: Sat Feb 06, 2010 5:15 am    Post subject: Reply with quote
Did it. If someone is interested:



Code:
         Programa     := CreateOleObject( 'com.sun.star.ServiceManager' );
         ooParams     := VarArrayCreate([0,0],varVariant);
         ooParams[0]  := Programa.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
         ooView       := Document.getCurrentController;
         ooFrame      := ooView.getFrame;
         ooDispatcher := Programa.createInstance('com.sun.star.frame.DispatchHelper');
// copy to clipboard
         oRange := Sheet.GetRows.GetByIndex(rl-1);
         ooView.Select( oRange );
         ooDispatcher.executeDispatch( ooFrame, '.uno:Copy',  '', 0, ooParams );
// add one row to the table
         Sheet.GetRows.InsertByIndex(rl,1);
// paste from clipboard
         oRange := Sheet.GetRows.GetByIndex(rl);
         ooView.Select( oRange );
         ooDispatcher.executeDispatch( ooFrame, '.uno:Paste',  '', 0, ooParams );
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group