In this discussion of dialog boxes, I will continue to explore the data control by looking at how we can manipulate the content and retrieve data. As mentioned in Part V, the Data Control and Data View share many of the same functions as well as the same base, the Data Sheet. If you would like to review part 1, it is available here.
Friday, March 08. 2019
LDC #126: Dialog Boxes Part V (continued) — Writing and Reading Information with a Data Control
Our Sample Script
The following is our test program, which allows many of the functions discussed here to be explored:
void dc_show_stats ();
int dc_function (string);
string menu[20];
int c_rx;
#beginresource
#define DC_DATA_CONTROL 201
#define DC_STATUS_DISPLAY 202
#define DC_ACTION_DISPLAY 203
#define DC_TESTS 204
DataControlDlg DIALOGEX 0, 0, 400, 150
EXSTYLE WS_EX_DLGMODALFRAME
STYLE DS_MODALFRAME | DS_3DLOOK | WS_POPUP | WS_VISIBLE | WS_CAPTION | WS_SYSMENU
CAPTION "Data Control Example"
FONT 8, "MS Shell Dlg"
{
CONTROL "", DC_DATA_CONTROL, "data_control", LBS_NOTIFY | WS_CHILD | WS_VISIBLE | WS_BORDER | WS_TABSTOP | WS_VSCROLL | WS_HSCROLL, 10, 10, 380, 110, 0
CONTROL "", DC_STATUS_DISPLAY, "static", WS_CHILD | WS_VISIBLE | WS_TABSTOP, 10, 125, 280, 8
CONTROL "", DC_ACTION_DISPLAY, "static", WS_CHILD | WS_VISIBLE | WS_TABSTOP, 10, 135, 280, 8
CONTROL "Tests >>", DC_TESTS, "button", BS_PUSHBUTTON | BS_CENTER | WS_CHILD | WS_VISIBLE | WS_TABSTOP, 350, 130, 45, 12
}
#endresource
int main() {
int ix;
if (GetScriptFolder() == "") {
MessageBox('i', "Script should be saved to provide a path for writing files.");
}
menu[ix++] = "DataControlAddString";
menu[ix++] = "DataControlAddTable";
menu[ix++] = "DataControlDeleteColumn";
menu[ix++] = "DataControlDeleteRow";
menu[ix++] = "DataControlInsertColumn";
menu[ix++] = "DataControlInsertRow";
menu[ix++] = "DataControlInsertString";
menu[ix++] = "DataControlLoadList";
menu[ix++] = "DataControlResetContent";
menu[ix++] = "DataControlResetContent All";
menu[ix++] = "DataControlSetCellText";
menu[ix++] = "separator";
menu[ix++] = "DataControlGetCellText";
menu[ix++] = "DataControlGetString";
menu[ix++] = "DataControlGetData";
menu[ix++] = "DataControlGetData Text";
menu[ix++] = "DataControlGetData CSV";
menu[ix++] = "DataControlGetData HTML";
DialogBox("DataControlDlg", "dc_");
return ERROR_NONE;
}
int dc_load() {
DataControlSetGridMode(DC_DATA_CONTROL);
dc_show_stats();
return ERROR_NONE;
}
void dc_action(int id, int ac) {
int rect[10];
int rc;
if (id == DC_TESTS) {
EditSetText(DC_ACTION_DISPLAY, "");
rect = ControlGetPosition(id);
rc = MenuTrackAdHocPopup(menu, rect["right"], rect["top"]);
if (rc < 0) { return 0; }
dc_function(menu[rc]);
dc_show_stats();
}
}
////////////////////////////////////////////////
// Dialog Support
void dc_show_stats() {
int pos[2];
int rows, cols;
int s_cx, s_mode;
rows = DataControlGetRowCount(DC_DATA_CONTROL);
cols = DataControlGetColumnCount(DC_DATA_CONTROL);
pos = DataControlGetCaretPosition(DC_DATA_CONTROL);
s_cx = DataControlGetSortColumn(DC_DATA_CONTROL);
s_mode = DataControlGetSortMode(DC_DATA_CONTROL);
EditSetText(DC_STATUS_DISPLAY, "Size: %d x %d Pos: %d:%d Sort cx: %s mode: %d", rows, cols, pos["Row"], pos["Col"], s_cx, s_mode);
}
////////////////////////////////////////////////
int dc_function(string name) { // Test a Function
string table[][8];
string s1, s2;
int pos[2];
int rc;
int s_cx, s_mode;
if (name == "DataControlAddString") {
DataControlAddString(DC_DATA_CONTROL, "-Add 1,Comma,Delimited");
DataControlAddString(DC_DATA_CONTROL, "Add,1,2,3,4,5,6");
DataControlAddString(DC_DATA_CONTROL, "Add,A,B,C,D,E,F");
DataControlAddString(DC_DATA_CONTROL, "Add,Cats,Dogs,Rats,Mice,Birds,Fish");
DataControlAddString(DC_DATA_CONTROL, "-Add 2\tTab\tDelimited");
DataControlAddString(DC_DATA_CONTROL, "Add\t7\t8\t9\t10\t11\t12");
DataControlAddString(DC_DATA_CONTROL, "Add\tG\tH\tI\tJ\tK\tL");
DataControlAddString(DC_DATA_CONTROL, "Add\tCats\tDogs\tRats\tMice\tBirds\tFish");
return ERROR_NONE;
}
if (name == "DataControlAddTable") {
table = EnumerateFolderDetails(GetDesktopFolder());
DataControlAddTable(DC_DATA_CONTROL, table);
DataControlDeleteColumn(DC_DATA_CONTROL, 8); //Modified qword
DataControlDeleteColumn(DC_DATA_CONTROL, 3, 4); //Created, Created qword, Accessed, Accessed qword
DataControlDeleteColumn(DC_DATA_CONTROL, 1); //Attribute bits
return ERROR_NONE;
}
if (name == "DataControlDeleteColumn") {
rc = DataControlDeleteColumn(DC_DATA_CONTROL, 1);
EditSetText(DC_ACTION_DISPLAY, "Delete column 1 returned %08X", rc);
return ERROR_NONE;
}
if (name == "DataControlDeleteRow") {
rc = DataControlDeleteRow(DC_DATA_CONTROL, 1);
EditSetText(DC_ACTION_DISPLAY, "Delete Row 1 returned %08X", rc);
return ERROR_NONE;
}
if (name == "DataControlInsertColumn") {
DataControlInsertColumn(DC_DATA_CONTROL, 0);
EditSetText(DC_ACTION_DISPLAY, "Inserted column before 1");
return ERROR_NONE;
}
if (name == "DataControlInsertRow") {
DataControlInsertRow(DC_DATA_CONTROL, 0);
EditSetText(DC_ACTION_DISPLAY, "Inserted row before 1");
return ERROR_NONE;
}
if (name == "DataControlInsertString") {
DataControlInsertString(DC_DATA_CONTROL, 0, "-Insert,0,Comma,Delimited, at top 0 (1)");
DataControlInsertString(DC_DATA_CONTROL, 0, "-Insert\t0\tTab\tDelimited, at top 0");
DataControlInsertString(DC_DATA_CONTROL, -1, "-Insert,bottom,Comma,Delimited,at end");
DataControlInsertString(DC_DATA_CONTROL, -1, "-Insert\tbottom\tTab\tDelimited, at end");
return ERROR_NONE;
}
if (name == "DataControlLoadList") {
s1 = "-List 1,Comma,Delimited,List\r";
s1 += "Add,1,2,3,4,5,6\r";
s1 += "Add,A,B,C,D,E,F\r";
s1 += "Add,Cats,Dogs,Rats,Mice,Birds,Fish\r"; // end w/ return
DataControlLoadList(DC_DATA_CONTROL, s1, TRUE);
s1 = "-List 2\tTab\tDelimited\r";
s1 += "Add\t7\t8\t9\t10\t11\t12\r";
s1 += "Add\tG\tH\tI\tJ\tK\tL\r";
s1 += "Add\tCats\tDogs\tRats\tMice\tBirds\tFish"; // end no return
DataControlLoadList(DC_DATA_CONTROL, s1);
return ERROR_NONE;
}
if (name == "DataControlSetCellText") {
rc = DataControlSetCellText(DC_DATA_CONTROL, 0, 0, "Cell Set 0,0");
if (IsError(rc)) {
EditSetText(DC_ACTION_DISPLAY, "Error %08X - %s", rc, GetLastErrorMessage());
}
return ERROR_NONE;
}
if (name == "DataControlResetContent") {
DataControlResetContent(DC_DATA_CONTROL);
c_rx = 0;
return ERROR_NONE;
}
if (name == "DataControlResetContent All") {
DataControlResetContent(DC_DATA_CONTROL, TRUE);
c_rx = 0;
return ERROR_NONE;
}
if (name == "DataControlGetCellText") {
s1 = DataControlGetCellText(DC_DATA_CONTROL, c_rx, 2);
EditSetText(DC_ACTION_DISPLAY, "Row %d Col %d : %s", c_rx++, 2, s1);
if (s1 == "") { c_rx = 0; }
return ERROR_NONE;
}
#ifdef DCC_GS_COMMA
if (name == "DataControlGetString") {
s1 = DataControlGetString(DC_DATA_CONTROL, c_rx, DCC_GS_COMMA);
EditSetText(DC_ACTION_DISPLAY, "Row %d : %s", c_rx++, s1);
if (s1 == "") { c_rx = 0; }
return ERROR_NONE;
}
#endif
if (name == "DataControlGetData") {
s1 = DataControlGetData(DC_DATA_CONTROL, FT_XDS_II);
if (IsError(s1)) {
rc = GetLastError(); s1= GetLastErrorMessage();
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Fail %08X %s", rc, s1);
return ERROR_NONE;
}
s2 = GetScriptFolder() + "DataControlGetData 01.xml";
rc = StringToFile(s1, s2);
if (IsError(rc)) {
s1 = GetLastErrorMessage();
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Write Fail %08X %s", rc, s1);
return ERROR_NONE;
}
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Succeeded");
return ERROR_NONE;
}
if (name == "DataControlGetData Text") {
s1 = DataControlGetData(DC_DATA_CONTROL, FT_TEXT);
if (IsError(s1)) {
rc = GetLastError(); s1= GetLastErrorMessage();
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Fail %08X %s", rc, s1);
return ERROR_NONE;
}
s2 = GetScriptFolder() + "DataControlGetData 01.txt";
rc = StringToFile(s1, s2);
if (IsError(rc)) {
s1 = GetLastErrorMessage();
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Write Fail %08X %s", rc, s1);
return ERROR_NONE;
}
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Succeeded");
return ERROR_NONE;
}
if (name == "DataControlGetData CSV") {
s1 = DataControlGetData(DC_DATA_CONTROL);
if (IsError(s1)) {
rc = GetLastError(); s1= GetLastErrorMessage();
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Fail %08X %s", rc, s1);
return ERROR_NONE;
}
s2 = GetScriptFolder() + "DataControlGetData 01.csv";
rc = StringToFile(s1, s2);
if (IsError(rc)) {
s1 = GetLastErrorMessage();
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Write Fail %08X %s", rc, s1);
return ERROR_NONE;
}
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Succeeded");
return ERROR_NONE;
}
if (name == "DataControlGetData HTML") {
s1 = DataControlGetData(DC_DATA_CONTROL, FT_HTML);
if (IsError(s1)) {
rc = GetLastError(); s1= GetLastErrorMessage();
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Fail %08X %s", rc, s1);
return ERROR_NONE;
}
s2 = GetScriptFolder() + "DataControlGetData 01.htm";
rc = StringToFile(s1, s2);
if (IsError(rc)) {
s1 = GetLastErrorMessage();
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Write Fail %08X %s", rc, s1);
return ERROR_NONE;
}
EditSetText(DC_ACTION_DISPLAY, "DataControlGetData Succeeded");
return ERROR_NONE;
}
return ERROR_NONE;
}
When our program is first run, a blank control will appear:
When we run the Test | DataControlAddTable function, we will see:
Which then yields:
The data in this case is from my desktop folder. For you it will be your own list of contents. Incidentally, I see that I have a fair amount of trash in my desktop.
At any rate, for clarity, the gridlines are turned on using the DataControlSetGridMode function to show the cells,
Moving and Adjusting Data
Since the Data Control class is meant to be subset compatible with common list box operations such as add, insert and delete, those functions are naturally part of the SDK. In addition, there are a lot of other functions to support adjusting content. One thing that is different from a list box is that all text content is UTF-8 encoded.
While we already covered functions to add strings to the Data Control, it is worth going over them again briefly. These are the first two functions in our example:
int = DataControlAddString ( int id, string data );
int = DataControlInsertString ( int id, int row, string data );
Both add the new string. The DataControlnsertString function will also return the position at which the row was inserted (or a formatted error code). The data parameter can be tabbed or CSV. Detection of the data mode is performed by scanning the data parameter for tab characters. If none are found, the function then scans for a comma, and, if located, the mode is set to CSV. Each function will automatically add columns to the control as required. For the insert function, setting the row parameter to -1 makes the function operate in the same manner as the DataControlAddString function in that it simply adds the new string to the end of the list. Unlike list box functions, the data is not automatically sorted during the adding action.
An entire table can be added using the DataControlAddTable function. This is much more efficient for large amounts of data.
int = DataControlAddTable ( int id, string data[][] );
The data parameter could be created in a number of ways, such as with the CSVReadTable function. Another way is to load a string of comma- or tab- delimited data is to use the DataControlLoadList function:
int = DataControlLoadList ( int id, string data, [boolean csv_mode] );
csv_mode is an optional boolean value specifying whether to interpret line data as a CSV table. The default value is FALSE. For maximum flexibility, the data string can be formatted as text lines or a comma- or space-delimited list. For example:
Lines — “Apples\r\n\Oranges\r\nBananas”
Comma — “Apples,Oranges,Bananas”
Spaces — “Apples Oranges Bananas”
In line mode, the csv_mode becomes active. If this parameter is not set to TRUE, tabs are interpreted as cell delimiters. When it is set to TRUE, the line is interpreted as CSV. Row (or record) endings are interpreted as \r, \r\n or \n.
In our DataControlAddTable example, the EnumerateFolderDetails function is used to get some data. For clarity, the DataControlDeleteColumn function is used to remove a bunch of date/time columns that don’t add value to our example.
int = DataControlDeleteColumn ( int id, int column, [int count] );
If the count parameter is omitted, a single column is deleted. The opposite wound be to inset a column:
int = DataControlInsertColumn ( int id, int index, [int count] );
In this case, the count parameter refers to the number of columns to insert prior to the zero-based column index parameter. If count is omitted, one column is inserted.
Empty rows can be inserted using the DataControlInsertRow function:
int = DataControlInsertRow ( int id, int index, [int count] );
Again, the count parameter defaults to 1. Likewise, rows can be deleted:
int = DataControlDeleteRow ( int id, int row, [int count] );
Finally, rows can be swapped:
int = DataControlSwapRows ( int id, int a, int b );
All of these functions will not alter the select state unless the select position becomes invalid as a result of the operation.
At a cell level, there are several functions that allow data to be loaded and styled. I will cover cell styling in a later blog.
int = DataControlSetCellText ( int id, int row, int column, string data );
The data will be stored in the cell as it is.
Finally, if all content is to be removed, the DataControlResetContent function can be used:
int = DataControlResetContent ( int id, [boolean labels] );
The labels parameter will flag that the legends should be reset as well. The default for this parameter is FALSE, or to not reset the labels.
Retrieving Data
Depending on the application, you may want to retrieve data from the sheet. You can get cells, rows, or the entire sheet. Our example provides four options to retrieve the entire sheet.
string = DataControlGetData ( int id, [dword type] );
This function returns the entire sheet as a string. The type is a file type code as specified in the SDK with the following types supported: FT_XDS_II, FT_TEXT, FT_HTML, and FT_CSV. The default is CSV. Note that while the CSV text will be UTF-8, a UTF-8 code is note added as a preamble to the string.
The menu examples will write files to the same folder the script is saved. These are:
DataControlGetData 01.csv
DataControlGetData 01.htm
DataControlGetData 01.txt
DataControlGetData 01.xml
Data can also be retrieved for an entire row (note this function is available from GoFiler 4.26a/Legato 1.2b and later):
string = DataControlGetString ( int id, int row, [dword type] );
In our example script, we used this function to retrieve data as CSV. The optional type can be DCC_GS_TABBED, DCC_GS_SPACES or DCC_GS_COMMA. The default is DCC_GS_TABBED. The returned buffer is limited to 1mb.
The raw content of any cell can be retrieved using DataControlGetCellText:
string = DataControlGetCellText ( int id, int row, int column );
The way this function operates is fairly obvious. While we haven’t discussed this topic yet, a cell may contain data that is not directly displayed. For example, if the cell’s type is changed to a checkbox, then the return value will be “1” or “0” depending on the check box state.
A table can also be retrieved using the DataControlGetTable function:
string [][] = DataControlGetTable ( int id, [boolean null] );
The null parameter is an optional boolean value that, when set to TRUE, causes the cells that are spanned over (merged) to be loaded with the text “}null{”. Otherwise, those cells are returned as an empty string. The default value for this parameter is FALSE. We have not covered spanned cells yet, but if you think about it, a two-dimensional string table does not have provisions to indicate that a position has been spanned over. An empty string could be an empty cell or part of a merged area.
Conclusion
This blog expanded the basics of loading and retrieving data from a Data Control. In my next blog, I will explain how to style data and format cells. Have fun with the example.
 Scott Theis is the President of Novaworks and the principal developer of the Legato scripting language. He has extensive expertise with EDGAR, HTML, XBRL, and other programming languages. |
Additional Resources
Legato Script Developers LinkedIn Group
Primer: An Introduction to Legato
Quicksearch
Categories
Calendar
Feeds
