Solved

C# reading Excel 2007 worksheet is limited to 255 characters per Cell

Posted on 2014-02-26
6
847 Views
Last Modified: 2014-02-26
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.OLEDB.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();

        }
    }
}
0
Comment
Question by:pclarke7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
HugoHiasl earned 500 total points
ID: 39888441
If you can be sure that the data is string then you could try:

                       TranArr[i, 1] = dr.GetString(0);
                        TranArr[i, 2] = dr.GetString(1);

Open in new window

0
 

Author Comment

by:pclarke7
ID: 39888608
Thanks Hugo,
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
0
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 39888682
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.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:pclarke7
ID: 39888738
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
0
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 39890067
That's not a problem.

    if (!dr.IsDBNull(0)) 
        TranArr[i, 1] = dr.GetString(0);
    } else {
        TranArr[i,1] = "";
    }

Open in new window

0
 

Author Closing Comment

by:pclarke7
ID: 39890464
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
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question