Exel-Import

In dem Script aus den "Compiled Table Tools" steht:

sysClient.Request("selection")

Dies gibt aus dem englischen Excel zurück (Zahlen nur als Beispiel):

[Book1]Sheet1!R3C1:R5C1

wobei R = Row und C = Column

im deutschen Excel gibt dieselbe Request zurück:

[Mappe1]Tabelle1!Z3S1:Z5S1

wobei Z = Zeile und S = Spalte

In dem Skript werden im Folgenden die Buchstaben R und Z als Tokens benutzt und noch ein paar mal verwandt. Statt "3" und "1" bekommt ArcView dann aber im deutschen Excel "Z3S1" was sie logischerweise nicht in eine Zahl umwandeln kann.

Also:
Skript "Table.ImportFromExcel.C" öffnen und alle Vorkommen der besagten "R" und "C" durch "Z" bzw. "S" ersetzen, dann funktioniert's auch mit den Umlauten.

Gruß von Maxilla

Hab's für den eigenen Gebrauch mal wie oben angesprochen auf das deutsche Excel gepolt. Hoffe, die lieben Anuva-Leute kriegen keine "Speicherplatz-Krise" wenn ich das hier mal hinkopiere (ansonsten bitte wieder löschen - aber so lang ist's ja auch net :)

Also, wer mag:


' Name: Table.ImportFromExcel
'
' Title: Creates a new table document from an Excel spreadsheet
'
' Topics: Tables
'
' Description: This script allows you to create a table document using the data
' contained in a Microsoft Excel Spreadsheet. Start Excel and open the spreadsheet
' that you want to use in ArcView, then run this script. A new table will be created
' by using DDE to talk to Excel. This script doesn't handle boolean fields, doesn't
' handle non-contiguous (a column without a field name), always assumes dates are of
' the format MM/dd/yy, and always writes a file called "C:\TEMP\EXCEL.DBF".
'
' Requires: Microsoft Excel is running and a selection exists. The first row in the
' rows contain data. You have a directory called "C:\TEMP" that you can write to.
'
' MODIFIED FOR GERMAN EXCEL : M.
'
sysClient = DDEClient.Make( "Excel","System")

if (sysClient.HasError) then
MsgBox.Info( sysClient.GetErrorMsg, "")
return NIL
' if you want to start Excel instead, use the following two lines instead of the two above
' system.execute("c:\msoffice\excel\excel.exe <path>\<book_name>.xls")
' sysClient = DDEClient.Make("Excel","System")
end

v= vtab.makenew("C:\temp\excel.dbf".AsFileName, dBase)
fldlst = {}

fulltopic = sysClient.Request("selection") 'returns [BookN]SheetN!RnCn:RnCn
sysClient.Close
topiclist=fulltopic.AsTokens("!:")

if (topiclist.count < 3) then
MsgBox.Info("Selection in Excel?","")
return NIL
end

sheetname = topiclist.get(0)

startrange = topiclist.get(1)
endrange = topiclist.get(2)

startrow = startrange.AsTokens("ZS").Get(0).AsNumber
startcol = startrange.AsTokens("ZS").Get(1).AsNumber
endrow = endrange.AsTokens("ZS").Get(0).AsNumber
endcol = endrange.AsTokens("ZS").Get(1).AsNumber

sheet = DDEClient.Make( "Excel", sheetname )

for each column_num in startcol..endcol
column_name = sheet.request( "Z"+startrow.AsString+"S"+column_num.AsString )

val = sheet.request( "Z"+(startrow+1).AsString+"S"+column_num.AsString )

if ((val.Trim.IsNumber) and (val.Trim.Contains("."))) then
decimal_places = (val.trim.count - val.trim.IndexOf(".")) - 1
f = field.make(column_name.trim,#FIELD_DECIMAL,12,decimal_places)
elseif (val.Trim.IsNumber) then
f = field.make(column_name.trim,#FIELD_DECIMAL,12,0)
elseif (val.Trim.Contains("/")) then
f = field.make(column_name.trim,#FIELD_DATE,8,0)
else
f = field.make(column_name.trim,#FIELD_CHAR,val.count+5,0)
' adding 5 to the length of the first string value of the column is arbitrary
' set it to whatever accommodates your data best
end

fldlst.add(f)

end

v.AddFields(fldlst)

for each row in (startrow+1)..endrow
i = 0
newRec = v.AddRecord

for each col in startcol..endcol
if (fldlst.get(i).GetType=#FIELD_CHAR) then
v.setvalue(fldlst.get(i),newRec,sheet.request( "Z"+row.AsString+"S"+col.AsString ).trim )
elseif (fldlst.get(i).GetType=#FIELD_DECIMAL) then
v.SetValue(fldlst.get(i),newRec,sheet.request( "Z"+row.AsString+"S"+col.AsString ))
else (fldlst.get(i).GetType=#FIELD_DATE)
the_date = date.make(sheet.request( "Z"+row.AsString+"S"+col.AsString ).trim,"MM/dd/yy")
v.setvalue(fldlst.get(i),newRec,the_date)
end
i = i + 1
end

end

table.make(v)

sheet.Close