package xlsx
import (
"bytes"
"encoding/xml"
"os"
"strings"
. "gopkg.in/check.v1"
)
type LibSuite struct{}
var _ = Suite(&LibSuite{})
// Attempting to open a file without workbook.xml.rels returns an error.
func (l *LibSuite) TestReadZipReaderWithFileWithNoWorkbookRels(c *C) {
_, err := OpenFile("./testdocs/badfile_noWorkbookRels.xlsx")
c.Assert(err, NotNil)
c.Assert(err.Error(), Equals, "xl/_rels/workbook.xml.rels not found in input xlsx.")
}
// Attempting to open a file with no worksheets returns an error.
func (l *LibSuite) TestReadZipReaderWithFileWithNoWorksheets(c *C) {
_, err := OpenFile("./testdocs/badfile_noWorksheets.xlsx")
c.Assert(err, NotNil)
c.Assert(err.Error(), Equals, "Input xlsx contains no worksheets.")
}
// Attempt to read data from a file with inlined string sheet data.
func (l *LibSuite) TestReadWithInlineStrings(c *C) {
var xlsxFile *File
var err error
xlsxFile, err = OpenFile("./testdocs/inlineStrings.xlsx")
c.Assert(err, IsNil)
sheet := xlsxFile.Sheets[0]
r1 := sheet.Rows[0]
c1 := r1.Cells[1]
val, err := c1.FormattedValue()
if err != nil {
c.Error(err)
return
}
if val == "" {
c.Error("Expected a string value")
return
}
c.Assert(val, Equals, "HL Retail - North America - Activity by Day - MTD")
}
// which they are contained from the XLSX file, even when the
// worksheet files have arbitrary, non-numeric names.
func (l *LibSuite) TestReadWorkbookRelationsFromZipFileWithFunnyNames(c *C) {
var xlsxFile *File
var err error
xlsxFile, err = OpenFile("./testdocs/testrels.xlsx")
c.Assert(err, IsNil)
bob := xlsxFile.Sheet["Bob"]
row1 := bob.Rows[0]
cell1 := row1.Cells[0]
if val, err := cell1.FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "I am Bob")
}
}
// We can marshal WorkBookRels to an xml file
func (l *LibSuite) TestWorkBookRelsMarshal(c *C) {
var rels WorkBookRels = make(WorkBookRels)
rels["rId1"] = "worksheets/sheet.xml"
expectedXML := `
`
xRels := rels.MakeXLSXWorkbookRels()
output := bytes.NewBufferString(xml.Header)
body, err := xml.Marshal(xRels)
c.Assert(err, IsNil)
c.Assert(body, NotNil)
_, err = output.Write(body)
c.Assert(err, IsNil)
c.Assert(output.String(), Equals, expectedXML)
}
// Excel column codes are a special form of base26 that doesn't allow
// zeros, except in the least significant part of the code. Test we
// can smoosh the numbers in a normal base26 representation (presented
// as a slice of integers) down to this form.
func (l *LibSuite) TestSmooshBase26Slice(c *C) {
input := []int{20, 0, 1}
expected := []int{19, 26, 1}
c.Assert(smooshBase26Slice(input), DeepEquals, expected)
}
// formatColumnName converts slices of base26 integers to alphabetical
// column names. Note that the least signifcant character has a
// different numeric offset (Yuck!)
func (l *LibSuite) TestFormatColumnName(c *C) {
c.Assert(formatColumnName([]int{0}), Equals, "A")
c.Assert(formatColumnName([]int{25}), Equals, "Z")
c.Assert(formatColumnName([]int{1, 25}), Equals, "AZ")
c.Assert(formatColumnName([]int{26, 25}), Equals, "ZZ")
c.Assert(formatColumnName([]int{26, 26, 25}), Equals, "ZZZ")
}
// getLargestDenominator returns the largest power of a provided value
// that can fit within a given value.
func (l *LibSuite) TestGetLargestDenominator(c *C) {
d, p := getLargestDenominator(0, 1, 2, 0)
c.Assert(d, Equals, 1)
c.Assert(p, Equals, 0)
d, p = getLargestDenominator(1, 1, 2, 0)
c.Assert(d, Equals, 1)
c.Assert(p, Equals, 0)
d, p = getLargestDenominator(2, 1, 2, 0)
c.Assert(d, Equals, 2)
c.Assert(p, Equals, 1)
d, p = getLargestDenominator(4, 1, 2, 0)
c.Assert(d, Equals, 4)
c.Assert(p, Equals, 2)
d, p = getLargestDenominator(8, 1, 2, 0)
c.Assert(d, Equals, 8)
c.Assert(p, Equals, 3)
d, p = getLargestDenominator(9, 1, 2, 0)
c.Assert(d, Equals, 8)
c.Assert(p, Equals, 3)
d, p = getLargestDenominator(15, 1, 2, 0)
c.Assert(d, Equals, 8)
c.Assert(p, Equals, 3)
d, p = getLargestDenominator(16, 1, 2, 0)
c.Assert(d, Equals, 16)
c.Assert(p, Equals, 4)
}
func (l *LibSuite) TestLettersToNumeric(c *C) {
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)
c.Assert(output, Equals, ans)
}
}
func (l *LibSuite) TestNumericToLetters(c *C) {
cases := map[string]int{
"A": 0,
"G": 6,
"Z": 25,
"AA": 26,
"AZ": 51,
"BA": 52,
"BZ": 77, "ZA": 26 * 26, "ZB": 26*26 + 1,
"ZZ": 26*26 + 25,
"AAA": 26*26 + 26 + 0, "AMI": 1022}
for ans, input := range cases {
output := numericToLetters(input)
c.Assert(output, Equals, ans)
}
}
func (l *LibSuite) TestLetterOnlyMapFunction(c *C) {
var input string = "ABC123"
var output string = strings.Map(letterOnlyMapF, input)
c.Assert(output, Equals, "ABC")
input = "abc123"
output = strings.Map(letterOnlyMapF, input)
c.Assert(output, Equals, "ABC")
}
func (l *LibSuite) TestIntOnlyMapFunction(c *C) {
var input string = "ABC123"
var output string = strings.Map(intOnlyMapF, input)
c.Assert(output, Equals, "123")
}
func (l *LibSuite) TestGetCoordsFromCellIDString(c *C) {
var cellIDString string = "A3"
var x, y int
var err error
x, y, err = GetCoordsFromCellIDString(cellIDString)
c.Assert(err, IsNil)
c.Assert(x, Equals, 0)
c.Assert(y, Equals, 2)
}
func (l *LibSuite) TestGetCellIDStringFromCoords(c *C) {
c.Assert(GetCellIDStringFromCoords(0, 0), Equals, "A1")
c.Assert(GetCellIDStringFromCoords(2, 2), Equals, "C3")
}
func (l *LibSuite) TestGetMaxMinFromDimensionRef(c *C) {
var dimensionRef string = "A1:B2"
var minx, miny, maxx, maxy int
var err error
minx, miny, maxx, maxy, err = getMaxMinFromDimensionRef(dimensionRef)
c.Assert(err, IsNil)
c.Assert(minx, Equals, 0)
c.Assert(miny, Equals, 0)
c.Assert(maxx, Equals, 1)
c.Assert(maxy, Equals, 1)
}
func (l *LibSuite) TestCalculateMaxMinFromWorksheet(c *C) {
var sheetxml = bytes.NewBufferString(`
0
1
2
3
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
minx, miny, maxx, maxy, err := calculateMaxMinFromWorksheet(worksheet)
c.Assert(err, IsNil)
c.Assert(minx, Equals, 0)
c.Assert(miny, Equals, 0)
c.Assert(maxx, Equals, 1)
c.Assert(maxy, Equals, 1)
}
func (l *LibSuite) TestGetRangeFromString(c *C) {
var rangeString string
var lower, upper int
var err error
rangeString = "1:3"
lower, upper, err = getRangeFromString(rangeString)
c.Assert(err, IsNil)
c.Assert(lower, Equals, 1)
c.Assert(upper, Equals, 3)
}
func (l *LibSuite) TestMakeRowFromSpan(c *C) {
var rangeString string
var row *Row
var length int
var sheet *Sheet
sheet = new(Sheet)
rangeString = "1:3"
row = makeRowFromSpan(rangeString, sheet)
length = len(row.Cells)
c.Assert(length, Equals, 3)
c.Assert(row.Sheet, Equals, sheet)
rangeString = "5:7" // Note - we ignore lower bound!
row = makeRowFromSpan(rangeString, sheet)
length = len(row.Cells)
c.Assert(length, Equals, 7)
c.Assert(row.Sheet, Equals, sheet)
rangeString = "1:1"
row = makeRowFromSpan(rangeString, sheet)
length = len(row.Cells)
c.Assert(length, Equals, 1)
c.Assert(row.Sheet, Equals, sheet)
}
func (l *LibSuite) TestReadRowsFromSheet(c *C) {
var sharedstringsXML = bytes.NewBufferString(`
Foo
Bar
Baz
Quuk
`)
var sheetxml = bytes.NewBufferString(`
0
1
2
3
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, cols, maxCols, maxRows := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxRows, Equals, 2)
c.Assert(maxCols, Equals, 2)
row := rows[0]
c.Assert(row.Sheet, Equals, sheet)
c.Assert(len(row.Cells), Equals, 2)
c.Assert(row.Height, Equals, 123.45)
c.Assert(row.isCustom, Equals, true)
cell1 := row.Cells[0]
c.Assert(cell1.Value, Equals, "Foo")
cell2 := row.Cells[1]
c.Assert(cell2.Value, Equals, "Bar")
col := cols[0]
c.Assert(col.Min, Equals, 0)
c.Assert(col.Max, Equals, 0)
c.Assert(col.Hidden, Equals, false)
c.Assert(len(worksheet.SheetViews.SheetView), Equals, 1)
sheetView := worksheet.SheetViews.SheetView[0]
c.Assert(sheetView.Pane, NotNil)
pane := sheetView.Pane
c.Assert(pane.XSplit, Equals, 0.0)
c.Assert(pane.YSplit, Equals, 1.0)
}
func (l *LibSuite) TestReadRowsFromSheetWithMergeCells(c *C) {
var sharedstringsXML = bytes.NewBufferString(`
Value A
Value B
Value C
`)
var sheetxml = bytes.NewBufferString(`
0
1
2
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, _, _, _ := readRowsFromSheet(worksheet, file, sheet)
row := rows[0] //
cell1 := row.Cells[0]
c.Assert(cell1.HMerge, Equals, 1)
c.Assert(cell1.VMerge, Equals, 0)
}
// An invalid value in the "r" attribute in a was causing a panic
// in readRowsFromSheet. This test is a copy of TestReadRowsFromSheet,
// with the important difference of the value 1048576 below in
Foo
Bar
Baz
Quuk
`)
var sheetxml = bytes.NewBufferString(`
0
1
2
3
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
// Discarding all return values; this test is a regression for
// a panic due to an "index out of range."
readRowsFromSheet(worksheet, file, sheet)
}
func (l *LibSuite) TestReadRowsFromSheetWithLeadingEmptyRows(c *C) {
var sharedstringsXML = bytes.NewBufferString(`
ABCDEF`)
var sheetxml = bytes.NewBufferString(`
0
1
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, _, maxCols, maxRows := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxRows, Equals, 5)
c.Assert(maxCols, Equals, 1)
c.Assert(len(rows[0].Cells), Equals, 0)
c.Assert(len(rows[1].Cells), Equals, 0)
c.Assert(len(rows[2].Cells), Equals, 0)
c.Assert(len(rows[3].Cells), Equals, 1)
if val, err := rows[3].Cells[0].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "ABC")
}
c.Assert(len(rows[4].Cells), Equals, 1)
if val, err := rows[4].Cells[0].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "DEF")
}
}
func (l *LibSuite) TestReadRowsFromSheetWithLeadingEmptyCols(c *C) {
var sharedstringsXML = bytes.NewBufferString(`
ABCDEF`)
var sheetxml = bytes.NewBufferString(`
0
1
0
1
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, cols, maxCols, maxRows := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxRows, Equals, 2)
c.Assert(maxCols, Equals, 4)
c.Assert(len(rows[0].Cells), Equals, 4)
if val, err := rows[0].Cells[0].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "")
}
if val, err := rows[0].Cells[1].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "")
}
if val, err := rows[0].Cells[2].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "ABC")
}
if val, err := rows[0].Cells[3].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "DEF")
}
c.Assert(len(rows[1].Cells), Equals, 4)
if val, err := rows[1].Cells[0].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "")
}
if val, err := rows[1].Cells[1].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "")
}
if val, err := rows[1].Cells[2].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "ABC")
}
if val, err := rows[1].Cells[3].FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "DEF")
}
c.Assert(len(cols), Equals, 4)
c.Assert(cols[0].Width, Equals, 0.0)
c.Assert(cols[1].Width, Equals, 0.0)
c.Assert(cols[2].Width, Equals, 17.0)
c.Assert(cols[3].Width, Equals, 18.0)
}
func (l *LibSuite) TestReadRowsFromSheetWithEmptyCells(c *C) {
var sharedstringsXML = bytes.NewBufferString(`
Bob
Alice
Sue
Yes
No
`)
var sheetxml = bytes.NewBufferString(`
0
1
2
3
4
3
4
3
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, cols, maxCols, maxRows := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxRows, Equals, 3)
c.Assert(maxCols, Equals, 3)
row := rows[2]
c.Assert(row.Sheet, Equals, sheet)
c.Assert(len(row.Cells), Equals, 3)
cell1 := row.Cells[0]
c.Assert(cell1.Value, Equals, "No")
cell2 := row.Cells[1]
c.Assert(cell2.Value, Equals, "")
cell3 := row.Cells[2]
c.Assert(cell3.Value, Equals, "Yes")
col := cols[0]
c.Assert(col.Min, Equals, 0)
c.Assert(col.Max, Equals, 0)
c.Assert(col.Hidden, Equals, false)
}
func (l *LibSuite) TestReadRowsFromSheetWithTrailingEmptyCells(c *C) {
var row *Row
var cell1, cell2, cell3, cell4 *Cell
var sharedstringsXML = bytes.NewBufferString(`
ABCD`)
var sheetxml = bytes.NewBufferString(`
0123
1
1
1
1
1
1
1
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, _, maxCol, maxRow := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxCol, Equals, 4)
c.Assert(maxRow, Equals, 8)
row = rows[0]
c.Assert(row.Sheet, Equals, sheet)
c.Assert(len(row.Cells), Equals, 4)
cell1 = row.Cells[0]
c.Assert(cell1.Value, Equals, "A")
cell2 = row.Cells[1]
c.Assert(cell2.Value, Equals, "B")
cell3 = row.Cells[2]
c.Assert(cell3.Value, Equals, "C")
cell4 = row.Cells[3]
c.Assert(cell4.Value, Equals, "D")
row = rows[1]
c.Assert(row.Sheet, Equals, sheet)
c.Assert(len(row.Cells), Equals, 4)
cell1 = row.Cells[0]
c.Assert(cell1.Value, Equals, "1")
cell2 = row.Cells[1]
c.Assert(cell2.Value, Equals, "")
cell3 = row.Cells[2]
c.Assert(cell3.Value, Equals, "")
cell4 = row.Cells[3]
c.Assert(cell4.Value, Equals, "")
}
func (l *LibSuite) TestReadRowsFromSheetWithMultipleSpans(c *C) {
var sharedstringsXML = bytes.NewBufferString(`
Foo
Bar
Baz
Quuk
`)
var sheetxml = bytes.NewBufferString(`
0
1
0
1
2
3
2
3
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, _, maxCols, maxRows := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxRows, Equals, 2)
c.Assert(maxCols, Equals, 4)
row := rows[0]
c.Assert(row.Sheet, Equals, sheet)
c.Assert(len(row.Cells), Equals, 4)
cell1 := row.Cells[0]
c.Assert(cell1.Value, Equals, "Foo")
cell2 := row.Cells[1]
c.Assert(cell2.Value, Equals, "Bar")
cell3 := row.Cells[2]
c.Assert(cell3.Value, Equals, "Foo")
cell4 := row.Cells[3]
c.Assert(cell4.Value, Equals, "Bar")
}
func (l *LibSuite) TestReadRowsFromSheetWithMultipleTypes(c *C) {
var sharedstringsXML = bytes.NewBufferString(`
Hello World
`)
var sheetxml = bytes.NewBufferString(`
0
12345
1.024
1
10+20
30
10/0
#DIV/0!
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, _, maxCols, maxRows := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxRows, Equals, 1)
c.Assert(maxCols, Equals, 6)
row := rows[0]
c.Assert(row.Sheet, Equals, sheet)
c.Assert(len(row.Cells), Equals, 6)
cell1 := row.Cells[0]
c.Assert(cell1.Type(), Equals, CellTypeString)
if val, err := cell1.FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "Hello World")
}
cell2 := row.Cells[1]
c.Assert(cell2.Type(), Equals, CellTypeNumeric)
intValue, _ := cell2.Int()
c.Assert(intValue, Equals, 12345)
cell3 := row.Cells[2]
c.Assert(cell3.Type(), Equals, CellTypeNumeric)
float, _ := cell3.Float()
c.Assert(float, Equals, 1.024)
cell4 := row.Cells[3]
c.Assert(cell4.Type(), Equals, CellTypeBool)
c.Assert(cell4.Bool(), Equals, true)
cell5 := row.Cells[4]
c.Assert(cell5.Type(), Equals, CellTypeFormula)
c.Assert(cell5.Formula(), Equals, "10+20")
c.Assert(cell5.Value, Equals, "30")
cell6 := row.Cells[5]
c.Assert(cell6.Type(), Equals, CellTypeError)
c.Assert(cell6.Formula(), Equals, "10/0")
c.Assert(cell6.Value, Equals, "#DIV/0!")
}
func (l *LibSuite) TestReadRowsFromSheetWithHiddenColumn(c *C) {
var sharedstringsXML = bytes.NewBufferString(`
This is a test.
This should be invisible.
`)
var sheetxml = bytes.NewBufferString(`
0
1
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
sst := new(xlsxSST)
err = xml.NewDecoder(sharedstringsXML).Decode(sst)
c.Assert(err, IsNil)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, _, maxCols, maxRows := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxRows, Equals, 1)
c.Assert(maxCols, Equals, 2)
row := rows[0]
c.Assert(row.Sheet, Equals, sheet)
c.Assert(len(row.Cells), Equals, 2)
cell1 := row.Cells[0]
c.Assert(cell1.Type(), Equals, CellTypeString)
if val, err := cell1.FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "This is a test.")
}
c.Assert(cell1.Hidden, Equals, false)
cell2 := row.Cells[1]
c.Assert(cell2.Type(), Equals, CellTypeString)
if val, err := cell2.FormattedValue(); err != nil {
c.Error(err)
} else {
c.Assert(val, Equals, "This should be invisible.")
}
c.Assert(cell2.Hidden, Equals, true)
}
// When converting the xlsxRow to a Row we create a as many cells as we find.
func (l *LibSuite) TestReadRowFromRaw(c *C) {
var rawRow xlsxRow
var cell xlsxC
var row *Row
rawRow = xlsxRow{}
cell = xlsxC{R: "A1"}
cell = xlsxC{R: "A2"}
rawRow.C = append(rawRow.C, cell)
sheet := new(Sheet)
row = makeRowFromRaw(rawRow, sheet)
c.Assert(row, NotNil)
c.Assert(row.Cells, HasLen, 1)
c.Assert(row.Sheet, Equals, sheet)
}
// When a cell claims it is at a position greater than its ordinal
// position in the file we make up the missing cells.
func (l *LibSuite) TestReadRowFromRawWithMissingCells(c *C) {
var rawRow xlsxRow
var cell xlsxC
var row *Row
rawRow = xlsxRow{}
cell = xlsxC{R: "A1"}
rawRow.C = append(rawRow.C, cell)
cell = xlsxC{R: "E1"}
rawRow.C = append(rawRow.C, cell)
sheet := new(Sheet)
row = makeRowFromRaw(rawRow, sheet)
c.Assert(row, NotNil)
c.Assert(row.Cells, HasLen, 5)
c.Assert(row.Sheet, Equals, sheet)
}
// We can cope with missing coordinate references
func (l *LibSuite) TestReadRowFromRawWithPartialCoordinates(c *C) {
var rawRow xlsxRow
var cell xlsxC
var row *Row
rawRow = xlsxRow{}
cell = xlsxC{R: "A1"}
rawRow.C = append(rawRow.C, cell)
cell = xlsxC{}
rawRow.C = append(rawRow.C, cell)
cell = xlsxC{R: "Z:1"}
rawRow.C = append(rawRow.C, cell)
cell = xlsxC{}
rawRow.C = append(rawRow.C, cell)
sheet := new(Sheet)
row = makeRowFromRaw(rawRow, sheet)
c.Assert(row, NotNil)
c.Assert(row.Cells, HasLen, 27)
c.Assert(row.Sheet, Equals, sheet)
}
func (l *LibSuite) TestSharedFormulas(c *C) {
var sheetxml = bytes.NewBufferString(`
1
2
3
2
2*A1
4
6
`)
worksheet := new(xlsxWorksheet)
err := xml.NewDecoder(sheetxml).Decode(worksheet)
c.Assert(err, IsNil)
file := new(File)
sheet := new(Sheet)
rows, _, maxCols, maxRows := readRowsFromSheet(worksheet, file, sheet)
c.Assert(maxCols, Equals, 3)
c.Assert(maxRows, Equals, 2)
row := rows[1]
c.Assert(row.Cells[1].Formula(), Equals, "2*B1")
c.Assert(row.Cells[2].Formula(), Equals, "2*C1")
}
// Test shared formulas that have absolute references ($) in them
func (l *LibSuite) TestSharedFormulasWithAbsoluteReferences(c *C) {
formulas := []string{
"A1",
"$A1",
"A$1",
"$A$1",
"A1+B1",
"$A1+B1",
"$A$1+B1",
"A1+$B1",
"A1+B$1",
"A1+$B$1",
"$A$1+$B$1",
`IF(C23>=E$12,"Q4",IF(C23>=$D$12,"Q3",IF(C23>=C$12,"Q2","Q1")))`,
`SUM(D44:H44)*IM_A_DEFINED_NAME`,
`IM_A_DEFINED_NAME+SUM(D44:H44)*IM_A_DEFINED_NAME_ALSO`,
`SUM(D44:H44)*IM_A_DEFINED_NAME+A1`,
"AA1",
"$AA1",
"AA$1",
"$AA$1",
}
expected := []string{
"B2",
"$A2",
"B$1",
"$A$1",
"B2+C2",
"$A2+C2",
"$A$1+C2",
"B2+$B2",
"B2+C$1",
"B2+$B$1",
"$A$1+$B$1",
`IF(D24>=F$12,"Q4",IF(D24>=$D$12,"Q3",IF(D24>=D$12,"Q2","Q1")))`,
`SUM(E45:I45)*IM_A_DEFINED_NAME`,
`IM_A_DEFINED_NAME+SUM(E45:I45)*IM_A_DEFINED_NAME_ALSO`,
`SUM(E45:I45)*IM_A_DEFINED_NAME+B2`,
"AB2",
"$AA2",
"AB$1",
"$AA$1",
}
anchorCell := "C4"
sharedFormulas := map[int]sharedFormula{}
x, y, _ := GetCoordsFromCellIDString(anchorCell)
for i, formula := range formulas {
res := formula
sharedFormulas[i] = sharedFormula{x, y, res}
}
for i, formula := range formulas {
testCell := xlsxC{
R: "D5",
F: &xlsxF{
Content: formula,
T: "shared",
Si: i,
},
}
c.Assert(formulaForCell(testCell, sharedFormulas), Equals, expected[i])
}
}
// Avoid panic when cell.F.T is "e" (for error)
func (l *LibSuite) TestFormulaForCellPanic(c *C) {
cell := xlsxC{R: "A1"}
// This line would panic before the fix.
sharedFormulas := make(map[int]sharedFormula)
// Not really an important test; getting here without a
// panic is the real win.
c.Assert(formulaForCell(cell, sharedFormulas), Equals, "")
}
func (l *LibSuite) TestRowNotOverwrittenWhenFollowedByEmptyRow(c *C) {
sheetXML := bytes.NewBufferString(`
RANDBETWEEN(1,100)
66
RANDBETWEEN(1,100)
30
RANDBETWEEN(1,100)
75
A4/A2
1.14
`)
sharedstringsXML := bytes.NewBufferString(``)
worksheet := new(xlsxWorksheet)
xml.NewDecoder(sheetXML).Decode(worksheet)
sst := new(xlsxSST)
xml.NewDecoder(sharedstringsXML).Decode(sst)
file := new(File)
file.referenceTable = MakeSharedStringRefTable(sst)
sheet := new(Sheet)
rows, _, _, _ := readRowsFromSheet(worksheet, file, sheet)
cells := rows[3].Cells
c.Assert(cells, HasLen, 1)
c.Assert(cells[0].Value, Equals, "75")
}
// This was a specific issue raised by a user.
func (l *LibSuite) TestRoundTripFileWithNoSheetCols(c *C) {
originalXlFile, err := OpenFile("testdocs/original.xlsx")
c.Assert(err, IsNil)
originalXlFile.Save("testdocs/after_write.xlsx")
_, err = OpenFile("testdocs/after_write.xlsx")
c.Assert(err, IsNil)
os.Remove("testdocs/after_write.xlsx")
}