338 lines
13 KiB
C#
Executable File
338 lines
13 KiB
C#
Executable File
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);
|
|
|
|
*/
|