pasar - manejo de archivos excel en c#

El acceso a la hoja de cálculo de Excel con C#ocasionalmente devuelve un valor en blanco para algunas celdas

Question

Necesito acceder a una hoja de cálculo Excel e insertar los datos de la hoja de cálculo en una base de datos SQL. Sin embargo, las claves principales están mezcladas, la mayoría son numéricas y algunas son alfanuméricas.

El problema que tengo es que cuando las teclas numéricas y alfanuméricas están en la misma hoja de cálculo, las celdas alfanuméricas devuelven valores en blanco, mientras que todas las demás devuelven sus datos sin problemas.

Estoy usando el método OleDb para acceder al archivo de Excel. Después de recuperar los datos con una cadena de Comandos, coloco los datos en un DataAdapter y luego llené un DataSet. Paso iterativamente por todas las filas (dr) en la primera DataTable en el DataSet.

Hago referencia a las columnas usando dr ["..."]. ToString ()

Si depuro el proyecto en Visual Studio 2008 y veo las "propiedades extendidas", al mantener mi mouse sobre el "dr" puedo ver los valores del DataRow, pero la clave principal que debería ser alfanumérica es {}. Los otros valores están entre comillas, pero el valor en blanco tiene llaves.

¿Es este un problema de C # o un problema de Excel?

¿Alguna vez alguien ha encontrado este problema, o tal vez ha encontrado una solución / solución?

Gracias por adelantado.

Answer 1

{} Significa que se trata de algún tipo de objeto vacío y no de cadena. Cuando pasas el cursor sobre el objeto, deberías poder ver su tipo. Del mismo modo, cuando use el reloj rápido para ver dr ["..."] debería ver el tipo de objeto. ¿De qué tipo es el objeto que recibes?

Answer 2

ItemArray es una matriz de objetos. Así que supongo que la "columna" en el DataRow, que estoy tratando de referenciar, es de tipo objeto.

Answer 3

Para la compatibilidad con VISTA, puede usar el controlador EXCEL 12.0 en la cadena de conexión. Esto debería resolver su problema. Lo hizo mío.

Answer 4

La fuente de datos de Excel selecciona un tipo de columna para toda la columna. Si una de las celdas no coincide exactamente con ese tipo, deja espacios en blanco como ese. Tuvimos problemas donde nuestra mecanógrafa ingresó un "8" (un espacio antes del número, por lo que Excel lo convirtió en una cadena para esa celda) en una columna numérica. Tendría sentido para mí que probaría los métodos de .Net Parse ya que son más robustos, pero supongo que no es así como funciona el controlador de Excel.

Nuestra solución, ya que estábamos usando servicios de importación de bases de datos, era registrar todas las filas que ''fallaban'' de esta manera. Luego, volvimos al documento XLS y volvimos a tipear esas celdas, para asegurarnos de que el tipo subyacente fuera el correcto. (Descubrimos que eliminar el espacio no lo solucionaba; primero teníamos que borrar toda la celda, luego volver a escribir el ''8''). Se siente hacky y no es elástico, pero ese fue el mejor método que encontramos. Si el controlador de Excel no puede leerlo correctamente por sí mismo, no hay nada que pueda hacer para sacar esos datos una vez que esté en .Net.

Solo otro caso en el que Office oculta los detalles importantes de los usuarios en nombre de la simplicidad y, por lo tanto, lo hace más difícil cuando tiene que ser exacto para los usos de energía.

Answer 5

Solución:

1. Coloca HDR = No para que la primera fila no se considere el encabezado de la columna. Cadena de conexión: Provider = Microsoft.Jet.OLEDB.4.0; Data Source = FilePath; Propiedades extendidas = "Excel 8.0; HDR = No; IMEX = 1";
2. Ignoras la primera fila y accedes a los datos de la forma que quieras (DataTable, DataReader ect). Puede acceder a las columnas por índices numéricos, en lugar de nombres de columna.

Funcionó para mí ¡De esta manera no tienes que modificar los registros!

Answer 6

Ordena los registros en el archivo xls por código ascii en orden descendente para que los campos alfanuméricos aparezcan en la parte superior debajo de la fila del encabezado. Esto asegura que la primera fila de datos leídos definirá el tipo de datos como "varchar" o "nvarchar"

Answer 7

hola todo este código es también obtiene valores alfanuméricos

using System.Data.OleDb;

string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties="+(char)34+"Excel 8.0;IMEX=1;"+(char)34;

string CommandText = "select * from [Sheet1$]";

OleDbConnection myConnection = new OleDbConnection(ConnectionString);
myConnection.Open();

OleDbDataAdapter myAdapter = new OleDbDataAdapter(CommandText, myConnection);

ds = null;
ds = new DataSet();
myAdapter.Fill(ds);

Answer 8

Esto no está del todo bien! Aparentemente, Jet / ACE SIEMPRE asume un tipo de cadena si las primeras 8 filas están en blanco, independientemente de IMEX = 1. Incluso cuando hice que las filas se leyeran a 0 en el registro, todavía tenía el mismo problema. Esta fue la única forma segura de hacerlo funcionar:

try
{
    Console.Write(wsReader.GetDouble(j).ToString());
}
catch   //Lame unfixable bug
{
    Console.Write(wsReader.GetString(j));
}

Answer 9

Solución:

Cadena de conexión:

Proveedor = Microsoft.Jet.OLEDB.4.0; Origen de datos = FilePath; Propiedades extendidas = "Excel 8.0; HDR = Sí; IMEX = 1";

1. HDR=Yes; indica que la primera fila contiene nombres de columna, no datos. HDR=No; indica lo opuesto.

2. IMEX=1; le dice al conductor que siempre lea las columnas de datos "entremezcladas" (números, fechas, cadenas, etc.) como texto. Tenga en cuenta que esta opción puede afectar negativamente el acceso de escritura de hoja de Excel.

Sintaxis SQL SELECT * FROM [sheet1$] . Es decir, el nombre de la hoja de trabajo de Excel seguido de $ y envuelto en corchetes [ ] .

Importante:

• Consulte el registro ubicado [HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / Jet / 4.0 / Engines / Excel] REG_DWORD "TypeGuessRows". Esa es la clave para no permitir que Excel use solo las primeras 8 filas para adivinar el tipo de datos de las columnas. Establezca este valor en 0 para escanear todas las filas. Esto podría perjudicar el rendimiento.

• Si el libro de trabajo de Excel está protegido por una contraseña, no puede abrirlo para acceder a los datos, incluso al proporcionar la contraseña correcta con su cadena de conexión. Si lo intenta, recibirá el siguiente mensaje de error: "No se pudo descifrar el archivo".

Answer 10

Respondí una pregunta similar aquí . Aquí he copiado y pegado la misma respuesta para su conveniencia:

Tuve el mismo problema, pero pude solucionarlo sin recurrir a la interfaz COM de Excel o software de terceros. Implica un poco de procesamiento, pero parece estar funcionando para mí.

1. Primero lea en los datos para obtener los nombres de las columnas
2. Luego, cree un nuevo DataSet con cada una de estas columnas, configurando cada uno de sus DataTypes en string.
3. Lee los datos de nuevo en este nuevo conjunto de datos. Voila: la notación científica ya no existe y todo se lee como una cadena.

Aquí hay un código que ilustra esto, y como un bono adicional, ¡es incluso StyleCopped!

public void ImportSpreadsheet(string path)
{
    string extendedProperties = "Excel 12.0;HDR=YES;IMEX=1";
    string connectionString = string.Format(
        CultureInfo.CurrentCulture,
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=/"{1}/"",
        path,
        extendedProperties);

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        using (OleDbCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT * FROM [Worksheet1$]";
            connection.Open();

            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            using (DataSet columnDataSet = new DataSet())
            using (DataSet dataSet = new DataSet())
            {
                columnDataSet.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(columnDataSet);

                if (columnDataSet.Tables.Count == 1)
                {
                    var worksheet = columnDataSet.Tables[0];

                    // Now that we have a valid worksheet read in, with column names, we can create a
                    // new DataSet with a table that has preset columns that are all of type string.
                    // This fixes a problem where the OLEDB provider is trying to guess the data types
                    // of the cells and strange data appears, such as scientific notation on some cells.
                    dataSet.Tables.Add("WorksheetData");
                    DataTable tempTable = dataSet.Tables[0];

                    foreach (DataColumn column in worksheet.Columns)
                    {
                        tempTable.Columns.Add(column.ColumnName, typeof(string));
                    }

                    adapter.Fill(dataSet, "WorksheetData");

                    if (dataSet.Tables.Count == 1)
                    {
                        worksheet = dataSet.Tables[0];

                        foreach (var row in worksheet.Rows)
                        {
                            // TODO: Consume some data.
                        }
                    }
                }
            }
        }
    }
}