|
Hi! Here is a hopefully convenient
description of my situation: - I have a main folder, containing
several subfolders. - Every (sub)folder
contains one or more .xls - Workbooks. - Every Workbook contains one or more
different Spreadsheets. - The workbooks contain some cells which
have Hyperlink addresses to other, relating workbooks. - Some cells in wokrbooks contain
comments, which must also be exported. My ultimate aim is to get ALL data, that means cell values, hyperlink addresses in cells, and comments, into
corresponding PostgreSQL database table. Principally there are at maximum three
different data per cell (value,
hyperlink address, comment). Here is my idea.,
which I lack of Python programming practice to implement in a reasonable amount of time, so every
help is welcome: Some kind of For - opens all workbooks one after another. Then
looks into every spreadsheet of a workbook. For every spreadsheet, read
out cell values, cell hyperlink addresses (if there are any), and cell comments
(if there are any). (Perhaps the win32com - module is
helpful?) Then open a PostgreSQL database
connection, and insert the cell values into a corresponding table. (which should
already be created in the database before?) This table should also contain a column
for the possible hyperlink addresses, and possible cell comment strings. I have enclosed various Code which might be of help: - VBA Code samples for reading a cell
comment and a hyperlink address from a spreadsheet cell. - PostgreSQLConnection.py, a class to
connect to a PostgreSQL database - Gerold.py, which uses the win32com -
module to access and manipulate an Excel workbook. My problem is how to combine these code
samples, especially implementing the for-loop for going through the folders and
opening Excel workbooks/spreadsheets, reading Excel data with win32com, and creating/inserting this data in corresonding tables in the
database, using pyPgSQL. So, this sounds like fun work... any
help appreciated. Cheers Juergen |
'---------------------------------------------------------------------------------------------------------------- 'Extract the text from a cell comment '----------------------------------------------------------------------------------------------------------------
Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
On Error Resume Next
strGotIt = WorksheetFunction.Clean _
(rCommentCell.Comment.Text)
GetCommentText = strGotIt
On Error GoTo 0
End Function
'----------------------------------------------------------------------------------------------------------------
'Extract the underlying address from a cell containing a Hyperlink
'----------------------------------------------------------------------------------------------------------------
Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:";, "")
End Function#!/usr/bin/env python
# -*- coding: iso-8859-1 -*-
import win32com.client
import win32ui
# Zum Excel verbinden
app = win32com.client.Dispatch("Excel.Application")
app.visible = True
# Neue Arbeitsmappe
workbook = app.Workbooks.Add()
# Zum Arbeitsblatt verbinden
sheet = workbook.Sheets[0]
# Kommentare hinzufügen
sheet.Range("A1").AddComment()
sheet.Range("A1").Comment.Text("Hallo")
sheet.Range("B2").AddComment()
sheet.Range("B2").Comment.Text("Welt")
# Kommentare auslesen und anzeigen
win32ui.MessageBox(
"Kommentar in A1: %s" % sheet.Range("A1").Comment.Text()
)
win32ui.MessageBox(
"Kommentar in B2: %s" % sheet.Range("B2").Comment.Text()
)
# Variablen löschen und damit die Verbindung zu Excel lösen
del sheet
del app
from pyPgSQL import PgSQL
class dbConn:
def __init__(self, **kwargs):
self.conn = None
self.conndata = kwargs
self.dbname = self.conndata['db']
self.PgSQL = PgSQL
self.connectPostGreSQL()
def connectPostGreSQL(self):
self.conn = self.PgSQL.connect( host=self.conndata['host'], \
database=self.conndata['db'], \
user=self.conndata['user'], \
password = self.conndata['passwd'])
self.cursor = self.conn.cursor()
# So und für die restliche SQL Syntax von Postgresql sollte man sich ein
Buch holen, da
# so Befehle wie concat, die man aus mySql her kennt, nicht funktionieren.
# Hier muss man den Befehl umbauen und Concatinieren mit || . Nur als ein
Beispiel
# von einigen.
newDB = dbConn(host="localhost", user="user1", passwd="", db="testDB")
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
