Как открыть большой excel файл

Argument ‘Topic id’ is null or empty

Сейчас на форуме

Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Работа с большими файлами экселя

Что такое большой файл? Ну так чтобы реально большой? В бытность свою я думал, что это файлик на 50-60 тыс строк записей. И оставался я бы в таком неведении до сих пор, но пришлось выполнять один проект, в котором надо было работать с файлами на 600-800 тыс строк. Хождение по мукам — под катом:

Что сначала

А сначала, друзья мои, ринулись мы в самое простое, что можно придумать. Interop.Excell, и все дела. Казалось. Ага, щаз. Как показали тестовые испытания, данный способ открытия приводил к тому, что за час было прочитано 200 тыс строк экселя, приложение активно потребляло оперативку, и раздвигало плечами остальные процессы на машине. Кончилось все ожидаемо, но следственный эксперимент надо было довести до конца — на 260 тысячах приложение свалилось в OutOfMemory на машине с 4 Гб. Стало понятно, что в лоб решить проблему не получится

Google it

Сколько нам открытий чудных… Гугль привел, как ни странно, в msdn, где я познакомился с двумя методами открытия очень больших файлов: DOM и SAX. Уж за давностью времен не вспомню, но какой то из них отвалился по причине опостылевшей уже на тот момент OutOfMemory, а второй был совершенно неюзабелен в плане доступа к данным. Почему — читаем ниже.

Из чего же, из чего же

Сделаны наши эксельки. Ни для кого, кто решил копнуть формат чуть глубже, не станет секретом, что в отличие от бинарным xls, xlsx — по сути zip архив с данными. Достаточно поменять расширение ручками и распаковать архив в папку — и мы получим всю внутреннюю структуру документа, что есть не что иное, как набор xml файлов и сопутствующей информации. Как оказалось, в корневом xml нет текстовых данных. Вместо этого мы имеем набор индексов, которые ссылаются на вспомогательный файл, в котором представлены пары «ключ/значение» Одним из вышеприведенных способов открыть то файл можно, но при этом нужно копаться в сопутствующих файлах и вытаскивать из них текстовые значения. Мрак.

И отступила тьма

После долгих мытарств и стенаний родилось следующее:

Наши любимые юзинги, которые некоторые личности забывают указывать:

using System;
using System.Collections. Generic ;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

* This source code was highlighted with Source Code Highlighter .

Собственно, сам код:

public delegate void MessageHave( string message);

public delegate void _DataLoaded( List < string >data);

public delegate void _NewProcent( int col);

public static _DataLoaded DataLoaded;

public static _NewProcent NewProcent;

public static MessageHave MessageHave_Event;

public static void ReadData( object data)
//Приводим объект с переданной парой «имя файла»-«выбранный лист экселя»
var keyValuePair = (KeyValuePair< string , string >)data;
using ( var cnn = new OleDbConnection( @»Provider=Microsoft.ACE.OLEDB.12.0;Data Source #A31515″>@»;Extended Properties #A31515″>»Excel 12.0;HDR=No;IMEX=1″ «» )
)
int calc = 1000;
MessageHave_Event( «Открытие соединения провайдера» );
cnn.Open();
try
var cmd = new OleDbCommand( String .Format( «select * from []» , keyValuePair.Value), cnn);
using (OleDbDataReader dr = cmd.ExecuteReader())
var lines = new List < string >();
int > if (dr != null )
while (dr.Read())
string text = «» ;
for ( int i = 0; i < dr.FieldCount; ++i)
if (dr[i] != null )
text += dr[i] + «^» ; //добавляем разделитель между ячейками
else
text += «^» ;
>
lines.Add(text);

id++;
if (id == calc)
NewProcent(id);
calc += 1000;
>
>
DataLoaded(lines);
>
cnn.Close();
>
catch (Exception ex)
MessageHave_Event( «Exception: » + ex.Message);
cnn.Close();
>
>
>

* This source code was highlighted with Source Code Highlighter .

Код показал производительность порядка 15-20 минут на файлах в 600-800 тыс строк записей.

Если кому то реализация покажется кривой — сильно не пинать 🙂 Выслушаю все комментарии

Что делать, если набор данных слишком велик для сетки Excel

Если вы открыли файл с большим набором данных в Excel, например файл с разделителями (.txt) или файл с разделителями-запятыми (.csv), возможно, вы видите предупреждающее сообщение «Этот набор данных слишком велик для сетки Excel. Если вы сохраните эту книгу, вы потеряете данные, которые не были загружены.«Это означает, что набор данных превышает количество строк или столбцов, доступных в Excel, поэтому некоторые данные не были загружены.

Важно принять дополнительные меры предосторожности, чтобы избежать потери данных:

Откройте файл в Excel для пк с помощью команды Получить данные. Если у вас есть приложение Excel для пк, вы можете использовать Power Query для загрузки полного набора данных и анализа его с помощью сводных таблиц.
Не сохраняйте файл в Excel . Если сохранить исходный файл, вы потеряете все данные, которые не были загружены. Помните, что это также неполный набор данных.
Сохранить усеченную копию . Если вам нужно сохранить файл, перейдите в раздел Файл >Сохранить копию. Затем введите другое имя, которое ясно, что это усеченная копия исходного файла.

Как открыть набор данных, превышающий ограничения сетки Excel

Использование Excel для ПК означает, что вы можете импортировать файл с помощью команды Получить данные для загрузки всех данных. Хотя данные по-прежнему не отображаются больше, чем количество строк и столбцов в Excel, полный набор данных существует, и вы можете проанализировать его без потери данных.

Откройте пустую книгу в Excel.
Перейдите на вкладку Данные >Из текста или CSV > найдите файл и выберите Импорт. В диалоговом окне предварительного просмотра выберите Загрузить в. >отчет сводной таблицы.
После загрузки используйте список полей для упорядочения полей в сводной таблице. Сводная таблица будет работать со всем набором данных для сводных данных.
Вы также можете сортировать данные в сводной таблице или фильтровать данные в сводной таблице.

Читать:

Как обновить версию php ubuntu

Дополнительные сведения об ограничениях форматов файлов Excel

При использовании Excel важно отметить, какой формат файла вы используете. Формат файла .xls имеет ограничение в 65 536 строк на каждом листе, а формат файла .xlsx имеет ограничение в 1 048 576 строк на лист. Дополнительные сведения см. в разделе Форматы файлов, поддерживаемые вспецификациях и ограничениях Excel и Excel.

Чтобы предотвратить достижение лимита в Excel, убедитесь, что используется формат .xlsx вместо формата .xls, чтобы воспользоваться преимуществами гораздо большего ограничения. Если вы знаете, что набор данных превышает ограничение .xlsx, используйте альтернативные обходные пути, чтобы открыть и просмотреть все данные.

Совет: Убедитесь, что все данные импортированы при открытии набора данных в Excel. Вы можете проверить количество строк или столбцов в исходном файле, а затем подтвердить его соответствие в Excel. Для этого выделите всю строку или столбец и просмотрите счетчик в строке состояния в нижней части Excel.

Каким способом открыть файл Excel

Основными операциями при работе с любыми файлами документов являются операции открытия существующего файла, сохранения отредактированного доку-мента и закрытия файла. В этом разделе мы рассмотрим особенности работы с файлами рабочих книг.

Рабочая книга Excel представляет собой файл в формате Microsoft Excel. Для того чтобы работать с сохраненной ранее рабочей книгой, необходимо открыть файл, содержащий эту рабочую книгу. Такой файл может находиться как в текущей папке, так и в любой другой, или на другом диске, или даже в сети. Стандартная рабочая книга Excel имеет одно из двух расширений:

xlsx (если рабочая книга не содержит макросов);
xlsm (если рабочая книга содержит макросы).

Файлы рабочих книг ранних версий Excel (2003 и ранее) имели расширение xls. Смена стандартного расширения связана с применением нового формата файла — Microsoft Office Open XML. В данном статье описываются разнообразные способы открытия файлов Excel как из среды Microsoft Excel, так и из других приложений Windows. Кроме того, обсуждаются методы работы с файлами, созданными с помощью ранних версий Excel.

Для того чтобы открыть рабочую книгу, сохраненную ранее:

Выполните команду Файл → Открыть либо нажмите комбинацию клавиш Ctrl+O. После выполнения любого из этих действий на экране появится диалоговое окно Открытие документа (рис. 1). В средней части окна изображен список файлов, находящихся в текущей папке. Слева расположены значки наиболее часто используемых папок Windows.
Если в текущей папке нужный файл отсутствует, найдите и откройте папку, содержащую искомый файл. Для этого воспользуйтесь раскрывающимся списком Папка, который расположен в верхней части окна. Нажмите кнопку справа от списка, а затем выберите диск, на котором находится файл. При перемещении по дереву каталогов удобно пользоваться кнопкой Переход на один уровень вверх, имеющей вид желтой папки со стрелкой вверх. Еще одна полезная кнопка расположена слева от нее. На ней изображена жирная стрелка влево. Эта кнопка позволит быстро вернуться назад к папкам, открытым ранее.
Выделив нужный файл, сделайте двойной щелчок кнопкой мыши или нажмите кнопку Открыть.

Существует и другой способ. Он медленнее, но зато предоставляет дополнительные возможности. Если вам предстоит решение контрольных работ на заказ, то обратите внимание на небольшую стрелочку справа на кнопке Открыть. Если ее нажать, на экран будет выведено дополнительное меню, содержащее команды Открыть, Открыть для чтения, Открыть как копию, Открыть в обозревателе, Открыть и восстановить.

Рис. 1. Диалоговое окно Открытие документа

Результат выбора первой команды идентичен результату простого нажатия кнопки Открыть — в диалоговом окне будет открыт файл. Вторая команда предназначена для открытия файлов только для чтения. Если выбрать третью команду, Excel откроет не сам файл, а его копию, причем изменения в рабочей книге, открытой таким образом, не повлияют на исходный файл рабочей книги. При сохранении будет сформировано новое имя файла, представляющее собой результат конкатенации двух строк: «Копия» («Copy of») и имени исходного файла. Команда Открыть в обозревателе дополнительного меню предназначена для открытия файлов при помощи обозревателя (браузера) Интернета. Она работает только с HTML-файлами. Наконец, последняя команда служит для восстановления поврежденных файлов рабочих книг или для извлечения из них данных (формулы и значения), если восстановить рабочую книгу не удается (эта команда появилась в Excel 2002). В случае необходимости открытия группы файлов нужно выделить требуемые файлы, а затем нажать кнопку Открыть. Все файлы должны находиться в одной папке.

Опишем еще один быстрый способ открытия файлов. Если с исходным файлом недавно работали, то его имя может храниться в списке последних открытых файлов. Для того чтобы воспользоваться этим списком, перейдите на вкладку Файл и в открывшемся представлении выберите пункт Последние. Теперь в окне выводятся имена последних двадцати пяти открытых файлов (установка количества запоминаемых имен файлов может меняться). Если имя нужного файла имеется в списке, выберите его. В противном случае воспользоваться этой возможностью не удастся.

Для изменения количества файлов, отображаемых в списке Последние, можно задать нужное значение в поле со счетчиком Число книг в списке последних файлов, расположенном под списком. Для первоначального включения возможности редактирования этого поля установите флажок, расположенный рядом.

Имеется возможность изменять размеры диалогового окна Открытие документа. Обратите внимание на маленький треугольник в правом нижнем углу этого окна — если за-хватить его мышью и потянуть, то размеры окна Открытие документа изменятся.