private IWorksheet GetWorksheet()
{
if (null == _worksheet)
{
var www = new WebClient();
var buf = www.DownloadData(_url);
// test for xml or pkzip (ie. packed xlsx format) leadin
// also account for leading text encoding markers in xml
if (buf.Length <= 10)
{
return(null);
}
if ((buf[0] == 'P' && buf[1] == 'K') ||
(buf[0] == '<' && buf[1] == '?' && buf[2] == 'x') ||
(buf[1] == '<' && buf[2] == '?' && buf[3] == 'x') ||
(buf[2] == '<' && buf[3] == '?' && buf[4] == 'x') ||
(buf[3] == '<' && buf[4] == '?' && buf[5] == 'x') ||
(buf[4] == '<' && buf[5] == '?' && buf[6] == 'x'))
{
using (var ms = new MemoryStream(buf))
_workbook = WorkbookFactory.GetExcel2007Reader(ms);
}
else
{
using (var ms = new MemoryStream(buf))
_workbook = WorkbookFactory.GetExcelBIFFReader(ms);
}
_worksheet = _workbook.Worksheets.GetWorksheetByIndex(0);
}
return(_worksheet);
}
public void AsDataSetXls()
{
IWorkbook workbook = WorkbookFactory.GetExcelBIFFReader(@"..\..\Files\AsDataSet\AsDataSet.xls");
DataSet set = workbook.AsDataSet(true);
Assert.AreEqual(1, set.Tables.Count);
Assert.AreEqual("Sheet1", set.Tables[0].TableName);
Assert.AreEqual(3, set.Tables[0].Rows.Count);
Assert.AreEqual(6, set.Tables[0].Columns.Count);
string[] expectedCols = { "A", "B", "C", "E", "F", "G" };
for (int i = 0; i < set.Tables[0].Columns.Count; i++)
{
Assert.AreEqual(expectedCols[i], set.Tables[0].Columns[i].ColumnName);
}
}