pclarke7
asked on
C# reading Excel 2007 worksheet is limited to 255 characters per Cell
Hi,
I have a c# application which is scccessfully reading data from various excel sheets. However once the cell exceeds 255 characters the value is either truncated to 255 or returned as null.
If I run the code below on my windows 7 64bit o/s - It reads the full contents of the cell which has more than 255 characters (700 characters). However if I run it on my windows 7 32bit o/s laptop then it returns null for every cell exceeding 255 characters.
Can anyone tell me why it works in 64b but not in 32b. see code below
using System;
using System.Collections.Generic ;
using System.Linq;
using System.Text;
using System.Data.OleDb;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string con = @"Provider=Microsoft.ACE.O LEDB.12.0; " +
@"Data Source=c:\temp\Book1.xlsx; " +
@"Extended Properties='Excel 8.0;HDR=Yes; IMEX=1;'";
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
var TranArr = new string[999, 20];
int i = 0;
using (OleDbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
TranArr[i, 1] = dr[0].ToString();
TranArr[i, 2] = dr[1].ToString();
Console.WriteLine(TranArr[ i, 1]);
Console.WriteLine(TranArr[ i, 2]);
}
}
}
Console.ReadKey();
}
}
}
I have a c# application which is scccessfully reading data from various excel sheets. However once the cell exceeds 255 characters the value is either truncated to 255 or returned as null.
If I run the code below on my windows 7 64bit o/s - It reads the full contents of the cell which has more than 255 characters (700 characters). However if I run it on my windows 7 32bit o/s laptop then it returns null for every cell exceeding 255 characters.
Can anyone tell me why it works in 64b but not in 32b. see code below
using System;
using System.Collections.Generic
using System.Linq;
using System.Text;
using System.Data.OleDb;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string con = @"Provider=Microsoft.ACE.O
@"Data Source=c:\temp\Book1.xlsx;
@"Extended Properties='Excel 8.0;HDR=Yes; IMEX=1;'";
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
var TranArr = new string[999, 20];
int i = 0;
using (OleDbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
TranArr[i, 1] = dr[0].ToString();
TranArr[i, 2] = dr[1].ToString();
Console.WriteLine(TranArr[
Console.WriteLine(TranArr[
}
}
}
Console.ReadKey();
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No... The only problem with using GetString is that it could lead to problems if the value in the cell is not a string. It depends on the implementation of the data provider if it makes an implizit conversion.
dr[] returns the data in an internal data format (which probably is limited to 255 characters)
dr.GetString() returns it as a more or less unlimited sized string.
dr[] returns the data in an internal data format (which probably is limited to 255 characters)
dr.GetString() returns it as a more or less unlimited sized string.
ASKER
Hi Hugo,
tried this and it works perfectly until it finds an null entry in the cell and then it crashes with the following error "Unable to cast object of type 'System.DBNull' to type 'System.String'."
any suggestions how I can get away without have to populate a string value into every instance of this cell.?
regards
Pat
tried this and it works perfectly until it finds an null entry in the cell and then it crashes with the following error "Unable to cast object of type 'System.DBNull' to type 'System.String'."
any suggestions how I can get away without have to populate a string value into every instance of this cell.?
regards
Pat
That's not a problem.
if (!dr.IsDBNull(0))
TranArr[i, 1] = dr.GetString(0);
} else {
TranArr[i,1] = "";
}
ASKER
Thanks Hugo,
your a star !!
I was just at the point of re-designing the application because of this restriction. I have been through so many posts on this issue over the past week but you are the first person to supply a simple workaround that works.
Pat
your a star !!
I was just at the point of re-designing the application because of this restriction. I have been through so many posts on this issue over the past week but you are the first person to supply a simple workaround that works.
Pat
ASKER
this particular cell will always be a string whereas the spreadsheet is a mix of strings and numerics. I assume that I can use dr.GetString(n) for every string field that could exceed 255 characters. Do you see any problems with this ?
regards
Pat