Excel-Pajak-Generator-Ak/excel_pajak/Services/ExcelService.cs

using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.UserModel.Helpers;
using excel_pajak.Models;

namespace excel_pajak.Services;

public static class ExcelService
{


    /// <summary>
    /// Initializes an Excel workbook from a template file.
    /// </summary>
    /// <param name="outputName">The name of the output Excel file (without .xlsx extension).</param>
    /// <param name="templatePath">The relative path to the template file from the application base directory.</param>
    /// <param name="outputFolder">The folder where the output file will be created.</param>
    /// <param name="overwrite">If true, will overwrite an existing file. If false, will throw IOException if file exists.</param>
    /// <returns>A tuple containing success status, output file path on success, or exception on failure.</returns>
    public static (bool success, string? result, Exception? error) InitExcel(string outputName, string templatePath, string outputFolder, bool overwrite = true)
    {
        if (string.IsNullOrWhiteSpace(outputName))
        {
            return (false, null, new ArgumentException("File name cannot be null or empty"));
        }

        try
        {
            var templateFullPath = Path.IsPathRooted(templatePath)
                ? templatePath
                : Path.Combine(AppDomain.CurrentDomain.BaseDirectory, templatePath);

            if (!File.Exists(templateFullPath))
            {
                return (false, null, new FileNotFoundException($"Template file not found: {templateFullPath}"));
            }

            if (!Directory.Exists(outputFolder))
            {
                Directory.CreateDirectory(outputFolder);
            }

            var outputFileName = outputName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase)
                ? outputName
                : outputName + ".xlsx";
            var outputFullPath = Path.Combine(outputFolder, outputFileName);

            if (!overwrite && File.Exists(outputFullPath))
            {
                return (false, null, new IOException($"File already exists: {outputFullPath}. Set overwrite=true to replace it."));
            }

            using (var sourceStream = new FileStream(templateFullPath, FileMode.Open, FileAccess.Read))
            {
                var workbook = new XSSFWorkbook(sourceStream);
                using (var destinationStream = new FileStream(outputFullPath, overwrite ? FileMode.Create : FileMode.CreateNew, FileAccess.Write))
                {
                    workbook.Write(destinationStream, false);
                }
                workbook.Close();
            }

            return (true, outputFullPath, null);
        }
        catch (Exception ex)
        {
            return (false, null, ex);
        }
    }

    public static bool WriteExcel(string fileName, string value, int row, int column)
    {
        return InternalWriteExcel(fileName, value, row, column);
    }

    public static bool WriteExcel(string fileName, long value, int row, int column)
    {
        return InternalWriteExcel(fileName, value, row, column);
    }

    public static bool WriteExcel(string fileName, decimal value, int row, int column)
    {
        return InternalWriteExcel(fileName, value, row, column);
    }

    private static bool InternalWriteExcel(string fileName, object value, int row, int column)
    {
        if (string.IsNullOrWhiteSpace(fileName))
        {
            return false;
        }

        if (row < 1)
        {
            return false;
        }

        if (column < 1)
        {
            return false;
        }

        if (!File.Exists(fileName))
        {
            return false;
        }

        try
        {
            XSSFWorkbook workbook;
            using (var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(fileStream);
            }

            ISheet sheet = workbook.NumberOfSheets > 0 ? workbook.GetSheetAt(0) : workbook.CreateSheet();

            IRow dataRow = sheet.GetRow(row - 1) ?? sheet.CreateRow(row - 1);
            ICell cell = dataRow.GetCell(column - 1) ?? dataRow.CreateCell(column - 1);

            if (value is string strValue)
            {
                if (string.IsNullOrEmpty(strValue))
                {
                    cell.SetCellValue(string.Empty);
                }
                else if (double.TryParse(strValue, out double numericValue))
                {
                    cell.SetCellValue(numericValue);
                }
                else
                {
                    cell.SetCellValue(strValue);
                }
            }
            else if (value is long longValue)
            {
                cell.SetCellValue((double)longValue);
            }
            else if (value is decimal decimalValue)
            {
                cell.SetCellValue((double)decimalValue);
            }
            else if (value is double doubleValue)
            {
                cell.SetCellValue(doubleValue);
            }

            using (var fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fileStream, false);

            }
            workbook.Close();

            return true;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public static bool RefreshFormulas(string fileName)
    {
        if (string.IsNullOrWhiteSpace(fileName))
        {
            return false;
        }

        if (!File.Exists(fileName))
        {
            return false;
        }

        try
        {
            XSSFWorkbook workbook;
            using (var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(fileStream);
            }

            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                sheet.ForceFormulaRecalculation = true;
            }

            XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

            using (var fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fileStream, false);
            }
            workbook.Close();

            return true;
        }
        catch
        {
            return false;
        }
    }
}