Создание файла excel из excel
Обновлено: 07.07.2024
На практике часто случается, что нужно решить какую-либо задачу, иногда довольно тривиальную с точки зрения пользователя Excel (к примеру изменить фон ячейки в Excel, добавить левую границу и т.д.), а решение в сети/документации искать не хочется или не получается, тогда можно попробовать начать запись макроса, выполнить нужную последовательность действий (изменить фон, добавить границу) , после чего записать макрос и открыть код VBA для анализа кода, названий свойств и методов. Такой способ довольно часто помогает быстро решить проблему.
1. Подключение к Excel
При подключении к Excel не забываем закрывать приложение после работы дабы не наплодить кучу ненужных процессов. Они также могут появиться если во время выполнения модуля выскочит ошибка до того как система дойдет до строк, где происходит закрытие Excel.
2. Запись значения в ячейку
Количество листов книги
3. Шрифт и размер шрифта
4. Ширина колонки
Чтобы скрыть колонку, нужно установить ширину в значение 0.
Получение последней ячейки в используемом диапазоне
Например, при открытии таблицы нам нужно узнать до какой строки и/или до какого столбца присутствуют заполненные ячейки.
4. Установка фильтра
5. Горизонтальное выравнивание ячейки
Режим выравнивания
Константа в Excel
Значение в ISBL
По центру выделения
По правому краю
6. Вертикальное выравнивание ячейки
Режим выравнивания
Константа в Excel
Значение в ISBL
По нижнему краю
По верхнему краю
7. Установка свойства ячейки "переносить по словам"
8. Отключение/включение режима показа предупреждений
9. Формулы
Для формул с русскими названиями нужно использовать не Formula, а FormulaLocal. Таким образом правильными вариантами написания формул будут:
10. Таблица цветов с индексами
11. Установка границы ячейки и тип линии границы
Значения константы XlBordersIndex
Значение в Excel
Значение в ISBL
В виде тире и точек
В виде тире и двойных точек
В виде двойной линии
В виде наклонной пунктирной
Свойство LineStyle (тип линии) может принимать следующие значения
Расположение линии
Значение в Excel
Значение в ISBL
Линия по диагонали сверху – вниз
Линия по диагонали снизу – вверх
Линия, обрамляющая диапазон слева
Линия, обрамляющая диапазон сверху
Линия, обрамляющая диапазон снизу
Линия, обрамляющая диапазон справа
Все вертикальные линии внутри диапазона
Все горизонтальные линии внутри диапазона
Значения константы XlBordersIndex
| Расположение линии | Значение в Excel | Константа в ISBL |
|---|---|---|
| Линия по диагонали сверху вниз | xlDiagonalDown | 5 |
| Линия по диагонали снизу – вверх | xlDiagonalUp | 6 |
| Линия, обрамляющая диапазон слева | xlEdgeLeft | 7 |
| Линия, обрамляющая диапазон сверху | xlEdgeTop | 8 |
| Линия, обрамляющая диапазон снизу | xlEdgeBottom | 9 |
| Линия, обрамляющая диапазон справа | xlEdgeRight | 10 |
| Все вертикальные линии внутри диапазона | xlInsideVertical | 11 |
| Все горизонтальные линии внутри диапазона | xlInsideHorizontal | 12 |
Свойство LineStyle (тип линии) может принимать следующие значения:
| Тип линии | Значение в Excel | Константа в ISBL |
|---|---|---|
| Нет линии | xlLineStyleNone | 0 |
| Непрерывная | xlContinuous | 1 |
| В виде тире и точек | xlDashDot | 4 |
| В виде тире и двойных точек | xlDashDotDot | 5 |
| В виде точек | xlDot | 8 |
| В виде двойной линии | xlDouble | 9 |
| В виде наклонной пунктирной | xlSlantDashDot | 13 |
Получение порядкового номера колонки Excel по буквенному обозначению имени
Однажды столкнулся с тем, что нужно было получить буквенное обозначение колонки Excel по её порядковому номеру. Например, для колонки номер 3 буквенное обозначение равно "C". Так вот ниже приведены две замечательные функции, которые вполне справляются с решением данной проблемы.
Наоборот, получение буквенного обозначения имени колонки по номеру колонки
Книга — это файл, который содержит один или несколько листов для упорядочения данных. Можно создать пустую книгу или воспользоваться шаблоном.
Создание книги
Выберите элемент Пустая книга или нажмите клавиши CTRL+N.
Начните вводить текст.
Создание книги на основе шаблона
Откройте вкладку Файл и выберите пункт Создать.
Откройте шаблон двойным щелчком.
Щелкните и начните вводить текст.
Откройте новую пустую книгу.
Откройте вкладку Файл.
Щелкните Создать.
В разделе Доступные шаблоны дважды щелкните элемент Пустая книга.
Сочетание клавиш. Чтобы быстро создать пустую книгу, вы также можете нажать клавиши CTRL+N.
По умолчанию новые книга содержит три листа, но их количество можно изменить.
При необходимости также можно добавить и удалить листы.
Дополнительные сведения о добавлении и удалении листов см. в статье Вставка и удаление листов.
Создание книги на основе существующей
Откройте вкладку Файл.
Щелкните Создать.
В разделе Шаблоны щелкните Из существующего документа.
В диалоговом окне Создание из имеющейся книги выберите диск, папку или адрес в Интернете, где находится книга, которую требуется открыть.
Щелкните книгу, а затем нажмите кнопку Создать.
Создание книги на основе шаблона
Откройте вкладку Файл.
Щелкните Создать.
Выполните одно из указанных ниже действий.
Чтобы использовать один из образцов шаблонов, доступных в Excel, в разделе Доступные шаблоны щелкните Образцы шаблонов, а затем дважды щелкните нужный шаблон.
Чтобы выбрать один недавно использовавшихся шаблонов, щелкните Последние шаблоны, а затем дважды щелкните нужный шаблон.
Чтобы использовать собственный шаблон, в разделе Мои шаблоны, а затем на вкладке Личные шаблоны в диалоговом окне Создать дважды щелкните нужный шаблон.
Примечание: Вкладка Личные шаблоны содержит созданные вами шаблоны. Если вы не видите шаблон, который хотите использовать, убедитесь, что он находится в правильной папке. Пользовательские шаблоны обычно хранятся в папке Шаблоны (стандартный путь в Windows Vista: C:\Пользователи\имя_пользователя\AppData\Local\Microsoft\Шаблоны; стандартный путь в Microsoft Windows XP: C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Шаблоны).
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
В этой статье приводится информация о подключении, которую вы должны предоставить, и необходимые настройки подключения для импорта данных из Excel и экспорта данных в Excel с помощью SQL Server Integration Services (SSIS).
В следующих разделах содержится вся информация, которая потребуется для успешной работы с приложением Excel из служб SSIS, а также для понимания и устранения распространенных проблем:
Средства, которые можно использовать.
Файлы, которые вам потребуются.
Сведения о подключении, которые необходимо предоставить, и параметры, которые необходимо настроить для загрузки данных в/из Excel с использованием SSIS.
-
в качестве источника данных.
- Укажите имя файла Excel и путь к нему.
- Выберите версию Excel.
- Укажите, содержит ли первая строка имена столбцов.
- Укажите лист или диапазон, который содержит данные.
Известные проблемы и ограничения.
- Проблемы с типами данных.
- Проблемы с импортом.
- Проблемы с экспортом.
Средства, которые можно использовать
Вы можете импортировать и экспортировать данные в Excel с использованием SSIS при помощи любого из следующих инструментов.
SQL Server Integration Services (SSIS) . Создайте пакет SSIS, настроив для него Excel в качестве источника или назначения с помощью диспетчера подключений Excel. (В этой статье не рассматривается создание пакетов SSIS.)
Получение файлов, необходимых для подключения к Excel
Прежде чем импортировать или экспортировать данные в Excel с использованием SSIS, вам может потребоваться скачать компоненты подключения для Excel, если они еще не установлены. По умолчанию компоненты подключений для Excel не устанавливаются.
Примечания по загрузке и установке
Обратите внимание, что необходимо скачать распространяемый пакет ядра СУБД Access 2016, а не среду выполнения Microsoft Access 2016.
Если на компьютере уже установлена 32-разрядная версия Office, необходимо установить 32-разрядную версию компонентов. Кроме того, следует выполнять пакет служб SSIS в 32-разрядном режиме или 32-разрядную версию мастера импорта и экспорта.
При возникновении проблем с установкой распространяемого пакета версии 2016 установите вместо него распространяемый пакет версии 2010 со следующей страницы: Распространяемый пакет ядра СУБД Microsoft Access 2010. (Для Excel 2013 распространяемый пакет не предусмотрен.)
Назначение Excel в качестве источника данных
Сначала необходимо указать, что вам требуется установить подключение к Excel.
В службах SSIS
В службах SSIS создайте диспетчер соединений Excel для подключения к файлу источника или назначения Excel. Создать диспетчер соединений можно несколькими способами:
Щелкните правой кнопкой в области Диспетчеры соединений и выберите команду Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
В меню Службы SSIS выберите Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
Диспетчер соединений необходимо создавать в то время, как вы настраиваете источник Excel или назначение Excel на странице Диспетчер соединений в редакторе источника Excel или в редакторе назначения Excel.
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта на странице Выбор источника данных или Выбор назначения выберите Microsoft Excel в списке Источник данных.
Если вы не видите Excel в списке источников данных, убедитесь, что используется 32-разрядная версия мастера. Компоненты подключений для Excel обычно представляют собой 32-разрядные файлы и не отображаются в 64-разрядной версии мастера.
Файл Excel и путь к нему
В первую очередь необходимо предоставить имя файла Excel и путь к нему. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Введите имя файла и путь к нему в следующем формате:
Для файла на локальном компьютере: C:\TestData.xlsx.
Для файла в общей сетевой папке: \\Sales\Data\TestData.xlsx.
Также можно нажать кнопку Обзор и выбрать электронную таблицу в диалоговом окне Открыть.
Подключить защищенный паролем файл Excel нельзя.
Версия Excel
Далее следует предоставить сведения о версии файла Excel. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Выберите версию Microsoft Excel, в которой был создан файл, или другую совместимую версию. Например, если вам не удалось установить компоненты подключений для версии 2016, вы можете установить компоненты для версии 2010 и выбрать в этом списке вариант Microsoft Excel 2007-2010.
Если у вас установлены старые версии компонентов подключений, вы не сможете выбрать более новые версии Excel в этом списке. Список Версия Excel содержит все версии Excel, поддерживаемые службами SSIS. Наличие элементов в этом списке не означает, что необходимые компоненты подключений были установлены. Например, вариант Microsoft Excel 2016 будет присутствовать в этом списке даже в том случае, если у вас не установлены компоненты подключений для версии 2016.
Первая строка содержит имена столбцов
Если вы импортируете данные из Excel, далее необходимо указать, содержит ли первая строка данных имена столбцов. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS или на странице Выбор источника данных в мастере импорта и экспорта.
- Если этот параметр отключен, поскольку в данных источника отсутствуют имена столбцов, мастер будет использовать в качестве заголовков столбцов значения F1, F2 и т. д.
- Если данные содержат имена столбцов, но этот параметр отключен, мастер импортирует строку имен столбцов как первую строку данных.
- Если данные не содержат имена столбцов, но этот параметр включен, мастер использует первую строку данных источника как имена столбцов. В этом случае первая строка в данных источника не включается в сами данные.
Если вы экспортируете данные из Excel и включили этот параметр, первая строка экспортированных данных будет включать имена столбцов.
Листы и диапазоны
В качестве источника или назначения для данных можно использовать три типа объектов Excel: лист, именованный диапазон или неименованный диапазон ячеек, который задается с помощью адреса.
Лист Чтобы указать лист, добавьте в конец имени листа символ $ и окружите строку разделителями, например [Листt1$] . Также можно выполнить поиск имени, заканчивающегося символом $ , в списке существующих таблиц и представлений.
Именованный диапазон Чтобы указать именованный диапазон, используйте имя диапазона, например Мой_диапазон. Также можно выполнить поиск имени, не заканчивающегося символом $ , в списке существующих таблиц и представлений.
Неименованный диапазон Чтобы указать диапазон ячеек, которым не были заданы имена, добавьте символ $ после имени листа, добавьте спецификацию диапазона и окружите строку разделителями, например [Лист1$A1:B4] .
Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:
В службах SSIS
В службах SSIS на странице Диспетчер соединений в редакторе источника Excel или редакторе назначения Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, выберите Таблица или представление в разделе Режим доступа к данным. Затем выберите лист или именованный диапазон в списке Имя листа Excel.
Чтобы использовать неименованный диапазон, который задается по адресу, выберите Команда SQL в разделе Режим доступа к данным. Затем в поле Текст команды SQL введите запрос, аналогичный представленному ниже примеру:
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта выполните одно из следующих действий:
При импорте из Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор копирования таблицы или запроса выберите Скопировать данные из одной или нескольких таблиц или представлений. Затем на странице Выбор исходных таблиц и представлений в столбце Источник укажите исходные листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор копирования таблицы или запроса выберите Написать запрос, указывающий данные для передачи. Затем на странице Определение исходного запроса введите запрос, аналогичный показанному ниже:
При экспорте в Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор исходных таблиц и представлений в столбце Назначение укажите диапазон в следующем формате без использования разделителей: Sheet1$A1:B5 . Мастер автоматически добавит разделители.
После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнить следующие действия на странице Выбор исходных таблиц и представлений мастера:
Проверить сопоставления столбцов между источником и назначением, выбрав команду Изменить сопоставления.
Выполнить предварительный просмотр данных, чтобы убедиться в правильности выбора, с помощью команды Предварительный просмотр.
Проблемы с типами данных
Типы данных
Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все строковые столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы SSIS сопоставляют типы данных Excel следующим образом:
Числовой — с плавающей запятой двойной точности (DT_R8)
Денежный — денежный (DT_CY)
Логический — логический (DT_BOOL)
Дата и время — datetime (DT_DATE)
Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)
Memo — текстовый поток в Юникоде (DT_NTEXT)
Преобразования типов данных и длины по умолчанию
В службах SSIS неявное преобразование типов данных не выполняется. В результате, возможно, потребуется использовать преобразование "Производный столбец" или "Преобразование данных" для явного преобразования данных Excel до их загрузки в назначение, отличное от Excel, либо для преобразования данных из источника, отличного от Excel, до их загрузки в назначение Excel.
Ниже приведены некоторые примеры преобразований, которые могут потребоваться:
Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами в формате с конкретными кодовыми страницами, отличными от Юникода.
Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.
Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.
Если вы используете мастер импорта и экспорта и вам требуется выполнить некоторые из этих преобразований данных, мастер выполнит настройку необходимых преобразований автоматически. В результате, даже если вам требуется использовать пакет служб SSIS, часто бывает полезно создать первичный пакет с помощью мастера импорта и экспорта. В таком случае мастер создаст и настроит диспетчеры соединений, источники, преобразования и назначения автоматически.
Проблемы с импортом
Пустые строки
Если в качестве источника указан лист или диапазон, драйвер считывает непрерывный блок ячеек, начиная с первой непустой ячейки в верхнем левом углу листа или диапазона. В результате данные могут начинаться не со строки 1, однако в данных источника не должны присутствовать пустые строки. Например, пустые строки не допускаются между заголовками столбцов и строками данных, а также после названия в верхней части листа.
Если над данными располагаются пустые строки, вы не сможете выполнить их запрос в качестве листа. В Excel необходимо выбрать диапазон данных, присвоить ему имя и выполнить запрос к именованному диапазону, а не к листу.
Отсутствующие значения
Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных.
Можно изменить поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне "Свойства".
Усеченный текст
Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены.
Чтобы импортировать данные из столбца типа memo без усечения, можно воспользоваться любым из двух способов:
Убедитесь, что столбец типа memo как минимум в одной из выбранных строк содержит значение длиной более 255 символов.
Увеличьте число строк в выборке драйвера, чтобы включить такую строку. Чтобы увеличить количество строк, включаемых в выборку, достаточно увеличить значение TypeGuessRows в следующем разделе реестра:
Проблемы с экспортом
Создание нового файла назначения
В службах SSIS
Создайте диспетчер соединений Excel с именем создаваемого файла Excel и путем к нему. Затем в редакторе назначения Excel в разделе Имя листа Excel выберите Создать, чтобы создать лист назначения. В этот момент службы SSIS создают новый файл Excel с указанным листом.
В мастере импорта и экспорта SQL Server
На странице Выбор назначения нажмите кнопку Обзор. В диалоговом окне Открыть перейдите в папку, в которой требуется создать новый файл Excel, укажите имя этого файла и выберите Открыть.
Экспорт в достаточно большой диапазон
Экспорт длинных текстовых значений
Чтобы успешно сохранять в столбцы Excel строки, имеющие длину более 255 символов, драйвер должен распознать тип данных целевого столбца как memo , а не как string.
- Если в существующей целевой таблице уже содержатся строки данных, то в столбце типа memo в первых нескольких строках, которые проверит драйвер, должен содержаться, по крайней мере, один экземпляр значения, имеющего длину более 255 символов.
См. также
Дополнительные сведения о компонентах и процедурах, описываемых в этой статье, см. в следующих статьях:
но этот вариант плох тем что мне необходимо порой заполнять до 3000 страниц
даже если разбивать на этапы и создавать файлы десятками, то все равно это требует много времени и это при том, что у меня сильный комп
переносил этот вариант на рабочий старый комп (2,2 гц проц, 1 гб озу), то там беда полная, более 7 сек уходит на 1 страницу, и чем дальше тем медленнее, бывает так чтобы оформить пятидесятую страницу, уходит более 30 сек
можно ли создать файл так, чтобы при первом его открытии ячейки уже были заполнены?
также прошу подскажите, может есть возможность сделать так чтобы шрифты/объединениеячеек/выравнивание и прочее тоже можно было изменить, не открывая при этом сам файл
Помощь в написании контрольных, курсовых и дипломных работ здесь
Как программно создать файл .xls.
Можт кто знает как из VB создать *.xls. черканите.
Не появляется форма с заполненными данными
Доброго времени суток! Помогите разобраться в чем проблема, где нехватает? не срабатывает действие.
Перенаправление с формы на таблицу с заполненными данными
Помогите пожалуйста! есть форма! ссылка на фому del ссылка на скачку формы .txt над.
Создать файл xls
Как программно создать файл xls, после создания я буду открыть его . ExcelФайл =.
далее его отрывать и заполнять пачкой а не построчно в цикле.
тоесть формировать массив строк и сбрасывать потом в ексель?
файл - это счета к оплате
в счете может быть от 1 до 3 услуг, поэтому счета имеют разные размеры (19, 21 или 23 строки)
к тому же неизвестно какой счет будет первым и сколько их будет вообще
поэтому шаблон мне не подходит
я рассматривал вариант с сохранением данных в буфере обмена, разделял ячейки через табуляцию, потом вставлял в отформатированный пустой excel
работает, но если пользователь вставляет не там где надо, или случайно добавляет/удаляет строки то сразу подозреваю будут "вопли/сопли почему не работает"
к тому же в буфере не могу сохранить большой объем информации, и приходится делать это частями
экономия времени мала
Добавлено через 1 час 45 минут
попробовал еще таким образом, но безуспешно
Дополнительные сведения: Невозможно привести COM-объект типа "Microsoft.Office.Interop.Excel.WorksheetClass" к интерфейсному типу "Microsoft.Office.Interop.Excel._Worksheet". Операция завершилась со сбоем, поскольку вызов QueryInterface COM-компонента для интерфейса с IID "" возвратил следующую ошибку: Интерфейс не поддерживается (Исключение из HRESULT: 0x80004002 (E_NOINTERFACE)).
из вашего разъяснения я нн понял структуры самого файла xls. вы б показали пример.
Добавлено через 2 минуты
а ошибка ууазывает на несоотвеьсвие типов. Если вы используете позднее связывание, то типы переменных не нужно ууазывать, дабы не ошибится. Поограмма сама присвоит соответствующий тип каждой.
Читайте также: