Всем привет, сегодня мы поговорим о том, как можно выгружать данные из Access в такие приложения, как Word и Excel. Но не стандартный метод, который есть в Access (связь с Office), а метод, позволяющий выгружать данные в определенный шаблон и в Word, и в Excel. Другими словами, это необходимо, когда невозможно или слишком трудоемко создать отчет в Access по уже существующему шаблону, например, в Word.
Как известно, отчет в Access может просто отображаться коряво, или, самый распространенный вариант, когда в отчете Access много текста, который не так хорошо отформатирован, как в Word, и нет такого много данных, но отчет должен быть автоматизирован, например, это какие-то договоры, выписки и т.д. Пользоваться самим вордовским слиянием не очень удобно, поэтому сегодня я расскажу, как можно заливать такие шаблоны прямо из Access нажатием кнопки. Помимо загрузки в шаблон Word, иногда необходимо выполнить загрузку в шаблон Excel, и этот способ мы сегодня тоже рассмотрим.
Экспорт данных из Access в шаблон Word
- Настройка шаблона Word;
- Настроить загрузку данных в шаблон.
Суть настройки шаблона заключается в размещении необходимых полей в тех местах шаблона, где вы хотите отображать те или иные данные. Это делается с помощью полей формы.
Примечание! Я использую Microsoft Word 2003.
Откройте шаблон Word, сначала добавьте необходимую панель инструментов, для этого нажмите «Вид -> Панель инструментов» и установите флажок «Формы». Теперь у вас отображается панель инструментов Shapes. Все, что осталось сделать, это вставить в места, где вы хотите отображать данные, элементы «Текстовое поле», которые доступны на только что добавленной панели инструментов.
После добавления поля у вас будет серая область, указывающая, что поле было добавлено. Теперь нужно задать имя этого поля, чтобы потом вставлять в него значения из доступа (стандартное имя не очень удобно). Для этого щелкните правой кнопкой мыши поле и выберите «Свойства». В поле маркера введите желаемое имя для этого поля, в примере я назвал его MyTestPole.
Создайте столько полей, сколько вам нужно.
На этом настройка шаблона закончена, рекомендую сделать этот шаблон только для чтения, иначе пользователь возьмет его, сформирует документ и сохранит, а шаблон потеряется, а если вы сделаете его только для чтения, вы не есть такая возможность, просто сохраните через «Сохранить как».
Перейдем к более интересной задаче, это реализация загрузки Access в этом шаблоне на VBA.
Примечание! Я использую Access в связке с MS SQL 2008, поэтому данные буду брать оттуда.
Код VBA для выгрузки данных в шаблон Word
Допустим, у вас есть форма, создайте на ней кнопку (я назвал ее тестовой кнопкой) и вставьте следующий код VBA в событие нажатия кнопки:
Экспорт данных из Access в шаблон Excel
В шаблоне Excel уже не нужно создавать поля как в Word, так как здесь мы уже будем ориентироваться на адреса ячеек.
Заполнить шаблон Excel можно несколькими способами, я опишу два, первый — когда нужно заполнить только несколько полей, то есть в источнике данных будет только одна строка с несколькими столбцами. Второй — когда линий уже несколько и вы точно не знаете сколько (зависит от некоторых условий). В шаблоне по умолчанию для этого отведена пара строк, поэтому мы добавим нужные нам строки, чтобы наши данные не пересекались со строками ниже (допустим, есть примечание, подпись заголовка и т д.). И совет, вот, например, я использую только один источник данных, а вы, если вам нужно заполнить шапку, примечание и определенное количество строк (т.е область данных), можете использовать несколько источников (Recordset).
Код VBA для выгрузки данных в шаблон Excel
Сначала добавьте кнопку на форму (я назвал ее testexcel) и вставьте следующий код в событие «Нажатие кнопки».
Поскольку новая строка будет создана без объединений или значений XLT.Worksheets(«Sheet1»).Rows(Rowss — 1).Copy newrow ‘, это всего лишь пример того, как удалить диапазон в документе ‘XLT. Листы («Лист1»). Range(«A10:F10»).ClearContents ‘динамически генерирует нужный адрес ячейки cell = «a» Rowss XLT.Worksheets(«Лист1»).Range(cell) = rsd.Fields(«field5»).Value ‘перейти к следующей строке Rowss = Rowss + 1 ‘перейти к следующей строке в источнике данных rsd .MoveNext Else ‘и это делается до тех пор, пока не закончатся строки, данные в шаблоне’, то есть если в исходнике всего 1 строка, то в коде
Здесь я тоже все подробно обсудил, но если у вас есть вопросы, задавайте их в комментариях к этой статье.
Для справки, я использовал позднюю ссылку здесь при создании объекта для Word.Application и Excel.Application, чтобы не добавлять необходимые библиотеки и обеспечить совместимость.
Надеюсь, мои примеры вам помогут!
Создание отчетов в MS Word с данными из БД
Отчёт – это особая форма представления данных. Он позволяет пользователю познакомиться с отобранными, агрегированными и особо оформленными данными. На сегодняшний день формирование и представление отчетов все еще является актуальной формой доведения до пользователя информации.
Для начала ознакомимся с используемыми данными. В БД имеются 3 таблицы с данными о товарах, покупателях и продажах.
Для начала нам понадобится импортировать в код необходимые модули:
re>from docx import Document import pandas as pd import pandas.io.sql as psql import matplotlib.pyplot as plt from io import BytesIO import pyodbc
Создаем соединение и формируем запрос. На данном этапе пользователю следует определиться с составом запрашиваемых запросом данных исходя из имеющейся задачи, т.е. на каком этапе производятся расчеты, агрегирование и/или фильтрация данных. В нашем случае будут запрашиваться данные всех продаж с привязкой к ФИО покупателя и данным о товаре. Обработка данных будет производится с помощью модуля pandas.
re>cnxn = pyodbc.connect("Driver=;" "Server=S1;" "Database=test;" "uid=sa;pwd=pass;" "Trusted_Connection=yes;") cursor = cnxn.cursor() sql = '''select c.name ,p.* from [dbo].[sales] s join [dbo].[customers] c on c.id = s.customer join [dbo].[product] p on s.product = p.id'''
Полученные данные отправляем в dataframe, закрываем соединение
re>df = psql.read_sql_query(sql,cnxn) cnxn.close() del df['id'] # ненужный столбец df
Следующий этап – непосредственно создание документа.
re>document = Document() # создается объект # добавляем первый заголовок document.add_heading('Отчет о продажах', 0) # добавляем простой текст с переменными из # данных таблицы (названия магазинов) shop_list = ', '.join(df['shop'].unique().tolist()) p = document.add_paragraph('Отчет о продажах в магазинах: ') # к тексту добавим сам список, выделяем жирным шрифтом p.add_run(shop_list).bold = True
Формируем таблицу о всех продажах – аналог входных данных запроса.
re>document.add_heading('Общие продажи', level=1) # заголовок rows, columns = df.shape # размеры dataframe table = document.add_table(rows=1, cols=columns) # создаем таблицу table.style = "Colorful List Accent 1" # определяем стиль # формируем заголовки таблицы hdr_cells = table.rows[0].cells for i in range(columns): hdr_cells[i].text = list(df.columns.values)[i] # заполняем данными из dataframe for row in range(rows): row_cells = table.add_row().cells row_data = df.iloc[row].tolist() for column in range(columns): row_cells[column].text = str(row_data[column])
На выходе получается следующий документ.
Какой же отчет бывает без графиков. Для примера, создадим график затрат в разрезе клиентов.
