Формулы: SetCellFormula и расчёт при открытии

Во всех прошлых уроках итоги считал Go: цикл сложил колонку, SetCellValue записал готовое число в итоговую строку, и файл открылся с уже посчитанной суммой. Это работает, пока данные не меняют руки: пользователь правит пару значений прямо в Excel, отправляет файл обратно — и итог вчерашнего дня висит над свежими данными, потому что это константа. Формула решает это принципиально: в ячейку кладётся не число, а выражение, и Excel пересчитывает его при каждом открытии и каждой правке. Поэтому тема делится на два непересекающихся мира: SetCellValue пишет значения, SetCellFormula пишет формулы, и путать их на одной ячейке не стоит. Разберём, как формула выглядит в файле, что произойдёт, если написать в ней русское СУММ вместо SUM, и какая цена у решения «считаем в Go» против «считает Excel». И одно предупреждение вынесем вперёд: excelize ничего не вычисляет — об этом первая врезка с предостережением.

SetCellFormula: excelize формулы как текст в ячейке#

Сигнатура проста: f.SetCellFormula принимает лист, координату ячейки и строку формулы — f.SetCellFormula("Отчёт", "E2", "=B2C2") кладёт в ячейку умножение двух соседних колонок. Знак равенства в начале обязателен: без него excelize честно запишет строку как текст, и Excel покажет в ячейке =B2C2 словами, а не результатом. Ошибку метод вернёт на пустой строке или несуществующем листе, но синтаксис формулы он не проверяет: опечатка вроде SUMM(B2:B21) пройдёт мимо компилятора и библиотеки и проявится только в Excel диалогом «файл содержит ошибки». Отсюда правило отладки: собрав файл с новыми формулами, откройте его в Excel или LibreOffice и посмотрите на итоговую строку — это единственный способ убедиться, что всё посчиталось. Внутри xlsx формула — это элемент f в XML-описании ячейки, рядом с которым Excel хранит вычисленное значение в элементе v; excelize формулу пишет, а значение за неё не подделывает. Так запрос excelize формулы упирается в одну и ту же схему: строка с равенством, английские имена функций, ссылки на ячейки и диапазоны.

Итоговая строка с суммой и средним

if err := f.SetCellFormula("Отчёт", "E21", "=SUM(E2:E20)"); err != nil {
    panic(err)
}

if err := f.SetCellFormula("Отчёт", "E22", "=AVERAGE(E2:E20)"); err != nil {
    panic(err)
}

f.SetCellStyle("Отчёт", "E21", "E22", bold)

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

Go формулы не вычисляет

Excelize умеет формулы записывать, но не считать: в сохранённом файле у свежей формулы нет кэшированного значения. Число появится только после открытия файла в Excel или LibreOffice, которые пересчитают книгу при загрузке. До этого момента GetCellValue по ячейке с формулой вернёт сам текст формулы, экспорт в CSV выдаст формулу, а парсер увидит строку с равенством вместо числа. Если данные нужны машине, а не человеку — считайте в Go и пишите значения.

Чтение: GetCellFormula и цена кэша#

Обратная операция — f.GetCellFormula("Отчёт", "E21"), она вернёт строку =SUM(E2:E20) для ячейки с формулой и пустую строку для обычной. Поведение GetCellValue зависит от того, кто создал файл: у книги, сохранённой Excel, в ячейках лежит кэш вычислений, и GetCellValue отдаст числа; у файла, который только что собрал excelize, кэша нет, и чтение вернёт текст формулы. Разница объясняет половину недоразумений на форумах: в тестах генерации файл читается обратно без Excel, кэша в нём нет, и проверять нужно GetCellFormula, а не значение. После того как человек открыл файл и пересохранил его, кэш появляется — и тогда чтение чисел работает. Метод UpdateLinkedValue обновляет связные значения для старых файлов, где формулы ссылаются на внешние книги, но вычислять что-либо он не берётся. Запомните разделение: формула — свойство, которое excelize читает и пишет честно; вычисленное значение — собственность Excel, и библиотека его только переносит.

Колонка формул и проверка чтением

for i := 2; i <= 20; i++ {
    formula := fmt.Sprintf("=B%d*C%d", i, i)
    if err := f.SetCellFormula("Отчёт", fmt.Sprintf("D%d", i), formula); err != nil {
        panic(err)
    }
}

got, err := f.GetCellFormula("Отчёт", "D5")
if err != nil {
    panic(err)
}
fmt.Println(got)

Формулы по строкам собираются Sprintf: адреса подставляются в шаблон той же техникой, что в уроке про чтение ячеек. GetCellFormula после записи вернёт ровно то, что написали, — это удобно в тестах генерации, где Excel недоступен.

Функции и ссылки: setcellformula на практике#

В файле формулы живут на английском: SUM, AVERAGE, IF, VLOOKUP, TODAY. Русские СУММ и ЕСЛИ — это слой интерфейса: Excel переводит имена на язык пользователя при отображении, но хранит и принимает английские, поэтому русские имена в строке excelize дадут ошибку при открытии. Разделитель аргументов — запятая: =IF(D2>0,D2,0) записывается так, хотя в русском Excel вы наберёте её с точкой с запятой. Ссылки на другие листы ставятся через восклицательный знак, а если в имени листа есть пробел, имя берётся в апострофы: ='Данные 2024'!C5. Доллар делает ссылку абсолютной — $B$2 не съедет при протягивании формулы в Excel. Полезно знать потолок: формула длиннее 8192 символов в Excel не влезет, и на практике это значит, что огромные выражения стоит разносить по нескольким ячейкам. Запрос setcellformula на практике сводится к трём этим вещам: английские функции, запятые между аргументами, кавычки вокруг листов с пробелами.

Аптечка формул для отчётов#

Строка формулы	Что делает	Нюанс
=SUM(E2:E20)	сумма колонки	диапазон через двоеточие
=AVERAGE(E2:E20)	среднее	дробный результат
=IF(D2>0,D2,0)	условие по строке	разделитель — запятая
=VLOOKUP(A2,Данные!A:C,3,0)	поиск по другому листу	имена функций на английском
='Данные 2024'!C5	ссылка на другой лист	апострофы при пробеле в имени
=$B$2*C2	абсолютная ссылка	доллар фиксирует ячейку

Формулы или значения: цена решения#

Выбор между формулой и значением — это вопрос о том, кто откроет файл. Человеку в Excel нужны формулы: он правит данные, итоги пересчитываются, файл остаётся живым документом. Машине — парсеру, почтовому роботу, загрузке в базу — нужны значения: у свежей выгрузки excelize кэша нет, и формулу она прочтёт как строку. Дублировать и то и другое на одних ячейках нельзя, а на разных — способ рассинхронизировать отчёт: правят одно, забывают второе. Рабочий гибрид выглядит так: в колонках данных — значения, в итоговой строке — формулы; человек правит данные и получает честные итоги, а машины читают данные и не трогают итоги. Стоимость формулы тоже стоит держать в голове: их в файле столько, сколько вы написали, и Excel пересчитывает их при каждом открытии — на выгрузке в сотни тысяч ячеек с формулами в каждой строке открытие станет заметно долгим. Там, где формулы нужны только в итогах, пишите их только в итогах.

Читаем формулы из чужих книг

Файлы, собранные Excel, несут кэш значений, поэтому по ним GetCellValue вернёт числа, а GetCellFormula — выражения. Для аудита чужой книги постройте карту формул: обход GetRows с проверкой GetCellFormula на каждой ячейке покажет, где считаются данные. Это удобнее, чем открывать файл глазами: карта формул сразу выделяет итоги, дубли и формулы со ссылками на отсутствующие листы.

Чек-лист формул#

SetCellFormula принимает строку с равенством: без него формула станет текстом.
Имена функций на английском: SUM, IF, VLOOKUP; русские имена напишет интерфейс Excel.
Разделитель аргументов — запятая; листы с пробелом берутся в апострофы.
Синтаксис формулы excelize не проверяет: открывайте файл в Excel после сборки.
GetCellFormula читает формулу, GetCellValue — кэш; в свежем файле кэша нет.
Человеку — формулы, машине — значения; гибрид: формулы только в итогах.

В песочнице файлов нет

SetCellFormula и GetCellFormula работают с книгой на диске, поэтому в браузерной песочнице не запускаются: там доступны fmt, strings, strconv, math, sort, errors и time без файлового ввода-вывода. Потренируйте сборку формул как строк: соберите Sprintf выражение для номера строки и напечатайте его — логика та же, файл соберите локально.

Кейс про итог, который не пересчитывался. Команда выгружала ежедневный отчёт продаж, и итоговая строка считалась в Go: сумма по колонке записывалась числом. Порядок рухнул, когда менеджер региона начал править объёмы прямо в файле перед отправкой наверх: он менял числа, файл сохранялся, а итог оставался вчерашним — константа не знала о правках. Переход занял один день: колонка данных осталась значениями, в итоговой строке появилась формула =SUM, а сумма через GetCellValue в тестах сменилась на GetCellFormula — проверять стали наличие формулы, а не число. Второй поворот случился через месяц: загрузчик отчёта в базу начал читать файлы и choking на формуле в итогах — парсер получал строку с равенством вместо суммы. Команда договорилась о контракте: для людей — файл с формулами в итогах, для загрузчика — отдельная выгрузка, где всё записано значениями. Спорная сторона осталась: дублирование генерации в двух видах держат в одном коде с флагом, но правки колонок приходится проверять в обоих режимах.

Проверка: чтение свежей формулы

Файл собран excelize, в ячейку записана формула через SetCellFormula. Что вернёт GetCellValue по этой ячейке до открытия файла в Excel?

Проверка: имена функций

Какую строку формулы excelize запишет, чтобы Excel посчитал сумму диапазона B2:B20?

Что напечатает чтение?

Прочитайте код и выберите, что он выведет:

f.SetCellValue("Sheet1", "A1", 2)
f.SetCellValue("Sheet1", "B1", 3)

f.SetCellFormula("Sheet1", "C1", "=A1+B1")

v, _ := f.GetCellValue("Sheet1", "C1")
fmt.Println(v)

Соберите формулы итогов

Допишите запись формулы, имя функции суммы и чтение формулы обратно.

f.("Отчёт", "E2", "=B2*C2") f.SetCellValue("Отчёт", "E21", "=(E2:E20)") got, _ := f.("Отчёт", "E2") fmt.Println(got)

Соберите запись и чтение формулы

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

Строки

Ваша программа

Нажимайте на строки слева, чтобы собрать программу

Карточки: формулы

Термин

SetCellFormula

нажмите, чтобы перевернуть

Определение

Пишет строку формулы в ячейку: лист, координата, выражение с равенством. Синтаксис не проверяет — проверяет Excel при открытии.

1 / 6

Формулы поставлены: итоги считает Excel, чтение и запись формул проверены, граница между формулами и значениями ясна. Дальше отчёт станет выразительнее: в уроке про условное форматирование отрицательные остатки подсветятся красным без единого нового стиля в цикле, а в уроке про диаграммы данные с листа превратятся в график рядом с таблицей. Записывать формулы вы научились вместе с SetCellValue из четвёртого урока — там же лежит правило проверки ошибок, которое здесь осталось тем же. Готовая схема «значения в данных, формулы в итогах» встречается в экспорте отчёта из базы, а проверка формул чтением разобрана подробнее в уроке про тесты генерации. Домашнее задание — заменить свой рукописный итог формулой =SUM и прочитать её обратно.

Песочница урока

Редактируйте код и запускайте его прямо здесь — интерпретатор работает в браузере, ничего устанавливать не нужно. За каждый запуск — +2 XP.

Вывод

Запустите код, чтобы увидеть вывод.

Коротко: что запомнить

SetCellFormula пишет строку с равенством; без равенства получится текст.
Excelize формулы не вычисляет: пересчитывает Excel или LibreOffice при открытии.
В свежем файле кэша нет: GetCellValue вернёт текст формулы, GetCellFormula — формулу.
Имена функций на английском, разделитель аргументов запятая, листы с пробелом в апострофах.
Синтаксис формулы проверяет Excel: после сборки файл открывают и смотрят итоги.
Человеку — формулы, машине — значения; гибрид: формулы только в итоговой строке.

Домашнее задание

Возьмите свой отчёт из прошлых уроков и замените рукописный итог формулой =SUM по колонке данных, добавьте =AVERAGE строкой ниже и выделите итоги жирным стилем. Соберите файл, прочитайте обе формулы через GetCellFormula и напечатайте их, затем откройте файл в Excel или LibreOffice и убедитесь, что итоги посчитаны, — и что правка одного числа меняет сумму.

Вопросы и ответы по уроку

Почему GetCellValue возвращает =SUM вместо числа?

Excelize не вычисляет формулы и не пишет кэш: в свежем файле хранится только текст формулы. Число появляется после пересчёта в Excel или LibreOffice; если значение нужно в Go без Excel, посчитайте его и запишите через SetCellValue.

Excel пишет ошибку при открытии файла с формулами — в чём дело?

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

Можно ли заставить excelize вычислить формулы?

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

Термины урока

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