xls mit Datenmigration

exestend

Mitglied
Hallo,

ich habe zwei Systeme.

Bei dem einem System, habe ich alle Daten, auch welche die ich nicht brauche. Ist eine Großhändlerliste mit Artikeln.

Also Artikelname, Preis, Menge, Hersteller, Bild-URL etc.

Diese .xls bekomme ich einmal am Tag.

Da brauche ich aber nicht alles. Ich brauche von der Tabelle nur einige Werte und die heißen in meinem System auch anders.

Also
Großhändler:
Preis, Menge, Artikelname, Kurzbeschreibung, Beschreibung etc.

In meinem System:
price,name, short_desc, desc etc.

Gibt es eine Möglichkeit, dass zu automatisieren?

Irgend ein php, jquery oder sonst was Script, wo man die Datei einlesen kann und diese dann bearbeitet wird.

Nach dem Motto:
Nehme Spalte "A" und ändere Zeile 1 in "price"
Lösche Spalte "B"
Lösche Spalte "C"
Lösche Spalte "D"
Ändere Spalte "E" Zeile 1 in "Aktiv" und von Zeile 2 alles in "1"

etc.

ps: Ich habe auch im Open office Forum danach gefragt, zwecks Lösung mit calc, denke aber trotzdem nebenbei darüber nach ob es mit einer Programmierung ggf. nicht besser ginge zwecks automatischem Upload oder so.

LG
 

ComFreek

Mod | @comfreek
Moderator
Hallo,

das geht mit PowerShell sehr einfach.
Das folgende Skript lädt before.xlsx, ändert Zelle A1 in "price", löscht die Spalten B, C und D:
Code:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")

$excel = New-Object -ComObject Excel.Application
# Prevent dialog boxes (e.g. 'do you want overwrite file xyz' when using SaveAs)
$excel.DisplayAlerts = $false;
$excel.Visible = $True

$workbook = $excel.Workbooks.Open("D:\Desktop\before.xlsx")
$worksheet = $workbook.Worksheets.Item(1)

$worksheet.Cells.Item(1, 1) = "price"
Start-Sleep -Seconds 2

# Delete columns B, C, D
$worksheet.Columns.Item(2).Delete()
$worksheet.Columns.Item(2).Delete()
$worksheet.Columns.Item(2).Delete()

$workbook.SaveAs(
    "D:\Desktop\after.xlsx",
    [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault,
    [type]::Missing, # password
    [type]::Missing, # write-reservation password
    $false, # read-only recommended
    $false, # create backup
    [Microsoft.Office.Interop.Excel.XlSaveAsAccessMode]::xlNoChange,
    [Microsoft.Office.Interop.Excel.XlSaveConflictResolution]::xlLocalSessionChanges
)

$excel.Quit()

Vorher:
img1.PNG

Nachher:
img2.PNG


Hier eine Einführung zu dem Thema: http://blogs.technet.com/b/heyscrip...s-powershell-to-automate-microsoft-excel.aspx

Weitere Ressourcen auf MSDN:

- Excel.Application Members: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.application_members.aspx
- Workbook Interface: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbook.aspx
- Workbook Members: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbook_members.aspx
- Worksheet Interface: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheet.aspx
- Worksheet Members: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheet_members.aspx

(Auf jeder Interface-Website kannst du unten unter "See also" auf die zugehörige Members-Seite gelangen.)
 

exestend

Mitglied
Cool, vielen vielen dank!
Hat mir sehr geholfen und geht ja noch einfacher wie mit excel / OO/LO

Danke!


ps: Ginge das auch mit ODS ?

Habe hier:
https://heyfryckles.wordpress.com
das gefunden:
Code:
param
(
)
begin
{
}
process
{
    $strStartFolder = "C:\MyDocument"
    $arrFiles = Get-ChildItem $strStartFolder -recurse -include @("*.xls") -erroraction silentlycontinue | Sort-Object
    $objExcel = New-Object -ComObject "excel.application" -erroraction silentlycontinue
    $objExcel.Visible = $true
    foreach ($objFile in $arrFiles)
    {
        $strOriginalPath = $objFile.fullname
    
        write-host $strOriginalPath
    
        $objDocument = $objExcel.workbooks.open($strOriginalPath)
        
        $strSaveAsPath = $strOriginalPath
        $strSaveAsPath = $strSaveAsPath.Replace(".xls", ".ods")
        # see XlFileFormat enumeration constants:
        # http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
        
        $intFileFormat =  60
        
        if (Test-Path $strSaveAsPath) {
            Remove-Item $strSaveAsPath
        }
        
        $objDocument.SaveAs($strSaveAsPath, $intFileFormat)
        $objDocument.close()
        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($objDocument)
        
        Remove-Item $strOriginalPath
        
    }
    
    $objExcel.Quit()
    [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel)
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
    
}
end
{
}

könnte man das noch intigrieren, oder wäre es sinnvoller, dass als zweites script zu speichern und danach aufzurufen?

LG
 
Zuletzt bearbeitet:

ComFreek

Mod | @comfreek
Moderator
Ja, du könntest statt einer XLSX-Datei (wie in meinem Skript) auch einfach eine ODS-Datei öffnen oder eine XLSX-Datei in einer ODS-Datei abspeichern.

Im Prinzip musst du dir das PowerShell-Skript nur wie einen Dirigenten vorstellen. Es öffnet Excel, lädt, verändert und speichert. Es läuft alles über Excel, so wie es beim normalen Betrieb mit einem Menschen auch laufen würde. Ergo dürften auch andere Dateiformate, die sonst auch funktionieren, auch hier geöffnet werden können.
Übrigens kannst du das Excel-Fenster auch ausblenden, wenn du "$excel.Visible = $True" zu "$excel.Visible = $False" abänderst.

Das kannst du freilich integrieren. Im Prinzip müsstest du nur den Dateinamen und das Dateiformat des SaveAs-Teils meines Skripts ändern:
Code:
$workbook.SaveAs(
    "D:\Desktop\after.ods", # ÄNDERUNG: Nun *.ods
    [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenDocumentSpreadsheet, # ÄNDERUNG: Nun nicht mehr Default, sondern spezifisch ODS angegben
    [type]::Missing, # password
    [type]::Missing, # write-reservation password
    $false, # read-only recommended
    $false, # create backup
    [Microsoft.Office.Interop.Excel.XlSaveAsAccessMode]::xlNoChange,
    [Microsoft.Office.Interop.Excel.XlSaveConflictResolution]::xlLocalSessionChanges
)

Der von dir gefundene Code macht dasselbe:

1) Er ändert xls zu ods im Dateinamen.
2) Er benutzt die Zahlenkonstante 60 als Dateiformat. Führt man in der PowerShell-Konsole [int] [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenDocumentSpreadsheet aus, so ergibt dies auch 60, d. h. wir benutzen dieselbe Konstante!
 

exestend

Mitglied
Cool, dass hat soweit gut funktioniert.

Aber eine sache habe ich nicht hinbekommen bis jetzt.

Die Ausgabedatei muss ja eine bestimmte reinfolge haben.
Das heißt, ich muss die Inhalte der eingeladenen Datei auch kopieren.

Großhändler hat z.B.
ID,Active,Titel,Cat

So, da lösche ich jetzt 1,2,4

Das heißt, die neue Spalte 1 ist "Title"

In der Ausgabedatei muss "Title" aber z.B. an dritter Stelle stehen.
Das habe ich noch nicht so ganz durchschaut.

Wäre es z.B. Sinnvoll erst alles zu löschen, was ich nicht brauche, die Felder zu bennen und ganz zum schluss alles richtig sortieren oder gleich beim umbenennen sortieren mit dem einfügen der richtigen Inhalte .z.B.

LG
 

ComFreek

Mod | @comfreek
Moderator
Wenn ich dich richtig verstehe, möchtest du die Spalten 1, 2, 4 nicht löschen (sodass die Spalte von rechts aufgeschoben wird) sondern nur von allen Werten "befreien"?
Genau das ist der Unterschied zwischen Delete und Clear.

Code:
# Lösche zweite Spalte, sodass die 3. Spalte die neue 2. wird usw.
$worksheet.Columns.Item(2).Delete()

# Lösche nur alle Werte der zweiten Spalte, die 2. Spalte bleibt die 2. Spalte!
$worksheet.Columns.Items(2).Clear()