java python - Cómo abrir un gran archivo Excel de manera eficiente



oc deberia (11)

La mayoría de los lenguajes de programación que funcionan con productos de Office tienen una capa intermedia y esto es generalmente donde está el cuello de botella, un buen ejemplo es usar PIA's / Interop o Open XML SDK.

Una forma de obtener los datos en un nivel inferior (pasando por alto la capa intermedia) es usar un controlador.

150 MB de archivo de Excel de una sola hoja que dura aproximadamente 7 minutos.

Lo mejor que puedo hacer es un archivo de 130 MB en 135 segundos, aproximadamente 3 veces más rápido:

Stopwatch sw = new Stopwatch();
sw.Start();

DataSet excelDataSet = new DataSet();

string filePath = @"c:\temp\BigBook.xlsx";

// For .XLSXs we use =Microsoft.ACE.OLEDB.12.0;, for .XLS we'd use Microsoft.Jet.OLEDB.4.0; with  "';Extended Properties=\"Excel 8.0;HDR=YES;\"";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
    ("select * from [Sheet1$]", conn);
    objDA.Fill(excelDataSet);
    //dataGridView1.DataSource = excelDataSet.Tables[0];
}
sw.Stop();
Debug.Print("Load XLSX tool: " + sw.ElapsedMilliseconds + " millisecs. Records = "  + excelDataSet.Tables[0].Rows.Count);

Win 7x64, Intel i5, 2.3ghz, 8GB ram, SSD250GB.

Si también puedo recomendarle una solución de hardware, intente resolverla con una SSD si está utilizando unidades de disco duro estándar.

Nota: No puedo descargar su hoja de cálculo de Excel porque estoy detrás de un firewall corporativo.

PD. Ver MSDN - La forma más rápida de importar archivos xlsx con 200 MB de datos , el consenso es que OleDB es el más rápido.

PD 2. He aquí cómo puedes hacerlo con Python: http://code.activestate.com/recipes/440661-read-tabular-data-from-excel-spreadsheets-the-fast/

Tengo un archivo de Excel de 150MB de una hoja que dura aproximadamente 7 minutos para abrirse en una máquina muy poderosa usando lo siguiente:

# using python
import xlrd
wb = xlrd.open_workbook(file)
sh = wb.sheet_by_index(0)

¿Hay alguna manera de abrir el archivo de Excel más rápido? Estoy abierto a incluso sugerencias muy extravagantes (como hadoop, spark, c, java, etc.). Idealmente, estoy buscando una manera de abrir el archivo en menos de 30 segundos si eso no es un sueño imposible. Además, el ejemplo anterior usa python, pero no tiene que ser python.

Nota: este es un archivo de Excel de un cliente. No se puede convertir a ningún otro formato antes de que lo recibamos. No es nuestro archivo

ACTUALIZACIÓN: Responda con un ejemplo de código de trabajo que abrirá el siguiente archivo Excel de 200MB en menos de 30 segundos y se recompensará con la recompensa: https://drive.google.com/file/d/0B_CXvCTOo7_2VW9id2VXRWZrbzQ/view?usp=sharing . Este archivo debe tener cadena (col 1), fecha (col 9) y número (col 11).


Have you tried loading the worksheet on demand , which available since version 0.7.1 of xlrd?

To do this you need to pass on_demand=True to open_workbook().

xlrd.open_workbook(filename=None, logfile=<_io.TextIOWrapper name='' mode='w' encoding='UTF-8'>, verbosity=0, use_mmap=1, file_contents=None, encoding_override=None, formatting_info=False, on_demand=False, ragged_rows=False)

Other potential python solutions I found for reading an xlsx file:

  • Read the raw xml in 'xl/sharedStrings.xml' and 'xl/worksheets/sheet1.xml'
  • Try the openpyxl library's Read Only mode which claims too be optimized in memory usage for large files.

    from openpyxl import load_workbook wb = load_workbook(filename='large_file.xlsx', read_only=True) ws = wb['big_data']
    
    for row in ws.rows:
        for cell in row:
            print(cell.value)
    
  • If you are running on Windows you could use PyWin32 and 'Excel.Application'

    import time
    import win32com.client as win32
    def excel():
       xl = win32.gencache.EnsureDispatch('Excel.Application')
       ss = xl.Workbooks.Add()
    ...
    

La biblioteca Python's Pandas se puede usar para contener y procesar sus datos, pero usarla para cargar directamente el archivo .xlsx será bastante lento, por ejemplo, usando read_excel() .

Un enfoque sería usar Python para automatizar la conversión de su archivo en CSV usando Excel y luego usar Pandas para cargar el archivo CSV resultante usando read_csv() . Esto le dará una buena velocidad, pero no menos de 30 segundos:

import win32com.client as win32        
import pandas as pd    
from datetime import datetime    

print ("Starting")
start = datetime.now()

# Use Excel to load the xlsx file and save it in csv format
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(r'c:\full path\BigSpreadsheet.xlsx')
excel.DisplayAlerts = False
wb.DoNotPromptForConvert = True
wb.CheckCompatibility = False

print('Saving')
wb.SaveAs(r'c:\full path\temp.csv', FileFormat=6, ConflictResolution=2) 
excel.Application.Quit()

# Use Pandas to load the resulting CSV file
print('Loading CSV')
df = pd.read_csv(r'c:\full path\temp.csv', dtype=str)

print(df.shape)
print("Done", datetime.now() - start)

Tipos de columna
Los tipos para sus columnas pueden especificarse pasando el dtype y los converters y parse_dates :

df = pd.read_csv(r'c:\full path\temp.csv', dtype=str, converters={10:int}, parse_dates=[8], infer_datetime_format=True)

También debe especificar infer_datetime_format=True , ya que esto acelerará en gran medida la conversión de la fecha.

nfer_datetime_format : boolean, default False

Si True y parse_dates están habilitados, pandas intentará inferir el formato de las cadenas de fecha y hora en las columnas, y si se puede inferir, cambie a un método más rápido para analizarlas. En algunos casos, esto puede aumentar la velocidad de análisis en 5-10x.

También agregue dayfirst=True si las fechas están en la forma DD/MM/YYYY .

Columnas selectivas
Si realmente solo necesita trabajar en las columnas 1 9 11 , entonces podría reducir aún más los recursos al especificar usecols=[0, 8, 10] siguiente manera:

df = pd.read_csv(r'c:\full path\temp.csv', dtype=str, converters={10:int}, parse_dates=[1], dayfirst=True, infer_datetime_format=True, usecols=[0, 8, 10])

El marco de datos resultante solo contendría esas 3 columnas de datos.

Unidad RAM
Usar una unidad RAM para almacenar el archivo CSV temporal acelerará aún más el tiempo de carga.

Nota: Esto supone que está usando una PC con Windows con Excel disponible.


Otra forma que debería mejorar en gran medida el tiempo de carga / operación es una RAMDrive

crea una RAMDrive con suficiente espacio para tu archivo y un 10% ... 20% de espacio extra ...
Copie el archivo para la RAMDrive ...
Cargue el archivo desde allí ... dependiendo de su disco y sistema de archivos, la mejora de velocidad debe ser enorme ...

Mi favorito es el kit de herramientas de IMDisk
( https://sourceforge.net/projects/imdisk-toolkit/ ) Aquí tiene una poderosa línea de comando para guiar todo ...

También recomiendo SoftPerfect ramdisk
( http://www.majorgeeks.com/files/details/softperfect_ram_disk.html )

pero eso también depende de tu sistema operativo ...


Parece que es difícil de lograr en Python en absoluto. Si desempaquetamos un archivo de datos de hoja, tomará todos los 30 segundos necesarios para pasarlo a través del analizador sintáctico SAX iterativo basado en C (utilizando lxml , un contenedor muy rápido sobre libxml2 ):

from __future__ import print_function

from lxml import etree
import time


start_ts = time.time()

for data in etree.iterparse(open('xl/worksheets/sheet1.xml'), events=('start',), 
                            collect_ids=False, resolve_entities=False,
                            huge_tree=True):
    pass

print(time.time() - start_ts)

La salida de muestra: 27.2134890556

Por cierto, el Excel necesita aproximadamente 40 segundos para cargar el libro de trabajo.


Save your excelsheet to a tab delimited file and open it as you'd normally read a plain txt :)

edit: You can then read the file line by line and split the lines at tabs. Get the data columns you need by index.


Me gustaría tener más información sobre el sistema donde está abriendo el archivo ... de todos modos:

busque en su sistema una actualización de Windows llamada
"Complemento de validación de archivo de Office para Office ..."

si lo tienes ... desinstálalo ...
el archivo debería cargar mucho más rápido
especialmente si se carga desde una acción


Estoy usando una estación de trabajo Dell Precision T1700 y el uso de c # pude abrir el archivo y leer su contenido en aproximadamente 24 segundos simplemente usando código estándar para abrir un libro de trabajo utilizando servicios de interoperabilidad. El uso de referencias a la Biblioteca de objetos de Microsoft Excel 15.0 aquí es mi código.

Mis declaraciones de uso:

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

Código para abrir y leer el libro de trabajo:

public partial class MainWindow : Window {
    public MainWindow() {
        InitializeComponent();

        Excel.Application xlApp;
        Excel.Workbook wb;
        Excel.Worksheet ws;

        xlApp = new Excel.Application();
        xlApp.Visible = false;
        xlApp.ScreenUpdating = false;

        wb = xlApp.Workbooks.Open(@"Desired Path of workbook\Copy of BigSpreadsheet.xlsx");

        ws = wb.Sheets["Sheet1"];

        //string rng = ws.get_Range("A1").Value;
        MessageBox.Show(ws.get_Range("A1").Value);

        Marshal.FinalReleaseComObject(ws);

        wb.Close();
        Marshal.FinalReleaseComObject(wb);

        xlApp.Quit();
        Marshal.FinalReleaseComObject(xlApp);

        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

He creado un programa Java de muestra que puede cargar el archivo en ~ 40 segundos mi computadora portátil (Intel i7 4 core, 16 GB RAM).

https://github.com/skadyan/largefile

Este programa usa la biblioteca de POI de Apache para cargar el archivo .xlsx usando la API XSSF SAX .

La implementación de interfaz de devolución de llamada com.stackoverlfow.largefile.RecordHandler se puede utilizar para procesar los datos cargados desde Excel. Esta interfaz define solo un método que toma tres argumentos

  • Nombre de la hoja: cadena, nombre de hoja de Excel
  • número de fila: int, número de fila de datos
  • y data map : Mapa: referencia de celda excel y valor de celda formateado excel

La clase com.stackoverlfow.largefile.Main muestra una implementación básica de esta interfaz que solo imprime el número de fila en la consola.

Actualizar

woodstox analizador woodstox parece tener un mejor rendimiento que el SAXReader estándar. (código actualizado en repo).

Además, para cumplir los requisitos de rendimiento deseados, puede considerar volver a implementar org.apache.poi...XSSFSheetXMLHandler . En la implementación, se puede implementar un manejo más optimizado del valor de cadena / texto y se puede omitir la operación de formateo de texto innecesario.


Logré leer el archivo en aproximadamente 30 segundos usando .NET core y Open XML SDK.

El siguiente ejemplo muestra una lista de objetos que contienen todas las filas y celdas con los tipos coincidentes, admite celdas de fecha, numéricas y de texto. El proyecto está disponible aquí: https://github.com/xferaa/BigSpreadSheetExample/ (Debe funcionar en Windows, Linux y Mac OS y no requiere que se instale Excel o cualquier componente de Excel).

public List<List<object>> ParseSpreadSheet()
{
    List<List<object>> rows = new List<List<object>>();

    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);

        Dictionary<int, string> sharedStringCache = new Dictionary<int, string>();

        int i = 0;
        foreach (var el in workbookPart.SharedStringTablePart.SharedStringTable.ChildElements)
        {
            sharedStringCache.Add(i++, el.InnerText);
        }

        while (reader.Read())
        {
            if(reader.ElementType == typeof(Row))
            {
                reader.ReadFirstChild();

                List<object> cells = new List<object>();

                do
                {
                    if (reader.ElementType == typeof(Cell))
                    {
                        Cell c = (Cell)reader.LoadCurrentElement();

                        if (c == null || c.DataType == null || !c.DataType.HasValue)
                            continue;

                        object value;

                        switch(c.DataType.Value)
                        {
                            case CellValues.Boolean:
                                value = bool.Parse(c.CellValue.InnerText);
                                break;
                            case CellValues.Date:
                                value = DateTime.Parse(c.CellValue.InnerText);
                                break;
                            case CellValues.Number:
                                value = double.Parse(c.CellValue.InnerText);
                                break;
                            case CellValues.InlineString:
                            case CellValues.String:
                                value = c.CellValue.InnerText;
                                break;
                            case CellValues.SharedString:
                                value = sharedStringCache[int.Parse(c.CellValue.InnerText)];
                                break;
                            default:
                                continue;
                        }

                        if (value != null)
                            cells.Add(value);
                    }

                } while (reader.ReadNextSibling());

                if (cells.Any())
                    rows.Add(cells);
            }
        }
    }

    return rows;
}

Ejecuté el programa en una computadora portátil de tres años con unidad SSD, 8 GB de RAM y una CPU Intel Core i7-4710 a 2.50 GHz (dos núcleos) en Windows 10 64 bits.

Tenga en cuenta que aunque abrir y analizar todo el archivo como cadenas tarda un poco menos de 30 segundos, cuando uso objetos como en el ejemplo de mi última edición, el tiempo sube a casi 50 segundos con mi computadora portátil. Probablemente te acercarás a 30 segundos en tu servidor con Linux.

El truco fue usar el enfoque SAX como se explica aquí:

https://msdn.microsoft.com/en-us/library/office/gg575571.aspx


foreach (Suit suit in Enum.GetValues(typeof(Suit)))
{
}

(La respuesta aceptada actual tiene un elenco que no creo que sea necesario (aunque puede que esté equivocado).)





java c# python c++ excel