public void MakeSheet(IEnumerable <DataRow> rows)
{
ISheet sh1 = WBC.WB.CreateSheet(SheetName);
var row0 = sh1.CreateRow(0);
for (int i = 0; i < FieldsConfig.Count; i++)
{
var fc = FieldsConfig[i];
var cell = row0.CreateCell(i);
cell.CellStyle = WBC.CellStyleCations;
cell.SetCellValue(FieldsConfig[i].Caption);
sh1.SetColumnWidth(i, fc.WidthInChars * 256);
if (fc.CellStyle != null)
{
sh1.SetDefaultColumnStyle(i, fc.CellStyle);
}
}
int ct = 1;
foreach (var dr in rows)
{
var row = sh1.CreateRow(ct);
ct++;
for (int i = 0; i < FieldsConfig.Count; i++)
{
var fc = FieldsConfig[i];
object value = null;
if (fc.FieldHelper == null)
{
value = dr[fc.FieldName];
}
else
{
value = fc.FieldHelper.GetFieldValue(fc.FieldName, dr);
}
var cell = row.CreateCell(i);
SetCellValue(cell, fc, value);
}
WBC.RowsDone++;
WBC.OnProgress();
if (WBC.Cancel)
{
return;
}
}
sh1.CreateFreezePane(0, 1);
}
public string ReadSheet()
{
ISheet sh1 = WBC.WB.GetSheet(SheetName);
var row0 = sh1.GetRow(0);
var valuerow = new object[FieldsConfig.Count];
Prepare();
for (int i = 1; i < sh1.PhysicalNumberOfRows; i++)
{
var row = sh1.GetRow(i);
for (int j = 0; j < FieldsConfig.Count; j++)
{
object value = null;
var fc = FieldsConfig[j];
var cell = row.GetCell(j);
string er = null;
if (cell == null)
{
er = GetBlancCellValue(fc, ref value);
}
else
{
er = GetCellValue(cell, fc, out value);
}
valuerow[j] = value;
if (er != "OK")
{
return($"Sheet:{SheetName}, Row:{i}, col:{j}, {er}");
}
}
try
{
UseRow(valuerow);
}
catch (Exception ex)
{
return($"Sheet:{SheetName}, Row:{i}, {ex.Message}");
}
WBC.RowsDone++;
WBC.OnProgress();
if (WBC.Cancel)
{
return("OK");
}
}
return("OK");
}
public List <CellError> TestSheet()
{
RowCount = 0;
RowCountNew = 0;
RowCountExisting = 0;
RowCountChanging = 0;
RowCountBad = 0;
var ret = new List <CellError>();
ISheet sh1 = WBC.WB.GetSheet(SheetName);
if (sh1 == null)
{
ret.Add(new CellError(SheetName, 0, 0, "Nav tādas lapas."));
return(ret);
}
if (sh1.PhysicalNumberOfRows == 0)
{
ret.Add(new CellError(SheetName, 0, 0, "Nav datu."));
return(ret);
}
RowCount = sh1.PhysicalNumberOfRows - 1;
var row0 = sh1.GetRow(0);
if (row0.Cells.Count != FieldsConfig.Count)
{
ret.Add(new CellError(SheetName, 0, 0, "Nekorekts kolonu skaits."));
return(ret);
}
for (int i = 0; i < FieldsConfig.Count; i++)
{
var fc = FieldsConfig[i];
var cell = row0.Cells[i];
if (cell.CellType != CellType.String || cell.StringCellValue != fc.Caption)
{
ret.Add(new CellError(SheetName, 0, i, "Nekorekts kolonnas nosaukums."));
}
}
if (ret.Count > 0)
{
return(ret);
}
var valuerow = new object[FieldsConfig.Count];
for (int i = 1; i < sh1.PhysicalNumberOfRows; i++)
{
var row = sh1.GetRow(i);
if (row == null)
{
ret.Add(new CellError(SheetName, i, 0, "Tukša rinda."));
return(ret);
}
bool badrow = false;
for (int j = 0; j < FieldsConfig.Count; j++)
{
object value = null;
var fc = FieldsConfig[j];
var cell = row.GetCell(j);
string er = null;
if (cell == null)
{
er = GetBlancCellValue(fc, ref value);
}
else
{
er = GetCellValue(cell, fc, out value);
}
valuerow[j] = value;
if (er != "OK")
{
badrow = true;
ret.Add(new CellError(SheetName, i, j, er));
}
}
if (badrow)
{
RowCountBad++;
}
else
{
var rowkind = GetRowKind(valuerow);
switch (rowkind)
{
case ERowKind.New:
RowCountNew++;
break;
case ERowKind.Existing:
RowCountExisting++;
break;
case ERowKind.Changing:
RowCountChanging++;
break;
}
}
WBC.RowsDone++;
WBC.OnProgress();
if (WBC.Cancel)
{
return(ret);
}
}
return(ret);
}