Vb net создать файл excel
@Mike The "without requiring Excel to be installed" piece has nothing to do with being professional. It's about dependencies. The original text of the question was worded as: "Ideally, I would like open source so I don't have to add any third party dependencies to my code, and I would like to avoid using Excel directly to create the file (using OLE Automation.)" It's unfortunate the question was drastically simplified.
Assuming you were trying to do something sans library or external code, I can't speak for xls file, but for xlsx files, why not start by taking an existing one, renaming it to a zip file and exploring the contents? A little bit of reverse engineering will tell you quite a bit. There are several different xml files and rels files in the various folders and subfolders. Try exploring that and see if it's something you can replicate or see if you can find documentation on the various xml namespaces/schemas.
46 Answers 46
You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:
This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.
It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.
ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.
You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.
There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.
Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)
Here some example code for ExcelLibrary:
Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:
Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.
It should be noted that ExcelLibrary has a lot of performance issues when dealing with large datasets(larger than 5000 rows with lots of columns). Currently doing a heavy modification of the code base at work so we can use it in a project.
I tried ExcelLibrary, all it could do was produce a 'corrupted file', nothing fancy just 3 columns, small file. Excel 2016 wouldn't open. Tried the automatic file generation rom DataTable.
If you are happy with the xlsx format, try my library, EPPlus. It started with the source from ExcelPackage, but since became a total rewrite.
It supports ranges, cell styling, charts, shapes, pictures, named ranges, AutoFilter, and a lot of other stuff.
You have two options:
EPPlus 4, licensed under LGPL (original branch, developed until 2020)
EPPlus 5, licensed under Polyform Noncommercial 1.0.0 (since 2020).
From the EPPlus 5 readme.md:
With the new license EPPlus is still free to use in some cases, but will require a commercial license to be used in a commercial business.
The examples were helpful. I was able to change my code from using Microsoft interop library (horribly slow) to this library (version 4.x) in a couple hours. My benchmark writes a file with two tabs and about 750,000 cells. Using MS interop it took 13 minutes. Using EPPlus it took 10 seconds, a roughly 80x speedup. Very happy!
@JanKällman You should update your CodePlex page to show you've got these methods available: LoadFromCollection
For clarity in this thread, the LGPL allows the software to be linked to without the infective part of the GPL occuring. You only need to open source changes you make to ClosedXml or if you directly put the source code (as opposed to referencing the ClosedXml assemblies) inside of your application then you need to open source your application.
@Paul Chernoch: We populate large Excel sheets with interop very quickly. The secret is to do a bulk update. Create a object [,] block, populate that, then write that matrix to Excel at one time: excelWorksheet.get_Range(range).Value2 = block;
And what about using Open XML SDK 2.0 for Microsoft Office?
Important to note that the DLL for this is just over 5 MB and limited to Office 2007 formats. But certainly the easiest and fastest solution which works for me.
The SDK models the XML into classes, so that each XML tag is mapped to a tag, and then you have to build the class hierarchy (each instance has a collection of child instances/tags) correctly. This means you have to know the XML structure of an Excel file, which is very complicated. It's much easier to use a wrapper such as EPPlus, mentioned above, which simplifies things.
I found Microsoft Open XML SDK's Open XML Writer to be great. Using the solutions above, (Especially Vincent Tom's sample (Poly Math)), it's easy to build a writer that streams through big sets of data, and writes records in a manner similiar and not too much more complex to what you'd do for CSV; but that you're instead writing xml. Open XML is the mindset that Microsoft considers it's new Office formats in. And you can always rename them from .xslx to .zip files if you feel like poking at their XML contents.
I've used with success the following open source projects:
ExcelPackage for OOXML formats (Office 2007)
NPOI for .XLS format (Office 2003). NPOI 2.0 (Beta) also supports XLSX.
Take a look at my blog posts:
A note on NPOI - Row and Column references are zero-based. Does work well for populating an existing template.
You can use OLEDB to create and manipulate Excel files. Check this: Reading and Writing Excel using OLEDB.
EDIT - Some more links:
Can someone confirm if this works when running in x64? I am pretty sure Jet only works if your app is compiled or running in 32-bit mode.
Be very careful with this -- it's a big ugly cludge (for example, sometimes it guesses a column type and discards all the data that does not fit).
One should be very careful if using this method. I've found it very flaky for data that isn't in a perfect format.
As a person who had to use OleDb in a big project, I say STAY AWAY FROM IT! It sometimes is not able to retrieve a cell value just because it couldn't understand the format. It doesn't have a delete operation. It works totally different and unpredictable even with a slightest provider change. I'd say go for a proven commercial solution.
Disclaimer: I own SpreadsheetGear LLC
You have a great product but I think a lot of people here are expecting free solutions. That might explain the down votes.
A few options I have used:
If XLSX is a must: ExcelPackage is a good start but died off when the developer quit working on it. ExML picked up from there and added a few features. ExML isn't a bad option, I'm still using it in a couple of production websites.
Be careful with ExcelPackage if you need to support XLS. I had a hard time with it and eventually switched to ExcelLibrary.
An extremely lightweight option may be to use HTML tables. Just create head, body, and table tags in a file, and save it as a file with an .xls extension. There are Microsoft specific attributes that you can use to style the output, including formulas.
I realize that you may not be coding this in a web application, but here is an example of the composition of an Excel file via an HTML table. This technique could be used if you were coding a console app, desktop app, or service.
It's so ad hoc but it works (not to mention excel issuing a warning on opening) and is so simple, it deserves to have a place as a solution. Though only for showing that you can export an excel file :))
Some people at my organization can't open excel files made this way in Office 2010 and above. Don't know what the problem is, but I had to roll my own OpenXML implementation. (see Sogger's answer)
Watch out for security warnings appearing to your user if you do this: "Warning, the content of the file doesn't match the extension". Particularly alarming when you're doing a download from your bank. I wouldn't rcommend pursuing the approach mentioned in this answer
I tried using this in a project that builds pretty large Excel sheets. Excellent library, but extremely poor in performance. I just did a comparison for the project I'm working on: ClosedXML (v 0.53.3) took 92,489 ms whereas EPPlus (v 2.9.03, for testing - we can't use because it's GPL) took 16,500 ms.
I appreciate that ClosedXML is open source (MIT). At the time I wrote this the LGPL licensed EPPlus project on GitHub is archived and its last release was in January 2019.
You might be impressed if you haven't tried them.
Please be warned of Microsoft's stance on this:
@Ricky B: Also, in my experience with the interop is that it does use excel. Every time we used it, if Excel wasn't installed on the machine, we would get COM exceptions.
With the OLE, even with very careful disposals, it eventually leaks memory or crashes. This is argueably OK for attended applications/ workstations, but for servers is not recommended (MS has a KB stating this). For our server, we just reboot it nightly. Again, that works OK.
I'm coming to this discussion after struggling more than a week on interop, and unless your needs are very simple, this is not gonna work. The support for formatting your spreadsheet is abysmal, which is arguably the reason for generating an .xls file and not just a flat .csv file. For example, have you tried outputting more than 911 characters in a cell, or have you tried setting the width of merged cells in a consistent manner? I have, and I can't tell you how much I hate this crap now. Do yourself a favor and go with one of the free libraries mentioned on this discussion.
Full source code is provided - free of charge - along with instructions, and a demo application.
After adding this class to your application, you can export your DataSet to Excel in just one line of code:
It doesn't get much simpler than that.
And it doesn't even require Excel to be present on your server.
That's partly true: The completely free version will generate a perfect .xlsx file for you, and all source code is provided. If you donate $10 or more to one of those two charities (of which I receive absolutely nothing), then you get a "better" version showing how to do formatting, dates, etc. Given the cost of third-party products, I reckon donating $10 to a good cause instead is well worth it !
You could consider creating your files using the XML Spreadsheet 2003 format. This is a simple XML format using a well documented schema.
You may want to take a look at GemBox.Spreadsheet.
They have a free version with all features but limited to 150 rows per sheet and 5 sheets per workbook, if that falls within your needs.
I haven't had need to use it myself yet, but does look interesting.
Syncfusion Essential XlsIO can do this. It has no dependency on Microsoft office and also has specific support for different platforms.
The whole suite of controls is available for free through the community license program if you qualify (less than 1 million USD in revenue). Note: I work for Syncfusion.
you can also use a third party library like Aspose.
This library has the benefit that it does not require Excel to be installed on your machine which would be ideal in your case.
Yes you can, if you don't mind paying a minimum license fee of $999. Try the MikesKnowledgeBase library. which is $999 cheaper than this !!
The various Office 2003 XML libraries avaliable work pretty well for smaller excel files. However, I find the sheer size of a large workbook saved in the XML format to be a problem. For example, a workbook I work with that would be 40MB in the new (and admittedly more tightly packed) XLSX format becomes a 360MB XML file.
Neither are cheap (500USD and 800USD respectively, I think). but both work independant of Excel itself.
I have written a simple code to export dataset to excel without using excel object by using System.IO.StreamWriter.
Below is the code which will read all tables from dataset and write them to sheets one by one. I took help from this article.
Like the article says though, that's XML that Excel will read rather than actually being an XLS file, which means that it might only work in Excel and not other programs that read spreadsheets. But it's probably better than the equivalent HTML table answers here!
The type it's Open XML but you can only write a .xls file and works perfectly. Take care with the blank spaces in the tags. Use my code refactored below.
OpenXML is also a good alternative that helps avoid installing MS Excel on Server.The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages.
It is a commercial product, but you get the full source if you buy it. So I suppose you could compile it into your assembly if you really wanted to. Otherwise it's just one extra assembly to xcopy - no configuration or installation or anything like that.
(Disclaimer: I work for SoftArtisans, the company that makes OfficeWriter)
Hi this solution is to export your grid view to your excel file it might help you out
No, this generates HTML marked as an Excel file rather than a true Excel file. Yes, Excel itself will open that OK but other programs that consume spreadsheets - including Microsoft's free Excel viewer, for example - won't accept it. You'd do better to create a real Excel file using one of the libraries here.
GridViewExportUtil only for Web. And for Windows Forms, WPF, Console, Service Windows, Unit Test or Addin ?
Or, you could use the Interop .
Here's a way to do it with LINQ to XML, complete with sample code:
It's a little complex, since you have to import namespaces and so forth, but it does let you avoid any external dependencies.
Some 3rd party component vendors like Infragistics or Syncfusion provide very good Excel export capabilities that do not require Microsoft Excel to be installed.
Since these vendors also provide advanced UI grid components, these components are particularly handy if you want the style and layout of an excel export to mimic the current state of a grid in the user interface of your application.
If your export is intended to be executed server side with emphasis on the data to be exported and with no link to the UI, then I would go for one of the free open source options (e.g. ExcelLibrary).
I have previously been involved with projects that attempted to use server side automation on the Microsoft Office suite. Based on this experience I would strongly recommend against that approach.
where sample look like this:
An overview of the process involved is:
I have developed a simple WPF app on github which will run on Windows for this purpose. There is a placeholder class called GeneratedClass where you can paste the generated code. If you go back one version of the file, it will generate an excel file like this:
Excel Library
How to use COM Interop to Create an Excel Spreadsheet
Form the following pictures you can find how to add Excel reference library in your project.
Select Add Reference dialogue from Project menu of your Visual Studio.
Select Microsoft Excel 15.0 Object Library of COM leftside menu and click OK button.
How to create an Excel Document Programmatically
First we have to initialize the Excel application Object.
Before creating new Excel Workbook, you should check whether Excel is installed in your system.
Then create new Workbook
After creating the new Workbook, next step is to write content to worksheet
In the above code we write the data in the Sheet1, If you want to write data in sheet 2 then you should code like this.
Save Excel file (SaveAs() method)t
After write the content to the cell, next step is to save the excel file in your system.
How to properly clean up Excel interop objects
Interop marshaling governs how data is passed in method arguments and return values between managed and unmanaged memory during calls. Most data types have common representations in both managed and unmanaged memory. The interop marshaler handles these types for you. Other types can be ambiguous or not represented at all in managed memory.
It is important to note that every reference to an Excel COM object had to be set to null when you have finished with it, including Cells, Sheets, everything.
Creating an Excel Spreadsheet Programmatically
Note : You have to add Microsoft.Office.Interop.Excel to your source code.
Оба языка поддерживают внедрение сведений о типах, что позволяет развертывать сборки, взаимодействующие с компонентами COM, без предварительного развертывания на компьютере основных сборок взаимодействия (PIA). Дополнительные сведения см. в разделе Пошаговое руководство: внедрению типов из управляемых сборок.
В данном пошаговом руководстве эти возможности показаны в контексте программирования для Microsoft Office, но многие из них могут оказаться полезными и в других ситуациях. В этом пошаговом руководстве вы создадите книгу Excel с помощью надстройки Excel, а затем документ Word со ссылкой на эту книгу. Наконец, вы узнаете, как включать и отключать зависимость PIA.
Предварительные требования
Для выполнения данного пошагового руководства на компьютере должны быть установлены Microsoft Office Excel и Microsoft Office Word.
Отображаемые на компьютере имена или расположения некоторых элементов пользовательского интерфейса Visual Studio могут отличаться от указанных в следующих инструкциях. Это зависит от имеющегося выпуска Visual Studio и используемых параметров. Дополнительные сведения см. в разделе Персонализация среды IDE.
Настройка надстройки Excel
Запустите Visual Studio.
В меню Файл выберите пункт Создать, а затем команду Проект.
в области шаблоны выберите Excel надстройка версии .
Если нужно, в поле Имя введите имя проекта.
В обозревателе решений появится новый проект.
Добавление ссылок
В обозревателе решений щелкните имя проекта правой кнопкой мыши и выберите пункт Добавить ссылку. Откроется диалоговое окно Добавление ссылки.
На вкладке сборки выберите Microsoft. Office. Com. Excel, версия (для получения ключа по номерам версий продуктов Office, см. статью версии Microsoft), в списке имя компонента , а затем нажмите и удерживайте клавишу CTRL и выберите Microsoft. Office. Interop. Word, . Если сборки отсутствуют, может потребоваться проверить, что они установлены и отображаются (см. раздел Практическое руководство. Установка основных сборок взаимодействия Microsoft Office).
Добавление необходимых операторов Imports или директив using
В обозревателе решений щелкните правой кнопкой мыши файл ThisAddIn.vb или ThisAddIn.cs и выберите в контекстном меню команду Просмотреть код.
В верхнюю часть файла с кодом добавьте следующие операторы Imports (Visual Basic) или директивы using , если это еще не сделано.
Создание списка банковских счетов
Замените определение класса Account следующим кодом. В определениях классов используются автоматически реализуемые свойства. Дополнительные сведения см. в разделе Автоматически реализуемые свойства.
Чтобы создать bankAccounts список, содержащий две учетные записи, добавьте следующий код ThisAddIn_Startup в метод в bankAccounts или ThisAddIn_Startup . В объявлениях списков используются инициализаторы коллекций. Дополнительные сведения см. в разделе Инициализаторы коллекций.
Экспорт данных в Excel
В том же самом файле добавьте в класс ThisAddIn следующий метод. Этот метод служит для настройки книги Excel и экспорта данных в нее.
В более ранних версиях этого языка приходилось использовать особый синтаксис.
Разработчики не могут создавать собственные индексированные свойства. Эта возможность поддерживает только использование имеющихся индексированных свойств.
Добавьте в конец метода DisplayInExcel следующий код, чтобы ширина столбца изменялась в соответствии с содержимым.
например, excelApp.Columns[1] возвращает Object , а AutoFit — метод Excel excelApp.Columns[1] . Без типа dynamic необходимо выполнять приведение объекта, возвращаемого excelApp.Columns[1] , к экземпляру Range перед вызовом метода AutoFit .
Дополнительные сведения о внедрении типов взаимодействия см. в подразделах "Поиск ссылки PIA" и "Восстановление зависимости PIA" далее в этом разделе. Дополнительные сведения о dynamic см. в разделе dynamic или Динамическое использование типа Dynamic.
Вызов метода DisplayInExcel
Добавьте следующий код в конец метода ThisAddIn_StartUp . Вызов метода DisplayInExcel содержит два аргумента. Первый аргумент представляет собой имя списка счетов, которые требуется обработать. Второй аргумент — это состоящее из нескольких строк лямбда-выражение, которое определяет, каким образом следует обрабатывать данные. Значения ID и balance для каждого из счетов отображаются в соседних ячейках, а если баланс имеет отрицательное значение, строка отображается красным. Дополнительные сведения см. в разделе Лямбда-выражения.
Чтобы запустить программу, нажмите клавишу F5. Появится книга Excel, содержащая данные о счетах.
Добавление документа Word
Добавьте в конец метода ThisAddIn_StartUp следующий код, чтобы создать документ Word, содержащий ссылку на книгу Excel.
Запуск приложения
- Нажмите клавишу F5 для запуска приложения. Будет запущено приложение Excel, в котором будет открыта таблица, содержащая сведения о двух счетах из списка bankAccounts . Затем будет открыт документ Word, содержащий ссылку на таблицу Excel.
Очистка готового проекта
- В Visual Studio в меню Построение выберите пункт Очистить решение. В противном случае надстройка будет запускаться при каждом открытии Excel на компьютере разработчика.
Поиск ссылки PIA
Запустите приложение снова, но не выбирайте пункт Очистить решение.
Выберите кнопку Пуск. перейдите на Microsoft Visual Studio версию > и откройте командную строку разработчика.
В окне командной строки разработчика для Visual Studio введите команду ildasm , а затем нажмите клавишу ВВОД. Появится окно программы IL DASM.
В меню файл в окне IL DASM выберите файл Открыть. дважды щелкните Visual Studio версия >, а затем дважды щелкните >. Откройте папку проекта и найдите в папке bin/Debug файл имя_проекта.dll. Дважды щелкните файл имя_проекта.dll. В новом окне будут показаны атрибуты проекта, а также ссылки на другие модули и сборки. Обратите внимание, что в сборку включены пространства имен Microsoft.Office.Interop.Excel и Microsoft.Office.Interop.Word . По умолчанию в Visual Studio компилятор импортирует в сборку необходимые типы из сборки PIA, на которую указывает ссылка.
Дважды щелкните значок МАНИФЕСТ. Откроется окно со списком сборок, содержащих элементы, на которые имеются ссылки в проекте. Сборки Microsoft.Office.Interop.Excel и Microsoft.Office.Interop.Word не будут указаны в этом списке. Поскольку необходимые для проекта типы были импортированы в сборку проекта, ссылки на сборки PIA не требуется. Это упрощает развертывание. Сборки PIA не обязательно должны присутствовать на компьютере пользователя, а поскольку приложение не требует развертывания конкретной версии сборки PIA, можно разрабатывать приложения, которые работают с различными версиями Office, если в этих версиях имеются все необходимые интерфейсы API.
Поскольку развертывать сборки PIA больше не требуется, можно создавать приложения для применения в сложных сценариях, чтобы эти приложения работали с несколькими версиями Office, включая и более ранние версии. Тем не менее это возможно только в том случае, если в коде не используются интерфейсы API, которые недоступны в используемой версии Office. Разработчик не всегда знает, был ли доступен тот или иной интерфейс API в более ранней версии, поэтому работать с более ранними версиями Office не рекомендуется.
До Office 2003 сборки PIA не публиковались. Поэтому единственными способом создания сборки взаимодействия в Office 2002 или более ранних версиях является импорт ссылки COM.
Закройте окно манифеста и окно сборки.
Восстановление зависимости PIA
В обозревателе решений нажмите кнопку Показать все файлы. Разверните папку Ссылки и выберите Microsoft.Office.Interop.Excel. Нажмите клавишу F4, чтобы открыть окно Свойства.
В окне Свойства измените значение свойства Внедрить типы взаимодействия с True на False.
Повторите шаги 1 и 2 этой процедуры для сборки Microsoft.Office.Interop.Word .
Нажмите клавишу F5, чтобы проверить, что проект по-прежнему выполняется правильно.
Повторите шаги 1–3 из предыдущей процедуры, чтобы открыть окно сборки. Обратите внимание, что сборки Microsoft.Office.Interop.Word и Microsoft.Office.Interop.Excel больше не входят в список внедренных сборок.
Дважды щелкните значок МАНИФЕСТ и просмотрите список сборок, на которые имеются ссылки. В списке будут указаны сборки Microsoft.Office.Interop.Word и Microsoft.Office.Interop.Excel . Поскольку приложение содержит ссылки на сборки PIA Excel и Word, а свойство Внедрить типы взаимодействия имеет значение False, на компьютере пользователя должны храниться обе сборки.
В Visual Studio в меню Построение выберите пункт Очистить решение, чтобы очистить завершенный проект.
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
NOTE
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, you also have to add “Syncfusion.Licensing” assembly reference and include a license key in your projects. Please refer to this link to know about registering Syncfusion license key in your applications to use our components.
Include the following namespaces in your .cs or .vb file as shown as follows.
Create a Hello World Excel File
The following code example explains how to create a hello world sample.
The output screen-shot of the above code.
Create a Simple Excel File
An instance of the ExcelEngine gives access to create an application instance that is similar to launching Microsoft Excel application. The following code snippet shows how to initialize the application object for creating or manipulating Excel documents.
By default, the Excel version 97 to 2003 (*.xls) is associated with application object. XlsIO writes the excel files in the respective format depending on this excel version. You can modify the default Excel version to Excel 2013 as shown as follows.
The workbook contains a collection of worksheets and various workbook-level properties. Each worksheet has cells, which can contain text, numbers, dates, formulas and more. The following code snippet illustrates how to create a workbook and access worksheet instance.
The following code snippet shows how to add an image into the worksheet.
Finally, save the document in file system and close/dispose the instance of IWorkbook and ExcelEngine.
The complete code to create a simple Excel document.
The output screen-shot of the above code.
Import Data to Excel Worksheets
XlsIO helps to import data from various data sources into a worksheet. The following data sources can be imported using XlsIO:
- Collection Objects
- Data Table
- Data Column
- Data View
- Array
The following code snippet shows how to import data from objects.
The following code snippet provides supporting methods and classes for the previous code.
You can refer various importing options in the “Importing Data to Worksheet” section.
Export Data from Excel Worksheets
The worksheet data can be exported to a data table using the ExportDataTable() method. This method provides various options that allows to export data through ExcelExportDataTableOptions.
The following code demonstrates how to export data from a worksheet to a data table with the ColumnNames and DetectColumnTypes options.
The following screenshot shows the DataTable of previous code.
You can refer various exporting options in the “Exporting from Worksheet to Data Table” section.
Template based data filling using Template Markers
A template marker is a special marker symbol that allows to generate a document by filling data in an Excel template from data source. This marker automatically maps the column name in the data source and names of the marker fields in the Excel template document and fills the data (text or image).
This functionality supports the following data sources.
- Collection Objects
- DataTable
- Array
Each marker starts with a prefix “%”, which is followed by a MarkerVariable and its property. The arguments are delimited by semicolon (;). The following syntax shows the usage of marker in input template document.
For example: %Reports.SalesPerson
Use the following syntax to maintain row formats while filling data.
For example: %Reports.SalesPerson;insert:copystyles
For example – let’s consider that you have a template document as shown below.
The following code snippet shows how to use template markers with objects.
Читайте также: