I have a need to take pre-generated reports and make them available to O4W users as Excel spreadsheets. Currently these are generated using OIPI (1) to a PDF and saving the results. When the users requests the report the pdf is sent and is displayed on their browser. I would like to be able to supply these same reports in Excel format. I noticed in OIPI (2) you can save a report into "xls" format. However, after trying a "simple" columner report the resulting spreadsheet is badly misformatted. There are columns that are skipped, and some have their width set to zero. Additionally, when it comes to paging, the headings are all messed up and the column headings appear at the top of each page. Ideally, it would be nice to not repeat the column headings…

I am looking for recommendations on what is the best way to accomplish this?

Thanks,

John

John,

   I did investigate the saving of a columnar report to Excel file format using OIPI(2) and did encounter formatting issues in the resulting exported file. I then exported as a CSV file and Excel was then able to display the data appropriately. Would this approach satisfy your requirements?

                                      Barry Hausner

Hi Barry,

Thanks for you response. I am aware of the csv option and do use it in some cases. However, correct me if I am wrong, but you lose control of the datatype when you open a csv file with Excel. What I mean is if I have a field being exported like zip code and the data contains leading zeroes, excel will automatically strip this when opening the file.

I am trying to offer the client the "option" of supplying the report to excel but I would like it to be formatted as close to the original report as possible.

Since these reports are pre-generated I use OLE to stuff the data into Excel and have Excel save it as a xls file. I have done this in the past but it is faily complicated and would involve major changes to the programs generating the output.

Any other options available?

Thanks,

John

John,

  Agreed, Excel doesn't know how to interpret datatypes when importing a CSV field. I did a little further investigation and came up with the following approach:

This works for Microsoft Office 2010, Excel Version 14

1. Open a blank (new) file (File → New from workbook)

2. Open the Import Wizard (Data → From Text)

3. Select your .csv file and Import

4. In the dialogue box, choose 'Delimited', and click Next.

5. Choose your delimiters (uncheck everything but 'comma'), choose your Text qualifiers (likely {None}), click Next

6. In the Data preview field select the column you want to be text. It should highlight.

7. In the Column data format field, select 'Text'.

8. Click finished.

This does give you control over how individual columns are interpreted as they are imported into Excel. I will continue to look into alternate (and perhaps more automated and elegant solutions) but thought this information might be of some value to you. Please let me know.

                                          Barry Hausner

Thanks again Barry,

Yes I am aware of the import wizard. Perhaps I should look into a way of automating this with OLE. This probably would require the least amount of customizations in the code generating the output.

I will have to think about..

Thanks,

John

I created a spreadsheet with an embedded macro that would import the CSV into the proper formatting and datatypes.

I believe you can do the same by setting the CSV as a pivot table datasource.

I have a need to take pre-generated reports and make them available to O4W users as Excel spreadsheets. Currently these are generated using OIPI (1) to a PDF and saving the results. When the users requests the report the pdf is sent and is displayed on their browser. I would like to be able to supply these same reports in Excel format. I noticed in OIPI (2) you can save a report into "xls" format. However, after trying a "simple" columner report the resulting spreadsheet is badly misformatted. There are columns that are skipped, and some have their width set to zero. Additionally, when it comes to paging, the headings are all messed up and the column headings appear at the top of each page. Ideally, it would be nice to not repeat the column headings…

I am looking for recommendations on what is the best way to accomplish this?

Thanks,

John

Hi, John. How are you generating your "simple" columnar report here? Are you using the OIPI commands (like ADDTABLE) to build the output, or are you just generating the output from an R/List statement?

Thanks,

- Bryan Shumsky

Revelation Software, Inc.

Most are addtable with some elements are TEXT commands.

I done some additional research and think I might have a solution.

If I run the report as HTML it creates a file with TR and TD elements that excel can easily import. The key is, right now that requires a warm body to select the htm file and do an Open With → Excel. I would like to be able to return this data through O4W and "automate" Excel to import the data.

Any way to do this?

Btw, I found the following code that can be placed on a webpage that takes a html table that is on the same page and loads it into Excel. The problem with this is it only takes the table. I want to load the entire html file including standalone text.

Thanks,

John

taken from http://www.databison.com/index.php/html-to-excel-using-javascript-and-excel-to-html-using-vba/

<script type="text/javascript">

function write_to_excel()

{

str="";

var mytable = document.getElementsByTagName("table")[0];

var row_Count = mytable.rows.length;

var col_Count = mytable.getElementsByTagName("tr")[0].getElementsByTagName("td").length;

var ExcelApp = new ActiveXObject("Excel.Application");

var ExcelSheet = new ActiveXObject("Excel.Sheet");

ExcelSheet.Application.Visible = true;

  for(var i=0; i < row_count ; i++) 

  {   

      for(var j=0; j < col_Count; j++) 

      {           

          str= mytable.getElementsByTagName("tr")[i].getElementsByTagName("td")[j].innerHTML;

           ExcelSheet.ActiveSheet.Cells(i+1,j+1).Value = str;

       }

   }

}

</script></script></head>

<body>

<input type="submit" value="Export to EXCEL" onclick="write_to_excel();"/>

<!– –>

<!– INSERT THE TABLE YOU WANT EXPORT HERE –>

<table><tr><td>First</td><td>second</td></tr></table>

<!– *example given above**–>

Please keep in mind I found a bug in the code. The first For loop should be row_Count not row_count.

John

Another option is to rename the file as .xls and Excel will open it however, 2007 and above adds an additional dialog box warning the user that the file is in the wrong format. What would be nice is if we could save the output directly into Excel (xls) format.

Thanks,

John

Function bsbs_open_csv_in_excel(InFile,OutFile,Err)



Equ wdFormatText to -4158



If Unassigned(OutFile) Or OutFile = '' Then

	OutFile=InFile

	OutFile[-4,4]=".XLS"

end



osdelete OutFile



ObjExcel = OleCreateInstance("Excel.Application")

objWorkBooks = OleGetProperty(objExcel, 'WorkBooks')

is_ok = ( oleStatus() eq 0 )

if is_ok else

	err = 'Unable to start Excel'

end



if is_ok then

	objWorkBook = OleCallMethod(objWorkBooks, 'Open', InFile)

	is_ok = ( oleStatus() eq 0 )

	if is_ok else

		err = 'Unable to Open |' : quote(InFile)

	end

End



if is_ok then

	x = OleCallMethod( objWorkBook,"SaveAs", OutFile, wdFormatText)

	is_ok = ( oleStatus() eq 0 )

	if is_ok else

		err = 'Unable to SaveAs |' : quote(OutFile)

	End

End

if is_ok Then

	oSheet=olegetproperty(objWorkBook,"Worksheets",1)

	Range=OleGetProperty(oSheet,"Range","A:Z")

	oEntireColumn=OleGetProperty(Range,"EntireColumn")

	OlePutProperty(oEntireColumn,"Autofit",1)

End





If is_ok Then

	ObjExcel->visible=1

	End Else

	x=OleCallMethod( objWorkBook,"Close")

	x=OleCallMethod( ObjExcel,"Quit")

end





Return is_ok

Thank you very much Barry!

I used a variation on your logic to save the file as xls format. "Equate xlWKS to -4143" and changed it so the program is essentially a "black box" converting the source file to a resulting Excel format.

View this thread on the Works forum...