Sakthi's CyberSpace: January 2009

Wednesday, January 28, 2009

Creating a colour gradient background without images - Javascript FAQ - Tek-Tips

Creating a colour gradient background without images - Javascript FAQ - Tek-Tips:
Graphics Display

Creating a colour gradient background without images

Here's some code to dynamically create a gradient background without using images.

The width / height of each colour band, as well as the total number of colour bands can be changed easily.

CODE
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="content-language" content="en" />
<title>Gradient Background</title>

<style type="text/css">
body {
padding: 0px;
margin: 20px;
background-color: #00CCFF;
}

#fadeBandsV {
position: absolute;
left: 0px;
top: 0px;
width: 100%;
height: 80px;
z-index: 1;
}

#fadeBandsH {
position: absolute;
left: 0px;
top: 0px;
width: 100%;
z-index: 1;
}

#fadeBandsV div {
overflow: hidden;
position: absolute;
width: 100%;
}

#fadeBandsH div {
overflow: hidden;
position: absolute;
height: 200px;
}

#pageContent {
position: relative;
z-index: 2;
}
</style>

<script type="text/javascript">

</script>
</head>

<body onload="drawGradient();">
<div id="fadeBandsV"></div>
<div id="fadeBandsH"></div>
<div id="pageContent">
<h1>A test heading</h1>
<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Cras lorem. Proin laoreet fringilla sem. Pellentesque malesuada, urna tristique ornare feugiat, turpis wisi ultrices nisl, ac vulputate dolor justo non sapien. Sed fermentum velit. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Etiam at nulla. Maecenas tortor nulla, adipiscing eget, auctor sed, cursus id, quam. Suspendisse at sem nonummy orci lobortis ultrices. Proin odio est, dictum id, dictum nec, ullamcorper sit amet, sem. Proin ullamcorper lacus vitae risus. Nulla feugiat posuere erat. In nec est ac eros lacinia congue. Suspendisse potenti. In a ipsum rhoncus ipsum volutpat consectetuer.</p>
<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Cras lorem. Proin laoreet fringilla sem. Pellentesque malesuada, urna tristique ornare feugiat, turpis wisi ultrices nisl, ac vulputate dolor justo non sapien. Sed fermentum velit. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Etiam at nulla. Maecenas tortor nulla, adipiscing eget, auctor sed, cursus id, quam. Suspendisse at sem nonummy orci lobortis ultrices. Proin odio est, dictum id, dictum nec, ullamcorper sit amet, sem. Proin ullamcorper lacus vitae risus. Nulla feugiat posuere erat. In nec est ac eros lacinia congue. Suspendisse potenti. In a ipsum rhoncus ipsum volutpat consectetuer.</p>
</div>
</body>
</html>

You can use "createGradientV" to create a vertical gradient (North to South), or "createGradientH" to create a horizontal gradient (West to East).

Each of the functions takes one or more sets of 4 parameters, documented in the code above.

To add more colour bands, simply add more sets of parameters to the "createGradient" method calls. To have less colour bands, remove any extra "createGradient" method calls.

This code has been tested working on IE/Win 6, NN/Win 7, Opera/Win 7, FF/Win, FF/Mac, and Safari/Mac.

It does NOT work on IE/Mac (tested on version 5.22).

Creating a colour gradient background without images - Javascript FAQ - Tek-Tips

Thursday, January 22, 2009

VBA Tips & Tricks: Embed Existing Word File to Spreadsheet using Excel VBA

VBA Tips & Tricks: Embed Existing Word File to Spreadsheet using Excel VBA: "Insert Existing File (Word Document) to Spreadsheet using VBA

Sub Insert_File_To_sheet()

Dim oWS As Worksheet ' Worksheet Object

Dim oOLEWd As OLEObject ' OLE Word Object

Dim oWD As Document ' Word Document Object (Use Microsoft Word Reference)

Set oWS = ActiveSheet

' embed Word Document

Set oOLEWd = oWS.OLEObjects.Add(Filename:='C:\VBADUD\Chapter 1.doc')

oOLEWd.Name = 'EmbeddedWordDoc'

oOLEWd.Width = 400

oOLEWd.Height = 400

oOLEWd.Top = 30

' Assign the OLE Object to Word Object

Set oWD = oOLEWd.Object

oWD.Paragraphs.Add

oWD.Paragraphs(oWD.Paragraphs.Count).Range.InsertAfter 'This is a sample embedded word document'

oOLEWd.Activate

End Sub

If you want to embed other document like PDF etc, you can do the same by

ActiveSheet.OLEObjects.Add Filename:= 'C:\VBADUD\Sample_CH03.pdf', Link:=False, DisplayAsIcon:= False"

VBA Tips & Tricks: Creating a Command Button on Sheet using Excel VBA

VBA Tips & Tricks: Creating a Command Button on Sheet using Excel VBA: "Creating a Command Button on Sheet using Excel VBA
Adding an OLE Object (Command Button) to a Worksheet using Excel VBA

Sub Create_Command_Button_2007()

'

' Creates a Command button and Positions it

' Written by Shasur for http://vbadud.blogspot.com

Dim oOLE As OLEObject

' Add a Command Button

oOLE = ActiveSheet.OLEObjects.Add(ClassType:='Forms.CommandButton.1', Left:=220, Top:=40, Height:=30, Width:=120)

oOLE.Interior.Color = vbRed

' Move and Size with cells

oOLE.Placement = XlPlacement.xlMoveAndSize

oOLE.Object.Caption = 'Click Me...'

End Sub

Each OLEObject object represents an ActiveX control or a linked or embedded OLE object.

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other is not automatically changed to match."

Excel Using VBA to write VBA code (create multiple buttons)

Excel Using VBA to write VBA code (create multiple buttons): "Sub AddComm_button()

Dim code As String
Dim j As Integer

Sheets('Sheet1').Select

For j = 1 To 2
'add button
ActiveSheet.OLEObjects.Add ClassType:='Forms.CommandButton.1', _
Left:=126 * j, Top:=96, Width:=126.75, Height:=25.5

'Code for button
code = ''
code = 'Sub CommandButton' & Trim(Str(j)) & '_Click()' & vbCrLf
code = code & ' Sheets(''Sheet' & Trim(Str(j)) & ''').Activate' & vbCrLf
code = code & 'End Sub'

'Write code for button
With ThisWorkbook.VBProject.VBComponents('Sheet1').CodeModule
.InsertLines .CountOfLines + 1, code
End With
Next j
End Sub"

Create a Word Document with VBA and VBScript: How to Automate Microsoft Word using Visual Basic

Create a Word Document with VBA and VBScript: How to Automate Microsoft Word using Visual Basic: "Microsoft Word has been around (in one form or another) since 1981 and it (with Microsoft Excel) forms the core of Microsoft Office. There are alternatives, such as OpenOffice.org (which has the advantage of being a cross-platform application), but there is no real competition - and one of the key uses of Microsoft Word is the writing of reports.

How may reports are written in Microsoft Word every year? Millions? And how many man hours are used in the production of those reports? Billions? One simple way of reducing some of that time and effort is to start automating reports - and a programmer can easily do this by using VBA (Visual Basic for Applications) with VBScript.

The starting point for this automation is a file saved with a .vbs extension, some variable declarations and a little code.
Declaring Variables for the Script

As always enforce variable declaration:
option explicit

Then declare the variables that are going to be used by the script:
dim word 'the Word application
dim doc 'the Word document
dim selection 'text selection
dim fs 'the File system
dim work_dir 'the directory for storing files
dim folder, folders 'used when creating the new file
dim i, j 'indexes for arrays

The variables in VBScript are not defined with a specific data type - by default they are all of the data type variant, but once the variables have been declared they can be populated (if the values are known at this point):
work_dir = 'C:\vba\word\'

Once any required variables have been loaded with data the programmer can turn to any objects that they may need access to.
Creating Objects

The programmer gains access to any OLE (Object Linking and Embedding) compliant applications though the createobject method. In this case access a Word application and the file system are required:
set word = createobject('word.application')
set fs = createobject('Scripting.FileSystemObject')

The next stage is to start making use of these objects in the vbs script.
Creating a New Word Document

Once the Word application object has been initiated then a new document can be created and written to - and the process is very simple:

* use the add method to create a new document
* access the document selection (the text)
* use the typetext method to write to the document
* use the typeparagraph method to add a paragraph break

And so the code will be something like:
set doc = word.documents.add
set selection = word.selection
selection.typetext 'An Automated Word Report'
selection.typeparagraph

A programmer can create a complete report just by adding more paragraphs -perhaps from data stored in a database. However, where ever the information comes from, the next stage is to save the document to a folder somewhere on the network.
Creating a New Directory

VBScript has no in-built method for creating a folder (or even checking that a folder exists); however, this is where the programmer can make use of the FileSystemObject (FSO) initiated at the start of the script. First the FSO is used to check if the directory exists:
if not fs.folderexists(work_dir) then

If the folder does not exist then it must be created - however this cannot be done in a single stroke; for example, in the case of C:\vba\word\

* C: must be present before the vba folder can exist
* the vba folder must exist before the word folder can be created

So the directory tree must be created folder by folder - progressing from the top layer to the bottom creating folders as they are required:
folders = split (work_dir, '\')
for i = 0 to ubound(folders) - 1
folder = ''
for j = 0 to i
folder = folder & folders(j) & '\'
next
if not fs.folderexists(folder) then
fs.createfolder folder
end if
next
end if

Once the folders are in place the document may be saved.
Saving a Word Document

Saving a Word document is very easy - the programmer only needs the saveas method:
doc.saveas(work_dir & 'testdoc.doc')

And with that the new Word document has been created - it's now just a matter of a little housekeeping.
Ending the Script

If all of the code is saved to a file (for example create_report.vbs) then a user can run it by double-clicking on the file icon in Windows Explorer; however, they will see not output - and that's because VBA keeps the file invisible by default. The developer, therefore, has two choices at this point - they can:

* make the document visible to the user
* close the Word application

To make the file visible the programmer would need to add the code:
word.visible = True

Or to close Microsoft Word:
word.Quit

And the programmer will also want to fully unload the FSO and the Word application (to ensure no memory leakage occurs):
set fs = nothing
set word = nothing

At this point, regardless of the script ending, the user will have brand new Word document in the specified folder - without having to type in a single word themselves."

Control Word from Excel using VBA in Microsoft Excel

Control Word from Excel using VBA in Microsoft Excel: "The two example macros below demonstrates how you can send information to Word
(e.g. creating a new document) and how you can retrieve information from Word
(e.g. reading information from a document).

Note! Read and edit the example code before you try to execute it in your own project!

Sub CreateNewWordDoc()
' to test this code, paste it into an Excel module
' add a reference to the Word-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer
Set wrdApp = CreateObject('Word.Application')
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add ' create a new document
' or
'Set wrdDoc = wrdApp.Documents.Open('C:\Foldername\Filename.doc')
' open an existing document
' example word operations
With wrdDoc
For i = 1 To 100
.Content.InsertAfter 'Here is a example test line #' & i
.Content.InsertParagraphAfter
Next i
If Dir('C:\Foldername\MyNewWordDoc.doc') <> '' Then
Kill 'C:\Foldername\MyNewWordDoc.doc'
End If
.SaveAs ('C:\Foldername\MyNewWordDoc.doc')
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

Sub OpenAndReadWordDoc()
' assumes that the previous procedure has been executed
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim tString As String, tRange As Word.Range
Dim p As Long, r As Long
Workbooks.Add ' create a new workbook
With Range('A1')
.Formula = 'Word Document Contents:'
.Font.Bold = True
.Font.Size = 14
.Offset(1, 0).Select
End With
r = 3 ' startrow for the copied text from the Word document
Set wrdApp = CreateObject('Word.Application')
'wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open('C:\Foldername\MyNewWordDoc.doc')
' example word operations
With wrdDoc
For p = 1 To .Paragraphs.Count
Set tRange = .Range(Start:=.Paragraphs(p).Range.Start, _
End:=.Paragraphs(p).Range.End)
tString = tRange.Text
tString = Left(tString, Len(tString) - 1)
' exclude the paragraph-mark
' check if the text has the content you want
If InStr(1, tString, '1') > 0 Then
' fill into active worksheet
ActiveSheet.Range('A' & r).Formula = tString
r = r + 1
End If
Next p
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
ActiveWorkbook.Saved = True
End Sub"

Tuesday, January 20, 2009

Dates Table - OS Developer Portal

Dates Table

I find that it can be advantageous to use a Dates table, especially when doing a lot of date calculations. Learn what a Dates table is and how to create one, and then try it out for yourself.

What is a Dates table?

A Dates table stores a range of dates. Dates tables are very common in a DateWarehouse as a dimension table. You can also use Dates tables in OLTP databases for lookups. When programmers use Dates tables, they don’t have to worry about using or designing functions for handling or formatting dates in the database. It is a precompilation of a wide range of date values and their associated month, quarter, year, etc.

Creating a Dates table

It’s simple to create a Dates table — it only takes a little TSQL programming. The script below creates the DateLookup table, which I will use throughout the rest of the example.

CREATE TABLE DateLookup
(
DateKey INT PRIMARY KEY,
DateFull DATETIME,
CharacterDate VARCHAR(10),
FullYear CHAR(4),
QuarterNumber TINYINT,
WeekNumber TINYINT,
WeekDayName VARCHAR(10),
MonthDay TINYINT,
MonthName VARCHAR(12),
YearDay SMALLINT,
DateDefinition VARCHAR(30),
WeekDay TINYINT,
MonthNumber TINYINT
)

As you can see from the field names, the table contains detailed information regarding parts of a date, such as the name of the month, name of the day of the weekend, the quarter number, etc. It’s very useful to have this information stored in a table for date searches based on certain months, quarters, and similar information.

The script below populates my DateLookup table with date information from the year 1900 through the end of 2100. I enter this large range of dates because I am not sure what type of dates I am going to handle in my tables, so I like to have a wide range available. This range likely won’t cover erroneous dates in my tables, but it should do a pretty good job covering a large percentage of them.

DECLARE @Date DATETIME
SET @Date = '1/1/1900'

WHILE @Date < '1/1/2100'
BEGIN
INSERT INTO DateLookup
(
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MonthName, YearDay,
DateDefinition,
CharacterDate,
WeekDay,
MonthNumber
)
SELECT
CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
DATENAME(mm, @Date) + ' ' + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',
' + CAST(DATEPART(yy, @Date) AS CHAR(4)),
CONVERT(VARCHAR(10), @Date, 101),
DATEPART(dw, @Date),
DATEPART(mm, @Date)

SET @Date = DATEADD(dd, 1, @Date)
END

Using the DateLookup table

Once I load data into my DateLookup table, I can run queries against it. For example, the following query lists the number of Wednesdays in the year 2003:

SELECT WeekDayName, DayCount = COUNT(*)
FROM DateLookup
WHERE FullYear = 2003 AND
WeekDayName = 'Wednesday'
GROUP BY WeekDayName

There were 52 Wednesdays in 2003.

The real power of using a Dates table comes when you use the table in conjunction with other tables.

Python ADO Date Time database fields - Application Development Forum

Python Date Time database fields - Application Development Forum: "def time_format_12(day_fraction):
assert 0.0 <= day_fraction < 1.0
seconds = int(day_fraction * 60 * 60 * 24)
minutes, second = divmod(seconds, 60)
hour, minute = divmod(minutes, 60)
if hour >= 12:
tag = 'PM'
else:
tag = 'AM'
hour12 = (hour - 1) % 12 + 1
return '%02d:%02d:%02d %s' % (hour12, minute, second, tag)

if __name__ == '__main__':
import sys
args = sys.argv[1:]
if args:
tests = map(float, args)
else:
tests = (
[0.0, 0.5, 0.9999999]
+ [(h + 0.99) / 24.0 for h in (0, 1, 11, 12, 13, 23)]
)
for test in tests:
print '%8.6f %s' % (test, time_format_12(test))

C:\junk>goldtech.py
0.000000 12:00:00 AM
0.500000 12:00:00 PM
1.000000 11:59:59 PM
0.041250 12:59:24 AM
0.082917 01:59:24 AM
0.499583 11:59:23 AM
0.541250 12:59:24 PM
0.582917 01:59:24 PM
0.999583 11:59:23 PM

C:\junk>goldtech.py 0.1 0.01 0.001
0.100000 02:24:00 AM
0.010000 12:14:24 AM
0.001000 12:01:26 AM"

Friday, January 9, 2009

JavaScript Open Excel

JavaScript Open Excel: "Using Javascript to Open Excel
There are many reasons a web developer might have to think of the Javascript Open Excel function. In any case we'll try and make it as easy as possible to opening excel using js. Here's the Javascript Open Excel Code:
view plaincopy to clipboardprint?

1. var newwindow=window.open('url to.xls','window2','');

var newwindow=window.open('url to.xls','window2','');

another option would be to use this Javascript OpenExcel Function in the header of your page:
view plaincopy to clipboardprint?

1. <script language='javascript'>
2. function openExcel(strFileName) {
3. var yourAddress = 'http://www.mySite.com';
4. openExcelDocPath(yourAddress + strFileName, false);
5. }
6. function openExcelDocPath(strLocation, boolReadOnly) {
7. var objExcel;
8. objExcel = new ActiveXObject('Excel.Application');
9. objExcel.Visible = true;
10. objExcel.Workbooks.Open(strLocation, false, boolReadOnly);
11. }
12. </script>

And place this in the body of your page.
view plaincopy to clipboardprint?

1. <p><a href='#' onclick='openExcel('test.xls');'>Open Excel</a></p>

Open Excel

Javascript Open Excel is not really that complicated. Keep in mind that viewers will not be able to save or write to your webserver unless it is local."

Monday, January 5, 2009

Least common multiple for 3 or more numbers - Stack Overflow

Least common multiple for 3 or more numbers - Stack Overflow: "def gcd(a, b):
'''Return greatest common divisor using Euclid's Algorithm.'''
while b:
a, b = b, a % b
return a

def lcm(a, b):
'''Return lowest common multiple.'''
return a * b // gcd(a, b)

def lcmm(*args):
'''Return lcm of args.'''
return reduce(lcm, args)

Usage:

>>> lcmm(100, 23, 98)
112700
>>> lcmm(*range(1, 20))
232792560

reduce() works something like that:

def reduce(callable, iterable, ini=None):
iterable = iter(iterable)

ret = iterable.next() if ini is None else ini

for item in iterable:
ret = callable(ret, item)

return ret"

Sunday, January 4, 2009

Interpreting Excel Currency Values - Stack Overflow

Interpreting Excel Currency Values - Stack Overflow: "
Problem Scenario:

For example, the cell appears as $548,982, but in python the value is returned as (1, 1194857614).
How can I get the numerical amount from excel or how can I convert this tuple value into the numerical value?

import struct
try: import decimal
except ImportError:
divisor= 10000.0
else:
divisor= decimal.Decimal(10000)

def xl_money(i1, i2):
byte8= struct.unpack('>q', struct.pack('>ii', i1, i2))[0]
return byte8 / divisor

>>> xl_money(1, 1194857614)
Decimal('548982.491')

Money in Microsoft COM is an 8-byte integer; it's fixed point, with 4 decimal places (i.e. 1 is represented by 10000). What my function does, is take the tuple of 4-byte integers, make an 8-byte integer using struct to avoid any issues of sign, and then dividing by the constant 10000. The function uses decimal.Decimal if available, otherwise it uses float.

UPDATE (based on comment): So far, it's only COM Currency values being returned as a two-integer tuple, so you might want to check for that, but there are no guarantees that this will always be successful. However, depending on the library you use and its version, it's quite possible that later on, after some upgrade, you will be receiving decimal.Decimals and not two-integer tuples anymore."

[python-win32] Problem about formatting a cell in Excel

[python-win32] Problem about formatting a cell in Excel: "FormatDict = { 'currency' : '$#,##0.00;[Red]-$#,##0.00', 'date' : 'mm/dd/yy', 'percent' : '0.0%', 'text' : '@' } def formatrange(format, row1, col1, row2, col2): range = sheet.Range(sheet.Cells(row1, col1), sheet.Cells(row2, col2) ) range.NumberFormat = FormatDict['text'] # format first four columns / 20 rows as text formatrange('text', 1, 1, 20, 4) # format next column as currency formatrange('currency', 1, 5, 20, 5) Here, FormatDict is a global variable. When I need to format a range of cells, I simply get the a reference to the range using normal methods, and set the NumberFormat property of that range to one of the values held in the FormatDict. The dict just lets me use a human-readable description of the format instead of a potentially-obscure series of punctuation characters."

Thursday, January 1, 2009

Mathematical Operators - Using Mathematical Operators in Excel Spreadsheets (BEDMAS)

Mathematical Operators - Using Mathematical Operators in Excel Spreadsheets: "Mathematical Operators

Creating formulas in Microsoft Excel is not difficult. Just combine the cell references of your data with the correct mathematical operator.

The mathematical operators used in Excel formulas are similar to the ones used in math class.

* Subtraction - minus sign ( - )
* Addition - plus sign ( + )
* Division - forward slash ( / )
* Multiplication - asterisk (* )
* Exponentiation - caret (^ )

Order of Operations

If more than one operator is used in a formula, there is a specific order that Excel will follow to perform these mathematical operations. This order of operations can be changed by adding brackets to the equation. An easy way to remember the order of operations is to use the acronym:

BEDMAS

The Order of Operations is:

Brackets
Exponents
Division
Multiplication
Addition
Subtraction

How the Order of Operations Works

Any operation(s) contained in brackets will be carried out first followed by any exponents.

After that, Excel considers division or multiplication operations to be of equal importance, and carries out these operations in the order they occur left to right in the equation.

The same goes for the next two operations – addition and subtraction. They are considered equal in the order of operations. Which ever one appears first in an equation, either addition or subtraction, is the operation carried out first."