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 ToxySpreadsheet LoadData(DataContext context)
{
ParserContext pc = new ParserContext(context.Path);
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(pc);
var ss = parser.Parse();
return(ss);
}
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 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 TestParseIndexOutOfRange()
{
string path = TestDataSample.GetFilePath("countrylist.csv", null);
ParserContext context = new ParserContext(path);
context.Properties.Add("HasHeader", "1");
ISpreadsheetParser parser = (ISpreadsheetParser)ParserFactory.CreateSpreadsheet(context);
try
{
ToxyTable ss = parser.Parse(1);
}
catch (ArgumentOutOfRangeException ex)
{
Assert.IsTrue(ex.Message.Contains("CSV only has one table"));
}
}
protected void Page_Load(object sender, EventArgs e)
{
//string path = Server.MapPath("~") + "\\AreaSearch.xlsx";
//string path = Server.MapPath("~") + "App_Data\\";
//string filename = Session["FileName"].ToString();
//string copyPath = path + r.Next().ToString() + ext;
string path = Session["FileName"].ToString();
//File.Copy(path + filename, path);
ParserContext context = new ParserContext(path);
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
DataSet ds = ss.ToDataSet();
if (ds.Tables.Count > 0)
{
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
if (ds.Tables.Count > 1)
{
GridView2.DataSource = ds.Tables[1];
GridView2.DataBind();
}
if (ds.Tables.Count > 2)
{
GridView3.DataSource = ds.Tables[2];
GridView3.DataBind();
}
//using (var stream = File.Open(path, FileMode.Open, FileAccess.Write, FileShare.ReadWrite))
//{
//}
//if ((System.IO.File.Exists(path)))
//{
// System.IO.File.Delete(path);
//}
}
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 ExcelTableTemplate Parse(string filepath)
{
var templateMetadata = new TemplateMetaData();
if (filepath.StartsWith("/"))
{
filepath = HostingEnvironment.MapPath(filepath);
}
FilePath = filepath;
ParserContext context = new ParserContext(filepath);
ISpreadsheetParser parser = ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
foreach (var table in ss.Tables)
{
_currTable = table;
for (_currRowIndex = 0; _currRowIndex < table.Rows.Count; _currRowIndex++)
{
var row = table.Rows[_currRowIndex];
for (_currColIndex = 0; _currColIndex < row.Cells.Count; _currColIndex++)
{
var cell = row.Cells[_currColIndex];
var tabletoken = ParseTable(cell);
if (tabletoken != null)
{
templateMetadata.Tables.Add(tabletoken);
}
else
{
var cellToken = ParseCell(cell);
if (cellToken != null)
{
templateMetadata.Cells.Add(cellToken);
}
}
}
}
}
return(null);
}
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 TestParseToxySpreadsheet()
{
string path = TestDataSample.GetFilePath("countrylist.csv", null);
ParserContext context = new ParserContext(path);
context.Properties.Add("HasHeader", "1");
ISpreadsheetParser parser = (ISpreadsheetParser)ParserFactory.CreateSpreadsheet(context);
ToxySpreadsheet ss = parser.Parse();
Assert.AreEqual(1, ss.Tables.Count);
Assert.AreEqual(14, ss.Tables[0].ColumnHeaders.Cells.Count);
Assert.AreEqual("Sort Order", ss.Tables[0].ColumnHeaders.Cells[0].Value);
Assert.AreEqual("Sub Type", ss.Tables[0].ColumnHeaders.Cells[4].Value);
Assert.AreEqual(272, ss.Tables[0].Rows.Count);
Assert.AreEqual(12, ss.Tables[0].Rows[12].RowIndex);
Assert.AreEqual("Kingdom of Bahrain", ss.Tables[0].Rows[12].Cells[2].ToString());
Assert.AreEqual(".bo", ss.Tables[0].Rows[20].Cells[13].ToString());
Assert.AreEqual(271, ss.Tables[0].LastRowIndex);
}
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());
}
private void ShowDocument(string filepath, string encoding, string extension)
{
ParserContext context = new ParserContext(filepath);
context.Encoding = Encoding.GetEncoding(encoding);
if (Mode == ViewMode.Text)
{
AppendRichTextBox();
var tparser = ParserFactory.CreateText(context);
rtbPanel.Text = tparser.Parse();
tbParserType.Text = tparser.GetType().Name;
}
else if (Mode == ViewMode.Structured)
{
switch (extension)
{
case ".csv":
AppendSpreadsheetGrid();
context.Properties.Add("HasHeader", "1");
ISpreadsheetParser csvparser = ParserFactory.CreateSpreadsheet(context);
ss = csvparser.Parse();
tbParserType.Text = csvparser.GetType().Name;
var table1 = ss.Tables[0];
ShowToGrid(table1);
cbSheets.Items.Clear();
foreach (var table in ss.Tables)
{
cbSheets.Items.Add(table.Name);
}
cbSheets.SelectedIndex = 0;
panel1.Visible = true;
break;
case ".xlsx":
case ".xls":
AppendSpreadsheetGrid();
ISpreadsheetParser ssparser = ParserFactory.CreateSpreadsheet(context);
ss = ssparser.Parse();
tbParserType.Text = ssparser.GetType().Name;
var table0 = ss.Tables[0];
ShowToGrid(table0);
cbSheets.Items.Clear();
foreach (var table in ss.Tables)
{
cbSheets.Items.Add(table.Name);
}
cbSheets.SelectedIndex = 0;
panel1.Visible = true;
break;
case ".vcf":
AppendDataGridView();
var vparser = ParserFactory.CreateVCard(context);
ToxyBusinessCards vcards = vparser.Parse();
tbParserType.Text = vparser.GetType().Name;
gridPanel.GridView.DataSource = vcards.ToDataTable().DefaultView;
break;
case ".pptx":
//TODO: show slides
break;
case ".xml":
case ".htm":
case ".html":
AppendTreePanel();
var domparser = ParserFactory.CreateDom(context);
ToxyDom htmlDom = domparser.Parse();
TreeNode rootNode = treePanel.Tree.Nodes.Add(htmlDom.Root.NodeString);
treePanel.Tree.BeginUpdate();
AppendTree(rootNode, htmlDom.Root);
treePanel.Tree.EndUpdate();
//rootNode.ExpandAll();
break;
}
}
else
{
AppendPropertyListPanel();
var tparser = ParserFactory.CreateMetadata(context);
ToxyMetadata metadatas = tparser.Parse();
plPanel.Clear();
foreach (var data in metadatas)
{
plPanel.AddItem(data.Name, data.Value.ToString());
}
tbParserType.Text = tparser.GetType().Name;
}
}