Параметр числа, который сохраняется при копировании числа из электронной таблицы в Word

Обновлено: 04.07.2024

Вы можете определить переменные, называемые подстановочными переменными, для многократного использования в одном скрипте с помощью команды SQL*Plus DEFINE. Обратите внимание, что вы также можете определить переменные подстановки для использования в заголовках и для сохранения нажатий клавиш (определив длинную строку как значение для переменной с коротким именем).

Чтобы получить список всех определений подстановочных переменных, введите отдельно DEFINE. Обратите внимание, что любая подстановочная переменная, которую вы явно определяете с помощью DEFINE, принимает только значения CHAR (то есть значение, которое вы присваиваете переменной, всегда обрабатывается как тип данных CHAR). Вы можете определить переменную подстановки типа данных NUMBER неявно с помощью команды ACCEPT. Вы узнаете больше о команде ACCEPT.

Чтобы удалить подстановочную переменную, используйте команду SQL*Plus UNDEFINE, за которой следует имя переменной.

Пример 6-1. Определение подстановочной переменной

Чтобы определить переменную подстановки L_NAME и присвоить ей значение "SMITH", введите следующую команду:

Чтобы подтвердить определение переменной, введите DEFINE, а затем имя переменной:

6.2 Об использовании предопределенных переменных

Существует девять переменных, содержащих информацию SQL*Plus, которые определяются во время установки SQL*Plus. Эти переменные можно переопределять, ссылаться на них или удалять так же, как и любые другие переменные. Они всегда доступны от сеанса к сеансу, если вы явно не удалите или не переопределите их.

Предопределенные переменные для списка предопределенных переменных и примеров их использования.

6.3 Ссылки на подстановочные переменные

Предположим, вы хотите написать запрос, подобный тому, что используется в SALES, чтобы перечислить сотрудников с различными должностями, а не только тех, чья работа — SA_MAN. Вы можете сделать это, редактируя другое значение в предложении WHERE каждый раз при запуске команды, но есть более простой способ.

Используя подстановочную переменную вместо текста SA_MAN в предложении WHERE, вы можете получить такие же результаты, как если бы вы записали значения в саму команду.

Переменной подстановки предшествует один или два амперсанда (&). Когда SQL*Plus обнаруживает в команде подстановочную переменную, SQL*Plus выполняет команду, как если бы она содержала значение подставляемой переменной, а не саму переменную.

Например, если переменная SORTCOL имеет значение JOB_ID, а переменная MYTABLE имеет значение EMP_DETAILS_VIEW, SQL*Plus выполняет команды

как если бы они были

6.3.1 Где и как использовать подстановочные переменные

Вы можете использовать переменные подстановки в любом месте команд SQL и SQL*Plus, за исключением первого введенного слова. Когда SQL*Plus обнаруживает в команде неопределенную подстановочную переменную, SQL*Plus запрашивает у вас значение.

В приглашении можно ввести любую строку, даже содержащую пробелы и знаки препинания. Если команда SQL, содержащая ссылку, должна заключать переменную в кавычки, а вы их туда не включаете, пользователь должен включить кавычки при появлении запроса.

SQL*Plus считывает ваш ответ с клавиатуры или стандартного ввода.

После того, как вы введете значение в приглашении, SQL*Plus выведет строку, содержащую переменную подстановки, дважды: один раз перед подстановкой введенного значения и один раз после подстановки. Вы можете скрыть этот список, установив для переменной команды SET VERIFY значение OFF.

Теперь запустите скрипт STATS:

И отвечайте на запросы о значениях, как показано ниже:

SQL*Plus отображает следующий вывод:

Более практичное использование подстановочных переменных – это запрос значения перед обращением к переменной:

Если эти две команды хранятся в сценарии SQL*Plus, при каждом запуске сценария можно вводить разные фамилии.

Если вы хотите добавить символы сразу после подставляемой переменной, используйте точку, чтобы отделить переменную от символа. Например:

интерпретируется как

Если вы хотите добавить точку сразу после имени подстановочной переменной, используйте две точки вместе. Например, если "myfile" определен как "отчеты", то команда:

Текст в комментариях ANSI "/* */" или "--", который выглядит как переменная подстановки, может рассматриваться как одна переменная. Например:

Здесь текст "& loc" в комментарии интерпретируется как ссылка на переменную. SQL*Plus предложит вам ввести значение переменной "loc".

Пример 6-2. Использование подстановочных переменных

Создайте скрипт с именем STATS, который будет использоваться для расчета статистики подгруппы (максимальное значение) в числовом столбце:

6.3.2 Разница между префиксами "&" и "&&"

И одинарный амперсанд (&), и двойной амперсанд (&&) могут префикс имени переменной подстановки в операторе. SQL*Plus предварительно обрабатывает оператор и подставляет значение переменной. Затем оператор выполняется.Если переменная не была определена ранее, SQL*Plus запрашивает значение перед выполнением подстановки.

Если одиночный префикс амперсанда используется с неопределенной переменной, значение, которое вы вводите в приглашении, не сохраняется. Сразу после подстановки значения в оператор переменная отбрасывается и остается неопределенной. Если на переменную ссылаются дважды, даже в одном и том же операторе, вам будет предложено дважды. В каждом запросе можно ввести разные значения:

Если ссылка с двойным амперсандом заставляет SQL*Plus запрашивать у вас значение, тогда SQL*Plus определяет переменную как это значение (то есть значение сохраняется до тех пор, пока вы не выйдете). Любая последующая ссылка на переменную (даже в той же команде) с использованием «&» или «&&» заменяет вновь определенное значение. SQL*Plus больше не будет запрашивать:

6.3.3 Сохранение значения столбца запроса в переменной подстановки

Данные, хранящиеся в базе данных, могут быть помещены в подстановочные переменные:

Параметр NEW_VALUE в команде COLUMN неявно создает переменную подстановки с именем mynv . Переменная не создается физически, пока запрос не ссылается на столбец LAST_NAME. Когда запрос завершается, переменная mynv содержит последнее полученное значение из столбца LAST_NAME :

6.3.4 Ограничения

Вы не можете использовать переменные подстановки в командах редактирования буфера APPEND, CHANGE, DEL и INPUT, а также в других командах, где подстановка не имеет смысла. Команды редактирования буфера APPEND, CHANGE и INPUT обрабатывают текст, начинающийся с "&" или "&&", буквально, как и любую другую текстовую строку.

6.3.5 Как переменные подстановки обрабатываются в SQL*Plus

Ссылки на подстановочные переменные предварительно обрабатываются и подставляются перед анализом и выполнением команды. Для каждого оператора SQL*Plus сделает следующее:

Шаг 1 выполняется внутри клиентского инструмента SQL*Plus. Затем SQL*Plus отправляет окончательный оператор в механизм базы данных, где выполняется шаг 2.

В цикле PL/SQL невозможно повторно запрашивать запрос. В этом примере запрашивается один раз, и введенное значение подставляется в текст скрипта. Полученный сценарий затем отправляется в механизм базы данных для выполнения. Одно и то же введенное значение сохраняется в таблице пять раз:

Переменные подстановки не расширяются рекурсивно. Если значение переменной, на которую указывает ссылка, содержит амперсанд, то амперсанд используется буквально и не рассматривается как префикс второй переменной:

Вы не можете использовать подстановочную переменную в качестве первого маркера команды. Имя каждой команды должно быть жестко закодировано, иначе будет отображаться ошибка. Например:

6.3.6 Команды замены переменных

Переменные подстановки можно использовать для замены опций и значений почти во всех командах SQL*Plus. Некоторые команды имеют особое значение для подстановочных переменных.

Читает строку ввода и сохраняет ее в заданной переменной подстановки.

Указывает атрибуты отображения для данного столбца.

Определяет пользовательскую или предопределенную переменную и присваивает ей значение CHAR или перечисляет значение и тип переменной одной переменной или всех переменных.

Вызывает текстовый редактор операционной системы для содержимого указанного файла или содержимого буфера.

Принимает или откатывает все ожидающие изменения, выходит из базы данных Oracle, завершает работу SQL*Plus и возвращает управление операционной системе.

Выполняет команду операционной системы, не выходя из SQL*Plus.

TTITLE размещает и форматирует указанный заголовок вверху каждой страницы отчета.

BTITLE размещает и форматирует указанный заголовок внизу каждой страницы отчета.

REPHEADER размещает и форматирует указанный заголовок отчета вверху каждого отчета.

REPFOOTER размещает и форматирует указанный нижний колонтитул отчета внизу каждого отчета.

Удаляет одну или несколько подстановочных переменных, определенных вами явно (с помощью команды DEFINE) или неявно (с помощью аргумента команды START).

КОГДА OSERROR выполняет указанное действие (закрывает SQL*Plus по умолчанию), если возникает ошибка операционной системы (например, ошибка записи файла).

КОГДА SQLERROR выполняет указанное действие (закрывает SQL*Plus по умолчанию), если команда SQL или блок PL/SQL генерируют ошибку.

Дополнительную информацию об этих командах подстановки переменных см. в разделе Сводка команд SQL*Plus.

6.3.6.1 Использование префиксов «&» с переменными заголовков

Команды заголовков (TTITLE, BTITLE, REPHEADER и REPFOOTER) заменяют переменные иначе, чем большинство других команд. (Исключением являются команды EXIT и SET SQLPROMPT, которые аналогичны командам заголовка).

Если вы хотите, чтобы на каждой странице печаталось одно и то же значение переменной, используйте префикс "&" и поместите переменную в строку в кавычках:

Если вы хотите, чтобы в каждом заголовке были данные запроса, уникальные для каждой страницы отчета, не используйте префикс "&" для переменной и не заключайте переменную в кавычки.

Переменные подстановки SQL*Plus раскрываются перед выполнением каждой команды. После того, как это произойдет в команде заголовка, результирующая строка сохраняется как текст заголовка. Что делает переменные в заголовках особенными, так это то, что их нужно повторно заменять для каждой страницы результатов запроса. Это означает, что текущие значения переменных замены COLUMN NEW_VALUE и OLD_VALUE отображаются на каждой странице, настраивая каждый заголовок для результатов, отображаемых на его странице. Если «&» используется непреднамеренно или неправильно для префикса переменных заголовка, можно получить двойную замену. Это зависит от значения переменной и легко упускается из виду при написании сценариев.

Любое не заключенное в кавычки и не ключевое слово в заголовке проверяется при печати страницы на предмет того, является ли оно переменной. Если это так, печатается его значение. Если нет, то слово печатается дословно. Это означает, что если вы используете «&myvar» в команде заголовка, а заменяемый текст сам может быть интерпретирован как имя другой переменной, то вы получаете двойную подстановку переменных. Например, скрипт:

приводит к сохранению текста "left scottsvar" в качестве заголовка. Когда заголовок печатается на каждой странице запроса, эта строка переоценивается. Само слово «scottsvar» в заголовке рассматривается как ссылка на переменную и заменяется. Результат запроса:

Использование "&" в заголовках чаще всего вызывает проблемы с числовыми именами переменных параметров скрипта SQL*Plus. Если значение произвольной переменной заголовка с префиксом "&" совпадает с именем переменной параметра скрипта, то произойдет двойная замена.

Чтобы отобразить "&" в заголовке, добавьте перед ним символ SET ESCAPE. Амперсанд (&) сохраняется в качестве текста заголовка и не заменяется при печати заголовков страниц.

6.3.6.2 Переменные и текстовые интервалы в заголовках

Пробелы без кавычек в заголовках удалены. Используйте пробел вместо символа SET CONCAT, чтобы отделить переменные от текста, который должен располагаться непосредственно рядом. Используйте пробел внутри кавычек для отображения пробела. Например, скрипт:

дает название:

6.3.7 Пространство имен переменных подстановки, типы, форматы и ограничения

Пространство имен подстановочной переменной

В сеансе SQL*Plus существует только одно глобальное пространство имен для подстановочных переменных. Если вы повторно подключаетесь с помощью CONNECT или запускаете индексы с помощью «@», все когда-либо определенные переменные становятся доступными для использования и могут быть переопределены или не определены.

Когда дочерний скрипт завершает работу, все переменные подстановки, которые он определил или изменил, становятся видимыми для вызывающего скрипта. Это особенно заметно, когда индексу, выполняемому с помощью "@" или START, присваиваются параметры скрипта. Параметры "&1" и т. д. переопределяются, и родительский скрипт видит новые значения.

Чтобы свести к минимуму проблемы и обеспечить удобочитаемость, используйте символические имена переменных для параметров команды. Все остальные ссылки должны использовать новое имя переменной вместо «&1». Например:

Вызов myscript.sql изменяет значение "&1" на "King". Сохранив исходное значение "&1" в "myuser" и используя "&myuser" вместо "&1" в SELECT, запрос выполняется правильно.

Типы подстановочных переменных

  • СИМВОЛ
  • ЧИСЛО
  • BINARY_FLOAT
  • BINARY_DOUBLE

Тип CHAR представляет собой общий текстовый формат, аналогичный типу столбца таблицы базы данных VARCHAR2. Все переменные, созданные из следующего, имеют тип CHAR:

  • с ОПРЕДЕЛЕНИЕМ
  • из подсказок для переменных "&"
  • из параметров скрипта

Это гарантирует дословную замену введенных значений без потери конверсии.

Переменные, созданные COLUMN NEW_VALUE или OLD_VALUE для столбцов в числовом формате Oracle, будут иметь тип NUMBER. Эти подстановочные переменные хранятся во внутреннем числовом представлении Oracle так же, как и в базе данных. Это позволяет изменять форматы отображения без потери внутренних значений. Аналогичным образом создаются подстановочные переменные типов BINARY_FLOAT и BINARY_DOUBLE для столбцов Oracle BINARY_FLOAT и BINARY_DOUBLE. Эти переменные хранятся в машинном представлении. Тип CHAR используется для переменных NEW_VALUE и OLD_VALUE со всеми другими типами столбцов.

Нет явного типа DATE. Ключевое слово DATE в команде ACCEPT используется исключительно для проверки правильности формата по отношению к формату даты. Переменные подстановки, созданные ACCEPT . DATE или COLUMN NEW_VALUE в столбце даты хранятся как тип CHAR. Например:

Если переменная уже существует и переопределяется, ее старый тип отбрасывается и используется новый тип.

Тип подставляемой переменной обычно прозрачен. Подстановочные переменные имеют слабую типизацию.Например, переменная COLUMN NEW_VALUE принимает определенный тип именованного столбца в каждом новом запросе. Он также может изменить тип во время запроса. Например, тип переменной подстановки, используемой в столбце NUMBER, изменяется с NUMBER на CHAR, когда извлекается значение NULL. Он снова меняется на NUMBER, когда извлекается следующее числовое значение.

Семантика сравнения типов не определена ни для одного типа, так как нет прямого сравнения переменных. Все переменные заменяются текстом перед выполнением любого оператора SQL или PL/SQL, который может выполнять сравнение.

Форматы подстановочных переменных

Когда переменная заменяется или ее значение отображается с помощью команды DEFINE, оно форматируется как текст перед окончательным выполнением команды, ссылающейся на переменную.

Переменные CHAR заменяются дословно.

Переменные NUMBER форматируются в соответствии с SET NUMWIDTH (по умолчанию) или SET NUMFORMAT (если вы явно установили его):

Формат отображения числа можно изменить даже после создания переменной. Чтобы показать это, сначала создайте переменную NUMBER. Вы не можете использовать DEFINE для этого, потому что он делает тип всех новых переменных CHAR. Вместо этого используйте команду COLUMN NEW_VALUE, которая наследует тип NUMBER из столбца NUMBER:

Изменение формата влияет на отображение числа, но не на сохраненное значение:

Ограничения на подстановку переменных

Максимально допустимое количество переменных подстановки — 2048. SQL*Plus выдает ошибку, и предпринимается попытка создать больше. Ограничение включает предопределенные переменные, однако при необходимости они могут быть не определены. Если оставить большое количество ненужно определенных переменных, это может снизить производительность SQL*Plus, поскольку поиск переменных выполняется медленнее.

Переменная подстановки символов может иметь длину до 240 байт.

Числовая подстановочная переменная содержит полный диапазон чисел Oracle.

Когда в командной строке выполняется подстановка переменных, результирующая длина строки не может быть больше, чем:

  • 3000 байт, если это строка SQL (например, SELECT или INSERT) или текст PL/SQL (например, BEGIN или CREATE PROCEDURE)
  • 2499 байт, если это строка команды SQL*Plus (например, TTITLE или COLUMN)

В противном случае отображается ошибка.

Эти ограничения могут быть ниже в старых версиях SQL*Plus.

6.3.8 Назначение переменных подстановки для связывания переменных

Вы можете назначить переменную подстановки переменной связывания:

SQL*Plus выполняет оператор присваивания PL/SQL после замены значения "mysubv". Если "mysubv" еще не определено, вам будет предложено ввести значение.

Переменная связывания может использоваться в последующих командах SQL или PL/SQL.

6.3.9 Назначение переменных связывания переменным подстановки

Иногда полезно сделать значение переменной связывания доступным для команд SQL*Plus, таких как TTITLE или SPOOL. Например, вы можете захотеть вызвать функцию PL/SQL, которая возвращает строку, и использовать значение имени буферного файла SQL*Plus. Команда SPOOL не понимает синтаксиса связываемой переменной, поэтому значение связываемой переменной необходимо сначала присвоить переменной подстановки.

Это делается с помощью команд COLUMN NEW_VALUE и SELECT. Например, объявите переменную связывания в SQL*Plus и создайте ее экземпляр в блоке PL/SQL. Его значение может быть возвращено функцией PL/SQL или, как здесь, задано прямым присвоением:

Передайте значение переменной связывания в новую переменную подстановки "nv" с помощью запроса:

Производить расчеты и составлять таблицы удобнее в Excel, если вы хотите работать с числовыми данными. По сути программа представляет собой таблицу. Слово для такой работы не совсем подходит.

Но иногда приходится переходить с таблицы Excel на Word. Также вам может понадобиться преобразовать заполненные данные. Преобразование из одного формата в другой непродуктивно и занимает слишком много времени. Должен быть другой способ.

Как переместить таблицу из Excel в Word?

Первый способ.

Результат нашей работы:

Результат нашей работы.

У этого метода есть некоторые недостатки:

  • таблица вставлена ​​как графический объект, то есть ее нельзя редактировать.
  • граница электронной таблицы может выходить за пределы листа.

Второй способ.

Мы можем редактировать данные, изменять границы, значения шрифта и формат.

У этого метода вставки есть один существенный недостаток. Таблица с большим объемом данных выходит за пределы листа.

Третий способ.

Вставленная таблица является графическим объектом. Вы не можете редактировать значения в ячейках.

Сначала необходимо удалить границы, чтобы вставить диапазон данных без границ.

В MS Excel.

Или используйте комбинацию клавиш CTRL+SHIFT+(минус).

В MS Word.

Если вы хотите манипулировать, границы должны быть вставлены с помощью метода 1 и 2.

Как преобразовать таблицу из Word в Excel?

Мы выполним обратный перевод.

У нас "аккуратный" стол. Поэтому он вставлен плавно и точно. Если данные были заполнены безобразно, с большим количеством лишних символов (непечатаемых), границы строк и столбцов могут сместиться. Есть 2 выхода из этой ситуации:

  1. Очистите данные вручную. Если есть много повторяющихся символов, вы можете использовать параметры автозамены.
  2. Сохраните таблицу как текст (в формате txt). В этом случае все лишние символы будут удалены. Затем вставьте данные из txt в Excel.

Вы можете редактировать данные, рисовать границы и т. д. Этот метод вставки удаляет лишние символы и предотвращает смещение ячеек.

Связывание Excel файлы в Word

Несколько недель назад я написал статью о связывании файлов Excel с PowerPoint. В то время как PowerPoint, вероятно, является приложением, которое я использую чаще всего (после Excel, конечно), на третьем месте будет Word. Если, как и я, вы регулярно создаете документы Word или используете стандартные шаблоны, то привязка файлов Excel непосредственно к документу Word может сэкономить много времени.

Хотя основной процесс такой же, как в PowerPoint, метод реализации немного отличается.

Связывание книги Excel с документом Word

Связать книгу Excel с документом Word очень просто. Просто скопируйте диапазон из Excel. Затем в Word нажмите Главная -> Вставить (маленькая стрелка). Существуют различные варианты, по умолчанию мы можем щелкнуть значки Вставить ссылку и сохранить форматирование или Вставить ссылку и объединить форматирование.

Excel Linked Word — параметры вставки по умолчанию

Но если мы нажмем Специальная вставка…, появится еще больше возможностей. Нажмите Вставить ссылку и выберите тип вставки. Наконец, нажмите ОК, чтобы вставить содержимое Excel в документ Word с выбранными параметрами.

Excel Linked Word - Специальная вставка

Обратите внимание на количество доступных вариантов вставки. Мне особенно нравится Неформатированный текст, так как его можно использовать для добавления переменной информации в стандартное предложение.

В Word при выборе любой связанный текст будет выделен серым цветом (если только не были изменены настройки в Word). Смотрите скриншот ниже.

Excel связан с Word — выделение серым цветом

Коды полей

Одним из ключевых отличий от PowerPoint является наличие кодов полей. Это метод, с помощью которого Word вставляет связанные документы и другие динамические параметры.

Вы можете просмотреть код за кодом поля, нажав Ctrl+F9, чтобы просмотреть все коды полей, или Shift+F9, чтобы отобразить только выбранный код поля. На снимке экрана ниже показан пример кода поля.

Excel связан с Word - коды полей

С помощью кодов полей можно добиться гораздо большего, но это выходит за рамки этой статьи.

Обновить и изменить ссылки

Преимущество связывания документов заключается в том, что после их создания вы можете быстро обновить связанные значения.

Обновить ссылку при открытии

При открытии документа Word у вас будет возможность обновить документ данными из связанных файлов.

Excel Linked Word — обновить ссылку при открытии

Обновление по требованию

Если вы хотите обновить только одну ссылку, щелкните ее правой кнопкой мыши и выберите в меню Обновить ссылку.

Excel Linked Word — ссылка правой кнопкой мыши

В подменю Связанный объект рабочего листа (как показано выше) есть дополнительные параметры для редактирования, открытия и преобразования ссылок.

Обновление с помощью опции «изменить ссылки»

Этот параметр доступен в меню Связанный объект листа, как показано выше, или на ленте Файл -> Информация -> Редактировать ссылки на файлы (он находится внизу правом углу, но только после сохранения документа Word).

Excel Linked Word — ссылки для редактирования файлов

Откроется окно "Ссылки", в котором представлены различные параметры для обновления, редактирования и разрыва ссылок.

Word, связанный с Excel — окно обновлений ссылок

Мои главные советы

  1. Используйте именованные диапазоны. Добавление строк и столбцов на лист не изменит ссылку на ссылку в Word. Например, если Word связан с ячейкой A1, то вы вставите новый столбец в начале, ячейка A1 станет ячейкой B1. Но Word по-прежнему ссылается на ячейку A1. Теперь это будет неправильная ячейка. Именованный диапазон помогает обеспечить отображение в документе правильного диапазона.
  2. Word не узнает, если вы измените имя или путь связанного файла (исключение см. в пункте 3 ниже). Необходимо убедиться, что самая последняя версия файла Excel всегда имеет то же имя файла и путь к файлу, чтобы Word мог его найти.
  3. Самый быстрый способ изменить источник файла Excel — открыть и файлы Excel, и связанные файлы Word. Затем сохраните файл Excel с новым путем или именем файла. В этом случае Word автоматически обновит ссылку на файл.

Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.

Не забывайте:

Если вы нашли этот пост полезным или у вас есть лучший подход, оставьте комментарий ниже.

Вам нужна помощь в адаптации этого к вашим потребностям?

Я предполагаю, что примеры в этом посте не совсем соответствуют вашей ситуации. Мы все используем Excel по-разному, поэтому невозможно написать пост, который удовлетворит потребности всех. Потратив время на изучение методов и принципов, изложенных в этом посте (и в других местах на этом сайте), вы сможете адаптировать его к своим потребностям.

  1. Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
  2. Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
  3. Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
  4. Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.

Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:

Горан Авиани

Горан Авиани

Как создавать, читать, обновлять и искать Файлы Excel с использованием Python

В этой статье подробно показано, как работать с файлами Excel и как изменять определенные данные с помощью Python.

Сначала мы научимся работать с CSV-файлами, читая, записывая и обновляя их. Затем мы рассмотрим, как читать файлы, фильтровать их по листам, искать строки/столбцы и обновлять ячейки файлов xlsx.

Начнем с простейшего формата электронной таблицы: CSV.

Часть 1. CSV-файл

Файл CSV представляет собой файл со значениями, разделенными запятыми, в котором данные в виде обычного текста отображаются в табличном формате. Их можно использовать с любой программой для работы с электронными таблицами, например Microsoft Office Excel, Google Spreadsheets или LibreOffice Calc.

Файлы CSV не похожи на другие файлы электронных таблиц, поскольку они не позволяют сохранять ячейки, столбцы, строки или формулы. Их ограничение состоит в том, что они также позволяют использовать только один лист в файле. Мой план в этой первой части статьи состоит в том, чтобы показать вам, как создавать файлы CSV с помощью Python 3 и модуля стандартной библиотеки CSV.

Это руководство завершится двумя репозиториями GitHub и работающим веб-приложением, которое на самом деле использует код второй части этого руководства (но обновленный и модифицированный для определенной цели).

Запись в файлы CSV

Сначала откройте новый файл Python и импортируйте модуль Python CSV.

Модуль CSV

Модуль CSV включает в себя все необходимые встроенные методы. К ним относятся:

  • CSV.читатель
  • csv.write
  • csv.DictReader
  • csv.DictWriter
  • и другие

В этом руководстве мы сосредоточимся на методах записи, DictWriter и DictReader. Они позволяют редактировать, изменять и манипулировать данными, хранящимися в CSV-файле.

На первом этапе нам нужно определить имя файла и сохранить его как переменную. Мы должны сделать то же самое с заголовком и информацией о данных.

Теперь нам нужно создать функцию с именем writer, которая будет принимать три параметра: заголовок, данные и имя файла.

Следующим шагом является изменение функции writer, чтобы она создавала файл, содержащий данные из переменных header и data. Это делается путем записи первой строки из переменной header, а затем записи четырех строк из переменной data (четыре строки, потому что в списке четыре кортежа).

В официальной документации Python описывается, как работает метод csv.writer. Я настоятельно рекомендую вам потратить минуту на его прочтение.

И вуаля! Вы создали свой первый CSV-файл с именем imdb_top_4.csv. Откройте этот файл в предпочитаемом вами приложении для работы с электронными таблицами, и вы должны увидеть что-то вроде этого:

1*DuYsqu8EFzU15u_0HgNDKg

Использование LibreOffice Calc для просмотра результата.

Результат может быть записан следующим образом, если вы решите открыть файл в другом приложении:

1*Q0U_MBj6mr3ekidC299lbQ

Использование SublimeText для просмотра результата.

Обновление CSV-файлов

Чтобы обновить этот файл, вы должны создать новую функцию с именем updater, которая будет принимать только один параметр с именем filename.

Эта функция сначала открывает файл, определенный в переменной filename, а затем сохраняет все данные, которые она считывает из файла, внутри переменной с именем readData. Второй шаг — жестко закодировать новое значение и поместить его вместо старого в позицию readData[0][‘Rating’].

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

Чтобы writer работал с новым параметром, вам нужно добавить новый параметр везде, где определен writer. Вернитесь к тому месту, где вы впервые вызвали функцию writer, и добавьте «write» в качестве нового параметра:

Сразу под функцией записи вызовите updater и передайте в него параметр filename:

Теперь вам нужно изменить функцию writer, чтобы она принимала новый параметр с именем option:

С этого момента мы ожидаем получить две разные опции для функции writer (запись и обновление). Из-за этого мы должны добавить два оператора if для поддержки этой новой функциональности. Первая часть функции под «if option == «write:» вам уже известна. Вам просто нужно добавить раздел «elif option == «update»: кода и часть else так, как они написаны ниже:

Браво! Готово!

Теперь ваш код должен выглядеть примерно так:

В первой части этой статьи мы увидели, как работать с CSV-файлами. Мы создали и обновили один такой файл.

Часть 2. Файл xlsx

Несколько выходных я работал над этим проектом. Я начал работать над этим, потому что в моей компании была потребность в таком решении. Моей первой идеей было встроить это решение прямо в систему моей компании, но тогда мне было бы не о чем писать, а?

Я создаю это решение, используя Python 3 и библиотеку openpyxl. Причина, по которой я выбрал openpyxl, заключается в том, что он представляет собой комплексное решение для создания рабочих листов, их загрузки, обновления, переименования и удаления. Это также позволяет нам читать или записывать строки и столбцы, объединять или разъединять ячейки или создавать диаграммы Python Excel и т. д.

Терминология Openpyxl и основная информация

  • Рабочая книга — это имя файла Excel в Openpyxl.
  • Рабочая книга состоит из листов (по умолчанию 1 лист). На листы ссылаются по их именам.
  • Лист состоит из строк (горизонтальных линий), начинающихся с цифры 1, и столбцов (вертикальных линий), начинающихся с буквы А.
  • Строки и столбцы образуют сетку и формируют ячейки, которые могут содержать некоторые данные (числовые или строковые значения) или формулы.

Первый шаг — открыть среду Python и установить openpyxl в свой терминал:

Затем импортируйте openpyxl в свой проект, а затем загрузите книгу в переменную theFile.

Как видите, этот код печатает все листы по их именам. Затем он выбирает лист с именем «клиенты 1» и сохраняет его в переменной currentSheet. В последней строке код печатает значение, расположенное в позиции B4 листа «клиенты 1».

Этот код работает как надо, но он очень жестко запрограммирован. Чтобы сделать это более динамичным, мы напишем код, который будет:

  • Читать файл
  • Получить все имена листов
  • Пройтись по всем листам
  • На последнем шаге код напечатает значения, расположенные в полях B4 каждого найденного листа внутри книги.

Это лучше, чем раньше, но это все еще жестко закодированное решение, и оно по-прежнему предполагает, что значение, которое вы будете искать, находится в ячейке B4, что просто глупо :)

Я предполагаю, что вашему проекту потребуется выполнить поиск определенного значения во всех листах файла Excel. Для этого мы добавим еще один цикл for в диапазон «ABCDEF», а затем просто выведем имена ячеек и их значения.

Мы сделали это, введя цикл «for row in range..». Диапазон цикла for определяется от ячейки в строке 1 до максимального количества или строк листа. Второй цикл for выполняет поиск по предопределенным именам столбцов «ABCDEF». Во втором цикле мы будем отображать полную позицию ячейки (имя столбца и номер строки) и значение.

Однако в этой статье моя задача — найти конкретный столбец с названием «телефон», а затем пройтись по всем строкам этого столбца. Для этого нам нужно изменить код, как показано ниже.

Этот модифицированный код проходит через все ячейки каждого листа, как и раньше, диапазон строк является динамическим, а диапазон столбцов - конкретным. Код перебирает ячейки и ищет ячейку, содержащую текст «телефон». Как только код находит конкретную ячейку, он уведомляет пользователя, в какой ячейке находится текст. Код делает это для каждой ячейки внутри всех листов в файле Excel.

Следующий шаг — просмотреть все строки этого конкретного столбца и вывести значения.

Это делается путем добавления функции с именем get_column_letter, которая находит букву столбца. После того, как буква столбца найдена, мы перебираем все строки этого конкретного столбца. Это делается с помощью функции get_all_values_by_cell_letter, которая выводит все значения этих ячеек.

Подведение итогов

Бюстгальтер! Есть много вещей, которые вы можете сделать после этого. Мой план состоял в том, чтобы создать онлайн-приложение, которое будет стандартизировать все шведские телефонные номера, взятые из текстового поля, и предлагать пользователям возможность просто скопировать результаты из того же текстового поля. Вторым шагом моего плана было расширение функциональности веб-приложения для поддержки загрузки файлов Excel, обработки телефонных номеров внутри этих файлов (стандартизация их до шведского формата) и предоставления обработанных файлов обратно пользователям.

Также код из второй части этой статьи доступен на GitHub:

Читайте также: