using Sbroenne.ExcelMcp.ComInterop.Session;
namespace Sbroenne.ExcelMcp.Core.Tests.Helpers;
/// <summary>
/// Helper functions for creating temporary Excel data sources consumable via the
/// Microsoft.ACE.OLEDB provider. These utilities are used by OLEDB integration tests.
/// </summary>
public static class AceOleDbTestHelper
{
private const string ProviderName = "Microsoft.ACE.OLEDB.16.0";
/// <summary>
/// Creates a temporary Excel workbook containing a simple Products table that can
/// be queried via ACE OLEDB. The workbook is saved to <paramref name="workbookPath"/>.
/// </summary>
public static void CreateExcelDataSource(string workbookPath)
{
ExcelSession.CreateNew(workbookPath, isMacroEnabled: false, (ctx, ct) =>
{
dynamic sheet = ctx.Book.Worksheets[1];
sheet.Name = "Products";
sheet.Range["A1"].Value2 = "Product";
sheet.Range["B1"].Value2 = "Price";
sheet.Range["A2"].Value2 = "Widget";
sheet.Range["B2"].Value2 = 19.99;
sheet.Range["A3"].Value2 = "Gadget";
sheet.Range["B3"].Value2 = 29.99;
ctx.Book.Save();
return 0;
});
}
/// <summary>
/// Updates the value cells in the Excel data source using an Excel COM operation.
/// Useful for simulating data source changes before calling Refresh on the connection.
/// </summary>
public static void UpdateExcelDataSource(string workbookPath, Action<dynamic> updateAction)
{
using var batch = ExcelSession.BeginBatch(workbookPath);
batch.Execute((ctx, ct) =>
{
dynamic sheet = ctx.Book.Worksheets[1];
updateAction(sheet);
ctx.Book.Save();
return 0;
});
}
/// <summary>
/// Generates an ACE OLEDB connection string targeting the supplied Excel workbook.
/// </summary>
public static string GetExcelConnectionString(string workbookPath, bool headersPresent = true)
{
string hdr = headersPresent ? "YES" : "NO";
return $"OLEDB;Provider={ProviderName};Data Source={workbookPath};Extended Properties=\"Excel 12.0 Xml;HDR={hdr}\"";
}
/// <summary>
/// Returns the default SQL command text used in tests to select data from the Products worksheet.
/// </summary>
public static string GetDefaultCommandText() => "SELECT * FROM [Products$]";
}
