Getting excel data into a formatted report.

[jrhappe]

I have a program that writes data collected to an Excel spread sheet as a single string with formatting characters,( ‘\’ codes). The unformatted data looks like this. 

 Now the challenge is to format the Excel sheet into a finished report. I would like to do this using a template. I have not had much luck using the report generation tool kit.

I would like it to look like this. 

I would like to open the template, append the line of text containing ‘\’ code,  starting at A,1.

[RubenVasquez]

Hi, you should do the following steps, one after another inside a sequence structure:

1. Use the "New report.vi" inside the excel toolkit, to open the template. In the report type input, just create a constant with "excel", and in the template input, just give it the path to the excel template file location. Connect the "report out" to the following instructions.

2. Use the "Excel easy text.vi" inside the excel toolkit, to write to the desired position inside the excel file. The "Report in" input should be connected to the report out output of the new report from step 1. The "Text" input is a string with the data. The "start" and "end" intpus, should be the same in order to write in a single cell, this is the position row and column (it is a cluster).

3. Once you finish writing, you should save with the "Save report to file.vi" inside the toolkit. If you provide the same path from step 1, the file will be overwritten, if not it will create a new excel file, and you can keep your template with no modification.

4. Finally close the report, by using the "Dispose report.vi"

In conclusion, you should use a sequence structure with 3 frames: one for step 1, one for step 2 and one for steps 3 and 4.

Take a look at the attached VI, this is an example in spanish. Is just a little more complex than explained above, but it has a bit more functionalities you could study. If you are not interested in all the VI, just take a look at the sequence.

[Bob_Schor]

There are a number of examples of how to write formatted Excel Reports that ship with LabVIEW.  A Revised Example that I know well can be found here.

I agree with the reply by RubenVasquez on how to use a Template -- any existing Excel file that you specify as a Template file will be opened when you do a New Report (if you don't specify a Template, a blank Excel Workbook will be opened).  At the time you save the Report (using Save Report to File), you specify the name of the Output File, which can be the same as the Template (overwriting it) or different (allowing you to use the Template for another Report).

I do not agree that you should use a three-frame Sequence structure to do this -- the proper use of the Error Line is a much better way of specifying the "sequence" of your code (as shown in the referenced Examples).

Something that you might consider is doing what is illustrated in the "Revised Example" cited in the first paragraph -- rather than try to parse a Template or write specific lines of text or data in specific cells, it may be easier to write your Report entirely with the RGT, including the row and column headers, spacing the entries "as you go".  This is particularly useful when you might not know, in advance, how many rows and/or columns an entry will occupy, and thus where the next entry should be placed.

Bob Schor

[Gregory]

---

@jrhappe wrote:

 I have not had much luck using the report generation tool kit.

---

Please post your code that generates the report, so that we might find the unlucky part of your code 🙂

[RTSLVU]

I highly recommend the XLR8 addon toolkit  for this type of formatting.

[jrhappe]

Thank you for all the help so far. This is the test program I have been playing with. The problem appears to be that the Excel Easy Text .vi will not handle the '\' code.

Here the text code if it does not come through the snippet, (first time posting one).

Opperator:\sJames\sHappe\rCalibration:\rPressure\sTransducer\sID\tPressure\sGauge\sID\tCal.\sDate\tCal\sDue\sDate\t15\sPSI\sGauge\sReading\s\t15\sPSI\sTransducer\sReading\t0\sPSI\sTransducer\sReading\t\rE-00640\tE-00678\tNov\s2018\t09\sNov\s2017\t15.05\t15.04\t-0.02\t\rBalance\sID\t5g\sWeight\sID\tCal.\sDate\tCal.\sDue\sDate\t5g\sWeight\sMeasured\t20g\sWeight\sID\tCal.\sDate\tCal.\sDue\sDate\t20g\sWeight\sMeasured\rE-00651\tE-00690\t30\sNov\s2017\tNov\s2018\t5.0003\tE-00688\t30\sNov\s2017\tNov\s2018\t20.0010\rMimic\sInformation:\rDI\sWater\sLot\s#\tGlycerol\sLot\s#\tDate\sMixed\r\nH023-09\tNA\tNA\r\n\rDate\t\sStation\s#\t\sSpecimen\s#\t\sRevision\t\sLot\s#\t\sFill\sVolume\s(mL)\t\sFluid\sDensity\s(g/mL)\t\sFluid\sViscosity\s(cP)\t\sSyringe\sDry\s(g)\t\sSyringe\sFilled\s(g)\t\sOBDD\sDry\s(g)\t\sOBDD\sFilled\s(g)\t\sSyringe\sEmpty\s(g)\s\t\r5/30/2018\t3.000000\t\s663-5\t\s\t\stest\sComparison\s16MAY18\t\s10\t\s1.0000\t1.0000\t8.9268\t18.8705\t21.1711\t31.1137\t8.9254\r\rOBDD\sEmpty\s(g)\t\sMaximum\sPSI\t\sMinimum\sPSI\t\sAvg.\sPSI\t\sDispense\sTime\s(sec)\t\sFlow\sRate\s(mL/hr)\t\sOBDD\sResudual\s(mL)\t\sDose\sAccuracy\sOBDD\s(%)\t\sTransfer\sSystem\s\t\sTrans.\sSys.\sResidual\s(mL)\t\sTotal\sResidual\s(mL)\t\sDose\sAccuracy\sSystem\s(%)\t\sNotes\r21.2851\t17.4203\t2.9948\t10.5613\t179.4780\t197.144\s\t0.1140\s\t98.9\t\t0.0025\t0.1165\s\t98.8\t\s

---

@Gregory wrote:

---

@jrhappe wrote:

 I have not had much luck using the report generation tool kit.

---

Please post your code that generates the report, so that we might find the unlucky part of your code 🙂

---

[jrhappe]

The template is attached below. 

[Bob_Schor]

You may be going about this in the "wrong way".  I copied your String into a Text File, then opened it with LabVIEW and read it -- it created a long string with embedded \s, \t, and \r characters.  I used three Search and Replace String functions to replace them respectively with a Space Character, a Tab Character, and an End-of-Line Constant.  I then noticed that I essentially had a Tab-Delimited String, so I then passed it through the SpreadSheet String to Array function and got the following:

Looks pretty close, right?  In fact, if you took this array and wrote it using Write Delimited Spreadsheet to a file with the extension ".csv", you could open it with Excel (which "knows how" to open Comma-Delimited-Values files, and, indeed, hijacks the extension to give it an Excel-like icon).  You could also (probably) use the RGT, instead, to write out this entire array as a true .xlsx file, ignoring the need for a Template (since you have almost everything in this file).

There is, however, one "gotcha" -- see the cell in the lower left corner?  It starts with \n, meaning that a few of your strings use \r for EOL, some use \r\n (and leave an "orphan" \n over), and some may use \n by itself.  This makes the parsing a little trickier, but that I leave as an Exercise for the Original Poster ...

Bob Schor