How to merge cell via excel buffer

kiyac

I would like to merge cell A1 and cell A2,
where A1 = company name, A2 = null

What I need to add in excel buffer and what coding that I should add in report?

Thanks!

SPost29

I created a function with Local Parameters:

Var Name DataType Subtype Length
No RowNo Integer
No ColumnNo Integer
No CellValue Text 250
No MyBold Boolean
No MyUnderLine Boolean
No MyNumberFormat Text 30
No MyMergeCells Boolean
No MyStartMergeCell Text 30
No MyEndMergeCell Text 30
No MyAlignment Option


This function creates the merge cell entry in the excel buffer
EnterMergeCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];MyBold : Boolean;MyUnderLine : Boolean;etc.)

INIT;
VALIDATE("Row No.",RowNo);
VALIDATE("Column No.",ColumnNo);
"Cell Value as Text" := CellValue;
Formula := '';
Bold := MyBold;
Underline := MyUnderLine;
NumberFormat := MyNumberFormat;
"Merge Cells" := MyMergeCells;
"Start Merge At Cell" := MyStartMergeCell;
"End Merge at Cell" := MyEndMergeCell;
Alignment :=  MyAlignment;
INSERT;

I also created a function for EnterCell with the same pararmeters but not the merge values

In the createsheet function I added in the section


IF FIND('-') THEN BEGIN
REPEAT
...
IF Underline THEN
XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;

right after the above I added this code

 
IF "Merge Cells" THEN
      XlWrkSht.Range("Start Merge At Cell"+':'+"End Merge at Cell").Merge;

In the report I had to figure out how many cells I needed to merge to accomodate my text length.


for example

ExcelBuf.EnterMergeCell(RowNo,1,VendorName,TRUE,FALSE,'',
                        TRUE,'A'+FORMAT(RowNo),'D'+FORMAT(RowNo),1);

Steve

bangswit

do we need add "merge cell" column in excel buffer?
i'm using nav 5 sp 1, and i don't have that column

bangswit

INIT;
VALIDATE("Row No.",RowNo);
VALIDATE("Column No.",ColumnNo);
"Cell Value as Text" := CellValue;
Formula := '';
Bold := MyBold;
Underline := MyUnderLine;
NumberFormat := MyNumberFormat;
"Merge Cells" := MyMergeCells;
"Start Merge At Cell" := MyStartMergeCell;
"End Merge at Cell" := MyEndMergeCell;
Alignment :=  MyAlignment;
INSERT;

are you sure about this code?
do we add it in Table Excel Buffer or report we want to export to excel?
because I have error message here

"Merge Cells" := MyMergeCells;
"Start Merge At Cell" := MyStartMergeCell;
"End Merge at Cell" := MyEndMergeCell;
Alignment :=  MyAlignment;

excell buffer doesn't have "merge cells"

vaprog

I'd do it like this:

PROCEDURE MergeRange(FromRow : Integer;FromCol : Integer;ToRow : Integer;ToCol : Integer);
VAR
  FromRec : Record 370;
  ToRec : Record 370;
BEGIN
  FromRec.VALIDATE("Row No.",FromRow);
  FromRec.VALIDATE("Column No.",FromCol);
  ToRec.VALIDATE("Row No.",ToRow);
  ToRec.VALIDATE("Column No.",ToCol);
  XlWrkSht.Range(FromRec.xlColID + FromRec.xlRowID + ':' + ToRec.xlColID + ToRec.xlRowID).Merge;
END;

Then after you created the sheet and before you save it away or give the user control call that function.