def csve2Excel(csveFile)#{{{{ #-------------------------------------------------------------------------------------------------- # Konvertiert eine csv-enhanced-Datei in eine Excel-Datei gleichen Namens # file.csve => file.xlsx # Cave: csve-Dateien liegen (hoffentlich ;-) immer in UNIX-Format und UTF-8 vor # # Erlaubte Formatanweisungen in csve-Dateien # %(row|range):(columnRange|range):(numberformat|font|fontsize|alignment|color|border):(|||||) # : [A-AZ]-[A-AZ] gilt nur fuer naechste Zeile # : (|[A-AZ][1-1000]-[A-AZ][1-1000]) # : (currency|currencyshort|float|integer|date|time|percent) # : (bold|italic) # : # : (left|center|right) # : (|white|black|green|yellow|...) # : (top|bottom)-(bold|medium) # # %row => betrifft angegebene Spalten der Folgezeile # %range => betrifft Range nach Excel-Konvention # Cave: Range-Anfang und -Ende sind duch '-' getrennt, nicht wie in Excel durch ':', da letzteres bereits das Splitkennzeichen ist # # Beispiel # %row:B-F:font:bold # %row:B-F:color:50 # %row:A-Z:border:top-bold # %range:C2-L70:numberformat:currency # %range:C2-L70:fontsize:10 # %range:C-C:alignment:right #-------------------------------------------------------------------------------------------------- begin #-------------------------------------------------------------------------------------------------- # Vorbelegungen #-------------------------------------------------------------------------------------------------- percFormat="0,00%" currencyFormat="#.##0,00 \"Eur\";[Rot]-#.##0,00 \"Eur\"" # currencyShortFormat="#.##0 \"Eur\";[Rot]-#.##0 \"Eur\"" currencyShortFormat="#.##0;[Rot]-#.##0" floatFormat="#.##0,00;[Rot]-#.##0,00" intFormat="#.##0;[Rot]-#.##0" dateFormat="TT.MM.JJ" timeFormat="hh:mm:ss" #-------------------------------------------------------------------------------------------------- (! csveFile.match(/\.csve$/)) && die("csve2Excel: Datei #{csveFile} ist keine .csve-Datei") (! File.exist?(csveFile)) && die("csve2Excel: Datei #{csveFile} existiert nicht") xlsxFile=csveFile.sub(/.csve$/,".xlsx") #-------------------------------------------------------------------------------------------------- (! $excel) && startExcel book=$excel.Workbooks.add sheet=book.Worksheets(1) #-------------------------------------------------------------------------------------------------- row=0 File.open(csveFile,"r"){ |file| file.each{ |line| #----------------------------------------------------------------------------- line.chomp! line.encode!("ISO-8859-1",undef: :replace,invalid: :replace, replace: "") line=replaceUmlaute(line) #----------------------------------------------------------------------------- # Kommentarzeile ueberspringen #----------------------------------------------------------------------------- if(line.match(/^\s*#/)) next #----------------------------------------------------------------------------- # Leerzeile quasi einfuegen #----------------------------------------------------------------------------- elsif(line.match(/^\s*$/)) row+=1 next #----------------------------------------------------------------------------- # Formatanweisung verarbeiten #----------------------------------------------------------------------------- #{{{{ elsif(line.match(/^\s*%/)) #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - range=numberFormat=fontSize=alignment=color=borderWeight=borderEdge="" setNumberFormat=setBold=setItalic=setFontSize=setAlignment=setColor=setBorder=false #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - myArr=line.split(':') (myArr.length != 4) && die("csve2Excel: Formatanweisung auf Zeile #{row} (#{line}) hat keine 4 Elemente") #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - # Zeile oder Range ? # => Range extrahieren und im Fall einer Zeile um die Nummer der Folgezeile ergaenzen #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - case myArr[0] when "%row" #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - # A-F #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - (! myArr[1].match(/^([[:alpha:]]{1,2})-([[:alpha:]]{1,2})$/)) && die("csve2Excel Zeile #{row}: Ungueltiger Row-Parameter #{myArr[1]}") range=$1+(row+1).to_s+":"+$2+(row+1).to_s # Erweiterung zu Range fuer Folgezeile: B-G => B7:G7 when "%range" #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - # A-F oder B3-AE70 #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - (! myArr[1].match(/^([[:alpha:]]{1,2}[[:digit:]]*-[[:alpha:]]{1,2}[[:digit:]]*)$/)) && die("csve2Excel Zeile #{row}: Ungueltiger Range-Parameter #{myArr[1]}") range=$1.sub(/-/,":") # A-F => A:F oder B7-D70 => B7:D70 else die("csve2Excel Zeile #{row}: Ungueltiger Parameter #{myArr[0]}") end # #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - # Formatierung verarbeiten #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - case myArr[2] #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - when "numberformat" setNumberFormat=true case myArr[3] when "currency" numberFormat=currencyFormat when "currencyshort" numberFormat=currencyShortFormat when "percent" numberFormat=percentFormat when "integer" numberFormat=integerFormat when "float" numberFormat=floatFormat when "date" numberFormat=dateFormat when "time" numberFormat=timeFormat else die("csve2Excel Zeile #{row}: Ungueltiger Zahlenformat-Parameter |#{myArr[3]}|") end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - when "font" case myArr[3] when "bold" setBold=true when "italic" setItalic=true else die("csve2Excel Zeile #{row}: Ungueltiger Font-Parameter #{myArr[3]}") end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - when "fontsize" setFontSize=true if(myArr[3].match(/^[[:digit:]]+$/)) fontSize=myArr[3].to_i else die("csve2Excel Zeile #{row}: Ungueltiger Fontsize-Parameter #{myArr[3]}") end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - when "alignment" setAlignment=true case myArr[3] when "left" alignment=ExcelConst::XlLeft when "center" alignment=ExcelConst::XlCenter when "right" alignment=ExcelConst::XlRight else die("csve2Excel Zeile #{row}: Ungueltiger Alignment-Parameter #{myArr[3]}") end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - when "color" setColor=true if(myArr[3].match(/^[[:digit:]]+$/)) color=myArr[3].to_i else die("csve2Excel Zeile #{row}: Ungueltiger Farb-Parameter #{myArr[3]}") end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - when "border" setBorder=true borderArr=myArr[3].split('-') # (top|bottom)-(bold|medium) (borderArr.length != 2) && die("csve2Excel: Borderanweisung auf Zeile #{row} (#{line}) hat keine 2 Elemente") #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - if(borderArr[0] == "top") borderEdge=ExcelConst::XlTop elsif(borderArr[0] == "bottom") borderEdge=ExcelConst::XlBottom else die("csve2Excel Zeile #{row}: Ungueltiger Linienparameter #{borderArr[0]}") end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - if(borderArr[1] == "bold") borderWeight=ExcelConst::XlThick elsif(borderArr[1] == "medium") borderWeight=ExcelConst::XlMedium else die("csve2Excel Zeile #{row}: Ungueltiger Linienstaerkenparameter #{borderArr[1]}") end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - else die("csve2Excel Zeile #{row}: Ungueltiger Formatierungs-Parameter #{myArr[2]}") end #----------------------------------------------------------------------------- # Ermittelte Formatierungen umsetzen #----------------------------------------------------------------------------- if(setNumberFormat) puts("setting #{range} => #{numberFormat}") sheet.range(range).numberFormat=numberFormat end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - if(setBold) puts("setting #{range} => bold") sheet.range(range).Font.Bold=1 end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - if(setItalic) puts("setting #{range} => italic") sheet.range(range).Font.Italic=1 end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - if(setFontSize) puts("setting #{range} => Fontsize #{fontSize}") sheet.range(range).FontSize=fontSize end if(setAlignment) puts("setting #{range} => Alignment #{myArr[3]}") sheet.range(range).horizontalAlignment=alignment end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - if(setColor) puts("setting #{range} => Color #{color}") sheet.range(range).Interior.colorIndex=color end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - if(setBorder) puts("setting #{range} => Border") sheet.range(range).Borders(borderEdge).LineStyle=ExcelConst::XlContinuous sheet.range(range).Borders(borderEdge).Weight=borderWeight end #- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - next # Naechste Zeile einlesen end #}}}} #----------------------------------------------------------------------------- # Keine Formatanweisung sondern "content" # Zeile nach Excel uebertragen #----------------------------------------------------------------------------- row+=1 puts("Zeile #{row}") colArr=line.split(';') col=0 colArr.each{ |content| col+=1 sheet.cells(row,col).Value=content } } } #-------------------------------------------------------------------------------------------------- # Restformatierungen #-------------------------------------------------------------------------------------------------- sheet.Columns("A:Z").autoFit #-------------------------------------------------------------------------------------------------- book.saveAs(xlsxFile) book.close puts("csve2Excel: => #{xlsxFile}") rescue => exception die("csve2Excel: #{exception.message}") ensure end end#}}}} def startExcel#{{{{ begin puts("trying to connect to Excel ...") $excel=WIN32OLE.connect('Excel.Application') $excelWasOpen=true rescue begin puts("... failed => launching Excel ...") $excel=WIN32OLE.new('Excel.Application'); checkValue($excel) rescue => exception die("startExcel: #{exception.message}") end end $excel.DisplayAlerts=false # verhindert Schlussfrage, ob gespeichert werden soll puts("loading Excel-constants ...") WIN32OLE.const_load($excel,ExcelConst) end#}}}}