XLSX-Workbook-Class icon indicating copy to clipboard operation
XLSX-Workbook-Class copied to clipboard

Published 20 hours ago • verified publisher icon ggreen86

Asking-about-filling-a-template

Open jhernancanom opened this issue 3 years ago • 2 comments

Hi, Greg. I have tried to "update" my script for copying a DBF to a XLSx-template.

I "developed" a script based in r31, and I made several modifications. Now I have tried to update it to r32, but I continue getting a wrong worksheet, wrong in the sense I am going to explain...

I send you:

  • Main.prg (simplified with respect to my scenario), and CopyToHoja1_CxS.prg that I am using to "fill" the XLSx-template with DBF-data.
  • My TEMPLATE.xlsx worksheet.
  • My VENTAS_ENERO_2021.DBF data file.
  • The VENTAS_ENERO_2021.xlsx worksheet I get when running my script.

In general my objective is:

  • Copy file TEMPLATE.xlsx to VENTAS_ENERO_2021.xlsx
  • With CopyToHoja1.prg, take VENTAS_ENERO_2021.xlsx and fill the cells with the data in VENTAS_ENERO_2021.DBF.

What I am getting is:

  • The formulas in the first row (columns SUBTOTAL, Descuentos, and TOTAL) are respected. The rest of the rows does not respect the formulas and the cells are filled with the values in the template.
  • The formula in the first col of the row 15 is respected. The rest of the columns does not respect the formulas and the cells are filled with the original-values in the template.
  • The VFP-"inputmask" (XLS-FormatNumber) in the cells are not respected.
  • In r31 the hidden attribute of the H-column is not respected. In r32 the hidden attribute of the H-column do is respected.
  • The word "Período:" in cell E4 dissapears.

I consider the script I got (based in R31) can be very complex for you to adjust.

But my goal is asking you: What could be the best "instructions"/commands for me to get the result: take a template and fill with data..... (and saving of course!!!)?

Thanks. Asking-about-filling-a-template.zip image

jhernancanom avatar Mar 07 '21 22:03 jhernancanom

Hi, Greg.

I found some alternative for solving the issues this way:

  • I replace r31 VCX/VCT with r32 VCX/VCT (because at least r32 respect the hidden col).

  • In order to the "final grid" get the cells with format number: After filling the template with the new data, I make a cycle to touch the format of every cell.

  • In order to the "final grid" get the cells with formulas: After filling the template with the new data, I make a cycle to touch the formula of every cell that lost its formula.

  • In order to the "final worksheet" get the label "Período:": In the original template, I had to fill the cell copying from another cell and pasting into it, and rewritten the word "Período:". I consider that this cell could have some type of format that WorkbookXLSX does not recognize yet.

  • I see that some colors change: XLS standard yellow does not change, but a light red yes.

Thanks.

jhernancanom avatar Mar 09 '21 04:03 jhernancanom

Hi, Greg. I have made some analysis and now I can share what I got: I have wrote some commands for filling a XLSx-template with DBF-data in R33-beta8 (current), and the troubleshhoting I found.

Attached:

  1. A description in DOC;
  2. A zip with VFP-script and XLSx-template.

Thanks. Mensaje-About filling a template in R33-beta8.docx About filling a template in R33-beta8.zip

jhernancanom avatar May 27 '21 07:05 jhernancanom