package excel
import (
"fmt"
"os"
"path/filepath"
"strings"
"github.com/xuri/excelize/v2"
)
type ExcelizeExcel struct {
file *excelize.File
}
func NewExcelizeExcel(file *excelize.File) Excel {
return &ExcelizeExcel{file: file}
}
func (e *ExcelizeExcel) GetBackendName() string {
return "excelize"
}
func (e *ExcelizeExcel) FindSheet(sheetName string) (Worksheet, error) {
index, err := e.file.GetSheetIndex(sheetName)
if err != nil {
return nil, fmt.Errorf("sheet not found: %s", sheetName)
}
if index < 0 {
return nil, fmt.Errorf("sheet not found: %s", sheetName)
}
return &ExcelizeWorksheet{file: e.file, sheetName: sheetName}, nil
}
func (e *ExcelizeExcel) CreateNewSheet(sheetName string) error {
_, err := e.file.NewSheet(sheetName)
if err != nil {
return fmt.Errorf("failed to create new sheet: %w", err)
}
return nil
}
func (e *ExcelizeExcel) CopySheet(srcSheetName string, destSheetName string) error {
srcIndex, err := e.file.GetSheetIndex(srcSheetName)
if srcIndex < 0 {
return fmt.Errorf("source sheet not found: %s", srcSheetName)
}
if err != nil {
return err
}
destIndex, err := e.file.NewSheet(destSheetName)
if err != nil {
return fmt.Errorf("failed to create destination sheet: %w", err)
}
if err := e.file.CopySheet(srcIndex, destIndex); err != nil {
return fmt.Errorf("failed to copy sheet: %w", err)
}
srcNext := e.file.GetSheetList()[srcIndex+1]
if srcNext != srcSheetName {
e.file.MoveSheet(destSheetName, srcNext)
}
return nil
}
func (e *ExcelizeExcel) GetSheets() ([]Worksheet, error) {
sheetList := e.file.GetSheetList()
worksheets := make([]Worksheet, len(sheetList))
for i, sheetName := range sheetList {
worksheets[i] = &ExcelizeWorksheet{file: e.file, sheetName: sheetName}
}
return worksheets, nil
}
// SaveExcelize saves the Excel file to the specified path.
// Excelize's Save method restricts the file path length to 207 characters,
// but since this limitation has been relaxed in some environments,
// we ignore this restriction.
// https://github.com/qax-os/excelize/blob/v2.9.0/file.go#L71-L73
func (w *ExcelizeExcel) Save() error {
file, err := os.OpenFile(filepath.Clean(w.file.Path), os.O_WRONLY|os.O_TRUNC|os.O_CREATE, os.ModePerm)
if err != nil {
return err
}
defer file.Close()
return w.file.Write(file)
}
type ExcelizeWorksheet struct {
file *excelize.File
sheetName string
}
func (w *ExcelizeWorksheet) Release() {
// No resources to release in excelize
}
func (w *ExcelizeWorksheet) Name() (string, error) {
return w.sheetName, nil
}
func (w *ExcelizeWorksheet) GetTables() ([]Table, error) {
tables, err := w.file.GetTables(w.sheetName)
if err != nil {
return nil, fmt.Errorf("failed to get tables: %w", err)
}
tableList := make([]Table, len(tables))
for i, table := range tables {
tableList[i] = Table{
Name: table.Name,
Range: NormalizeRange(table.Range),
}
}
return tableList, nil
}
func (w *ExcelizeWorksheet) GetPivotTables() ([]PivotTable, error) {
pivotTables, err := w.file.GetPivotTables(w.sheetName)
if err != nil {
return nil, fmt.Errorf("failed to get pivot tables: %w", err)
}
pivotTableList := make([]PivotTable, len(pivotTables))
for i, pivotTable := range pivotTables {
pivotTableList[i] = PivotTable{
Name: pivotTable.Name,
Range: NormalizeRange(pivotTable.PivotTableRange),
}
}
return pivotTableList, nil
}
func (w *ExcelizeWorksheet) SetValue(cell string, value any) error {
if err := w.file.SetCellValue(w.sheetName, cell, value); err != nil {
return err
}
if err := w.updateDimension(cell); err != nil {
return fmt.Errorf("failed to update dimension: %w", err)
}
return nil
}
func (w *ExcelizeWorksheet) SetFormula(cell string, formula string) error {
if err := w.file.SetCellFormula(w.sheetName, cell, formula); err != nil {
return err
}
if err := w.updateDimension(cell); err != nil {
return fmt.Errorf("failed to update dimension: %w", err)
}
return nil
}
func (w *ExcelizeWorksheet) GetValue(cell string) (string, error) {
value, err := w.file.GetCellValue(w.sheetName, cell)
if err != nil {
return "", err
}
if value == "" {
// try to get calculated value
formula, err := w.file.GetCellFormula(w.sheetName, cell)
if err != nil {
return "", fmt.Errorf("failed to get formula: %w", err)
}
if formula != "" {
return w.file.CalcCellValue(w.sheetName, cell)
}
}
return value, nil
}
func (w *ExcelizeWorksheet) GetFormula(cell string) (string, error) {
formula, err := w.file.GetCellFormula(w.sheetName, cell)
if err != nil {
return "", fmt.Errorf("failed to get formula: %w", err)
}
if formula == "" {
// fallback
return w.GetValue(cell)
}
if !strings.HasPrefix(formula, "=") {
formula = "=" + formula
}
return formula, nil
}
func (w *ExcelizeWorksheet) GetDimention() (string, error) {
return w.file.GetSheetDimension(w.sheetName)
}
func (w *ExcelizeWorksheet) GetPagingStrategy(pageSize int) (PagingStrategy, error) {
return NewExcelizeFixedSizePagingStrategy(pageSize, w)
}
func (w *ExcelizeWorksheet) CapturePicture(captureRange string) (string, error) {
return "", fmt.Errorf("CapturePicture is not supported in Excelize")
}
func (w *ExcelizeWorksheet) AddTable(tableRange, tableName string) error {
enable := true
if err := w.file.AddTable(w.sheetName, &excelize.Table{
Range: tableRange,
Name: tableName,
StyleName: "TableStyleMedium2",
ShowColumnStripes: true,
ShowFirstColumn: false,
ShowHeaderRow: &enable,
ShowLastColumn: false,
ShowRowStripes: &enable,
}); err != nil {
return err
}
return nil
}
// updateDimention updates the dimension of the worksheet after a cell is updated.
func (w *ExcelizeWorksheet) updateDimension(updatedCell string) error {
dimension, err := w.file.GetSheetDimension(w.sheetName)
if err != nil {
return err
}
startCol, startRow, endCol, endRow, err := ParseRange(dimension)
if err != nil {
return err
}
updatedCol, updatedRow, err := excelize.CellNameToCoordinates(updatedCell)
if err != nil {
return err
}
if startCol > updatedCol {
startCol = updatedCol
}
if endCol < updatedCol {
endCol = updatedCol
}
if startRow > updatedRow {
startRow = updatedRow
}
if endRow < updatedRow {
endRow = updatedRow
}
startRange, err := excelize.CoordinatesToCellName(startCol, startRow)
if err != nil {
return err
}
endRange, err := excelize.CoordinatesToCellName(endCol, endRow)
if err != nil {
return err
}
updatedDimension := fmt.Sprintf("%s:%s", startRange, endRange)
return w.file.SetSheetDimension(w.sheetName, updatedDimension)
}