Trabajar con documentos Excel utilizando Delphi

Seguidamente os dejo una lista bastante amplia de funciones escritas en Delphi que se utilizan cuando estamos programando una aplicación que necesita crear, acceder o modificar un documento Excel.

Crear un documento

Hace visible una hoja de cálculo

Añade un Workbook

Añadir hoja 

Borrar hoja

Contar hojas

Obtiene una hoja

Selecciona una hoja

Graba el Workbook

Cierra el WorkBook

Cierra Excel

Pone un rango

Obtiene un rango

Pone el ancho de columna

Pone el Alto de una columna

Obtiene el formato de un rango de celdas

Pone el formato de un rango de celdas

Pone la alineación horizontal

Pone alineación vertical

Pone la orientación

Pone el ajuste de texto

Comprime el texto hasta ajustar

Funde celdas

Pone un rango de fonts

Configura el borde de un rango de celdas

Pone un patrón a un rango de celdas

Pone una imagen de fondo

Muestra líneas de división

Muestra el diálogo configuración de la impresora

Muestra el diálogo de la impresora

Muestra la ventana activa

Pone la orientación de la página

Pone el tamaño del papel

Pone el área de impresión

Imprime las líneas de división

Vista previa de la impresión

Imprime indicando el número de copias

Crear un documento

uses
ComObj, Classes;

var
E:variant;

Function CreateExcel:boolean;

begin

CreateExcel:=true;

try

E:=CreateOleObject('Excel.Application');

except

CreateExcel:=false;

end;

end;

End;

Hace visible una hoja de cálculo

Function VisibleExcel(visible:boolean): boolean;

begin

VisibleExcel:=true;

try

E.visible:=visible;

except

VisibleExcel:=false;

end;

End;

Añade un Workbook

Function AddWorkBook:boolean;

begin

AddWorkBook:=true;

try

E.Workbooks.Add;

except

AddWorkBook:=false;

end;

End;

Añadir hoja

Function AddSheet(newsheet:string):boolean;

begin

AddSheet:=true;

try

E.Sheets.Add;

E.ActiveSheet.Name:=newsheet;

except

AddSheet:=false;

end;

End;

Borrar hoja

Function DeleteSheet(sheet:variant):boolean;

begin

DeleteSheet:=true;

try

E.DisplayAlerts:=False;

E.Sheets[sheet].Delete;

E.DisplayAlerts:=True;

except

DeleteSheet:=false;

end;

End;

Contar hojas

Function CountSheets:integer;

begin

try

CountSheets:=E.ActiveWorkbook.Sheets.Count;

except

CountSheets:=-1;

end;

End;

Obtiene una hoja

Function GetSheets(value:TStrings):boolean;

var
a_:integer;

begin

GetSheets:=true;

value.Clear;

try

for
a_:=1 to E.ActiveWorkbook.Sheets.Count do

value.Add(E.ActiveWorkbook.Sheets.Item[a_].Name);

except

GetSheets:=false;

value.Clear;

end;

End;

Selecciona una hoja

Function SelectSheet (sheet:variant):boolean;

begin

SelectSheet:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Select;

except

SelectSheet:=false;

end;

End;

Graba el Workbook

Function SaveWorkBookAs(file_:string): boolean;

begin

SaveWorkBookAs:=true;

try

E.DisplayAlerts:=False;

E.ActiveWorkbook.SaveAs(file_);

E.DisplayAlerts:=True;

except

SaveWorkBookAs:=false;

end;

End;

Cierra el WorkBook

Function CloseWorkBook:boolean;

begin

CloseWorkBook:=true;

try

E.ActiveWorkbook.Close;

except

CloseWorkBook:=false;

end;

End;

Cierra Excel

Function CloseExcel:boolean;

begin

CloseExcel:=true;

try

E.Quit;

except

CloseExcel:=false;

end;

End;

Pone un rango

Function SetRange (sheet:variant;range:string;

value_:variant):boolean;

begin

SetRange:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range[range]:=value_;

except

SetRange:=false;

end;

End;

Obtiene un rango

Function GetRange (sheet:variant;range:string):variant;

begin

try

GetRange:=E.ActiveWorkbook.Sheets.Item[sheet].Range[range];

except

GetRange:=null;

end;

End;

Pone el ancho de columna

Function SetColumnWidth (sheet:variant;

column:variant;width:real):boolean;

begin

SetColumnWidth:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Columns

 
[column].ColumnWidth:=width;

except

SetColumnWidth:=false;

end;

End;

Pone el Alto de una columna

Function SetRowHeight (sheet:variant;row:variant;

height:real):boolean;

begin

SetRowHeight:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Rows[row].RowHeight:=height;

except

SetRowHeight:=false;

end;

End;

Obtiene el formato de un rango de celdas

Function GetFormatRange (sheet:variant;

range:string):string;

begin

try

GetFormatRange:=E.ActiveWorkbook.Sheets.Item

 
[sheet].Range[range].NumberFormat;

except

GetFormatRange:='';

end;

End;

Pone el formato de un rango de celdas

Function SetFormatRange(sheet:variant;range:string;

format:string):boolean;

begin

SetFormatRange:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range

 
[range].NumberFormat:=format;

except

SetFormatRange:=false;

end;

End;

Pone la alineación horizontal

const

xlHAlignCenter=-4108;

xlHAlignDistributed=-4117;

xlHAlignJustify=-4130;

xlHAlignLeft=-4131;

xlHAlignRight=-4152;

xlHAlignCenterAcrossSelection=7;

xlHAlignFill=5;

xlHAlignGeneral=1;

Function SetHorizontalAlignment
(sheet:variant;range:string;

alignment:integer):boolean;

begin

SetHorizontalAlignment:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range

 
[range].HorizontalAlignment:=alignment;

except

SetHorizontalAlignment:=false;

end;

End;

Pone alineación vertical

const

xlVAlignBottom=-4107;

xlVAlignCenter=-4108;

xlVAlignDistributed=-4117;

xlVAlignJustify=-4130;

xlVAlignTop=-4160;

Function SetVerticalAlignment
(sheet:variant;range:string;

alignment:integer):boolean;

begin

SetVerticalAlignment:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range

 
[range].VerticalAlignment:=alignment;

except

SetVerticalAlignment:=false;

end;

End;

Pone la orientación

Function SetOrientation (sheet:variant;range:string;

orientation:integer):boolean;

begin

SetOrientation:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range

 
[range].Orientation:=orientation;

except

SetOrientation:=false;

end;

End;

Pone el ajuste de texto

Function SetWrapText(sheet:variant;range:string;

WrapText:boolean):boolean;

begin

SetWrapText:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range

 
[range].WrapText:=WrapText;

except

SetWrapText:=false;

end;

End;

Comprime el texto hasta ajustar

Function SetShrinkToFit (sheet:variant;range:string;

ShrinkToFit:boolean):boolean;

begin

SetShrinkToFit:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range

 
[range].ShrinkToFit:=ShrinkToFit;

except

SetShrinkToFit:=false;

end;

End;

Funde celdas

Function SetMergeCells (sheet:variant;range:string;

MergeCells:boolean):boolean;

begin

SetMergeCells:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range

 
[range].MergeCells:=MergeCells;

except

SetMergeCells:=false;

end;

End;

Pone un rango de fonts

Function SetFontRange(sheet:variant;range:string;

font:Tfont):boolean;

begin

SetFontRange:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Font.Name:=font.Name;

if
fsBold in
font.Style then E.ActiveWorkbook.Sheets.Item

  
[sheet].Range[range].Font.Bold:=True

 
else
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Font.Bold:=False;

if
fsItalic in
font.Style then E.ActiveWorkbook.Sheets.Item

  
[sheet].Range[range].Font.Italic:=True

 
else
E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Font.Italic:=False;

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Font.Size:=font.Size;

if
fsStrikeOut in
font.Style

  then
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Font.Strikethrough:=True

 
else
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Font.Strikethrough:=False;

if
fsUnderline in
font.Style then

 
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Font.Underline:=xlUnderlineStyleSingle

 
else
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Font.Underline:=xlUnderlineStyleNone;

E.ActiveWorkbook.Sheets.Item [sheet].Range

  
[range].Font.Color:=font.Color;

except

SetFontRange:=false;

end;

End;

Function SetFontRangeEx(sheet:variant;range:string;

underlinestyle,colorindex:integer;superscript,subscript:boolean):boolean;

begin

SetFontRangeEx:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Font.Superscript:=superscript;

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Font.Subscript:=subscript;

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Font.ColorIndex:=colorindex;

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Font.Underline:=underlinestyle;

except

SetFontRangeEx:=false;

end;

End;

Configura el borde de un rango de celdas

Function SetBorderRange(sheet:variant;range:string;

Edge,LineStyle,Weight,ColorIndex,Color:integer):boolean;

begin

SetBorderRange:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Borders.item

 
[Edge].Weight:=Weight;

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Borders.item

 
[Edge].LineStyle:=LineStyle;

if
ColorIndex>0
then

 
E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Borders.item

 
[Edge].ColorIndex:=ColorIndex

else

 
E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Borders.item

 
[Edge].Color:=color;

except

SetBorderRange:=false;

end;

Pone un patrón a un rango de celdas

Function SetPatternRange(sheet:variant;range:string;

Pattern,ColorIndex,PatternColorIndex,Color,PatternColor:integer):boolean;

begin

SetPatternRange:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].Range[range].Interior.Pattern:=Pattern;

if
ColorIndex>0

  then
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Interior.ColorIndex:=ColorIndex

 
else
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Interior.Color:=color;

if
PatternColorIndex>0

  then
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Interior.PatternColorIndex:=PatternColorIndex

 
else
E.ActiveWorkbook.Sheets.Item[sheet].Range

  
[range].Interior.PatternColor:=PatternColor;

except

SetPatternRange:=false;

end;

End;

Pone una imagen de fondo

Function SetBackgroundPicture(sheet:variant;

file_:string):boolean;

begin

SetBackgroundPicture:=true;

try

E.ActiveWorkbook.Sheets.Item

 
[sheet].SetBackgroundPicture(FileName:=file_);

except

SetBackgroundPicture:=false;

end;

End;

Muestra líneas de división

Function DisplayGridlines(display:boolean):boolean;

begin

DisplayGridlines:=true;

try

E.ActiveWindow.DisplayGridlines:=display;

except

DisplayGridlines:=false;

end;

End;

Muestra el diálogo configuración de la impresora

Function ShowDialogPrinterSetup:boolean;

const
xlDialogPrinterSetup = 9;

begin

ShowDialogPrinterSetup:=true;

try

ShowDialogPrinterSetup:=E.Dialogs.Item

 
[xlDialogPrinterSetup].Show;

except

ShowDialogPrinterSetup:=false;

end;

End;

Muestra el diálogo de la impresora

Function ShowPrintDialog:boolean;

begin

ShowPrintDialog:=true;

try

ShowPrintDialog:=E.Dialogs.Item[xlDialogPrint].Show;

except

ShowPrintDialog:=false;

end;

End;

Muestra la ventana activa

Function WindowView (view:integer):boolean;

begin

WindowView:=true;

try

E.ActiveWindow.View:=view;

except

WindowView:=false;

end;

End;

Pone la orientación de la página

Function PageOrientation (sheet:variant;

orientation:integer):boolean;

begin

PageOrientation:=true;

try

E.ActiveWorkbook.Sheets.Item

 
[sheet].PageSetup.Orientation:=orientation;

except

PageOrientation:=false;

end;

End;

Pone el tamaño del papel

Function PagePaperSize (sheet:variant;

papersize:integer):boolean;

begin

PagePaperSize:=true;

try

E.ActiveWorkbook.Sheets.Item

 
[sheet].PageSetup.PaperSize:=papersize;

except

PagePaperSize:=false;

end;

End;

Pone el área de impresión

Function PagePrintArea (sheet:variant;

printarea:string):boolean;

begin

PagePrintArea:=true;

try

E.ActiveWorkbook.Sheets.Item

 
[sheet].PageSetup.PrintArea:=printarea;

except

PagePrintArea:=false;

end;

End;

Imprime las líneas de división

Function PrintGridlines (sheet:variant;

gridline:boolean):boolean;

begin

PrintGridlines:=true;

try

E.ActiveWorkbook.Sheets.Item

 
[sheet].PageSetup.PrintGridlines:=gridline;

except

PrintGridlines:=false;

end;

End;

Vista previa de la impresión

Function PrintPreview (sheet:variant):boolean;

begin

PrintPreview:=true;

try

E.ActiveWorkbook.Sheets.Item[sheet].PrintPreview;

except

PrintPreview:=false;

end;

End;

Vista previa de la impresión

Function PrintPreviewEx:boolean;

begin

PrintPreviewEx:=true;

try

E.Dialogs.Item[xlDialogPrintPreview].Show;

except

PrintPreviewEx:=false;

end;

End;

Imprime indicando el número de copias

Function Print (sheet:variant;copies:integer):boolean;

begin

Print:=true;

try

E.ActiveWorkbook.Sheets.Item

 
[sheet].PrintOut(Copies:=copies);

except

Print:=false;

end;

End;