private void XlsxToTableData(string testCaseId, string sheetName, string[] columnNames)
{
ArrayList list = new ArrayList();
DataTable dataTable = new DataTable();
int sheetCount = xssfworkbook.NumberOfSheets;
Logger.Info(string.Concat("Total Sheets found in the workbook are : [", sheetCount, "]"));
ISheet sheet = null;
//Get all sheets and based on passed sheet name get the sheet id
for (int i = 0; i < sheetCount; i++)
{
if (xssfworkbook.GetSheetName(i).Equals(sheetName))
{
sheet = xssfworkbook.GetSheetAt(i);
Logger.Info(string.Concat("User had passed Sheetname: [", sheetName, "]"));
Logger.Info(string.Concat("Fetching the data for sheet : [", sheetName + "]"));
break;
}
}
//Get the column Header
// sheet = xssfworkbook.GetSheetAt(0);
Logger.Debug("Fetching the Test Data header information..");
IRow headerRow = sheet.GetRow(0);
if (null == headerRow)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
throw new ResourceException(methodName);
}
IEnumerator rows = sheet.GetRowEnumerator();
//Get the column and row count
int columnCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;
Logger.Info(string.Concat("Total Column count is : [", rowCount + "]"));
Logger.Info(string.Concat("Total Row count is : [", columnCount + "]"));
//Add the row data table
for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
for (int requiredColumn = 0; requiredColumn < columnNames.Length; requiredColumn++)
{
if (headerRow.GetCell(columnIndex).ToString().Equals(columnNames[requiredColumn]))
{
list.Add(columnIndex);
dataTable.Columns.Add(headerRow.GetCell(columnIndex).ToString());
}
}
}
//Skip reading the Header data
bool skipReadingHeaderRow = rows.MoveNext();
while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
DataRow dataRow = dataTable.NewRow();
foreach (int i in list)
{
ICell cell = row.GetCell(i);
if (cell != null && row.GetCell(0).ToString().Equals(testCaseId))
{
dataRow[i] = cell.ToString();
}
}
dataTable.Rows.Add(dataRow);
}
xssfworkbook = null;
sheet = null;
testDataSet.Tables.Add(dataTable);
}
