public void TestReadExcelAndConvertToDataSet()
{
ParserContext c = new ParserContext(TestDataSample.GetExcelPath("Employee.xls"));
var parser = ParserFactory.CreateSpreadsheet(c);
var spreadsheet = parser.Parse();
DataSet ds = spreadsheet.ToDataSet();
Assert.AreEqual(3, ds.Tables.Count);
Assert.AreEqual("Sheet1", ds.Tables[0].TableName);
Assert.AreEqual("Sheet2", ds.Tables[1].TableName);
Assert.AreEqual("Sheet3", ds.Tables[2].TableName);
var s1 = ds.Tables[0];
Assert.AreEqual(System.DBNull.Value, s1.Rows[0][0]);
Assert.AreEqual(System.DBNull.Value, s1.Rows[0][1]);
Assert.AreEqual(System.DBNull.Value, s1.Rows[0][2]);
Assert.AreEqual("Employee Info", s1.Rows[1][1]);
Assert.AreEqual("Last name:", s1.Rows[3][1]);
Assert.AreEqual("lastName", s1.Rows[3][2]);
Assert.AreEqual("First name:", s1.Rows[4][1]);
Assert.AreEqual("firstName", s1.Rows[4][2]);
Assert.AreEqual("SSN:", s1.Rows[5][1]);
Assert.AreEqual("ssn", s1.Rows[5][2]);
}
public void BaseTestWithHeaderRow(string filename)
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath(filename));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
parser.Context.Properties.Add("HasHeader", "1");
ToxySpreadsheet ss = parser.Parse();
Assert.AreEqual(1, ss.Tables[0].HeaderRows.Count);
Assert.AreEqual("A", ss.Tables[0].HeaderRows[0].Cells[0].Value);
Assert.AreEqual("B", ss.Tables[0].HeaderRows[0].Cells[1].Value);
Assert.AreEqual("C", ss.Tables[0].HeaderRows[0].Cells[2].Value);
Assert.AreEqual("D", ss.Tables[0].HeaderRows[0].Cells[3].Value);
Assert.AreEqual(3, ss.Tables[0].Rows.Count);
Assert.AreEqual("1", ss.Tables[0].Rows[0].Cells[0].Value);
Assert.AreEqual("2", ss.Tables[0].Rows[0].Cells[1].Value);
Assert.AreEqual("3", ss.Tables[0].Rows[0].Cells[2].Value);
Assert.AreEqual("4", ss.Tables[0].Rows[0].Cells[3].Value);
Assert.AreEqual("A1", ss.Tables[0].Rows[1].Cells[0].Value);
Assert.AreEqual("A2", ss.Tables[0].Rows[1].Cells[1].Value);
Assert.AreEqual("A3", ss.Tables[0].Rows[1].Cells[2].Value);
Assert.AreEqual("A4", ss.Tables[0].Rows[1].Cells[3].Value);
Assert.AreEqual("B1", ss.Tables[0].Rows[2].Cells[0].Value);
Assert.AreEqual("B2", ss.Tables[0].Rows[2].Cells[1].Value);
Assert.AreEqual("B3", ss.Tables[0].Rows[2].Cells[2].Value);
Assert.AreEqual("B4", ss.Tables[0].Rows[2].Cells[3].Value);
}
public void TestExcelFile()
{
string path = Path.GetFullPath(TestDataSample.GetExcelPath("comments.xls"));
ParserContext context = new ParserContext(path);
IMetadataParser parser = (IMetadataParser)ParserFactory.CreateMetadata(context);
ToxyMetadata x = parser.Parse();
Assert.AreEqual(8, x.Count);
Assert.AreEqual("Microsoft Excel", x.Get("ApplicationName").Value);
}
public void TestExcel2003TextParser()
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath("Employee.xls"));
ITextParser parser = ParserFactory.CreateText(context);
string result = parser.Parse();
Assert.IsNotNull(result);
Assert.IsTrue(result.IndexOf("Last name") > 0);
Assert.IsTrue(result.IndexOf("First name") > 0);
}
public void BaseTestWithoutHeader(string filename)
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath(filename));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
Assert.IsNull(ss.Tables[0].PageHeader);
Assert.AreEqual(0, ss.Tables[0].HeaderRows.Count);
Assert.AreEqual(9, ss.Tables[0].Rows.Count);
}
public void TestExcel2007TextParserWithHeaderFooter()
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath("WithVariousData.xlsx"));
context.Properties.Add("IncludeHeaderFooter", "1");
ITextParser parser = ParserFactory.CreateText(context);
string result = parser.Parse();
Assert.IsNotNull(result);
Assert.IsTrue(result.IndexOf("This is the header") > 0);
}
public void TestExcel2007TextParserWithoutSheetNames()
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath("WithVariousData.xlsx"));
context.Properties.Add("IncludeSheetNames", "0");
ITextParser parser = ParserFactory.CreateText(context);
string result = parser.Parse();
Assert.IsNotNull(result);
Assert.IsTrue(result.IndexOf("Sheet1") < 0);
}
public void BaseTestHeader(string filename)
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath(filename));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
Assert.IsNull(ss.Tables[0].PageHeader);
parser.Context.Properties.Add("ExtractSheetHeader", "1");
ToxySpreadsheet ss2 = parser.Parse();
Assert.IsNotNull(ss2.Tables[0].PageHeader);
Assert.AreEqual("|testdoc|test phrase", ss2.Tables[0].PageHeader);
}
public void TestParseSheetIndexOutOfRange()
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath("Formatting.xlsx"));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
try
{
ToxyTable ss = parser.Parse(50);
}
catch (ArgumentOutOfRangeException ex)
{
Assert.IsTrue(ex.Message.Contains("This file only contains 3 sheet(s)."));
}
}
public void BaseTestExcelComment(string filename)
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath(filename));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
Assert.AreEqual(3, ss.Tables.Count);
Assert.AreEqual(3, ss.Tables[0].Rows.Count);
Assert.AreEqual(0, ss.Tables[0].Rows[1].Cells[0].CellIndex);
Assert.AreEqual(2, ss.Tables[0].Rows[1].RowIndex);
//TODO: fix this comment without cell value
Assert.AreEqual("comment top row1 (index0)\n", ss.Tables[0].Rows[0].Cells[0].Comment);
Assert.AreEqual("comment top row3 (index2)\n", ss.Tables[0].Rows[1].Cells[0].Comment);
Assert.AreEqual("comment top row4 (index3)\n", ss.Tables[0].Rows[2].Cells[0].Comment);
}
public void TestExcel2007TextParser()
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath("WithVariousData.xlsx"));
ITextParser parser = ParserFactory.CreateText(context);
string result = parser.Parse();
Assert.IsNotNull(result);
Assert.IsTrue(result.IndexOf("Foo") > 0);
Assert.IsTrue(result.IndexOf("Bar") > 0);
Assert.IsTrue(result.IndexOf("a really long cell") > 0);
Assert.IsTrue(result.IndexOf("have a header") > 0);
Assert.IsTrue(result.IndexOf("have a footer") > 0);
Assert.IsTrue(result.IndexOf("This is the header") < 0);
}
public void BaseTestExcelFormatedString(string filename)
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath(filename));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
Assert.AreEqual(13, ss.Tables[0].Rows.Count);
Assert.AreEqual("Dates, all 24th November 2006", ss.Tables[0].Rows[0].Cells[0].ToString());
Assert.AreEqual("24/11/2006", ss.Tables[0].Rows[1].Cells[1].ToString());
Assert.AreEqual("2006/11/24", ss.Tables[0].Rows[2].Cells[1].ToString());
Assert.AreEqual("2006-11-24", ss.Tables[0].Rows[3].Cells[1].ToString());
Assert.AreEqual("06/11/24", ss.Tables[0].Rows[4].Cells[1].ToString());
Assert.AreEqual("24/11/06", ss.Tables[0].Rows[5].Cells[1].ToString());
Assert.AreEqual("24-11-06", ss.Tables[0].Rows[6].Cells[1].ToString());
Assert.AreEqual("10.52", ss.Tables[0].Rows[9].Cells[1].ToString());
Assert.AreEqual("10.520", ss.Tables[0].Rows[10].Cells[1].ToString());
Assert.AreEqual("10.5", ss.Tables[0].Rows[11].Cells[1].ToString());
Assert.AreEqual("£10.52", ss.Tables[0].Rows[12].Cells[1].ToString());
}
public void BaseTestFillBlankCells(string filename)
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath(filename));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
Assert.AreEqual(1, ss.Tables[0].Rows[0].Cells.Count);
Assert.AreEqual(0, ss.Tables[0].Rows[1].Cells.Count);
Assert.AreEqual(2, ss.Tables[0].Rows[2].Cells.Count);
Assert.AreEqual(2, ss.Tables[0].Rows[3].Cells.Count);
Assert.AreEqual(2, ss.Tables[0].Rows[4].Cells.Count);
parser.Context.Properties.Add("FillBlankCells", "1");
ToxySpreadsheet ss2 = parser.Parse();
Assert.AreEqual(3, ss2.Tables[0].Rows[0].Cells.Count);
Assert.AreEqual(3, ss2.Tables[0].Rows[1].Cells.Count);
Assert.AreEqual(3, ss2.Tables[0].Rows[2].Cells.Count);
Assert.AreEqual(3, ss2.Tables[0].Rows[3].Cells.Count);
Assert.AreEqual(3, ss2.Tables[0].Rows[4].Cells.Count);
}
public void BaseTestExcelContent(string filename)
{
ParserContext context = new ParserContext(TestDataSample.GetExcelPath(filename));
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
Assert.AreEqual(3, ss.Tables.Count);
Assert.AreEqual("Sheet1", ss.Tables[0].Name);
Assert.AreEqual("Sheet2", ss.Tables[1].Name);
Assert.AreEqual("Sheet3", ss.Tables[2].Name);
Assert.AreEqual(5, ss.Tables[0].Rows.Count);
Assert.AreEqual(0, ss.Tables[1].Rows.Count);
Assert.AreEqual(0, ss.Tables[2].Rows.Count);
ToxyTable table = ss.Tables[0];
Assert.AreEqual(1, table.Rows[0].RowIndex);
Assert.AreEqual(2, table.Rows[1].RowIndex);
Assert.AreEqual(3, table.Rows[2].RowIndex);
Assert.AreEqual(4, table.Rows[3].RowIndex);
Assert.AreEqual(5, table.Rows[4].RowIndex);
Assert.AreEqual(1, table.Rows[0].Cells.Count);
Assert.AreEqual(0, table.Rows[1].Cells.Count);
Assert.AreEqual(2, table.Rows[2].Cells.Count);
Assert.AreEqual(2, table.Rows[3].Cells.Count);
Assert.AreEqual(2, table.Rows[4].Cells.Count);
Assert.AreEqual("Employee Info", table.Rows[0].Cells[0].ToString());
Assert.AreEqual(1, table.Rows[0].Cells[0].CellIndex);
Assert.AreEqual("Last name:", table.Rows[2].Cells[0].ToString());
Assert.AreEqual(1, table.Rows[2].Cells[0].CellIndex);
Assert.AreEqual("lastName", table.Rows[2].Cells[1].ToString());
Assert.AreEqual(2, table.Rows[2].Cells[1].CellIndex);
Assert.AreEqual("First name:", table.Rows[3].Cells[0].ToString());
Assert.AreEqual("firstName", table.Rows[3].Cells[1].ToString());
Assert.AreEqual("SSN:", table.Rows[4].Cells[0].ToString());
Assert.AreEqual("ssn", table.Rows[4].Cells[1].ToString());
}