Los ejemplos a continuación fueron diseñados para examinar un lote de archivos Excel para buscar información dentro de ellos, la información de cada celda era extraída con el propósito de construir un DataTable que permitiera al usuario de la aplicación ejecutar querys sobre los archivos de Excel usando el comando .Select de los DataTable, la idea era construir un aplicativo que permitiera buscar ciertos patrones en los archivos de Excel. En el caso especifico del proyecto, era para buscar donde había que aplicar conversión monetaria para el Bolivar Fuerte.
La técnica que recomiendo es la tercera que utiliza la librería NExcel, la cual permite extraer las formulas del Excel.
La primera y mas fácil de las técnicas, pero mas limitada es utilizar oldb para hacer querys sobre el Excel como si fuera BD y ahí puedes obtener bastante info
Utilizando una cadena como: Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties="Excel 8.0;HDR=NO;";Data Source=
public static DataTable ObtenerExcelDataTableOleDB(string sConexion)
{
DataTable dt = ObtenerDataTable("Excel");
string[] Hojas = ObtenerExcelSheets(sConexion);
foreach(string sHoja in Hojas)
{
dt = MergeDataTable(dt,ObtenerDataTableHojaExcel(sConexion,sHoja));
}
return dt;
}
private static String[] ObtenerExcelSheets(string sConexion)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(sConexion);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheets;
}
catch(Exception ex)
{
throw ex;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}
///
/// Obtiene mediate Oledb el contenido de la hoja de un Excel
///
///
Conexión OLEDB///
La hoja a procesar///
private static DataTable ObtenerDataTableHojaExcel(string sConexion, string sHoja)
{
DataTable dt = ObtenerDataTable("Excel");
DataSet ds = new DataSet();
OleDbConnection objConn = new OleDbConnection(sConexion);
OleDbCommand cmd = new OleDbCommand("SELECT * FROM ["+sHoja+"]",objConn);
OleDbDataAdapter da= new OleDbDataAdapter(cmd);
da.Fill(ds);
int i=1;
string sArchivo = sConexion.Substring(sConexion.IndexOf("Source=")+7,sConexion.IndexOf(".xls")-(sConexion.IndexOf("Source=")+7));
foreach(DataRow dr2 in ds.Tables[0].Rows)
{
int col = 1;
string sFecha = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
foreach(DataColumn dc in ds.Tables[0].Columns)
{
if(dr2[col-1].ToString().Length >0)
{
DataRow dr = dt.NewRow();
dr["Fecha"] = sFecha;
dr["Archivo"] = sArchivo;
dr["Hoja"] = sHoja.Replace("$","");
dr["Fila"] = i;
dr["Columna"] = col;
dr["Valor"] = dr2[col-1].ToString();
dt.Rows.Add(dr);
}
col++;
}
i++;
}
return dt;
}
La segunda alternativa es cargando Excel como un objeto a través de la librería Net.SourceForge.Koogra
public static DataTable ObtenerExcelDataTable (string sArchivo)
{
DataTable dt = ObtenerDataTable(sArchivo);
Net.SourceForge.Koogra.Excel.Workbook wb = new Net.SourceForge.Koogra.Excel.Workbook(sArchivo);
foreach(Worksheet ws in wb.Sheets)
{
for(int r = ws.Rows.FirstRow; r <= ws.Rows.LastRow; ++r)
{
Net.SourceForge.Koogra.Excel.Row row = ws.Rows[(ushort)r];
if(row != null)
{
for(int c = row.Cells.FirstCol; c <= row.Cells.LastCol; ++c)
{
Net.SourceForge.Koogra.Excel.Cell cell = row.Cells[(byte)c];
if(cell != null)
{
DataRow dr = dt.NewRow();
dr["Fecha"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
dr["Archivo"] = sArchivo;
dr["Hoja"] = ws.Name;
dr["Fila"] = r + 1;
dr["Columna"] = c + 1;
dr["Valor"] = cell.Value;
dt.Rows.Add(dr);
}
}
}
}
}
return dt;
}
///
/// Permite obtener un DataTable a partir de Un archivo de Excel con Kogra
///
///
Archivo a consultar/// DataTable con toda la data del Excel
public static DataTable ObtenerExcelDataTable (string sArchivo)
{
DataTable dt = ObtenerDataTable(sArchivo);
Net.SourceForge.Koogra.Excel.Workbook wb = new Net.SourceForge.Koogra.Excel.Workbook(sArchivo);
foreach(Worksheet ws in wb.Sheets)
{
for(int r = ws.Rows.FirstRow; r <= ws.Rows.LastRow; ++r)
{
Net.SourceForge.Koogra.Excel.Row row = ws.Rows[(ushort)r];
if(row != null)
{
for(int c = row.Cells.FirstCol; c <= row.Cells.LastCol; ++c)
{
Net.SourceForge.Koogra.Excel.Cell cell = row.Cells[(byte)c];
if(cell != null)
{
DataRow dr = dt.NewRow();
dr["Fecha"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
dr["Archivo"] = sArchivo;
dr["Hoja"] = ws.Name;
dr["Fila"] = r + 1;
dr["Columna"] = c + 1;
dr["Valor"] = cell.Value;
dt.Rows.Add(dr);
}
}
}
}
}
return dt;
}
///
/// Permite obtener un DataTable a partir de Un archivo de Excel con NExcel
///
///
Archivo a consultar /// DataTable con toda la data del Excel
///
Indica si se deben incluir formulas o no public static object[] ObtenerExcelDataTable (string sArchivo,bool bFormulas, bool bNoGuardarData)
{
object[] Resultado = new object[4];
DataTable dt = ObtenerDataTable(sArchivo);
NExcel.Workbook wb = NExcel.Workbook.getWorkbook(sArchivo);
int iNumHojas = 0;
int iNumFormulas = 0;
int iCeldasData = 0;
foreach(NExcel.Sheet ws in wb.Sheets)
{
iNumHojas++;
for (int r = 0; r < ws.Rows; r++)
{
for(int c=0; c
{
NExcel.Cell cell = null;
try
{
cell = ws.getCell(c,r);
}
catch(Exception ex)
{
if(!bNoGuardarData)
{
DataRow dr = dt.NewRow();
dr["Fecha"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
dr["Archivo"] = sArchivo;
dr["Hoja"] = ws.Name;
dr["Fila"] = r + 1;
dr["Columna"] = c + 1;
dr["Valor"] = "CEODiscovery:Error obteniendo Celda."+ex.Message;
dt.Rows.Add(dr);
}
}
if(cell != null && cell.Contents != null)
{
if(cell.Contents.Length >0)
{
if(!bNoGuardarData)
{
DataRow dr = dt.NewRow();
dr["Fecha"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
dr["Archivo"] = sArchivo;
dr["Hoja"] = ws.Name;
dr["Fila"] = r + 1;
dr["Columna"] = c + 1;
dr["Valor"] = cell.Contents;
dt.Rows.Add(dr);
}
iCeldasData++;
}
if(bFormulas && cell.Type.ToString()=="Numerical Formula")
{
if(!bNoGuardarData)
{
string sFormula = "";
try
{
string sTipo = cell.GetType().ToString();
switch(sTipo)
{
case "NExcel.Read.Biff.NumberFormulaRecord":
NExcel.Read.Biff.NumberFormulaRecord form = ((NExcel.Read.Biff.NumberFormulaRecord)(cell));
sFormula = ":" + form.Formula;
break;
case "NExcel.Read.Biff.SharedNumberFormulaRecord":
NExcel.Read.Biff.SharedNumberFormulaRecord form2 = ((NExcel.Read.Biff.SharedNumberFormulaRecord)(cell));
sFormula = "2:" + form2.Formula;
break;
default:
sFormula = " Formato de formula no procesado";
break;
}
}
catch(Exception ex)
{
sFormula = "Error al leer formula:" + ex.Message;
}
DataRow dr = dt.NewRow();
dr["Fecha"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
dr["Archivo"] = sArchivo;
dr["Hoja"] = ws.Name;
dr["Fila"] = r + 1;
dr["Columna"] = c + 1;
dr["Valor"] = "Formula" + sFormula;;
dt.Rows.Add(dr);
}
iNumFormulas++;
}
}
}
}
}
Resultado[0] = dt;
Resultado[1] = iNumHojas;
Resultado[2] = iNumFormulas;
Resultado[3] = iCeldasData;
return Resultado;
}
//Este método llama a mi otra aplicación que genera un txt de las macros, no se incluyó el código dentro del //mismo proyecto debido a que no se deseaba atar el proyecto principal de búsqueda a las librerías de Excel
//que son necesarias para poder sacar las macros.
private long[] ProcesarMacros(string sArchivo)
{
long[] Respuesta = new long[2];
Respuesta[0] = 0;
Respuesta[1] = 0;
try
{
Process p = new Process();
p.StartInfo.WorkingDirectory = Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath)+"\\" + ConfigurationSettings.AppSettings["VersionOffice"].ToString();
p.StartInfo.FileName = ConfigurationSettings.AppSettings["AplicacionMacros"].ToString();
string sFile = Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath)+"\\Macro"+Path.GetFileName(sArchivo)+".txt";
p.StartInfo.Arguments = "\"" + sArchivo + "\" \""+ sFile +"\"";
p.StartInfo.CreateNoWindow = false;
p.Start();
p.WaitForExit();
if(File.Exists(sFile))
{
object[] Obj = Utilidades.ObtenerArchivoDataTable(sFile,"VB",cbNoGuardarData.Checked);
DataTable dtArchivo = (DataTable)Obj[0];
dtResul = Utilidades.MergeDataTable(dtResul,dtArchivo);
FileInfo fi = new FileInfo(sFile);
Respuesta[0] = fi.Length;
Respuesta[1] = (int)Obj[1];
fi.Delete();
}
if(File.Exists(Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath)+"\\ErrorEnMacro.txt"))
{
using (StreamReader sr = File.OpenText(Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath)+"\\ErrorEnMacro.txt"))
{
string sError = sr.ReadToEnd();
MessageBox.Show("Error procesando Macro:"+sError);
}
File.Delete(Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath)+"\\ErrorEnMacro.txt");
}
}
catch(Exception ex)
{
MessageBox.Show("Macros Error:"+ex.Message);
}
return Respuesta;
}
//Este es el código que saca la info de las macros
///
/// Programa que extrae las macros de un Excel
///
class ExtractExcel
{
///
/// The main entry point for the application. El primer parametro de args debe ser el Excel a procesar
/// El segundo parametro de args debe ser el nombre del archivo a donde se quiere generar el código.
///
[STAThread]
static void Main(string[] args)
{
//args = new string[2];
//args[0] = "c:\\AppAttrib4MS.xls";
//args[1] = "c:\\MacrosExcel.txt";
StreamWriter sw = null;
Excel.Application objXLApp = null;
Excel.Workbook objXLABC = null;
Excel.Workbooks objXLWorkbooks = null;
try
{
sw = new StreamWriter(args[1]);
VBIDE.VBProject objProject;
VBIDE.CodeModule objCode;
object oMissing = System.Reflection.Missing.Value;
int iLine=0;
string sProcName;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
objXLApp = new Excel.Application();
objXLApp.Visible = false;
objXLWorkbooks = objXLApp.Workbooks;
objXLABC = objXLWorkbooks.Open(args[0].ToString(),oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
VBIDE.vbext_ProcKind pk;
objProject = objXLABC.VBProject;
foreach(VBIDE.VBComponent objComponent in objProject.VBComponents)
{
try
{
objCode = objComponent.CodeModule;
iLine = 1;
while (iLine < objCode.CountOfLines)
{
sProcName = objCode.get_ProcOfLine(iLine,out pk);
if(sProcName !=null && sProcName.Length >0)
{
iLine = iLine + objCode.get_ProcCountLines(sProcName, pk);
sw.WriteLine(objCode.get_Lines(1,iLine));
sw.Flush();
}
else
{
iLine = iLine + 1;
}
}
}
catch{}
}
sw.Close();
objXLABC.Close(false,null,null);
objXLApp.Quit();
}
catch(Exception ex)
{
StreamWriter sw2 = new StreamWriter(Path.GetDirectoryName(args[1])+"\\ErrorEnMacro.txt");
sw2.WriteLine("Error:"+ex.Message+" Trace:"+ex.StackTrace);
sw2.Close();
if(sw!=null)
{
sw.Close();
}
if(objXLABC != null)
objXLABC.Close(null,null,null);
if(objXLApp != null)
objXLApp.Quit();
}
}
}
No hay comentarios.:
Publicar un comentario