Read Excel File lost Merge Cell Data

[Sam.Huang]

Hi all,

I read a excel file got data as below and some different with raw data in Excel sheet which merge data in sheet and read is blank by labview ,Do you have good way to handle this issue, thanks in advance!

[altenbach]

Starting from the top, you could just replace all empty fields with the entry from above. Here's one possibility:

(Of course if the merged cells only occur in one specific columns, further simplifications are possible)

[altenbach]

---

@altenbach wrote:

(Of course if the merged cells only occur in one specific columns, further simplifications are possible)

---

Here's how that could look like:

[Bob_Schor]

What you seem to be hoping for is for LabVIEW to "become Excel" and go into an Excel WorkSheet, where Excel has allowed the User to "merge" a range of cells so that the Worksheet shows an expanded "Cell" with a single value.

What you would need to do to undo this is the following:

1. Search the WorkSheet, looking for a Cell that is a Merged Cell.  Search from left-to-right, top-to-bottom (as I believe the single value in the Merge Range is contained in the Upper Left Cell in the Merge.
2. Once you find a Merged Cell, ask Excel to "turn off the Merge".  If the Merge consists of R Rows and C Columns, there will be 1 Cell with data, and R*C - 1 blank Cells.
3. You now need to read the value that is in the Upper Left (the UL) Cell, and populate it to the rest of the (now blank) Merge area.
   1. One way is to copy the value in the UL Cell to the (C-1) columns in the same Row.  That gets the top row filled with identical values (from the UL Cell).
   2. Next, duplicate the C identical Values in the just-done top row for the R-1 rows below the top row.
   3. The first step creates C-1 values, and the second step adds C*(R-1) values, for a total of R*C - 1 duplicates of the UL Cell, as required.

This requires a fair amount of expertise in VBA.  In principle, you could try to do some of this using LabVIEW and low level calls to Excel VBA.  It will probably, however, be faster and "guaranteed to work" to open the File in Excel, search for Merged Cells, and then manually do the steps I outlined above.  When the Excel WorkSheet was originally created, someone manually replaced identical cells with a single Merged Cell.  For a reasonably-sized WorkSheet, it is easy enough to find the Merges, change the Format to remove the Merge, then copy the single "merged" value into the new blank Columns and Rows.

Now your Excel Spreadsheet is in a "no Merge" format.  If you want to keep it as an Excel file-type (.xls or .xlsx), I recommend using the Report Generation Toolkit.  Otherwise, open the Excel file, save it in CSV format (which removes many of the more difficult Excel "features", and lets you work with the file using the "Delimited Spreadsheet" functions in LabVIEW.

Bob Schor 

[Sam.Huang]

Thanks for you prompt relpy,it's very useful , occasionally real blank in excel sheet cell, how to ignore the issue, thanks!!

[altenbach]

---

@Sam.Huang wrote:

Thanks for you prompt relpy,it's very useful , occasionally real blank in excel sheet cell, how to ignore the issue, thanks!!

---

Not knowing about the exact format and all possibilities, it seems that it might be sufficient to substitute the previous value of the second column ONLY if that string exists in a substring of the entry in the first column.

[altenbach]

---

@altenbach wrote:
Not knowing about the exact format and all possibilities, it seems that it might be sufficient to substitute the previous value of the second column ONLY if that string exists in a substring of the entry in the first column.

---

See if this works....