xou / elixlsx Goto Github PK
View Code? Open in Web Editor NEWAn Elixir XLSX writer
License: MIT License
An Elixir XLSX writer
License: MIT License
I can't find any documentation for this one. Is it even possible to do this?
Thanks for the great project!
This can be reproduced by creating the simplest possible file then trying to open it in Microsoft Excel for Mac (https://products.office.com/en-ca/buy/compare-microsoft-office-products?tab=tabs-2).
For example if you run the example from the readme:
Workbook.append_sheet(%Workbook{}, Sheet.with_name("Sheet 1") |> Sheet.set_cell("A1", "Hello", bold: true)) |> Elixlsx.write_to("hello.xlsx")
Then open the resulting file with Office for Mac results in the following warning:
If you click "Open and Repair" the file will open but be displayed with no styling and a prompt as follows:
If you click "View" to see the log you'll see:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>Repair Result to hello1050.xml</logFileName><summary>Errors were detected in file '/Users/bruce/Work/viewprogis/viewprogis_web/vpg/hello105.xlsx'</summary><removedParts summary="Following is a list of removed parts:"><removedPart>Removed Part: /xl/styles.xml with XML error. (Styles) Load error. Line 36, column 36.</removedPart></removedParts><repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml</repairedRecord><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet2.xml</repairedRecord></repairedRecords></recoveryLog>
Suggesting a problem with xl/styles.xml
I think I have found a solution and will submit a pull request soon.
I have a workflow where I'm generating the .xlsx
and uploading it to S3.
Would it be possible to embed an image either by reading a file or by using a Base64 encoding?
If the current version does not support this would it be technically feasible to add such a feature?
For some reason I need to generate a very large excel file, which has 700 cols and 13,000 rows. On my 4 core server it takes nearly 30 minutes to write file.
Because the file is created at the very end of the process (and is fast), I guess the bottle neck is on generating the XML in memory. But I'm not sure if it's an Elixir performance issue or this library's. I also have several steps to transform the data to %Worksheet{}
(without stream), and it's very fast.
When setting Elixlsx.Sheet
pane_freeze
to {1, 0}
, MS Office on Mac OS complains about corrupted file with
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>freeze_bug.xlsx</logFileName>
<summary>Errors were detected in file 'freeze_bug.xlsx'</summary>
<removedFeatures summary="Following is a list of removed features:">
<removedFeature>Removed Feature: View from /xl/worksheets/sheet1.xml part</removedFeature>
</removedFeatures>
</recoveryLog>
Minimal reproduction:
require Elixlsx
alias Elixlsx.Sheet
alias Elixlsx.Workbook
sheet1 = Sheet.with_name("Freeze bug")
|> Sheet.set_cell("A1", "Header")
|> Sheet.set_cell("B1", "value")
|> Sheet.set_pane_freeze(1, 0)
workbook = %Workbook{sheets: [sheet1]}
Elixlsx.write_to(workbook, "freeze_bug.xlsx")
I need to support hyperlinks. I'm thinking of making a PR for it, but this will probably take a while and I'm not sure how to support cells that contain hyperlinks and normal text at the same time (though I don't personally need it, that seems allowed in the spec).
For anyone looking for a quick fix, you can use the HYPERLINK
function like [{:formula, "HYPERLINK(\"#{url}\", \"#{url}\")"}]
.
So for now I'll just leave this issue here of how they look like in the XLSX:
In the sheet$n.xml
row, the hyperlink is referenced via r
, like r=C8
.
In the sheet$n.xml
, in the <workbook/>
tag this section:
<hyperlinks>
<hyperlink ref="C8" r:id="rId1" display="http://example.com"/>
</hyperlinks>
In worksheets/_rels/sheet$n.xml.rels
is this content:
<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="http://example.com" TargetMode="External"/>
</Relationships>
I'm not entirely sure how the code for a cell should look like, given that hyperlinks can be mixed with other content.
Hello,
When I try to use a cell of another sheet:
{:formula, "$Deductions.I1"}
The result on the excel cell is the following: =$deductions.i1
and due to the lowercase is not working.
The Workbook seems fine ([formula: "$Deductions.I1", ...]), might be the write_to_memory.
Thank you!
Would it be possible to open an already existing XLSX and only change the text of certain cells?
Hello,
For a code like:
alias Elixlsx.Sheet
alias Elixlsx.Workbook
sheet1 = Sheet.with_name("First")
|> Sheet.set_cell("A1", {:formula, "FILTERXML(A2,\"//price\")"})
|> Sheet.set_cell("A2",
"<?xml version=\"1.0\" encoding=\"UTF-8\"?><pricing><price>0.9</price></pricing>"
)
%Workbook{sheets: [sheet1]}
|> Elixlsx.write_to("example.xlsx")
The formula FILTERXML
is considered as an invalid name.
Graphically, it's can be regulated like a valid name with a manual acceptance of the formula.
I want to make the data type of all cells "text" in the Excel downloaded.
Hello all, great project :) I have a question about possibility to adding a comment to cell?
Is it possible to set cell to an integer value rather than float?
Hey!
I was playing around with your library and it works quite nicely, however i would like to set the background color for the cells.
Is there an option i missed or would it be possible to add?
<c r="M4" s="1">
<f>E4*D4-C4</f>
<v>312.39750000000004</v>
</c>
In 0.4.2 trying to create a sheet with any string causes a function XML.valid?/1 is undefined or private
error. Think this is related to this change https://github.com/xou/elixlsx/pull/86/files#diff-602217924496a0bfead8d52a018857f7R28
Running through the examples here with 0.4.2 don't work as long as any string is present in any row. Removing all instances of strings lets the library work as intended.
I've verified that going down a version to 0.4.1 fixes the issue with strings.
I want to be able to add a Date ~D[2020-02-27]
My current solution is to change it to {Date.to_erl(date), {0, 0, 0}}
with the option yyyymmdd: true
but I would like to not have to pass in the tuple.
Hi, great work on this library.
Please i'm using this to create excel files from a database.
Some of my data contains decimals (via the Elixir Decimal library, e.g. #Decimal<100.25>)
This error comes up:
ERR: %CaseClauseError{term: #Decimal<100.0>}
(elixlsx) lib/elixlsx/xml_templates.ex:163: Elixlsx.XMLTemplates.get_content_type_value/2
(elixlsx) lib/elixlsx/xml_templates.ex:197: anonymous fn/3 in Elixlsx.XMLTemplates.xl_sheet_cols/3
(elixir) lib/enum.ex:1229: Enum."-map/2-lists^map/1-0-"/2
(elixir) lib/enum.ex:1229: Enum."-map/2-lists^map/1-0-"/2
(elixlsx) lib/elixlsx/xml_templates.ex:185: Elixlsx.XMLTemplates.xl_sheet_cols/3
(elixlsx) lib/elixlsx/xml_templates.ex:258: anonymous fn/3 in Elixlsx.XMLTemplates.xl_sheet_rows/3
Please how may we use the Decimal type with elixlsx
Thanks.
Hi there and thanks for your work on this library!
Would it be possible to get a release? We currently have lots of warnings in our build logs due to the "old" String.slice syntax you already fixed upstream and we would love to upgrade to a new version.
Thanks again ❤️
x when is_atom(x) ->
{"s", Atom.to_string(x)}
at get_content_type_value /1 in xml_templates.ex
:propcheck
stops the app from running - can we remove it?
Hi!
I love how simple the API in this project is!
I can't find anything about reading xlsx file in the documentation. I am searching for something that can modify existing xlsx file. Is this possible?
Thanks for your amazing work!
Hi, thanks for writing/maintaining this library ✌️
Could you please git push --tags
when you have a chance so that the newer releases are tagged appropriately on GitHub? Thanks!
I use the Elixlsx.Sheet.set_row_height/2
function for change the height. But the row height is not changed. The code snippet from the example.exs
does not work too.
OS: Mac OS X
Software: Microsoft Excel for Mac (2016)
I will try to find and solve the problem.
Trying to set cell background color like this:
rows = [ [ [5], [35], [235, bg_color: "#ffff00"] ] ]
sheet = %Sheet{rows: rows}
workbook = %Workbook{sheets: [sheet]}
{:ok, excel} = Elixlsx.write_to(workbook, "example.xlsx")
In result getting 3-rd cell with white background, the rest cells have yellow background. Expecting an opposite result.
This code:
%Workbook{sheets: [%Sheet{name: "Posts", rows: [nil]}]}
breaks the lib when I execute Elixlsx.write_to_memory("output.xlsx")
on Elixlsx.Compiler.compinfo_from_rows.
That's behaviour expected?
Please can we have a way to set cell/range properties without changing the cell text.
|> Sheet.set_cell("A1", bold: true, color: "#ffffff", "Employee Data", bg_color: "#0070C0")
If the content of "A1" is already set, can we have for example:
|> Sheet.format_cell("A1", bold: true, color: "#ffffff", bg_color: "#0070C0")
Not sure if this functionality is already available, I couldn't find it in the documents. Please point me if it is present already.
Otherwise appreciate if this is something that can be done as a feature request.
Thanks
Is it possible to use rich text in the cells?
That is, to partially format the text?
This PR fixed a Dialyzer issue in the write_to_memory
function. Can we get a bump on Hex so that users can get this fix without specifying the GitHub dependency?
Hi there, I've encountered a problem when a sheet's name contains an &
character the generated file can't be opened by excel.
To reproduce:
%Workbook{sheets: [%Sheet{name: "elix & lsx"}]} |> Elixlsx.write_to("elixlsx-test.xlsx")
At first I thought that maybe & characters were not allowed in sheet names, but I can use them when working within excel, is this an expected behavior?
ps. Thanks for your work on this library!
Hello,
I got couple of warnings with Elixir 1.5.2 and version 0.3.0:
warning: String.to_char_list/1 is deprecated, use String.to_charlist/1
lib/elixlsx/writer.ex:112
warning: String.to_char_list/1 is deprecated, use String.to_charlist/1
lib/elixlsx/util.ex:56
warning: String.to_char_list/1 is deprecated, use String.to_charlist/1
lib/elixlsx/util.ex:63
warning: the char_list() type is deprecated, use charlist()
lib/elixlsx/writer.ex:9
warning: defp style_to_xml_entry/2 is private, @doc's are always discarded for private functions/macros/types
lib/elixlsx/xml_templates.ex:369
warning: defp make_style_alignment/1 is private, @doc's are always discarded for private functions/macros/types
lib/elixlsx/xml_templates.ex:440
warning: defp make_cellxfs/2 is private, @doc's are always discarded for private functions/macros/types
lib/elixlsx/xml_templates.ex:457
I saw cell formatting is possible. But is it possible to format a column
The pattern {'ok', {__filename@1, _binary@1}} can never match the type {'error',_}
Using Elixir 1.7.3 and Elixlsx 0.4.0:
defmodule Foo do
require Elixlsx
alias Elixlsx.Sheet
alias Elixlsx.Workbook
@spec generate_report() :: binary()
def generate_report() do
sheet =
Sheet.with_name("OSHA")
|> Sheet.set_cell("B1", "wat")
filename = "filename.xlsx"
{:ok, {_filename, binary}} =
%Workbook{sheets: [sheet]}
|> Elixlsx.write_to_memory(filename)
binary
end
end
Right now we have a 2.5mb spreadsheet being generated, and the call to write_to_memory/2
takes about 25 seconds. Is this more than normal, or should we be investigating a cause?
Regardless it would be a very high priority for us to make this faster.
The 0.4.2 release includes code that defines a top-level XML
module (from #86), which causes compilation to fail when loaded in a project that also defines a top-level XML
module (or a warning, depending on the setting of --warnings-as-errors
).
Usual practice would be to move that module to Elixlsx.XML
and add alias
lines to the couple of places that use it.
How can I append new lines to an existing file without overwrite the actual file lines?
example: "happy\b"
can not insert, display empty
I have two files TestRun.xlsx and TestRun.xls that should return the same.
However xls
files are return nil
where there should be an integer.
{:ok, pid, parser} = Exoffice.parse("test/csv_examples/TestRun.xls", 0)
s = Exoffice.get_rows(pid, parser)
Enum.map(s, fn(row)->
Enum.map(row, fn(item) -> item end)
end)
assert xlsx_sheet_to_list("test/csv_examples/TestRun.xlsx") === [
["ID", "Thing 1", "Things 2 "],
[1, "apple ", "blue "],
[2, "orange", "red"],
[3, "pear", "green",
[4, "strawberry", "yellow"],
[5, "blueberry", "purple"],
[6, "kiwi", "orange"],
[7, "lime", "cyan"],
[8, "lemon", "magenta"]
]
assert xls_sheet_to_list("test/csv_examples/TestRun.xls") === [
["ID", "Thing 1", "Things 2 "],
[nil, "apple ", "blue "],
[nil, "orange", "red"],
[nil, "pear", "green",
[nil, "strawberry", "yellow"],
[nil, "blueberry", "purple"],
[nil, "kiwi", "orange"],
[nil, "lime", "cyan"],
[nil, "lemon", "magenta"]
]
This code, as given in the doc example, works but generates a dialyzer warning about breaking the contract:
Workbook.append_sheet(%Workbook{}, Sheet.with_name("blah"))
This does not:
bogus_sheet = Sheet.with_name("Unused")
Workbook.append_sheet(%Workbook{sheets: [bogus_sheet]}, Sheet.with_name("blah))
But of course that gives us two sheets.
The problem appears to be in the Workbook module, line 16, which defines sheets
as nonempty_list
. This makes sense once the sheet is created, but not during creation.
The workaround might be to avoid append_sheet
for creation and use %Workbook{sheets: [sheet]}
instead.
Suggestion: add a Workbook.new(sheet)
function.
Please merge in PR #121 , which should make Dialyzer happy when using Elixlsx.
If I do
sheet = %Elixlsx.Sheet{name: "I am a very long sheet name sorry", rows: [[1,2],[1,2]]}
workbook = %Elixlsx.Workbook{sheets: [sheet]}
Elixlsx.write_to_memory(workbook, "#file.xlsx")
I got a warning when opening the file in excel (the Data is OK).
This is more a question or maybe a suggestion for improvement.
Is it possible to set the column width to automatically adapt to the width of the content of the data in the cells? In Excel/LibreOffice you can easily adapt the width by double clicking the resize handle of the column but is there any way to programatically achieve this without a hardcoded width?
Using {:elixlsx, "~> 0.3.1"}
still reflects old code with String.to_char_list
method even though master branch has been updated to String.to_charlist
https://github.com/xou/elixlsx/blob/master/lib/elixlsx/util.ex#L56
warning: String.to_char_list/1 is deprecated, use String.to_charlist/1 lib/elixlsx/util.ex:56
There's a function to generate the spreadsheet passing only a list and then auto generate a XLSX that each line(cell line) represents each value in the List???
I am trying to take a list of lists and turn it into a spreadsheet
I am doing this by enumerating over everything and putting it into Sheet.set_at/5
Upon calling Workbook.append_sheet(%Workbook{}, worksheet) |> Elixlsx.write_to_memory("filename.xlsx")
I get the following
** (ArgumentError) you attempted to apply :rows on {:ok, %Elixlsx.Sheet{col_widths: %{}, group_cols: [], group_rows: [], merge_cells: [], name: "test", pane_freeze: nil, row_heights: %{}, rows: [[["registration", {:bold, true}]], [["C-FUYE"]], [["C-FJXA"]], [["C-LSFR"]], [["C-FUZH"]], [["C-FXPY"]], [["C-DUOD"]], [["C-PRIV"]]], show_grid_lines: true}}. If you are using apply/3, make sure the module is an atom. If you are using the dot syntax, such as map.field or module.function, make sure the left side of the dot is an atom or a map
:erlang.apply({:ok, %Elixlsx.Sheet{col_widths: %{}, group_cols: [], group_rows: [], merge_cells: [], name: "test", pane_freeze: nil, row_heights: %{}, rows: [[["registration", {:bold, true}]], [["C-FUYE"]], [["C-FJXA"]], [["C-LSFR"]], [["C-FUZH"]], [["C-FXPY"]], [["C-DUOD"]], [["C-PRIV"]]], show_grid_lines: true}}, :rows, [])
(elixlsx) lib/elixlsx/compiler.ex:68: anonymous fn/2 in Elixlsx.Compiler.compinfo_from_sheets/2
(elixir) lib/list.ex:228: List."-foldl/3-lists^foldl/2-0-"/3
(elixlsx) lib/elixlsx/compiler.ex:80: Elixlsx.Compiler.make_workbook_comp_info/1
(elixlsx) lib/elixlsx.ex:58: Elixlsx.write_to_memory/2
I Have:
To read from the file Elixlsx created into Workbook?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.