Export to Spreadsheet, w/o Excel

[MotoMan_Yz400]

I see a few tutorials pertaining to exporting data into an Excel spreadsheet. Looks straightforward, however I do not own Excel. I use Open Office (hey, it's free). I'm able to open Excel spreadsheet within Open Office Calc. However when I write my code, and I goto reference Excel, it's not there. Well I can figure this due to the fact I dont own Excel. Now Open Office doesnt show either.

So I was wondering if there is a way I can create an Excel file, without having Excel installed? Maybe download a component that allows me to, or maybe another work around?

[asnider]

You're right, you don't see the driver because you don't own the software. You could just write to a delimited text file, which imports into any excel-like system.

[MotoMan_Yz400]

You're right, you don't see the driver because you don't own the software. You could just write to a delimited text file, which imports into any excel-like system.

Yeah, thats what I was thinking. But you loose the "Coolness Factor" b/c you really cant open it from explorer like that. You'll have to import it. Oh well.

I'll continue on the delimited style, however, going straight to spreadsheet would be nice.

[chris128]

Yeah, thats what I was thinking. But you loose the "Coolness Factor" b/c you really cant open it from explorer like that. You'll have to import it. Oh well.

I'll continue on the delimited style, however, going straight to spreadsheet would be nice.

Do you mean you want the CSV file to just open in Excel when you open it through explorer? because thats standard behavior for any machine with Excel on it... the CSV extension is associated with Excel when it gets installed.

Or did you just mean that for you (without excel obviously) when you open it you would like it to be in a more readable format?

[MotoMan_Yz400]

Do you mean you want the CSV file to just open in Excel when you open it through explorer? because thats standard behavior for any machine with Excel on it... the CSV extension is associated with Excel when it gets installed.

Or did you just mean that for you (without excel obviously) when you open it you would like it to be in a more readable format?

Well the reason I wanted it in an Ecxel file is the standard of it all. That way I can open, print, modify, etc. in a standard way with many programs (In my case Open Office). With delimited format, it get uber clumsy to try to read the file, I would have to manually import it into OOffice every time I want to change something. I guess what I'll end up doing is assigning my delimited file an extension, and assigning that extension to Open Office. It's clumsy, however it'll work.


But I would still love to hear if someone has a work around for this!

[chris128]

I'm pretty sure that without Excel your not going to do what you want I'm afraid. Although I guess you can never say never with programming!

[MotoMan_Yz400]

I'm pretty sure that without Excel your not going to do what you want I'm afraid. Although I guess you can never say never with programming!

Well my other alternative was to find a standard file format (other than delimited) that was in ASCII. And "build" a spreadsheet file that way. But I'm almost better off just sticking to delimited.

[chris128]

lol yeah I would say stick to CSV

[MotoMan_Yz400]

lol yeah I would say stick to CSV

Yeah thats what I was thinking. Now I'm having trouble with setting it up correctly.

I have a indexing line in my file (first line). listing line data width (number of entries in all the lines), and a file length (how many entries). So it would look like this:

3,2
Data,Data,Data
Data,Data,Data
EOF


I know there has to be a better way. I know I can get rid of the line data width, that should be easy. How do I find the file length (# of lines)?

And I used to know, however I forgot how to `parse?' the file. I'm importing the entire file into an array, however I need to break each line up first.

Better way?

I'll need to add entries sorted by the first data in the line. Any suggestions on the best way to go about doing this? Importing the whole file into an array, edit, then pushing it back out just seems clumsy.

[chris128]

So if I understand correctly - you need to find out how many lines are in the file and also need to know how to split the lines up using the comma as a seperator yeah?

This would display how many lines are in the file in a messagebox:

Dim AllLines() As String = IO.File.ReadAllLines("C:\test.csv")
MessageBox.Show(AllLines.Count)

To use the text file parser you would declare it like this:

Dim AllValues As New FileIO.TextFieldParser("C:\test.csv")

and then you would have to play around with the properties to set the delimiter and field width etc, I've never tried using it so cant tell you off the top of my head I'm afraid.
Should help you a bit though

[Jenner]

Another option is to write it to an OpenOffice standard, such as .XLSX. This will work with Office 2007 and earlier versions of Office that have the 2007 compatibility pack installed, as well as OpenOffice.

The standard is XML based and is an open standard. Shouldn't be too hard to write an export routine to it.

[RobDog888]

xlsx is the Excel 2007 file extension which uses a xml based file format that is compressed.

You can write a Excel file (xls) without Excel but you need to study Microsoft's Binary Interchange File Format (BIFF).

[chris128]

Another option is to write it to an OpenOffice standard, such as .XLSX. This will work with Office 2007 and earlier versions of Office that have the 2007 compatibility pack installed, as well as OpenOffice.

The standard is XML based and is an open standard. Shouldn't be too hard to write an export routine to it.

I thought openoffice couldnt open files saved from Excel in the 2007 format (XLSX) ?

[mendhak]

Well my other alternative was to find a standard file format (other than delimited) that was in ASCII. And "build" a spreadsheet file that way. But I'm almost better off just sticking to delimited.

XML, pure and simple. Your own format.

[RobDog888]

xlsx files are opensource and there are utilities to read/write to them without Excel from third party companies as well as Microsoft. But remember that its Excel 2007 file format so it cant be opened by Excel 2003 or earlier versions.

[chris128]

well ya learn something new every day... I thought XLSX was another Microsoft only format like XLS etc. Obviously I was wrong

[RobDog888]

Yea, back in late 2006 MS announced they were making the file format "open source". If you are interested you can search the MS site for all the details and about the new format.