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); } }