package xlsx import ( "errors" "fmt" "strconv" ) // Sheet is a high level structure intended to provide user access to // the contents of a particular sheet within an XLSX file. type Sheet struct { Name string File *File Rows []*Row Cols *ColStore MaxRow int MaxCol int Hidden bool Selected bool SheetViews []SheetView SheetFormat SheetFormat AutoFilter *AutoFilter DataValidations []*xlsxDataValidation } type SheetView struct { Pane *Pane } type Pane struct { XSplit float64 YSplit float64 TopLeftCell string ActivePane string State string // Either "split" or "frozen" } type SheetFormat struct { DefaultColWidth float64 DefaultRowHeight float64 OutlineLevelCol uint8 OutlineLevelRow uint8 } type AutoFilter struct { TopLeftCell string BottomRightCell string } // Add a new Row to a Sheet func (s *Sheet) AddRow() *Row { row := &Row{Sheet: s} s.Rows = append(s.Rows, row) if len(s.Rows) > s.MaxRow { s.MaxRow = len(s.Rows) } return row } // Add a new Row to a Sheet at a specific index func (s *Sheet) AddRowAtIndex(index int) (*Row, error) { if index < 0 || index > len(s.Rows) { return nil, errors.New("AddRowAtIndex: index out of bounds") } row := &Row{Sheet: s} s.Rows = append(s.Rows, nil) if index < len(s.Rows) { copy(s.Rows[index+1:], s.Rows[index:]) } s.Rows[index] = row if len(s.Rows) > s.MaxRow { s.MaxRow = len(s.Rows) } return row, nil } // Add a DataValidation to a range of cells func (s *Sheet) AddDataValidation(dv *xlsxDataValidation) { s.DataValidations = append(s.DataValidations, dv) } // Removes a row at a specific index func (s *Sheet) RemoveRowAtIndex(index int) error { if index < 0 || index >= len(s.Rows) { return errors.New("RemoveRowAtIndex: index out of bounds") } s.Rows = append(s.Rows[:index], s.Rows[index+1:]...) return nil } // Make sure we always have as many Rows as we do cells. func (s *Sheet) maybeAddRow(rowCount int) { if rowCount > s.MaxRow { loopCnt := rowCount - s.MaxRow for i := 0; i < loopCnt; i++ { row := &Row{Sheet: s} s.Rows = append(s.Rows, row) } s.MaxRow = rowCount } } // Make sure we always have as many Rows as we do cells. func (s *Sheet) Row(idx int) *Row { s.maybeAddRow(idx + 1) return s.Rows[idx] } // Return the Col that applies to this Column index, or return nil if no such Col exists func (s *Sheet) Col(idx int) *Col { if s.Cols == nil { panic("trying to use uninitialised ColStore") } return s.Cols.FindColByIndex(idx + 1) } // Get a Cell by passing it's cartesian coordinates (zero based) as // row and column integer indexes. // // For example: // // cell := sheet.Cell(0,0) // // ... would set the variable "cell" to contain a Cell struct // containing the data from the field "A1" on the spreadsheet. func (sh *Sheet) Cell(row, col int) *Cell { // If the user requests a row beyond what we have, then extend. for len(sh.Rows) <= row { sh.AddRow() } r := sh.Rows[row] for len(r.Cells) <= col { r.AddCell() } return r.Cells[col] } //Set the parameters of a column. Parameters are passed as a pointer //to a Col structure which you much construct yourself. func (s *Sheet) SetColParameters(col *Col) { if s.Cols == nil { panic("trying to use uninitialised ColStore") } s.Cols.Add(col) } func (s *Sheet) setCol(min, max int, setter func(col *Col)) { if s.Cols == nil { panic("trying to use uninitialised ColStore") } cols := s.Cols.getOrMakeColsForRange(s.Cols.Root, min, max) for _, col := range cols { switch { case col.Min < min && col.Max > max: // The column completely envelops the range, // so we'll split it into three parts and only // set the width on the part within the range. // The ColStore will do most of this work for // us, we just need to create the new Col // based on the old one. newCol := col.copyToRange(min, max) setter(newCol) s.Cols.Add(newCol) case col.Min < min: // If this column crosses the minimum boundary // of the range we must split it and only // apply the change within the range. Again, // we can lean on the ColStore to deal with // the rest we just need to make the new // Col. newCol := col.copyToRange(min, col.Max) setter(newCol) s.Cols.Add(newCol) case col.Max > max: // Likewise if a col definition crosses the // maximum boundary of the range, it must also // be split newCol := col.copyToRange(col.Min, max) setter(newCol) s.Cols.Add(newCol) default: newCol := col.copyToRange(min, max) setter(newCol) s.Cols.Add(newCol) } } return } // Set the width of a range of columns. func (s *Sheet) SetColWidth(min, max int, width float64) { s.setCol(min, max, func(col *Col) { col.SetWidth(width) }) } // Set the outline level for a range of columns. func (s *Sheet) SetOutlineLevel(minCol, maxCol int, outlineLevel uint8) { s.setCol(minCol, maxCol, func(col *Col) { col.SetOutlineLevel(outlineLevel) }) } // Set the type for a range of columns. func (s *Sheet) SetType(minCol, maxCol int, cellType CellType) { s.setCol(minCol, maxCol, func(col *Col) { col.SetType(cellType) }) } // When merging cells, the cell may be the 'original' or the 'covered'. // First, figure out which cells are merge starting points. Then create // the necessary cells underlying the merge area. // Then go through all the underlying cells and apply the appropriate // border, based on the original cell. func (s *Sheet) handleMerged() { merged := make(map[string]*Cell) for r, row := range s.Rows { for c, cell := range row.Cells { if cell.HMerge > 0 || cell.VMerge > 0 { coord := GetCellIDStringFromCoords(c, r) merged[coord] = cell } } } // This loop iterates over all cells that should be merged and applies the correct // borders to them depending on their position. If any cells required by the merge // are missing, they will be allocated by s.Cell(). for key, cell := range merged { maincol, mainrow, _ := GetCoordsFromCellIDString(key) for rownum := 0; rownum <= cell.VMerge; rownum++ { for colnum := 0; colnum <= cell.HMerge; colnum++ { // make cell s.Cell(mainrow+rownum, maincol+colnum) } } } } func (s *Sheet) makeSheetView(worksheet *xlsxWorksheet) { for index, sheetView := range s.SheetViews { if sheetView.Pane != nil { worksheet.SheetViews.SheetView[index].Pane = &xlsxPane{ XSplit: sheetView.Pane.XSplit, YSplit: sheetView.Pane.YSplit, TopLeftCell: sheetView.Pane.TopLeftCell, ActivePane: sheetView.Pane.ActivePane, State: sheetView.Pane.State, } } } if s.Selected { worksheet.SheetViews.SheetView[0].TabSelected = true } } func (s *Sheet) makeSheetFormatPr(worksheet *xlsxWorksheet) { if s.SheetFormat.DefaultRowHeight != 0 { worksheet.SheetFormatPr.DefaultRowHeight = s.SheetFormat.DefaultRowHeight } worksheet.SheetFormatPr.DefaultColWidth = s.SheetFormat.DefaultColWidth } // func (s *Sheet) makeCols(worksheet *xlsxWorksheet, styles *xlsxStyleSheet) (maxLevelCol uint8) { maxLevelCol = 0 if s.Cols == nil { panic("trying to use uninitialised ColStore") } s.Cols.ForEach( func(c int, col *Col) { XfId := 0 style := col.GetStyle() hasNumFmt := len(col.numFmt) > 0 if style == nil && hasNumFmt { style = NewStyle() } if hasNumFmt { xNumFmt := styles.newNumFmt(col.numFmt) XfId = handleStyleForXLSX(style, xNumFmt.NumFmtId, styles) } col.outXfID = XfId // When the cols content is empty, the cols flag is not output in the xml file. if worksheet.Cols == nil { worksheet.Cols = &xlsxCols{Col: []xlsxCol{}} } worksheet.Cols.Col = append(worksheet.Cols.Col, xlsxCol{ Min: col.Min, Max: col.Max, Hidden: col.Hidden, Width: col.Width, CustomWidth: col.CustomWidth, Collapsed: col.Collapsed, OutlineLevel: col.OutlineLevel, Style: XfId, BestFit: col.BestFit, Phonetic: col.Phonetic, }) if col.OutlineLevel > maxLevelCol { maxLevelCol = col.OutlineLevel } }) return maxLevelCol } func (s *Sheet) makeRows(worksheet *xlsxWorksheet, styles *xlsxStyleSheet, refTable *RefTable, maxLevelCol uint8) { maxRow := 0 maxCell := 0 var maxLevelRow uint8 xSheet := xlsxSheetData{} for r, row := range s.Rows { if r > maxRow { maxRow = r } xRow := xlsxRow{} xRow.R = r + 1 if row.isCustom { xRow.CustomHeight = true xRow.Ht = fmt.Sprintf("%g", row.Height) } xRow.OutlineLevel = row.OutlineLevel if row.OutlineLevel > maxLevelRow { maxLevelRow = row.OutlineLevel } for c, cell := range row.Cells { var XfId int col := s.Col(c) if col != nil { XfId = col.outXfID } // generate NumFmtId and add new NumFmt xNumFmt := styles.newNumFmt(cell.NumFmt) style := cell.style switch { case style != nil: XfId = handleStyleForXLSX(style, xNumFmt.NumFmtId, styles) case len(cell.NumFmt) == 0: // Do nothing case col == nil: XfId = handleNumFmtIdForXLSX(xNumFmt.NumFmtId, styles) case !compareFormatString(col.numFmt, cell.NumFmt): XfId = handleNumFmtIdForXLSX(xNumFmt.NumFmtId, styles) } if c > maxCell { maxCell = c } xC := xlsxC{ S: XfId, R: GetCellIDStringFromCoords(c, r), } if cell.formula != "" { xC.F = &xlsxF{Content: cell.formula} } switch cell.cellType { case CellTypeInline: // Inline strings are turned into shared strings since they are more efficient. // This is what Excel does as well. fallthrough case CellTypeString: if len(cell.Value) > 0 { xC.V = strconv.Itoa(refTable.AddString(cell.Value)) } xC.T = "s" case CellTypeNumeric: // Numeric is the default, so the type can be left blank xC.V = cell.Value case CellTypeBool: xC.V = cell.Value xC.T = "b" case CellTypeError: xC.V = cell.Value xC.T = "e" case CellTypeDate: xC.V = cell.Value xC.T = "d" case CellTypeStringFormula: xC.V = cell.Value xC.T = "str" default: panic(errors.New("unknown cell type cannot be marshaled")) } xRow.C = append(xRow.C, xC) if nil != cell.DataValidation { if nil == worksheet.DataValidations { worksheet.DataValidations = &xlsxDataValidations{} } cell.DataValidation.Sqref = xC.R worksheet.DataValidations.DataValidation = append(worksheet.DataValidations.DataValidation, cell.DataValidation) worksheet.DataValidations.Count = len(worksheet.DataValidations.DataValidation) } if cell.HMerge > 0 || cell.VMerge > 0 { // r == rownum, c == colnum mc := xlsxMergeCell{} start := GetCellIDStringFromCoords(c, r) endCol := c + cell.HMerge endRow := r + cell.VMerge end := GetCellIDStringFromCoords(endCol, endRow) mc.Ref = start + cellRangeChar + end if worksheet.MergeCells == nil { worksheet.MergeCells = &xlsxMergeCells{} } worksheet.MergeCells.Cells = append(worksheet.MergeCells.Cells, mc) } } xSheet.Row = append(xSheet.Row, xRow) } // Update sheet format with the freshly determined max levels s.SheetFormat.OutlineLevelCol = maxLevelCol s.SheetFormat.OutlineLevelRow = maxLevelRow // .. and then also apply this to the xml worksheet worksheet.SheetFormatPr.OutlineLevelCol = s.SheetFormat.OutlineLevelCol worksheet.SheetFormatPr.OutlineLevelRow = s.SheetFormat.OutlineLevelRow if worksheet.MergeCells != nil { worksheet.MergeCells.Count = len(worksheet.MergeCells.Cells) } if s.AutoFilter != nil { worksheet.AutoFilter = &xlsxAutoFilter{Ref: fmt.Sprintf("%v:%v", s.AutoFilter.TopLeftCell, s.AutoFilter.BottomRightCell)} } worksheet.SheetData = xSheet dimension := xlsxDimension{} dimension.Ref = "A1:" + GetCellIDStringFromCoords(maxCell, maxRow) if dimension.Ref == "A1:A1" { dimension.Ref = "A1" } worksheet.Dimension = dimension } func (s *Sheet) makeDataValidations(worksheet *xlsxWorksheet) { if len(s.DataValidations) > 0 { if worksheet.DataValidations == nil { worksheet.DataValidations = &xlsxDataValidations{} } for _, dv := range s.DataValidations { worksheet.DataValidations.DataValidation = append(worksheet.DataValidations.DataValidation, dv) } worksheet.DataValidations.Count = len(worksheet.DataValidations.DataValidation) } } // Dump sheet to its XML representation, intended for internal use only func (s *Sheet) makeXLSXSheet(refTable *RefTable, styles *xlsxStyleSheet) *xlsxWorksheet { worksheet := newXlsxWorksheet() // Scan through the sheet and see if there are any merged cells. If there // are, we may need to extend the size of the sheet. There needs to be // phantom cells underlying the area covered by the merged cell s.handleMerged() s.makeSheetView(worksheet) s.makeSheetFormatPr(worksheet) maxLevelCol := s.makeCols(worksheet, styles) s.makeDataValidations(worksheet) s.makeRows(worksheet, styles, refTable, maxLevelCol) return worksheet } func handleStyleForXLSX(style *Style, NumFmtId int, styles *xlsxStyleSheet) (XfId int) { xFont, xFill, xBorder, xCellXf := style.makeXLSXStyleElements() fontId := styles.addFont(xFont) fillId := styles.addFill(xFill) // HACK - adding light grey fill, as in OO and Google greyfill := xlsxFill{} greyfill.PatternFill.PatternType = "lightGray" styles.addFill(greyfill) borderId := styles.addBorder(xBorder) xCellXf.FontId = fontId xCellXf.FillId = fillId xCellXf.BorderId = borderId xCellXf.NumFmtId = NumFmtId // apply the numFmtId when it is not the default cellxf if xCellXf.NumFmtId > 0 { xCellXf.ApplyNumberFormat = true } xCellXf.Alignment.Horizontal = style.Alignment.Horizontal xCellXf.Alignment.Indent = style.Alignment.Indent xCellXf.Alignment.ShrinkToFit = style.Alignment.ShrinkToFit xCellXf.Alignment.TextRotation = style.Alignment.TextRotation xCellXf.Alignment.Vertical = style.Alignment.Vertical xCellXf.Alignment.WrapText = style.Alignment.WrapText XfId = styles.addCellXf(xCellXf) return } func handleNumFmtIdForXLSX(NumFmtId int, styles *xlsxStyleSheet) (XfId int) { xCellXf := makeXLSXCellElement() xCellXf.NumFmtId = NumFmtId if xCellXf.NumFmtId > 0 { xCellXf.ApplyNumberFormat = true } XfId = styles.addCellXf(xCellXf) return }