The excel file is coming from windows ms excel.

panic: runtime error: index out of range

goroutine 24 [running]:
runtime.panic(0x5185c0, 0x8612dc)
    /usr/local/Cellar/go/1.3/libexec/src/pkg/runtime/panic.c:279 +0xf5
github.com/tealeg/xlsx.getValueFromCellData(0xc20a941638, 0x4, 0xa, 0xc20a941658, 0x1, 0xc20a941680, 0x3, 0xc208dc5fc0, 0x0, 0x0)
    /Users/xxxx/src/github.com/tealeg/xlsx/lib.go:324 +0x1c0
github.com/tealeg/xlsx.readRowsFromSheet(0xc2088a2930, 0xc20875fcc0, 0x0, 0x0, 0x0, 0x4, 0xc208707860)
    /Users/xxxx/src/github.com/tealeg/xlsx/lib.go:387 +0x530
github.com/tealeg/xlsx.readSheetFromFile(0xc209d6b080, 0x0, 0xc209e562d0, 0x7, 0xc209e562e0, 0x1, 0xc209e562f0, 0x4, 0xc20875fcc0, 0xc2087079e0)
    /Users/xxxx/src/github.com/tealeg/xlsx/lib.go:421 +0x12f
created by github.com/tealeg/xlsx.readSheetsFromZipFile
    /Users/xxxx/src/github.com/tealeg/xlsx/lib.go:451 +0x3a1

Sorry,I can not public that excel file,and I do not have windows ms excel. Other excel software(mac numbers, windows wps) will not product that kind of xlsx file.

change this function it will pass this xlsx file,but will fail some other tests.
https://github.com/tealeg/xlsx/blob/master/sharedstrings.go#L91

func (rt *RefTable) AddString(str string) int {
    rt.indexedStrings = append(rt.indexedStrings, str)
    index, ok := rt.knownStrings[str]
    if ok {
        return index
    }
    index = len(rt.indexedStrings) - 1
    rt.knownStrings[str] = index
    return index
}

Having already created a complete row of blank cells, readRowsFromSheet then creates a new blank cell for each new value it reads from the raw spreadsheet and copies that back to the array. This is silly and wasteful.

I have a web application, and there is a panic/recover middleware to handle any type of un-handled panic, but here in this line :

Why this part is in a go routine??

Do not see how to read and write the merged cell.Such as：

at (https://github.com/SheetJS/js-xlsx)

ws['!merges']: array of range objects corresponding to the merged cells in the worksheet. Plaintext utilities are unaware of merge cells. CSV export will write all cells in the merge range if they exist, so be sure that only the first cell (upper-left) in the range is set.

but not found read and write the merged cell at this project.

When opening an Excel file (one date cell filled in, otherwise empty) the following error occurs.

panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x68 pc=0x4807f8]

goroutine 1 [running]:
runtime.panic(0x504e60, 0x6694cf)
C:/Go/src/pkg/runtime/panic.c:266 +0xc8
archive/zip.(_File).findBodyOffset(0x0, 0x43c343, 0xc0840045a0, 0xb)
C:/Go/src/pkg/archive/zip/reader.go:196 +0x48
archive/zip.(_File).Open(0x0, 0x0, 0x0, 0x0, 0x0)
C:/Go/src/pkg/archive/zip/reader.go:133 +0x65
github.com/tealeg/xlsx.readSharedStringsFromZipFile(0x0, 0xc08404f3f0, 0x0, 0x0,
0xffffffffffffff00, ...)
C:/Go Programs/src/github.com/tealeg/xlsx/lib.go:632 +0x2a
github.com/tealeg/xlsx.ReadZipReader(0xc0840061c8, 0xc08401b2a0, 0x0, 0x0)
C:/Go Programs/src/github.com/tealeg/xlsx/lib.go:765 +0x22f
github.com/tealeg/xlsx.ReadZip(0xc0840061c0, 0x0, 0x0, 0x0)
C:/Go Programs/src/github.com/tealeg/xlsx/lib.go:721 +0x6d
github.com/tealeg/xlsx.OpenFile(0x533090, 0x9, 0x4db300, 0xc084028ea0, 0x3c2f20)

The error occurs when reading xl/sharedStrings.xml. This Spreadsheet does not contain an xl/sharedStrings.xml and the function cannot deal with it. This happens if the spreadsheet does not contain any strings (Thus only numbers and dates).
A work-around for the current code: Add a string in any cell, save the file, remove the string(The sharedStrings.xml persists).
To fix this do a simple check on the file and return an empty reftable(as is wont be used).
Insert the following in lib.go at line 632.

if f == nil {
return make([]string,0),nil
}

When I try open xlsx files and got error. panic error can't be recovered.

Hi, I am looking for a way to iterate to the last filled row in excel I.e.

↑ this is the row I want

In VBA I could do something like this:

For row = 1 To Rows.Count

    If IsEmpty(Cells(row, 1)) Then   'checks whether the "Value" row is empty or not, if it's empty, it would exit the function else it would check whether the "ID" row is empty

            If Not IsEmpty(Cells(row, 1)) Then
                   'run code on selected cell
            End If 

    End If

End For

Thanks in advance!

As a maintainer, I'd like tests which create XML with arbitrary values in places where user input (.xlsx files) could have invalid values in otherwise valid XML. This was prompted by #125.

At present, subtle bugs may exist which will go unnoticed until a user has a (probably, but not necessarily) malformed XLSX file which hits an edge case.

If someone with both time and enough knowledge of the XLSX format reads this, please consider contributing. This may allow us to find and fix bugs before our users are bitten by them.

Excel 2013 is prone to give this warning: "We found a problem with some content in [generated file name.xlsx. Do you want us to try to recover as much as we can ?"
But nothing appears to be broken - the generated data had made its way into the file.

I suspect this issue is due to the incomplete style information exported currently.

I'm using the library to parse an XLSX document, every time I try to get the String value of a cell I get the error:

strconv.ParseFloat: parsing "TRK UT EX CAP M1152A1": invalid syntax

Clearly the internal Value is text, and I'm using the .String() method, so I'm not sure why the library is trying to parse the data as a Float.

If I pull the value directly with .Value rather than .String() I get the correct value, so I'm not sure exactly what is happening with the case statement in FormattedValue() that is making the library think that it is a Float rather than text.

Is it because the cell has formatted text?

Thanks,
Andy

I don't know the format behind README.org, but I propose to add a link to http://godoc.org/github.com/tealeg/xlsx near the top.

Works fine with go1.0.3, but not with go1.1

xl, err := xlsx.OpenFile("data_use.xlsx")
if err != nil {
    fmt.Println(err)
    return
}

output:
xml: patternFill>fgColor>indexed chain not valid with attr flag

as the title,i was stoped.

Hi
I try to read a xlsx file which is exceed 10MB, it's stuck.
is there a way to not read blank rows?

Regards

I found there is a public function TimeFromExcelTime , but its parameters are private type.

Could you give a demo code for extract xlsx's time format cell into Go's Time?

If you have an XLSX file that contains a row where not every column contains a value, then the blank fields are completely ommitted from the API. This is problematic because it makes it seem like all values are left packed. The most typical use case is actually that you want to export the structure with this fields intact (treating and Excel worksheet as a table).

Using the program xlsx2csv as an example we can see some input that should result in output like this:

A, B, C, D
1, , 3, 4

Actually appears as:

A, B, C, D
1,3,4

Is there any plan for .xls support ?

If a sheet has formulas, and I change the values of cells that these formulas use, and then print values of the cells that these formulas set, I get the values that were there before the source cells were changed.

I realize that formula recalculation is not implemented, but my question is whether this is possible? Do you know how to do it? Maybe I can add this feature...

The file has some pictures and how to deal with them?

I got a panic erro when run the example.
xl, err := xlsx.OpenFile("data_use.xlsx")
if err != nil {
fmt.Println(err)
return
}
(windows 7, go 1.1.2)

When reading xlsx file like the following CSV that has two empty rows. xlsx module returns just two rows that have values (sheet.Cell(0, 0).Value == "A3").

"", ""
"", ""
"A3", "B3"
"A4", "B4"

readRowsFromSheet(https://github.com/tealeg/xlsx/blob/master/lib.go#L349) function returns just line 3 and 4 (omit empty leading rows). And I can't get any information how many rows were omitted from result of OpenFile(). It is not good for handling cell range string (like A2:C5).

I think there are two options:

1. Add empty rows and cells into xlsx.Sheet.Rows to keep Cell(0, 0) always points A1 cell.
2. Add MinRow, MinCol property to xlsx.Sheet to enable to handle omitted information from client code.

I think no.1 is an easy solution to understand the behaviour of xlsx module but it will break backward compatibility.

If you decide the which is better, I will create pull request ;)

As reported here:http://godashboard.appspot.com/package

Using the following file with version d6607c5 I get a broken Excel file. How can I write multiple rows?

package main

import (
    "fmt"
    "github.com/tealeg/xlsx"
)

func main() {
    var file *xlsx.File
    var sheet *xlsx.Sheet
    var row *xlsx.Row
    var cell *xlsx.Cell
    var err error

    file = xlsx.NewFile()
    sheet = file.AddSheet("Sheet1")
    row = sheet.AddRow()
    cell = row.AddCell()
    cell.Value = "I am a cell!"

    row = sheet.AddRow()
    cell = row.AddCell()
    cell.Value = "I am a cell!"

    err = file.Save("MyXLSXFile.xlsx")

    if err != nil {
        fmt.Printf(err.Error())
    }
}

I created an xlsx file with the example code in readme.org.

I can open it with MS excel, but Mac Numbers can't open it.

Does this project not support compatibility with Mac Numbers?

the cell content is "[1],[12,"DATE NOT NULL DEFAULT '0000-00-00'"]"
when I call cell.String()
I got this error
strconv.ParseFloat: parsing "[1],[12,"DATE NOT NULL DEFAULT '0000-00-00'"]":

in getBuiltinNumberFormat()
I found the numFmtId is 49 and it return "@"

func (c *Cell) Bool() bool { just returns c.Value == "1".

Given that there is a CellType CellTypeBool, should it check that and use c.Value == "1" if so, and otherwise return false for "" (empty string) and true for anything else?

Hello,

A big xlsx file that previously was loaded ok, after update of xlsx, is failing with this error.
Was any change made to code to limit the size of loadable files ?

Ricardo.

When I test from my excel file, I found the weird problem. The value is empty but when I see using libreoffice or MS excel there is not empty.
Here my sample file

This is my sample code to test those file.

package main

import (
    "log"
    "github.com/tealeg/xlsx"
    "os"
)

func main() {
    excelFileName := os.Getenv("TEST_XLSX") 
    xlFile, err := xlsx.OpenFile(excelFileName)

    if err != nil {
        log.Println(err)
        return
    }
    for _, sheet := range xlFile.Sheets {
        log.Println(sheet.Cell(0,4).Value)
    }
}

Here the result and there is an empty value.

2015/01/09 14:29:57 SP
2015/01/09 14:29:57 SPIE
2015/01/09 14:29:57 EPIE
2015/01/09 14:29:57 PPIE
2015/01/09 14:29:57 MSET
2015/01/09 14:29:57 TMU
2015/01/09 14:29:57 TS
2015/01/09 14:29:57 ETT
2015/01/09 14:29:57 VM
2015/01/09 14:29:57 TP
2015/01/09 14:29:57 PC
2015/01/09 14:29:57 HS
2015/01/09 14:29:57 ELB
2015/01/09 14:29:57 TMS
2015/01/09 14:29:57 
2015/01/09 14:29:57 LC
2015/01/09 14:29:57 ETM
2015/01/09 14:29:57 ETR
2015/01/09 14:29:57 ICM
2015/01/09 14:29:57 

hi,
im on windows x32. is the following issue expected?

go get github.com/tealeg/xlsx

github.com/tealeg/xlsx

C:\gopath\src\github.com\tealeg\xlsx\lib.go:217: constant 9223372036854775807 ov
erflows int
C:\gopath\src\github.com\tealeg\xlsx\lib.go:218: constant 9223372036854775807 ov
erflows int
C:\gopath\src\github.com\tealeg\xlsx\lib.go:241: constant 9223372036854775807 ov
erflows int
C:\gopath\src\github.com\tealeg\xlsx\lib.go:242: constant 9223372036854775807 ov
erflows int

i think you assume everybody is on x64:
minx = math.MaxInt64

Did a go get -u to refresh, now the Writing functionality doesn't seem to work anymore - I'm using the sample program provided - workbook is created by is unreadable:

"Excel could not open MyXLSXFile.xlsx because some content is unreadable Do you want to open and repair this workbook?"

I'm on a Mac, Excel is 2013. This worked back in December, so something has changed - I'll start grabbing earlier commits and report back if I find one that works vs broken.

Hi, I'm using the XLSX style, e.g Fill style in a Cell, I set cell.SetStyle(style) where style is fill := NewFill("solid", "#12CDD3", "#FFFFFF"); style.Fill = *fill, but it does not show any fill color in the generated XLSX file, Am I using the style the right way, what I was missing here? I set ApplyFill to true by the way. ;-) Thanks

The function names in style.go:

DefaulFont()
DefaulBorder()
DefaulFill()

seem to be missing a character 't' in the word 'Default'. This was confusing for me when programming using the API and I had to double-check.

** Edit: whelp it looks like style support is disabled for now, but this might be nice to have down the road :p

This is a feature request and I'm too new at Go to really be of use. Would love to be able to read (and even write to) a named cell/range. Python's xlrd (for reading excel files) has support for named ranges and you could probably take a look at the code there to get started.

• Add XlsxFile2Array done #50
• Add TitleArrayToGrid wip(wait #50 to be merged.)

XlsxFile2Array

Read all data as string from a xlsx file,and return all data.

TitleArrayToGrid

Transform data from [][]string with title as first row,remain row as data to a []map[string]string represent.
As you see,this function is base on XlsxFile2Array.
.

Those helper functions can simplify your code greatly, I write it ,so you do not need to write it again.

kmgExcel:
https://github.com/bronze1man/kmg/tree/master/encoding/kmgExcel
http://godoc.org/github.com/bronze1man/kmg/encoding/kmgExcel

I put here as csv file, you could convert it to xlsx.

I expect got last elemen '7', but the decoder can not correct decode it.

code,No,Grp,Name,Ty,Width,Tag,Req,Var,PostFn,WidthFn

C05,1,0,a,X,7,303,R,,,
C05,2,0,b,X,7,,,,,

When opening XLSX file generated by this library in most recent Microsoft Excel version (2013), all columns are "hidden". In fact they're not hidden, but their width is 0. Can I add a style to column / cell to have different width?

Loading issue17.xlsx using the following code fails with the traceback below.

package main

import (
"fmt"
"github.com/tealeg/xlsx"
"log"
)

func main() {
excelFileName := "foo.xlsx"
xlFile, err := xlsx.OpenFile(excelFileName)
if err != nil {
log.Fatal(err)
}
for _, sheet := range xlFile.Sheets {
for _, row := range sheet.Rows {
for _, cell := range row.Cells {
fmt.Printf("%s\n", cell.String())
}
}
}

}

And this is the error (while loading the data)

$ go run main.go
panic: runtime error: index out of range

goroutine 3 [running]:
github.com/tealeg/xlsx.readRowsFromSheet(0x21024e930, 0x21024b1e0, 0x2102b8010, 0x1, 0x2102b8020, ...)
/Users/patrick/prog/go/xlsx/xlsx/src/github.com/tealeg/xlsx/lib.go:340 +0x451
github.com/tealeg/xlsx.readSheetFromFile(0x2102b7120, 0x0, 0x2102b8000, 0x6, 0x2102b8010, ...)
/Users/patrick/prog/go/xlsx/xlsx/src/github.com/tealeg/xlsx/lib.go:364 +0xfb
created by github.com/tealeg/xlsx.readSheetsFromZipFile
/Users/patrick/prog/go/xlsx/xlsx/src/github.com/tealeg/xlsx/lib.go:393 +0x2f4

goroutine 1 [chan receive]:
github.com/tealeg/xlsx.readSheetsFromZipFile(0x210277240, 0x21024b1e0, 0x0, 0x0, 0x0, ...)
/Users/patrick/prog/go/xlsx/xlsx/src/github.com/tealeg/xlsx/lib.go:396 +0x347
github.com/tealeg/xlsx.ReadZip(0x210247300, 0x8, 0x210247300, 0x0)
/Users/patrick/prog/go/xlsx/xlsx/src/github.com/tealeg/xlsx/lib.go:506 +0x2fe
github.com/tealeg/xlsx.OpenFile(0x103d70, 0x8, 0x10, 0x0, 0x1, ...)
/Users/patrick/prog/go/xlsx/xlsx/src/github.com/tealeg/xlsx/lib.go:458 +0x65
main.main()
/Users/patrick/prog/go/xlsx/xlsx/main.go:11 +0x3c

exit status 2

I write a date cell "2012/11/8" in Excel.
And use this lib cell.String() to read it and shows 41221.

I am unable to open the file neither when I escape the file path nor when I do not escape it. Do I provide the absolute path or is it relative to the application? Thanks in advance!

@tealeg I can offer help with this if you want, but it's pretty easy to set up and would be a nice integration I think: http://docs.travis-ci.com/user/languages/go/

How to use the xlsx‘s style? Where there is examples

Here is my function, you can check it on Excel/Libreoffice's "=COLUMN(AMI1)"

func lettersToNumeric(letters string) int {
sum, mul, n := 0, 1, 0
for i := len(letters) - 1; i >= 0; i, mul, n = i-1, mul*26, 1 {
c := letters[i]
switch {
case 'A' <= c && c <= 'Z':
n += int(c - 'A')
case 'a' <= c && c <= 'z':
n += int(c - 'a')
}
sum += n * mul
}
return sum
}

func TestLettersToNumeric(t _testing.T) {
cases := map[string]int{"A": 0, "G": 6, "z": 25, "AA": 26, "Az": 51,
"BA": 52, "Bz": 77, "ZA": 26_26 + 0, "ZZ": 26_26 + 25,
"AAA": 26_26 + 26 + 0, "AMI": 1022}
for input, ans := range cases {
output := lettersToNumeric(input)
if output != ans {
t.Error("Expected output '"+input+"' == ", ans,
"but got ", strconv.Itoa(output))
}
}
}

I cannot get the name of a sheet. Here is the code I'm using:

excelFileName := "/home/julian/Desktop/googleDocsTest.xlsx"
excelFile, error := xlsx.OpenFile(excelFileName);

if excelFile == nil {

    fmt.Println("error: ", error)
}

return excelFile

excelFile := readExcel()

for index, sheet := range excelFile.Sheets {

    fmt.Println("index: ", index)
    fmt.Println("sheet: ", sheet.Name)
}

}


The console output is:
index: 0
sheet:

All others Sheet fields (Rows, MaxRow and MaxCol) have the expected values.

I have just finish this job in my project.
The interface to caller just like csv,but msexcel can open it with chinese in it with utf-8.
It should be easy to paste and copy code into this project.
The xlsx file which generated can be opend by wps and msexcel without any warning.
MacNumber can not open it right now.
I do not understand or read xlsx file format specification,it is too complex for my need.I just unzip wps's xlsx file, copy some content into my code,dynamically generated some content with xml,and zip them into a xlsx file.

https://github.com/bronze1man/kmg/blob/master/encoding/kmgExcel/XlsxWriter.go#L13
http://godoc.org/github.com/bronze1man/kmg/encoding/kmgExcel#Array2XlsxFile

This function is looking a bit hairy now. I should try an break it down a little. In doing so maybe strengthen the tests a little.

About lib.go#calculateMaxMinFromWorksheet
https://github.com/tealeg/xlsx/blob/master/lib.go#L233
I guess

            if y < miny {
                miny = y
            }

is never reached.
Because the variable miny is 0 at first. The variable y from getCoordsFromCellIDString function.
The getCoordsFromCellIDString function always return more than equal 0. The getCoordsFromCellIDString function looks return 0 and more, always.
https://github.com/tealeg/xlsx/blob/master/lib.go#L197

I have the following errors while executing tests on master branch :

FAIL: date_test.go:39: DateSuite.TestTimeFromExcelTime

date_test.go:47:
c.Assert(date, Equals, time.Date(2013, 1, 1, 0, 0, 0, 0, time.Local))
... obtained time.Time = time.Time{sec:63492595200, nsec:0x0, loc:(_time.Location)(0x7b50c0)} ("2013-01-01 01:00:00 +0100 CET")
... expected time.Time = time.Time{sec:63492591600, nsec:0x0, loc:(_time.Location)(0x7b50c0)} ("2013-01-01 00:00:00 +0100 CET")

FAIL: date_test.go:67: DateSuite.TestTimeFromExcelTimeWith1904Offest

date_test.go:69:
c.Assert(date1904Offset, Equals, time.Date(2013, 1, 1, 0, 0, 0, 0, time.Local))
... obtained time.Time = time.Time{sec:63492595200, nsec:0x0, loc:(_time.Location)(0x7b50c0)} ("2013-01-01 01:00:00 +0100 CET")
... expected time.Time = time.Time{sec:63492591600, nsec:0x0, loc:(_time.Location)(0x7b50c0)} ("2013-01-01 00:00:00 +0100 CET")

FAIL: date_test.go:50: DateSuite.TestTimeFromExcelTimeWithFractionalPart

date_test.go:61:
c.Assert(date.Round(time.Second), Equals, time.Date(2003, 11, 22, 18, 0, 0, 0, time.Local))
... obtained time.Time = time.Time{sec:63205120800, nsec:0x0, loc:(_time.Location)(0x7b50c0)} ("2003-11-22 19:00:00 +0100 CET")
... expected time.Time = time.Time{sec:63205117200, nsec:0x0, loc:(_time.Location)(0x7b50c0)} ("2003-11-22 18:00:00 +0100 CET")

FAIL: lib_test.go:83: LibSuite.TestFormattedValue

lib_test.go:163:
c.Assert(cell.FormattedValue(), Equals, "6:00 pm")
... obtained string = "7:00 pm"
... expected string = "6:00 pm"

--- FAIL: Test (0.14 seconds)
FAIL
OOPS: 36 passed, 4 FAILED
exit status 1
FAIL github.com/tealeg/xlsx 0.387s

excel内的时间导入mysql数据库都是0001-01-01 00:00:00

func TestCopy(fp string) error {
xlFile, err := xlsx.OpenFile(fp)
if err != nil {
return fmt.Errorf("open src file failure:%v", err)
}
destfp := filepath.Join("/home/dev/upload/reports/", guid.NewGUID()+".xlsx")
if err := xlFile.Save(destfp); err != nil {
return fmt.Errorf("save to dest file failure:%v", err)
}
rfp := destfp
fmt.Println("new gened file", rfp)
return nil
}

panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xb code=0x1 addr=0x70 pc=0x5e1cf9]

goroutine 1 [running]:
github.com/tealeg/xlsx.(_Style).makeXLSXStyleElements(0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
/home/dev/projects/src/github.com/tealeg/xlsx/style.go:28 +0x149
github.com/tealeg/xlsx.(_Sheet).makeXLSXSheet(0xc208042300, 0xc20803b440, 0xc208000a20, 0x0)
/home/dev/projects/src/github.com/tealeg/xlsx/sheet.go:76 +0x198
github.com/tealeg/xlsx.(_File).MarshallParts(0xc2080e00c0, 0xa68658, 0x0, 0x0)
/home/dev/projects/src/github.com/tealeg/xlsx/file.go:177 +0x3ae
github.com/tealeg/xlsx.(_File).Write(0xc2080e00c0, 0x7f4e5a4b6ba0, 0xc208038018, 0x0, 0x0)
/home/dev/projects/src/github.com/tealeg/xlsx/file.go:87 +0x4b
github.com/tealeg/xlsx.(*File).Save(0xc2080e00c0, 0xc208042540, 0x51, 0x0, 0x0)
/home/dev/projects/src/github.com/tealeg/xlsx/file.go:74 +0xa7
yhCRM/controllers.MakeRpt(0xa2c590, 0x47, 0x0, 0x0)
/home/dev/projects/src/yhCRM/controllers/xlsx.go:99 +0x29b
main.main()
/home/dev/.local/share/data/liteide/goplay.go:22 +0x3ab
exit status 2