Quick way to compare stringgrid cells !

VCL TMS VCL UI Pack
1

Hi All

I have 2 "Excel" spreadsheets with exactly the same formatting and columns but different data.
I need to process "Spreadsheet 1" and "Spreadsheet 2" and
create "Spreadsheet 3" (only the new cells and changed cells)

The data is for products and prices.
The idea is to create a new spreadsheet 3 with exactly the same format etc
from the spreadsheet 1 and spreadsheet 2.

"Spreadsheet 1" is old data
(eg price data from 2014)

"Spreadsheet 2" is new data
(eg price data from 2015)

"Spreadsheet 3" is the data that is different
(cells that have new values / changed values)

e.g. the price cell has changed from 1.20 to 1.25
e.g. there is a new product code

I have to process many thousands of data lines so efficiency is very important.

The data is read from each spreadsheet into separate string grids
"olddata" stringgrid, "newdata" stringgrid.

Is there a quick way to process this efficiently using TAdvStringGrid ?

Many thanks in advance for any help and guidance.

regards

Kamran

2

Sorry, there is currently not a built-in feature that could compare grid contents.
A possible implementation would be by using two grid instances, load the spreadsheet content in each of these grids and then write a loop over all cells of both grids to compare contents, i.e.


for x := 0 to grid1.ColCount - 1 do
  for y := 0 to grid1.RowCount - 1 do
  begin
     if grid1.Cells[x,y] <> grid2.Cells[x,y] then 
       // dfifference found
  end;

3

Hi Bruno,

j don't know wich vcl Kamran's installed, but (for me) a good solution to compare two or more xls(x) files is to work with flexcel.



Regard



Daniele

4

Hi Daniele

I have flexcel vcl.

So how would that be possible with flexcel?

do you have an example ?

thank you

Kamran

5

Hi Kamran,

in very very easy way (is the way j use to compare ... so sorry if this is not a fastest way or best one) consider to use flexcel version 6.



Declare



Xls1        : TXlsFile;    // First file

Xls2        : TXlsFile;   // Second

NewFile   : TXlsFile; // NewOne



In form create event you need to create the worksheet



XLs1:=TXlsFile.Create(true);

XLs2:=TXlsFile.Create(true);

NewFile:=TXlsFile.Create(true);



Now you need to load the sheet (Xls(x) file)

XLs1.Open(FileName1);

XLs2.Open(FileName2);



Now you have the two excell file sotored in the grid with images, charts and so on.

Note that the two files stored in Xls1 and Xls2 are already in own grid so if yours files are the same and change are only in the cells value, with a simple loop you can find and store the difference in the new file.



Compare the cells value



if Xls1.CellValue(Row,Col)<>Xls2.CellValue(Row,Col) do Write your new row in newfile.



When you close the form,

FreeAnNil(Xls1);

FreeAnNil(Xls2);

FreeAnNil(NewFile);



Anyway you can get more help and better info at



http://www.tmssoftware.com/site/blog.asp?post=228



and in the folder

...\FlexCellVCLNT\DEMO



where you can find MainDemo that is great techer.



Hope to be helpful.



Have a good evening,



Daniele





6

Bruno and Daniele

thank you !