private DataGridViewRow BindRow(VItem vitem, DataGridViewRow row = null)
{
if (row == null)
{
row = new DataGridViewRow();
row.Tag = vitem;
row.CreateCells(gv);
}
for (int i = 0; i < VItem.Columns.Count; i++)
{
var vcell = vitem.VCells[i];
if (ChangedCells.Contains(vcell))
{
row.Cells[i].Style.BackColor = Color.SkyBlue;
}
var cell = row.Cells[i];
if (gv.Columns[i].Name == "TYPE & SPECIFICATION")
{
cell.Value = vitem.TypeDescription;
cell.ReadOnly = true;
}
else if (gv.Columns[i].Name == "EQUIP №")
{
cell.Value = vitem.EquipName;
cell.ReadOnly = true;
}
else
{
cell.Value = ExcelTableParser.GetCellValue(vitem.VCells[i]);
cell.Tag = vitem.VCells[i];
}
}
return(row);
}
public List <VItem> Parse(string templateFile)
{
var items = new List <VItem>();
var template = File.ReadAllText(templateFile).Trim();
var lines = template.Split('\n').Select(t => t.Trim()).ToList();
var rowStart = int.Parse(lines[1]);
//var key = int.Parse(lines[3]);
var columns = new Dictionary <string, int>();
for (int i = 3; i < lines.Count; i++)
{
var pair = lines[i].Split(',');
columns.Add(pair[0].Trim(), pair[1].Trim()[0] - 'A');
}
VItem.Columns = columns;
using (var stream = File.OpenRead(FilePath))
{
Workbook = new XSSFWorkbook(stream);
VItem lastItem = null;
for (int i = 0; i < Workbook.Count; i++)
{
var sheet = Workbook.GetSheetAt(i);
if (Regex.IsMatch(sheet.SheetName, @"[\d|\.]+"))
{
for (int r = rowStart - 1; r < sheet.LastRowNum; r++)
{
var row = sheet.GetRow(r);
if (row.GetCell(0) == null)
{
break;
}
var designation = GetCellValue(row.GetCell(columns["DESIGNATION"]));
if (designation == "TOTAL")
{
// AT END
break;
}
var equipName = GetCellValue(row.GetCell(columns["EQUIP №"]));
var subItemName = GetCellValue(row.GetCell(columns["SUB №"]));
var typeName = GetCellValue(row.GetCell(columns["TYPE & SPECIFICATION"])).Trim();
var identity = row.GetCell(columns["ITEM №"]);
if (string.IsNullOrWhiteSpace(GetCellValue(identity)))
{
if (lastItem != null)
{
lastItem.EquipName += equipName;
if (subItemName != null)
{
lastItem.TypeDescription += typeName + "\r\n";
}
}
continue;
}
var item = new VItem(sheet.SheetName, r);
item.EquipName = equipName;
item.TypeDescription = typeName;
item.TypeCategory = Regex.Match(item.EquipName, "[a-zA-Z]+").Groups[0].Value;
if (lastItem != null)
{
lastItem.Rows = item.RowNum - lastItem.RowNum;
}
foreach (var col in columns)
{
item.VCells.Add(row.GetCell(col.Value));
}
items.Add(item);
lastItem = item;
}
lastItem.Rows = sheet.LastRowNum - lastItem.RowNum;
}
}
}
return(items);
}