Recent

Author Topic: fpspread copyWorksheet and copyRows  (Read 5590 times)

kjteng

  • Sr. Member
  • ****
  • Posts: 258
fpspread copyWorksheet and copyRows
« on: July 09, 2018, 04:19:08 pm »
What is the correct way to call the above two methods? I tried the following but failed:
a) with wbSrc1.Workbook  do
     CopyWorksheetFrom(GetWorksheetByIndex(1));

b) with wbSrc1.Workbook do
    CopyWorksheetFrom(GetFirstWorksheet);

I get error message: Sheet1 is not a valid worksheet name


c)  with wbSrc1 do
       Worksheet.CopyRow(1, 2, Workbook.GetWorksheetByName('Sheet3'));   

This does not seem to copy any things from sheet3?

wp

  • Hero Member
  • *****
  • Posts: 11833
Re: fpspread copyWorksheet and copyRows
« Reply #1 on: July 09, 2018, 05:07:38 pm »
This is the signature of the CopyWorksheet function
Code: Pascal  [Select][+][-]
  1.     function  CopyWorksheetFrom(AWorksheet: TsWorksheet;
  2.       ReplaceDuplicateName: Boolean = false): TsWorksheet;
You obviously did not use the optional second parameter which - if true - forces the copied worksheet to have a new unique sheetname.

CopyRow copies only a row record, it does not copy any cells visible within that row. The row record contains information on row height and row format (default format assigned to empty cells)

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpspread copyWorksheet and copyRows
« Reply #2 on: July 09, 2018, 05:32:32 pm »
Thanks for the quick reply.
Yes I did not set the ReplaceDuplicateName.  Now it works but I am still dont quite understand what is the use of this parameter.When should we (or when can we) set it to False?

wp

  • Hero Member
  • *****
  • Posts: 11833
Re: fpspread copyWorksheet and copyRows
« Reply #3 on: July 09, 2018, 06:22:52 pm »
When should we (or when can we) set it to False?
When you copy a worksheet from another workbook and you know that there is no name collision.

I just wonder why I selected  the default value to be false, and why there is a default value at all. (...long time gone....)

Please note also that this feature was added by user request, and I guess it's not heavily tested, in particular with regard to the new formula storage and 3d formulas.

kjteng

  • Sr. Member
  • ****
  • Posts: 258
Re: fpspread copyWorksheet and copyRows
« Reply #4 on: July 09, 2018, 07:14:08 pm »
Ok, after few round testing, I have a better understand of this method now. The CopyWorkSheet actually does not copy the source worksheet to current worksheet (Self) but instead it append a new worksheet (duplicate of the source worksheet) to the workbook. As both the source and destination worksheet are in the same workbook, we must set ReplaceDuplicateName to true  i.e. use a different name for the new worksheet appended. 

We can omit the ReplaceDuplicateName (hence default to false)
if the source worksheet is in another workbook and there is no worksheet having the same name in the destination workbook.

Nevertherless, I think the error message  'SheetX is not a valid worksheet name' is misleading. May be 'SheetX already exists' would be better.

wp

  • Hero Member
  • *****
  • Posts: 11833
Re: fpspread copyWorksheet and copyRows
« Reply #5 on: July 09, 2018, 07:52:39 pm »
New revision in svn:
- "Dupliate worksheet" error message of Workbook.AddWorksheet
- Make the optional parameter of Workbook.CopyWorksheetFrom a mandatory one.

totya

  • Hero Member
  • *****
  • Posts: 720
Re: fpspread copyWorksheet and copyRows
« Reply #6 on: July 08, 2020, 02:06:43 pm »
Hi wp master!

I have an heavily formatted worksheet with data (by Excel), and this is the "formatting and data sample" for the other worksheet. This worksheet name is "sample". I need 10-20 copy from this worksheet to the one new workbook, with new own names.

Workbook.CopyWorksheetFrom sounds good, but I need the optional "new worksheet name" or similar parameter, if possible. Thank you :)

wp

  • Hero Member
  • *****
  • Posts: 11833
Re: fpspread copyWorksheet and copyRows
« Reply #7 on: July 08, 2020, 02:50:36 pm »
Can't you just rename the copied worksheet after copying?

Code: Pascal  [Select][+][-]
  1. var
  2.   workbook: TsWorkbook;
  3.   old_worksheet: TsWorksheet;
  4.   new_worksheet: TsWorksheet;
  5.   new_Name: String;
  6. ...
  7.   new_worksheet := workbook.CopyWorksheetFrom(old_worksheet, true);
  8.   if workbook.ValidWorksheetName('something_else') then
  9.     new_worksheet.Name := 'something_else'
  10.   else
  11.     ShowMessage('Invalid worksheet name.');
« Last Edit: July 08, 2020, 02:53:58 pm by wp »

totya

  • Hero Member
  • *****
  • Posts: 720
Re: fpspread copyWorksheet and copyRows
« Reply #8 on: July 08, 2020, 03:12:21 pm »
Thank you master!

But I noticed a problem (I tried office xlsx format only) the copied worksheet column width isn't equal with the original worksheet column width. This is mean the column width information lost.

wp

  • Hero Member
  • *****
  • Posts: 11833
Re: fpspread copyWorksheet and copyRows
« Reply #9 on: July 08, 2020, 04:27:27 pm »
I checked the code, and it really seems to copy everything. And the attached demo verifies this.

totya

  • Hero Member
  • *****
  • Posts: 720
Re: fpspread copyWorksheet and copyRows
« Reply #10 on: July 08, 2020, 08:12:13 pm »
Thank you master!

But I noticed a problem (I tried office xlsx format only) the copied worksheet column width isn't equal with the original worksheet column width. This is mean the column width information lost.

This bug solved via svn 7529, by wp master, thank you! :)

talitaedwiges

  • Newbie
  • Posts: 5
Re: fpspread copyWorksheet and copyRows
« Reply #11 on: November 10, 2022, 02:10:29 pm »
Hello wp
This is the signature of the CopyWorksheet function
Code: Pascal  [Select][+][-]
  1.     function  CopyWorksheetFrom(AWorksheet: TsWorksheet;
  2.       ReplaceDuplicateName: Boolean = false): TsWorksheet;
You obviously did not use the optional second parameter which - if true - forces the copied worksheet to have a new unique sheetname.

CopyRow copies only a row record, it does not copy any cells visible within that row. The row record contains information on row height and row format (default format assigned to empty cells)

! I need to create a new worksheet (file) based on a template worksheet. I'm using CopyWorksheetFrom it worked, but the cell values ​​with formulas and links are not copied.

 

TinyPortal © 2005-2018