package xlsx import ( "bytes" "encoding/xml" "testing" qt "github.com/frankban/quicktest" . "gopkg.in/check.v1" ) type SheetSuite struct{} var _ = Suite(&SheetSuite{}) // Test we can add a Row to a Sheet func (s *SheetSuite) TestAddRow(c *C) { // Create a file with three rows. var f *File f = NewFile() sheet, _ := f.AddSheet("MySheet") row0 := sheet.AddRow() cell0 := row0.AddCell() cell0.Value = "Row 0" c.Assert(row0, NotNil) row1 := sheet.AddRow() cell1 := row1.AddCell() cell1.Value = "Row 1" row2 := sheet.AddRow() cell2 := row2.AddCell() cell2.Value = "Row 2" // Check the file expected := []string{"Row 0", "Row 1", "Row 2"} c.Assert(len(sheet.Rows), Equals, len(expected)) for i, row := range sheet.Rows { c.Assert(row.Cells[0].Value, Equals, expected[i]) } // Insert a row in the middle row1pt5, err := sheet.AddRowAtIndex(2) c.Assert(err, IsNil) cell1pt5 := row1pt5.AddCell() cell1pt5.Value = "Row 1.5" expected = []string{"Row 0", "Row 1", "Row 1.5", "Row 2"} c.Assert(len(sheet.Rows), Equals, len(expected)) for i, row := range sheet.Rows { c.Assert(row.Cells[0].Value, Equals, expected[i]) } // Insert a row at the beginning rowNewStart, err := sheet.AddRowAtIndex(0) c.Assert(err, IsNil) cellNewStart := rowNewStart.AddCell() cellNewStart.Value = "Row -1" // Insert a row at one index past the end, this is the same as AddRow(). row2pt5, err := sheet.AddRowAtIndex(5) c.Assert(err, IsNil) cell2pt5 := row2pt5.AddCell() cell2pt5.Value = "Row 2.5" expected = []string{"Row -1", "Row 0", "Row 1", "Row 1.5", "Row 2", "Row 2.5"} c.Assert(len(sheet.Rows), Equals, len(expected)) for i, row := range sheet.Rows { c.Assert(row.Cells[0].Value, Equals, expected[i]) } // Negative and out of range indicies should fail for insert _, err = sheet.AddRowAtIndex(-1) c.Assert(err, NotNil) // Since we allow inserting into the position that does not yet exist, it has to be 1 greater // than you would think in order to fail. _, err = sheet.AddRowAtIndex(7) c.Assert(err, NotNil) // Negative and out of range indicies should fail for remove err = sheet.RemoveRowAtIndex(-1) c.Assert(err, NotNil) err = sheet.RemoveRowAtIndex(6) c.Assert(err, NotNil) // Remove from the beginning, the end, and the middle. err = sheet.RemoveRowAtIndex(0) c.Assert(err, IsNil) err = sheet.RemoveRowAtIndex(4) c.Assert(err, IsNil) err = sheet.RemoveRowAtIndex(2) c.Assert(err, IsNil) expected = []string{"Row 0", "Row 1", "Row 2"} c.Assert(len(sheet.Rows), Equals, len(expected)) for i, row := range sheet.Rows { c.Assert(row.Cells[0].Value, Equals, expected[i]) } } // Test we can get row by index from Sheet func (s *SheetSuite) TestGetRowByIndex(c *C) { var f *File f = NewFile() sheet, _ := f.AddSheet("MySheet") row := sheet.Row(10) c.Assert(row, NotNil) c.Assert(len(sheet.Rows), Equals, 11) row = sheet.Row(2) c.Assert(row, NotNil) c.Assert(len(sheet.Rows), Equals, 11) } func (s *SheetSuite) TestMakeXLSXSheetFromRows(c *C) { file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() cell := row.AddCell() cell.Value = "A cell!" refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) xSheet := sheet.makeXLSXSheet(refTable, styles) c.Assert(xSheet.Dimension.Ref, Equals, "A1") c.Assert(xSheet.SheetData.Row, HasLen, 1) xRow := xSheet.SheetData.Row[0] c.Assert(xRow.R, Equals, 1) c.Assert(xRow.Spans, Equals, "") c.Assert(xRow.C, HasLen, 1) xC := xRow.C[0] c.Assert(xC.R, Equals, "A1") c.Assert(xC.S, Equals, 0) c.Assert(xC.T, Equals, "s") // Shared string type c.Assert(xC.V, Equals, "0") // reference to shared string xSST := refTable.makeXLSXSST() c.Assert(xSST.Count, Equals, 1) c.Assert(xSST.UniqueCount, Equals, 1) c.Assert(xSST.SI, HasLen, 1) xSI := xSST.SI[0] c.Assert(xSI.T, Equals, "A cell!") } // Test if the NumFmts assigned properly according the FormatCode in cell. func TestMakeXLSXSheetWithNumFormats(t *testing.T) { c := qt.New(t) file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() cell1 := row.AddCell() cell1.Value = "A cell!" cell1.NumFmt = "general" cell2 := row.AddCell() cell2.Value = "37947.7500001" cell2.NumFmt = "0" cell3 := row.AddCell() cell3.Value = "37947.7500001" cell3.NumFmt = "mm-dd-yy" cell4 := row.AddCell() cell4.Value = "37947.7500001" cell4.NumFmt = "hh:mm:ss" refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) worksheet := sheet.makeXLSXSheet(refTable, styles) c.Assert(styles.CellStyleXfs, qt.IsNil) c.Assert(styles.CellXfs.Count, qt.Equals, 4) c.Assert(styles.CellXfs.Xf[0].NumFmtId, qt.Equals, 0) c.Assert(styles.CellXfs.Xf[1].NumFmtId, qt.Equals, 1) c.Assert(styles.CellXfs.Xf[2].NumFmtId, qt.Equals, 14) c.Assert(styles.CellXfs.Xf[3].NumFmtId, qt.Equals, 164) c.Assert(styles.NumFmts.Count, qt.Equals, 1) c.Assert(styles.NumFmts.NumFmt[0].NumFmtId, qt.Equals, 164) c.Assert(styles.NumFmts.NumFmt[0].FormatCode, qt.Equals, "hh:mm:ss") // Finally we check that the cell points to the right CellXf / // CellStyleXf. c.Assert(worksheet.SheetData.Row[0].C[0].S, qt.Equals, 0) c.Assert(worksheet.SheetData.Row[0].C[1].S, qt.Equals, 1) } // When we create the xlsxSheet we also populate the xlsxStyles struct // with style information. func TestMakeXLSXSheetAlsoPopulatesXLSXSTyles(t *testing.T) { c := qt.New(t) file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() cell1 := row.AddCell() cell1.Value = "A cell!" style1 := NewStyle() style1.Font = *NewFont(10, "Verdana") style1.Fill = *NewFill("solid", "FFFFFFFF", "00000000") style1.Border = *NewBorder("none", "thin", "none", "thin") cell1.SetStyle(style1) // We need a second style to check that Xfs are populated correctly. cell2 := row.AddCell() cell2.Value = "Another cell!" style2 := NewStyle() style2.Font = *NewFont(10, "Verdana") style2.Fill = *NewFill("solid", "FFFFFFFF", "00000000") style2.Border = *NewBorder("none", "thin", "none", "thin") cell2.SetStyle(style2) refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) worksheet := sheet.makeXLSXSheet(refTable, styles) c.Assert(styles.Fonts.Count, qt.Equals, 1) c.Assert(styles.Fonts.Font[0].Sz.Val, qt.Equals, "10") c.Assert(styles.Fonts.Font[0].Name.Val, qt.Equals, "Verdana") c.Assert(styles.Fills.Count, qt.Equals, 2) c.Assert(styles.Fills.Fill[0].PatternFill.PatternType, qt.Equals, "solid") c.Assert(styles.Fills.Fill[0].PatternFill.FgColor.RGB, qt.Equals, "FFFFFFFF") c.Assert(styles.Fills.Fill[0].PatternFill.BgColor.RGB, qt.Equals, "00000000") c.Assert(styles.Borders.Count, qt.Equals, 1) c.Assert(styles.Borders.Border[0].Left.Style, qt.Equals, "none") c.Assert(styles.Borders.Border[0].Right.Style, qt.Equals, "thin") c.Assert(styles.Borders.Border[0].Top.Style, qt.Equals, "none") c.Assert(styles.Borders.Border[0].Bottom.Style, qt.Equals, "thin") c.Assert(styles.CellStyleXfs, qt.IsNil) c.Assert(styles.CellXfs.Count, qt.Equals, 1) c.Assert(styles.CellXfs.Xf[0].FontId, qt.Equals, 0) c.Assert(styles.CellXfs.Xf[0].FillId, qt.Equals, 0) c.Assert(styles.CellXfs.Xf[0].BorderId, qt.Equals, 0) // Finally we check that the cell points to the right CellXf / // CellStyleXf. c.Assert(worksheet.SheetData.Row[0].C[0].S, qt.Equals, 0) c.Assert(worksheet.SheetData.Row[0].C[1].S, qt.Equals, 0) } // If the column width is not customised, the xslxCol.CustomWidth field is set to 0. func (s *SheetSuite) TestMakeXLSXSheetDefaultsCustomColWidth(c *C) { file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() cell1 := row.AddCell() cell1.Value = "A cell!" refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) worksheet := sheet.makeXLSXSheet(refTable, styles) c.Assert(worksheet.Cols, IsNil) } // If the column width is customised, the xslxCol.CustomWidth field is set to 1. func TestMakeXLSXSheetSetsCustomColWidth(t *testing.T) { c := qt.New(t) file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() cell1 := row.AddCell() cell1.Value = "A cell!" sheet.SetColWidth(1, 1, 10.5) refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) worksheet := sheet.makeXLSXSheet(refTable, styles) c.Assert(worksheet.Cols.Col[0].CustomWidth, qt.Equals, true) } func (s *SheetSuite) TestMarshalSheet(c *C) { file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() cell := row.AddCell() cell.Value = "A cell!" refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) xSheet := sheet.makeXLSXSheet(refTable, styles) output := bytes.NewBufferString(xml.Header) body, err := xml.Marshal(xSheet) c.Assert(err, IsNil) c.Assert(body, NotNil) _, err = output.Write(body) c.Assert(err, IsNil) expectedXLSXSheet := ` 0&C&"Times New Roman,Regular"&12&A&C&"Times New Roman,Regular"&12Page &P` c.Assert(output.String(), Equals, expectedXLSXSheet) } func (s *SheetSuite) TestMarshalSheetWithMultipleCells(c *C) { file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() cell := row.AddCell() cell.Value = "A cell (with value 1)!" cell = row.AddCell() cell.Value = "A cell (with value 2)!" refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) xSheet := sheet.makeXLSXSheet(refTable, styles) output := bytes.NewBufferString(xml.Header) body, err := xml.Marshal(xSheet) c.Assert(err, IsNil) c.Assert(body, NotNil) _, err = output.Write(body) c.Assert(err, IsNil) expectedXLSXSheet := ` 01&C&"Times New Roman,Regular"&12&A&C&"Times New Roman,Regular"&12Page &P` c.Assert(output.String(), Equals, expectedXLSXSheet) } func TestSetColWidth(t *testing.T) { c := qt.New(t) file := NewFile() sheet, _ := file.AddSheet("Sheet1") sheet.SetColWidth(1, 1, 10.5) sheet.SetColWidth(2, 6, 11) c.Assert(sheet.Cols.FindColByIndex(1).Width, qt.Equals, 10.5) c.Assert(sheet.Cols.FindColByIndex(1).Max, qt.Equals, 1) c.Assert(sheet.Cols.FindColByIndex(1).Min, qt.Equals, 1) c.Assert(sheet.Cols.FindColByIndex(2).Width, qt.Equals, float64(11)) c.Assert(sheet.Cols.FindColByIndex(2).Max, qt.Equals, 6) c.Assert(sheet.Cols.FindColByIndex(2).Min, qt.Equals, 2) } func TestSetDataValidation(t *testing.T) { c := qt.New(t) file := NewFile() sheet, _ := file.AddSheet("Sheet1") dd := NewDataValidation(0, 0, 10, 0, true) err := dd.SetDropList([]string{"a1", "a2", "a3"}) c.Assert(err, qt.IsNil) sheet.AddDataValidation(dd) c.Assert(sheet.DataValidations, qt.HasLen, 1) c.Assert(sheet.DataValidations[0], qt.Equals, dd) } func (s *SheetSuite) TestSetRowHeightCM(c *C) { file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() row.SetHeightCM(1.5) c.Assert(row.Height, Equals, 42.51968505) } func (s *SheetSuite) TestAlignment(c *C) { leftalign := *DefaultAlignment() leftalign.Horizontal = "left" centerHalign := *DefaultAlignment() centerHalign.Horizontal = "center" rightalign := *DefaultAlignment() rightalign.Horizontal = "right" file := NewFile() sheet, _ := file.AddSheet("Sheet1") style := NewStyle() hrow := sheet.AddRow() // Horizontals cell := hrow.AddCell() cell.Value = "left" style.Alignment = leftalign style.ApplyAlignment = true cell.SetStyle(style) style = NewStyle() cell = hrow.AddCell() cell.Value = "centerH" style.Alignment = centerHalign style.ApplyAlignment = true cell.SetStyle(style) style = NewStyle() cell = hrow.AddCell() cell.Value = "right" style.Alignment = rightalign style.ApplyAlignment = true cell.SetStyle(style) // Verticals topalign := *DefaultAlignment() topalign.Vertical = "top" centerValign := *DefaultAlignment() centerValign.Vertical = "center" bottomalign := *DefaultAlignment() bottomalign.Vertical = "bottom" style = NewStyle() vrow := sheet.AddRow() cell = vrow.AddCell() cell.Value = "top" style.Alignment = topalign style.ApplyAlignment = true cell.SetStyle(style) style = NewStyle() cell = vrow.AddCell() cell.Value = "centerV" style.Alignment = centerValign style.ApplyAlignment = true cell.SetStyle(style) style = NewStyle() cell = vrow.AddCell() cell.Value = "bottom" style.Alignment = bottomalign style.ApplyAlignment = true cell.SetStyle(style) parts, err := file.MarshallParts() c.Assert(err, IsNil) obtained := parts["xl/styles.xml"] shouldbe := ` ` expected := bytes.NewBufferString(shouldbe) c.Assert(obtained, Equals, expected.String()) } func TestBorder(t *testing.T) { c := qt.New(t) file := NewFile() sheet, _ := file.AddSheet("Sheet1") row := sheet.AddRow() cell1 := row.AddCell() cell1.Value = "A cell!" style1 := NewStyle() style1.Border = *NewBorder("thin", "thin", "thin", "thin") style1.ApplyBorder = true cell1.SetStyle(style1) refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) worksheet := sheet.makeXLSXSheet(refTable, styles) c.Assert(styles.Borders.Border[0].Left.Style, qt.Equals, "thin") c.Assert(styles.Borders.Border[0].Right.Style, qt.Equals, "thin") c.Assert(styles.Borders.Border[0].Top.Style, qt.Equals, "thin") c.Assert(styles.Borders.Border[0].Bottom.Style, qt.Equals, "thin") c.Assert(worksheet.SheetData.Row[0].C[0].S, qt.Equals, 0) } func TestOutlineLevels(t *testing.T) { c := qt.New(t) file := NewFile() sheet, _ := file.AddSheet("Sheet1") r1 := sheet.AddRow() c11 := r1.AddCell() c11.Value = "A1" c12 := r1.AddCell() c12.Value = "B1" r2 := sheet.AddRow() c21 := r2.AddCell() c21.Value = "A2" c22 := r2.AddCell() c22.Value = "B2" r3 := sheet.AddRow() c31 := r3.AddCell() c31.Value = "A3" c32 := r3.AddCell() c32.Value = "B3" // Add some groups r1.OutlineLevel = 1 r2.OutlineLevel = 2 sheet.SetOutlineLevel(1, 1, 1) refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) worksheet := sheet.makeXLSXSheet(refTable, styles) c.Assert(worksheet.SheetFormatPr.OutlineLevelCol, qt.Equals, uint8(1)) c.Assert(worksheet.SheetFormatPr.OutlineLevelRow, qt.Equals, uint8(2)) c.Assert(worksheet.Cols.Col[0].OutlineLevel, qt.Equals, uint8(1)) c.Assert(worksheet.SheetData.Row[0].OutlineLevel, qt.Equals, uint8(1)) c.Assert(worksheet.SheetData.Row[1].OutlineLevel, qt.Equals, uint8(2)) c.Assert(worksheet.SheetData.Row[2].OutlineLevel, qt.Equals, uint8(0)) } func (s *SheetSuite) TestAutoFilter(c *C) { file := NewFile() sheet, _ := file.AddSheet("Sheet1") r1 := sheet.AddRow() r1.AddCell() r1.AddCell() r1.AddCell() r2 := sheet.AddRow() r2.AddCell() r2.AddCell() r2.AddCell() r3 := sheet.AddRow() r3.AddCell() r3.AddCell() r3.AddCell() // Define a filter area sheet.AutoFilter = &AutoFilter{TopLeftCell: "B2", BottomRightCell: "C3"} refTable := NewSharedStringRefTable() styles := newXlsxStyleSheet(nil) worksheet := sheet.makeXLSXSheet(refTable, styles) c.Assert(worksheet.AutoFilter, NotNil) c.Assert(worksheet.AutoFilter.Ref, Equals, "B2:C3") }