Jump to content
Sign in to follow this  
Timo

Excel-COM-Array

Recommended Posts

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

Bye...,Timo

Share this post


Link to post
Share on other sites

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 by Burrup

qq

Share this post


Link to post
Share on other sites

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...


Bye...,Timo

Share this post


Link to post
Share on other sites

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


__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...