从Excel导入:某些单元格为空 [英] Importing from Excel: some cells become null

问题描述

我正在从excel文件中导入数据，我刚发现导入后某些单元格变成了"

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

这是我正在使用的代码

                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();
                }

有问题的列是 QTY ，其中包含:

The column in question is QTY which contains:

12
14
15
11
19k/yr
4
2

导入后，它成为我的gridview上的一个空间.所有其他单元格在gridview上的显示都很好.

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

在GridView中输出:

OUTPUT in GridView:

12
14
15
11

4
2

有什么想法吗?

推荐答案

在Excel中使用OLEDB时，一列的数据类型由每列的前几项确定.在您的情况下，该列的前几项是数字，因此假设该列的类型为 int .如果希望将该列视为文本，则需要确保顶部具有一些虚拟行，这些虚拟行的数据可确保正确的数据类型，然后在将数据读入数据表后过滤掉这些行.我知道这很聪明，但是应该可以.

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.

编辑-替代方法:使用Excel Interop/COM填充数据表

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-确保在使用COM对象后正确处置

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());
    }
}

这篇关于从Excel导入:某些单元格为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！