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); }