private int FindTableID(HallElement hall, string key)
{
foreach (string name in hall.Rows.AllKeys)
{
if (name == key)
{
return(Convert.ToInt32(hall.Rows[name].Value));
}
}
return(0);
}
public void Import(ImportSection importSection, DateTime meetingDate, string hallName, HSSFSheet sourceSheet, ref StringBuilder sql)
{
HallElement hall = importSection.Halls[hallName];
string leftHeaderString = string.Empty;
string leftSubHeaderString = string.Empty;
string left2SubHeaderString = string.Empty;
string topHeaderString = string.Empty;
string topSubHeaderString = string.Empty;
string top2SubHeaderString = string.Empty;
using (DataTable lifeNumberTable = InitializeTable(importSection.Cols))
{
for (int dataRowIndex = hall.SourceLeftHeaderRow; ; dataRowIndex++)
{
HSSFRow sourceTopHeaderRow = sourceSheet.GetRow(hall.SourceTopHeaderRow) as HSSFRow;
HSSFRow sourceTopSubHeaderRow = sourceSheet.GetRow(hall.SourceTopSubHeaderRow) as HSSFRow;
HSSFRow sourceTop2SubHeaderRow = sourceSheet.GetRow(hall.SourceTopSubHeaderRow + 1) as HSSFRow;
HSSFRow sourceDataRow = sourceSheet.GetRow(dataRowIndex) as HSSFRow;
string key = string.Empty;
if (!string.IsNullOrEmpty(sourceDataRow.GetCell(hall.SourceLeftHeaderCol).StringCellValue))
{
leftHeaderString = sourceDataRow.GetCell(hall.SourceLeftHeaderCol).StringCellValue;
}
if (leftHeaderString == "總計")
{
break;
}
if (leftHeaderString == "合計")
{
continue;
}
switch (sourceDataRow.GetCell(hall.SourceLeftSubHeaderCol).CellType)
{
case CellType.String:
leftSubHeaderString = sourceDataRow.GetCell(hall.SourceLeftSubHeaderCol).StringCellValue;
break;
case CellType.Numeric:
leftSubHeaderString = Convert.ToString(sourceDataRow.GetCell(hall.SourceLeftSubHeaderCol).NumericCellValue);
break;
}
switch (sourceDataRow.GetCell(hall.SourceLeftSubHeaderCol + 1).CellType)
{
case CellType.String:
left2SubHeaderString = sourceDataRow.GetCell(hall.SourceLeftSubHeaderCol + 1).StringCellValue;
break;
case CellType.Numeric:
left2SubHeaderString = Convert.ToString(sourceDataRow.GetCell(hall.SourceLeftSubHeaderCol + 1).NumericCellValue);
break;
}
//if (left2SubHeaderString == "區小計")
//{
key = string.Format("{0}{1}{2}", leftHeaderString, importSection.Seperator, leftSubHeaderString);
//}
//else
//{
// key = string.Format("{0}{1}{2}{1}{3}", leftHeaderString, importSection.Seperator, leftSubHeaderString, left2SubHeaderString);
//}
DataRow dataRow = lifeNumberTable.NewRow();
dataRow["MeetingDate"] = meetingDate;
dataRow["TableID"] = FindTableID(hall, key);
dataRow["BaseNum"] = sourceDataRow.GetCell(hall.SourceLeftSubHeaderCol + 2).NumericCellValue;
dataRow["TargetNum"] = sourceDataRow.GetCell(hall.SourceLeftSubHeaderCol + 3).NumericCellValue;
for (int dataColIndex = hall.SourceTopHeaderCol; dataColIndex < sourceTopHeaderRow.LastCellNum; dataColIndex++)
{
string value;
if (!string.IsNullOrEmpty(sourceTopHeaderRow.GetCell(dataColIndex).StringCellValue))
{
topHeaderString = sourceTopHeaderRow.GetCell(dataColIndex).StringCellValue;
topSubHeaderString = string.Empty;
top2SubHeaderString = string.Empty;
}
if (!string.IsNullOrEmpty(sourceTopSubHeaderRow.GetCell(dataColIndex).StringCellValue))
{
topSubHeaderString = sourceTopSubHeaderRow.GetCell(dataColIndex).StringCellValue;
}
if (!string.IsNullOrEmpty(sourceTop2SubHeaderRow.GetCell(dataColIndex).StringCellValue))
{
top2SubHeaderString = sourceTop2SubHeaderRow.GetCell(dataColIndex).StringCellValue;
value = FindField(importSection.Cols, string.IsNullOrEmpty(top2SubHeaderString) ? topHeaderString : string.Format("{0}{1}{2}{1}{3}", topHeaderString, importSection.Seperator, topSubHeaderString, top2SubHeaderString));
}
else
{
value = FindField(importSection.Cols, string.IsNullOrEmpty(topSubHeaderString) ? topHeaderString : string.Format("{0}{1}{2}", topHeaderString, importSection.Seperator, topSubHeaderString));
}
if (!string.IsNullOrEmpty(value))
{
dataRow[value] = sourceDataRow.GetCell(dataColIndex).NumericCellValue;
}
}
WriteTo(lifeNumberTable, dataRow, ref sql);
}
}
}
