Как создать xls файл с заполненными данными?

@kritjara · Регистрация: 22.10.2012

Author24 — интернет-сервис помощи студентам

на данный момент использую следующий код для создания xls файла и последующего его заполнения

VB.NET

Dim ExcelApp As Excel.Application = CreateObject("Excel.Application")
Dim ExcelBook As Excel.Workbook = ExcelApp.Workbooks.Add
Dim xls As Excel.Worksheet = ExcelBook.ActiveSheet
ExcelApp.Visible = True
 
'цикл, заполняющий файл

но этот вариант плох тем что мне необходимо порой заполнять до 3000 страниц
даже если разбивать на этапы и создавать файлы десятками, то все равно это требует много времени и это при том, что у меня сильный комп
переносил этот вариант на рабочий старый комп (2,2 гц проц, 1 гб озу), то там беда полная, более 7 сек уходит на 1 страницу, и чем дальше тем медленнее, бывает так чтобы оформить пятидесятую страницу, уходит более 30 сек

можно ли создать файл так, чтобы при первом его открытии ячейки уже были заполнены?
также прошу подскажите, может есть возможность сделать так чтобы шрифты/объединениеячеек/выравнивание и прочее тоже можно было изменить, не открывая при этом сам файл

@Yury Komar · 29.08.2016, 11:16

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

@kritjara · 29.08.2016, 14:26 **[ТС]**

файл - это счета к оплате
в счете может быть от 1 до 3 услуг, поэтому счета имеют разные размеры (19, 21 или 23 строки)
к тому же неизвестно какой счет будет первым и сколько их будет вообще
поэтому шаблон мне не подходит

я рассматривал вариант с сохранением данных в буфере обмена, разделял ячейки через табуляцию, потом вставлял в отформатированный пустой excel
работает, но если пользователь вставляет не там где надо, или случайно добавляет/удаляет строки то сразу подозреваю будут "вопли/сопли почему не работает"
к тому же в буфере не могу сохранить большой объем информации, и приходится делать это частями
экономия времени мала

Добавлено через 1 час 45 минут
попробовал еще таким образом, но безуспешно

VB.NET

Dim ExcelApp As Excel.Application = CreateObject("Excel.Application")
Dim ExcelBook As Excel.Workbook = ExcelApp.Workbooks.Add
Dim xls As Excel.Worksheet = New Excel.Worksheet
' заполняю xls
ExcelBook.Worksheets.Add(xls)

выдается следующаи ошибка
Дополнительные сведения: Невозможно привести COM-объект типа "Microsoft.Office.Interop.Excel.WorksheetClass" к интерфейсному типу "Microsoft.Office.Interop.Excel._Worksheet". Операция завершилась со сбоем, поскольку вызов QueryInterface COM-компонента для интерфейса с IID "{000208D8-0000-0000-C000-000000000046}" возвратил следующую ошибку: Интерфейс не поддерживается (Исключение из HRESULT: 0x80004002 (E_NOINTERFACE)).

@Yury Komar · 29.08.2016, 14:29

из вашего разъяснения я нн понял структуры самого файла xls... вы б показали пример.

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

@kritjara · 29.08.2016, 14:40 **[ТС]**

честно скажу, я не профи в программировании, самоучка и неопытен
не все понимаю с ваших слов, например

Сообщение от Yury Komar

Если вы используете позднее связывание, то типы переменных не нужно ууазывать, дабы не ошибится.

если можно, то в виде кода это показать

и вот файл, который формируетсяКнига.xls

@Yury Komar · 29.08.2016, 16:09

не объявляя переменных

VB.NET

1
2
3

Dim ExcelApp = CreateObject("Excel.Application")
Dim ExcelBook = ExcelApp.Workbooks.Add
Dim xls = ExcelBook.Worksheets.Add("New Worksheet")

@Sklifosofsky · 29.08.2016, 20:37

Вот есть некоторые нюансы по производительности при заполнении:
Работу ускорит ExcelApp.Visible = False, а соответственно при этом не будет перерендеринга окна. После заполнения можно включить.
Также с ExcelApp.ScreenUpdating = False. При этом окно не пропадает, но только картинка не обновляется. По завершению переключить на True.
Есть быстрый способ заполнения при передачи двумерных массивов. У меня огромные массивы влетали за секунду

VB.NET

  dim arr(2,2) as String
 
        For i As Integer = 0 To 2
            For j As Integer = 0 To 2
                n += 1
                arr(j, i) = n.ToString
            Next
        Next
 
        oSheet.Range("A1").Resize(3, 3).Value  = arr ' вставка начинается с первой ячейки

Попробуйте по экспериментировать с последним вариантом.

Насчет стилей подсказать не могу

@ovva · 03.09.2016, 01:02

Решил проанализировать скорость формирования документа, который представляет собой список счетов (вариант ТС). Выделяется три блока счетов, которые заполняются из таблицы с базовой информацией и копируются в окончательный документ. Рассмотрены два варианта:
1. С использованием Microsoft.Office.Interop.Excel (формат файла xls)
2. С использованием библиотеки EPPlus (формат файла xlsx)
Получены следующие результаты:

Число счетов	Вариант xls (время в сек)	Вариант xlsx (время в сек)
20	1.5	0.3
100	4.5	0.6
500	20	2.3
1000	40	4.6

Замечания:
1. Вполне можно ограничиться использованием одного типа блока (неиспользуемая строка остается пустой).
2. Если полученные результаты не устраивают, то от использования формата Excel придется отказаться и формировать счета непосредственно в программе с выводом на печать (в файл). Это конечно достаточно хлопотно.
3. Окончательную компиляцию выполняйте в конфигурации Release.

@kritjara · 03.09.2016, 16:01 **[ТС]**

интересный вариант заполнения

тут же остается вопрос, каким способом вы определяли как должен выглядеть счет?
какие и сколько видов услуг? как быть если в счет попадает одноразовая услуга с другим названием?
как быть если услуг более 5 видов? создавать новые готовые блоки и для каждого раза новые (потому что разные услуги)?

и глядя на тот факт что 3000 листов формируются за 5 минут придется подстраиваться под именно этот вариант
в связи с этим прошу Вас ответить еще на пару вопросов
1. за счет чего файл настолько быстро формируется?
2. будет ли работать библиотека на конечной машине (win XP .NET 4.0, MS Office 2003 и 2010)?
3. насколько сильно синтаксис этой библиотеки отличается от VBA?
4. чем лучше эта библиотека, чем та что встроена в VS 2105?

@ovva · 03.09.2016, 18:11

kritjara,
Как я уже писал, в данном случае меня интересовала скорость заполнения документа типа предложенного вами образца. Какие услуги и сколько их может быть, речи не было, как не было и внятной постановки задачи. Исходя из здравого смысла, могу предположить, что источником информации является база данных, в которой и хранится вся информация по видам и количеству услуг привязанных к конкретному клиенту.
В прилагаемых проектах вид счета определяется тремя шаблонами счета (исходя из приложенного вами образца). Если услуг много? Для начала стоит определить число услуг имеющих максимальную частоту среди всех возможных вариантов. Например, максимальное число услуг для одного клиента 10, а с максимальной частотой – 5. тогда я бы завел три блока шаблона счета: с тремя строками в счете, с пятью и с десятью. Напомню, все имена услуг и их количественные характеристики берутся из базы. Если в базе находим клиента, к которому привязаны две услуги то выбираем шаблон 1 и заполняем первые две строки в счете, третья остается пустой и т.д. сканируем базу по всем клиентам и выбираем подходящий шаблон. То, что в счете могут оставаться пустые строки не является криминалом. Если хотите, чтобы таких счетов было меньше увеличьте число шаблонов.
Отвечу на ваши вопросы:
1. См. проект
2. Если речь идет о EPPlus то Net 4.0 вполне достаточно, при формировании файла MSOffice никак не участвует, т.е. Office может вообще отсутствовать. Но для просмотра файла Excel (7, 10) потребуется (но и в Excel2003 этот файл также можно просмотреть).
3. Этот синтаксис определяется Open XML SDK, хотя какие-то аналогии с Excel VBA, наверное, можно провести.
4. Не понял, о какой встроенной в VS библиотеке идет речь.

@kritjara · 04.09.2016, 10:09 **[ТС]**

ovva, правильно ли я вас понял?
под шаблоном имеется ввиду Range отформатированный (с объединенеями ячеек, отрисованными таблицами и заполненной шапкой: реквизиты)
для меня важно динамическое выполнение кода, если я правильно вас понял, то шаблоны можно заставить создаваться кодом

Добавлено через 9 минут

Сообщение от ovva

4. Не понял, о какой встроенной в VS библиотеке идет речь.

Microsoft.Office.Interop.Excel

не буду загадывать хуже или лучше она чем EPPlus, но ваша разница во времени заполнения xls и xlsx может быть изза разницы форматов, одни и те же табличные данные в разных форматах занимают совершенно разное место на диске

какие явные плюсы об этой библиотеке вы можете привести помимо того что установка офиса не требуется?
и работает ли библиотека с .docx? насколько я понял, то нет, если правильно понял

@Yury Komar · 04.09.2016, 10:23

kritjara, Microsoft.Office.Interop.Excel это не встроенная в студию библиотека, и она не работает с самим файлом, а работает с объектом программы Excel, а уже эксель работает с вашим файлом, тоесть вы передаете экселю команды, а он уже их выполняет, а вот EPPlus работает с самим файлом, от этого и скорость, будто вы напрямую читаете строки файла и так далее.

@ovva · 04.09.2016, 13:14

kritjara,
1. Да, см. файл tAccount.xlsx лист Blocks.
2. Непонятно что вы вкладываете в "динамическое выполнение кода". Эти блоки шаблонов можно сформировать и в коде, только зачем. Смысла в этом никакого тем более если вдруг шаблон нужно поменять, то на листе Excel это можно сделать без проблем.
3. Yury Komar, этот пункт уже разъяснил.
4. Форматы xls и xlsx различаются принципиально. Файл xlsx это фактически архив. Вы можете установить у такого файла расширение zip, открыть архив и посмотреть, что в него входит.
5. EPPlus с docx не работает. Но есть другие библиотеки, работающие с этими файлами на той же базовой основе (Open XML), например DocX (http://docx.codeplex.com).

@jkrnd · 04.12.2016, 13:40

kritjara, я попробовал создать xls файл указанным Вами способом, но у меня ничего не получилось.
Ссылку COM | Microsoft Office 10.0 Object Library подключил. Наверное нужно ещё что-то. С уже созданным файлом xls работать получается, а вот создать его из проекта Windows Form пока не выходит

@Yury Komar Модератор 4203 / 3287 / 497 Регистрация: 27.01.2014 Сообщений: 6,033
	29.08.2016, 11:16	2
	а если не создавать а хранить шаблон в ресурсах. далее его отрывать и заполнять пачкой а не построчно в цикле. тоесть формировать массив строк и сбрасывать потом в ексель? 0

@Yury Komar Модератор 4203 / 3287 / 497 Регистрация: 27.01.2014 Сообщений: 6,033
	29.08.2016, 14:29	4
	из вашего разъяснения я нн понял структуры самого файла xls... вы б показали пример. Добавлено через 2 минуты а ошибка ууазывает на несоотвеьсвие типов. Если вы используете позднее связывание, то типы переменных не нужно ууазывать, дабы не ошибится. Поограмма сама присвоит соответствующий тип каждой. 0

@kritjara 69 / 59 / 15 Регистрация: 22.10.2012 Сообщений: 314
	29.08.2016, 14:40 [ТС]	5
	честно скажу, я не профи в программировании, самоучка и неопытен не все понимаю с ваших слов, например Сообщение от Yury Komar Если вы используете позднее связывание, то типы переменных не нужно ууазывать, дабы не ошибится. если можно, то в виде кода это показать и вот файл, который формируетсяКнига.xls 0

@kritjara 69 / 59 / 15 Регистрация: 22.10.2012 Сообщений: 314
	03.09.2016, 16:01 [ТС]	9
	интересный вариант заполнения тут же остается вопрос, каким способом вы определяли как должен выглядеть счет? какие и сколько видов услуг? как быть если в счет попадает одноразовая услуга с другим названием? как быть если услуг более 5 видов? создавать новые готовые блоки и для каждого раза новые (потому что разные услуги)? и глядя на тот факт что 3000 листов формируются за 5 минут придется подстраиваться под именно этот вариант в связи с этим прошу Вас ответить еще на пару вопросов 1. за счет чего файл настолько быстро формируется? 2. будет ли работать библиотека на конечной машине (win XP .NET 4.0, MS Office 2003 и 2010)? 3. насколько сильно синтаксис этой библиотеки отличается от VBA? 4. чем лучше эта библиотека, чем та что встроена в VS 2105? 0

@ovva 4407 / 3531 / 843 Регистрация: 02.02.2013 Сообщений: 3,417 Записей в блоге: 2
	03.09.2016, 18:11	10
	kritjara, Как я уже писал, в данном случае меня интересовала скорость заполнения документа типа предложенного вами образца. Какие услуги и сколько их может быть, речи не было, как не было и внятной постановки задачи. Исходя из здравого смысла, могу предположить, что источником информации является база данных, в которой и хранится вся информация по видам и количеству услуг привязанных к конкретному клиенту. В прилагаемых проектах вид счета определяется тремя шаблонами счета (исходя из приложенного вами образца). Если услуг много? Для начала стоит определить число услуг имеющих максимальную частоту среди всех возможных вариантов. Например, максимальное число услуг для одного клиента 10, а с максимальной частотой – 5. тогда я бы завел три блока шаблона счета: с тремя строками в счете, с пятью и с десятью. Напомню, все имена услуг и их количественные характеристики берутся из базы. Если в базе находим клиента, к которому привязаны две услуги то выбираем шаблон 1 и заполняем первые две строки в счете, третья остается пустой и т.д. сканируем базу по всем клиентам и выбираем подходящий шаблон. То, что в счете могут оставаться пустые строки не является криминалом. Если хотите, чтобы таких счетов было меньше увеличьте число шаблонов. Отвечу на ваши вопросы: 1. См. проект 2. Если речь идет о EPPlus то Net 4.0 вполне достаточно, при формировании файла MSOffice никак не участвует, т.е. Office может вообще отсутствовать. Но для просмотра файла Excel (7, 10) потребуется (но и в Excel2003 этот файл также можно просмотреть). 3. Этот синтаксис определяется Open XML SDK, хотя какие-то аналогии с Excel VBA, наверное, можно провести. 4. Не понял, о какой встроенной в VS библиотеке идет речь. 0

@kritjara 69 / 59 / 15 Регистрация: 22.10.2012 Сообщений: 314
	04.09.2016, 10:09 [ТС]	11
	ovva, правильно ли я вас понял? под шаблоном имеется ввиду Range отформатированный (с объединенеями ячеек, отрисованными таблицами и заполненной шапкой: реквизиты) для меня важно динамическое выполнение кода, если я правильно вас понял, то шаблоны можно заставить создаваться кодом Добавлено через 9 минут Сообщение от ovva 4. Не понял, о какой встроенной в VS библиотеке идет речь. Microsoft.Office.Interop.Excel не буду загадывать хуже или лучше она чем EPPlus, но ваша разница во времени заполнения xls и xlsx может быть изза разницы форматов, одни и те же табличные данные в разных форматах занимают совершенно разное место на диске какие явные плюсы об этой библиотеке вы можете привести помимо того что установка офиса не требуется? и работает ли библиотека с .docx? насколько я понял, то нет, если правильно понял 0

@Yury Komar Модератор 4203 / 3287 / 497 Регистрация: 27.01.2014 Сообщений: 6,033
	04.09.2016, 10:23	12
	kritjara, Microsoft.Office.Interop.Excel это не встроенная в студию библиотека, и она не работает с самим файлом, а работает с объектом программы Excel, а уже эксель работает с вашим файлом, тоесть вы передаете экселю команды, а он уже их выполняет, а вот EPPlus работает с самим файлом, от этого и скорость, будто вы напрямую читаете строки файла и так далее. 1

@ovva 4407 / 3531 / 843 Регистрация: 02.02.2013 Сообщений: 3,417 Записей в блоге: 2
	04.09.2016, 13:14	13
	kritjara, 1. Да, см. файл tAccount.xlsx лист Blocks. 2. Непонятно что вы вкладываете в "динамическое выполнение кода". Эти блоки шаблонов можно сформировать и в коде, только зачем. Смысла в этом никакого тем более если вдруг шаблон нужно поменять, то на листе Excel это можно сделать без проблем. 3. Yury Komar, этот пункт уже разъяснил. 4. Форматы xls и xlsx различаются принципиально. Файл xlsx это фактически архив. Вы можете установить у такого файла расширение zip, открыть архив и посмотреть, что в него входит. 5. EPPlus с docx не работает. Но есть другие библиотеки, работающие с этими файлами на той же базовой основе (Open XML), например DocX (http://docx.codeplex.com). 1

@jkrnd 178 / 68 / 13 Регистрация: 22.12.2015 Сообщений: 2,648
	04.12.2016, 13:40	14
	kritjara, я попробовал создать xls файл указанным Вами способом, но у меня ничего не получилось. Ссылку COM \| Microsoft Office 10.0 Object Library подключил. Наверное нужно ещё что-то. С уже созданным файлом xls работать получается, а вот создать его из проекта Windows Form пока не выходит 0