public Cell(int index, IExcelDataReader reader)
{
Type = reader.GetFieldType(index)?.Name;
Value = reader.GetValue(index);
NumberFormatIndex = reader.GetNumberFormatIndex(index);
NumberFormatString = reader.GetNumberFormatString(index);
}
internal SheetData(IExcelDataReader reader)
{
ColumnCount = reader.FieldCount;
Name = reader.Name;
while (reader.Read())
{
List <string> row = new List <string>(ColumnCount);
bool isNullRow = true;
for (int i = 0; i < ColumnCount; ++i)
{
var val = reader.GetValue(i);
string strVal = "";
if (val is DateTime)
{
var fmt = reader.GetNumberFormatString(i);
strVal = ((DateTime)val).ToString(fmt.Replace('h', 'H'));
}
else
{
strVal = val.ToString();
}
row.Add(strVal);
}
if (!isNullRow)
{
data.Add(row);
}
}
}
private string GetFormattedValue(IExcelDataReader reader, int columnIndex, CultureInfo culture)
{
var value = reader.GetValue(columnIndex);
var formatString = reader.GetNumberFormatString(columnIndex);
if (formatString != null)
{
var format = new NumberFormat(formatString);
return(format.Format(value, culture));
}
return(Convert.ToString(value, culture));
}
public static string GetFormattedValue(this IExcelDataReader reader, int columnIndex, CultureInfo culture = null)
{
var value = reader.GetValue(columnIndex);
var formatString = reader.GetNumberFormatString(columnIndex);
if (formatString != null)
{
var format = new NumberFormat(formatString);
return(format.Format(value, culture ?? new CultureInfo("pt-BR")));
}
return(Convert.ToString(value, culture ?? new CultureInfo("pt-BR")));
}
private static string GetFormattedValue(IExcelDataReader reader, int columnIndex, CultureInfo culture)
{
string result;
var value = reader.GetValue(columnIndex);
var formatString = reader.GetNumberFormatString(columnIndex);
if (formatString != null)
{
var format = new NumberFormat(formatString);
result = format.Format(value, culture);
}
else
{
result = Convert.ToString(value, culture);
}
return(result.Replace('\r', ' ').Replace('\n', ' '));
}
public string GetFormattedValue(IExcelDataReader reader, int columnIndex, CultureInfo culture)
{
var value = reader.GetValue(columnIndex);
var formatString = reader.GetNumberFormatString(columnIndex);
if (formatString != null)
{
var format = new NumberFormat(formatString);
var forstr = format.Format(value, culture);
if (value != null && value.GetType() == typeof(System.Double) && getDoublePointCnt(forstr) > mDoublePointKeepCnt)
{
forstr = Convert.ToDouble(forstr).ToString("F" + mDoublePointKeepCnt.ToString());
forstr = Convert.ToDouble(forstr).ToString("G");
}
return(forstr);
}
return(Convert.ToString(value, culture));
}
private SheetData ReadSheet(IExcelDataReader reader)
{
SheetData sheetData = new SheetData();
int rowIndex = 0;
int columnCount = reader.FieldCount;
while (reader.Read())
{
bool isNullRow = true;
var rowStart = reader.GetValue(0);
//#开头的行是被注释掉的，不读
if (rowStart != null && rowStart.ToString().StartsWith("#"))
{
continue;
}
string[] row = new string[columnCount];
for (int i = 0; i < columnCount; ++i)
{
var val = reader.GetValue(i);
string strVal = "";
if (val != null)
{
isNullRow = false;
if (val is DateTime time)
{
var fmt = reader.GetNumberFormatString(i);
strVal = time.ToString(fmt.Replace('h', 'H'));
}
else
{
strVal = val.ToString();
}
}
row[i] = strVal;
}
//跳过空行
if (isNullRow)
{
continue;
}
if (sheetData.Titiles.Count == 0)
{
//读取标题
for (int i = 0; i < columnCount; ++i)
{
if (!string.IsNullOrWhiteSpace(row[i]))
{
sheetData.Titiles.Add(new ColumnTitle {
Name = row[i], Index = i
});
}
}
}
else
{
RowData rowData = new RowData()
{
RowIndex = rowIndex, Data = new string[sheetData.Titiles.Count]
};
for (int i = 0; i < sheetData.Titiles.Count; ++i)
{
rowData.Data[i] = row[sheetData.Titiles[i].Index];
}
sheetData.Data.Add(rowData);
}
rowIndex++;
}
return(sheetData);
}
private static DataTable AsDataTable(IExcelDataReader self, ExcelDataTableConfiguration configuration, ExcelDataValidatorConfiguration validator, out DataTable errorTable, out DataTable targetTable)
{
var error = new DataTable {
TableName = self.Name + @" Error"
};
var target = new DataTable {
TableName = self.Name + @" Target"
};
var result = new DataTable {
TableName = self.Name
};
error.ExtendedProperties.Add("visiblestate", self.VisibleState);
target.ExtendedProperties.Add("visiblestate", self.VisibleState);
result.ExtendedProperties.Add("visiblestate", self.VisibleState);
var first = true;
var emptyRows = 0;
var columnIndices = new List <int>();
while (self.Read())
{
if (first)
{
if (configuration.UseHeaderRow && configuration.ReadHeaderRow != null)
{
configuration.ReadHeaderRow(self);
}
if (self.FieldCount != configuration.dtFieldColumn.Rows.Count)
{
// return "Số lượng column không hợp lệ";
errorTable = error;
targetTable = target;
return(result);
}
for (var i = 0; i < self.FieldCount; i++)
{
if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i))
{
continue;
}
var name = configuration.UseHeaderRow
? Convert.ToString(self.GetValue(i))
: null;
if (name.ToString() != configuration.dtFieldColumn.Rows[i][0].ToString())
{
//ten cot khong trung
errorTable = error;
targetTable = target;
return(result);
}
if (string.IsNullOrEmpty(name))
{
name = configuration.EmptyColumnNamePrefix + i;
}
// if a column already exists with the name append _i to the duplicates
var columnName = GetUniqueColumnName(result, name);
var column_error = new DataColumn(columnName, typeof(object))
{
Caption = name
};
var column_target = new DataColumn(columnName, typeof(object))
{
Caption = name
};
var column = new DataColumn(columnName, typeof(object))
{
Caption = name
};
error.Columns.Add(column_error);
target.Columns.Add(column_target);
result.Columns.Add(column);
columnIndices.Add(i);
}
var column3 = new DataColumn(@"Error_Excel", typeof(string))
{
Caption = @"Error_Excel"
};
error.Columns.Add(column3);
error.BeginLoadData();
target.BeginLoadData();
result.BeginLoadData();
first = false;
if (configuration.UseHeaderRow)
{
continue;
}
}
if (configuration.FilterRow != null && !configuration.FilterRow(self))
{
continue;
}
if (IsEmptyRow(self, configuration))
{
emptyRows++;
continue;
}
for (var i = 0; i < emptyRows; i++)
{
result.Rows.Add(result.NewRow());
}
emptyRows = 0;
StringBuilder hasError = new StringBuilder(String.Empty);
var row = result.NewRow();
for (var i = 0; i < columnIndices.Count; i++)
{
var columnIndex = columnIndices[i];
var columnName = columnIndices[i];
var value = self.GetValue(columnIndex);
if (configuration.TransformValue != null)
{
var transformedValue = configuration.TransformValue(self, i, value);
if (transformedValue != null)
{
value = transformedValue;
}
}
var formatString = self.GetNumberFormatString(columnIndex);
if (value != null)
{
if (formatString != null)
{
var format = new NumberFormat(formatString);
value = format.Format(value, CultureInfo.InvariantCulture);
}
}
//else
//{
// value = Convert.ToString(value, CultureInfo.InvariantCulture);
//}
if (validator.ValidateCell != null)
{
try
{
var message = validator.ValidateCell(self, i, value);
if (!String.IsNullOrWhiteSpace(message))
{
hasError.Append($"Column {error.Columns[columnIndex].ColumnName} - {message}, ");
}
}
catch
{
var message = "Invalid Format";
hasError.Append($"Column {error.Columns[columnIndex].ColumnName} - {message}, ");
}
}
row[i] = value;
}
result.Rows.Add(row);
if (hasError.Length < 1)
{
var target_row = target.NewRow();
target_row.ItemArray = row.ItemArray;
target.Rows.Add(target_row);
}
else
{
var error_row = error.NewRow();
error_row.ItemArray = row.ItemArray;
error_row[columnIndices.Count] = hasError;
error.Rows.Add(error_row);
}
}
result.EndLoadData();
target.EndLoadData();
error.EndLoadData();
errorTable = error;
targetTable = target;
return(result);
}
