Работа с большими файлами Excel

Доброго времени суток. Имеется большой файл, сохранённый в ".xlsb", его размер 30Мб (вроде бы не так уж и велик), много страниц, формул и т.д.
При добавлении строки или (иногда) при сохранении пишет что недостаточно системных ресурсов (ОЗУ 3Гб), поискав нашёл пути решения этой проблемы - разбиение на несколько файлов (такой вариант не подходит) и добавление БД, по этой теме мне не удалось найти достаточно информации, т.к. Excel таблица в своём роде тоже БД и по запросу в google ничего толкового я не нашёл. Вопрос - как? и стоит ли игра свечь?

P.S.: Разного рода оптимизации существующего файла уже проводились. + Не исключено что есть другие пути решения проблемы.

0

Да, безусловно стоит. Excel не предназначен для работы с большими объемами информации.

В первую оченедь, вам надо организовать хранение уже имеющихся данных. Для начала вполне подойдет база Access, так как ее проще освоить, хотя по опыту работы с ним и с БД Oracle - особой разницы для Excel нет, он с обемими базами работает одинаково хорошо. При организации базы данных советую обратиться к т.н. "нормальным формам" (тут Гугл вам в помощь). В принципе, рано или поздно вы сами к этому придете, потому что иначе с базой данных очень тяжело работать. Но это произойдет через пару полных перекраиваний структуры с нуля, так что лучше учиться на чужих ошибках

Следующий шаг - настроить получение данных. В принипе, Access уже имеет табличное представление, формы и отчеты, хотя его функционал в этом плане, конечно, значительно хуже, чем у Excel. В любом случае, вам придется освоить язык запросов к базам данных SQL, потому что без него дальше никуда. Access умеет строить простые запросы в графическом виде, но как только вам потребуется что-то более сложное - придется все делать руками. Учтите, что SQL-редактор Access, настолько убог, что Блокнот по сравнению с ним просто межгалактический крейсер. Если у вас получиться ограничиться Access - просто замечательно. Если все-таки необходим Excel - придется настраивать обмен данных.

В Excel есть 2 встроенных способа получения данных из БД - это простая таблица и сводная таблица, связанные с определенным подключением. Первую можно создать с листа "Данные", вторую - с листа "Вставка". Сводная таблица - вещь во многих отношениях почти незаменимая, советую с ней познакомиться.

Ну и следующий шаг - передача данных из Excel в БД. Тут вам, скорее всего, понадобятся UserForms, функционал и внешний вид которых оставляют желать, но большинство задач с их помощью все-таки реализуема.

Здесь есть небольшой пример кода для работы с БД Access.

3

Спасибо за развёрнутый ответ, с SQL знаком, писал на C#, правда давно, с Access никогда не работал, да и не установлен он у меня (а покупать не хочется), БД от Oracle, как Вы понимаете, в таком случае точно не подходит, следовательно возникает вопрос - могу ли я создавать и работать БД Access без самого Access и можно ли использовать mySQL например или что-то бесплатное?

0

Сообщение от Sl12 могу ли я создавать и работать БД Access без самого Access и можно ли использовать mySQL например или что-то бесплатное?

Теоретически вы можете работать с БД Access, но работа с БД без СУБД - это для особых ценителей.

Уверен, что и с mySQL не возникнет никаких проблем.

1

Регулярно работаю с большими файлами, размер "базы" уже 39Mb в формате xls, оптимизации пока не требуется и памяти хватает (1Gb), листов в книге порядка 10-15. Единственный недостаток сейчас - время сохранения увеличилось, но в пределах допустимого. Трансформировать это в базу данных для меня пока не оправдано (высокие трудозатраты).

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

0

Сообщение от mc-black Трансформировать это в базу данных для меня пока не оправдано (высокие трудозатраты).

А потом трудозатраты снизятся? Вы же понимаете, что рано или поздно ваша "база" отбросит копыта и все равно придется переходить на БД.

0

Сообщение от mc-black Регулярно работаю с большими файлами, размер "базы" уже 39Mb в формате xls, оптимизации пока не требуется и памяти хватает (1Gb), листов в книге порядка 10-15. Единственный недостаток сейчас - время сохранения увеличилось, но в пределах допустимого. Трансформировать это в базу данных для меня пока не оправдано (высокие трудозатраты). Кстати про оптимизацию. Я бы порекомендовал в файле полностью избавиться от внешних ссылок на другие книги Excel, т.к. при любом пересчете листа они в фоновом режме все-таки открываются, а это требует дополнительных ресурсов. И от связей между отдельными листами книги тоже можно избавиться, при помощи макросов, макросами же можно заменить "тяжелые" для вычислений формулы. Плюс не лениться и как-то систематизировать форматирование таблиц, т.к. загаживание листов беститтемными разношерстными форматами может сильно утяжелить книгу.

Писал же что оптимизация уже проведена, однако проблемы это не решило, в формате xls (2003) файл занимал около 90Мб, а то и больше.

Сообщение от Gibboustooth А потом трудозатраты снизятся? Вы же понимаете, что рано или поздно ваша "база" отбросит копыта и все равно придется переходить на БД.

Настал видимо тот момент (мой случай), посмотрим что получится.

0

А потом трудозатраты снизятся? Вы же понимаете, что рано или поздно ваша "база" отбросит копыта и все равно придется переходить на БД.

Уже давно бы отбросила! Но я базу обнуляю каждый год, т.е. у меня инфа бъется по годам (а больше и не надо). Плюс у меня есть большой запас на оптимизацию - я могу разделить исходные данные и отчетные формы на 2 книги минимум.

Если б я развел фигни на 50-90 Mb, я бы конечно подумал о БД, особенно мне нравится идея не с одной базой Access, а связка: БД в Assess, а интерфейс к ней - ввод и обработка данных, вывод отчетов - в Excel, как в первом ответе. Но это надо немного заморочиться и сделать!

0

Для меня всё что надо сделать - это таблица, где ячейки, в которой сейчас есть формулы, просто значение связано со значением в БД, ну и при изменении пользователем значения в таблице изменяется значение и в БД.

0

Сообщение от Sl12 Для меня всё что надо сделать - это таблица, где ячейки, в которой сейчас есть формулы, просто значение связано со значением в БД, ну и при изменении пользователем значения в таблице изменяется значение и в БД.

Это не так просто, как вам представляется. Вы когда-нибудь работали с базами данных?

0

Сообщение от Gibboustooth Это не так просто, как вам представляется. Вы когда-нибудь работали с базами данных?

Да, давненько правда было, писал пару сайтов на ASP.NET, программы на C# c БД и несколько Java приложений, знаю что не просто, в 2 этапа - собрать всё в БД и потом связать значения с ячейками. Посмотрим что получится.

0

Сообщение от Gibboustooth При организации базы данных советую обратиться к т.н. "нормальным формам"

Нашел внятное описание с примерами.
3-я форма избавит в будущем от головной боли.

Итак, mySQL установлен и настроен.
А как правильно оформить VBA-кодом запрос к БД mySQL?

Добавлено через 32 минуты
Вот здесь дан оптимальный код?
Не будет подводных камней при работе с такой базой на удаленном серваке?

Я так понял для экспериментов нужно кроме установки серв. части на localhost, еще и устанавливать некий драйвер связи MySQL Connector/ODBC 5.1

Сайт генерации различных вариантов запросов подключения к БД SQL

0

Таблица в примере кривая - не в 3-й нормальной форме, потому что у одной книги может быть несколько авторов.

А, код нормальный (хотя я предпочитаю выполнять запросы INSERT и UPDATE через , а не через ). Так оно мне кажется более правильным.

Подводные камни будут в том, что вам надо будет контролировать действия пользователя на листе Excel. Например, нужно будет запрерить добавлять или удалать столбцы, добавлять данные в любое место листа, а не только в первой свободной строчке. Также вам придется выделить один из столбцов под ID записи в таблице (ее уникальный ключ).
Проверку вводимых данных и выпадающие списки можно, в принципе реализовать с помощью средств самого Excel (Данные -> Проверка данных). Придется, конечно, со списками немного позаморачиваться, но там все решаемо.

1