private void readPivotTable(XSSFSheet sheet)
{
List <XSSFPivotTable> tables = sheet.GetPivotTables();
if (tables.Count > 0)
{
try
{
CT_PivotTableDefinition cT_PivotTableDefinition = tables[0].GetCTPivotTableDefinition();
parseLocation(cT_PivotTableDefinition.location.@ref);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
logger.logException(ex);
}
}
}
public List <List <object> > readSheet_ReturnLists(int offSetRowPlus, int offSetRowMinus, int startingRow, int startingColumn)
{
if (sheet.GetType() == typeof(XSSFWorkbook))
{
XSSFSheet sheetTemp = sheet as XSSFSheet;
List <XSSFPivotTable> tables = sheetTemp.GetPivotTables();
readPivotTable(sheetTemp);
}
if (lastPivotRow == 0)
{
firstPivotRow = startingRow;
lastPivotColumn = sheet.GetRow(startingRow).LastCellNum;
ICell currentLastCell = sheet.GetRow(startingRow).GetCell(lastPivotColumn);
while (currentLastCell == null || string.IsNullOrWhiteSpace(currentLastCell.ToString()))
{
lastPivotColumn--;
currentLastCell = sheet.GetRow(startingRow).GetCell(lastPivotColumn);
}
if (currentLastCell != null)
{
lastPivotColumn++;
}
//Console.WriteLine(sheet.LastRowNum);
lastPivotRow = sheet.LastRowNum + 1;
for (int counter = startingRow; counter < sheet.LastRowNum; counter++)
{
IRow row = sheet.GetRow(counter);
if (row == null)
{
lastPivotRow = counter;
break;
}
if (row.GetCell(startingColumn) == null || string.IsNullOrEmpty(row.GetCell(startingColumn).ToString()))
{
lastPivotRow = counter;
break;
}
}
}
List <List <object> > tableInfo = new List <List <object> >();
try
{
for (int rowCounter = firstPivotRow + offSetRowPlus, counter = 0; rowCounter < lastPivotRow - offSetRowMinus; rowCounter++, counter++)
{
IRow row = sheet.GetRow(rowCounter);
List <object> tempRow = new List <object>();
//DateTime.TryParse(row.GetCell(38).DateCellValue.ToString(), out date);
for (int colCounter = startingColumn; colCounter < lastPivotColumn; colCounter++)
{
if (row.GetCell(colCounter) != null)
{
tempRow.Add(row.GetCell(colCounter));
}
else
{
tempRow.Add(String.Empty);
}
}
tableInfo.Add(tempRow);
}
}
catch (Exception ex)
{
logger.logException(ex);
}
#if Debug
Console.WriteLine(tableInfo[tableInfo.Count - 1][0, 2]);
#endif
resetVaules();
return(tableInfo);
}
public object[,] readSheet(int offSetRowPlus, int offSetRowMinus, int startingRow, int startingColumn)
{
Console.WriteLine(sheet.GetType());
if (!isXlsSheet)
{
XSSFSheet sheetTemp = sheet as XSSFSheet;
List <XSSFPivotTable> tables = sheetTemp.GetPivotTables();
readPivotTable(sheetTemp);
}
if (lastPivotRow == 0)
{
firstPivotRow = startingRow;
lastPivotColumn = sheet.GetRow(startingRow).LastCellNum;
ICell currentLastCell = sheet.GetRow(startingRow).GetCell(lastPivotColumn);
while (currentLastCell == null || string.IsNullOrWhiteSpace(currentLastCell.ToString()))
{
lastPivotColumn--;
currentLastCell = sheet.GetRow(startingRow).GetCell(lastPivotColumn);
}
if (currentLastCell != null)
{
lastPivotColumn++;
}
//Console.WriteLine(sheet.LastRowNum);
lastPivotRow = sheet.LastRowNum + 1;
for (int counter = startingRow; counter < sheet.LastRowNum; counter++)
{
IRow row = sheet.GetRow(counter);
if (row.GetCell(startingColumn) == null || string.IsNullOrEmpty(row.GetCell(startingColumn).ToString()))
{
lastPivotRow = counter;
break;
}
}
}
object[,] tableInfo = new object[lastPivotRow - firstPivotRow - offSetRowMinus, lastPivotColumn];
try
{
for (int rowCounter = firstPivotRow + offSetRowPlus, counter = 0; rowCounter < lastPivotRow - offSetRowMinus; rowCounter++, counter++)
{
try
{
IRow row = sheet.GetRow(rowCounter);
for (int colCounter = 0; colCounter < lastPivotColumn; colCounter++)
{
try
{
if (row.GetCell(colCounter) != null)
{
tableInfo[counter, colCounter] = row.GetCell(colCounter).ToString();
}
}
catch (Exception ex)
{
//The cell is null and there it no way to catch with the if statement
logger.logException(ex);
logger.addTextToLogFile("Row: " + rowCounter + " Column: " + colCounter + " Object not instantiated");
}
}
}
catch (Exception ex)
{
logger.logException(ex);
logger.addTextToLogFile("Row: " + rowCounter);
}
}
}
catch (Exception ex)
{
logger.logException(ex);
}
#if Debug
Console.WriteLine(tableInfo[tableInfo.Count - 1][0, 2]);
#endif
resetVaules();
return(tableInfo);
}