diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index d4c8a9a..4276d00 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -77,6 +77,7 @@ + diff --git a/ClosedXML_Tests/OleDb/OleDbTests.cs b/ClosedXML_Tests/OleDb/OleDbTests.cs new file mode 100644 index 0000000..695df08 --- /dev/null +++ b/ClosedXML_Tests/OleDb/OleDbTests.cs @@ -0,0 +1,144 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.OleDb; +using System.IO; +using System.Linq; + +namespace ClosedXML_Tests.OleDb +{ + [TestFixture] + public class OleDbTests + { + [Test] + public void TestOleDbValues() + { + using (var tf = new TestFile(CreateTestFile(), true)) + { + Console.Write("Using temporary file\t{0}", tf.Path); + var connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';", tf.Path); + using (var connection = new OleDbConnection(connectionString)) + { + // Install driver from https://www.microsoft.com/en-za/download/details.aspx?id=13255 if required + // Also check that test runner is running under correct architecture: + connection.Open(); + using (var command = new OleDbCommand("select * from [Sheet1$]", connection)) + using (var dataAdapter = new OleDbDataAdapter()) + { + dataAdapter.SelectCommand = command; + var dt = new DataTable(); + dataAdapter.Fill(dt); + + Assert.AreEqual("Base", dt.Columns[0].ColumnName); + Assert.AreEqual("Ref", dt.Columns[1].ColumnName); + + Assert.AreEqual(2, dt.Rows.Count); + + Assert.AreEqual(42, dt.Rows.Cast().First()[0]); + Assert.AreEqual("42", dt.Rows.Cast().First()[1]); + + Assert.AreEqual(41, dt.Rows.Cast().Last()[0]); + Assert.AreEqual("41", dt.Rows.Cast().Last()[1]); + } + + using (var command = new OleDbCommand("select * from [Sheet2$]", connection)) + using (var dataAdapter = new OleDbDataAdapter()) + { + dataAdapter.SelectCommand = command; + var dt = new DataTable(); + dataAdapter.Fill(dt); + + Assert.AreEqual("Ref1", dt.Columns[0].ColumnName); + Assert.AreEqual("Ref2", dt.Columns[1].ColumnName); + Assert.AreEqual("Sum", dt.Columns[2].ColumnName); + Assert.AreEqual("SumRef", dt.Columns[3].ColumnName); + + var expected = new Dictionary() + { + {"Ref1", "42" }, + {"Ref2", "41" }, + {"Sum", "83" }, + {"SumRef", "83" }, + }; + + foreach (var col in dt.Columns.Cast()) + foreach (var row in dt.Rows.Cast()) + { + Assert.AreEqual(expected[col.ColumnName], row[col]); + } + + Assert.AreEqual(2, dt.Rows.Count); + } + + connection.Close(); + } + } + } + + private string CreateTestFile() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").Value = "Base"; + ws.Cell("B1").Value = "Ref"; + + ws.Cell("A2").Value = 42; + ws.Cell("A3").Value = 41; + + ws.Cell("B2").FormulaA1 = "=A2"; + ws.Cell("B3").FormulaA1 = "=A3"; + + ws = wb.AddWorksheet("Sheet2"); + ws.Cell("A1").Value = "Ref1"; + ws.Cell("B1").Value = "Ref2"; + ws.Cell("C1").Value = "Sum"; + ws.Cell("D1").Value = "SumRef"; + + ws.Cell("A2").FormulaA1 = "=Sheet1!A2"; + ws.Cell("B2").FormulaA1 = "=Sheet1!A3"; + ws.Cell("C2").FormulaA1 = "=SUM(A2:B2)"; + ws.Cell("D2").FormulaA1 = "=SUM(Sheet1!A2:Sheet1!A3)"; + + ws.Cell("A3").FormulaA1 = "=Sheet1!B2"; + ws.Cell("B3").FormulaA1 = "=Sheet1!B3"; + ws.Cell("C3").FormulaA1 = "=SUM(A3:B3)"; + ws.Cell("D3").FormulaA1 = "=SUM(Sheet1!B2:Sheet1!B3)"; + + var path = Path.ChangeExtension(Path.GetTempFileName(), "xlsx"); + wb.SaveAs(path, true, true); + + return path; + } + } + + internal class TestFile : IDisposable + { + internal TestFile(string path) + : this(path, false) + { } + + internal TestFile(string path, bool preserve) + { + this.Path = path; + this.Preserve = preserve; + } + + public string Path { get; private set; } + public bool Preserve { get; private set; } + + public void Dispose() + { + if (!Preserve) + File.Delete(Path); + } + + public override string ToString() + { + return this.Path; + } + } + } +}