Importing from Excel: some cells become null

Question

I'm importing data from an excel file and I just noticed that some cells are becoming " " after the import

Here's the code I'm using

                FileUploadExcel.SaveAs("C:\\datatop.xls");
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\1.xls;Extended Properties=Excel 8.0;";


                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();
                    OleDbCommand command = new OleDbCommand("Select MONTH, QTY FROM [Sheet1$]", connection);
                    DataTable tb = new DataTable();
                    using (System.Data.Common.DbDataReader dr = command.ExecuteReader())
                    {
                        tb.Load(dr);
                    }
                    gv.DataSource = tb;
                    gv.DataBind();
                }

The column in question is QTY which contains:

It becomes a space on my gridview after the import. All other cells are displaying just fine on the gridview.

OUTPUT in GridView:

Any ideas?

Did some of your code get cut off? I don't get `1.Text` and `2.Text`. — devuxer, May 23 '11 at 03:29
Is you're able to edit your Excel file, try putting an apostrophe (') before 19k/yr. — devuxer, May 23 '11 at 03:39

devuxer · Accepted Answer · 2011-05-23T05:50:02.603

When using OLEDB with Excel, the data type of a column is determined by the first few items in each column. In your case, the first few items in the column are numbers, so it assumes the column is of type int. If you want the column to be considered text, you need to make sure you have some dummy rows at the top with data that ensures the right data types, then filter out those rows once the data is read into the data table. I know it's a cluge, but it should work.

Edit - Alternative: Using Excel Interop/COM to fill a DataTable

using Microsoft.Office.Interop.Excel;
using Sd = System.Data;

private void FillTableData(Sd.DataTable table, Worksheet worksheet, Range cells)
{
    using (var com = new ComObjectManager())
    {
        var firstCell = GetFirstCell(com, cells);
        var beginCell = com.Get<Range>(() => (Range)cells.Item[2, 1]);
        var endCell = GetLastContiguousCell(com, cells, firstCell);
        if (beginCell.Value == null) return;
        var range = GetRange(com, cells, beginCell, endCell);
        var data = (object[,])range.Value;
        var rowCount = data.GetLength(0);
        for (var rowIndex = 0; rowIndex < rowCount; rowIndex++)
        {
            var values = new object[table.Columns.Count];
            for (var columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
            {
                var value = data[rowIndex + 1, columnIndex + 1];
                values[columnIndex] = value;
            }
            table.Rows.Add(values);
        }
    }
}

private Range GetFirstCell(ComObjectManager com, Range cells)
{
    return com.Get<Range>(() => (Range)cells.Item[1, 1]);
}

private Range GetLastContiguousCell(ComObjectManager com, Range cells, Range beginCell)
{
    var bottomCell = com.Get<Range>(() => beginCell.End[XlDirection.xlDown]);
    var rightCell = com.Get<Range>(() => beginCell.End[XlDirection.xlToRight]);
    return com.Get<Range>(() => (Range)cells.Item[bottomCell.Row, rightCell.Column]);
}

ComObjectManager - ensures that COM objects are properly disposed after being used

using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;

public class ComObjectManager : IDisposable
{
    private Stack<object> _comObjects = new Stack<object>();

    public TComObject Get<TComObject>(Func<TComObject> getter)
    {
        var comObject = getter();
        _comObjects.Push(comObject);
        return comObject;
    }

    public void Dispose()
    {
        while (_comObjects.Count > 0)
            Marshal.ReleaseComObject(_comObjects.Pop());
    }
}

thanks mate. but is there another way? because i'm importing close to a hundred excel files and it's quite tedious to edit them all — Pod Mays, May 23 '11 at 05:20
Another possibility is to use Excel interop. This will only work if you are working on a machine that has Office installed, but if you do, it offers more flexibility than OLEDB. I'll update my answer with some sample code. — devuxer, May 23 '11 at 05:40

score 1 · Answer 2 · answered May 23 '11 at 05:57

@Pod Mays Rather you can use a third party DLL to work with excel for many operations and it is quite easy to work with it. Here is the link to its download :

also by using source code that is also available with it, you can easily and fastly learn how to use it. which is available here :

I also recommend it because it works very good and it has also not given me any problems.

Importing from Excel: some cells become null

2 Answers2

Linked