Руководство

Настройка

Во-первых, убедитесь в том, что установлен пакет XLSX.jl.

julia> using Pkg

julia> Pkg.add("XLSX")

Начало работы

Основным сценарием является считывание файла Excel и считывание значений.

julia> import XLSX

julia> xf = XLSX.readxlsx("myfile.xlsx")
XLSXFile("myfile.xlsx") containing 3 Worksheets
            sheetname size          range
-------------------------------------------------
              mysheet 4x2           A1:B4
           othersheet 1x1           A1:A1
                named 1x1           B4:B4

julia> XLSX.sheetnames(xf)
3-element Array{String,1}:
 "mysheet"
 "othersheet"
 "named"

julia> sh = xf["mysheet"] # получает ссылку на лист
4×2 XLSX.Worksheet: ["mysheet"](A1:B4)

julia> sh[2, 2] # обращается к элементу B2 (2-я строка, 2-й столбец)
"first"

julia> sh["B2"] # также можно использовать имя ячейки
"first"

julia> sh["A2:B4"] # или диапазон ячеек
3×2 Array{Any,2}:
 1  "first"
 2  "second"
 3  "third"

julia> XLSX.readdata("myfile.xlsx", "mysheet", "A2:B4") # краткая форма записи для всего кода выше
3×2 Array{Any,2}:
 1  "first"
 2  "second"
 3  "third"

julia> sh[:] # все данные внутри измерения листа
4×2 Array{Any,2}:
  "HeaderA"  "HeaderB"
 1           "first"
 2           "second"
 3           "third"

julia> xf["mysheet!A2:B4"] # вы также можете запрашивать значения с помощью ссылки на лист
3×2 Array{Any,2}:
 1  "first"
 2  "second"
 3  "third"

julia> xf["NAMED_CELL"] # можно считывать даже именованные диапазоны
"B4 is a named cell from sheet \"named\""

julia> xf["mysheet!A:B"] # Поддерживаются также диапазоны столбцов
4×2 Array{Any,2}:
  "HeaderA"  "HeaderB"
 1           "first"
 2           "second"
 3           "third"

Для проверки внутреннего представления каждой ячейки используйте метод getcell или getcellrange.

В приведенном выше примере для открытия файла использовалось выражение xf = XLSX.readxlsx(filename), поэтому все содержимое файла получается с диска одновременно.

Для считывания содержимого файла по мере необходимости можно использовать функцию XLSX.openxlsx (см. раздел Чтение больших файлов Excel и кэширование).

Типы данных

Для работы с файлами XLSX этот пакет использует следующие конкретные типы.

# XLSX.CellValueType — Type

CellValueType

Конкретные поддерживаемые типы данных.

Union{String, Missing, Float64, Int, Bool, Dates.Date, Dates.Time, Dates.DateTime}

Их абстрактные типы преобразовываются в соответствующие конкретные типы во время записи.
Значения Nothing во время записи преобразовываются в Missing.

Чтение табличных данных

Метод XLSX.gettable возвращает табличные данные из электронной таблицы в виде структуры XLSX.DataTable, которая реализует интерфейс Tables.jl. Ее можно использовать для создания объекта DataFrame из DataFrames.jl. Описание дополнительных параметров см. в docstring метода gettable.

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

julia> using DataFrames, XLSX

julia> df = DataFrame(XLSX.readtable("myfile.xlsx", "mysheet"))
3×2 DataFrames.DataFrame
│ Row │ HeaderA │ HeaderB  │
├─────┼─────────┼──────────┤
│ 1   │ 1       │ "first"  │
│ 2   │ 2       │ "second" │
│ 3   │ 3       │ "third"  │

Чтение ячеек в виде матрицы Julia

Для считывания содержимого в виде матрицы Julia используйте функцию XLSX.readdata или XLSX.getdata.

julia> import XLSX

julia> m = XLSX.readdata("myfile.xlsx", "mysheet!A1:B3")
3×2 Array{Any,2}:
  "HeaderA"  "HeaderB"
 1           "first"
 2           "second"

Индексирование в Worksheet приведет к диспетчеризации в метод XLSX.getdata.

julia> xf = XLSX.readxlsx("myfile.xlsx")
XLSXFile("myfile.xlsx") containing 3 Worksheets
            sheetname size          range
-------------------------------------------------
              mysheet 4x2           A1:B4
           othersheet 1x1           A1:A1
                named 1x1           B4:B4

julia> xf["mysheet!A1:B3"]
3×2 Array{Any,2}:
  "HeaderA"  "HeaderB"
 1           "first"
 2           "second"

julia> sheet = xf["mysheet"]
4×2 XLSX.Worksheet: ["mysheet"](A1:B4)

julia> sheet["A1:B3"]
3×2 Array{Any,2}:
  "HeaderA"  "HeaderB"
 1           "first"
 2           "second"

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

julia> sheet["A1"]
"HeaderA"

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

julia> dtable = XLSX.readtable("myfile.xlsx", "mysheet")
XLSX.DataTable(Any[Any[1, 2, 3], Any["first", "second", "third"]], [:HeaderA, :HeaderB], Dict(:HeaderB => 2, :HeaderA => 1))

julia> m = hcat(dtable.data...)
3×2 Matrix{Any}:
 1  "first"
 2  "second"
 3  "third"

Чтение больших файлов Excel и кэширование

У метода XLSX.openxlsx есть параметр enable_cache, который управляет кэшированием ячеек листа.

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

Если enable_cache=false, ячейки листа будут всегда считываться с диска. Рекомендуется использовать, если требуется считать таблицу, которая не помещается в памяти.

В следующем примере показано чтение ячеек листа по одной строке за раз, где myfile.xlsx является таблицей, которая не помещается в памяти.

julia> XLSX.openxlsx("myfile.xlsx", enable_cache=false) do f
           sheet = f["mysheet"]
           for r in XLSX.eachrow(sheet)
              # r — это `SheetRow`; значения считываются с помощью ссылок на столбцы
              rn = XLSX.row_number(r) # номер строки `SheetRow`
              v1 = r[1]    # считывается значение в столбце 1
              v2 = r["B"]  # считывается значение в столбце 2

              println("v1=$v1, v2=$v2")
           end
      end
v1=HeaderA, v2=HeaderB
v1=1, v2=first
v1=2, v2=second
v1=3, v2=third

Табличные данные можно также передавать в потоковом режиме с помощью функции XLSX.eachtablerow(sheet), которая является базовым итератором в методе gettable. Описание дополнительных параметров см. в docstring функции XLSX.eachtablerow.

julia> XLSX.openxlsx("myfile.xlsx", enable_cache=false) do f
           sheet = f["mysheet"]
           for r in XLSX.eachtablerow(sheet)
               # r - это`TableRow`; значения считываются с помощью заголовков или номеров столбцов
               rn = XLSX.row_number(r) # номер строки `TableRow`
               v1 = r[1] # считывается значение в столбце 1 таблицы
               v2 = r[:HeaderB] # считывается значение в столбце с заголовком `:HeaderB`

               println("v1=$v1, v2=$v2")
            end
       end
v1=1, v2=first
v1=2, v2=second
v1=3, v2=third

Запись файлов Excel

Создание новых файлов

При открытии файла в режиме write с помощью функции XLSX.openxlsx для редактирования открывается новый (пустой) файл Excel.

XLSX.openxlsx("my_new_file.xlsx", mode="w") do xf
    sheet = xf[1]
    XLSX.rename!(sheet, "new_sheet")
    sheet["A1"] = "this"
    sheet["A2"] = "is a"
    sheet["A3"] = "new file"
    sheet["A4"] = 100

    # добавляем строку с A5 по E5
    sheet["A5"] = collect(1:5) # эквивалентно `sheet["A5", dim=2] = collect(1:4)`

    # добавляем столбец с B1 по B4
    sheet["B1", dim=1] = collect(1:4)

    # добавляем матрицу с A7 по C9
    sheet["A7:C9"] = [ 1 2 3 ; 4 5 6 ; 7 8 9 ]
end

Редактирование существующих файлов

При открытии файла в режиме read-write с помощью функции XLSX.openxlsx для редактирования открывается существующий файл Excel. Данные, имеющиеся в исходном файле, сохраняются.

XLSX.openxlsx("my_new_file.xlsx", mode="rw") do xf
    sheet = xf[1]
    sheet["B1"] = "new data"
end

Известно, что режим read-write приводит к некоторой потере данных. См. проблему #159. Простые данные должны работать нормально. Пользователям рекомендуется с осторожностью использовать эту функцию при работе с формулами и диаграммами.

Экспорт табличных данных из электронной таблицы

При наличии ссылки на лист используйте метод XLSX.writetable!. Базовой ячейкой по умолчанию является ячейка "A1".

using XLSX, Test

filename = "myfile.xlsx"

columns = Vector()
push!(columns, [1, 2, 3])
push!(columns, ["a", "b", "c"])

labels = [ "column_1", "column_2"]

XLSX.openxlsx(filename, mode="w") do xf
    sheet = xf[1]
    XLSX.writetable!(sheet, columns, labels, anchor_cell=XLSX.CellRef("B2"))
end

# считываем данные обратно
XLSX.openxlsx(filename) do xf
    sheet = xf[1]
    @test sheet["B2"] == "column_1"
    @test sheet["C2"] == "column_2"
    @test sheet["B3"] == 1
    @test sheet["B4"] == 2
    @test sheet["B5"] == 3
    @test sheet["C3"] == "a"
    @test sheet["C4"] == "b"
    @test sheet["C5"] == "c"
end

Для записи напрямую в новый файл можно также использовать функцию XLSX.writetable (см. следующий раздел).

Экспорт табличных данных из любого источника, совместимого с `Tables.jl`

Для экспорта табличных данных в Excel используйте метод XLSX.writetable, который принимает либо столбцы и имена столбцов, либо любую таблицу Tables.jl.

julia> using Dates

julia> import DataFrames, XLSX

julia> df = DataFrames.DataFrame(integers=[1, 2, 3, 4], strings=["Hey", "You", "Out", "There"], floats=[10.2, 20.3, 30.4, 40.5], dates=[Date(2018,2,20), Date(2018,2,21), Date(2018,2,22), Date(2018,2,23)], times=[Dates.Time(19,10), Dates.Time(19,20), Dates.Time(19,30), Dates.Time(19,40)], datetimes=[Dates.DateTime(2018,5,20,19,10), Dates.DateTime(2018,5,20,19,20), Dates.DateTime(2018,5,20,19,30), Dates.DateTime(2018,5,20,19,40)])
4×6 DataFrames.DataFrame
│ Row │ integers │ strings │ floats │ dates      │ times    │ datetimes           │
├─────┼──────────┼─────────┼────────┼────────────┼──────────┼─────────────────────┤
│ 1   │ 1        │ Hey     │ 10.2   │ 2018-02-20 │ 19:10:00 │ 2018-05-20T19:10:00 │
│ 2   │ 2        │ You     │ 20.3   │ 2018-02-21 │ 19:20:00 │ 2018-05-20T19:20:00 │
│ 3   │ 3        │ Out     │ 30.4   │ 2018-02-22 │ 19:30:00 │ 2018-05-20T19:30:00 │
│ 4   │ 4        │ There   │ 40.5   │ 2018-02-23 │ 19:40:00 │ 2018-05-20T19:40:00 │

julia> XLSX.writetable("df.xlsx", df)

Вы также можете экспортировать в Excel несколько таблиц, каждая из которых будет на отдельном листе, передав в именованном аргументе либо кортеж (из столбцов и их имен) для каждого имени листа, либо список пар "sheet name" => table для любого совместимого источника Tables.jl.

julia> import DataFrames, XLSX

julia> df1 = DataFrames.DataFrame(COL1=[10,20,30], COL2=["Fist", "Sec", "Third"])
3×2 DataFrames.DataFrame
│ Row │ COL1 │ COL2  │
├─────┼──────┼───────┤
│ 1   │ 10   │ Fist  │
│ 2   │ 20   │ Sec   │
│ 3   │ 30   │ Third │

julia> df2 = DataFrames.DataFrame(AA=["aa", "bb"], AB=[10.1, 10.2])
2×2 DataFrames.DataFrame
│ Row │ AA │ AB   │
├─────┼────┼──────┤
│ 1   │ aa │ 10.1 │
│ 2   │ bb │ 10.2 │

julia> XLSX.writetable("report.xlsx", "REPORT_A" => df1, "REPORT_B" => df2)

В этом последнем примере показано, как сделать то же самое, но если вы заранее не знаете, сколько таблиц будет экспортироваться.

df1 = DataFrame(A=[1,2], B=[3,4])
df2 = DataFrame(C=["Hey", "you"], D=["out", "there"])

sheet_names = [ "1st", "2nd" ]
dataframes = [ df1, df2 ]

@assert length(sheet_names) == length(dataframes)

XLSX.openxlsx("report.xlsx", mode="w") do xf
    for i in eachindex(sheet_names)
        sheet_name = sheet_names[i]
        df = dataframes[i]

        if i == firstindex(sheet_names)
            sheet = xf[1]
            XLSX.rename!(sheet, sheet_name)
            XLSX.writetable!(sheet, df)
        else
            sheet = XLSX.addsheet!(xf, sheet_name)
            XLSX.writetable!(sheet, df)
        end
    end
end

Интерфейс Tables.jl

Оба типа — XLSX.DataTable и XLSX.TableRowIterator — соответствуют требованиям интерфейса Tables.jl. Экземпляр XLSX.TableRowIterator создается функцией XLSX.eachtablerow.

Кроме того, XLSX.writetable принимает аргумент, который соответствует требованиям интерфейса Tables.jl.

Например, тип DataFrame из пакета DataFrames поддерживает интерфейс Tables.jl. Следующий код записывает объект DataFrame в файл Excel и снова считывает его.

julia> using Dates

julia> import DataFrames, XLSX

julia> df = DataFrames.DataFrame(integers=[1, 2, 3, 4], strings=["Hey", "You", "Out", "There"], floats=[10.2, 20.3, 30.4, 40.5], dates=[Date(2018,2,20), Date(2018,2,21), Date(2018,2,22), Date(2018,2,23)], times=[Dates.Time(19,10), Dates.Time(19,20), Dates.Time(19,30), Dates.Time(19,40)], datetimes=[Dates.DateTime(2018,5,20,19,10), Dates.DateTime(2018,5,20,19,20), Dates.DateTime(2018,5,20,19,30), Dates.DateTime(2018,5,20,19,40)])
4×6 DataFrames.DataFrame
│ Row │ integers │ strings │ floats  │ dates      │ times    │ datetimes           │
│     │ Int64    │ String  │ Float64 │ Date       │ Time     │ DateTime            │
├─────┼──────────┼─────────┼─────────┼────────────┼──────────┼─────────────────────┤
│ 1   │ 1        │ Hey     │ 10.2    │ 2018-02-20 │ 19:10:00 │ 2018-05-20T19:10:00 │
│ 2   │ 2        │ You     │ 20.3    │ 2018-02-21 │ 19:20:00 │ 2018-05-20T19:20:00 │
│ 3   │ 3        │ Out     │ 30.4    │ 2018-02-22 │ 19:30:00 │ 2018-05-20T19:30:00 │
│ 4   │ 4        │ There   │ 40.5    │ 2018-02-23 │ 19:40:00 │ 2018-05-20T19:40:00 │

julia> XLSX.writetable("output_table.xlsx", df, overwrite=true, sheetname="report", anchor_cell="B2")

julia> f = XLSX.readxlsx("output_table.xlsx")
XLSXFile("output_table.xlsx") containing 1 Worksheet
            sheetname size          range
-------------------------------------------------
               report 6x7           A1:G6


julia> s = f["report"]
6×7 XLSX.Worksheet: ["report"](A1:G6)

julia> df2 = XLSX.eachtablerow(s) |> DataFrames.DataFrame
4×6 DataFrames.DataFrame
│ Row │ integers │ strings │ floats  │ dates      │ times    │ datetimes           │
│     │ Int64    │ String  │ Float64 │ Date       │ Time     │ DateTime            │
├─────┼──────────┼─────────┼─────────┼────────────┼──────────┼─────────────────────┤
│ 1   │ 1        │ Hey     │ 10.2    │ 2018-02-20 │ 19:10:00 │ 2018-05-20T19:10:00 │
│ 2   │ 2        │ You     │ 20.3    │ 2018-02-21 │ 19:20:00 │ 2018-05-20T19:20:00 │
│ 3   │ 3        │ Out     │ 30.4    │ 2018-02-22 │ 19:30:00 │ 2018-05-20T19:30:00 │
│ 4   │ 4        │ There   │ 40.5    │ 2018-02-23 │ 19:40:00 │ 2018-05-20T19:40:00 │