c# - seleccionar - obtener nombre de hoja excel vba
Usando Excel OleDb para obtener nombres de hojas EN ORDEN DE HOJA (10)
Estoy usando OleDb para leer de un libro de Excel con muchas hojas.
Necesito leer los nombres de las hojas, pero los necesito en el orden en que están definidos en la hoja de cálculo; entonces si tengo un archivo que se ve así;
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
/__GERMANY__//__UK__//__IRELAND__/
Entonces necesito obtener el diccionario
1="GERMANY",
2="UK",
3="IRELAND"
He intentado usar OleDbConnection.GetOleDbSchemaTable() , y eso me da la lista de nombres, pero los ordena alfabéticamente. La ordenación alfa significa que no sé a qué número de hoja corresponde un nombre particular. Entonces lo entiendo;
GERMANY, IRELAND, UK
que ha cambiado el orden de UK e IRELAND .
La razón por la que necesito que se clasifique es porque tengo que dejar que el usuario elija un rango de datos por nombre o índice; pueden solicitar "todos los datos de ALEMANIA a IRLANDA" o "datos de la hoja 1 a la hoja 3".
Cualquier idea sería muy apreciada.
si pudiera usar las clases de interoperabilidad de la oficina, sería sencillo. Desafortunadamente, no puedo porque las clases de interoperabilidad no funcionan de manera confiable en entornos no interactivos como los servicios de Windows y los sitios ASP.NET, así que necesitaba usar OLEDB.
¿No puedes pasar las hojas de 0 a Count of names -1? de esa manera, debes obtenerlos en el orden correcto.
Editar
Noté a través de los comentarios que hay muchas preocupaciones sobre el uso de las clases Interop para recuperar los nombres de las hojas. Por lo tanto, aquí hay un ejemplo usando OLEDB para recuperarlos:
/// <summary>
/// This method retrieves the excel sheet names from
/// an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
private String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// 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++;
}
// Loop through all of the sheets if you want too...
for(int j=0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
}
return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}
Extraído del Article sobre CodeProject.
Como el código anterior no cubre los procedimientos para extraer la lista del nombre de la hoja para Excel 2007, el siguiente código será aplicable tanto para Excel (97-2003) como para Excel 2007:
public List<string> ListSheetInExcel(string filePath)
{
OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
String strExtendedProperties = String.Empty;
sbConnection.DataSource = filePath;
if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
{
sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
}
else if (Path.GetExtension(filePath).Equals(".xlsx")) //for 2007 Excel file
{
sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
}
sbConnection.Add("Extended Properties",strExtendedProperties);
List<string> listSheet = new List<string>();
using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
{
conn.Open();
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains ''_xlnm#_FilterDatabase'' or sheet name(i.e. sheet name always ends with $ sign)
{
listSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
}
return listSheet;
}
La función anterior devuelve la lista de hojas, en particular, el archivo de Excel para ambos tipos de excel (97,2003,2007).
De otra manera:
un archivo xls (x) es solo una colección de archivos * .xml almacenados en un contenedor * .zip. descomprima el archivo "app.xml" en la carpeta docProps.
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">
<TotalTime>0</TotalTime>
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
-<HeadingPairs>
-<vt:vector baseType="variant" size="2">
-<vt:variant>
<vt:lpstr>Arbeitsblätter</vt:lpstr>
</vt:variant>
-<vt:variant>
<vt:i4>4</vt:i4>
</vt:variant>
</vt:vector>
</HeadingPairs>
-<TitlesOfParts>
-<vt:vector baseType="lpstr" size="4">
<vt:lpstr>Tabelle3</vt:lpstr>
<vt:lpstr>Tabelle4</vt:lpstr>
<vt:lpstr>Tabelle1</vt:lpstr>
<vt:lpstr>Tabelle2</vt:lpstr>
</vt:vector>
</TitlesOfParts>
<Company/>
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>14.0300</AppVersion>
</Properties>
El archivo es un archivo alemán (Arbeitsblätter = worksheets). Los nombres de la tabla (Tabelle3, etc.) están en el orden correcto. Solo necesita leer estas etiquetas;)
Saludos
Esto funcionó para mí. Robado desde aquí: ¿cómo se obtiene el nombre de la primera página de un libro de Excel?
object opt = System.Reflection.Missing.Value;
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(WorkBookToOpen,
opt, opt, opt, opt, opt, opt, opt,
opt, opt, opt, opt, opt, opt, opt);
Excel.Worksheet worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
string firstSheetName = worksheet.Name;
He creado la siguiente función usando la información proporcionada en la respuesta de @kraeppy ( https://.com/a/19930386/2617732 ). Esto requiere utilizar .NET Framework v4.5 y requiere una referencia a System.IO.Compression. Esto solo funciona para los archivos xlsx y no para los archivos xls más antiguos.
using System.IO.Compression;
using System.Xml;
using System.Xml.Linq;
static IEnumerable<string> GetWorksheetNamesOrdered(string fileName)
{
//open the excel file
using (FileStream data = new FileStream(fileName, FileMode.Open))
{
//unzip
ZipArchive archive = new ZipArchive(data);
//select the correct file from the archive
ZipArchiveEntry appxmlFile = archive.Entries.SingleOrDefault(e => e.FullName == "docProps/app.xml");
//read the xml
XDocument xdoc = XDocument.Load(appxmlFile.Open());
//find the titles element
XElement titlesElement = xdoc.Descendants().Where(e => e.Name.LocalName == "TitlesOfParts").Single();
//extract the worksheet names
return titlesElement
.Elements().Where(e => e.Name.LocalName == "vector").Single()
.Elements().Where(e => e.Name.LocalName == "lpstr")
.Select(e => e.Value);
}
}
Me gusta la idea de @deathApril para nombrar las hojas como 1_Germany, 2_UK, 3_IRELAND. También recibí tu problema para hacer este cambio de nombre para cientos de hojas. Si no tiene un problema para cambiar el nombre de la hoja, puede usar esta macro para hacerlo por usted. Tomará menos de unos segundos cambiar el nombre de todos los nombres de las hojas. lamentablemente ODBC, OLEDB devuelve el orden del nombre de la hoja por asc. No hay reemplazo para eso. Tienes que usar COM o renombrar tu nombre para estar en el orden.
Sub Macro1()
''
'' Macro1 Macro
''
''
Dim i As Integer
For i = 1 To Sheets.Count
Dim prefix As String
prefix = i
If Len(prefix) < 4 Then
prefix = "000"
ElseIf Len(prefix) < 3 Then
prefix = "00"
ElseIf Len(prefix) < 2 Then
prefix = "0"
End If
Dim sheetName As String
sheetName = Sheets(i).Name
Dim names
names = Split(sheetName, "-")
If (UBound(names) > 0) And IsNumeric(names(0)) Then
''do nothing
Else
Sheets(i).Name = prefix & i & "-" & Sheets(i).Name
End If
Next
End Sub
ACTUALIZACIÓN: Después de leer el comentario de @SidHoland con respecto a BIFF, una idea brilló. Los siguientes pasos se pueden hacer a través del código. No sé si realmente desea hacer eso para obtener los nombres de las hojas en el mismo orden. Avíseme si necesita ayuda para hacerlo a través del código.
1. Consider XLSX as a zip file. Rename *.xlsx into *.zip
2. Unzip
3. Go to unzipped folder root and open /docprops/app.xml
4. This xml contains the sheet name in the same order of what you see.
5. Parse the xml and get the sheet names
ACTUALIZACIÓN: Otra solución: NPOI podría ser útil aquí http://npoi.codeplex.com/
FileStream file = new FileStream(@"yourexcelfilename", FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
{
Console.WriteLine(hssfworkbook.GetSheetName(i));
}
file.Close();
Esta solución funciona para xls. No intenté xlsx.
Gracias,
Esen
No se puede encontrar esto en la documentación real de MSDN, pero un moderador en los foros dijo
Me temo que OLEDB no conserva el orden de las hojas como lo estaban en Excel
Nombres de hoja de Excel en orden de hoja
Parece que esto sería un requisito bastante común de que habría una solución decente.
No veo ninguna documentación que indique que se garantiza que el orden en app.xml sea el orden de las hojas. Es PROBABLEMENTE, pero no de acuerdo con la especificación OOXML.
El archivo workbook.xml, por otro lado, incluye el atributo sheetId, que sí determina la secuencia: de 1 a la cantidad de hojas. Esto está de acuerdo con la especificación OOXML. workbook.xml se describe como el lugar donde se guarda la secuencia de las hojas.
Así que leer workbook.xml después de que se extraiga del XLSX sería mi recomendación. NO aplicación.xml. En lugar de docProps / app.xml, use xl / workbook.xml y mire el elemento, como se muestra aquí -
`
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303" />
<workbookPr defaultThemeVersion="124226" />
- <bookViews>
<workbookView xWindow="120" yWindow="135" windowWidth="19035" windowHeight="8445" />
</bookViews>
- <sheets>
<sheet name="By song" sheetId="1" r:id="rId1" />
<sheet name="By actors" sheetId="2" r:id="rId2" />
<sheet name="By pit" sheetId="3" r:id="rId3" />
</sheets>
- <definedNames>
<definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">''By song''!$A$1:$O$59</definedName>
</definedNames>
<calcPr calcId="145621" />
</workbook>
`
Prueba esto. Aquí está el código para ordenar los nombres de las hojas.
private Dictionary<int,string> GetExcelSheetNames(string fileName)
{
Excel.Application _excel = null;
Excel.Workbook _workBook = null;
Dictionary<int,string> excelSheets = new Dictionary<int,string>();
try
{
object missing = Type.Missing;
object readOnly = true;
Excel.XlFileFormat.xlWorkbookNormal
_excel = new Excel.ApplicationClass();
_excel.Visible = false;
_workBook = _excel.Workbooks.Open(fileName, 0, readOnly, 5, missing,
missing, true, Excel.XlPlatform.xlWindows, "//t", false, false, 0, true, true, missing);
if (_workBook != null)
{
int index = 0;
foreach (Excel.Worksheet sheet in _workBook.Sheets)
{
// Can get sheet names in order they are in workbook
excelSheets.Add(++index, sheet.Name);
}
}
}
catch (Exception e)
{
return null;
}
finally
{
if (_excel != null)
{
if (_workBook != null)
{
_workBook.Close(false, Type.Missing, Type.Missing);
}
_excel.Application.Quit();
}
_excel = null;
_workBook = null;
}
return excelSheets;
}
Según MSDN, en un caso de hojas de cálculo dentro de Excel, podría no funcionar porque los archivos de Excel no son bases de datos reales. Por lo tanto, no podrá obtener el nombre de las hojas en orden de su visualización en el libro de trabajo.
Codifique para obtener el nombre de las hojas según su apariencia visual usando interoperabilidad:
Agregar referencia a la biblioteca de objetos de Microsoft Excel 12.0.
El siguiente código dará el nombre de las hojas en el orden real almacenado en el libro, no el nombre ordenado.
Código de muestra:
using Microsoft.Office.Interop.Excel;
string filename = "C://romil.xlsx";
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb =excel.Workbooks.Open(filename, missing, missing, missing, missing,missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
ArrayList sheetname = new ArrayList();
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wb.Sheets)
{
sheetname.Add(sheet.Name);
}