Как обезличить конфиденциальную информацию в документах Excel?

Давайте рассматривается один из способов защиты данных при помощи Python и COM-соединения с файлом Excel.

При работе с клиентскими данными мы часто сталкиваемся с проблемой хранения конфиденциальной информации. На информационных ресурсах общего доступа — это запрещено. Чаще всего объем выгрузок состоит из нескольких сотен тысяч строк, содержащих личные данные клиентов. Чтобы сотрудникам не обезличивать объемное количество конфиденциальных данных вручную, мы нашли способ автоматизировать этот процесс.

Решить эту задачу нам поможет Python. Одним из вариантов получения доступа к данным документа, обычно это excel, является использование COM-соединения. Читатель наверняка спросит: «Почему не использовать замечательный модуль pandas?» Ответ на этот вопрос мы дадим позже. Для выбранного нами метода решения задачи, будем использовать модуль win32com. Открытие документа для чтения/записи происходит следующим образом:

import win32com.client as wclient path = r"C:\Users\...\data.xlsx" # путь к документу excel excel = wclient.Dispatch("Excel.Application") workbook = excel.Workbooks.Open(path)

Теперь можно использовать методы VBA (Visual Basic for Applications) из кода Python, обращаясь к объекту sheet. Перебрать все ячейки столбца, содержащего личные данные, позволяет метод Range():

r = sheet.UsedRange.Rows.Count # получаем количество строк for i, cell in enumerate(sheet.Range("B2:B{}".format(r))): # B – столбец в excel message = cell[0].value # получаем содержимое ячейки

В случае, если определённый столбец содержит только, например, номера банковских карт (БК) клиента, задача сильно упрощается. На практике встречаются ситуации, когда номер БК находится в тексте, и так для каждой ячейки столбца. Пример текста: «Клиент Иванов Иван Иванович осуществил денежный перевод на карту 1234 XXXX XXXX XXXX…» (в примере «X» соответствует отдельной цифре). Помимо номера БК в тексте может содержаться номер мобильного телефона и паспортные данные клиента. В таком случае для поиска личных данных придётся использовать регулярные выражения.

import re # импортируем модуль re для работы с регулярными выражениями query_storage = {'card': r'\b\d{4}\s?\d{4}\s?\d{4}\s?\d{4}\b'}

Согласно регулярному выражению, сохранённому в словаре query_storage, будет производиться поиск набора из 16 цифр, которые могут быть записаны без пробелов, либо разделены пробелами на группы из четырёх цифр. Разумеется, в тексте номер карты может встречаться не один раз, либо возможно появление номеров других карт, следовательно, необходимо получить позицию всех номеров БК:

for key in query_storage: reg_obj = re.compile(query_storage[key]) result = [[m.start(), m.end()] for m in reg_obj.finditer(text)]

В переменной result будет сохранён список позиций начала и конца всех найденных номеров БК, либо также других данных, если для них в словаре query_storage были записаны регулярные выражения. Зная «координаты» нужных данных, мы сможем заменить их в тексте, например, символами «*».

Пример простейшего кода для обезличивания персональных данных клиентов, содержащихся в одной колонке таблицы excel, с сохранением исходного формата записи, представлен далее.

import win32com.client as wclient import re query_storage = {'card': r'\b\d{4}\s?\d{4}\s?\d{4}\s?\d{4}\b'} path = r"C:\Users\...\data.xlsx" def encrypt(text): changed = False for key in query_storage: reg_obj = re.compile(query_storage[key]) result = [[m.start(), m.end()] for m in reg_obj.finditer(text)] if len(result) != 0: changed = True for coord in result: fragment = text[coord[0]:coord[1]] if '*' in fragment: continue upd_fragment = str() for c in fragment: if c.isdigit(): upd_fragment += '*' else: upd_fragment += c text = text.replace(fragment, upd_fragment) return text, changed def main(): excel = wclient.Dispatch("Excel.Application") workbook = excel.Workbooks.Open(path) sheet = workbook.ActiveSheet for i, cell in enumerate(sheet.Range("B2:B{}".format(r))): message = cell[0].value message_encrypted, flag = encrypt(message) if flag: sheet.Range("B{}".format(i + 2)).value = message_encrypted workbook.Save() # после завершения работы с документом сохраняем его workbook.Close() # и закрываем excel.Quit() if __name__ == "__main__": main()

В строке, отвечающей за обновление текста ячейки (в теле условия if flag:) к переменной i, изначально равной 0, прибавляется 2: первой строке соответствует индекс 1, в обрабатываемой таблице столбцы имели заголовки, поэтому данные начинаются со строки с индексом 2.

Таким образом, после выполнения кода, приведённый выше фрагмент текста будет выглядеть следующим образом: «Клиент Иванов Иван Иванович осуществил денежный перевод на карту **** **** **** ****…». Добавив в словарь query_storage регулярные выражения для номера мобильного телефона и/или паспортных данных, мы сможем скрыть и их. Это поможет избежать утечки персональных данных клиентов.

Теперь вернёмся к pandas. Конечно, использовать этот модуль можно, но при обработке крупных файлов может возникнуть проблема нехватки оперативной памяти, потому что pandas загружает данные в ОЗУ, помимо этого загрузка объёмного файла, как и его последующее сохранение занимают длительное время, хотя обработка данных происходит очень быстро. Мы провели некоторые тесты с файлом размером около 40 Мб, содержащим 28500 строк, результаты представим в таблице.

Как обезличить конфиденциальную информацию в документах Excel?

Как видно, при использовании COM-объекта, время обработки значительно выше, чем у pandas, но при сохранении документа ситуация обратная, и из-за этого общее время работы pandas больше. Помимо этого, на практике мы столкнулись с ситуацией, когда при использовании движка’ xlsxwriter’ с pandas, при наличии web-ссылок, в тексте во время сохранения происходило преобразование всего текста в одну ссылку, и строки, содержащие эти ссылки, отбрасывались и не попадали в сохранённый файл из-за недопустимо большой длины ссылок. И ещё один момент – использование COM-объекта позволяет делать изменения в исходном файле, не создавая копии. В нашем случае перечисленные факторы и сыграли в пользу выбора COM-соединения для практического применения.

#selectel_инструкция