Chapter4:SpreadsheetsinCalc
CalculatingWithformulae
Allformulaebeginwithanequalssign.formulaemaycontainnumbersortext,andother
dataisalsopossiblesuchasformatdetails,thatspecifyhowthenumbersaretobeformatted.
Naturallytheformulaewillalsocontainarithmeticoperators,logicoperatorsorfunction
starts.
Whenusingthebasicarithmeticsigns(
+,­,*,/
)informulae,rememberthatusingtheMultiplicationand
DivisionbeforeAdditionandSubtractionrule.Insteadofwriting
=SUM(A1:B1)
it'sbettertowrite
=A1+B1
.
Parenthesesarealsousefulforgrouping.Forexample,theresultoftheformula
=1+2*3
meanssomethingdifferent
than
=(1+2)*3
.
HerearesometypicalCalcformulae:
=A1+ 10
DisplaysthecontentsofcellA1plus10 .
=A1* 16%
Displays16%ofthecontentsofA1.
=A1*A2
DisplaystheresultofthemultiplicationofA1andA2.
=ROUND(A1;1)
RoundsthecontentsincellA1toonedecimalplace.
=EFFECTIVE(5%;12)
Calculatestheeffectiveinterestat5%annuallywith12
payments.
=B8­SUM(B 10:B 14)
CalculatesthesumofthecellsB10toB 14minusthevalueof
B8.
=SUM(B8;SUM(B10:B 14))
CalculatesthesumofcellsB10toB 14andaddsthevalueto
B8.
Itisalsopossibletonestfunctionsinformulae,asshownintheexample.Functionsmayalso
benestedwithinfunctions.Forexample,insteadofsimply=ROUND(A1;1),calculateand
roundasinefunctionusing=ROUND(SIN(A1);2).UsetheFunctionWizardtohelpform
nestedfunctions.
OpenOffice.orgUserGuidefor2.x
217
Pdf link open in new window - insert, remove PDF links in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Free C# example code is offered for users to edit PDF document hyperlink (url), like inserting and deleting
add links to pdf online; add hyperlink to pdf online
Pdf link open in new window - VB.NET PDF url edit library: insert, remove PDF links in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Help to Insert a Hyperlink to Specified PDF Document Page
adding links to pdf; add hyperlinks pdf file
Chapter4:SpreadsheetsinCalc
CalculatingWithDatesandTimes
Inspreadsheetsuserscandomorethanjustdisplaydatesandtimes(bothtakenfromthe
internalclockofthecomputer).Calculationscanalsobemadewithdatesandtimes.For
instance,tofindoutexactlyone'sageinsecondsorhours,followthesesteps:
1. Openanewspreadsheet.
2. EnteradateincellA1.e.g.abirthday,suchas.,1/3/48.
3. EnterthefollowingformulaincellA3:=NOW()­A1
4. PressEnterorclicktheAccepticonontheformulabar( ).Theresultappearsin
dateformat.
5. Right­clickthiscell(i.e.bringupitscontextmenu)sincetheexerciseistoseethe
differencebetweentwodatesasanumberofdays,theformatofcellA3shouldbesetasa
number.
6. SelectFormatCells....
7. TheCellAttributesdialogueappears.
1. OntheNumberstab,theNumbercategorywillappearhighlighted.Theformatisset
toGeneralwhichcauses,amongotherthings,theresultofcalculationscontaining
dateentriestoalsobedisplayedasadate.
2. Setthenumberformatto­1,234,forexample.
3. PressOKtoclosethedialogue.
8. CellA3willnowcontainthenumberofdaysbetweentoday'sdateandthespecifieddate.
9. Experimentwithsomeadditionalformulae:
1. inA4enter=A3*24tocalculatethehours.
2. inA5enter=A4*60fortheminutes.
3. inA6enter=A5*60forseconds.
OpenOffice.orgUserGuidefor2.x
218
C# PDF: PDF Document Viewer & Reader SDK for Windows Forms
Please note that, there will be a pop-up window "cannot open your file" if your loaded Please click the following link to see more C# PDF imaging project
add url pdf; add hyperlink to pdf
VB.NET Image: VB Code to Download and Save Image from Web URL
to download image from website link more easily. reImage, "c:/reimage.png", New PNGEncoder()) End powerful & profession imaging controls, PDF document, image
add hyperlink to pdf in; add hyperlink pdf file
Chapter4:SpreadsheetsinCalc
4. PresstheEnterkeyaftereachformula.
Thetimesincethebirthdaywillbecalculatedanddisplayedinthevariousunits.Thevalues
arecalculatedasoftheexactmomentwhenthelastformulawasenteredandconfirmedby
pressingtheEnterkey.Thisvalueisnotautomaticallyupdated,althoughNOWcontinuously
changes,ofcourse.IntheToolsmenu,themenuitemCellContents>AutoCalculateis
normallyactive;however,automaticcalculationdoesnotapplytothefunctionNOW.Consider
that,ifitwere,thecomputerwoulduseallitsresourcesupdatingthesheet.
Whenspreadsheetscontainaverylargeamountofdataorverycomplicatedformulaethat
maybemodifiedbeforeviewingthecalculationresults,itmaybeprudenttocancelor
disabletheautomaticcalculationfunction.Calculationtimenaturallybecomeslongerasthe
complexityandsizeofaspreadsheetincreases.
InsertingandEditingNotes
AnotemaybeassignedtoeachcellbychoosingInsert>Note.Allnotesareindicatedbya
smallredsquare,thenoteindicator,inthecell.Notesmadevisiblewheneverthemouse
pointerisoverthecell,providedHelp>TipsorExtendedTipsisactive.
Foraselectedcell,chooseShowNotefromthecontextmenuofthecell.Doingsokeeps
thenotevisibleuntiltheShowNotecommandisdeactivatedfromthecontextmenu.
Toeditapermanentlyvisiblenote,justclickinit.Whentheentiretextofthenoteis
deleted,thenthenoteitselfisdeleted.
AnotherwaytodeleteanoteisbychoosingEdit>DeleteContents,orcallingthesame
dialoguewiththeDeletekey.
SelectTools>Options>OpenOffice.orgCalc­Viewtoshoworhidethenoteindicator
bycheckingoruncheckingtheNoteindicatorbox.
OpenOffice.orgUserGuidefor2.x
219
C# PDF Convert to Jpeg SDK: Convert PDF to JPEG images in C#.net
Open source codes can be added to C# class. String inputFilePath = Program.RootPath + "\\" 1.pdf"; PDFDocument doc = new PDFDocument(inputFilePath
clickable links in pdf; pdf edit hyperlink
C# PDF Convert to HTML SDK: Convert PDF to html files in C#.net
is trying to display a PDF document file inside a browser window. PDFDocument pdf = new PDFDocument(@"C:\input.pdf"); pdf.ConvertToVectorImages(ContextType.HTML
pdf hyperlink; add links to pdf
Chapter4:SpreadsheetsinCalc
HandlingMultipleSheets
Spreadsheetarenotlimitedtojustonesheet.Infact,severalsheetslaidontopofeachother.
Eachsheethasitsownuniquenamedisplayedonthesheettabatthebottomofthewindow.
Additionalsheetscanbeappendedtoadocumentorinsertedinfrontofthecurrentsheet.
Activatethecontextmenuofasheettabandchoosethecorrespondingcommand.Choose
Renameinthecontextmenutomodifythenameofasheet.
DisplayingMultipleSheets
Usethesenavigationbuttonstodisplaythesheettabsofalltheavailable
sheets.Clickthebuttononthefarrightofthisgrouptomovetothethelast
sheettabtoseeitsname.Todisplaythesheetitself,clickonthename.
OpenOffice.orgUserGuidefor2.x
220
VB.NET Word: VB.NET Code to Draw and Write Text and Graphics on
fileName, New WordDecoder()) 'use WordDecoder open a wordfile Dim Word document function, please link to Word & profession imaging controls, PDF document, tiff
add links to pdf file; add a link to a pdf in acrobat
C# TIFF: C#.NET TIFF Document Viewer, View & Display TIFF Using C#
TIFF Mobile Viewer in most mobile browsers; Open, load & Free to convert TIFF document to PDF document for management Please link to get more detailed tutorials
add hyperlink to pdf in preview; change link in pdf
Chapter4:SpreadsheetsinCalc
Whenthereisinsufficientspacetodisplaythesheettabsonthelowerwindowborder,
increaseitbymovingtheseparatorbarbetweenthetabbarandthehorizontalscrollingbar
withthemousebutton.Keepthemousebuttonpressedanddragtotheright.Rememberthis
sharestheavailablespacebetweenthesheettabsandhorizontalscrollbar.
WorkingWithMultipleSheets
Eachsheetofaspreadsheetiscompletelyindependentoftheothersheetsinaspreadsheet
document.Howeverthesamedatacanbeincorporatedintoseveralsheets.Forexample,the
samedatashouldbeinsertedatthesamelocationinthefirstthreesheets.Todoso,selectall
threesheetstogetherandenterthedatainonlyoneofthesheets.
Selectingseveralsheetstogether,issimplyamatterofclickingthesheettabsofthesheetsin
questionwhilepressingtheCtrlkey.Allselectedsheetswillnowhavewhitesheettabs,
insteadofthegraysheettabsoftheunselectedsheets.Toundotheselectionofasheet,click
itssheettabagainwhilstpressingtheCtrlkey.Clickingthesheettabofthecurrentsheet
whilepressingtheShiftkey,ensuresthatonlythisoneisselected.
Calcincludesthenameofthesheetinthereferencewhenassigningsheetreferences.Thus,
insteadofreferringto$A$1,Calcrefersto$Sheet 1.$A$1whichmakesmulti­sheet
referencingeasyandstraightforwardasshownintheexamplesbelow:.
ToaddressthefirstcellfrombothSheet1andSheet2inasinglereference,for
example,summarize,the3Dformulawillread=SUM(Sheet 1.A1:Sheet2.A1).The
sumfunctionhasoneaddendhere,namely,therangefromSheet1.A1toSheet
2.A1.Inthisrangetherearetwocells(aslongasnomorecellsareincludedbetween
Sheet1andSheet2).Thesimpleformula(nota3Dformula)wouldonlylisttwo
addends:=SUM(Sheet 1.A1;Sheet2.A1).
ToincludeanysubsequentlyinsertedsheetsfoundbetweenSheet1andSheet2,the
formulawouldthenbe=SUM(Sheet 1.A1:Sheet2.B2).
Thefulladdressofacellalsocontainsthecompletepathandfilenameofthespreadsheet
document.So,initsfullform,thereferencetocellA1inSheet1ofthedocument
name.sxccouldbe='file:///home/username/name.sxc'#$sheet 1.A1
under*
UNIX
where/homeisamountedfilesystemandusernamespecifiesthedirectory
wherethefileisstored.UnderWindows®,thespecificationissimilarandcouldbe
='file:///c:/name.sxc'#$sheet 1.A1wherethedriveis“C:”.
Note:thesinglequotessurroundingthefilename,andthe#characterthatdescribesthelocationwithinthefile,in
accordancewithURLconvention.
PrintingSpreadsheets
C
lickingthePrintFileDirectlyiconintheStandardtoolbarsendsallthesheetsinthedocumenttothe
printer.However,ifthere'saprintrangeselected,thenonlyselectionisprinted.Tosettheprintrange,selectthe
cellstobeprinted,thenusetheFormat>PrintRanges>Definecommand.Thereisfurtherinformationonthis
topicintheOpenOffice.orgHelp.
OpenOffice.orgUserGuidefor2.x
221
Chapter4:SpreadsheetsinCalc
Toprinttheonlycurrentsheet,gotomenuFile>Print....InthePrintdialogue,selectthe
SelectionoptionandclickOK.If,however,thereisselectedacertainrangeofcells,only
thosecellsareprintedandinthecolumnwidthasshowninthesheet.
Ifvarioussheetsaretoprintsimultaneously,forexample,Sheet1andSheet2,select
thembeforehand(holddowntheCtrlkeyandclickthesheettabs).Thewhitetabsarethe
selectedones.Next,gotothePrintdialogue,enabletheSelectionoptionandonlythe
selectedsheetswillbeprinted.Afterhavingprintedthedesiredsheets,remembertoclickthe
sheetthatisbeingworkedonwhileholdingdowntheShiftkeysothatonlythatsheetis
selected.Failuretodothiswillresultinallmodificationsbeingappliedonallsheets.
OpenOffice.orgUserGuidefor2.x
222
Chapter4:SpreadsheetsinCalc
FormattingaSpreadsheet
Aspreadsheetishardlyeverusedwithoutformatting.Cellcontents,suchastextsand
numbers,aregivencertainformats,andthecellsthemselvesareformattedwithdifferent
colours,bordersandotherattributes.
Eithercreatethenumbersformatoruseoneofthemanypredefinedformats.Forcells,awide
selectionofcellStylesisprovidedandpersonalcellStylescanbedefinedinthesamewayas
onedoestextStyles.
Certainvaluesinspreadsheetsareoftengivenparticularemphasis,e.g.inatableofturnovers
toshowallthevaluesabovetheaverageingreenandallthosebelowtheaverageinred.This
ispossibleusingtheConditionalFormattingforspreadsheetdocuments(coveredinalater
chapter.)
FormatingNumbers
Enteranumberintothesheet,forexample,1234.5678.Thisnumberwillbedisplayedin
thedefaultnumberformat,withtwodecimalplaces.Onewillsee1234. 57whenthethe
entryisconfirmed.Onlythedisplayinthedocumentwillberoundedoff;internally,the
numberretainsallfourdecimalplacesafterthedecimalpoint.
1. SetthecursoratthenumberandchooseFormat>CellstostarttheCellAttributes
dialogue.
2. OntheNumberstabthereisaselectionofpredefinednumberformats.Apreviewbox,n
thebottomrightofthedialogue,showshowthecurrentnumberwillappearwitha
particularformat.
3. Thisdialoguealsoallowsdefiningotherattributes(inadditiontonumberformats)which
applytotheselectedcellsorcellcontents.Forexample,font,size,andcolourcanbe
definedontheFonttabpage.
Modifyingthenumberofthedecimalplacesdisplayedinacellissometimes
required.TheeasiestmethodistousetheNumberFormat:AddDecimalPlaceor
NumberFormat:DeleteDecimalPlaceiconsontheobjectbar.
Dates
1. Likewise,fromthelistofoptions,thedateandtimecanbeformattedasdesired.
Theyearinthedatedetailsisoftenstatedastwodigits.Internallytheyearismanagedby
OpenOffice.orgasfourdigits,sothatinthecalculationofdifferencefrom1/1/99to
1/1/01theresultwillcorrectlybetwoyears.
Tools>Options>OpenOffice.org>Generaldefinesuptowhichyearatwo­digityear
“xx”shouldbedisplayedas“20xx”.
Thismeansthatifadateof1/1/30orhigherisentered,itwillbetreatedinternallyas
1/1/1930orhigher.Allloweryearsapplytothenextcentury.So,forexample,1/1/20is
convertedinto1/1/2020.
OpenOffice.orgUserGuidefor2.x
223
Chapter4:SpreadsheetsinCalc
FormattingCellsandSheets
ThedistinctionbetweendirectandStyleformattingholdstrueforcellsaswellasfortext
documents,e.g.Achoicebetweenapplyingaparticularfontsizedirectlyasdirectformatting
toacellordefiningaStyletoapplythedesiredfontsize.Stylesmakeparticularsensefor
documentsthatareusedextensivelyoraretobetemplates.Itdoesnotmakesensetouse
Stylesfordocumentsthatareonlyprintedanddiscarded.
UsingAutoFormatforTables
AquickwaytoformatatableoracellrangeisofferedbytheFormat­AutoFormat
function.Inaspreadsheet,firstselectthecellstowhichtoapplyAutoFormatting,including
thecolumnandrowheadersandanysumrowsorcolumns.Atleast3x3cellsmustbe
highlightedbeforethismenuitemisactivated.ThenstarttheAutoFormatdialogue.
ThepreviewshowsanexampleofhoweachselectedformatintheFormatpanelwilllook.
ClickOKtoapplytheformattotheselectedrangeinthespreadsheet.
Note:Ifthereisnochangeincolourofthecellcontents,thenunderTools>Options>OpenOffice.orgCalc>
View>ShoworintheViewmenu,Highlightvaluesmaybechecked.Inthiscase,thespecifiedcolourswillbe
used.
Auser­formatcanalsobesetasanAutoFormat:
1. Formataspreadsheettoasdesired.
2. Selectthewholesheet,e.g.byclickingtheemptybuttoninthetopleftcorner(abovethe
firstrowheader).
3. CalluptheAutoFormatdialogueandclickAdd....Adialoguewillappearinwhichto
givethenewformataname.
Theformatdefinedinthiswaycanthenbeusedinthefutureonotherspreadsheets.By
choosinganappropriatebackgroundcolourandapatternforthecellsinthesheet,an
eye­appealingspreadsheetiscreated.
OpenOffice.orgUserGuidefor2.x
224
Chapter4:SpreadsheetsinCalc
UsetheMorebuttontodisplayadditionaloptionsinthedialogue.Intheadditionalsection
thatisthendisplayed,selectwhichpropertiesofthechosenformataretobeexcludedfrom
theautomaticformatting.Forexample,removingthecheckmarkinfrontofFont,thefont
willnotbetakenintoaccountbytheAutoFormat.
LayoutofSpreadsheets
Sheetsdonothavetohavethelookofanyofthepredefinedformats.Therearemanywaysto
applyadifferentlayoutforspreadsheets,aswellastablesintextdocuments.
DesigningSpreadsheetsClearly
Calcassistsindesigningasheetinavarietyofways.Forexample,herearethreedifferent
viewsofthesamesheet,varyingonlyinlayout:
Thisisasamplesheetwithoutanyspecialformatting,asitappearsbydefault.
TheabovegraphicshowsthesheetformattedusingoneoftheAutoFormats,itwilllook
appealingwithoutrequiringanyfurtherediting.
OpenOffice.orgUserGuidefor2.x
225
Chapter4:SpreadsheetsinCalc
Inthisexample,thesheethasbeenformattedwithcertaincellattributesintheFormat>
Cellsdialogue.Inadditiontheviewofthegridlinesandsheetheadershasbeendeactivated
inTools>Options>OpenOffice.orgCalc>Viewandagraphicsfileloadedasbackground
inFormat>Page>Background.
Note:AnimageloadedviaFormat>Page>Background,isonlyvisibleinprintorviaFile>PageView.
Forabackgroundimagetoappearonscreenaswell,insertthegraphicimagebychoosingInsert>Picture>
FromFileandputtheimagebehindthecellsbywayofthecontextmenucommandArrangement>Tothe
Background.
Tolaterselectthebackgroundimage,usetheNavigator.
FormattingTextinaSpreadsheet
1. Selectthetextforformatting.
2. Choosethedesiredtextattributesfromthespreadsheetobjectbar.
Note:Format>Cellsisanotherchoice.TheCellAttributesdialogueallowschoosingvarioustextattributesonthe
Fonttabpage.
FormattingNumbersinaSpreadsheet
1. Selectthecellscontainingthenumberstobeformatted.
2. Toformatnumbersinthedefaultcurrencyformatoraspercentages,usetheiconsonthe
spreadsheetobjectbar.Forotherformats,chooseFormat>Cells....Choosefromthe
presetformatsordefineapersonalformatontheNumberstabpage.
FormattingBordersandBackgroundsforCellsandPages
Basically,aformatcanbeassignedtoanygroupofcellsbyfirstselectingthecells(for
multipleselection,holddowntheCtrlkeywhenclicking),andthenactivatingtheCell
AttributesdialogueviaFormat>Cell.Inthisdialogue,selectattributessuchasshadows
andbackgrounds.
Toapplyformattingattributestoanentiresheet,chooseFormat>Page.Thispermits
definingheadersandfooters,forexample,toappearoneachprintedpage.
OpenOffice.orgUserGuidefor2.x
226
Documents you may be interested
Documents you may be interested