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

    Что такое большой файл? Ну так чтобы реально большой? В бытность свою я думал, что это файлик на 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=" +
                             keyValuePair.Key + @";Extended Properties=""Excel 12.0;HDR=No;IMEX=1""")
            )
          {
            int calc = 1000;
            MessageHave_Event("Открытие соединения провайдера");
            cnn.Open();
            try
            {
              var cmd = new OleDbCommand(String.Format("select * from [{0}]", keyValuePair.Value), cnn);
              using (OleDbDataReader dr = cmd.ExecuteReader())
              {
                var lines = new List<string>();
                int id = 0;
                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 тыс строк записей.

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

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 43

      +8
      > какой то из них отвалился по причине опостылевшей уже на тот момент OutOfMemory

      Конечно же DOM.
        –2
        «О, сколько нам открытий чудных готовит..» фирма Microsoft!
          +7
          Я по работе сталкивался с IDE Mono Develop, Zend, Flash Develop, Eclipse, и еще парочкой. При всей «открыто-чудности» мелкомягких, Visual Studio — это лучшая IDE, и за это им большое спасибо. Их можно за многое любить и ненавидеть, и вообще тема для очередного холивара, который начинать как то не хочется, но некоторые их решения достойны уважения, как ни крути.
            –3
            Ну зачем расточаться «большим спасибо», они же не шкуродеры. Достаточно 450$).
            Всё-всё — no hollywar.
            0
            В данном случае Microsoft не при чем.
              0
              О сколько тайн прекрасных, Вам предстоит еще познать.
              Фирма Майкрософт тут ни при чем.

              Ну какой интеллектуал запихивает такие данные в эксель?
            +1
            > работать с файлами на 600-800 тыс строк кода

            Ошибки тут нет? VBA-код что ли?
              +3
              Рошаль тут непричем, -x документы мсофиса это ZIP архивы
                0
                Сорри, конечно Вы правы. Поправлю
                +1
                Стандарт, если шо, тут…
                msdn.microsoft.com/en-us/library/dd922181(v=office.12).aspx
                  +23
                  Я тут вообще мимо проходил, но можно из листингов убрать пустые строки?
                    +1
                    Эхх, это ж Source Code Highlighter. Он когда-то работал хорошо, но после очередного обновления Хабра стала выдаваться такая фигня, а поправить некому.
                      +6
                      Тест.
                      <source lang="c#">
                      using System;
                      using System.Collections.Generic;
                      using System.Data;
                      using System.Data.OleDb;
                      using System.IO;
                      using System.Linq;

                      <code>
                      using System;
                      using System.Collections.Generic;
                      using System.Data;
                      using System.Data.OleDb;
                      using System.IO;
                      using System.Linq;

                      Просто для проверки. В предпросмотре всё Ok. Никаких лишних строк, не знаю как получится в итоге :)
                      0
                      Сам не в восторге, знать бы еще как…
                        0
                        попробуйте <source lang="c#">. Или вы его и используете?
                      +8
                      Где-то полгода назад искал решения для экспорта-импорта файлов Excel на сервере.
                      Наткнулся на EPPlus и просто пищал от радости: не требует установки Офиса, простой API, не попалось ни единого бага (ну, на наших задачах — несколько worksheet'ов, несложное форматирование).

                      Проект пятилетний, много серверных модулей и клиентских приложений. К тому времени мы реально использовали в разных местах Interop, OLEDB, CSV и генерацию на основе XML-templates. У всех решений свои проблемы. Мы смотрели в сторону VSTO или Add-in Express'а, но EP+ очень удачно попался под руку.

                      Производительность EP+ заявляют сравнимую с тем что у вас получается — «Can now load 50 000 cells in seconds».

                      Да, конкретно у OLEDB — помимо естественных ограничений представления данных как реляционных и установки офисных DLL-ек (чего нам хотелось избежать на сервере), была еще заморочка с автоматическим распознованием типа данных по первым N (default 8) строкам. Если в первых строках в колонке шли цифры, то появление потом цифро-буквенного значения передавалось как DBNull и надо бвло лезть в реестр чтобы это исправить.
                        0
                        Я для работы с xlsx-файлами пользовался этим кодом. Правда с такими большими файлами не работал, не знаю насчет производительности.
                          +1
                          Юзаю библитеку libxl — офиса не надо, написана на си, работает под все чем можно (iOS даж есть) — весьма шустрая ошибок не встречал.
                          Вообщем рекомендую к использованию.
                            0
                            А как у нее насчет сотен тысяч строк?
                              0
                              Если дадите пример — проверю, сколько времени уйдет на чтение
                                +1
                                к сожалению, я сомневаюсь, что заказчику понравилась бы массовая рассылка боевых данных :)
                                  0
                                  мне хватит только поля допустим с именем, остальные можете заменить на что угодно или убрать
                            0
                            А если в access перенести? Для него такие объемы не проблема.
                              0
                              А если File->SaveAs->CSV, то жизнь вообще прекрасна: StreamReader stmReader=File.OpenText(.....);
                              Такая бредовая идея во время мытарств кстати проскакивала :)))) Но негоже было заставлять залезать и пересохранять документы.

                              Проблема всех проектов (а на самом деле норма), что нам приходится работать с тем, что дают, и никуда от этого не денешься.
                                0
                                Посмотрите возможности импорта в access. Там такие полезные вещи есть как вынос ошибок вставки в отдельную таблицу, настройки шаблона при импорте.
                                И кодировать не надо (для меня это плюс) :)
                                  0
                                  проблема была в том, что задача ставилась как:
                                  «вот те файлы, нужно чтоб в программе указал файл, кнопку бздынь, оно ууууууух, потом тырлинь-окей. А мы чтобы своими делами занимались пока суть да дело»

                                  т.е. процесс автоматизируем максимально. И о каких либо сторонних действиях со стороны оператора речи быть не могло
                              0
                              Не подумайте что лезу не в свое дело, просто действительно интересно откуда могут взяться такие большие Exel файлы и почему именно в этом формате, раз они явно не для чтения/редактирования человеком?
                                0
                                По моей практике такое часто возникает при передаче данных в результате экспорта из какой нибудь БД.
                                  0
                                  Отчеты по продажам одной компании. Отчет за месяц весит примерно 250 метров (по одной стране)
                                    +1
                                    Итаки да, дополню. Такие файлы — настолько удивительная редкость, что большинство попросту не столкнется с такими объемами. Но данная задача позволила нам узнать новое: не только то, что такие файлы существуют в природе, но и то, как написать более быстрый и менее ресурсоемкий загрузчик из экселя. Пока что на других своих проектах я не хочу возращаться к Interop
                                      0
                                      Это обычный вопрос при переносе данных между системами.
                                      Не бакап же отдавать :)
                                        +1
                                        Вот если честно, то за такое канделябром по морде, ИМХО, надо, поскольку ничего Excel-специфичного там не используется, а значит, вполне можно было бы обойтись обычным и кросс-платформенным CSV.
                                      0
                                      Бывают логи в XML и по несколько гигов.
                                      +3
                                      Я обычно в таких случаях сохранял как CSV, а дальше — по старинке.
                                        0
                                        про CSV отписался выше — стояла задача «одну кнопочку, чтобы было красиво»
                                        0
                                        Когда мне нужно было такое, я просто распарсил SpreadsheetML. Стандарт — открытый. Опыт у меня уже был (так парсил и ворд и ООо документ). Писать — пара тыщ строк кода, пара дней.
                                          0
                                          Это был последний вариант, если бы выложенный не сработал
                                            0
                                            Provider=Microsoft.ACE.OLEDB.12.0 — ещё хорошо бы упомянуть, откуда есть берётся этот провайдер БД (стандарный, с офисом ставится, отдельный).
                                          0
                                          4Гб оперативки и OutOfMemory никак не связаны — заканчивается 2Гб виртуального адресного пространства процесса.
                                          Функция NewProcent очень порадовала =)

                                          А Interop — штука для неторопливых, но иногда это единственный вариант. Делал через неё раскраску файлов (regexp'ами) — за несколько часов 40k строк отчёта переварила.
                                            0
                                            Когда дело доходит до форматирования — большинство сторонних открытых решений действительно не подходят. Мы в нашем софте в итоге, устав от тормозов и глюков особенностей Interop, обзавелись вот этой штукой. В работе с ней тоже есть нюансы, но в целом всё сильно упростилось.
                                            0
                                            А пробовали Open XML SDK?
                                              0
                                              В сторону NPOI не смотрели? Установки не требует, пара длл-ок и все. По скорости все устраивает (60к строк в xls пишет моментально), хотя настолько большие файлы как у автора обрабатывать не приходилось.
                                                0
                                                >>text +=
                                                конкатенация строк в цикле зло!

                                                Only users with full accounts can post comments. Log in, please.