Create an excel file from set of excel data

[luke devon <lu...@yahoo.com>]

Hi 


 
I do have a requirement to create a excel
file with some existing data. I would like to check with the mailing list whether
any one who has came up with a solution for similar requirement

First let me bring in the scenario. (sample files attached) As I mentioned below, 
there are more than 100 excel files with some data.
 
FileName_Unix_Zone2.xls
FileName_Linux_Zone1.xls
FileName_Ruby_Zone3.xls
FileName_Fedora_Zone4.xls
FileName_Ubuntu_Zone5.xls
FileName_gentoo_Zone2.xls
FileName_drupal_Zone3.xls ............so on. There can be 
only 5 numbers of zones. Zone value will not comes with the excel contents. It 
is always in the file name.
 
Explaining the excel data 
format:
 
 
In the CELLB1 there is a value. This value keep changing .file to file it is differ. But 
location is constant. It is B1
In the excel contents, there is 3 column values. ABC1, 
ABC2 and ABC3. ABC1 is located in B5, ABC2 located in C5 and ABC3 located in D5. 
This is also constant. Values of column headers won’t change and locations are 
also fixed.
 
Cell format of the contents of ABC3 is "Number with 2 
decimal points"
 
Since these set of files are auto generated, column 
headers and locations are fixed.
 
Requrement:
 
I wanted extract all these values from each and every 
files and create a one file. For the new file, 
 
A2 must be Zone, B2 must be XYZ, C2 must be ABC1, D2 is 
ABC2 and E2 is ABC3.
 
When filling the content for the new file, 
 
For the Zone filed, I need to extract the value from the 
file name since value for the Zone is always comes in the file 
name.
 
For the XYZ filed, I need to extract the 
value from excel contents which is located in CELLB1.
 
For the ABC1, ABC2 and ABC3 fields, extracting values 
from the excel contents.
 
Usually, ABC1, ABC2 and ABC3 do have more values. Hence 
when we print these values into a single file for the Zone and XYZ can have more 
spaces. 
 
Zone       XYZ        ABC1     ABC2     ABC3
zone1      Unix        a              s              d
?              ?              d              f               g
?              ?              x              g              r
 
? = spaces
 
Spaces needed to fill up by the same values belong to the 
certain filed. 
 
In the example,
 
Zone       XYZ        ABC1     ABC2     ABC3
zone1      Unix        a              s              d
zone1      unix         d              f               g
zone1      unix         x              g              r
 
 
Like that, need to extract all excel files and put in to 
a single file as follows,
 
Zone       XYZ        ABC1     ABC2     ABC3
zone1      Unix        a              s              d
zone1      unix         d              f               g
zone1      unix         x              g              d
zone3      Linux      a              s              d
zone3      Linux      d              f               g
zone3      Linux      x              g              r
 
When save in to a file, contents must be sorted by Zone 
column and file name must be ends with the time stamp 
(yyyymmdd).
 
eg: Report_20100510.xls

Thanks in advance
Luke