5 Replies Latest reply: May 15, 2014 9:56 AM by Bill Watson RSS

    V8.02: Formatting lost when exporting summaries

    Bill Watson

      Hi all.

       

      I created a model/project today which exports multiple summaries to one excel sheet. This all went well with the first summary exporting as expected. Subsequent summaries however exported with formatting missing (the client requested numbers formatted in Thousands)

       

      Is this a known problem and if so is there a workaround?

        • V8.02: Formatting lost when exporting summaries
          Bill Watson

          Any thoughts guys? This one really has me stumped.

          • V8.02: Formatting lost when exporting summaries
            Grant Perkins

            Hi Bill,

             

            This is not something I have noticed before but I just had an opportunity to run quick test (8.02 Pro) or several and I found that:-

             

            Measure values from summaries seem to be formatted as specified and include the decimal places and thousands separators as requested. This was fine whether it was for the first export or subsequent appends in the same export process or later processes.

             

            Key fields or those same fields used as Item fields show up with decimal point and thousands separators, where the field in the table is so defined, but after export the decimal place remains but the thousands separators do not exist.

             

            This is true whether the export is creating the worksheet or appending to it later.

             

            Is this what you get?

             

            Grant

            • V8.02: Formatting lost when exporting summaries
              Bill Watson

              Grant

               

              Sounds like you are on the right track. I am outputing the summaries one after the other as part of the project output in one process, appending them onto the sheet as shown in the image linked below:

               

              [url="http://i72.photobucket.com/albums/i161/HunkMccoy/work/modeloutput.gif"]http://i72.photobucket.com/albums/i161/HunkMccoy/work/modeloutput.gif[/url]

               

              As you can see the first summary outputs correctly, the subsequent summaries (which were cloned from the first with the filter changed) all lose formatting. I was just wondering, is this anything to do with the second summary having no data?

              • V8.02: Formatting lost when exporting summaries
                Grant Perkins

                Hi Bill,

                 

                By coincidence I was looking at a similar issue yesterday though not specifically for your problem type.

                 

                However I noticed that appending to an existing named range (Tab) did not pick up any specific formats from columns already in place.

                 

                In the example I was looking at the financials were not defined to have commas but the effect showed up with dates.

                 

                Now this is a guess but I rather suspect that the JetExport engine used for Export and Project Export is a little inconsistent when asked to append. Indeed I suspect it is a little inconsistent if the exported data area, with or without a header row, does not start on row one of the Excel worksheet. But I don't have the time or expertise to qualify that statement by testing and may have the wrong object in my sights!

                 

                I was using V8.02 by the way.

                 

                I am no Excel expert but I wonder if the simple solution would be to export the data to a worksheet 'as it comes' and then use that as a database behind a front end worksheet that does the formatting and 'presentation' to the customer.

                 

                Either that or run a formatting macro when the sheet is opened?

                 

                Something along those lines?

                 

                I hope this helps you to decide on a way forward.

                 

                Grant

                 

                [size="1"][ November 20, 2006, 11:18 AM: Message edited by: Grant Perkins ][/size]

                • V8.02: Formatting lost when exporting summaries
                  Bill Watson

                  Thanks Grant. Good to know I'm not just going loopy and missing something obvious.

                   

                  I will have a look at maybe doing some post process formatting.