| Введение
Друзья, до сих пор в Интернете я не встречал решения проблемы снятия
процесса EXCEL.EXE из диспетчера задач. Причём в VS6 этого глюка не было
и в помине, он появился с появлением .NET! Поэтому для простого парня
вроде меня было совершенно неясно - то ли код неверный, то ли софт на
компе установлен криво, то ли ещё чего. Как бы Вы, друзья, ни бились,
наверно тоже ничего в Интернете не найдёте. Эта статья рассказывает Вам
как правильно работать с Экселем в .NET (под Web и под Win) от начала и
до конца.
Этот Принтскрин иллюстрирует зависание задачи EXCEL.EXE в диспетчере
задач при условии неправильного использования Экселя:
Эта картина может появиться при любом способе вызова Экселя - и при
так называемом раннем связывании и при позднем. В роли эксперта здесь
выступает Гвоздев Дмитрий (GvozdevDV@sibintek.ru).
"В .NET есть и раннее и позднее связывание, но при работе со старыми
компонентами COM, .NET инкапсулирует позднее связывание с помощью RCW
(Rintime Callable Wrapper), который в свою очередь предоставляет .NET
возможность использовать проперти и методы компонетнов COM - как было в
VB6 при раннем связывании", - говорит эксперт. Пока, в рамках введения,
опишу клиническую картину более подробнее.
Клиническая картина при работе с Windows-формой:
Эксель появляется в диспетчере и может плодиться там до
бесконечности. Однако, при вызове оператора GC.Collect(), все процессы
уничтожаются кроме одного. (GC.WaitForPendingFinalizers() и повторный
GC.Collect() не помогают). При закрытии программы этот последний процесс
пропадает. Поэтому, в принципе, можно говорить о том, что Windows сам
себя вытягивает из болота подобно барону Мюнхаузену. Исходя из этого,
проблема незакрытия процесса в Windows-е мало кого волнует. Поскольку
решается она так же как и для Web-форм, то мы не будет здесь
останавливаться, а рассмотрим в подробностях решение для Web.
Клиническая картина при работе с Web-формой:
Так же, как и для Windows, Эксель размножается. При вызове
GC.Collect(), все процессы кроме одного пропадают.
(GC.WaitForPendingFinalizers() и повторный GC.Collect() не помогают).
Этот последний процесс не уничтожается сам ни при закрытии формы, ни
после истечения срока сессии (по умолчанию 20 минут). Он пропадает лишь
после рестарта IIS-а, да и то не всегда. Поэтому, друзья, Вы понимаете,
что сколько сессий, столько и процессов скопится в памяти. Конечно,
далеко не все используют Эксель в своей работе, например на страницах
http://www.aspnetmania.com/
Эксель не использует. Но если кто-то из Вас делает не просто сайты, а
программы для нужд предприятия, то Вы обязательно столкнётесь с
электронными таблицами, сводными таблицами и диаграммами. Ведь в Экселе
во-первых удобно работать с отчётами, а во-вторых удобно выводить
конечный результат на принтер.
Почему висит процесс и что с этим делать:
Как выяснилось в ходе исследований, ошибка появляется во-первых из-за
старой версии MS-Office (нужна 2003-я версия или более новая), а
во-вторых из-за неграмотного завершения работы с Экселем. Чтобы узнать
как решается проблема и как вообще надо работать с Экселем, прочитайте
статью до конца.
1. Правила использования Экселя
Дорогие друзья! Всегда стремитесь к тому, чтобы Ваши программы были
написаны не через пень колоду, а как доктор прописал. Только в этом
случае можно гарантировать, что Ваша программа будет работать; а если не
будет работать, то по причинам от Вас не зависящим.
1.1. Облегчите себе жизнь, именуйте переменные по правилу - первая
маленькая буква означает тип переменной, затем идёт название с большой
буквы. Соблюдение этого правила позволит читать код на чёрнобелой
распечатке. Я называю свои переменные так:
Dim oPeremennaya 'объектная
Dim iPeremennaya As Integer 'целая
Dim lPeremennaya As Long 'длинная целая
Dim dPeremennaya As Double 'двойной точности
Dim tPeremennaya As DateTime 'дата и время
Dim sPeremennaya As String 'строковая
1.2. Перед использованием Экселя убедитесь, что версия MS-Office не
старее 2003-й. К сожалению, Microsoft хоть и внушает уважение, но
допускает в своей работе такие глюки, что Студия .NET 2003 прямо скажем
криво работает с Экселем 2002 (XP). Если у Вас нет возможности
установить новую версию Офиса, то миритесь с теми глюками, которые есть.
1.3. Существует несколько способов работы с Экселем. Далее по тексту
предполагается, что мы используем Web-форму, если Вы пишите под Windows,
то особо ничего не меняется, просто сделайте скидку на то, что в
Windows-е файлы сохраняются и запускаются на клиенте, ну и Яваскрипта
никакого не надо. Вот какие способы можно использовать:
1.3.1. Работа через CSV-файлы. Хотя в проводнике Windows данный тип
файла ассоциируется с Экселем, на самом деле это настоящая таблица базы
данных с разделительными запятыми, которую можно использовать и в
Access-е, и на SQL-сервере, Oracle-е и других СУБД. Работа сводится к
созданию или открытию файла для чтения и записи, добавления туда нужной
информации, сохранению файла под определённым именем на диске и загрузке
этого файла посредством Экселя. Но как Вы понимаете, друзья, мы тут не
сможем ни шрифт поменять, ни ширину колонок, ни комментарии вклеить, ну
ничего не сможем, я уж не говорю о сводных таблицах и диаграммах.
Вот, посмотрите как это делается:
Rem Работа с CSV
Open sFileToExport For Output As #iFileNum 'открываем файл для записи
Print #iFileNum, "НГДУ;";
Print #iFileNum, "Программа;";
Print #iFileNum, "ДатаСпец;";
Print #iFileNum, "СуммаСпец;";
Print #iFileNum, "Проект;";
Print #iFileNum, "Контрагент;";
Print #iFileNum, "Получатель;";
Print #iFileNum, "ДатаПрог;";
Print #iFileNum, "СтатусПрог;";
Print #iFileNum, "СуммаПрог;";
Print #iFileNum, "НаимПрог;";
Print #iFileNum, "ПрихПрог;";
Print #iFileNum, "РасхПрог;";
Print #iFileNum, "Остаток= Прих-Расх;";
Print #iFileNum, "ПримФил"
For lKodIndex1 = 0 To lRecordCount - 1
Print #iFileNum, sNgdu; ";";
Print #iFileNum, oRecordset(0, lKodIndex1); ";";
Print #iFileNum, oRecordset(2, lKodIndex1); ";";
Print #iFileNum, oRecordset(3, lKodIndex1); ";";
Print #iFileNum, oRecordset(5, lKodIndex1); ";";
Print #iFileNum, oRecordset(6, lKodIndex1); ";";
Print #iFileNum, oRecordset(7, lKodIndex1); ";";
Print #iFileNum, oRecordset(9, lKodIndex1); ";";
Print #iFileNum, oRecordset(10, lKodIndex1); ";";
Print #iFileNum, oRecordset(11, lKodIndex1); ";";
Print #iFileNum, pubStringScv(oRecordset(12, lKodIndex1)); ";";
Print #iFileNum, dPrih; ";";
Print #iFileNum, dRash; ";";
Print #iFileNum, dPrih - dRash; ";";
Print #iFileNum, sPrim
Next lKodIndex1
Close iFileNum
Итак, сперва мы открыли файл с именем sFileToExport, затем вписываем
первую строку - заголовок. После этого заполняем в цикле нужные
значения, беря их из двумерного массива объектного типа или из других
переменных. Обратите внимание на то, что последний оператор Print не
содержит точки с запятой - это признак конца строки. И ещё одно важное
замечание: вообще-то вместо разделителя "точка с запятой" можно
использовать разделитель "запятая". Но тогда у Вас не будет возможности
задать числа с десятичной запятой. Вот почему я Вам настоятельно советую
использовать не запятую, а точку с запятой. Такой вариант таблицы, как я
уже говорил, будет работать не только на Excel, но и на Access и другие
БД. И в конце - уже после цикла - нам остаётся закрыть открытый файл. На
всякий случай напоминаю, что в ASP.NET файл создаётся не на компьютере
клиента, а на сервере! Вы должны вместо пользователя "ASPNET" прописать
своего пользователя в файле Machine.config или Web.config и дать ему
права на чтение/запись в нужную папку. Иначе ждите ошибки, связанной с
ограничением прав доступа. Если Вы используете Windows 2003 или более
новую версию, то там уже речь идёт не о пользователе "ASPNET", а о
группе пользователей "IIS_WPG".
Что полезного в этом коде? Да, казалось бы он бесполезен, ведь мы
можем вызвать объект Excel.Application и спокойно работать. Но когда
речь идёт не о сотне строк, а о десятках тысяч, то уже от
Excel.Application мало толку. Такой отчётище у Вас будет выводиться
несколько часов (применительно к Pentium III)!!! А вот этот простенький
способ может выгрузить отчётище за секунды!
1.3.2. Подобные п. 1.3.1. способы
Я имею в виду работу с файлами, имеющими расширение XLS (как вариант
- HTML и другие форматы, распознаваемые Экселем). В принципе, можно
сгенерить файл CSV и сохранить его с расширением XLS, Эксель нормально
его проглотит как свой, но всё-таки я здесь говорю о настоящем XLS. Это
бинарный файл и для того, чтобы понять правила его создания, Вам
придётся взломать какой-нибудь простенький экселевский файлик и
посмотреть как он устроен. Я считаю, что нам с Вами, не вундеркиндам, а
простым умным мальчикам и девочкам, вполне придётся по зубам XLS 5-й
версии. Посмотрите его через PC-Tools или Norton или другой доступный
редактор двоичных кодов, и Вы сможете генерить настоящий XLS без помощи
Excel.Application! Если лень разбираться с двоичными кодами и карьера
хакера (в хорошем смысле) Вас не привлекает, то хотябы разберитесь с
текстовым кодом формата HTML. Наградой для Вас будет на несколько
порядков большая скорость чтения/записи файлов по сравнению с работой
через Excel.Application. Вы можете спросить почему Excel.Application так
медленно работает. Ответ настолько прост, что вы удивитесь: бытует
мнение, что Microsoft намерено тормозит работу своих программ, чтобы у
пользователей был стимул купить новый компьютер. Об этом говорят многие
умные люди, хотя конечно нет никаких доказательств кроме логических
рассуждений. Чтобы получить доказательства, нам с Вами пришлось бы
заново переписать все библиотеки API Windows, а потом написать заново
аналог Экселя - самой сложной программы пакета MS-Office - и потом
сравнить скорости работы. Но давайте не будем ругать Microsoft, а
наоборот поклонимся ему до земли, ведь только подумайте, что бы мы
делали без этой очень мощной, умной, да и что греха таить - доброй -
американской корпорации! Не зря говорят, что огромный контингент
русскоязычных программистов работает там. Чтож, учите английский язык -
может и кому-то из Вас улыбнётся удача!
1.3.3. Способ от AL (http://www.aspnetmania.com/Users/User/51.html),
повествующий о том, как перебросить табличку из DataGrid на экселевский
лист.
Код приведён в кодохранилище сайта
http://www.aspnetmania.com/ по
адресу
http://www.aspnetmania.com/Code/Code.aspx?ID=36 и имеет довольно
высокую оценку, равную 4,33 из 5,00 на момент набора этой статьи (9
сентября 2004 года). Код сей довольно бурно обсуждался в своё время на
форуме по адресу http://www.aspnetmania.com/Forums/ForumMessage/42448.html
Я приведу листинг этого способа ниже, чтобы Вы могли изучать статью
не из Интернета, а по распечатке. А пока в двух словах опишу что здесь
имеется ввиду. Способ позволяет пользователю передать в эксель не только
значения, но и форматирование, доступное для элемента управления
DataGrid. Если Вы в своей практике используете DataGrid, то пожалуйста,
обратите внимание на этот код. Автор способа утверждает, что "всё крoме
фoрмул и кoмментaриев будет рaбoтaть и тo я пoдoзревaю, чтo мoжнo
сделaть".
Название: экспорт контрола в MS Excel
Описание: пример кaк
сделaть экспoрт кoнтрoлa в MS-Excel, чтoбы Excel фaйл сгрузился
пользователю в виде attachment
Комментировать приведённый код не берусь, так как сам его не
испытывал. Но это и не надо, поскольку авторитет автора очень велик.
AL является одним из немногих учёных, посещающих форумы по
программированию не для того, чтобы спрашивать, а чтобы только помочь
другим людям своим мудрым советом.
1.3.4. Самый главный способ, позволяющий использовать Эксель на
полную мощь. Это он может вызвать засорение памяти при неграмотном
использовании. Ещё раз повторюсь - MS-Excel - самая сложная и самая
умная программа из пакета MS-Office ("Говорят, что Excel - это самая
сложная из всех написанных для Windows программ. А поскольку она имеет
намного больше функциональных возможностей, чем любое другое приложение
для Windows, то Excel оказывается идеальным кандидатом для удалённого
программирования. Вы получите в своё распоряжение всю функциональность
Excel, написав всего несколько строк кода", - говорится в книге Джона
Коннэлла "Visual Basic 6. Введение в программирование баз данных").
Относитесь к Экселю как к брату, изучайте его необъятные возможности. И
Вам будут не нужны другие построители отчётов, вроде Crystal Reports, а
руководство будет Вами довольно. Если же Вы освоите в придачу к этому
способ 1.3.2, то цены Вам не будет, друзья мои!
Итак, выполняйте пошаговые инструкции:
1.3.4.1. Сделайте ссылку на библиотеку Эксель. Если Вы используете
раннее связывание, то вы должны сделать этот шаг. Впрочем, он не
повредит и при использовании позднего связывания, хотя проку в нём уже
не будет. Если Вы вынуждены работать со старым Экселем (2002 или
древнее), то подключайте библиотеку версии 10 или ту, что есть под
рукой. Беда у Вас будел лишь в том, что Вам наверняка не удастся просто
так очистить память от процесса Excel. В шестой версии Бэйсика или
другого языка из Студии 6 такой проблемы не было, а вот с появлением
VS.NET она вдруг ни с того, ни с сего появилась. Видимо программисты
Microsoft, работающие в разных отделах, не договорились между собой. Я
думаю, что это произошло не по злому умыслу, а в силу чрезвычайной
сложности Excel.Application как объекта. Даже с выходом 2003-й версии
MS-Office проблему не устранили до конца, но по крайней мере мы теперь
знаем как её решить обходным путём. В принципе, конечно, можно пройтись
по списку процессов, найти Экселевские, да и позакрывать их, но никто
Вам не даст гарантии, что Вы закроете ненужный процесс: удалится всё!
Всё под одну кошёлку. Но если на Web-сервере пользователи не работают и
Вы отслеживаете все события типа "Открыт Эксель" и "Закрыт Эксель", то в
принципе, если уж так Вы хотите, можно и не ставить 2003-й Эксель, а
использовать старый. Тогда используйте код, приведённый ниже, но учтите,
что я Вам не советую этого делать, лучше поставьте новую версию Экселя.
Dim sExcel As String = "EXCEL.EXE" ' имя файла
Dim sProcessName As String = System.IO.Path.GetFileNameWithoutExtension(sExcel) 'имя процесса
Dim oProcess As System.Diagnostics.Process
For Each oProcess In System.Diagnostics.Process.GetProcessesByName(sProcessName)
oProcess.Kill()
Next
Это был код очистки памяти для романтиков, пишущих программы не
сколько для конечного результата, сколько для собственного удовольствия.
А всех остальных я приглашаю продолжить тему и посмотреть как же даётся
ссылка на библиотеку Эксель. Обратите внимание на то, что после
установки Офиса 2003 параллельно с Офисом XP 2002, нам без разницы какую
библиотеку использовать - хотите 10-ю, хотите 11-ю. Все они будут
работать. Вот, друзья, взгляните на картинку и делайте так же:
Нажимаем правой кнопкой мыши на Referens и выбираем ссылку на Эксель.
Ну тут я выбрал 10-ю библиотеку. Кстати если Вы выберите сперва одну,
потом её удалите и добавите другую, то обнаружите, что в каждом случае
добавится разный набор библиотек. Я приведу принтскрины и того и другого
варианта. Думаю, что если Вам Студия выдаст другой набор библиотек, то
это не страшно.
Случай 10-й библиотеки и 11-й соответственно
 
В зависимости от того сколько версий MS-Office у Вас установлено, код
может быть таким:
Dim oExcel As Excel.Application
или таким:
Dim oExcel As Microsoft.Office.Interop.Excel.Application
И наверно может даже ещё другим. Для того, чтобы выяснить правильный
код, откройте Object Browser и поищите объект Application:
В данном примере мы нашли, что действует строка Dim oExcel As
Excel.Application.
1.3.4.2. Используйте код, приведённый ниже в отдельной процедуре
ToExcel.
Раннее связывание (рекомендуется)
REM Вывод в Эксель
Private Sub ToExcel()
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
Dim sFile As String
REM работа с Экселем
oExcel = New Excel.Application 'создаём новый процесс Эксель
'oExcel = CreateObject("Excel.Application") 'другой способ создания нового процесса Эксель - для
'позднего связывания.
'oExcel.Visible = True 'работает только под Windows
oBook = oExcel.Workbooks.Open("Путь и имя файла.xls") 'открываем книгу с шаблоном
oSheet = oBook.ActiveSheet
REM заполнения файла Эксель
Rem заполнение комментариев
oRange = oSheet.Range("A1")
If Not oRange.Comment Is Nothing Then
oRange.Comment.Delete()
End If
oRange.AddComment("Комментарий")
REM заполнение значений (y,x)
oSheet.Cells(1, 4).Value = "Значение ячейки Y=1 и X=4"
REM работа с памятью
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRange)
oRange = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet)
oSheet = Nothing
sFile = "Путь и имя" & ".xls"
oBook.SaveAs(FileName:=sFile)
oBook.Close(SaveChanges:=False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
GC.Collect()
End Sub
В случае позднего связывания, которое я Вам не советую (так как это
должно медленнее работать, хотя аналитик Гвоздев Дмитрий полагает, что
применительно к .NET скорость не замедлится), наберите такой код:
Rem создаём ссылочную переменную для электронной таблицы
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True 'работает только под Windows
oExcel.Workbooks.Open "Путь и файл.xls"
Set oTemp = oExcel
iVersion = Val(objExcel.Application.Version)
If (iVersion >= 8) Then Set oExcel = oExcel.ActiveSheet
Rem заполнения файла Эксель
Rem заполнение значений (y,x)
oExcel.Cells(1, 4).Value = "Значение"
Это кусок кода, который отличается от раннего связывания. Не забудьте
операторы Dim и последующую очистку памяти. Слово Set в .NET набирать не
обязательно, так как .NET научилась различать простые переменные и
объекты. Если Вы используете позднее связывание, то напоминаю, что пункт
1.3.4.1. выполнять не обязательно.
Вот эта процедура - ToExcel - если только не производится работы с
данными, отрабатывает нормально и процесс Excel в диспетчере задач
пропадает. Но какой смысл открыть и закрыть книгу, не работая с ней? Вот
если Вы поработаете, то у Вас зависнет один процесс на каждую сессию.
Чтобы от него избавиться, надо запустить оператор Gc.Collect() после
отработки процедуры ToExcel. Смотрите приведённый в следующем пункте код
и сделайте так же.
1.3.4.3. Вызовите процедуру ToExcel из другой процедуры и добавьте
после оператор Gc.Collect()
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ToExcel()
GC.Collect()
End Sub
Сперва выполняется ToExcel, а мусор подметает последующий
GC.Collect()
1.3.4.4. Если Вы работаете под Интернет, то Вам придётся создать
своего пользователя и прописать его в Machine.config или Web.config в
двух разделах:
<identity impersonate="true" userName="Домен\Пользователь" password="Пароль"/>
<processModel enable="true" timeout="Infinite" idleTimeout="Infinite" shutdownTimeout="0:00:05"
requestLimit="Infinite" requestQueueLimit="5000" restartQueueLimit="10" memoryLimit="60"
webGarden="false" cpuMask="0xffffffff" userName="Домен\Пользователь" password="Пароль"
logLevel="Errors" clientConnectedCheck="0:00:05" comAuthenticationLevel="Connect"
comImpersonationLevel="Impersonate" responseDeadlockInterval="00:03:00" maxWorkerThreads="20"
maxIoThreads="20"/>
Пользователю дайте все права, которыми сами обладаете. Только в этом
случае можно гарантировать, что не возникнет ошибки доступа.
2. Живой пример - вывод в Эксель счёта на оплату
В этом примере используется весьма сложная технология. Если Вы хотите
повторить её, то постарайтесь не отклоняться от приводимого примера в ту
или иную сторону.
2.1. Процедура ToExcel. Перед тем как её сделать, объявите переменную
sWin уровня формы:
Проперти toWin нам тоже понадобится, оно содержит яваскрипт,
открывающий окно с Экселем. Чтобы это окно не открывалось когда не
попадя, добавьте в процедуру Page_Load строку sWin="".
REM Вывод в Эксель
Private Sub ToExcel()
Dim sSql As String
Dim oRows1(,) 'начинка
Dim lX1 As Long
Dim lY1 As Long
Dim oRows2(,) 'шапка
Dim lX2 As Long
Dim lY2 As Long
Dim l1 As Long
Dim l2 As Long
Dim dSum9 As Double
Dim dSum10 As Double
Dim dSum18 As Double
Dim dSum19 As Double
Dim excelVersion As Integer
'Dim oExcel As New Microsoft.Office.Interop.Excel.Application
'Dim oBook As Microsoft.Office.Interop.Excel.Workbook
'Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
'Dim oRange As Microsoft.Office.Interop.Excel.Range
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
Dim sDubl As String = TextBox11.Text
Dim dSumStar As Double
Dim bDubl As Boolean
Dim lKod As Long = TextBox5.Text
Dim iNum1 As Integer
Dim iNum2 As Integer
Dim sErr As String
Dim dStoimBez As Double
Dim dStoimNds As Double
Dim dSsnBez As Double
Dim dSsnNds As Double
Dim sFile As String
'набор начинка
sSql = "SELECT ... "
sSql = sSql & "... "
sSql = sSql & "...;"
REM Новое распределение
'00 наименование
'01 ед изм
'02 колич
'03 цена без ндс
'04 %ндс
'05 прог
'06 поз
'07 классификатор
'08 %ссн
'09 статус
'10 %оплаты
iNum1 = Zap(sSql, sErr, oRows1) 'функция взятия запроса SQL
If iNum1 < 1 Then
Exit Sub
End If
lX1 = UBound(oRows1, 1)
lY1 = UBound(oRows1, 2)
For l1 = 0 To lX1
For l2 = 0 To lY1
If IsDBNull(oRows1(l1, l2)) Or IsNothing(oRows1(l1, l2)) Then oRows1(l1, l2) = 0
Select Case l1
Case 3
oRows1(l1, l2) = Math.Round(oRows1(l1, l2), 2)
End Select
Next l2
Next l1
'набор шапка
sSql = "SELECT ... "
sSql = sSql & "... "
sSql = sSql & "...;"
'0=телефон
'1=дата договора
'2=код типа договора
'3=тип договора
'4=договор
'5=адрес
'6=инн
'7=сумма предыдущего дубля
iNum2 = Zap(sSql, sErr, oRows2)
If iNum2 <= 0 Then
Exit Sub
End If
lX2 = UBound(oRows2, 1)
lY2 = UBound(oRows2, 2)
If IsDBNull(oRows2(0, 0)) Or IsNothing(oRows2(0, 0)) Then oRows2(0, 0) = "—"
If IsDBNull(oRows2(1, 0)) Or IsNothing(oRows2(1, 0)) Then oRows2(1, 0) = "—"
If IsDBNull(oRows2(2, 0)) Or IsNothing(oRows2(2, 0)) Then oRows2(2, 0) = 0
If IsDBNull(oRows2(3, 0)) Or IsNothing(oRows2(3, 0)) Then oRows2(3, 0) = "—"
If IsDBNull(oRows2(4, 0)) Or IsNothing(oRows2(4, 0)) Then oRows2(4, 0) = "—"
If IsDBNull(oRows2(5, 0)) Or IsNothing(oRows2(5, 0)) Then oRows2(5, 0) = "—"
If IsDBNull(oRows2(6, 0)) Or IsNothing(oRows2(6, 0)) Then oRows2(6, 0) = "—"
If IsDBNull(oRows2(7, 0)) Or IsNothing(oRows2(7, 0)) Then oRows2(7, 0) = 0
REM работа с Экселем
oExcel = New Excel.Application 'создаём новый процесс Эксель
'oExcel = CreateObject("Excel.Application") 'другой способ создания нового процесса Эксель
'oExcel.Visible = True 'работает только под Windows
oBook = oExcel.Workbooks.Open(dataDir4 & "Счёт на оплату (Вэб).xls") 'открываем книгу с шаблоном
oSheet = oBook.ActiveSheet
REM заполнения файла Эксель
' заполнение комментариев
oRange = oSheet.Range("A1")
If Not oRange.Comment Is Nothing Then
oRange.Comment.Delete()
End If
oRange.AddComment("MX Finance Web: Файл создан " & Now)
REM заполнение значений (y,x)
If IsNumeric(sDubl) Then
sDubl = " (дубль " & sDubl & ")"
dSumStar = Math.Round(oRows2(7, 0), 2) 'сумма предыдущего дубля
bDubl = True
End If
oSheet.Cells(1, 4).Value = "СЧЁТ " & TextBox7.Text & sDubl & " от " & _
FormatDateTime(TextBox8.Text, DateFormat.ShortDate)
oSheet.Cells(3, 6).Value = "Покупатель: " & TextBox13.Text
oSheet.Cells(4, 6).Value = "Адрес: " & oRows2(5, 0)
oSheet.Cells(5, 6).Value = "Телефон: " & oRows2(0, 0)
If IsNumeric(oRows2(0, 0)) Then
If oRows2(0, 0) = 0 Then
oSheet.Cells(5, 6).Value = "Телефон: " & "—"
End If
End If
oSheet.Cells(6, 6).Value = "ИНН: " & oRows2(6, 0)
oSheet.Cells(10, 6).Value = "Получатель: " & TextBox13.Text
oSheet.Cells(14, 1).Value = TextBox9.Text
If oRows2(2, 0) <> 6 Then
oSheet.Cells(20, 1).Value = "Средства для выполнения обязательств по договору " & oRows2(4, 0)
Else
oSheet.Cells(20, 1).Value = "Средства для выполнения обязательств по договору комиссии " & oRows2(4, 0)
End If
For l1 = 20 To lY1 + 20
oSheet.Cells(1, 1).Value = "Заполнение строки " & l1 - 19 & " из " & lY1 + 1 & ". Ждите."
oSheet.Cells(l1, 2).Value = oRows1(0, l1 - 20) 'наим
oSheet.Cells(l1, 3).Value = oRows1(1, l1 - 20) 'ед изм
oSheet.Cells(l1, 4).Value = oRows1(2, l1 - 20) 'колич
oSheet.Cells(l1, 5).Value = oRows1(3, l1 - 20) 'цена
dStoimBez = Math.Round(oRows1(2, l1 - 20) * oRows1(3, l1 - 20), 2) 'стоимость без нал
oSheet.Cells(l1, 6).Value = dStoimBez 'стоимость без нал
oSheet.Cells(l1, 7).Value = "—" 'акциз
oSheet.Cells(l1, 8).Value = oRows1(4, l1 - 20) 'налоговая ставка
dStoimNds = Math.Round(dStoimBez * oRows1(4, l1 - 20) / 100, 2) 'сумма налога
oSheet.Cells(l1, 9).Value = dStoimNds 'сумма налога
dSum9 += dStoimNds 'сумма налога
oSheet.Cells(l1, 10).Value = dStoimBez + dStoimNds 'сумма с налогом
dSum10 += dStoimBez + dStoimNds 'сумма с налогом
oSheet.Cells(l1, 13).Value = oRows1(5, l1 - 20) 'программа
oSheet.Cells(l1, 14).Value = oRows1(6, l1 - 20) 'позиция
oSheet.Cells(l1, 15).Value = oRows1(7, l1 - 20) 'классификатор
oSheet.Cells(l1, 16).Value = oRows1(8, l1 - 20) 'ссн
dSsnBez = Math.Round(dStoimBez * oRows1(8, l1 - 20) / 100, 2) 'сумма ссн без налога
oSheet.Cells(l1, 17).Value = dSsnBez 'сумма ссн без налога
dSsnNds = Math.Round(dSsnBez * 0.18, 2) 'налог
oSheet.Cells(l1, 18).Value = dSsnNds 'налог
dSum18 += dSsnNds 'налог
oSheet.Cells(l1, 19).Value = dSsnBez + dSsnNds 'сумма ссн с налогом
dSum19 += dSsnBez + dSsnNds 'сумма ссн с налогом
oSheet.Cells(l1, 20).Value = oRows1(9, l1 - 20) 'вид оплаты
oSheet.Cells(l1, 21).Value = oRows1(10, l1 - 20) 'процент оплаты
Next l1
With oSheet.Range("A20:A" & l1 - 1 & "")
.HorizontalAlignment = 1 '1=xlGeneral=1
.VerticalAlignment = -4160 'xlTop=-4160
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = -5002 'xlContext=-5002
.MergeCells = True
End With
oSheet.Range("A20:U" & l1 - 1 & "").Borders(5).LineStyle = -4142 'xlDiagonalDown=5,xlNone=-4142
oSheet.Range("A20:U" & l1 - 1 & "").Borders(6).LineStyle = -4142 'xlDiagonalUp=6,xlNone=-4142
With oSheet.Range("A20:U" & l1 - 1 & "").Borders(7) 'xlEdgeLeft=7
.LineStyle = 1 'xlContinuous=1
.Weight = 2 'xlThin=2
.ColorIndex = -4105 'xlAutomatic=-4105
End With
With oSheet.Range("A20:U" & l1 - 1 & "").Borders(8) 'xlEdgeTop=8
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oSheet.Range("A20:U" & l1 - 1 & "").Borders(9) 'xlEdgeBottom=9
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oSheet.Range("A20:U" & l1 - 1 & "").Borders(10) 'xlEdgeRight=10
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oSheet.Range("A20:U" & l1 - 1 & "").Borders(11) 'xlInsideVertical=11
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oSheet.Range("A20:U" & l1 - 1 & "").Borders(12) 'xlInsideHorizontal=12
'.LineStyle = 1
If l1 > 21 Then
.Weight = 2
.ColorIndex = -4105
End If
End With
oSheet.Cells(1, 1).Value = ""
If oRows2(2, 0) <> 6 Then
oSheet.Cells(l1, 1).Value = "Сумма без учёта снабженческо-сбытовой наценки"
oSheet.Cells(l1, 9).Value = dSum9
oSheet.Cells(l1, 10).Value = dSum10
oSheet.Cells(l1, 18).Value = dSum18
oSheet.Cells(l1, 19).Value = dSum19
oSheet.Cells(l1 + 1, 1).Value = "Сумма снабженческо-сбытовой наценки"
oSheet.Cells(l1 + 1, 8).Value = 18
oSheet.Cells(l1 + 1, 9).Value = dSum18
oSheet.Cells(l1 + 1, 10).Value = dSum19
oSheet.Cells(l1 + 2, 1).Value = "Сумма к оплате"
oSheet.Cells(l1 + 2, 9).Value = dSum9 + dSum18
oSheet.Cells(l1 + 2, 10).Value = dSum10 + dSum19
oSheet.Cells(l1 + 3, 1).Value = "Сумма к оплате прописью: " & Num2Text(dSum10 + dSum19)
If bDubl Then
oSheet.Cells(l1 + 4, 1).Value = "Сумма предыдущего дубля"
oSheet.Cells(l1 + 4, 10).Value = dSumStar
oSheet.Cells(l1 + 5, 1).Value = "Разница к доплате = Сумма к оплате - Сумма предыдущего дубля"
oSheet.Cells(l1 + 5, 10).Value = dSum10 + dSum19 - dSumStar
'oSheet.Cells(l1 + 6, 1).Value = "Разница к доплате прописью: " & _
pubNum2Text(dSum10 + dSum19 - dSumStar)
oSheet.Cells(l1 + 9, 1).Value = "Руководитель предприятия ___________________________________ " & _
"(Фамилия И.О.)"
oSheet.Cells(l1 + 13, 1).Value = "Главный бухгалтер __________________________________________ " & _
"(Фамилия И.О.)"
Else
oSheet.Cells(l1 + 7, 1).Value = "Руководитель предприятия ___________________________________ " & _
"(Фамилия И.О.)"
oSheet.Cells(l1 + 11, 1).Value = "Главный бухгалтер __________________________________________ " & _
"(Фамилия И.О.)"
End If
Else
oSheet.Cells(l1, 1).Value = "Сумма к оплате"
oSheet.Cells(l1, 9).Value = dSum9
oSheet.Cells(l1, 10).Value = dSum10
oSheet.Cells(l1 + 1, 1).Value = "Сумма к оплате прописью: " & Num2Text(dSum10)
If bDubl Then
oSheet.Cells(l1 + 2, 1).Value = "Сумма предыдущего дубля"
oSheet.Cells(l1 + 2, 10).Value = dSumStar
oSheet.Cells(l1 + 3, 1).Value = "Разница к доплате = Сумма к оплате - Сумма предыдущего дубля"
oSheet.Cells(l1 + 3, 10).Value = dSum10 + dSum19 - dSumStar
'oSheet.Cells(l1 + 4, 1).Value = "Разница к доплате прописью: " & _
pubNum2Text(dSum10 + dSum19 - dSumStar)
oSheet.Cells(l1 + 7, 1).Value = "Руководитель предприятия ___________________________________ " & _
"(Фамилия И.О.)"
oSheet.Cells(l1 + 11, 1).Value = "Главный бухгалтер __________________________________________ " & _
"(Фамилия И.О.)"
Else
oSheet.Cells(l1 + 5, 1).Value = "Руководитель предприятия ___________________________________ " & _
"(Фамилия И.О.)"
oSheet.Cells(l1 + 9, 1).Value = "Главный бухгалтер __________________________________________ " & _
"(Фамилия И.О.)"
End If
End If
REM работа с памятью
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRange)
oRange = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet)
oSheet = Nothing
sFile = Now.Year & "-" & Now.Month & "-" & Now.Day & "~" & Now.Hour & "-" & Now.Minute & "-" & Now.Second & ".xls"
oBook.SaveAs(FileName:=dataDir4 & sFile) ', ConflictResolution:=xlLocalSessionChanges'xlLocalSessionChanges=2
oBook.Close(SaveChanges:=False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
GC.Collect()
'GC.WaitForPendingFinalizers()
'GC.Collect()
sWin = "../MskServer/BD/Xls/Счета на оплату/" & sFile
End Sub
2.2. Проперти toWin. Напоминаю, что этот проперти содержит яваскрипт,
необходимый для открытия Экселя в отдельном окне Интернет Эксплорера.
Если Вы программируете не под Интернет, а под Windows, то не пишите этот
код.
Public ReadOnly Property toWin() As String
Get
If sWin <> "" Then
Return "<script language='javascript'>window.open('" & sWin & "','w1','toolbar=yes,location=yes,_
directories=yes,status=yes,menubar=yes,scrollbars=yes');</script>"
End If
End Get
End Property
2.3. Процедура запуска вывода счёта в Эксель.
Private Sub ImageButton2_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs)
Handles ImageButton2.Click
ToExcel()
GC.Collect()
End Sub
2.4. Осталось добавить ссылку на проперти toWin в HTML-представление
Web-формы. Если Вы пишите под Windows, не обращайте внимания на этот
пункт.
Приведённый пример работает быстро и качественно. Это происходит
потому, что он выполнен в строгом соответствии с моими рекомендациями.
Думаю, Вам всё понятно. Но на всякий случай уточню, что запросы SQL я
сдесь беру через специальную функцию собственного изобретения. Вы можете
их брать по-своему. Листинг функции я не привожу в рамках данной статьи,
так как это уже другая тема разговора. Возможно я Вам её приведу в
другой статье - следите за следующими выпусками.
Заключение
Друзья! Если у Вас есть сомнения - стоит ли использовать Эксель в
своей работе - отбросьте их! Эксель генерит самые лучшие и красивые
отчёты, можете мне поверить на слово. Вы можете добавить на лист
диаграмму, картинку; Вы можете использовать сводные таблицы. А вместо
скалькулированных значений - использовать формулы. Выдайте руководству
не бумажку, а экселевский файл - и к Вам будет меньше претензий и лишних
вопросов. В конце статьи напоминаю Вам, что современная версия Экселя
поддерживает на одном листе 256 колонок и 65536 строк.
Будьте здоровы! |
Форум Программиста
Новости
Обзоры
Магазин Программиста
Каталог ссылок
Поиск
Добавить файл
Обратная связь 