xvdoc/export_exel.go

package xvdoc

import (
	"bytes"
	"fmt"
	_ "image/gif"
	_ "image/jpeg"
	_ "image/png"
	"io/ioutil"
	"log"
	"os"

	"github.com/xuri/excelize/v2"
)

func exportXLSX(mList []Matrix) (res []byte, err error) {
	if len(mList) == 0 {
		return nil, fmt.Errorf("XVDoc export xlsx error :: expexcted not empty matrix list")
	}

	// create excelize file and setup sheet name
	f := excelize.NewFile()
	for index, matrix := range mList {
		sName := matrix.Name
		if len(sName) == 0 {
			sName = fmt.Sprintf("Sheet%v", index)
		}
		if index == 0 {
			f.SetSheetName("Sheet1", sName)
		} else {
			f.NewSheet(sName)
		}
		////////////////

		// setup global styles
		var sID int
		xStyles := make(map[string]int)
		for name, style := range matrix.Styles {
			var styleRes JSONMap
			if styleRes, err = style.Result(matrix.Styles, name); err != nil {
				return
			}
			//fmt.Println("!!!!!!!!!!!!!!", name, string(styleRes.JSON()))
			if sID, err = f.NewStyle(string(styleRes.JSON())); err != nil {
				return
			}
			xStyles[name] = sID
		}
		////////////////

		// setup cells values
		var coords, cellName string
		for rIndex, row := range matrix.Cells {
			for cIndex, cell := range row {
				if coords, err = excelize.CoordinatesToCellName(cIndex+1, rIndex+1); err == nil {
					if cell != nil {
						if cell.Type() == "formula" {
							formula := cell.(*CellFormula)
							args := make([]string, len(formula.args))
							for i, arg := range formula.args {
								if arg.aType != argTypeValue {
									x, y := arg.Coords(cIndex, rIndex)
									if cellName, err = excelize.CoordinatesToCellName(int(x)+1, int(y)+1); err != nil {
										return
									} else {
										args[i] = cellName
									}
								} else {
									args[i] = fmt.Sprint(arg.Value())
								}
							}
							if err = f.SetCellFormula(sName, coords, formula.ReplaceArgs(args)); err != nil {
								return
							}
						} else {
							f.SetCellValue(sName, coords, cell.Val())
						}

						// setup cell styles
						if styles := cell.Styles(); len(styles) > 0 {
							//fmt.Println("&&&&", styles)
							for _, style := range styles {
								if sID, check := xStyles[style]; check {
									if err = f.SetCellStyle(sName, coords, coords, sID); err != nil {
										return
									}
								}
							}
						}
					}
				} else {
					return nil, err
				}
			}
			if height, check := matrix.RowsHeight[rIndex]; check {
				f.SetRowHeight(sName, rIndex+1, height)
			}
		}

		var c, dc string

		// setup rect styles
		for _, rect := range matrix.StyleRects {
			if c, err = excelize.CoordinatesToCellName(rect.x+1, rect.y+1); err != nil {
				return
			}
			if dc, err = excelize.CoordinatesToCellName(rect.dx+1, rect.dy+1); err != nil {
				return
			}
			for _, style := range rect.styles {
				if sID, check := xStyles[style]; check {
					if err = f.SetCellStyle(sName, c, dc, sID); err != nil {
						return
					}
				}
			}
		}
		///////////////////////////////////

		// setup images
		for _, image := range matrix.Images {
			if c, err = excelize.CoordinatesToCellName(image.x+1, image.y+1); err != nil {
				return
			}
			format := ""
			if image.format != nil {
				format = string(image.format.JSON())
			}
			log.Println(image.ext)
			if image.ext != "" {
				var fl *os.File
				if fl, err = os.Open(image.path); err != nil {
					return
				}
				defer fl.Close()
				var src []byte
				if src, err = ioutil.ReadFile(image.path); err != nil {
					return
				}
				log.Println("!!!!!!!!!!!!!!!!!!!!!!!!!")
				if err = f.AddPictureFromBytes(sName, c, format, image.name, image.ext, src); err != nil {
					return
				}
			} else {
				if err = f.AddPicture(sName, c, image.path, format); err != nil {
					return
				}
			}
		}
		///////////////////////////////////

		// setup columns width
		for _, cWidth := range matrix.ColumnsWidth {
			if c, err = excelize.ColumnNumberToName(cWidth.x + 1); err != nil {
				return
			}
			if dc, err = excelize.ColumnNumberToName(cWidth.dx + 1); err != nil {
				return
			}
			if err = f.SetColWidth(sName, c, dc, cWidth.width); err != nil {
				return
			}
		}

		// setup merges
		for _, merge := range matrix.Merges {
			if c, err = excelize.CoordinatesToCellName(merge.x+1, merge.y+1); err != nil {
				return
			}
			if dc, err = excelize.CoordinatesToCellName(merge.dx+1, merge.dy+1); err != nil {
				return
			}
			if err = f.MergeCell(sName, c, dc); err != nil {
				return
			}
		}

		for _, dropdown := range matrix.Dropdowns {
			if c, err = excelize.CoordinatesToCellName(dropdown.x+1, dropdown.y+1); err != nil {
				return
			}
			if dc, err = excelize.CoordinatesToCellName(dropdown.dx+1, dropdown.dy+1); err != nil {
				return
			}
			r := excelize.NewDataValidation(true)
			r.Sqref = "A5:A7"
			r.Sqref = fmt.Sprintf("%v:%v", c, dc)
			r.SetDropList(dropdown.values)
			f.AddDataValidation(sName, r)
		}
		///////////////////////////////////
	}

	//r := excelize.NewDataValidation(true)
	//r.Sqref = "A5:A7"
	//r.SetDropList([]string{"10", "30", "50"})
	//f.AddDataValidation("xv-test", r)

	// setup result data
	var buf bytes.Buffer
	f.Write(&buf)
	return buf.Bytes(), nil
}