using System;
using Spire.Xls;

//using Spire.Xls;
using System.IO;
using System.Diagnostics;
using Excel2PDF;

using System.Collections.Generic;
using System.Globalization;

using System.Runtime.InteropServices;


namespace Excel2PDF
{
    public class InvoiceCell       // Invoice Cells from Invoice Template
    {
        public String Account { get; set; } = "I2";         // "Account"+ (also I32)
        public String Date { get; set; } = "I3";            // I3 (also I33) 
        public String Invoice { get; set; } = "I4";         //I4 "Invoice" (also I34)
        public String Total_Charges { get; set; } = "I14";  //I14 (also I36)
        
        public String Account2 { get; set; } = "I32";       // "Account"+ (also I32)
        public String Date2 { get; set; } = "I33";          // I3 (also I33) 
        public String Invoice2 { get; set; } = "I34";       //I4 "Invoice" (also I34)
        public String Total_Charges2 { get; set; } = "I36"; //I14 (also I36)
        
        public String CCI { get; set; } = "C12";  // C12 "Canada Contract Identifier"
        
        public String PaperFee { get; set; } = "H17";
        public String LateCharge { get; set; } = "H18";
        public String Service_Charges { get; set; } = "I19";
        public String UsageCharges { get; set; } = "H20";
        public String TotalUsageCharges { get; set; } = "I21";
        public String DataMonthlyCharges { get; set; } = "H23";
        public String DataNon_RecurringCharges { get; set; } = "H24";
        public String DataTotalOtherCharges { get; set; } = "H25";
        public String Data_Services { get; set; } = "I27";
        public String TaxON_PST { get; set; } = "F29";
        public String TaxON_GST { get; set; } = "G29";
        public String TaxON_HST { get; set; } = "H29";
        public String TaxPQ_PST { get; set; } = "F30";
        public String TaxPQ_GST { get; set; } = "G30";
        public String TaxPQ_HST { get; set; } = "H30";
        public String Taxes { get; set; } = "I31";
    }
    
    // Source - https://stackoverflow.com/a
    // Posted by Alexandru
    // Retrieved 2025-12-03, License - CC BY-SA 3.0

    internal class OutputSink : IDisposable
    {
        [DllImport("kernel32.dll")]
        public static extern IntPtr GetStdHandle(int nStdHandle);

        [DllImport("kernel32.dll")]
        public static extern int SetStdHandle(int nStdHandle, IntPtr hHandle);

        private readonly TextWriter _oldOut;
        private readonly TextWriter _oldError;
        private readonly IntPtr _oldOutHandle;
        private readonly IntPtr _oldErrorHandle;

        public OutputSink()
        {
            _oldOutHandle = GetStdHandle(-11);
            _oldErrorHandle = GetStdHandle(-12);
            _oldOut = Console.Out;
            _oldError = Console.Error;
            Console.SetOut(TextWriter.Null);
            Console.SetError(TextWriter.Null);
            SetStdHandle(-11, IntPtr.Zero);
            SetStdHandle(-12, IntPtr.Zero);
        }

        public void Dispose()
        {
            SetStdHandle(-11, _oldOutHandle);
            SetStdHandle(-12, _oldErrorHandle);
            Console.SetOut(_oldOut);
            Console.SetError(_oldError);
        }
    }

        
    
    internal class Program
    {
        static void Main(string[] args)
        {
            //MakePDF();
            Dictionary<string,string> Invoice = new Dictionary<string,string> { };
            InvoiceCell ic = new InvoiceCell { };
            
            Invoice.Add( ic.Account , "Account P02-" + "123456"); //bdf2 C2   
            int d = 20250731;       
            Invoice.Add( ic.Date , dateFormal(d));  
            //string dt = "20250930";     
            Invoice.Add( ic.Invoice , ( "Invoice " + d.ToString().Substring(4) + d.ToString().Substring(0, 4) + " - NU" ) );   //mmddyyyy 
            Invoice.Add( ic.Total_Charges ,  String.Format("${0:n}",10000) ); 
            
            Invoice.Add( ic.Account2 , Invoice[ic.Account]);       
            Invoice.Add( ic.Date2 , Invoice[ic.Date]);  
            Invoice.Add( ic.Invoice2 , Invoice[ic.Invoice]); 
            Invoice.Add( ic.Total_Charges2 , Invoice[ic.Total_Charges]); 
            
            customInvoice("doug", ref Invoice);
        }
        
        
        static void MakePDF()
        {
            // Create a Workbook object
            Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();

            // Load an Excel file (replace with your file path)
            workbook.LoadFromFile("test.xlsx");
            
            //PdfFont fieldFont = new PdfFont(PdfFontFamily.Helvetica, 10f);
            
            // Get the first worksheet
            Spire.Xls.Worksheet sheet = workbook.Worksheets[0];

            // Set the print area to a specific cell range
            // Comment this line out if you need to export the entire worksheet as a PDF
            //sheet.PageSetup.PrintArea = "B1:E6";

            // Optional: Set worksheets to fit to page when converting
            workbook.ConverterSetting.SheetFitToPage = true;

            // Save the Excel file to PDF
            workbook.SaveToFile("Output.pdf", FileFormat.PDF);

            // Dispose resources
            workbook.Dispose();
        }
        
        static void LoadStream()
        {

            // 1. Create a Workbook instance
            Workbook workbook = new Workbook();

            // 2. Create a Stream object (example using FileStream)
            // Replace "sample.xls" with your actual file path or retrieve the stream from your source
            FileStream fileStream = File.OpenRead("sample.xls");

            // Ensure the stream position is at the beginning (if necessary)
            fileStream.Seek(0, SeekOrigin.Begin);

            // 3. Use the Workbook.LoadFromStream() method to load the file
            try
            {
                workbook.LoadFromStream(fileStream);
            }
            catch (Exception ex)
            {
                // Handle exceptions, e.g., file format not supported (Excel 95 or earlier not supported)
                Console.WriteLine($"Error loading stream: {ex.Message}");
            }
            finally
            {
                // 4. Close the stream after loading
                fileStream.Dispose();
            }

            // Now you can work with the workbook data
            Worksheet sheet = workbook.Worksheets[0];
            // ... do operations like reading cells, etc. ...

            // Example of saving the processed file to a new file
            workbook.SaveToFile("From_stream_processed.xls", ExcelVersion.Version97to2003);
            Process.Start("From_stream_processed.xls");

        }
        
        static void LoadStreamXLSX()
        {
          // 1. Create a Workbook instance
          Workbook workbook = new Workbook();

          // 2. Open a FileStream from your source file
          using (FileStream fileStream = File.OpenRead("Sample.xlsx"))
          {
              // Ensure the stream position is at the beginning
              fileStream.Seek(0, SeekOrigin.Begin);
              
              // 3. Load the workbook from the stream
              workbook.LoadFromStream(fileStream);

              // 4. (Optional) Work with the workbook, e.g., modify a cell
              workbook.Worksheets[0].Range["A1"].Text = "Data loaded from stream!";

              // (Optional) Save the result to a new stream or file
              // workbook.SaveToStream(outputStream);
              // workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2016);
          }
        }
        
        static void customInvoice(string type, ref Dictionary<string,string> CellData)
        {
          //string fontDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "");
          string fontDirectory = AppDomain.CurrentDomain.BaseDirectory;
          string[] fontDirectories = { fontDirectory };

          /* Set the custom font file directory for the workbook instance
          TextWriter originalOutput = Console.Out;

          // Temporarily suppress console output
          Console.SetOut(TextWriter.Null);
          
          Workbook workbook = new Workbook();   // spews Not Implemented error
          
          // Restore the original standard output writer
          //Console.SetError(originalOutput);
          
          //Console.SetOut(originalOutput);
          
          
          /*
          // Store the original listeners
          var originalListeners = Debug.Listeners;

          // Temporarily clear the listeners to suppress all Debug output
          Debug.Listeners.Clear();

          // Code here that generates unwanted debug output (e.g., a noisy library call)
          Workbook workbook = new Workbook();   // spews Not Implemented error 

          // Re-add the original listeners (or just the DefaultTraceListener)
          Debug.Listeners.Add(new DefaultTraceListener());
          
          */
          Workbook workbook = new Workbook();
          /*
          using (new OutputSink())
          {
          Console.WriteLine("a");
          workbook = new Workbook();   // spews Not Implemented error
          Console.WriteLine("b");
          }
          */
          workbook.CustomFontFileDirectory = fontDirectories;
          //Console.WriteLine("c");
          
          // 1. Create a Workbook instance
          //Workbook workbook = new Workbook();

          // 2. Open a FileStream from your source file
          using (FileStream fileStream = File.OpenRead("test.xlsx"))
          {
              // Ensure the stream position is at the beginning
              fileStream.Seek(0, SeekOrigin.Begin);
              
              // 3. Load the workbook from the stream
              workbook.LoadFromStream(fileStream);

              // 4. (Optional) Work with the workbook, e.g., modify a cell
              foreach( var cell in CellData)
              {
                // workbook.Worksheets[0].Range["A1"].Text = "Data loaded from stream!";
                workbook.Worksheets[0].Range[cell.Key].Text = cell.Value;
              
              } // end foreach
              
              //Spire.Xls.Worksheet sheet = workbook.Worksheets[0];

              // Set the print area to a specific cell range
              // Comment this line out if you need to export the entire worksheet as a PDF
              //sheet.PageSetup.PrintArea = "B1:E6";

              // Optional: Set worksheets to fit to page when converting
              workbook.ConverterSetting.SheetFitToPage = true;

              // (Optional) Save the result to a new stream or file
              // workbook.SaveToStream(outputStream);
              // workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2016);
              // Save the Excel file to PDF
              string pdf_name = "Output_" + type + ".pdf";
              workbook.SaveToFile("Output.pdf", FileFormat.PDF);
          }  //end using
        }
        
        public static string dateFormal(int dateInt)
        {
            //int dateInt = 20251130;
            string dateString = dateInt.ToString();

            // 1. Define the input format ("yyyyMMdd")
            string inputFormat = "yyyyMMdd";

            // 2. Parse the string into a DateTime object
            
            DateTime dateTimeObject = DateTime.ParseExact(
                dateString,
                inputFormat,
                CultureInfo.InvariantCulture
            );

            // 3. Define the desired output format ("MMMM dd, yyyy")
            // MMMM = Full month name
            // dd   = Day of the month
            // yyyy = Year

            string outputFormat = "MMMM d, yyyy";
            string result = dateTimeObject.ToString(outputFormat);
 
            //Console.WriteLine(result);
            // Output: November 30, 2025
            return result;
        }

      }
        
}

/*

// Ensure these files are present in this directory in your application's deployment environment.
string fontDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Fonts");
string[] fontDirectories = { fontDirectory };

// Set the custom font file directory for the workbook instance
Workbook workbook = new Workbook();
workbook.CustomFontFileDirectory = fontDirectories; //

// Load your Excel file
workbook.LoadFromFile("InputFile.xls");

// ... perform other operations ...

// When saving or converting (e.g., to PDF), the library will check the specified path for fonts
workbook.SaveToFile("OutputFile.xlsx", ExcelVersion.Version2013);

*/