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

deberia aprender python

Question

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).

Answer 1

Bueno, si su Excel va a ser tan simple como un archivo CSV como su ejemplo ( https://drive.google.com/file/d/0B_CXvCTOo7_2UVZxbnpRaEVnaFk/view?usp=sharing ), puede intentar abrir el archivo como un archivo zip y leer directamente cada xml:

Intel i5 4460, 12 GB de RAM, SSD Samsung EVO PRO.

Si tiene una gran cantidad de RAM de memoria: este código necesita una gran cantidad de memoria RAM, pero toma 20 ~ 25 segundos. (Necesitas el parámetro -Xmx7g)

package com.devsaki.opensimpleexcel;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.nio.charset.Charset;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.zip.ZipFile;

public class Multithread {

    public static final char CHAR_END = (char) -1;

    public static void main(String[] args) throws IOException, ExecutionException, InterruptedException {
        String excelFile = "C:/Downloads/BigSpreadsheetAllTypes.xlsx";
        ZipFile zipFile = new ZipFile(excelFile);
        long init = System.currentTimeMillis();
        ExecutorService executor = Executors.newFixedThreadPool(4);
        char[] sheet1 = readEntry(zipFile, "xl/worksheets/sheet1.xml").toCharArray();
        Future<Object[][]> futureSheet1 = executor.submit(() -> processSheet1(new CharReader(sheet1), executor));
        char[] sharedString = readEntry(zipFile, "xl/sharedStrings.xml").toCharArray();
        Future<String[]> futureWords = executor.submit(() -> processSharedStrings(new CharReader(sharedString)));

        Object[][] sheet = futureSheet1.get();
        String[] words = futureWords.get();

        executor.shutdown();

        long end = System.currentTimeMillis();
        System.out.println("only read: " + (end - init) / 1000);

        ///Doing somethin with the file::Saving as csv
        init = System.currentTimeMillis();
        try (PrintWriter writer = new PrintWriter(excelFile + ".csv", "UTF-8");) {
            for (Object[] rows : sheet) {
                for (Object cell : rows) {
                    if (cell != null) {
                        if (cell instanceof Integer) {
                            writer.append(words[(Integer) cell]);
                        } else if (cell instanceof String) {
                            writer.append(toDate(Double.parseDouble(cell.toString())));
                        } else {
                            writer.append(cell.toString()); //Probably a number
                        }
                    }
                    writer.append(";");
                }
                writer.append("/n");
            }
        }
        end = System.currentTimeMillis();
        System.out.println("Main saving to csv: " + (end - init) / 1000);
    }

    private static final DateTimeFormatter formatter = DateTimeFormatter.ISO_DATE_TIME;
    private static final LocalDateTime INIT_DATE = LocalDateTime.parse("1900-01-01T00:00:00+00:00", formatter).plusDays(-2);

    //The number in excel is from 1900-jan-1, so every number time that you get, you have to sum to that date
    public static String toDate(double s) {
        return formatter.format(INIT_DATE.plusSeconds((long) ((s*24*3600))));
    }

    public static String readEntry(ZipFile zipFile, String entry) throws IOException {
        System.out.println("Initialing readEntry " + entry);
        long init = System.currentTimeMillis();
        String result = null;

        try (BufferedReader br = new BufferedReader(new InputStreamReader(zipFile.getInputStream(zipFile.getEntry(entry)), Charset.forName("UTF-8")))) {
            br.readLine();
            result = br.readLine();
        }

        long end = System.currentTimeMillis();
        System.out.println("readEntry ''" + entry + "'': " + (end - init) / 1000);
        return result;
    }


    public static String[] processSharedStrings(CharReader br) throws IOException {
        System.out.println("Initialing processSharedStrings");
        long init = System.currentTimeMillis();
        String[] words = null;
        char[] wordCount = "Count=/"".toCharArray();
        char[] token = "<t>".toCharArray();
        String uniqueCount = extractNextValue(br, wordCount, ''"'');
        words = new String[Integer.parseInt(uniqueCount)];
        String nextWord;
        int currentIndex = 0;
        while ((nextWord = extractNextValue(br, token, ''<'')) != null) {
            words[currentIndex++] = nextWord;
            br.skip(11); //you can skip at least 11 chars "/t></si><si>"
        }
        long end = System.currentTimeMillis();
        System.out.println("SharedStrings: " + (end - init) / 1000);
        return words;
    }


    public static Object[][] processSheet1(CharReader br, ExecutorService executorService) throws IOException, ExecutionException, InterruptedException {
        System.out.println("Initialing processSheet1");
        long init = System.currentTimeMillis();
        char[] dimensionToken = "dimension ref=/"".toCharArray();
        String dimension = extractNextValue(br, dimensionToken, ''"'');
        int[] sizes = extractSizeFromDimention(dimension.split(":")[1]);
        br.skip(30); //Between dimension and next tag c exists more or less 30 chars
        Object[][] result = new Object[sizes[0]][sizes[1]];

        int parallelProcess = 8;
        int currentIndex = br.currentIndex;
        CharReader[] charReaders = new CharReader[parallelProcess];
        int totalChars = Math.round(br.chars.length / parallelProcess);
        for (int i = 0; i < parallelProcess; i++) {
            int endIndex = currentIndex + totalChars;
            charReaders[i] = new CharReader(br.chars, currentIndex, endIndex, i);
            currentIndex = endIndex;
        }
        Future[] futures = new Future[parallelProcess];
        for (int i = charReaders.length - 1; i >= 0; i--) {
            final int j = i;
            futures[i] = executorService.submit(() -> inParallelProcess(charReaders[j], j == 0 ? null : charReaders[j - 1], result));
        }
        for (Future future : futures) {
            future.get();
        }

        long end = System.currentTimeMillis();
        System.out.println("Sheet1: " + (end - init) / 1000);
        return result;
    }

    public static void inParallelProcess(CharReader br, CharReader back, Object[][] result) {
        System.out.println("Initialing inParallelProcess : " + br.identifier);

        char[] tokenOpenC = "<c r=/"".toCharArray();
        char[] tokenOpenV = "<v>".toCharArray();

        char[] tokenAttributS = " s=/"".toCharArray();
        char[] tokenAttributT = " t=/"".toCharArray();

        String v;
        int firstCurrentIndex = br.currentIndex;
        boolean first = true;

        while ((v = extractNextValue(br, tokenOpenC, ''"'')) != null) {
            if (first && back != null) {
                int sum = br.currentIndex - firstCurrentIndex - tokenOpenC.length - v.length() - 1;
                first = false;
                System.out.println("Adding to : " + back.identifier + " From : " + br.identifier);
                back.plusLength(sum);
            }
            int[] indexes = extractSizeFromDimention(v);

            int s = foundNextTokens(br, ''>'', tokenAttributS, tokenAttributT);
            char type = ''s''; //3 types: number (n), string (s) and date (d)
            if (s == 0) { // Token S = number or date
                char read = br.read();
                if (read == ''1'') {
                    type = ''n'';
                } else {
                    type = ''d'';
                }
            } else if (s == -1) {
                type = ''n'';
            }
            String c = extractNextValue(br, tokenOpenV, ''<'');
            Object value = null;
            switch (type) {
                case ''n'':
                    value = Double.parseDouble(c);
                    break;
                case ''s'':
                    try {
                        value = Integer.parseInt(c);
                    } catch (Exception ex) {
                        System.out.println("Identifier Error : " + br.identifier);
                    }
                    break;
                case ''d'':
                    value = c.toString();
                    break;
            }
            result[indexes[0] - 1][indexes[1] - 1] = value;
            br.skip(7); ///v></c>
        }
    }

    static class CharReader {
        char[] chars;
        int currentIndex;
        int length;

        int identifier;

        public CharReader(char[] chars) {
            this.chars = chars;
            this.length = chars.length;
        }

        public CharReader(char[] chars, int currentIndex, int length, int identifier) {
            this.chars = chars;
            this.currentIndex = currentIndex;
            if (length > chars.length) {
                this.length = chars.length;
            } else {
                this.length = length;
            }
            this.identifier = identifier;
        }

        public void plusLength(int n) {
            if (this.length + n <= chars.length) {
                this.length += n;
            }
        }

        public char read() {
            if (currentIndex >= length) {
                return CHAR_END;
            }
            return chars[currentIndex++];
        }

        public void skip(int n) {
            currentIndex += n;
        }
    }


    public static int[] extractSizeFromDimention(String dimention) {
        StringBuilder sb = new StringBuilder();
        int columns = 0;
        int rows = 0;
        for (char c : dimention.toCharArray()) {
            if (columns == 0) {
                if (Character.isDigit(c)) {
                    columns = convertExcelIndex(sb.toString());
                    sb = new StringBuilder();
                }
            }
            sb.append(c);
        }
        rows = Integer.parseInt(sb.toString());
        return new int[]{rows, columns};
    }

    public static int foundNextTokens(CharReader br, char until, char[]... tokens) {
        char character;
        int[] indexes = new int[tokens.length];
        while ((character = br.read()) != CHAR_END) {
            if (character == until) {
                break;
            }
            for (int i = 0; i < indexes.length; i++) {
                if (tokens[i][indexes[i]] == character) {
                    indexes[i]++;
                    if (indexes[i] == tokens[i].length) {
                        return i;
                    }
                } else {
                    indexes[i] = 0;
                }
            }
        }

        return -1;
    }

    public static String extractNextValue(CharReader br, char[] token, char until) {
        char character;
        StringBuilder sb = new StringBuilder();
        int index = 0;

        while ((character = br.read()) != CHAR_END) {
            if (index == token.length) {
                if (character == until) {
                    return sb.toString();
                } else {
                    sb.append(character);
                }
            } else {
                if (token[index] == character) {
                    index++;
                } else {
                    index = 0;
                }
            }
        }
        return null;
    }

    public static int convertExcelIndex(String index) {
        int result = 0;
        for (char c : index.toCharArray()) {
            result = result * 26 + ((int) c - (int) ''A'' + 1);
        }
        return result;
    }
}

Respuesta anterior (No es necesario el parámetro Xms7g, por lo tanto, se necesita menos memoria): se necesita abrir y leer el archivo de ejemplo de aproximadamente 35 segundos (200 MB) con una unidad de disco duro, con SDD tarda un poco menos (30 segundos).

Aquí el código: https://github.com/csaki/OpenSimpleExcelFast.git

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.nio.charset.Charset;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.zip.ZipFile;

public class Launcher {

    public static final char CHAR_END = (char) -1;

    public static void main(String[] args) throws IOException, ExecutionException, InterruptedException {
        long init = System.currentTimeMillis();
        String excelFile = "D:/Downloads/BigSpreadsheet.xlsx";
        ZipFile zipFile = new ZipFile(excelFile);

        ExecutorService executor = Executors.newFixedThreadPool(4);
        Future<String[]> futureWords = executor.submit(() -> processSharedStrings(zipFile));
        Future<Object[][]> futureSheet1 = executor.submit(() -> processSheet1(zipFile));
        String[] words = futureWords.get();
        Object[][] sheet1 = futureSheet1.get();
        executor.shutdown();

        long end = System.currentTimeMillis();
        System.out.println("Main only open and read: " + (end - init) / 1000);


        ///Doing somethin with the file::Saving as csv
        init = System.currentTimeMillis();
        try (PrintWriter writer = new PrintWriter(excelFile + ".csv", "UTF-8");) {
            for (Object[] rows : sheet1) {
                for (Object cell : rows) {
                    if (cell != null) {
                        if (cell instanceof Integer) {
                            writer.append(words[(Integer) cell]);
                        } else if (cell instanceof String) {
                            writer.append(toDate(Double.parseDouble(cell.toString())));
                        } else {
                            writer.append(cell.toString()); //Probably a number
                        }
                    }
                    writer.append(";");
                }
                writer.append("/n");
            }
        }
        end = System.currentTimeMillis();
        System.out.println("Main saving to csv: " + (end - init) / 1000);
    }

    private static final DateTimeFormatter formatter = DateTimeFormatter.ISO_DATE_TIME;
    private static final LocalDateTime INIT_DATE = LocalDateTime.parse("1900-01-01T00:00:00+00:00", formatter).plusDays(-2);

    //The number in excel is from 1900-jan-1, so every number time that you get, you have to sum to that date
    public static String toDate(double s) {
        return formatter.format(INIT_DATE.plusSeconds((long) ((s*24*3600))));
    }

    public static Object[][] processSheet1(ZipFile zipFile) throws IOException {
        String entry = "xl/worksheets/sheet1.xml";
        Object[][] result = null;
        char[] dimensionToken = "dimension ref=/"".toCharArray();
        char[] tokenOpenC = "<c r=/"".toCharArray();
        char[] tokenOpenV = "<v>".toCharArray();

        char[] tokenAttributS = " s=/"".toCharArray();
        char[] tokenAttributT = " t=/"".toCharArray();
        try (BufferedReader br = new BufferedReader(new InputStreamReader(zipFile.getInputStream(zipFile.getEntry(entry)), Charset.forName("UTF-8")))) {
            String dimension = extractNextValue(br, dimensionToken, ''"'');
            int[] sizes = extractSizeFromDimention(dimension.split(":")[1]);
            br.skip(30); //Between dimension and next tag c exists more or less 30 chars
            result = new Object[sizes[0]][sizes[1]];
            String v;
            while ((v = extractNextValue(br, tokenOpenC, ''"'')) != null) {
                int[] indexes = extractSizeFromDimention(v);

                int s = foundNextTokens(br, ''>'', tokenAttributS, tokenAttributT);
                char type = ''s''; //3 types: number (n), string (s) and date (d)
                if (s == 0) { // Token S = number or date
                    char read = (char) br.read();
                    if (read == ''1'') {
                        type = ''n'';
                    } else {
                        type = ''d'';
                    }
                } else if (s == -1) {
                    type = ''n'';
                }
                String c = extractNextValue(br, tokenOpenV, ''<'');
                Object value = null;
                switch (type) {
                    case ''n'':
                        value = Double.parseDouble(c);
                        break;
                    case ''s'':
                        value = Integer.parseInt(c);
                        break;
                    case ''d'':
                        value = c.toString();
                        break;
                }
                result[indexes[0] - 1][indexes[1] - 1] = value;
                br.skip(7); ///v></c>
            }
        }
        return result;
    }

    public static int[] extractSizeFromDimention(String dimention) {
        StringBuilder sb = new StringBuilder();
        int columns = 0;
        int rows = 0;
        for (char c : dimention.toCharArray()) {
            if (columns == 0) {
                if (Character.isDigit(c)) {
                    columns = convertExcelIndex(sb.toString());
                    sb = new StringBuilder();
                }
            }
            sb.append(c);
        }
        rows = Integer.parseInt(sb.toString());
        return new int[]{rows, columns};
    }

    public static String[] processSharedStrings(ZipFile zipFile) throws IOException {
        String entry = "xl/sharedStrings.xml";
        String[] words = null;
        char[] wordCount = "Count=/"".toCharArray();
        char[] token = "<t>".toCharArray();
        try (BufferedReader br = new BufferedReader(new InputStreamReader(zipFile.getInputStream(zipFile.getEntry(entry)), Charset.forName("UTF-8")))) {
            String uniqueCount = extractNextValue(br, wordCount, ''"'');
            words = new String[Integer.parseInt(uniqueCount)];
            String nextWord;
            int currentIndex = 0;
            while ((nextWord = extractNextValue(br, token, ''<'')) != null) {
                words[currentIndex++] = nextWord;
                br.skip(11); //you can skip at least 11 chars "/t></si><si>"
            }
        }
        return words;
    }

    public static int foundNextTokens(BufferedReader br, char until, char[]... tokens) throws IOException {
        char character;
        int[] indexes = new int[tokens.length];
        while ((character = (char) br.read()) != CHAR_END) {
            if (character == until) {
                break;
            }
            for (int i = 0; i < indexes.length; i++) {
                if (tokens[i][indexes[i]] == character) {
                    indexes[i]++;
                    if (indexes[i] == tokens[i].length) {
                        return i;
                    }
                } else {
                    indexes[i] = 0;
                }
            }
        }

        return -1;
    }

    public static String extractNextValue(BufferedReader br, char[] token, char until) throws IOException {
        char character;
        StringBuilder sb = new StringBuilder();
        int index = 0;

        while ((character = (char) br.read()) != CHAR_END) {
            if (index == token.length) {
                if (character == until) {
                    return sb.toString();
                } else {
                    sb.append(character);
                }
            } else {
                if (token[index] == character) {
                    index++;
                } else {
                    index = 0;
                }
            }
        }
        return null;
    }

    public static int convertExcelIndex(String index) {
        int result = 0;
        for (char c : index.toCharArray()) {
            result = result * 26 + ((int) c - (int) ''A'' + 1);
        }
        return result;
    }

}

Answer 2

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();
    }
}

Answer 3

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.

Answer 4

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.

Answer 5

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/

Answer 6

La solución de c # y ole todavía tiene un cuello de botella. Así que lo pruebo por c ++ y ado.

_bstr_t connStr(makeConnStr(excelFile, header).c_str());

TESTHR(pRec.CreateInstance(__uuidof(Recordset)));       
TESTHR(pRec->Open(sqlSelectSheet(connStr, sheetIndex).c_str(), connStr, adOpenStatic, adLockOptimistic, adCmdText));

while(!pRec->adoEOF)
{
    for(long i = 0; i < pRec->Fields->GetCount(); ++i)
    {   
        _variant_t v = pRec->Fields->GetItem(i)->Value;
        if(v.vt == VT_R8)
            num[i] = v.dblVal;
        if(v.vt == VT_BSTR)
            str[i] = v.bstrVal;          
        ++cellCount;
    }                                    
    pRec->MoveNext();
}

En la maquina i5-4460 y HDD, encuentro que 500 miles de celdas en xls tomarán 1.5. Pero los mismos datos en xlsx tomarán 2.829. Así que es posible manipular sus datos por debajo de los 30s.

Si realmente necesita menos de 30 años, use la unidad RAM para reducir el IO del archivo. Mejorará significativamente su proceso. No puedo descargar sus datos para probarlos, así que por favor dígame el resultado.

Answer 7

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

Answer 8

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

Answer 9

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 ...

Answer 10

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.

Answer 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()
  ...

Answer 12

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.