/// <summary>
/// Helper method for creating a list of ktExaminedGroup
/// from an Excel worksheet.
/// </summary>
public bool LoadUIOrder(Worksheet worksheet,
SharedStringTable sharedString)
{
Result = new List <ktUIOrder>();
//Linq query to get the column headers on the sheet
Row columnRow =
(from row in worksheet.Descendants <Row>()
where row.RowIndex == 1
select row).First();
IEnumerable <String> headerValues =
from cell in columnRow.Descendants <Cell>()
where cell.CellValue != null
select
(cell.DataType != null &&
cell.DataType.HasValue &&
cell.DataType == CellValues.SharedString
?sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
foreach (string header in headerValues)
{
ColumnNames.Add(header);
}
if (CheckColumnNames(ColumnNames))
{
//LINQ query to skip first row with column names.
IEnumerable <Row> dataRows =
from row in worksheet.Descendants <Row>()
where row.RowIndex > 1
select row;
if (CheckDataInSheets(dataRows))
{
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
//IEnumerable<String> textValues =
// from cell in row.Descendants<Cell>()
// where cell.CellValue != null
// select
// (cell.DataType != null
// && cell.DataType.HasValue
// && cell.DataType == CellValues.SharedString
// ? sharedString.ChildElements[
// int.Parse(cell.CellValue.InnerText)].InnerText
// : cell.CellValue.InnerText)
// ;
IEnumerable <String> textValues =
from cell in row.Descendants <Cell>()
where cell.CellValue != null
select
(cell.DataType != null &&
cell.DataType.HasValue &&
cell.DataType == CellValues.SharedString
?sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText)
;
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
if (textValues.Count() == 5)
{
//Create a ktUIOrder and add it to the list.
var textArray = textValues.ToArray();
ktUIOrder order = new ktUIOrder();
order.DesignID = textArray[0];
order.GroupOrder = double.Parse(textArray[1], CultureInfo.InvariantCulture); // Convert.ToDouble(textArray[1]);
order.GroupTypeID = textArray[2];
order.PageTypeID = textArray[3];
order.IncludedTypeID = textArray[4];
Result.Add(order);
}
else
{
var textArray = textValues.ToArray();
ktUIOrder order = new ktUIOrder();
order.DesignID = textArray[0];
order.GroupOrder = double.Parse(textArray[1], CultureInfo.InvariantCulture); // Convert.ToDouble(textArray[1]);
order.GroupTypeID = textArray[2];
order.PageTypeID = "";
order.IncludedTypeID = textArray[4];
Result.Add(order);
}
}
else
{
//If no cells, then you have reached the end of the table.
break;
}
}
}
else
{
return(false);
}
return(true);
}
return(false);
}
/// <summary>
/// Helper method for creating a list of ktExaminedGroup
/// from an Excel worksheet.
/// </summary>
public bool LoadUIOrder(Worksheet worksheet,
SharedStringTable sharedString)
{
Result = new List<ktUIOrder>();
//Linq query to get the column headers on the sheet
Row columnRow =
(from row in worksheet.Descendants<Row>()
where row.RowIndex == 1
select row).First();
IEnumerable<String> headerValues =
from cell in columnRow.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
foreach (string header in headerValues)
{
ColumnNames.Add(header);
}
if (CheckColumnNames(ColumnNames))
{
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows =
from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
if (CheckDataInSheets(dataRows))
{
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
//IEnumerable<String> textValues =
// from cell in row.Descendants<Cell>()
// where cell.CellValue != null
// select
// (cell.DataType != null
// && cell.DataType.HasValue
// && cell.DataType == CellValues.SharedString
// ? sharedString.ChildElements[
// int.Parse(cell.CellValue.InnerText)].InnerText
// : cell.CellValue.InnerText)
// ;
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText)
;
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
if (textValues.Count() == 5)
{
//Create a ktUIOrder and add it to the list.
var textArray = textValues.ToArray();
ktUIOrder order = new ktUIOrder();
order.DesignID = textArray[0];
order.GroupOrder = double.Parse(textArray[1], CultureInfo.InvariantCulture); // Convert.ToDouble(textArray[1]);
order.GroupTypeID = textArray[2];
order.PageTypeID = textArray[3];
order.IncludedTypeID = textArray[4];
Result.Add(order);
}
else
{
var textArray = textValues.ToArray();
ktUIOrder order = new ktUIOrder();
order.DesignID = textArray[0];
order.GroupOrder = double.Parse(textArray[1], CultureInfo.InvariantCulture); // Convert.ToDouble(textArray[1]);
order.GroupTypeID = textArray[2];
order.PageTypeID = "";
order.IncludedTypeID = textArray[4];
Result.Add(order);
}
}
else
{
//If no cells, then you have reached the end of the table.
break;
}
}
}
else
{
return false;
}
return true;
}
return false;
}
