3

Is there an Excel text file format (XlFileFormat) to export that will not put quotation marks around strings? I've tried xlTextWindows. Maybe there is an additional setting I'm missing?

Most of the time, the datasets I'm exporting are in CSV, but I have a situation where I still want to export with an Excel type (long story) but really it is just a small blob of text. Otherwise, I'd just export to a text file; trying to avoid that for this one case.

[Edit] In this case, I have an Excel workbook with a single worksheet, with one cell (No header) with the text:

[Reports]

When saved, the text file contains:

"[Reports]"

because it is a text field and that is what Excel does when it saves it to a text file.

For this purpose, it cannot contain any surrounding quotation marks or any other characters.

I will probably end up using a FileStream and StreamWriter, but would like to see if this can be avoided.

[Additional Edit]

What the app does:

It Loops through the rows of a datatable and place the value for each column in an Excel workbook sheet cell.

This works great when you want it to truely be an Excel file or a .CSV file. I have an instance where I need to save a single cell's text to a text file, Excel insists on enclosing the text with quotation marks. I don't know about anyone else, but when I save something in Notepad, I don't add quotation marks. The application that wants to read this file, doesn't like the text enclosed in quotation marks either. There is no need to escape quotation marks if they 'happen' to be in the chunk of text, but they should never be added. Nothing should be added. It should just be a block of text in the same format it was in the Datatable.

Save the Excel workbook using

ExcelWorkbook.SaveAs

JeffO
  • 7,957
  • 3
  • 44
  • 53

3 Answers3

1

Have you tried Text (Tab delimited): enter image description here

smirkingman
  • 6,167
  • 4
  • 34
  • 47
  • I'm not doing this in the Excel application but in VB.Net using the: Microsoft.Office.Interop.Excel and tab delimited is unfortunately not an option: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx – JeffO Feb 01 '11 at 17:43
  • 1
    @jeff ah, in that case, I guess you'll have to use a streamwriter. Don't forget that there's a bug in Excel.UsedRange.Rows: You have to code *Worksheet.UsedRange.Rows.Count + Worksheet.UsedRange.Row - 1* to get the correct number of rows in the sheet. (Idem for UsedRange.Columns) – smirkingman Feb 01 '11 at 17:51
  • 1
    Just to clarify, tab-delimited is supported but its called `xlTextWindows` – Chris Haas Feb 02 '11 at 17:08
  • @Chris Haas - that may come in handy. – JeffO Feb 02 '11 at 23:05
  • thanks for the tip. I'm using streamwriter and it works fine. – JeffO Feb 02 '11 at 23:10
0

Haven't tried this, but it may help

What if you try this in a few ugly steps..

  • Save data as CSV - direct to text with Comma separators, no quotes (as you require. Use the CSV extensions)
  • Load CSV in Excel via Code
  • Save the file in Excel XLS(S) format via code

Then it might skip adding the quotes to the text in cells in the XLS(S) file it has saved.

Grantly
  • 2,546
  • 2
  • 21
  • 31
0

Take a look at SpreadsheetML. I have a sample here:
Generating an Excel file in ASP.NET

It's xml, so you could use a CDATA section to avoid the need to replace quotes with the " entity.

Community
  • 1
  • 1
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794