Formatting Numeric Data As Text Labels

Aspose.Cells Product Family
1

I have numeric that has problems in formatting due to Aspose's (or possibly Excel's) interpretation of the data as numeric or date format when the data is opened.

My data comes from a CSV file which I open using a stream. I've tried prepending various characters to the data but still seem to have an issue.

How can I get data in fields to appear 'as-is' with the text always being treated as text labels rather than dates or numerics?

Sample code which I'm using to open the data.


#region Private Data Members

private byte[] mData;

#endregion

#region Public Methods

///


/// Convert a CSV byte array to an Excel byte array
///
///
///
public byte[] CSVtoXLS(byte[] fileData)
{
License license = new License();
license.SetLicense("Aspose.Excel.lic");

// Write excel file using aspose open and save
Excel excel = new Excel();

MemoryStream memoryStream = new MemoryStream(fileData);
excel.Open(memoryStream, FileFormatType.CSV);


// Lock top row (freeze pane)
excel.Worksheets[0].FreezePanes(
1,1,1,
excel.Worksheets[0].Cells.MaxDataColumn);

for (int row=0; row<excel.Worksheets[0].Cells.MaxDataRow; row++)
{
// resize rows
excel.Worksheets[0].AutoFitRow(row);
}
for (int col=0; col<excel.Worksheets[0].Cells.MaxDataColumn; col++)
{
// resize columns
excel.Worksheets[0].AutoFitColumn(col);
}


MemoryStream memoryStream2 = new MemoryStream();
excel.Save(memoryStream2, FileFormatType.Excel97);

byte[] data = memoryStream2.ToArray();

// close and GC before exit
memoryStream.Close();
memoryStream2.Close();

excel = null;
memoryStream = null;
memoryStream2 = null;

return (data);
}

///


/// Convert a CSV stream to an Excel data stream
///
///
///
public System.IO.Stream CSVtoXLSStream(byte[] fileData)
{
License license = new License();
license.SetLicense("Aspose.Excel.lic");

// Write excel file using aspose open and save
Excel excel = new Excel();
MemoryStream memoryStream = new MemoryStream(fileData);
excel.Open(memoryStream, FileFormatType.CSV);

for (int row=0;
row<excel.Worksheets[0].Cells.MaxDataRow;
row++)
{
// resize rows
excel.Worksheets[0].AutoFitRow(row);
}
for (int col=0;
col<excel.Worksheets[0].Cells.MaxDataColumn;
col++)
{
// resize columns
excel.Worksheets[0].AutoFitColumn(col);
}

MemoryStream memoryStream2 = new MemoryStream();
excel.Save(memoryStream2, FileFormatType.Excel97);

return (memoryStream2);
}

#endregion

2

Please post your csv file here. I will check it.

3

Well, we are not actually using files. We are using streams and byte arrays in memory with data delimited using CSV format. Due to the flexibility of your open and save methods, this is possible - thanks!!

However, here is sample code to demonstrate what happens when I try to format numeric data as test. It is a test console app which takes one parameter, the name of the input file. It then outputs one Excel file:

Code:

using System;
using System.IO;
using System.Text;
using Aspose.Excel;
using System.Reflection;


public class Test
{
public static void Main (string[] args)
{
if (args == null || args.Length == 0)
{
throw new ApplicationException ("Specify the CSV file to open.");
}

License license = new License();
license.SetLicense("Aspose.Excel.lic");

Excel excel = new Excel();
byte[] fileData = FileToByteArray(args[0]);
MemoryStream memoryStream = new MemoryStream(fileData);
excel.Open(memoryStream, FileFormatType.CSV);

for (int row=0; row {
// resize rows
excel.Worksheets[0].AutoFitRow(row);
}
for (int col=0; col {
// resize columns
excel.Worksheets[0].AutoFitColumn(col);
}

//MemoryStream memoryStream2 = new MemoryStream();
//excel.Save(memoryStream2, FileFormatType.Excel97);
excel.Save(args[0].Replace(".csv",".xls"), FileFormatType.Excel97);


}

///
/// Convert a file to a byte array
///
///
///
public static byte[] FileToByteArray(string fileName)
{
FileInfo fileInfo = new FileInfo(fileName);
long fileLength = fileInfo.Length;
FileStream fileStream = new FileStream(
fileName, FileMode.Open, FileAccess.Read);
BinaryReader binaryReader = new BinaryReader(fileStream);
byte[] data = binaryReader.ReadBytes((int)fileLength);
binaryReader.Close();
fileStream.Close();
return data;
}
}

Input file "test.csv":

09/03/1971
"09/03/1971"
"'09/03/1971"
/"09/03/1971/"
/'09/03/1971
'09/03/1971
''09/03/1971
\'09/03/1971
\"09/03/1971

Aspose Output:

26179
26179
'09/03/1971
/"09/03/1971/"
/'09/03/1971
'09/03/1971
''09/03/1971
\'09/03/1971
\"09/03/1971

I need a way to get a value in a CSV string to appear in the Excel spreadsheet 'verbatim' - exactly as it would appear in the CSV text.

I also need to output it to an Excel 97 file. Let me know if this won't work or if the limitation is related to the Excel 97 output format.

I'm able to parse and pad the data in the initial CSV string. If I could prepend a character sequence which would make the text parse as a label (rather than an Excel recognized date format or numeric format) on the open method call, it would be ideal.

We are also handling many other formats and need this particular format (Excel) to work in a similar fashion.

4

Please try this attached fix and add a line of code:

excel.Open(memoryStream, FileFormatType.CSV);

//Added code

excel.Worksheets[0].Cells.Columns[0].Style.Custom = "mm/dd/yyyy";

Aspose.Excel converts your string to numeric values as MS Excel. But since it's difficult for us the get the format string, you should set the number format string by your code.