Hello Baaners

I have created an ASCII File with the Headings and Details and i want it to be formatted such that it opens in Excel perfectly, i.e, Each Columns comes as speperately as intended.

So i created a "tab" in the Headers and in details and put it in an ASCII File using seq.open. Now i copied onto the Client using the command server2client and saved it as ".xlsx" file format. Now when the Excel opens it, i get a message
"File extension or the format is not valid. Verify the file is not corrupted and so on"

But when i save the file as ".xls", the Excel opens it more or less perfectly. I need to save it in ".xlsx" format as it is the latest one.

Also, i need to make the Header Bold..

PS: If you have used Mingle, there is an option which says, export to excel and it exports very beautifully. Was thinking if this could be replicated or if it is already there.

Hi,

There are many ways to achieve that but with a bit coding and customizations- >

This shows a way to do it with creating an XML file with Excel supporting tags (http://www.baanboard.com/baanboard/showthread.php?t=42287)

This shows a way to do it with Open XML (http://www.baanboard.com/node/3819)

Other ways are ->

1. Create a script in any programming you want to convert CSV to Excel.
2. Use external reporting tool
3. Create HTML file with <td> and <tr> tags and rename it as .xls (Again, it will show a pop up warning but it will eventually open up with the formatting and styling you need)

Output to Excel utility uses a Java Program to perform the export using dll ttstpdllssi. Not sure if you can use it but if you can hack out its usage using ttstpbaandoc, you might be able to reuse it in your program.

Any links for learning about Open XML standards?

You can start here (http://openxmldeveloper.org/)

So how it works exactly? If i code it in Open XML, Excel reads it as xlsx file? and converts all the formatting like the Colour,Bold etc perfectly?

Okay Then..

I have converted it to xml file and then opening into Excel. Have one last hurdle. While opening the xls file, the Excel says "The Data may be corrupt".Are you sure you want to open the file?"

I think to skip this one thing and i will accomplish this Topic. What can i add into the "xml" file so that i dont get to see this warning?

PS: Apart from the Reg Edit Solution. Thats not practical,i.e,going to Client side and doing reg Edit in Mass.

Hi,

Check if this workbook opens up without any issues. If yes, build one with this standard.

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Your_name_here</Author>
<LastAuthor>Your_name_here</LastAuthor>
<Created>20080625</Created>
<Company>ABC Inc</Company>
<Version>10.2625</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6135</WindowHeight>
<WindowWidth>8445</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>

<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom" />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Style>
</Styles>

<Worksheet ss:Name="Sample Sheet 1">
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ID="Table1">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
<Cell><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">2</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">3</Data></Cell>
<Cell><Data ss:Type="Number">4</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">5</Data></Cell>
<Cell><Data ss:Type="Number">6</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">7</Data></Cell>
<Cell><Data ss:Type="Number">8</Data></Cell>
</Row>
</Table>
</Worksheet>

<Worksheet ss:Name="Sample Sheet 2">
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ID="Table2">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
<Cell><Data ss:Type="String">A</Data></Cell>
<Cell><Data ss:Type="String">B</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">C</Data></Cell>
<Cell><Data ss:Type="String">D</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">E</Data></Cell>
<Cell><Data ss:Type="String">F</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">G</Data></Cell>
<Cell><Data ss:Type="String">H</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

No it does not. Same Issue. If i name it as xml file it opens perfectly but if i name it as xls or xlsx, i get the warning message...

Hi,

Its a result of feature called Extension Hardening which was introduced in Office 2007 onwards. You might try it on Office 14 where it will work just fine or an older version of Office 2003. I don't see a problem if you just use .xml extension either as its an XML based worksheet. It will save you from handling delimiter issues while working on global reports.

Other solutions are ->

1. Modifying the registries on each client machine
2. Creating your own utility in any other programming which does the excel writing for you
3. Using alternatives like OpenOffice where the problem doesn't exists
4. Upgrade to some later version or downgrade to office 2003

Creating the utility seems the best option. Calling it from script and converting into xlsx..
Now how do i create it?:)

If you know Java, do what Infor does. Use Apache POI (https://poi.apache.org/)

What about transforming using XSLT? fr

Its one way, but will take a lot of coding and brainstorming. I would prefer java/python over it.

An xlsx file is usually a bunch of around 10 files zipped... What you do in Java exactly? You make all the files?

Hi,

You wont need to do that. The language you use will do it for you. For example, you can use Xslx Writer library in Python to do it. Here is the link with all sorts of examples -> http://xlsxwriter.readthedocs.io/examples.html

Or you can use Java based apache poi XSSF library. Google it and you will get tons of ways to do it.

So in your first post in this thread, you gave the link to Open XML format, So does that piece of program directly write the file in xlsx format??

Hi,

No, I don't think so. It was an example for writing an excel file(.xls) with the styling if required(in your case I guess you wanted to make the fonts Bold). XSLX is not possible without any utility as you can't build it from scratch unless you go with writing a code to create the supporting files and zip them which is too tedious of a task.

But again, why don't you just go with .xls if its giving you the required styling and formatting? I guess users should be okay with that. And if they aren't you would need to build the utility of your own as I pointed out earlier.