Jump to content
LarsJ

Data display functions based on virtual listviews

Recommended Posts

LarsJ

_ArrayDisplayEx

This is copied from the top of ArrayDisplayEx.au3:

; Displays a 1D or 2D array in a virtual ListView
Func _ArrayDisplayEx( _
  $aArray, _          ; 1D/2D array eg. StringSplit("Mo,Tu,We,Th,Fr,Sa,Su", ",")
  $sTitle = "", _     ; GUI title bar text, default title is set to "ArrayDisplayEx"
  $sHeader = "", _    ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0000, _ ; Set additional options through flag values
  $aFeatures = "" )   ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView
  ; 0x0020 => No grid lines in ListView

  ; $aFeatures parameter
  ; Features available in _ArrayDisplayEx:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ;   Debugging features:                                        ; Include file:
  ;     "DataTypes"   => Data types info                         ; <Display function>_DataTypesInfo.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => No array variable passed to function
  ; 2 => Larger than 2D array passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 6 => Too many GUIs

Usage of additional features is demonstrated in Examples\ArrayDisplayEx\1) Single features\.

How to use _ArrayDisplayEx in your own project is demonstrated in Examples\ArrayDisplayEx\3) Using function\.

Edited by LarsJ
Minor updates
  • Like 2

Share this post


Link to post
Share on other sites
LarsJ

CSVfileDisplay

This is copied from the top of CSVfileDisplay.au3:

; Displays a CSV file in a virtual ListView
Func CSVfileDisplay( _
  $sCSVfile, _         ; Name of CSV file (plain text file)
  $iEncoding = 128, _  ; File encoding, default is UTF-8 with BOM
  $cSeparator = "|", _ ; Field separator character, default is "|"
  $iMax_Fields = 0, _  ; Max. number of data fields in a line in the CSV file
  $sTitle = "", _      ; GUI title bar text, default title is set to "CSVfileDisplay"
  $sHeader = "", _     ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0100, _  ; Set additional options through flag values
  $aFeatures = "" )    ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView
  ; 0x0020 => No grid lines in ListView
  ; 0x0100 => Calculate $iMax_Fields from first line
  ; 0x0200 => Calculate $iMax_Fields from first 10 lines
  ; 0x0400 => Calculate $iMax_Fields from first 100 lines

  ; $aFeatures parameter
  ; Features available in CSVfileDisplay:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => CSV file does not exist
  ; 2 => Invalid CSV file passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 6 => Too many GUIs

The first four function parameters are related to the data source, the CSV file. $sCSVfile is the file name, $iEncoding is the file encoding, $sSeparator is the field separator character and $iMax_Fields is the maximum number of data fields in a line in the CSV file.

The default value of $iMax_Fields is zero which means that the number of data fields is calculated automatically by inspecting the lines in top of the CSV file. How many lines that are inspected is decided through three flag values 0x0100, 0x0200, and 0x0400 which means that 1, 10 and 100 lines in top of the file are inspected.

Because this inspection takes time it's limited to 100 lines. If the lines from number 101 to the end of file contains more fields than the first 100 lines you have to set $iMax_Fields manually.

If the lines in the file contains 10 fields but you only want to display 7 fields you can set $iMax_Fields = 7.

CSVfileDisplay main code
The CSV file is loaded into a 1D array, $aCSVfile, with FileReadToArray(). Through $iFrom and $iTo parameters of the caching mechanism in the virtual listview a range of rows in $aCSVfile are split into single fields by the separator character and stored in a 2D array, $aDisplay. The visible listview cells can now be filled with data from $aDisplay.

CSVfileDisplay examples
Usage of $iMax_Fields and 0x0100, 0x0200, and 0x0400 flags is demonstrated in Examples\CSVfileDisplay\0) Data source\.

Usage of additional features is demonstrated in Examples\CSVfileDisplay\1) Single features\.

How to use CSVfileDisplay in your own project is demonstrated in Examples\CSVfileDisplay\3) Using function\.

Wrapper function
In most examples, the parameters $iEncoding, $sSeparator and $iMax_Fields are the same. Therefore, it's convenient to use a wrapper function to execute CSVfileDisplay(). Examples\CSVfileDisplay\CSVfileDisplayWrapper.au3 contains the wrapper function CSVfileDisplayWr() that's used in most of the examples:

#include-once
#include "..\..\Functions\CSVfileDisplay\CSVfileDisplay.au3"
#include "..\..\Resources\CSVfileDisplay\Conv2DArray.au3"
#include "..\..\Resources\CSVfileDisplay\Save1DArray.au3"

Func CSVfileDisplayWr( _
  $sCSVfile, _         ; Name of CSV file (plain text file)
  $sTitle = "", _      ; GUI title bar text, default title is set to "CSVfileDisplay"
  $sHeader = "", _     ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0100, _  ; Set additional options through flag values
  $aFeatures = "" )    ; 2D array of feature type/info pairs

  CSVfileDisplay( $sCSVfile, 128, "|", 0, $sTitle, $sHeader, $iFlags, $aFeatures )
EndFunc

Note that CSVfileDisplayWr() is very similar to _ArrayDisplayEx(). CSVfileDisplayWrapper.au3 also includes a couple of UDFs that are used to create CSV files on the fly.

Edited by LarsJ
Minor updates
  • Like 3

Share this post


Link to post
Share on other sites
LarsJ

_SQLite_Display

This is copied from the top of SQLiteDisplay.au3:

; Displays data from a table, view or SELECT statement in a virtual ListView
Func _SQLite_Display( _
  $hDB, _             ; An open database, use -1 to use last opened database
  $sSQL, _            ; SELECT statement to be executed or name of a table or view
  $sTitle = "", _     ; GUI title bar text, default title is set to "SQLiteDisplay"
  $sHeader = "" _     ; ListView header column names, default is "Row|Col0|Col1|Col2|...|ColN"
  , _                 ;     where Col0-ColN are names from SELECT statement
  $iFlags = 0x0000, _ ; Set additional options through flag values
  $aFeatures = "" )   ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView
  ; 0x0020 => No grid lines in ListView

  ; $aFeatures parameter
  ; Features available in _SQLite_Display:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => Invalid database, table, view or SELECT statement passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 6 => Too many GUIs

Main code
The main code in _SQLite_Display is based on sqlite3_get_table. sqlite3_get_table extracts all elements from the data source at once and inserts the elements in a C-array as strings (more precisely as pointers to zero-terminated UTF-8 strings). Because data is displayed in the virtual listview directly from the C-array _SQLite_Display is very fast.

Note that sqlite3_get_table can use a lot of memory for large data sources, because all data elements are stored in the C-array.

NULL-values are shown as "NULL" (without quotation marks) in the listview. Since there is no point in showing BLOB-data in listview cells, BLOB-data should be excluded.

Sorting features
When extracting data from a database, data is usually sorted directly through the SELECT statement used to extract data. Therefore no sorting features ("SortRows", "SortCols", "SortByCols") are supported in _SQLite_Display.

This is a fact at least in this first version. But I'll not exclude support for sorting in a later version. It would be nice to be able to sort data by multiple columns through the "SortByCols" feature that was introduced a couple of weeks ago.

Sorting features (2018-04-21)
All three sorting features ("SortRows", "SortCols", "SortByCols") are supported in this updated version of _SQLite_Display.

Because rows from a table, view or SELECT statement is stored in a C-array, which is a 2D array in the same way as $aArray is a 2D array for _ArrayDisplayEx, it's possible to display the rows for _SQLite_Display in another order exactly in the same way as it's done for _ArrayDisplayEx.

The way to do it is to display the rows through an index. Since the rows are stored in a SQLite database the index should be created through a SELECT statement.

In examples, code sections like this are used to create sorting indexes:

; Sort by dates and times ascending
_SQLite_Exec( -1, "CREATE TABLE IF NOT EXISTS DTIndexAsc AS SELECT Mytable.RowId AS RowIndex FROM MyTable ORDER BY Dates,Times;" )
_SQLite_GetTable( -1, "SELECT RowIndex FROM DTIndexAsc;", $aIndex, $iRows, $iColumns )
Local $aDTIndexAsc[$iRows]
For $i = 0 To $iRows - 1
  $aDTIndexAsc[$i] = $aIndex[$i+2] + 0
Next

; Sort by dates and times descending
_SQLite_Exec( -1, "CREATE TABLE IF NOT EXISTS DTIndexDesc AS SELECT Mytable.RowId AS RowIndex FROM MyTable ORDER BY Dates DESC,Times DESC;" )
_SQLite_GetTable( -1, "SELECT RowIndex FROM DTIndexDesc;", $aIndex, $iRows, $iColumns )
Local $aDTIndexDesc[$iRows]
For $i = 0 To $iRows - 1
  $aDTIndexDesc[$i] = $aIndex[$i+2] + 0
Next

Installing SQLite
Navigate to Precompiled Binaries for Windows and download the three zip-files.

  1. Extract sqlite3.dll from sqlite-dll-win64-x64-3230000.zip and rename it to sqlite3_x64.dll
  2. Extract sqlite3.dll from sqlite-dll-win32-x86-3230000.zip
  3. Extract the 3 files in sqlite-tools-win32-x86-3230000.zip
  4. Copy all 5 files to C:\Windows\System32
  5. Copy sqlite3.dll to C:\Windows\SysWOW64

Create DBs
Run CreateDBs.au3 in Resources\SQLiteDisplay\ to create test databases. The DBs are created from an input array of random data generated with FASudf. FASudf is included in Resources\FAMproj\. The databases contains from 10,000 - 2,000,000 rows and 7 columns. The column data types are integers, strings, integers, floats, dates (integers), times (integers) and row/col (strings). Inserting rows in the database tables is implemented through bulk insertions to improve performance. This is a picture of a running CreateDBs.au3:


daR0q1v.png

The green bar is a progress bar.

Examples
Examples\SQLiteDisplay\1) Simple example.au3
:

#AutoIt3Wrapper_Au3Check_Parameters=-d -w- 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include "..\..\Functions\SQLiteDisplay\SQLiteDisplay.au3"

Opt( "MustDeclareVars", 1 )

Example(   "10000.db" ) ;    10,000 rows, 7 columns
Example(   "50000.db" ) ;    50,000 rows, 7 columns
Example(  "100000.db" ) ;   100,000 rows, 7 columns
Example(  "500000.db" ) ;   500,000 rows, 7 columns
Example( "1000000.db" ) ; 1,000,000 rows, 7 columns
Example( "2000000.db" ) ; 2,000,000 rows, 7 columns

Func Example( $sDB )
  _SQLite_Startup()
  _SQLite_Open( "..\..\Resources\SQLiteDisplay\" & $sDB )

  _SQLite_Display( -1, "MyTable", $sDB )

  _SQLite_Close()
  _SQLite_Shutdown()
EndFunc

Most of the features in the list above are demonstrated in scripts in Examples\SQLiteDisplay\1) Single features\. SQLiteDisplay performance example.

Zip-file
Updated zip-file in bottom of first post.

Edited by LarsJ
Minor updates

Share this post


Link to post
Share on other sites
LarsJ

SafeArrayDisplay

This is copied from the top of SafeArrayDisplay.au3:

; Displays a 1D/2D safearray of variants in a virtual ListView
Func SafeArrayDisplay( _
  $pSafeArray, _      ; Pointer to 1D/2D safearray
  $sTitle = "", _     ; GUI title bar text, default title is set to "SafeArrayDisplay"
  $sHeader = "", _    ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0000, _ ; Set additional options through flag values
  $aFeatures = "" )   ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView
  ; 0x0020 => No grid lines in ListView

  ; $aFeatures parameter
  ; Features available in SafeArrayDisplay:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ;   Debugging features:                                        ; Include file:
  ;     "DataTypes"   => Data types info                         ; <Display function>_DataTypesInfo.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => No valid safearray pointer passed to function
  ;      Non-zero-based safearray passed to function
  ;      Safearray base type not supported
  ; 2 => Larger than 2D array passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 6 => Too many GUIs

SafeArrayDisplay displays a 1D/2D safearray in a virtual ListView. In this version only safearrays of variants are supported (base type $VT_VARIANT).

A safearray of variants is equivalent to an AutoIt array. The only difference between SafeArrayDisplay in this version and _ArrayDisplayEx is that the former takes a safearray as input parameter while the latter takes an AutoIt array as input parameter.

If an AutoIt array is converted to a safearray and the array and safearray are displayed side by side they'll look exactly alike.

Note that in this version, not all functions are implemented in SafeArrayDisplay_ColumnColors.au3, SafeArrayDisplay_ColumnFormats.au3 and SafeArrayDisplay_FormatsColors.au3. The code in these files must be combined with the code in SafeArrayDisplay_SortFuncs.au3.

Purpose
The purpose of SafeArrayDisplay is primarily to support safearrays in relation to the last two sections of this post in Fast Array Management Functions UDF, thus being able to achieve much better performance during (especially simple) array manipulations.

Main code
SafeArrayDisplay.au3 is a copy of ArrayDisplayEx.au3 which is then updated to handle safearrays. Apart from name changes (ArrayDisplayEx to SafeArrayDisplay), updates are minimal.

The main difference between SafeArrayDisplay and ArrayDisplayEx is the code in SafeArrayDisplay_<feature>.au3 and ArrayDisplayEx_<feature>.au3. In SafeArrayDisplay_<feature>.au3 there is some additional code to handle safarrays.

There's also a difference in the code in Internal\0)CheckDataSource.au3.

Large arrays
A week ago when _SQLite_Display above was presented, FASudf was simultaneously included under Resources\. FASudf makes it possible to carry out tests with large safearrays. Performance of large safearrays is demonstrated in Examples\SafeArrayDisplay\4) Performance\Performance.au3. Especially the following questions are interesting:

  • How fast is SafeArrayDisplay to show the first page in the listview (from "Display safearray ..." appears in SciTE console)?
  • How well does the listview respond to scroll events when the scroll bar thumb is pulled up and down with the mouse (not too much elasticity between mouse pointer and scroll bar thumb)?
  • How fast does a sorting index replace when you click a sorting field in the listview header?

Examples
Most features in the list above are demonstrated in scripts in Examples\SafeArrayDisplay\1) Single features\. This is BackColor.au3:

#AutoIt3Wrapper_Au3Check_Parameters=-d -w- 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include "..\..\..\Functions\SafeArrayDisplay\SafeArrayDisplay.au3"
#include "..\..\..\Resources\FAMproj\FAMudf\AccArrays\AccArraysUtils.au3"

Opt( "MustDeclareVars", 1 )

Example()

Func Example()
  Local $iRows = 10000, $iCols = 10
  Local $aArray[$iRows][$iCols]

  For $i = 0 To $iRows - 1
    For $j = 0 To $iCols - 1
      $aArray[$i][$j] = $i & "/" & $j
    Next
  Next

  Local $pSafeArray
  AccArrays_ArrayToSafeArray( $aArray, $pSafeArray )
  $aArray = 0

  Local $aFeatures = [ "BackColor", 0xCCFFCC ]
  SafeArrayDisplay( $pSafeArray, "", "", 0, $aFeatures )
EndFunc

Zip-file
Updated zip-file in bottom of first post.

Edited by LarsJ
Minor updates

Share this post


Link to post
Share on other sites
Chimp
17 hours ago, LarsJ said:

... Comments are welcome ....

@LarsJ,   thank you for this nice share!
.... only a little perplexity about the need to edit a separate include file to set features. Anyway i see that if you want you can also set the features locally avoiding to use the "external custom include" as I'm doing in this little test. (save this little test in this path: Examples\ArrayDisplayEx\3) Using function\My Project) 

Spoiler
#AutoIt3Wrapper_Au3Check_Parameters=-d -w- 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include "Display\Display.au3"
#include <date.au3>
#include <array.au3>

Opt("MustDeclareVars", 1)

example()

Func example()
    Local $aMonth = _GenerateMonth() ; generate a little array of current month
    ;
    _ArrayDisplayEx( _
            _ArrayExtract($aMonth, 1, 6, 0, 6), _ ; ............. Array of values to be displayed
            "A calendar", _ ; ................................... Title of the window
            _ArrayToString($aMonth, "|", 0, 0, "", 0, 6), _ ; ... String wit titles of each column
            0x0004, _ ; ......................................... additional options
            CSS_Display() _ ; ................................... 2D array of feature type/info pairs
            )
EndFunc   ;==>example

; #FUNCTION# ====================================================================================================================
; Author ........: Chimp
; Modified.......:
; ===============================================================================================================================
Func _GenerateMonth($iYear = @YEAR, $iMonth = @MON, $ISO = True)
    ; this function creates a month calendar into an 7x7 array
    Local $aMonth[7][7], $iDOW
    Local $iFirstDOW = $ISO ? _DateToDayOfWeekISO($iYear, $iMonth, 1) : _DateToDayOfWeek($iYear, $iMonth, 1)
    For $iDay = 1 To 7
        $aMonth[0][$iDay - 1] = $ISO ? _DateDayOfWeek(Mod($iDay, 7) + $ISO, $DMW_LOCALE_SHORTNAME) : _DateDayOfWeek($iDay, $DMW_LOCALE_SHORTNAME)
    Next
    For $iDay = 1 To _DateDaysInMonth($iYear, $iMonth)
        $iDOW = $ISO ? _DateToDayOfWeekISO($iYear, $iMonth, $iDay) : _DateToDayOfWeek($iYear, $iMonth, $iDay)
        $aMonth[Int(($iFirstDOW + $iDay - 2) / 7) + 1][$iDOW - 1] = $iDay
    Next
    Return $aMonth
EndFunc   ;==>_GenerateMonth

Func CSS_Display()

    ; Column colors:
    Local $aColColors = [[6, 0xFFFFCC],[7, 0xFF4500]] ; set colors of columns

    ; load the carrier array with features
    Local $aFeatures = [["ColColors", $aColColors]] ; fill the "aFeatures" array
    Return $aFeatures ; return the carrier with all features
EndFunc   ;==>CSS_Display

 

...from a better reading I see that embedding features in an include is just a further option available allowed by your udf (sorry for my misunderstanding)


If allowed just two little suggestions demands:
would be nice to be able to "hide" the first column where are shown the row numbers by setting a flag ( $iFlags)
would be also nice , as an option, to be able to use this data display as an embeddable GUI control instead of as well as a "standalone window"

Thanks againn for sharing!

Edited by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
LarsJ

Chimp, The functions are mainly intended for large amounts of data with a large number of rows, where it's reasonable to use as much code and virtual listviews. I've added a new paragraph to top of first post to stress this.

In a virtual listview with many rows and maybe also custom draw code to add column colors you'll get a huge amount of WM_NOTIFY messages. You can get 1000 messages per second. This means that you have to be very careful about the performance of the code. You cannot add a lot of Case or If statements to implement more functionality.

Because the purpose of these functions is large amounts of data with 10,000 rows or more where row numbers are important, and because of performance considerations, I don't think I'll add an option to remove the row number column.

The performance considerations is also the main reason why I've omitted some of the original features.

Embeddable GUI control
A main purpose of these functions is testing and debugging large arrays and CSV files in an easy and fast way, and also to display large arrays and CSV files for end users in an easy and fast way. Using the functions as an embeddable GUI control seems to be more cumbersome and time consuming. Sorry, but I don't think I'll add the code.

Regards Lars.

  • Like 1

Share this post


Link to post
Share on other sites
LarsJ

Sort rows by multiple columns ("SortByCols") feature

The existing code already contains functionality to sort the rows in the data source through an index. It's the "SortRows" feature. This feature supports only a single sorting index that shows the rows in a particular order when the rows are displayed in the listview.

The new feature makes it possible to replace this sorting index on the fly when a column header item is clicked. The new feature supports the following functionality:

  • Assign a particular sorting index to a particular column
  • Set default sorting column when the listview is displayed
  • Decide if a column can be sorted ascending/descending or both
  • Decide if a column initially should be sorted asc or desc
  • Use 2 different indexes for ascending/descending sorting
  • 3 ways to mark a column (in header) as target for sorting
    • With small arrows as used in Windows/File Explorer
    • By using header item background colors
    • By using header item texts

What's the purpose of using 2 different indexes for ascending/descending sorting? Assume that the rows in a data source are sorted by a date column and also by a name column in case of duplicate dates. Initially in ascending order for both dates and names. If in descending order you want only the dates in descending order but the names still in ascending order, you need 2 different sorting indexes. It's not enough just to use the first index and show the rows in reverse order.

Note that the ability to use a sorting index is supported by the "SortRows" feature. The "SortByCols" feature makes it possible to assign a sorting index to a column and click the header item. The ability to use multiple columns to handle duplicates is provided by the sorting function. In the example above the 2 sorting indexes should both be assigned to the date column which is the primary sorting column.

Examples
All "SortByCols" functionality is demonstrated in examples in "Examples\ArrayDisplayEx\1) Single features\SortByCols\" and "Examples\CSVfileDisplay\1) Single features\SortByCols". These are the same examples.

The easiest examples are those with "colors" in the name because the meaning of the colors is simple and clear.

Updates
Besides the "SortByCols" feature there are some few minor updates.

Zip-file
Documentation in HelpFiles\DisplayFeatures.txt. Code in updated zip-file in first post.

Share this post


Link to post
Share on other sites
LarsJ

Chimp, I'm working on embeddable GUI control versions of these functions. Hopefully I'll have some code ready in a month or two.

  • Like 1
  • Haha 1

Share this post


Link to post
Share on other sites
Chimp
Posted (edited)

... good news,  :) I stay tuned

Edited by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
LarsJ

Share this post


Link to post
Share on other sites
LarsJ

Documentation for SafeArrayDisplay added to post 5 above. Code for SafeArrayDisplay added to zip-file in bottom of first post.

Thanks for all the feedback. Always nice with some feedback.

  • Like 1
  • Thanks 1

Share this post


Link to post
Share on other sites
LarsJ
Posted (edited)

_ArrayDisplayCtrl

See "Embedded controls" section in top of first post for general information about embedded GUI controls.

This is copied from the bottom of ArrayDisplayEx.au3:

; Displays a 1D or 2D array in a virtual ListView as an embedded GUI control
Func _ArrayDisplayCtrl( $hUserGui, $x, $y, $w, $h, _
  $aArray, _          ; 1D/2D array eg. StringSplit("Mo,Tu,We,Th,Fr,Sa,Su", ",")
  $sTitle = "", _     ; GUI title bar text, default title is set to "ArrayDisplayEx" ; <<<< Not used >>>>
  $sHeader = "", _    ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0000, _ ; Set additional options through flag values
  $aFeatures = "" )   ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView ; <<<< Not used >>>>
  ; 0x0020 => No grid lines in ListView
  ; 0x0040 => No bottom controls

  ; $aFeatures parameter
  ; Features available in _ArrayDisplayCtrl:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ;   Debugging features:                                        ; Include file:
  ;     "DataTypes"   => Data types info                         ; <Display function>_DataTypesInfo.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => No array variable passed to function
  ; 2 => Larger than 2D array passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 5 => ControlId error
  ; 6 => Too many GUIs

Note the new flag 0x0040 to remove the bottom controls in the embedded control.

Usage of the embedded control is demonstrated in Examples\ArrayDisplayCtrl\.

Updated zip-file in bottom of first post.

Edited by LarsJ
Minor updates
  • Like 2

Share this post


Link to post
Share on other sites
LarsJ
Posted (edited)

CSVfileDisplayCtrl

See "Embedded controls" section in top of first post for general information about embedded GUI controls.

This is copied from the bottom of CSVfileDisplay.au3:

; Displays a CSV file in a virtual ListView as an embedded GUI control
Func CSVfileDisplayCtrl( $hUserGui, $x, $y, $w, $h, _
  $sCSVfile, _         ; Name of CSV file (plain text file)
  $iEncoding = 128, _  ; File encoding, default is UTF-8 with BOM
  $cSeparator = "|", _ ; Field separator character, default is "|"
  $iMax_Fields = 0, _  ; Max. number of data fields in a line in the CSV file
  $sTitle = "", _      ; GUI title bar text, default title is set to "CSVfileDisplay" ; <<<< Not used >>>>
  $sHeader = "", _     ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0100, _  ; Set additional options through flag values
  $aFeatures = "" )    ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView ; <<<< Not used >>>>
  ; 0x0020 => No grid lines in ListView
  ; 0x0040 => No bottom controls
  ; 0x0100 => Calculate $iMax_Fields from first line
  ; 0x0200 => Calculate $iMax_Fields from first 10 lines
  ; 0x0400 => Calculate $iMax_Fields from first 100 lines

  ; $aFeatures parameter
  ; Features available in CSVfileDisplayCtrl:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => CSV file does not exist
  ; 2 => Invalid CSV file passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 5 => ControlId error
  ; 6 => Too many GUIs

Note the new flag 0x0040 to remove the bottom controls in the embedded control.

Usage of the embedded control is demonstrated in Examples\CSVfileDisplayCtrl\.

Updated zip-file in bottom of first post.

Edited by LarsJ
Minor updates

Share this post


Link to post
Share on other sites
LarsJ
Posted (edited)

_SQLite_DisplayCtrl

See "Embedded controls" section in top of first post for general information about embedded GUI controls.

This is copied from the bottom of SQLiteDisplay.au3:

; Displays data from a table, view or SELECT statement in a virtual ListView as an embedded GUI control
Func _SQLite_DisplayCtrl( $hUserGui, $x, $y, $w, $h, _
  $hDB, _             ; An open database, use -1 to use last opened database
  $sSQL, _            ; SELECT statement to be executed or name of a table or view
  $sTitle = "", _     ; GUI title bar text, default title is set to "SQLiteDisplay" ; <<<< Not used >>>>
  $sHeader = "" _     ; ListView header column names, default is "Row|Col0|Col1|Col2|...|ColN"
  , _                 ;     where Col0-ColN are names from SELECT statement
  $iFlags = 0x0000, _ ; Set additional options through flag values
  $aFeatures = "" )   ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView ; <<<< Not used >>>>
  ; 0x0020 => No grid lines in ListView
  ; 0x0040 => No bottom controls

  ; $aFeatures parameter
  ; Features available in _SQLite_DisplayCtrl:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => Invalid database, table, view or SELECT statement passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 5 => ControlId error
  ; 6 => Too many GUIs

Note the new flag 0x0040 to remove the bottom controls in the embedded control.

Usage of the embedded control is demonstrated in Examples\SQLiteDisplayCtrl\.

Updated zip-file in bottom of first post.

Edited by LarsJ
Minor updates

Share this post


Link to post
Share on other sites
LarsJ
Posted (edited)

SafeArrayDisplayCtrl

See "Embedded controls" section in top of first post for general information about embedded GUI controls.

This is copied from the bottom of SafeArrayDisplay.au3:

; Displays a 1D/2D safearray of variants in a virtual ListView as an embedded GUI control
Func SafeArrayDisplayCtrl( $hUserGui, $x, $y, $w, $h, _
  $pSafeArray, _      ; Pointer to 1D/2D safearray
  $sTitle = "", _     ; GUI title bar text, default title is set to "SafeArrayDisplay" ; <<<< Not used >>>>
  $sHeader = "", _    ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0000, _ ; Set additional options through flag values
  $aFeatures = "" )   ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView ; <<<< Not used >>>>
  ; 0x0020 => No grid lines in ListView
  ; 0x0040 => No bottom controls

  ; $aFeatures parameter
  ; Features available in SafeArrayDisplayCtrl:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ;   Debugging features:                                        ; Include file:
  ;     "DataTypes"   => Data types info                         ; <Display function>_DataTypesInfo.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => No valid safearray pointer passed to function
  ;      Non-zero-based safearray passed to function
  ;      Safearray base type not supported
  ; 2 => Larger than 2D array passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 5 => ControlId error
  ; 6 => Too many GUIs

Note the new flag 0x0040 to remove the bottom controls in the embedded control.

Usage of the embedded control is demonstrated in Examples\SafeArrayDisplayCtrl\.

Updated zip-file in bottom of first post.

Edited by LarsJ
Minor updates

Share this post


Link to post
Share on other sites
LarsJ
Posted (edited)

Documentation for CSVfileDisplayCtrl, _SQLite_DisplayCtrl and SafeArrayDisplayCtrl added to the three posts above. Code added to zip-file in bottom of first post.


New group of examples (Mixed functions) added to zip-file in bottom of first post.


"Sorting features (2018-04-21)" section added to _SQLite_Display post (post 4).


The following text is added to first post in the Embedded Controls section below the two code boxes regarding message handling:

As can be seen from the two Case statements, it's only tested if a message corresponds to a control in the range from the first controlId to the last controlId. A prerequisite for this to work is that the controlIds is an uninterrupted continuous sequence of integers.

This is tested at the bottom of Common\4)CreateAutoItGUICtrl.au3. If the requirement is not met, @error is set to 5. The issue is demonstrated in an example: "a) Demonstration of @error = 5.au3".

Edited by LarsJ
@error = 4 --> 5

Share this post


Link to post
Share on other sites
LarsJ

A new Cleanup and release memory paragraph is added to Embedded controls section in top of first post.

 

SQLiteDisplay performance example
Examples\SQLiteDisplay\4) Performance\Performance.au3
is a SQLiteDisplay performance example. It runs the same code as Examples\SQLiteDisplay\1) Single features\SortByCols.au3 but for a varying number of rows. FSMudf is included (in Resources\) to be able to extract sorting indexes from the databases as fast as possible.

The sorting indexes are created the first time Performance.au3 is run. This means that the first time takes a little longer. Next time is faster because the sorting indexes already exist and only have to be extracted from the databases.

The following questions are interesting:

  • How fast are the pure SQLite sortings that creates the sorting indexes in the databases?
  • How fast are the sorting indexes extracted from the databases as native AutoIt 1D arrays?
  • How fast is _SQLite_Display to show the first page in the listview (from "Display table ..." appears in SciTE console)? It's about the same time as is required to extract the data source as a C-array.
  • How well does the listview respond to scroll events when the scroll bar thumb is pulled up and down with the mouse (not too much elasticity between mouse pointer and scroll bar thumb)?
  • How fast does a sorting index replace when you click a sorting field in the listview header?

Share this post


Link to post
Share on other sites
LarsJ

_ArrayDisplayMult

See "Multiple GUIs" section in top of first post for general information about multiple concurrent and responsive GUIs.

This is copied from the middle of ArrayDisplayEx.au3:

; Displays a 1D or 2D array in a virtual ListView, support for multiple GUIs
Func _ArrayDisplayMult( _
  $aArray, _          ; 1D/2D array eg. StringSplit("Mo,Tu,We,Th,Fr,Sa,Su", ",")
  $sTitle = "", _     ; GUI title bar text, default title is set to "ArrayDisplayMult"
  $sHeader = "", _    ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0000, _ ; Set additional options through flag values
  $aFeatures = "" )   ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView
  ; 0x0020 => No grid lines in ListView

  ; $aFeatures parameter
  ; Features available in _ArrayDisplayMult:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ;   Debugging features:                                        ; Include file:
  ;     "DataTypes"   => Data types info                         ; <Display function>_DataTypesInfo.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => No array variable passed to function
  ; 2 => Larger than 2D array passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 6 => Too many GUIs

Usage of _ArrayDisplayMult() is demonstrated in Examples\ArrayDisplayMult\.

Updated zip-file in bottom of first post.

Share this post


Link to post
Share on other sites
LarsJ

CSVfileDisplayMult

See "Multiple GUIs" section in top of first post for general information about multiple concurrent and responsive GUIs.

This is copied from the middle of CSVfileDisplay.au3:

; Displays a CSV file in a virtual ListView, support for multiple GUIs
Func CSVfileDisplayMult( _
  $sCSVfile, _         ; Name of CSV file (plain text file)
  $iEncoding = 128, _  ; File encoding, default is UTF-8 with BOM
  $cSeparator = "|", _ ; Field separator character, default is "|"
  $iMax_Fields = 0, _  ; Max. number of data fields in a line in the CSV file
  $sTitle = "", _      ; GUI title bar text, default title is set to "CSVfileDisplayMult"
  $sHeader = "", _     ; ListView header column names, default is "Col0|Col1|Col2|...|ColN"
  $iFlags = 0x0100, _  ; Set additional options through flag values
  $aFeatures = "" )    ; 2D array of feature type/info pairs

  ; $iFlags values
  ; Add required values together
  ; 0x0000 => Left aligned text
  ; 0x0002 => Right aligned text
  ; 0x0004 => Centered text
  ; 0x0008 => Verbose mode
  ; 0x0010 => Half-height ListView
  ; 0x0020 => No grid lines in ListView
  ; 0x0100 => Calculate $iMax_Fields from first line
  ; 0x0200 => Calculate $iMax_Fields from first 10 lines
  ; 0x0400 => Calculate $iMax_Fields from first 100 lines

  ; $aFeatures parameter
  ; Features available in CSVfileDisplayMult:
  ;
  ; Features implemented through Common\ files (always usable):
  ;     "ColAlign"    => Column alignment
  ;     "ColWidthMin" => Min. column width
  ;     "ColWidthMax" => Max. column width
  ;     "BackColor"   => Listview background color
  ;     "HdrColors"   => Listview header background colors
  ;     "UserFunc"    => User supplied function
  ;
  ; Features implemented through include files
  ;   End user features:                                         ; Include file:
  ;     "ColFormats"  => Column text formats                     ; <Display function>_ColumnFormats.au3
  ;     "ColColors"   => Column background colors                ; <Display function>_ColumnColors.au3
  ;     "SortRows"    => Sort rows in data source by index       ; <Display function>_SortFuncs.au3
  ;     "SortCols"    => Sort columns in data source by index    ; <Display function>_SortFuncs.au3
  ;     "SortByCols"  => Sort rows by multiple columns           ; <Display function>_SortFuncs.au3
  ;
  ; See DisplayFeatures.txt for docu of features

  ; Error codes in @error
  ; 1 => CSV file does not exist
  ; 2 => Invalid CSV file passed to function
  ; 3 => Missing include file
  ; 4 => Unsupported feature
  ; 6 => Too many GUIs

Usage of CSVfileDisplayMult() is demonstrated in Examples\CSVfileDisplayMult\.

Updated zip-file in bottom of first post.

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

×