c# - net - ¿Cómo importo desde Excel a un DataSet usando Microsoft.Office.Interop.Excel?

leer excel c# interop

Question

Lo que quiero hacer

Estoy tratando de usar el espacio de nombres Microsoft.Office.Interop.Excel para abrir un archivo de Excel (XSL o CSV, pero lamentablemente no XSLX) e importarlo a un DataSet. No tengo control sobre la hoja de trabajo ni los nombres de las columnas, así que debo permitir cambios en ellos.

Lo que he intentado

Intenté con el método OLEDB de esto en el pasado, y tuve muchos problemas con él (errores, lentitud y conocimiento previo requerido del esquema del archivo Excel), así que quiero evitar hacerlo nuevamente. Lo que me gustaría hacer es usar Microsoft.Office.Interop.Excel para importar el libro de trabajo directamente a un DataSet, o recorrer las hojas de trabajo y cargar cada una en una DataTable.

Créalo o no, he tenido problemas para encontrar recursos para esto. Algunas búsquedas en StackOverflow han encontrado principalmente personas que intentan hacer lo contrario (DataSet => Excel) o la técnica OLEDB. Google no ha sido mucho más útil.

Lo que tengo hasta ahora

 public void Load(string filename, Excel.XlFileFormat format = Excel.XlFileFormat.xlCSV)
{
app = new Excel.Application();
book = app.Workbooks.Open(Filename: filename, Format: format);
DataSet ds = new DataSet();
foreach (Excel.Worksheet sheet in book.Sheets)
{
DataTable dt = new DataTable(sheet.Name);
ds.Tables.Add(dt);
//??? Fill dt from sheet
}
this.Data = ds;
}

Estoy bien con la importación de todo el libro de una vez, o pasando por una hoja a la vez. ¿Puedo hacer esto con Interop.Excel?

Answer 1

¿Has visto este? De http://www.aspspider.com/resources/Resource510.aspx :

public DataTable Import(String path)
{
Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "/t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
object rowIndex = 2;
DataTable dt = new DataTable();
dt.Columns.Add("FirstName");
dt.Columns.Add("LastName");
dt.Columns.Add("Mobile");
dt.Columns.Add("Landline");
dt.Columns.Add("Email");
dt.Columns.Add("ID");
DataRow row;
while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)
{
rowIndex = 2 + index;
row = dt.NewRow();
row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2);
row[4] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2);
index++;
dt.Rows.Add(row);
}
app.Workbooks.Close();
return dt;
}

Answer 2

¿Qué pasa con el uso de Excel Data Reader (alojado anteriormente here ) un proyecto de código abierto en codeplex? Me funciona muy bien exportar datos de hojas de Excel.

El código de muestra proporcionado en el enlace especificado:

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file (''97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

ACTUALIZAR

Después de buscar un poco, me encontré con este artículo: Lectura de MS Excel más rápida usando Office Interop Assemblies . El artículo solo usa Office Interop Assemblies para leer datos de una hoja de Excel determinada. El código fuente del proyecto está allí también. Supongo que este artículo puede ser un punto de partida sobre lo que intentas lograr. A ver si eso ayuda

ACTUALIZACIÓN 2

El código siguiente lleva un excel workbook y lee todos los valores encontrados, para cada excel worksheet dentro del excel workbook .

private static void TestExcel()
{
ApplicationClass app = new ApplicationClass();
Workbook book = null;
Range range = null;
try
{
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
book = app.Workbooks.Open(@"C:/data.xls", Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value);
foreach (Worksheet sheet in book.Worksheets)
{
Console.WriteLine(@"Values for Sheet "+sheet.Index);
// get a range to work with
range = sheet.get_Range("A1", Missing.Value);
// get the end of values to the right (will stop at the first empty cell)
range = range.get_End(XlDirection.xlToRight);
// get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
range = range.get_End(XlDirection.xlDown);
// get the address of the bottom, right cell
string downAddress = range.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);
// Get the range, then values from a1
range = sheet.get_Range("A1", downAddress);
object[,] values = (object[,]) range.Value2;
// View the values
Console.Write("/t");
Console.WriteLine();
for (int i = 1; i <= values.GetLength(0); i++)
{
for (int j = 1; j <= values.GetLength(1); j++)
{
Console.Write("{0}/t", values[i, j]);
}
Console.WriteLine();
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
range = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
}
}

En el código anterior, los values[i, j] son el valor que necesita agregar al dataset . denota la fila, mientras que, j denota la columna.

Answer 3

object[,] valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
//Get the column names
for (int k = 0; k < valueArray.GetLength(1); )
{
//add columns to the data table.
dt.Columns.Add((string)valueArray[1,++k]);
}
//Load data into data table
object[] singleDValue = new object[valueArray.GetLength(1)];
//value array first row contains column names. so loop starts from 1 instead of 0
for (int i = 1; i < valueArray.GetLength(0); i++)
{
Console.WriteLine(valueArray.GetLength(0) + ":" + valueArray.GetLength(1));
for (int k = 0; k < valueArray.GetLength(1); )
{
singleDValue[k] = valueArray[i+1, ++k];
}
dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges);
}

Answer 4

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
namespace trg.satmap.portal.ParseAgentSkillMapping
{
class ConvertXLStoDT
{
private StringBuilder errorMessages;
public StringBuilder ErrorMessages
{
get { return errorMessages; }
set { errorMessages = value; }
}
public ConvertXLStoDT()
{
ErrorMessages = new StringBuilder();
}
public System.Data.DataTable XLStoDTusingInterOp(string FilePath)
{
#region Excel important Note.
/*
* Excel creates XLS and XLSX files. These files are hard to read in C# programs.
* They are handled with the Microsoft.Office.Interop.Excel assembly.
* This assembly sometimes creates performance issues. Step-by-step instructions are helpful.
*
* Add the Microsoft.Office.Interop.Excel assembly by going to Project -> Add Reference.
*/
#endregion
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
System.Data.DataTable dt = new System.Data.DataTable(); //Creating datatable to read the content of the Sheet in File.
try
{
excelApp = new Microsoft.Office.Interop.Excel.Application(); // Initialize a new Excel reader. Must be integrated with an Excel interface object.
//Opening Excel file(myData.xlsx)
workbook = excelApp.Workbooks.Open(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range excelRange = ws.UsedRange; //gives the used cells in sheet
ws = null; // now No need of this so should expire.
//Reading Excel file.
object[,] valueArray = (object[,])excelRange.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
excelRange = null; // you don''t need to do any more Interop. Now No need of this so should expire.
dt = ProcessObjects(valueArray);
}
catch (Exception ex)
{
ErrorMessages.Append(ex.Message);
}
finally
{
#region Clean Up
if (workbook != null)
{
#region Clean Up Close the workbook and release all the memory.
workbook.Close(false, FilePath, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
#endregion
}
workbook = null;
if (excelApp != null)
{
excelApp.Quit();
}
excelApp = null;
#endregion
}
return (dt);
}
/// <summary>
/// Scan the selected Excel workbook and store the information in the cells
/// for this workbook in an object[,] array. Then, call another method
/// to process the data.
/// </summary>
private void ExcelScanIntenal(Microsoft.Office.Interop.Excel.Workbook workBookIn)
{
//
// Get sheet Count and store the number of sheets.
//
int numSheets = workBookIn.Sheets.Count;
//
// Iterate through the sheets. They are indexed starting at 1.
//
for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
{
Worksheet sheet = (Worksheet)workBookIn.Sheets[sheetNum];
//
// Take the used range of the sheet. Finally, get an object array of all
// of the cells in the sheet (their values). You can do things with those
// values. See notes about compatibility.
//
Range excelRange = sheet.UsedRange;
object[,] valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
//
// Do something with the data in the array with a custom method.
//
ProcessObjects(valueArray);
}
}
private System.Data.DataTable ProcessObjects(object[,] valueArray)
{
System.Data.DataTable dt = new System.Data.DataTable();
#region Get the COLUMN names
for (int k = 1; k <= valueArray.GetLength(1); k++)
{
dt.Columns.Add((string)valueArray[1, k]); //add columns to the data table.
}
#endregion
#region Load Excel SHEET DATA into data table
object[] singleDValue = new object[valueArray.GetLength(1)];
//value array first row contains column names. so loop starts from 2 instead of 1
for (int i = 2; i <= valueArray.GetLength(0); i++)
{
for (int j = 0; j < valueArray.GetLength(1); j++)
{
if (valueArray[i, j + 1] != null)
{
singleDValue[j] = valueArray[i, j + 1].ToString();
}
else
{
singleDValue[j] = valueArray[i, j + 1];
}
}
dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges);
}
#endregion
return (dt);
}
}
}