How can i automatically increase the cell index in Excel ?

[~]

Hi all, Can U suggest me a solution for this trouble?

I am using LabWindows/CVI 7.1 and trying to store simple database in Excel by excelreport.fp.

Now I can save  a string such as " 01030604F2" ( Modbus ASCII  frame message) in to one cell in Excell . But trouble is that , next time i try to save ; it only can save into same position , same cell index , e.g B2 . So i need to increase the index each time i run my CVI application but  I don't know how  ? 

Following  is my code :

char fileName[500];
unsigned char a[10];
switch (event)
        {
        case EVENT_COMMIT:
        ExcelRpt_ApplicationNew (VTRUE, &ExcelHandle);
        GetProjectDir (fileName);
        strcat(fileName, "\\book1.xls");
        ExcelRpt_WorkbookOpen (ExcelHandle, fileName, &ExcelWorkbookHandle);
        ExcelRpt_GetWorksheetFromName (ExcelWorkbookHandle, "Sheet1", &ExcelWorksheetHandle);
        GetCtrlVal (panelHandle, PANEL_ASCII, a);
        ExcelRpt_SetCellValue (ExcelWorksheetHandle, "B2", ExRConst_dataString, a);
        break;
        }
return 0;

I really appreciate all replies ...
Thank U in advance..

[RobertoBozzolo]

Never tried it but... couldn't you format an appropriate string and pass it as the cell address? Something like this:

}

        (...)

        GetCtrlVal (panelHandle, PANEL_ASCII, a);

        sprintf (string, "B%d", index);
        ExcelRpt_SetCellValue (ExcelWorksheetHandle, string, ExRConst_dataString, a);

        index++;

}

[~]

Thanks Roberto,

U gave me 50%  to deal with that trouble.

Now i got this idea :

+ Each time i run my application,i need to get the current value of cell address .Then i will save it to "index" and do index+=1;

e.g last time i save the value in A2 cell, so the next time i run , i will got the value A3 .

Yeah it sounds great but until now i can not find anything likes GetCellAddress ? Can U show me this ? Thank You 🙂 .

P/s: I think i can't find it in ExcellReport because i tried somes but didn't work so beginning to find in MSExcel 9.0 Object library ... hope can find it as soon as possible...

[ebalci]

You can write the cell address you last used to a file and read it and increment it before you write the next.
Within an execution you do not need to do that of course. Just use a variable and then while exiting save the variable value to a file.

or

Read the cell values until you come accross an empty cell and write in that cell.
Maybe you may find the ExcelRpt_Find function useful also.

Hope this helps.

Message Edited by ebalci on 06-04-2007 08:50 AM

[RobertoBozzolo]

Another idea is to save the index of the last cell used directly in the excel file, e.g. in the first cell of the column. WHen you reopen it to write new data firstly read that cell and then increment your index for all data to save, write the last index used and close the file.

[~]

Thank you both of you,
It's help me so much :), really !

Now i can handle the process of writing string into Excel and i am trying to read from it and getting stuck !
My Excel database includes a column written the system date , e.g the A column . I am trying to read this column and then display at the interface by Tree !

My code is below :

char terminal[10];    // the last cell used
char value[10];         // the value of cell
char null[2];              // NULL
int total;                       // number of cells used
int index=2;                // initative value  of cell address : "A2"

null[0]='\0';
ExcelRpt_ApplicationNew (VTRUE, &ExcelHandle);
GetProjectDir (fileName);
strcat(fileName, "\\book1.xls");
ExcelRpt_WorkbookOpen (ExcelHandle, fileName, &ExcelWorkbookHandle);
ExcelRpt_GetWorksheetFromName (ExcelWorkbookHandle, "Sheet1", &ExcelWorksheetHandle);
            
        
ExcelRpt_Find (ExcelWorksheetHandle, ExRConst_dataString, null, "A1",
               ExRConst_Values, ExRConst_Whole, ExRConst_ByColumns, ExRConst_Next, 0, 0, terminal); 
           
// printf("%s",terminal);  use to check error and it displays : $A$11 _ i used from A1 to A10 storing data _ it seems to be right ?


ExcelRpt_RangeSelect (ExcelWorksheetHandle, "A2:terminal");                    // i think the argument "A2:terminal" is the trouble.
                                                                                                                           //It doesnt work  from here!!!
                                                                                                                           // It can not select the range   

ExcelRpt_GetNumCellsInRange (ExcelWorksheetHandle, "A2:terminal", &total);          // counting for the number of cells used in A column
                                                                                                                                           // used for loop
// printf("%d",total); it shows  a very huge number ,nearly a million   WHY ????????????

for (index;index<total;index++ )
 {
  sprintf(string,"A%d",index);                                                                                                
  ExcelRpt_GetCellValue (ExcelWorksheetHandle, string, ExRConst_dataString, value);
  InsertTreeItem (panelHandle, PANEL_TREE, VAL_SIBLING,0 , VAL_NEXT, value, "", 0,0 );
 }

/////////////////////////////////////////////////////////

I tried to check the value and datatype of terminal but i think it's ok and i used this method to write string to Excel (as suggestion from ebalci )

I really dont know why ??? 😞

Thank U  for reading 😞

[ebalci]

Hi ~,

I think my suggestion for using ExcelRpt_Find mislead you..

I suggested it in case you have a fixed pattern for the terminal commands and you need to find them in the worksheet.
I did not mean that you have to use it.

Anyway, in the ExcelRpt_Find function, you could enter an empty string like "" , no need to assign a variable.

Actually I suggest to change the name of that string (you are using null) because NULL (uppercase) has a very specific meaning and it might get confused.

Besides, the terminal string has length 10. This might be too small. It says in the function help to assign MAX_FOUND_CELLRANGE_LENGTH as the string size.

ExcelRpt_RangeSelect (ExcelWorksheetHandle, "A2:terminal");

This line is also problematic. You cannot use a variable name in a string (within quotation marks) and expect the compiler to parse it as a variable. You should use sprintf or Fmt to construct the cell range. Example:

sprintf(cellRange, "A2:%s", terminal);

ExcelRpt_RangeSelect (ExcelWorksheetHandle, cellRange);

Note again that you can handle your initial problem by using the saving the last cell index to a text file or the Excel file itself, as suggested by Roberto.

[~]

Hi, 🙂

Yeah, it has run  after i added the sprintf command , i'm very excited but also sad cause i'm really not good at programming in C :(.

Anyways, at the first time , i considered both your suggestions and Roberto's one and i tried both . I realised that Roberto's solution is easier and faster (not need to find every cell in column) but at that time , i thought i need urs because i want to find a specific day  in a database so I chose ur way to solve my problem :). Now after some new " discoveries" , i changed my mind and use Roberto's suggestion .

Now i got a new trouble!

I has just realised that Excel can display both date and time in one cell . However, when i use TimeStr() to write in one cell and change the format of this cell by right click on it to display date (Right Click_Format Cells_Number Tab_Time_3/14/01 1:30PM).

It displays like this  1/0/00 10:15PM  ? Is that solely an Excel's problem or also CVI ? I suppose it is Excel's one ?

So i found another way to display both date&time :

char moment[100];
strcpy(moment,DateStr());
strcat(moment,TimeStr());
ExcelRpt_SetCellValue (ExcelWorksheetHandle, address, ExRConst_dataString,moment );

How about it ?

Thank U 🙂

[~]

Hi 🙂

Again and again troubles 🙂

Hic Now I  can not save properly . When i used one of the following code to save a workbook , it always requires me to save as , and it displays an information showing that this Excel file is a "read-only" file ???

Excel_WorkbookSave (ExcelWorkbookHandle, NULL);
or
Excel_AppSave (ExcelHandle, NULL, CA_DEFAULT_VAL);
or
Excel_AppSaveWorkspace (ExcelHandle, NULL, CA_DEFAULT_VAL);

In case i run with out any command to save, i could save this file 2 times and then it could not be saved again.

I attached my folder and hope that someone can help me 🙂

P/s: I have to save in "K2" cell the value of 2 at the first call because it's a initative value .
      And some other functions still do not work well 🙂

[~]

Sorry, i cannot see my attached file in my last reply so i upload it to Esnips ; Thank U again 🙂

http://www.esnips.com/doc/aee7ba8e-4408-4717-afc6-a9237a0c41cb/Excel