AdvGridExelIO questions

Collins_Charles · February 16, 2015, 4:08pm

When saving the file, if the filename exists, it is not overwritten. Other than using fileExist() functions and deleting, can AdvGridExcelIO automatically be set to overwrite?

When exporting, setting gridStartColumn and gridStartRow to 0 pull all grid data. What is the purpose of XLSStartColumn and XLSStartRow? For importing?

Thanks.

brunofierens · February 16, 2015, 5:20pm

You can control with AdvGridExcelIO.Options.ExportOverwrite what should happen when a file already exists.

2) XlsStartColumn / XlsStartRow set from which column & row the exported cells will be set

Collins_Charles · February 16, 2015, 6:20pm

How do GridStartColumn and XlsStartColumn differ then? (or GridStartRow and XlsStartRow)

brunofierens · February 16, 2015, 6:28pm

Cell in TAdvStringGrid versus cell in the XLS file.

Collins_Charles · February 16, 2015, 8:35pm

If I set GridStartColumn, GridStartRow to 0,0 and XLSStartColumn, XLSStartRow to 1,1, the exported spreadsheet starts in column 0,0 not in 1,1. This is why I was asking if one affected import and the other export.

brunofierens · February 16, 2015, 11:35pm

I cannot see this here.

Test code:

begin

advstringgrid1.RandomFill(true);

advgridexcelio1.AdvStringGrid := AdvStringGrid1;

advgridexcelio1.GridStartRow := 0;

advgridexcelio1.GridStartCol := 0;

advgridexcelio1.XlsStartRow := 1;

advgridexcelio1.XlsStartCol := 1;

advgridexcelio1.XLSExport('c:\temp\startcolrowtest.xls');

end;

This sets the grid cell value of cell 0,0 in XLS sheet cell R1C1.

Collins_Charles · February 17, 2015, 9:13pm

Component version 3.13: Options set in component, not code.

GridStartCol 0

GridStartRow 0

XlsStartCol 1

XlsStartRow 1

Row 0, column 0-3 of AdvStringGrid: Fleet / Cab / Customer / RO

Row 1, column 0-3 of AdvStringGrid: 1 / H01436 / / 0150695

Row 2, column 0-3 of AdvStringGrid: 1 / H01664 / 30384 / 0150716

Row 0, column 0-3 of Spreadsheet: Fleet / Cab / Customer / RO

Row 1, column 0-3 of Spreadsheet: 1 / H01436 / / 0150695

Row 2, column 0-3 of Spreadsheet: 1 / H01664 / 30384 / 0150716

I took screenshots of these, the the PNG format does not seem to load into the reply section here.

XlsStartRow/Column has no effect on export data from AdvStringGrid using the AdvExcelIO helper.

brunofierens · February 17, 2015, 9:33pm

XlsStartCol 1

XlsStartRow 1

is the default setting, i.e. it exports in the XLS file from the topleft cell.
I'm not sure what else you expect and/or what else you see?

Collins_Charles · February 18, 2015, 5:24pm

It is confusing that the grid counts from 0 but you are now saying that the XLS counts from 1.

The question originally was does one control export and the other import, or do they both function. It appears you are saying they both function, but that they don't have the same frame of reference. My test shows that on export, 0,0 and 1,1 coordinates on the XLS options produce the exact same result.

brunofierens · February 18, 2015, 5:46pm

There is a difference, TAdvStringGrid has configurable fixed columns / rows while Excel has not, hence these differences.

Collins_Charles · February 18, 2015, 8:28pm

Would you care to elaborate for those of us too dense to understand the differences? That has been the point of my question. So far I see no explanation as to what difference exists between setting the XLS coordinates to 0,0 as to setting them to 1,1. From running a program, changing these coordinates does nothing.

Collins_Charles · February 18, 2015, 9:01pm

Coordinates 0,0 and 1,1 are treated the same in Excel. Setting coordinates to something like 5,10 does do an offset.

brunofierens · February 18, 2015, 9:03pm

Again, as TAdvStringGrid has configurable fixed columns and rows, it's normal cells can start at coordinates 0,0 (when FixedCols = 0, FixedRows = 0) or default at 1,1 (FixedRows = 1, FixedCols = 1) So, the default GridStartCol, GridStartRow is 1,1 as default the grid has one fixed row and one fixed column. If you have no fixed columns and/or rows in the grid, you'd typically set GridStartCol, GridStartRow to 0,0
In Excel, this is different. Excel cannot have zero fixed rows or columns. There is always a fixed row and fixed column and the coordinate of the first accessible (topleft) cell is R1C1. So, default, AdvGridExcelIO.XlsStartCol,AdvGridExcelIO.XlsStartRow is set to 1,1 to direct the first cell for export to this first topleft cell, i.e. cell 1,1. If you want that the data of the grid starts at a different topleft cell in the XLS file, you'd set AdvGridExcelIO.XlsStartCol,AdvGridExcelIO.XlsStartRow to the Excel coordinates of this cell where you want the data to start.
I'm not sure how I can make this more clear.

Collins_Charles · February 19, 2015, 5:11pm

My understanding of GridStartRow and GridStartColumn is that this is the co-ordinate of the first cell to export, not a reference to "fixed" row or column counts. The simple answer would have been:

AdvStringGrid starts counting from position 0, Excel starts counting from position 1. If you put 0 into Excel, it will treat it as 1.

At no point did any of this refer to "fixed row and fixed column" definitions.