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(` 01231111111 `) 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") }