Excel-COM-Array

[Timo]

Hello together,

I read from Excel via COM my data into an array and display it in a ListView. Thats works fine.

But in the excelfile there a some empty lines. I don't know how to get them out.

Has anyone an idea who to filter them so that I can delete them from the array?

$CellRange = "A1:P50"
   $oExcelDoc = ObjGet($dateiname)
   $oDocument = $oExcelDoc.Worksheets(1)
   $array_lrl = $odocument.range($CellRange).value
   $string = ""
   For $y = 0 to Ubound($array_lrl,2)-1
      $trennzeichen=""
      GUICtrlCreateListViewItem ("",$liste)
      For $x = 0 to Ubound ($array_lrl,1)-1      
         $string = $array_lrl[$x][$y]    
         GUICtrlSetData (-1, $trennzeichen & $string)
         $trennzeichen = $trennzeichen & "|"
      Next   
   Next

[buzz44]

Why do you create and empty list view item and then set the data? It makes alot more sense to create the list view item directly with the information in it...

$CellRange = "A1:P50"
   $oExcelDoc = ObjGet($dateiname)
   $oDocument = $oExcelDoc.Worksheets(1)
   $array_lrl = $odocument.range($CellRange).value
   $string = ""
   For $y = 0 to Ubound($array_lrl,2)-1
      $trennzeichen=""
      For $x = 0 to Ubound ($array_lrl,1)-1     
         $string = $array_lrl[$x][$y]   
         GUICtrlCreateListViewItem ($trennzeichen & $string, $liste)
         $trennzeichen = $trennzeichen & "|"
      Next  
   Next

Atleast I think that should work. I'm don't use Excel /w COM.

Secondly, in relation to your question. If the above doesn't solve your problem just check if the array index isn't empty... eg.

$CellRange = "A1:P50"
   $oExcelDoc = ObjGet($dateiname)
   $oDocument = $oExcelDoc.Worksheets(1)
   $array_lrl = $odocument.range($CellRange).value
   $string = ""
   For $y = 0 to Ubound($array_lrl,2)-1
      $trennzeichen=""
      For $x = 0 to Ubound ($array_lrl,1)-1     
         $string = $array_lrl[$x][$y]
         If $string <> "" Then
            GUICtrlCreateListViewItem ($trennzeichen & $string, $liste)
            $trennzeichen = $trennzeichen & "|"
         EndIf
      Next  
   Next

Edited May 29, 2005 by Burrup

[Timo]

Hi Burrup,

your idea with the 'GUICtrlCreateListViewItem' is fine. That makes sense. Thanks.

But the problem with the empty lines still exists. In the excelsheet there are complete empty lines; with your check wether '$string' is empty we check every cell.

Here's a short example of my problem, the second and the fourth line should be deleted but I need the empty cell in the last line for calculation:

A1 B1 C1 D1 E1

0 0 0 0 0

A3 B3 C3 D3 E3

0 0 0 0 0

A5 B6 0 0 E6

Hm...

[/dev/null]

But the problem with the empty lines still exists. In the excelsheet there are complete empty lines; with your check wether '$string' is empty we check every cell.

Figure out if the lines are empty (all zeros ...), copy the "non empty" lines to a new array and use that array for your code. Not very elegant, but works.

$n = 0

   dim $new_array_lrl[Ubound($array_lrl,2)-1][Ubound ($array_lrl,1)-1]
   For $y = 0 to Ubound($array_lrl,2)-1
      $not_empty = 0
      For $x = 0 to Ubound ($array_lrl,1)-1     
         $string = $array_lrl[$x][$y]   
         if $string <> "0" then
            $not_empty = 1
            exitloop
         endif
      Next  
      if $not_empty = 1 then
         For $x = 0 to Ubound ($array_lrl,1)-1      
            $new_array_lrl[$x][$n]=$array_lrl[$x][$y]    
         Next  
         $n = $n + 1
      endif 
   Next

   For $y = 0 to $n-1
      For $x = 0 to Ubound ($new_array_lrl,1)-1     
         $string = $new_array_lrl[$x][$y]   
        ;; ==> Your code here ....
      Next  
   Next

Cheers

Kurt