private ConvertedDatatable GetTableFromRange(Excel.Range range)
{
Excel.Worksheet ws = range.Worksheet;
//Lets discover the end range.
int ColumnCount = ws.UsedRange.Columns.Count;
int RowCount = ws.UsedRange.Rows.Count;
Excel.Range endRange = ws.Cells[RowCount, ColumnCount];
Excel.Range testRange = ws.Range[range, endRange];
ConvertedDatatable res = new ConvertedDatatable
{
Cells = testRange.Value2,
LastRow = testRange.Rows.Count,
LastColumn = testRange.Columns.Count
};
return(res);
}
public bool LoadQuestionsFromFile(string filePath, out List <QuestionField> res)
{
if (OpenFile(filePath))
{
Excel.Worksheet workSheet = xlWorkbook.Sheets[1];
Excel.Range rng = workSheet.UsedRange;
mainTable = GetTableFromRange(rng);
for (int x = 1; x <= mainTable.LastRow; x++)
{
for (int y = 1; y <= mainTable.LastColumn; y++)
{
if (mainTable.Cells[x, y] == null)
{
continue;
}
if (mainTable.Cells[x, y].ToString() == "Valdkond->")
{
FieldColumn = y;
FieldFirstRow = x + 1;
}
if (mainTable.Cells[x, y].ToString() == "Küsimus")
{
QuestionColumn = y;
}
if (mainTable.Cells[x, y].ToString() == "Vastus")
{
AnswerColumn = y;
}
if (mainTable.Cells[x, y].ToString() == "Pilt")
{
PictureColumn = y;
}
}
// We have found them all...
if (FieldColumn > 0 && AnswerColumn > 0 && QuestionColumn > 0 && PictureColumn > 0)
{
Console.WriteLine("FieldColumn : " + FieldColumn + " AnswerColumn : " + AnswerColumn + "QuestionColumn : " + QuestionColumn);
break;
}
}
//Lets move forward in the file.
int currentRow = FieldFirstRow;
List <QuestionField> parsedList = new List <QuestionField>();
while (currentRow < mainTable.LastRow)
{
if (mainTable.Cells[currentRow, FieldColumn] != null)
{
//Lets do this in a simple way, we just assume we found a field.
QuestionField field = parseQuestionField(currentRow, FieldColumn);
parsedList.Add(field);
currentRow += 6;
}
else
{
currentRow++;
}
}
Console.WriteLine("Parsed {0} fields from excel file", parsedList.Count);
res = parsedList;
return(true);
}
else
{
res = null;
return(false);
}
}
