Тема: Создание модуля печати данных о приходе и расходе
Цель: Научиться создавать различные отчеты в Excel из программ написанных на Delphi.
Оборудование и/или программное обеспечение: ПК, Delphi, Excel
Теоретическая часть
Здесь найдете информацию о том как: - Подключить и правильно отключить интерфейс Excel; - Как изменить размер, цвет и тип шрифта; - Как выделить, объединить, заполнить и размножить диапазон ячеек; - Как повернуть и отцентрировать текст; - Как нарисовать границы ячеек; - Как ввести формулу в ячейку и многое другое... Работать будем через модуль ComObj, для этого вuses необходимо добавить модуль ComObj и модуль Excel_TLB (для MS Excel 2007). Uses ……, ComObj, Excel_TLB; Модуль Excel_TLB содержит необходимые константы для работы сExcel, его можно не подключать, но тогда придется в ручную прописывать значения всех используемых констант из этого модуля. Значения констант можно найти внутри модуля или в интернете, но для разных версий MS Excel они разные. Внимание!!! МодульExcel_TLB в других версияхMS Excel может называться по другому. Перед подключением модуля Excel_TLB, необходимо импортировать библиотеку Excel. Для этого выберите Component->Import Component->Import a Type Library-> находимMS Excel и следуем инструкциям.
В разделе описания переменных мы должны описать переменную типа Variant или OleVariant для подключения интерфейса Excel.Я описал переменную excel. var Form1: TForm1; excel: variant; // Переменная в которой создаётся объект EXCEL
Создание документа Внимание!!! Всегда когда создаете объект интерфейса, заключайте процедуру создания в модуль обработки ошибок: try создаем интерфейс; формируем отчет; освобождаем интерфейс; Except обрабатываем ошибки; освобождаем интерфейс; end; Далее идет краткий справочник по основным функциям работы с EXCEL try // создаем обьект EXCEL excel := CreateOleObject('Excel.Application'); // Чтоб не задавал вопрос о сохранении документа excel.DisplayAlerts := false; // создаем новый документ рабочую книгу excel.WorkBooks.Add;
// или загружаем его из директории с программой excel.WorkBooks.Open(GetCurrentDir() + '\отчет.xls'); { GetCurrentDir()- возвращает путь к директории с программой} // Делаем его видимым данную функцию после отладки и тестирования лучше использовать в конце, после сформирования отчета (это ускоряет процесс вывода данных в отчет) excel.Visible := true; //задаем тип формул в формате R1C1 excel.Application.ReferenceStyle := xlR1C1; // задаем тип формул в формате A1 excel.Application.ReferenceStyle := xlA1; // Задаем название первому и второму листу excel.WorkBooks[1].WorkSheets[1].Name := 'Отчет1'; excel.WorkBooks[1].WorkSheets[2].Name := 'Отчет2'; //задаем формат числа для первой и четвертой колонки формат числа excel.WorkBooks[1].WorkSheets[1].Columns[1].NumberFormat := '0,00'; excel.WorkBooks[1].WorkSheets[1].Columns[4].NumberFormat := '0,0'; // задаем ширину первой и второй колонки excel.WorkBooks[1].WorkSheets[1].Columns[1].ColumnWidth := 10; excel.WorkBooks[1].WorkSheets[1].Columns[2].ColumnWidth := 20; // задаем начертание, цвет, размер и тип шрифта для первого ряда excel.WorkBooks[1].WorkSheets[1].Rows[1].Font.Bold := True; //жирный excel.WorkBooks[1].WorkSheets[1].Rows[1].Font.Color := clRed; // цвет красный excel.WorkBooks[1].WorkSheets[1].Rows[1].Font.Size := 12;//размер 12 excel.WorkBooks[1].WorkSheets[1].Rows[1].Font.Name := 'Times New Roman';//шрифт //присваиваем ячейке 1,4 и 2,4 значения (1 - ряд, 4 - колонка) excel.WorkBooks[1].WorkSheets[1].Cells[1, 4] := 'А так можно внести значение в ячейку'; excel.WorkBooks[1].WorkSheets[1].Cells[2, 4] := 'А так можно внести значение в ячейку'; //ввод в ячейку 'A12' формулы '=b5+c4' excel.WorkBooks[1].WorkSheets[1].Range['A12'].Formula:='=b5+c4'; // Выравнивам первый ряд по центру по вертикали excel.WorkBooks[1].WorkSheets[1].Rows[1].VerticalAlignment := xlCenter; // Выравнивам первый ряд по центру по горизонтали excel.WorkBooks[1].WorkSheets[1].Rows[1].HorizontalAlignment := xlCenter; // Выравнивам в ячейке по левому краю excel.WorkBooks[1].WorkSheets[1].Cells[3, 2].HorizontalAlignment := xlLeft; // Выравнивам в ячейке по правому краю excel.WorkBooks[1].WorkSheets[1].Cells[3, 4].HorizontalAlignment := xlRight; // Обьединяем ячейки 'A1:A8' excel.WorkBooks[1].WorkSheets[1].Range['A1:A8'].Merge; // Поворачиваем слова под углом 90 градусов для второго ряда excel.WorkBooks[1].WorkSheets[1].Rows[2].Orientation := 90; // Поворачиваем слова под углом 45 градусов для диапазона ячеек 'B3:D3' excel.WorkBooks[1].WorkSheets[1].Range['B3:D3'].Orientation := 45; //рисуем границы выделенного диапазона левая excel.Selection.Borders[xlEdgeLeft].LineStyle := xlContinuous; //стиль линии сплошная excel.Selection.Borders[xlEdgeLeft].Weight := xlMedium;//толщина линии //рисуем границы выделенного диапазона верхняя excel.Selection.Borders[xlEdgeTop].LineStyle := xlContinuous; excel.Selection.Borders[xlEdgeTop].Weight := xlMedium; //рисуем границы выделенного диапазона нижняя excel.Selection.Borders[xlEdgeBottom].LineStyle := xlContinuous; excel.Selection.Borders[xlEdgeBottom].Weight := xlMedium; //рисуем границы выделенного диапазона правая excel.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous; excel.Selection.Borders[xlEdgeRight].Weight := xlMedium; //рисуем границы выделенного диапазона вертикальные внутрениие excel.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous; excel.Selection.Borders[xlInsideVertical].Weight := xlMedium; //рисуем границы выделенного диапазона горизонтальные внутрениие excel.Selection.Borders[xlInsideHorizontal].LineStyle := xlContinuous; excel.Selection.Borders[xlInsideHorizontal].Weight := xlMedium; //автозаполнение выделенного диапазона //для примера заполним область ячеек 'A10:C10' словом 'привет' //и размножим его вниз еще на пять ячеек 'A10:C15' excel.WorkBooks[1].WorkSheets[1].Range['A10:C10'].Value:='привет'; //выделяем диапазон ячеек 'A10:C10' excel.WorkBooks[1].WorkSheets[1].Range['A10:C10'].Select; //автозаполняем (копируем) выделенным диапазоном область ячеек 'A10:C15' excel.selection.autofill(excel.WorkBooks[1].WorkSheets[1].Range['A10:C15'],xlFillDefault); //отключаем предупреждения, чтобы не задавал вопросов о сохранении и других excel.DisplayAlerts := False; //сохраняем документ в формате Excel 97-2003 excel.ActiveWorkBook.Saveas(GetCurrentDir() + '\отчет.xls',xlExcel8); //сохраняем документ в текущем формате Excel 2007 excel.ActiveWorkBook.Saveas(GetCurrentDir() + '\отчет.xlsx'); //закроем все книги excel.Workbooks.Close; //закрываем Excel excel.Application.quit; //освобождаем интерфейсы excel := Unassigned; Except //обрабатываем ошибки showmessage('Внимание! Произошла ошибка при создании MS Excel приложения'); //закроем все книги excel.Workbooks.Close; //закрываем Excel excel.Application.quit; //освобождаем интерфейсы excel := Unassigned; end; end; При работе с листом Excelмы можем использовать следующие варианты: - работать с областью Range['B3:D3']; - работать с ячейкой Cells[2, 4] где 2 - ряд, 4 - колонка; - работать с рядами Rows[1] или с диапазоном рядов Rows['1:5']; - работать с колонками Columns[1] или диапазоном колонок Columns['A:I']; Range['A1'] и Cells[1, 1] обозначают одно и тоже. После сформирования документа или возникновении ошибки вы должны правильно освободить интерфейсы. Иначе при закрытии Excelон скрывается с экрана, но если открыть диспетчер задач он продолжает там висеть и если данный процесс не завершить , то при каждом новом запуске их будет накапливаться больше и больше, пока компьютер не начнет виснуть. Поэтому обязательно необходимо освобождать все интерфейсы с вязанные с Excel с его книгами и листами. Например так: //закроем все книги excel.Workbooks.Close; //закрываем Excel excel.Application.quit; //освобождаем интерфейсы sheet:=Unassigned; //интерфейс листа если он был создан WorkBook := Unassigned;//интерфейс рабочей книги если он был создан excel := Unassigned;//интерфейс самого предложения если он был создан end;
Практическая часть
На этом уроке мы реализуем печать данных прихода, расхода и состояния склада. Печать будем реализовывать через MS Excel, то есть это будет не совсем печать, а экспорт данных в MS Excel, а там пользователь может сам выбрать, печатать или сохранить отчет. В начале приготовим шаблоны MS Excel, в которые будем выводить отчеты.
Шаблон ведомость по приходу продуктов питания.
В шаблоне использован стиль ссылок R1C1. ВключаетсяПараметры->Формулы-> Стиль ссылок R1C1. Файл сохраняем с типом файла Шаблон Excel 97-2003.
Результат работы программы:
Шаблон ведомость по расходу продуктов питания.
Шаблон остатки на складе.
Далее в формы прихода и расхода внесем несколько изменений, необходимых для фильтрации документов прихода и расхода по дате. Добавим на формы прихода и расхода по два компонента TDateTimePicker из вкладки Win32и компонент TCheckBox из вкладки Standart. Кнопку Button для вызова печати. Размещаем, как показано на рисунке:
Аналогично для формы расхода. В инспекторе объектов для обеих DateTimePicker ов обнуляем свойство Time.
А для события OnChange прописываем следующий код:
procedure TForm_prihod.DateTimePicker1Change(Sender: TObject); begin //Проверка установленных дат if datetimepicker1.Date>datetimepicker2.Date then begin ShowMessage('Внимание. Начальная дата прихода больше конечной'); checkbox1.Checked:=false; DateTimePicker1.Date:=DateUtils.StartOfTheMonth(now); DateTimePicker2.Date:=date; end; end; procedure TForm_prihod.DateTimePicker2Change(Sender: TObject); begin //Проверка установленных дат if datetimepicker1.Date>datetimepicker2.Date then begin ShowMessage('Внимание. Начальная дата прихода больше конечной'); checkbox1.Checked:=false; DateTimePicker1.Date:=DateUtils.StartOfTheMonth(now); DateTimePicker2.Date:=date; end; end;
А для события OnClick компонента CheckBox1 пишем:
procedure TForm_prihod. Click(Sender: TObject); //включение фильтра begin dm.table_prihod.Filtered:=checkbox1.Checked; if checkbox1.Checked=true then dm.table_prihod.Filter:='(date_prihoda>='+datetostr(datetimepicker1.Date)+') and ('+ 'date_prihoda<='+datetostr(datetimepicker2.Date)+')'; end;
Аналогичные операции проделываем с формой расхода.
Далее переходим в DataModule (Unit_dm) и размещаем там компонент TADOQueryиз вкладки dbGo (ADO). В свойстве Name задаем имя ADOQuery_print, в свойство Connection->Form_general.ADOConnection1. Затем из вкладки Data Access размещаем компонент TDataSource в свойстве Name задаем имя print, а в свойство DataSet->ADOQuery_print.
Сейчас создадим отдельный модуль для печати. Выбираем File->New->Unit – Delphi. Назовем его print.
Подключение интерфейсаExcel происходит через модульcomobj.
Ниже привожу полный текст модуля с комментариями:
unit print; interface Uses Windows, Dialogs, SysUtils, Variants, DB, Excel_TLB, comobj, unit_dm; {Внимание!!! Перед подключением модуля Excel_TLB, необходимо импортировать библиотеку Excel. Для этого выберите Component->Import Component->Import a Type Library-> находим MS Excel и следуем инструкциям} function CreateApplication(FileName:string):boolean; procedure print_prihod(date_start,date_end:TDateTime;check:boolean); procedure print_rashod(date_start,date_end:TDateTime;check:boolean); procedure print_ostatki; var exl: OleVariant; WorkBook, Sheet: Variant; implementation function CreateApplication(FileName:string):boolean; //создаем приложение excel begin try //Создаем объект интерфейса для доступа к серверу COM exl := CreateOleObject('Excel.Application'); // Отключаем реакцию Excel на события, //чтобы ускорить вывод информации exl.Application.EnableEvents := false; //Создаем книгу и обращаемся к первому листу Workbook := exl.Application.WorkBooks.Add(GetCurrentDir()+FileName); Sheet := WorkBook.WorkSheets[1]; result:=true; Except showmessage('Внимание! Произошла ошибка при создании MS Excel приложения'); result:=false; //освобождаем интерфейсы Sheet := Unassigned; WorkBook := Unassigned; exl := Unassigned; end; end; //печать прихода procedure print_prihod(date_start,date_end:TDateTime;check:boolean); var ArrayData,ArrayData1,ArrayData2: Variant; x,y,kdx,ndx,ndy,kdy,n,m,i:integer; //ndx, ndy -начало диапазона по оси х (вправо) и по оси у (вниз) //kdx, kdy -конец диапазона по оси х и по оси у // ArrayData - двухмерный массив для продуктов // ArrayData1 - двухмерный массив для единиц измерения // ArrayData2 - массив для дат begin if CreateApplication('\Шаблоны\Ведомость прихода продуктов.xlt')=false then exit; try //делаем запрос на выбор продуктов и единиц измерения прихода //и заполняем вариантный массив для продуктов и для единиц измерения dm.ADOQuery_print.Active:=false; dm.ADOQuery_print.SQL.Clear; dm.ADOQuery_print.SQL.Add('SELECT products.product_name, ed_izmer.ed_name FROM prihod LEFT JOIN ((storage LEFT JOIN products ON storage.id_product = products.id) LEFT JOIN ed_izmer'+ ' ON storage.id_ed_izmer = ed_izmer.id) ON prihod.id = storage.id_prihod GROUP BY products.product_name, ed_izmer.ed_name;'); dm.ADOQuery_print.Active:=True; y:=dm.print.DataSet.RecordCount;//количество записей по продуктам в приходе ArrayData := VarArrayCreate([1, y*2,1,1], varVariant); //двухмерный массив для продуктов ArrayData1 := VarArrayCreate([1, y*2,1,1], varVariant); //двухмерный массив для единиц измерения dm.ADOQuery_print.First; for i:=1 to y*2 do //умножаем на два так как в шаблоне для наименования продукта //используется высота ячейки в две клетки, приходится первую заполнять, а вторую пропускать begin if (i mod 2)<>0 then begin //шаг - каждый второй ArrayData[i,1] :=dm.ADOQuery_print.FieldByName('product_name').AsString; //заполняем продукт ArrayData[i+1,1] :=''; // оставляем пустой ArrayData1[i,1] :=dm.ADOQuery_print.FieldByName('ed_name').AsString; //заполняем единицу измерения ArrayData1[i+1,1] :='';// оставляем пустой if dm.ADOQuery_print.eof<>true then dm.ADOQuery_print.next; end; end; //выполняем запрос на выбор даты прихода и заполняем вариантный массив2 dm.ADOQuery_print.Active:=false; dm.ADOQuery_print.SQL.Clear; //проверяем включен ли фильтр и формируем соответствующий запрос по дате или без if check=false then dm.ADOQuery_print.SQL.Add('SELECT prihod.date_prihoda FROM prihod GROUP BY prihod.date_prihoda ORDER BY prihod.date_prihoda;') else begin dm.ADOQuery_print.Parameters.AddParameter.Name:='date1'; dm.ADOQuery_print.Parameters.ParamByName('date1').DataType:=ftDateTime; dm.ADOQuery_print.Parameters.AddParameter.Name:='date2'; dm.ADOQuery_print.Parameters.ParamByName('date2').DataType:=ftDateTime; dm.ADOQuery_print.SQL.Add('SELECT prihod.date_prihoda FROM prihod GROUP BY prihod.date_prihoda HAVING (((prihod.date_prihoda)>=:date1 and (prihod.date_prihoda)<=:date2)) ORDER BY prihod.date_prihoda; '); dm.ADOQuery_print.Parameters.ParamByName('date1').Value:=date_start; dm.ADOQuery_print.Parameters.ParamByName('date2').Value:=date_end; end; dm.ADOQuery_print.Active:=True; x:=dm.print.DataSet.RecordCount;//количество записей дат ArrayData2 := VarArrayCreate([1, x,1,1], varVariant);//массив для дат dm.ADOQuery_print.First; for i:=1 to x do begin //заполняем массив датами ArrayData2[i,1] :=dm.ADOQuery_print.FieldByName('date_prihoda').AsString; if dm.ADOQuery_print.eof<>true then dm.ADOQuery_print.next; end; // рисуем поле данных ndy:=24; ndx:=18; kdx:=18+x*4-1; kdy:=25; //выделение диапазона ячеек sheet.Range[sheet.cells[24,18],sheet.cells[25,21]].Select; //объеденение ячеек sheet.Range[sheet.cells[24,18],sheet.cells[25,21]].Merge; //рисуем поле с цифрами ndy:=23; ndx:=18; kdx:=18+x*4-1; kdy:=23; sheet.Range[sheet.cells[23,18],sheet.cells[23,21]].Select; sheet.Range[sheet.cells[23,18],sheet.cells[23,21]].Merge; Exl.Selection.HorizontalAlignment:=xlCenter; sheet.cells[23,18].value:=3; //рисуем поле с датами ndy:=17; ndx:=18; kdx:=18+x*4-1; kdy:=22; sheet.Range[sheet.cells[17,18],sheet.cells[22,21]].Select; sheet.Range[sheet.cells[17,18],sheet.cells[22,21]].Merge; exl.Selection.Orientation := 90; Exl.Selection.HorizontalAlignment:=xlCenter; Exl.Selection.VerticalAlignment:=xlCenter; //общее выделение и размножение вправо if x>1 then begin ndy:=17; ndx:=18; kdx:=18+x*4-1; kdy:=25; sheet.Range[sheet.cells[17,18],sheet.cells[25,21]].Select; //автозаполнение выделенного диапазона exl.selection.autofill(sheet.Range[sheet.cells[ndy,ndx], sheet.cells[kdy,kdx]], xlFillDefault); end; //рисуем шапку над датой ndy:=15; ndx:=18; kdx:=18+x*4-1; kdy:=16; sheet.Range[sheet.cells[15,18],sheet.cells[16,kdx]].Select; sheet.Range[sheet.cells[15,18],sheet.cells[16,kdx]].Merge; // рисуем поле итоги sheet.Range[sheet.cells[15,kdx+1],sheet.cells[22,kdx+5]].Select; sheet.Range[sheet.cells[15,kdx+1],sheet.cells[22,kdx+5]].Merge; sheet.cells[15,kdx+1].value:='Итого'; Exl.Selection.HorizontalAlignment:=xlCenter; sheet.Range[sheet.cells[23,kdx+1],sheet.cells[23,kdx+5]].Select; sheet.Range[sheet.cells[23,kdx+1],sheet.cells[23,kdx+5]].Merge; sheet.cells[23,kdx+1].value:=3+x; Exl.Selection.HorizontalAlignment:=xlCenter; sheet.Range[sheet.cells[24,kdx+1],sheet.cells[25,kdx+5]].Select; sheet.Range[sheet.cells[24,kdx+1],sheet.cells[25,kdx+5]].Merge; //вводим формулу суммы sheet.cells[24,kdx+1].value:='=SUM(RC[-'+inttostr(x*4)+']:R[1]C[-1])'; //выделяем и рисуем границы шапки таблицы sheet.Range[sheet.cells[15,18],sheet.cells[23,kdx+5]].Select; exl.Selection.Borders[xlEdgeLeft].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeLeft].Weight := xlMedium; exl.Selection.Borders[xlEdgeTop].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeTop].Weight := xlMedium; exl.Selection.Borders[xlEdgeBottom].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeBottom].Weight := xlMedium; exl.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeRight].Weight := xlMedium; exl.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideVertical].Weight := xlMedium; exl.Selection.Borders[xlInsideHorizontal].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideHorizontal].Weight := xlMedium; ndy:=24; ndx:=2; kdx:=18+x*4-1; kdy:=24+y*2-1; //общее выделение и размножение вниз if y>1 then begin sheet.Range[sheet.cells[ndy,ndx],sheet.cells[ndy+1,kdx+5]].Select; exl.selection.autofill(sheet.Range[sheet.cells[ndy,ndx], sheet.cells[kdy,kdx+5]],xlfillcopy); end; //выделяем и рисуем границы данныx sheet.Range[sheet.cells[24,18],sheet.cells[kdy,kdx+5]].Select; exl.Selection.NumberFormat:='0,000'; exl.Selection.Borders[xlEdgeTop].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeTop].Weight := xlMedium; exl.Selection.Borders[xlEdgeBottom].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeBottom].Weight := xlMedium; exl.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeRight].Weight := xlMedium; exl.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideVertical].Weight := xlThin; exl.Selection.Borders[xlInsideHorizontal].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideHorizontal].Weight := xlThin; //заполняем продуктами и еденицами измерения sheet.Range[sheet.cells[24,2],sheet.cells[kdy,14]].value:= ArrayData; sheet.Range[sheet.cells[24,15],sheet.cells[kdy,17]].value:= ArrayData1; //в зависимости от фильтра по дате заполняем шапку датами начала периода и конца периода if check=false then sheet.cells[8,27].value:=ArrayData2[1,1]+' - '+ArrayData2[x,1] else sheet.cells[8,27].value:=DateToStr(date_start)+' - '+DateToStr(date_end); // заполняем таблицу датами dm.ADOQuery_print.First; for i:=1 to x do begin sheet.cells[17,14+i*4].value:= dm.ADOQuery_print.fieldbyname('date_prihoda').AsString; if dm.ADOQuery_print.eof<>true then dm.ADOQuery_print.next; end; //запрос на выбор продукта, единицы измерения, даты прихода и суммы //формируем запрос и заполняем таблицу данными dm.ADOQuery_print.Active:=false; dm.ADOQuery_print.SQL.Clear; dm.ADOQuery_print.SQL.Add('SELECT products.product_name, ed_izmer.ed_name, prihod.date_prihoda, Sum(storage.quantity) AS [Sum-quantity]'+ ' FROM prihod LEFT JOIN ((storage LEFT JOIN products ON storage.id_product = products.id) LEFT JOIN ed_izmer ON storage.id_ed_izmer = ed_izmer.id) ON prihod.id = storage.id_prihod'+ ' GROUP BY products.product_name, ed_izmer.ed_name, prihod.date_prihoda ORDER BY prihod.date_prihoda; '); dm.ADOQuery_print.Active:=True; dm.ADOQuery_print.First; for m:=1 to y*2 do for n:=1 to x do if (m mod 2)<>0 then begin //выбираем продукт и единицу измерения и ищем совпадение по дате прихода if (dm.ADOQuery_print.Locate('product_name;ed_name;date_prihoda', VarArrayOf([ArrayData[m,1], ArrayData1[m,1],ArrayData2[n,1]]),[loCaseInsensitive, loPartialKey])) then begin sheet.cells[23+m,14+n*4].value:= dm.ADOQuery_print.fieldbyname('Sum-quantity').Value; end; end; //показываем excel exl.visible:=true; //освобождаем память и интерфейс excel ArrayData := Unassigned; ArrayData1 := Unassigned; ArrayData2 := Unassigned; Sheet := Unassigned; WorkBook := Unassigned; exl := Unassigned; Except //в случае ошибки освобождаем ресурсы showmessage('Внимание! Произошла ошибка при создании отчета'); exl.DisplayAlerts := False; // отключаем предупреждения exl.Workbooks.Close; // закроем все книги exl.Application.quit; ArrayData := Unassigned; ArrayData1 := Unassigned; ArrayData2 := Unassigned; Sheet := Unassigned; WorkBook := Unassigned; exl := Unassigned; end; end; //печать расхода procedure print_rashod(date_start,date_end:TDateTime;check:boolean); var ArrayData,ArrayData1,ArrayData2: Variant; x,y,kdx,ndx,ndy,kdy,n,m,i:integer; //ndx, ndy -начало диапазона по оси х (вправо) и по оси у (вниз) //kdx, kdy -конец диапазона по оси х и по оси у // ArrayData - двухмерный массив для продуктов // ArrayData1 - двухмерный массив для единиц измерения // ArrayData2 - массив для дат begin if CreateApplication('\Шаблоны\Ведомость расхода продуктов.xlt')=false then exit; try //делаем запрос на выбор продуктов и единиц измерения прихода //и заполняем вариантный массив для продуктов и для единиц измерения dm.ADOQuery_print.Active:=false; dm.ADOQuery_print.SQL.Clear; dm.ADOQuery_print.SQL.Add('SELECT products.product_name, ed_izmer.ed_name FROM rashod_doc LEFT JOIN ((rashod LEFT JOIN products ON rashod.id_product = products.id) LEFT JOIN ed_izmer'+ ' ON rashod.id_ed_izmer = ed_izmer.id) ON rashod_doc.id = rashod.id_rashod_doc GROUP BY products.product_name, ed_izmer.ed_name;'); dm.ADOQuery_print.Active:=True; y:=dm.print.DataSet.RecordCount;//количество записей по продуктам в приходе ArrayData := VarArrayCreate([1, y*2,1,1], varVariant); //двухмерный массив для продуктов ArrayData1 := VarArrayCreate([1, y*2,1,1], varVariant); //двухмерный массив для единиц измерения dm.ADOQuery_print.First; for i:=1 to y*2 do //умножаем на два так как в шаблоне для наименования продукта //используется высота ячейки в две клетки, приходится первую заполнять, а вторую пропускать begin if (i mod 2)<>0 then begin //шаг - каждый второй ArrayData[i,1] := dm.ADOQuery_print.FieldByName('product_name').AsString; //заполняем продукт ArrayData[i+1,1] :=''; // оставляем пустой ArrayData1[i,1] := dm.ADOQuery_print.FieldByName('ed_name').AsString; //заполняем единицу измерения ArrayData1[i+1,1] :='';// оставляем пустой if dm.ADOQuery_print.eof<>true then dm.ADOQuery_print.next; end; end; //выполняем запрос на выбор даты прихода и заполняем вариантный массив2 dm.ADOQuery_print.Active:=false; dm.ADOQuery_print.SQL.Clear; //проверяем включен ли фильтр и формируем соответствующий запрос по дате или без if check=false then dm.ADOQuery_print.SQL.Add('SELECT rashod_doc.date_rashoda FROM rashod_doc GROUP BY rashod_doc.date_rashoda ORDER BY rashod_doc.date_rashoda;') else begin dm.ADOQuery_print.Parameters.AddParameter.Name:='date1'; dm.ADOQuery_print.Parameters.ParamByName('date1').DataType:=ftDateTime; dm.ADOQuery_print.Parameters.AddParameter.Name:='date2'; dm.ADOQuery_print.Parameters.ParamByName('date2').DataType:=ftDateTime; dm.ADOQuery_print.SQL.Add('SELECT rashod_doc.date_rashoda FROM rashod_doc GROUP BY rashod_doc.date_rashoda HAVING (((rashod_doc.date_rashoda)>=:date1 and (rashod_doc.date_rashoda)<=:date2)) ORDER BY rashod_doc.date_rashoda; '); dm.ADOQuery_print.Parameters.ParamByName('date1').Value:=date_start; dm.ADOQuery_print.Parameters.ParamByName('date2').Value:=date_end; end; dm.ADOQuery_print.Active:=True; x:=dm.print.DataSet.RecordCount;//количество записей дат ArrayData2 := VarArrayCreate([1, x,1,1], varVariant);//массив для дат dm.ADOQuery_print.First; for i:=1 to x do begin //заполняем массив датами ArrayData2[i,1] :=dm.ADOQuery_print.FieldByName('date_rashoda').AsString; if dm.ADOQuery_print.eof<>true then dm.ADOQuery_print.next; end; // рисуем поле данных ndy:=24; ndx:=18; kdx:=18+x*4-1; kdy:=25; //выделение диапазона ячеек sheet.Range[sheet.cells[24,18],sheet.cells[25,21]].Select; //объеденение ячеек sheet.Range[sheet.cells[24,18],sheet.cells[25,21]].Merge; //рисуем поле с цифрами ndy:=23; ndx:=18; kdx:=18+x*4-1; kdy:=23; sheet.Range[sheet.cells[23,18],sheet.cells[23,21]].Select; sheet.Range[sheet.cells[23,18],sheet.cells[23,21]].Merge; Exl.Selection.HorizontalAlignment:=xlCenter; sheet.cells[23,18].value:=3; //рисуем поле с датами ndy:=17; ndx:=18; kdx:=18+x*4-1; kdy:=22; sheet.Range[sheet.cells[17,18],sheet.cells[22,21]].Select; sheet.Range[sheet.cells[17,18],sheet.cells[22,21]].Merge; exl.Selection.Orientation := 90; Exl.Selection.HorizontalAlignment:=xlCenter; Exl.Selection.VerticalAlignment:=xlCenter; //общее выделение и размножение вправо if x>1 then begin ndy:=17; ndx:=18; kdx:=18+x*4-1; kdy:=25; sheet.Range[sheet.cells[17,18],sheet.cells[25,21]].Select; //автозаполнение выделенного диапазона exl.selection.autofill(sheet.Range[sheet.cells[ndy,ndx], sheet.cells[kdy,kdx]], xlFillDefault); end; //рисуем шапку над датой ndy:=15; ndx:=18; kdx:=18+x*4-1; kdy:=16; sheet.Range[sheet.cells[15,18],sheet.cells[16,kdx]].Select; sheet.Range[sheet.cells[15,18],sheet.cells[16,kdx]].Merge; // рисуем поле итоги sheet.Range[sheet.cells[15,kdx+1],sheet.cells[22,kdx+5]].Select; sheet.Range[sheet.cells[15,kdx+1],sheet.cells[22,kdx+5]].Merge; sheet.cells[15,kdx+1].value:='Итого'; Exl.Selection.HorizontalAlignment:=xlCenter; sheet.Range[sheet.cells[23,kdx+1],sheet.cells[23,kdx+5]].Select; sheet.Range[sheet.cells[23,kdx+1],sheet.cells[23,kdx+5]].Merge; sheet.cells[23,kdx+1].value:=3+x; Exl.Selection.HorizontalAlignment:=xlCenter; sheet.Range[sheet.cells[24,kdx+1],sheet.cells[25,kdx+5]].Select; sheet.Range[sheet.cells[24,kdx+1],sheet.cells[25,kdx+5]].Merge; //вводим формулу суммы sheet.cells[24,kdx+1].value:='=SUM(RC[-'+inttostr(x*4)+']:R[1]C[-1])'; //выделяем и рисуем границы шапки таблицы sheet.Range[sheet.cells[15,18],sheet.cells[23,kdx+5]].Select; exl.Selection.Borders[xlEdgeLeft].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeLeft].Weight := xlMedium; exl.Selection.Borders[xlEdgeTop].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeTop].Weight := xlMedium; exl.Selection.Borders[xlEdgeBottom].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeBottom].Weight := xlMedium; exl.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeRight].Weight := xlMedium; exl.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideVertical].Weight := xlMedium; exl.Selection.Borders[xlInsideHorizontal].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideHorizontal].Weight := xlMedium; ndy:=24; ndx:=2; kdx:=18+x*4-1; kdy:=24+y*2-1; //общее выделение и размножение вниз if y>1 then begin sheet.Range[sheet.cells[ndy,ndx], sheet.cells[ndy+1,kdx+5]].Select; exl.selection.autofill(sheet.Range[sheet.cells[ndy,ndx], sheet.cells[kdy,kdx+5]], xlfillcopy); end; //выделяем и рисуем границы данныx sheet.Range[sheet.cells[24,18],sheet.cells[kdy,kdx+5]].Select; exl.Selection.NumberFormat:='0,000'; exl.Selection.Borders[xlEdgeTop].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeTop].Weight := xlMedium; exl.Selection.Borders[xlEdgeBottom].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeBottom].Weight := xlMedium; exl.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeRight].Weight := xlMedium; exl.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideVertical].Weight := xlThin; exl.Selection.Borders[xlInsideHorizontal].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideHorizontal].Weight := xlThin; //заполняем продуктами и еденицами измерения sheet.Range[sheet.cells[24,2],sheet.cells[kdy,14]].value:= ArrayData; sheet.Range[sheet.cells[24,15],sheet.cells[kdy,17]].value:= ArrayData1; //в зависимости от фильтра по дате заполняем шапку датами начала периода и конца периода if check=false then sheet.cells[8,27].value:= ArrayData2[1,1]+' - '+ ArrayData2[x,1] else sheet.cells[8,27].value:= DateToStr(date_start) + ' - ' + DateToStr(date_end); // заполняем таблицу датами dm.ADOQuery_print.First; for i:=1 to x do begin sheet.cells[17,14+i*4].value:= dm.ADOQuery_print.fieldbyname('date_rashoda').AsString; if dm.ADOQuery_print.eof<>true then dm.ADOQuery_print.next; end; //запрос на выбор продукта, единицы измерения, даты расхода и суммы //формируем запрос и заполняем таблицу данными dm.ADOQuery_print.Active:=false; dm.ADOQuery_print.SQL.Clear; dm.ADOQuery_print.SQL.Add('SELECT products.product_name, ed_izmer.ed_name, rashod_doc.date_rashoda, Sum(rashod.quantity) AS [Sum-quantity]'+ ' FROM rashod_doc LEFT JOIN ((rashod LEFT JOIN products ON rashod.id_product = products.id) LEFT JOIN ed_izmer ON rashod.id_ed_izmer = ed_izmer.id) ON rashod_doc.id = rashod.id_rashod_doc'+ ' GROUP BY products.product_name, ed_izmer.ed_name, rashod_doc.date_rashoda ORDER BY rashod_doc.date_rashoda; '); dm.ADOQuery_print.Active:=True; dm.ADOQuery_print.First; for m:=1 to y*2 do for n:=1 to x do if (m mod 2)<>0 then begin //выбираем продукт и единицу измерения и ищем совпадение по дате прихода if (dm.ADOQuery_print.Locate('product_name;ed_name;date_rashoda', VarArrayOf([ArrayData[m,1], ArrayData1[m,1], ArrayData2[n,1]]), [loCaseInsensitive, loPartialKey])) then begin sheet.cells[23+m,14+n*4].value:=dm.ADOQuery_print.fieldbyname('Sum-quantity').Value; end; end; //показываем excel exl.visible:=true; //освобождаем память и интерфейс excel ArrayData := Unassigned; ArrayData1 := Unassigned; ArrayData2 := Unassigned; Sheet := Unassigned; WorkBook := Unassigned; exl := Unassigned; Except //в случае ошибки освобождаем ресурсы showmessage('Внимание! Произошла ошибка при создании отчета'); exl.DisplayAlerts := False; // отключаем предупреждения exl.Workbooks.Close; // закроем все книги exl.Application.quit; ArrayData := Unassigned; ArrayData1 := Unassigned; ArrayData2 := Unassigned; Sheet := Unassigned; WorkBook := Unassigned; exl := Unassigned; end; end; //Печать остатков на складе procedure print_ostatki; var ArrayData: Variant; i:integer; begin //если остатков нет выходим if dm.ostatki.DataSet.RecordCount=0 then begin showmessage('На складе нет остатков'); exit; end; //создаем интерфейс Excel if CreateApplication('\Шаблоны\Остатки на складе.xlt')=false then exit; try begin //объявляем вариантный массив ArrayData := VarArrayCreate([1, dm.ostatki.DataSet.RecordCount,1,6], varVariant); dm.ADOQuery_ostatki.First; for i:= 1 to dm.ostatki.DataSet.RecordCount do begin //Заполняем вариантный массив данными из запроса остатки ArrayData[i,1] :=i; ArrayData[i,2] :=dm.ADOQuery_ostatki.FieldByName('product_name').Value; ArrayData[i,3] :=dm.ADOQuery_ostatki.FieldByName('ed_name').Value; ArrayData[i,4] :=dm.ADOQuery_ostatki.FieldByName('ostatok').Value; ArrayData[i,5] :=dm.ADOQuery_ostatki.FieldByName('summa').Value; ArrayData[i,6] :=dm.ADOQuery_ostatki.FieldByName('sred_price').Value; dm.ADOQuery_ostatki.Next; end; end; //выделяем и рисуем границы данныx sheet.Range['a4','f'+IntToStr(dm.ostatki.DataSet.RecordCount+3)].Select; exl.Selection.Borders[xlEdgeTop].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeTop].Weight := xlMedium; exl.Selection.Borders[xlEdgeBottom].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeBottom].Weight := xlMedium; exl.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeRight].Weight := xlMedium; exl.Selection.Borders[xlEdgeLeft].LineStyle := xlContinuous; exl.Selection.Borders[xlEdgeLeft].Weight := xlMedium; exl.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideVertical].Weight := xlThin; exl.Selection.Borders[xlInsideHorizontal].LineStyle := xlContinuous; exl.Selection.Borders[xlInsideHorizontal].Weight := xlThin; //заполняем ячейки таблицы Excel из массива sheet.Range['a4','f'+IntToStr(dm.ostatki.DataSet.RecordCount+3)].value:= ArrayData; //показываем excel exl.visible:=true; //освобождаем память и интерфейс excel ArrayData := Unassigned; Sheet := Unassigned; WorkBook := Unassigned; exl := Unassigned; Except //в случае ошибки освобождаем ресурсы showmessage('Внимание! Произошла ошибка при создании отчета'); exl.DisplayAlerts := False; // отключаем предупреждения exl.Workbooks.Close; // закроем все книги exl.Application.quit; ArrayData := Unassigned; Sheet := Unassigned; WorkBook := Unassigned; exl := Unassigned; end; end; end.
На форме приход дважды нажимаем на кнопку печат ь и пишем следующий код для событияOnClick:
procedure TForm_prihod.Button4Click(Sender: TObject); var date_start:TDate; date_end:TDate; check:boolean; begin //вызов процедуры печати if CheckBox1.Checked=True then check:=true else check:=false ; date_start:=(datetimepicker1.Date); date_end:=(datetimepicker2.Date); print_prihod(date_start,date_end,check); end;
Не забываем прописать модуль print в uses для формы прихода. uses general, unit_dm, prihod_prod, print;
Аналогично делаем и для формы расхода. Для печати остатков на складе, размещаем на форме кнопку называем ее Печать и по событию OnClick вызываем процедуру печати print_ostatki; Печать справочников вы можете организовать самостоятельно по аналогии с печатью остатков на складе. Примеры запросов к базе, созданные в конструкторе MS Access, для выбора информации для отчета по приходу я оставил в базе для образца их можно удалить. Запросы называются: ADOQuery_print1 - выбор продуктов и единиц измерения; ADOQuery_print2 – запрос на выбор дат прихода ADOQuery_print3 – запрос на выбор продукта, единицы измерения, даты прихода и суммы.
Вопросы для контроля
1. Напишите код для /печати прихода.
2. Напишите запрос на выбор продуктов.
3. Напишите код заполнения продуктов.
4. Напишите код заполнения массива датами.
5. Напишите запрос на выбор продукта, единицы измерения, даты прихода и суммы
Литература
1. Фаронов В.В. Программирование на языке высокого уровня: Учебник для вузов. -СПб.: Питер, 2003.