coreoffice / corexlsx Goto Github PK

I am getting an error while reading excel sheet cell content.
using this method to read content. file.parseSharedStrings()
getting an error this "Expected to decode String but found SharedBox instead."
SampleExcel.xlsx

Example cell format:

 <xf numFmtId="0" fontId="0" fillId="4" borderId="4" applyNumberFormat="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1" applyProtection="0"><alignment vertical="bottom"/></xf>

is encoded as:

CoreXLSX.Format(numberFormatId: 0, fontId: 0, applyNumberFormat: false, applyFont: true, applyFill: true, applyBorder: true, applyAlignment: true, applyProtection: false, alignment: CoreXLSX.Format.Alignment(vertical: Optional("bottom"), horizontal: nil, wrapText: nil))

Note, fillId and borderId are missing.

i see that cells contain some kind of indices instead of actual values (i.e. some kind of integer number instead of string)
how can i get actual value of a cell? probably there is some kind of reference table for it?

I crashed while calling try file.parsesharedstrings () with an error:

keyNotFound(CodingKeys(stringValue: "uniqueCount", intValue: nil), Swift.DecodingError.Context(codingPath: [CodingKeys(stringValue: "uniqueCount", intValue: nil)], debugDescription: "No attribute or element found for key CodingKeys(stringValue: \"uniqueCount\", intValue: nil) (\"uniqueCount\").", underlyingError: nil))"

Maybe I use library incorrectly way, sorry.
I can't parse files from google sheets.
Example file is: https://docs.google.com/spreadsheets/d/1sYkVn4MkLaXS2zUrh3_HTsgHHqNvWy-47dhlh32EfqM/export?format=xlsx
I try to parse sheet from the file and get something about:
Cell(reference: C1, type: Optional("s"), s: Optional("2"), inlineString: nil, formula: nil, value: Optional("1"))
But there is string "АПО-1" in cell C1. I get values like strange numbers for each cell in the table.

Code is here:

guard let url = url, let file = XLSXFile(filepath: url.path) else { return }
for path in try file.parseWorksheetPaths() {
let ws = try file.parseWorksheet(at: path)
for row in ws.data?.rows ?? [] {
for c in row.cells {
print(c)
}
}
}

XMLCoder should have publicly visible docs for its API. First option to consider for auto-generated docs is Jazzy. Generated docs could be published with GitHub Pages.

I download xml from bank and I want convert it to csv
I try last version CoreXLSX, but I can't open file
(numbers and Microsoft excel already can open this file)
is can be opened file like this format, by CoreXLSX?

The following file referenced in the readme returns a 404:
https://github.com/MaxDesiatov/CoreXLSX/blob/master/Sources/CoreXLSX/Worksheet.swift

It should be:
https://github.com/MaxDesiatov/CoreXLSX/blob/master/Sources/CoreXLSX/Worksheet/Worksheet.swift

Hello,

I cannot read values from cells which contain numbers, eg the value 1,19. The cell is formatted as Number.

Is this possible with this library?

Thanks!

Error Domain=NSXMLParserErrorDomain Code=73 "(null)" UserInfo={NSXMLParserErrorColumn=16328, NSXMLParserErrorLineNumber=2, NSXMLParserErrorMessage=Couldn't find end of Start Tag c
}

I'm getting this error while trying to read a sheet from a parsed xlsx file.
I think the error is happening on the Worksheet file, where it has CodingKeys enum and a dimension case.
It looks like related with #12, #15 and #24 issues.

Fatal error: Error raised at top level: 
Swift.DecodingError.keyNotFound(CodingKeys(stringValue: "dimension", intValue: nil),
Swift.DecodingError.Context(codingPath: [], debugDescription: "No value associated with key CodingKeys(stringValue: \"dimension\", intValue: nil) (\"dimension\").", underlyingError: nil))

I tried the parsing with sharedStrings and the value of cells directly in case of numbers. Both work fine. But when there is a date in the cell field. It's not returned as String, which I thought it would be. But I get a rather random number 4328 something.

Version
'CoreXLSX', '~> 0.10.0'

Describe the bug
XLSXFile(filepath: path) was not initialising on passing the path of .xlsx file which is in application's document directory.
But when i put .xlsx file in application's bundle folder and pass bundle path to XLSXFile(filepath: path) at that time library was successfully initialise and read excel data perfectly.

To Reproduce
Steps to reproduce the behavior:

1. Save a .xlsx file in application's document directory
2. Pass path of file whose location is in document directory to XLSXFile(filepath: path)
3. Seems XLSXFile not initialising

Expected behavior
Library should be initialise when passing path of document directory or other locations like iCloud documents etc.

I read a xlsx file containing Chinese characters, but the corresponding cell returns nil.
Can you tell me why?

Thanks!

It would be convenient if there was something like file.parseWorksheet(filepath: "xmlfile.xml", name: "myworksheet")

Is there any roadmap to support xlsb file format?

A cell has inline string but CoreXLSX return nil

Print result in console and i get this:
Cell(reference: E1, type: Optional("s"), s: nil, inlineString: nil, formula: nil, value: Optional("1157"))

Version

0.11.0

Describe the bug

Copied example code from readme but I have compile errors

To Reproduce
Steps to reproduce the behavior:

1. copy code from readme

let filepath = "./categories.xlsx"
guard let file = XLSXFile(filepath: filepath) else {
  fatalError("XLSX file at \(filepath) is corrupted or does not exist")
}

for (worksheetName, path) in try file.parseWorksheetPathsAndNames() {
  if let worksheetName = worksheetName {
    print("This worksheet has a name: \(worksheetName)")
  }

  let worksheet = try file.parseWorksheet(at: path)
  for row in worksheet.data?.rows ?? [] {
    for c in row.cells {
      print(c)
    }
  }
}

2. compile

Expected behavior
Worktable code in readme

pls add editing & saving

I need to parse styles.xml to get the properties like time，can you help me

I want complete list string value not unique from .xlsx file in including empty cell also and in particular order row wise or column wise, but Using sharedString.xml, I am getting only unique values and random order. How can I do this? Can u help on this? Thanks

Hi.

I am posting this issue since when I try to parse excel files like the one attached I get the message "archiveEntryNotFound" and the cells are not serialised.

137679-ComparativeSalesInformation.xlsx

Thank you!

Hello,
Thank you for creating such an awesome library I'm trying to open a .xlsx file from he document directory and it crashes all the time.

Now when I open the xlsx file from the Bundle
with the below URL : /var/containers/Bundle/Application/625F9EB4-041E-4546-83A4-0712C667E57F/RegressionTool.app/TestData.xlsx

It works fine.

But when I open it from the Documents Directory the url is :
file:///private/var/mobile/Containers/Data/Application/024B9FF2-3059-4C0B-9CD6-CDE59D20A4D5/Documents/Inbox/Blank-2.xlsx

(now this Blank-2.xlsx is another file stored in Documents Directory).

It crashes and gives me fatalError("XLSX file corrupted or does not exist")

A simple question How can I open it.

Any help is appreciated.

Thank you.

Version
'~> 0.6.1'

Describe the bug
Error I'm getting when trying to load very basic spreadsheets:

error: valueNotFound(CoreXLSX.Fonts, Swift.DecodingError.Context(codingPath: [CodingKeys(stringValue: "fonts", intValue: nil)], debugDescription: "Expected Fonts value but found null instead.", underlyingError: nil))

To Reproduce
HelloWorld.xlsx
Blank.xlsx

Attaching two files which fail to load with the below code:

        let f = Bundle.main.url(forResource: "Models.scnassets/levels/HelloWorld", withExtension: "xlsx")!
        guard let file = XLSXFile(filepath: f.path) else {
            fatalError("XLSX file corrupted or does not exist")
        }
      
        do {
            let styles = try file.parseStyles()
            
            let ws = try file.parseWorksheet(at: "xl/worksheets/sheet1.xml")
            for row in ws.data?.rows ?? [] {
                for c in row.cells {
                    print(c)
                }
            }
        } catch CoreXLSXError.archiveEntryNotFound {
              print("CoreXLSXError.archiveEntryNotFound")
        } catch {
            print("error: \(error)")
        }

Expected behavior
The files should load and just ignore empty border/font tags similar to this:

<borders count="1"><border/></borders>
<fonts count="2"><font><sz val="10.0"/><color rgb="FF000000"/><name val="Arial"/></font><font/></fonts>

Failing elements:

<si>
    <r>
      <rPr>
        <sz val="10"/>
        <color indexed="8"/>
        <rFont val="Helvetica Neue"/>
      </rPr>
      <t xml:space="preserve"> the </t>
    </r>

This is blocked by CoreOffice/XMLCoder#12, potential API design for resolving this is described in MaxDesiatov/XMLCoder#45

Hi @MaxDesiatov - function was working but I'm now getting a crash on a test sheet, any ideas?

Crash message is: 'try!' expression unexpectedly raised an error: Swift.DecodingError.dataCorrupted(Swift.DecodingError.Context(codingPath: [CodingKeys(stringValue: "relationship", intValue: nil), _XMLKey(stringValue: "Index 3", intValue: 3), CodingKeys(stringValue: "type", intValue: nil)], debugDescription: "Cannot initialize SchemaType from invalid String value http://schemas.openxmlformats.org/officeDocument/2006/relationships/calcChain", underlyingError: nil))

Spreadsheet is:
Logbook.xlsx

Update: the issue seems to affect any document with formulas, where a calcChain relationship is included in the relationships file

Thanks, Phil

(lldb) po file.parseWorksheetPaths()
▿ 3 elements

• 0 : "xl/worksheets/sheet3.xml"
• 1 : "xl/worksheets/sheet2.xml"
• 2 : "xl/worksheets/sheet1.xml"

(lldb) po file.parseWorksheet(at: "xl/worksheets/sheet3.xml")
▿ DecodingError
▿ keyNotFound : 2 elements
- .0 : CodingKeys(stringValue: "cols", intValue: nil)
▿ .1 : Context
- codingPath : 0 elements
- debugDescription : "No value associated with key CodingKeys(stringValue: "cols", intValue: nil) ("cols")."
- underlyingError : nil

(lldb) po file.parseWorksheet(at: "xl/worksheets/sheet2.xml")
▿ DecodingError
▿ keyNotFound : 2 elements
- .0 : CodingKeys(stringValue: "cols", intValue: nil)
▿ .1 : Context
- codingPath : 0 elements
- debugDescription : "No value associated with key CodingKeys(stringValue: "cols", intValue: nil) ("cols")."
- underlyingError : nil

(lldb) po file.parseWorksheet(at: "xl/worksheets/sheet1.xml")
Error Domain=NSXMLParserErrorDomain Code=39 "(null)" UserInfo={NSXMLParserErrorColumn=16328, NSXMLParserErrorLineNumber=2, NSXMLParserErrorMessage=AttValue: " or ' expected
}
▿ DecodingError
▿ dataCorrupted : Context
- codingPath : 0 elements
- debugDescription : "The given data was not valid XML."
▿ underlyingError : Optional
- some : Error Domain=NSXMLParserErrorDomain Code=111 "(null)"

Hello,

I can not read the numeric value from sheet through sharedStrings. Could you please check this ? i attached the screenshot of example sheet. ( Here, i m not getting the values 1,2,3.. from "No" column. )

(lldb) po file.parseWorksheetPaths()
▿ 3 elements

• 0 : "xl/worksheets/sheet3.xml"
• 1 : "xl/worksheets/sheet2.xml"
• 2 : "xl/worksheets/sheet1.xml"

(lldb) po file.parseWorksheet(at: "xl/worksheets/sheet3.xml")
▿ DecodingError
▿ keyNotFound : 2 elements
- .0 : CodingKeys(stringValue: "sheetData", intValue: nil)
▿ .1 : Context
- codingPath : 0 elements
- debugDescription : "No value associated with key CodingKeys(stringValue: "sheetData", intValue: nil) ("sheetData")."
- underlyingError : nil

As soon as new version of XMLCoder is released, it will provide a new error context length property, which should be exposed on XLSXFile to make debugging in CoreXLSX easier

The 'Workbook' object exposes sheet names, however these are related to actual sheet data (in xl/worksheets) using the rId relationship property via the 'workbook.xml.rels' file:

(workbook.xml): <sheets> <sheet name="Test Sheet 1" sheetId="1" r:id="rId1"/>
(workbook.xml.rels): <Relationship Id="rId1" Target="worksheets/sheet1.xml"/>

The 'parseWorksheetPaths' method on XLSXFile accesses the rels file to get the worksheet paths but does not publicly expose the other properties. If you want to keep this library to reflect the underlying structure of the XLSX file (which I think is correct BTW) I think it just needs a 'Relationship' object with 3 properties: ID, path and type. 'parseWorksheetPaths' would need to be updated to read these objects then filter and map as at present. Only issue I can see is that in the rels file 'path' is relative to its directory so you might need to prefix that directory to the path to stay consistent with the way paths are treated currently.

try! parseWorksheet throwing exception:

Error Domain=NSXMLParserErrorDomain Code=73 "(null)" UserInfo={NSXMLParserErrorColumn=16328, NSXMLParserErrorLineNumber=2, NSXMLParserErrorMessage=Couldn't find end of Start Tag c
}

Version
0.8.0

Describe the bug
First of all I'd like to give my compliments to this library. The speed is impressive.

We use multi-line strings in Excel cells. In Excel on macOS, this can be accomplished by using CTRL+ALT+ENTER for a new line within a cell.

When loading the Excel file with CoreXLSX and using parseSharedStrings() the multi-line formatting is removed, resulting in incorrect text since the lines are appended as a single line string.

To Reproduce
Steps to reproduce the behavior:

1. Prepare the Excel file. Cell A1 contains an ordinary single line text.
   Cell A2 is multi-line and contains the following 3 lines (all separated by CTRL+ALT+ENTER):

first line
second line (separated by CTRL+ALT+ENTER on macOS)
third line

2. Use Xcode to load the Excel file and get the shared strings with CoreXLSX:

// Load Excel file:
guard let file = XLSXFile(filepath: filepath) else {
    fatalError("XLSX file corrupted or does not exist")
}

// Get shared strings:
let sharedStrings = try? file.parseSharedStrings()
print("Shared Strings: ", sharedStrings!)

3. Xcode Console output:

Shared Strings:  SharedStrings(uniqueCount: 2, items: [CoreXLSX.SharedStrings.Item(text: Optional("just a single line"), richText: []), CoreXLSX.SharedStrings.Item(text: Optional("first linesecond line (separated by CTRL+ALT+ENTER on macOS)third line"), richText: [])])

4. Note that the second cell doesn't have any new line formatting in the text property, but all 3 lines are merged together:
   "first linesecond line (separated by CTRL+ALT+ENTER on macOS)third line"

Expected behavior
That the new line formatting is preserved in the text property. Similar to the original sharedStrings.xml file within the XLSX file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="2" uniqueCount="2"><si><t>just a single line</t></si><si><t>first line
second line (separated by CTRL+ALT+ENTER on macOS)
third line</t></si></sst> 

File for reproduction
Excel file:
multi-line text in cell.xlsx

Additional context
Probably this is not related to CoreXLSX directly, but to any of the dependencies (e.g. XMLCoder).
Possible related issues on other libraries that might help solving this:

• weshatheleopard/rubyXL#202
• jmcnamara/libxlsxwriter#156

Received this error in catch (NSError) when parsing the worksheet

if filemgr.fileExists(atPath: dataFileURL.path) {
let file = XLSXFile(filepath: dataFileURL.path)
do {
for path in try file!.parseWorksheetPaths() {
let worksheets = try file!.parseWorksheet(at: path)

Printing description of self.error:
▿ Optional

• some : Error Domain=NSCocoaErrorDomain Code=4865 "Expected String but found null instead." UserInfo={NSCodingPath=(
  "CodingKeys(stringValue: "sheetData", intValue: nil)",
  "CodingKeys(stringValue: "row", intValue: nil)",
  "XMLKey(stringValue: "10", intValue: 10)",
  "XMLKey(stringValue: "10", intValue: 10)",
  "CodingKeys(stringValue: "c", intValue: nil)",
  "XMLKey(stringValue: "8", intValue: 8)",
  "XMLKey(stringValue: "8", intValue: 8)",
  "CodingKeys(stringValue: "f", intValue: nil)"
  ), NSDebugDescription=Expected String but found null instead.}

I have sent a copy of xlsx. Can't really do anything beyond this.

Version
0.9.1

Describe the bug
file.parseWorksheet(at: path) throws CoreXLSXError.archiveEntryNotFound when trying to access a single sheet spreadsheet.

parseWorksheetPaths() returns [ "xl//xl/worksheets/sheet1.xml" ], which looks wrong. I'd guess, because parseWorksheetPaths() does not check that a worksheet.target ("/xl/worksheets/sheet1.xml" in this case) contains a root path and adds the directory prefix ("xl") on top of that.

File for reproduction
Unfortunately, I cannot provide the file in question. Try generating one in Windows version fo Excel.

Additional context
Add any other context about the problem here.

Worksheet names are stored in the workbook.xml file and linked using r:Id to workbook.xml.rels - it would be good if this was read at the same time as parseDocumentPaths so a calling function could reference worksheets by name instead of path? However I realise that this would break the close link between objects in this library and the XLSX archive structure so maybe a looking function to get a worksheet name from a path (by unzipping workbook.xml) would do the job? Thanks

I try to use different excel in my project. but it's always crash at here.

storage.push(container: box) decoded = try type.init(from: self) storage.popContainer()
Anyone know how what happening here? Thanks

Hi there,

I'm assuming you can't use this library to read encrypted sheets with a password.
Is this feature easy to add?

Thanks...

Robert

Version
0.9.0

Describe the bug
Parsing a spreadsheet from a customer throws this error. I can provide a sanitized version if required, but looking over the code, it seems like this is just an omitted type from Relationships.swift.

See the attached Package.swift, main.swift, and test.xlsx. The spreadsheet contains one worksheet with 3 rows and 3 columns. Yet when reading them Rows 1 and 3 show only 2 columns because ColumnB is empty. There should be 3 cells in each of the 3 rows.

If I am missing something in the documentation please let me know.
Thanks.

Archive.zip

I'm getting this error all the time

typeMismatch(Swift.String, Swift.DecodingError.Context(codingPath: [CodingKeys(stringValue: "sheetData", intValue: nil), CodingKeys(stringValue: "row", intValue: nil), XMLKey(stringValue: "365", intValue: 365), CodingKeys(stringValue: "c", intValue: nil), XMLKey(stringValue: "2", intValue: 2), CodingKeys(stringValue: "f", intValue: nil)], debugDescription: "Expected to decode String but found SharedBox instead.", underlyingError: nil))

XLXSFile.worksheetCache does not seem to be written to - parseWorksheet attempts to get a worksheet object from it but never updates it. Not really a bug as it will still work, but am I missing something?

Currently there's no data on how parsing speed changes for big files. This is somewhat blocked by #20. When that one's implemented I'd like CoreXLSX to write out and parse back big files and record the time to be correlated with changes made to XMLCoder and model types.

It's also worth investigating Attabench for this.

Hi @duodo2412, the value of the cell B14 in this case is not "", it's essentially nil, because the cell itself is absent in the .xlsx file in this case. If you'd like to verify that, you can specify the exact cell:

let cell = worksheet.cells(atColumns: [ColumnReference("B")!], rows: [4])

My undertstanding is that this will return an empty array [] in your case. Does that resolve your issue?

Tks for the your answer .
Can i ask you another question.

I have a lot of sheets in xlsx file.
My code:
let sheets: [String] = try file.parseWorksheetPaths() and print result

• After that i change the order of sheet in file and execute my code again. Result is the same before i change the order of sheet .
  How can i know exactly the sheet i want to get ( may be i can query with name of sheet ) . Can you help me?
  Tks

Originally posted by @duodo2412 in #21 (comment)

Version
CoreXLSX (0.11.0)

Describe the bug
Excel cells with Time validator not get correctly parsed.
Look at the attached file.

To Reproduce

let sharedStrings = try file.parseSharedStrings()
let paths = try file.parseWorksheetPaths()
for path in paths {
    let worksheet = try file.parseWorksheet(at: path)
    for row in worksheet.data?.rows ?? [] {
        for c in row.cells {
            switch c.reference.column.value {
            case "I": 
                // should be 9:30 or 09:30:00
                print(c) // Cell(reference: I2, type: nil, s: Optional("7"), inlineString: nil, formula: nil, value: Optional("0.39583333333212067"))
                print(c.stringValue(sharedStrings)) // Optional("0.39583333333212067")
                print(c.dateValue) // Optional(1901-01-29 18:59:59 +0000)
                print(c.value) // Optional("0.39583333333212067")

Expected behavior
09:30:00 as string

File for reproduction
look at the isf_agenda_template_1_0.xlsx attached file

Additional context
Swift 5 iOS app running on iPhone 7 with iOS 13.5.

isf_agenda_template_1_0.xlsx

The comment for parseDocumentRelationships reads:

Return parsed path with a parsed relationships model for a document at given path. Use parseDocumentPaths first to get a string path to pass as an argument to this function.

However, parseDocumentPaths has a default access level of "internal", so you can't call it to get the paths.

XLSXFile.parseEntry invokes archive.extract with the default buffer size of 16k bytes, this is not big enough for many Excel files and causes the XML decoder to terminate with a 'Premature end of file' error (or other error).
The function needs to be invoked with a larger buffer at least as big as the longest line of XML in the input stream - I have a worksheet with 1 line of XML that is over 2Mb long, I have set the buffer to 10Mb and it decodes without error. (Took me a long time to find the cause of this error!)

There are reports about failed xcodebuid runs when attempting to install CoreXLSX with Carthage. This needs to be verified and fixed.

Hi, I am trying to build this framework using Xcode 11 and have cloned the repository but the build fails with an error - No such module 'XMLCode', however in the Dependencies folder there appear to be all the sources for XMLCode 0.1.0.

Can someone help with steps to get this compiling under Xcode 11.

BTW I am not using cocoa pods or Package Manager and assume it is possible to simply clone the repositories and add the two dependent frameworks.

Thanks
Duncan

I'm getting almost the error

Error 2: dataCorrupted(Swift.DecodingError.Context(codingPath: [CodingKeys(stringValue: "relationship", intValue: nil), XMLKey(stringValue: "2", intValue: 2), CodingKeys(stringValue: "type", intValue: nil)], debugDescription: "Cannot initialize SchemaType from invalid String value http://schemas.openxmlformats.org/officeDocument/2006/relationships/connections", underlyingError: nil))
I tried to put case connections = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/connections"
but didn't work