asked on

update an Excel workbook

So I have an Excel workbook with three Worksheets that is basically a weekly executive summary of a lot of data in a postgresql database -

The main Worksheet will eventually have 12 data columns and I will use the Month as the offset. . While worksheet2 is also updated and the content in Worksheet 3 is replaced.

The source of the data is currently a series of SQL selects that all output to a text file, and then someone, namely me, has to update the Workbook and send the updated result to an Exchange group for distribution. Obviously the goal is to automate this and run it out of Cron weekly.

I moved the xlsx to my FreeBSD server and using unzip pulled apart the xlsx. But there are a lot of components and it' s not obvious how for example the "sharedStrings.xml" is updated in relation to the worksheets/sheet1.xml files.

Hoping someone may have a link to a whitepaper or book that would give me better insight into the relationships here, and on how to work with these files before I zip them back up and distribute them.

TIA --

aikimark

Please post a representative sample of the source and destination files. Do not substitute XML files for XLSX files.

Michael Fowler

Without more detail it is hard to say but one option here could be to do the work at the SQL side using Views and/or additional tables.

This would enable the excel to be live and users would only need to open/update the sheet to see the latest data.

https://support.office.com/en-gb/article/Connect-a-SQL-Server-database-to-your-workbook-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad

Phonebuff

ASKER

@MIchael, Not an option unfortunately --

So I get this tree when I unzip the xlsx

The majority of the "strings" are in sharedStrings.xml and the rest of the data in the sheet?.xml files.

If no one is familiar with this I will just keep digging till I get there -

Thanks -

-rw-r--r--  1 jms  1002  29915 Apr 10 11:03 April 2015 Stats.xlsx
-rwxr-xr-x  1 jms  1002   1854 Jan  1  1980 [Content_Types].xml

_rels:
total 22
drwxr-xr-x  2 jms  1002    3 Apr 12 13:37 .
drwxr-xr-x  5 jms  1002    7 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  588 Jan  1  1980 .rels

docProps:
total 26
drwxr-xr-x  2 jms  1002    4 Apr 12 13:37 .
drwxr-xr-x  5 jms  1002    7 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  828 Jan  1  1980 app.xml
-rwxr-xr-x  1 jms  1002  623 Jan  1  1980 core.xml

xl:
total 98
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 .
drwxr-xr-x  5 jms  1002     7 Apr 12 13:37 ..
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 _rels
-rwxr-xr-x  1 jms  1002   722 Jan  1  1980 calcChain.xml
drwxr-xr-x  3 jms  1002     4 Apr 12 13:37 drawings
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 media
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 printerSettings
-rwxr-xr-x  1 jms  1002  9128 Jan  1  1980 sharedStrings.xml
-rwxr-xr-x  1 jms  1002  4783 Jan  1  1980 styles.xml
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 theme
-rwxr-xr-x  1 jms  1002   634 Jan  1  1980 workbook.xml
drwxr-xr-x  3 jms  1002     6 Apr 12 13:41 worksheets

cd xl

-rwxr-xr-x  1 jms  1002   722 Jan  1  1980 calcChain.xml
-rwxr-xr-x  1 jms  1002  9128 Jan  1  1980 sharedStrings.xml
-rwxr-xr-x  1 jms  1002  4783 Jan  1  1980 styles.xml
-rwxr-xr-x  1 jms  1002   634 Jan  1  1980 workbook.xml

_rels:
total 22
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  1113 Jan  1  1980 workbook.xml.rels

drawings:
total 30
drwxr-xr-x  3 jms  1002     4 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 _rels
-rwxr-xr-x  1 jms  1002  3788 Jan  1  1980 drawing1.xml

media:
total 30
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  8971 Jan  1  1980 image1.png

printerSettings:
total 26
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  7992 Jan  1  1980 printerSettings1.bin

theme:
total 26
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  6995 Jan  1  1980 theme1.xml

worksheets:
total 63
drwxr-xr-x  3 jms  1002      6 Apr 12 13:41 .
drwxr-xr-x  8 jms  1002     12 Apr 12 13:37 ..
drwxr-xr-x  2 jms  1002      3 Apr 12 13:37 _rels
-rwxr-xr-x  1 jms  1002   6204 Jan  1  1980 sheet1.xml
-rwxr-xr-x  1 jms  1002   9210 Jan  1  1980 sheet2.xml
-rwxr-xr-x  1 jms  1002  14387 Jan  1  1980 sheet3.xml

Open in new window

ASKER CERTIFIED SOLUTION

Rob Brockett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phonebuff

ASKER

Why do you need to break into the xml structure?

Must the file be xlsx or can it be changed to allow inclusion of macros eg xlsm or my general preference of xlsb?

The data is on a FreeBSD box in a very large relation database. The Worksheet is an executive summary snapshot and is to be distributed weekly out of a CRON job via e-mail directly to the principals.

NO WINDOWS, NO EXCEL SOFTWARE, NO PEOPLE.

I do have this mostly figured out now and I do appreciate the advise to date.

SOLUTION

Michael Fowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phonebuff

ASKER

The answers will help me and others with future projects, but this one is still an XML file that I am working with, as there is no other option available..