Исходники.Ру - Программирование
Исходники
Статьи
Книги и учебники
Скрипты
Новости RSS
Магазин программиста

Главная » Статьи по программированию » .NET - Все статьи »

Обсудить на форуме Обсудить на форуме

Как правильно работать с Excel в .NET

Введение

Друзья, до сих пор в Интернете я не встречал решения проблемы снятия процесса 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 строк.

Будьте здоровы!


Может пригодится:


Автор: Максим Прохоров
Прочитано: 31398
Рейтинг:
Оценить: 1 2 3 4 5

Комментарии: (3)

Прислал: Василий
ну да полезная прога

Прислал: Петя
Как сделать что при передачи данных автоматически подбиралась под них ширина

Прислал: Печкин
Разбирать буду, а так ничего вроде бы

Добавить комментарий
Ваше имя*:
Ваш email:
URL Вашего сайта:
Ваш комментарий*:
Код безопастности*:

Рассылка новостей
Рейтинги
© 2007, Программирование Исходники.Ру