private int GetInt(IExcelDataReader reader, int index)
{
if (typeof(string) == reader.GetFieldType(index))
{
string str = reader.GetString(index);
bool isNumeric = int.TryParse(str, out int pin);
if (!isNumeric && str != null && str != "")
{
throw new Exception("Excel файлын тоо агуулах ёстой баганад өөр төрлийн мэдээлэл орсон байна. Мөрийн дугаар: " + index.ToString());
}
else if (str == null)
{
return(-1);
}
return(pin);
}
else if (typeof(double) == reader.GetFieldType(index))
{
return((int)reader.GetDouble(index));
}
else if (reader.GetFieldType(index) == null)
{
return(-1);
}
else
{
throw new Exception("Excel файлын тоо агуулах ёстой баганад өөр төрлийн мэдээлэл орсон байна. Мөрийн дугаар: " + index.ToString());
}
}
public static float GetFloat(int i)
{
var type = reader.GetFieldType(i);
if (type == typeof(double))
{
return (float)reader.GetDouble(i);
}
if (type == typeof(float))
{
return reader.GetFloat(i);
}
if (type == typeof(int))
{
return reader.GetInt32(i);
}
if (float.TryParse(reader.GetValue(i)?.ToString(), out float value))
{
return value;
}
throw Exception("Can't parse number from column " + (i + 1));
}
private bool filterTable(IExcelDataReader rowReader, int index)
{
if (rowReader.GetFieldType(index) == null)
{
return(false);
}
else if (rowReader.GetFieldType(index) == typeof(string))
{
var value = rowReader.GetString(index);
if (string.IsNullOrEmpty(value) || string.IsNullOrWhiteSpace(value) || value.Contains("//") == true)
{
return(false);
}
}
return(true);
}
private void ReadColumn(IExcelDataReader reader, int colPosition, out object value, out Type valueType)
{
switch (Type.GetTypeCode(reader.GetFieldType(colPosition)))
{
case TypeCode.String:
{
valueType = typeof(string);
value = reader.GetString(colPosition) as string;
}
break;
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Double:
{
valueType = typeof(double);
value = reader.GetDouble(colPosition);
}
break;
default:
{
valueType = typeof(object);
value = null;
}
break;
}
}
/// <summary>
/// Format a cell as a string before we pass to the type converter, so any formatting done in Excel
/// is properly applied to the string result that is then sent to the users parser.
/// </summary>
/// <param name="index">Column in the excel file</param>
/// <param name="capitalBoolean">True to use capital boolean format, false to use C# style</param>
/// <returns>Cell value formatted as a string, or the original value if not</returns>
private string FormatValueAsString(
int index,
bool capitalBoolean)
{
var value = _reader.GetValue(index);
if (value != null)
{
// For compatibility with old PHP code, format TRUE and FALSE for boolean values
var type = _reader.GetFieldType(index);
if (type == typeof(bool))
{
if ((bool)value)
{
return(capitalBoolean ? "TRUE" : "True");
}
return(capitalBoolean ? "FALSE" : "False");
}
// To ensure DateTime values make sense in any culture, we render them in ISO 8601 format
if (type == typeof(DateTime))
{
return(((DateTime)value).ToString("o"));
}
// TODO: We currently do not have a way to get real formatting out of the Excel file cell,
// so to do this correctly we really need to find a way to get that, and then format it.
return(value.ToString().Trim());
}
// Always return empty strings, not nulls
return(string.Empty);
}
private int Integerize(IExcelDataReader reader, int position)
{
int result = 0;
var fileType = reader.GetFieldType(position);
if (fileType == null)
{
return(0);
}
if (fileType.Equals(typeof(System.Int32)))
{
return(reader.GetInt32(position));
}
if (fileType.Equals(typeof(System.Double)))
{
return(Convert.ToInt32(reader.GetDouble(position)));
}
if (fileType.Equals(typeof(System.String)))
{
int.TryParse(reader.GetString(position), out result);
return(result);
}
return(0);
}
public Cell(int index, IExcelDataReader reader)
{
Type = reader.GetFieldType(index)?.Name;
Value = reader.GetValue(index);
NumberFormatIndex = reader.GetNumberFormatIndex(index);
NumberFormatString = reader.GetNumberFormatString(index);
}
public bool InvalidCellData()
{
using (ReadAndStreamFile())
{
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
var dataTable = dataSet.Tables[0];
var rows = Enumerable.Range(0, reader.FieldCount).Select(i => reader.Read()).ToArray();
errorDetected = false;
for (var ColumnIndexNumber = 0; ColumnIndexNumber < dataTable.Columns.Count; ColumnIndexNumber++)
{
for (var RowIndexNumber = 0; RowIndexNumber < dataTable.Rows.Count; RowIndexNumber++)
{
//RowIndexNumber is row number starts from index number 0
//ColumnIndexNumber is column number starts from index number 0
var data = dataTable.Rows[RowIndexNumber][ColumnIndexNumber];
var excel = new ExcelDataReaderFile();
var cellAddress = excel.GetAddress(ColumnIndexNumber, RowIndexNumber + 1);
if (data.ToString().Length != 0)
{
if (data.GetType() == reader.GetFieldType(ColumnIndexNumber) && data.ToString().Length > rowDataSizeLimit)
{
Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is {data.ToString().Length} characters long and exceeds {rowDataSizeLimit} character cell contents limit. Supply valid cell contents.");
errorDetected = true;
}
else if (data.ToString().Length <= rowDataSizeLimit & data.GetType() != reader.GetFieldType(ColumnIndexNumber))
{
Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} data {data} data type {data.GetType()} does not match data type of column data {reader.GetFieldType(ColumnIndexNumber)}. Supply data with a consistent data type.");
errorDetected = true;
}
else if (data.ToString().Length > rowDataSizeLimit & data.GetType() != reader.GetFieldType(ColumnIndexNumber))
{
Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is {data.ToString().Length} characters long and exceeds {rowDataSizeLimit} character cell contents limit. Supply valid cell contents. Data type {data.GetType()} does not match data type of column data {reader.GetFieldType(ColumnIndexNumber)}. Supply data with a consistent data type.");
errorDetected = true;
}
else
{
}
}
//else
//{
// Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is empty. Supply valid cell data.");
// errorDetected = false;
//}
}
}
reader.Dispose();
reader.Close();
return(errorDetected);
}
}
private IEnumerable <IRow> GetRows()
{
int line = 0;
string oldSheet = null;
int rowNumber = 0;
do
{
if (IsSheetMatch(_reader.Name))
{
while (_reader.Read())
{
var results = new string[_reader.FieldCount];
for (int i = 0; i < _reader.FieldCount; ++i)
{
if (_reader.GetFieldType(i) == null)
{
results[i] = "";
}
else
{
if (!_fieldConverters.TryGetValue(_reader.GetFieldType(i), out var fieldConverter))
{
throw new UnsupportedDataTypeException($"Data Type \"{_reader.GetFieldType(i).Name}\" not supported ({_reader.Name}:{i.ToColumnRef()}{rowNumber}).");
}
results[i] = fieldConverter(_reader, i);
}
}
if (_reader.Name != oldSheet)
{
oldSheet = _reader.Name;
rowNumber = 0;
}
yield return(new SpreadsheetRow(++line, ++rowNumber, oldSheet, results));
}
}
} while (_reader.NextResult());
}
private void ButtonLoad_Click(object sender, EventArgs e)
{
using (OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xls;*.xlsx", ValidateNames = true
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
try
{
MessageBox.Show(ofd.FileName);
fileStream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read); //Dosya Kullanımdaysa hata veriyor..
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
isFileLoaded = true;
LabelFileName.Text = ofd.SafeFileName;
path = ofd.FileName.Replace(ofd.SafeFileName, "");
IExcelDataReader reader = ExcelReaderFactory.CreateReader(fileStream);
result = reader.AsDataSet();
cellTypes = new Dictionary <int, Type[, ]>();
for (int workSheetIndex = 0; workSheetIndex < reader.ResultsCount; workSheetIndex++)
{
Type[,] types = new Type[reader.RowCount, reader.FieldCount];
int rowIndex = 0;
while (reader.Read())
{
for (int columnIndex = 0; columnIndex <= reader.FieldCount - 1; columnIndex++)
{
types[rowIndex, columnIndex] = reader.GetFieldType(columnIndex);
}
rowIndex++;
}
cellTypes.Add(workSheetIndex, types);
reader.NextResult();
}
reader.Close();
reader.Dispose();
reader = null;
}
}
}
private string ParseCell(IExcelDataReader reader, int columnToParse)
{
//GetFieldType() returns the type of a value in the current row.Always one of the types supported by Excel:
//double, int, bool, DateTime, TimeSpan, string, or null if there is no value.
var fieldType = reader.GetFieldType(columnToParse);
if (fieldType == null)
{
return(null);
}
string result = null;
if (fieldType == typeof(double))
{
var value = reader.GetDouble(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(int))
{
var value = reader.GetInt32(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(bool))
{
var value = reader.GetBoolean(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(DateTime))
{
var value = reader.GetDateTime(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(TimeSpan))
{
Console.WriteLine("TimeSpan parsing is not implemented");
//var value = reader.GetTimeS(0);
//data[key] = value;
}
else if (fieldType == typeof(string))
{
var value = reader.GetString(columnToParse);
result = value;
}
else
{
Console.WriteLine($"unknown type from Excel field: {0}", fieldType);
}
return(result);
}
private void FillPropertyFromParsedField(IExcelDataReader reader, object newObject, int col, PropertyInfo targetProperty)
{
var fieldType = reader.GetFieldType(col);
if (fieldType == typeof(string))
{
ParseString(reader.GetString(col), newObject, targetProperty);
}
else if (fieldType == typeof(double))
{
ParseDouble(reader.GetDouble(col), newObject, targetProperty);
}
else if (fieldType == typeof(int))
{
ParseInt(reader.GetInt32(col), newObject, targetProperty);
}
else if (fieldType == typeof(bool))
{
ParseBool(reader.GetBoolean(col), newObject, targetProperty);
}
// Only other possibility is that type is null. Do nothing in this case.
}
public IEnumerable <Order> Map(IExcelDataReader dataReader, string delimiter)
{
_logger.LogInformation($"Map using OrderMapper.");
var quantity = dataReader.GetValue(70) == null ? 1 : (int)dataReader.GetDouble(70);
_logger.LogInformation("Order, quantity: {0}", quantity);
if (quantity > 1)
{
var productNameColumnContent = dataReader.GetString(57);
var hasDelimiters = productNameColumnContent.IndexOf(delimiter) > 0;
_logger.LogInformation("Split productname: {0}", hasDelimiters);
if (hasDelimiters)
{
var productNames = productNameColumnContent.Split(delimiter);
var prices = (dataReader.GetFieldType(62) == typeof(string) ? dataReader.GetString(62) : dataReader.GetDouble(62).ToString()).Split(delimiter);
for (int i = 0; i < quantity; i++)
{
var result = Read(dataReader);
result.ProductName = productNames[i];
result.ProductSalesPrice = prices[i];
yield return(result);
}
}
else
{
yield return(Read(dataReader));
}
}
else
{
yield return(Read(dataReader));
}
}
static string tablePath;//테이블의 위치 경로.
static void Main(string[] args)
{
if (args.Length == 0)
{
MessageBox.Show("args가 널");
Console.WriteLine("args가 널");
return;
}
string strSrc = args[0];
string strDest = args[1];
fileName = strSrc;
dirName = strDest;
tablePath = Path.GetDirectoryName(strDest);
//MessageBox.Show("fileName : " + fileName + " / " + "dirName : " + dirName + " / tablePath : " + tablePath);
string fName = string.Format("{0}/{1}", tablePath, fileName);
try
{
fileStream = File.Open(fName, FileMode.Open, FileAccess.Read);
}
catch (Exception ex)
{
MessageBox.Show("예외 : " + ex.Message);
return;
}
IExcelDataReader reader = ExcelReaderFactory.CreateReader(fileStream);
result = reader.AsDataSet();
cellTypes = new Dictionary <int, Type[, ]>();
for (int workSheetIndex = 0; workSheetIndex < reader.ResultsCount; workSheetIndex++)
{
Type[,] types = new Type[reader.RowCount, reader.FieldCount];
int rowIndex = 0;
while (reader.Read())
{
for (int columnIndex = 0; columnIndex <= reader.FieldCount - 1; columnIndex++)
{
types[rowIndex, columnIndex] = reader.GetFieldType(columnIndex);
}
rowIndex++;
}
cellTypes.Add(workSheetIndex, types);
reader.NextResult();
}
reader.Close();
reader.Dispose();
reader = null;
//Convert....
string TEXTJSON = "{\r\n";
string comma;
List <string> headers;
string items = "";
for (int tableIndex = 0; tableIndex < result.Tables.Count; tableIndex++)
{
TEXTJSON += "\t\"" + result.Tables[tableIndex].TableName + "\": [\r\n";
var table = result.Tables[tableIndex];
headers = new List <string>();
for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{
headers.Add("\"" + table.Rows[0][columnIndex] + "\": ");
}
items = "";
for (int rowIndex = 1; rowIndex < table.Rows.Count; rowIndex++)
{
items += "\t\t{\r\n";
for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{
comma = "";
if (columnIndex < table.Columns.Count - 1)
{
comma += ",";
}
string text = "\t\t\t" + headers[columnIndex] + GetFixedType(tableIndex, table, rowIndex, columnIndex) + comma + "\r\n";
items += text;
}
comma = "";
if (rowIndex < table.Rows.Count - 1)
{
comma += ",";
}
items += "\t\t}" + comma + "\r\n";
}
comma = "";
if (tableIndex < result.Tables.Count - 1)
{
comma += ",";
}
TEXTJSON += items + "\t]" + comma + "\r\n";
}
TEXTJSON += "}";
string tableName = dirName;
try
{
FileStream fs = new FileStream(tableName, FileMode.Create, FileAccess.Write);
fs.Close();
File.AppendAllText(tableName, Environment.NewLine + TEXTJSON);
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Gets all the records in the Excel file and converts each to dictionary of strings to strings.
/// </summary>
/// <returns>An enumeration of dictionaries.</returns>
public IEnumerable <Dictionary <string, string> > GetRecordsAsDictionary()
{
// We assume all columns contain headers by default
_columnCount = _reader.FieldCount;
// First make sure we have a header record
if (!_reader.Read())
{
throw new ExcelReaderException("No header record was found.");
}
// Process each column in the header row
var headers = new List <string>();
for (var i = 0; i < _columnCount; i++)
{
// Get the header name
var name = _reader.GetString(i);
if (string.IsNullOrEmpty(name))
{
// Header is null or empty, so we are done. This can happen if the file has more total columns
// in it than header rows, which can happen if some white space ends up in a right column
// or there are extra rows below the records
_columnCount = i;
break;
}
// Now store the named index for later for this header column
if (!_configuration.IsHeaderCaseSensitive)
{
name = name.ToLower();
}
headers.Add(name);
}
// Move to the next row
_row++;
// Read each record one at a time and yield it
while (_reader.Read())
{
var record = new Dictionary <string, string>();
for (var i = 0; i < _columnCount; i++)
{
try {
var value = _reader.GetValue(i);
if (value != null)
{
string text;
var type = _reader.GetFieldType(i);
if (type == typeof(bool))
{
// For compatibility with old PHP code, format TRUE and FALSE for boolean values
text = (bool)value ? "TRUE" : "FALSE";
}
else if (type == typeof(DateTime))
{
// To ensure DateTime values make sense in any culture, we render them in ISO 8601 format
text = ((DateTime)value).ToString("o");
}
else
{
text = value.ToString();
}
record.Add(headers[i], text);
}
else
{
// Always return empty strings, not nulls
record.Add(headers[i], "");
}
} catch (Exception ex) {
// Build the details about the error so it can be logged
var details = new ExcelReadErrorDetails {
Row = _row + 1,
Column = i + 1,
FieldName = headers[i],
FieldValue = _reader.GetValue(i),
};
// Add the details to the exception
ExceptionHelper.AddExceptionDataMessage(ex, null, details);
// If we are ignoring errors, optionally call the callback and continue
if (_configuration.IgnoreReadingExceptions)
{
_configuration.ReadingExceptionCallback?.Invoke(ex, details);
_row++;
continue;
}
throw;
}
}
_row++;
yield return(record);
}
}
private static Song ReadSongFromSpreadsheetTab(IExcelDataReader reader)
{
var row = 0;
var currentLines = new List <List <List <List <Note> > > >();
var noteGroups = new List <NoteGroup>();
var instrumentMappings = new List <InstrumentMapping>();
var instrumentOffsets = new Dictionary <Instrument, int>();
var instrumentVolumes = new Dictionary <Instrument, double>();
var masterVolume = 1d;
var beatsPerMinute = 60d;
while (reader.Read())
{
row++;
if (!reader.IsDBNull(0) && reader.GetFieldType(0) == typeof(double))
{
var noteNumber = (int)reader.GetDouble(0);
var noteName = reader.GetString(1);
var isDrum = Constants.Drums.Contains(noteName);
var notes = Enumerable.Range(2, reader.FieldCount - 2)
.Select(column => StringUtil.SplitString(Convert.ToString(reader.GetValue(column)), '_')
.Select(rawNotes => rawNotes.Split(", ")
.Select(rawNote =>
{
var noteMatch = SpreadsheetNoteRegex.Match(rawNote);
if (noteMatch.Success)
{
var length = double.Parse(noteMatch.Groups[1].Value);
var sharpOrFlat = noteMatch.Groups[2].Value;
var instrumentIndicator = noteMatch.Groups[3].Value;
var effectiveNoteNumber = sharpOrFlat switch { "#" => noteNumber + 1, "b" => noteNumber - 1, _ => noteNumber };
var instrument = isDrum
? Instrument.Drumkit
: instrumentIndicator switch
{
"P" => Instrument.Piano,
"B" => Instrument.BassGuitar,
"L" => Instrument.LeadGuitar,
"W" => Instrument.Sawtooth,
"Q" => Instrument.Square,
"R" => Instrument.Celesta,
"V" => Instrument.Vibraphone,
"T" => Instrument.PluckedStrings,
"S" => Instrument.SteelDrum,
_ => instrumentMappings.FirstOrDefault(mapping => mapping.RangeStart <= effectiveNoteNumber && effectiveNoteNumber <= mapping.RangeEnd)?.Instrument ?? Instrument.Piano
};
var noteOffset = instrumentOffsets.TryGetValue(instrument, out var offsetValue) ? offsetValue : 0;
var instrumentVolume = instrumentVolumes.TryGetValue(instrument, out var instrumentVolumeValue) ? instrumentVolumeValue : 1;
return(new Note
{
Instrument = instrument,
Number = effectiveNoteNumber + noteOffset,
Pitch = isDrum ? effectiveNoteNumber * 5 : effectiveNoteNumber,
Name = isDrum ? noteName : $"{noteName[0]}{sharpOrFlat}{noteName.Substring(1)}",
Volume = instrumentVolume * masterVolume,
Length = length
});
}
else
{
return(null);
}
}).ToList()
).ToList()
).ToList();
currentLines.Add(notes);
}
else
{
ProcessSpreadsheetLines(currentLines, noteGroups, ref beatsPerMinute);
if (!reader.IsDBNull(0) && reader.GetFieldType(0) == typeof(string))
{
switch (reader.GetString(0))
{
case "Instrument Mappings":
foreach (var column in Enumerable.Range(1, reader.FieldCount - 1))
{
var value = reader.GetValue(column);
if (value == null)
{
continue;
}
var match = InstrumentMappingRegex.Match(Convert.ToString(value));
if (match.Success)
{
var instrument = ParseInstrument(match.Groups[1].Value);
var rangeStart = int.Parse(match.Groups[2].Value);
var rangeEnd = int.Parse(match.Groups[3].Value);
instrumentMappings.Add(new InstrumentMapping {
Instrument = instrument, RangeStart = rangeStart, RangeEnd = rangeEnd
});
}
else
{
Console.Error.WriteLine($"Unable to parse instrument mapping on sheet {reader.Name} row {row} column {column}");
}
}
break;
case "Instrument Offsets":
foreach (var column in Enumerable.Range(1, reader.FieldCount - 1))
{
var value = reader.GetValue(column);
if (value == null)
{
continue;
}
var match = InstrumentOffsetRegex.Match(Convert.ToString(value));
if (match.Success)
{
var instrument = ParseInstrument(match.Groups[1].Value);
var offset = int.Parse(match.Groups[2].Value);
instrumentOffsets[instrument] = offset;
}
else
{
Console.Error.WriteLine($"Unable to parse instrument offset on sheet {reader.Name} row {row} column {column}");
}
}
break;
case "Volume":
foreach (var column in Enumerable.Range(1, reader.FieldCount - 1))
{
var value = reader.GetValue(column);
if (value == null)
{
continue;
}
var match = VolumeLevelRegex.Match(Convert.ToString(value));
if (match.Success)
{
var instrumentName = match.Groups[1].Value;
var volume = double.Parse(match.Groups[2].Value) / 100;
if (instrumentName.Length > 0)
{
var instrument = ParseInstrument(instrumentName);
instrumentVolumes[instrument] = volume;
}
else
{
masterVolume = volume;
}
}
else
{
Console.Error.WriteLine($"Unable to parse volume level on sheet {reader.Name} row {row} column {column}");
}
}
break;
}
}
}
}
// Process any remaining lines
ProcessSpreadsheetLines(currentLines, noteGroups, ref beatsPerMinute);
return(new Song
{
NoteGroups = noteGroups
});
}
public Type GetFieldType(int i) => _reader.GetFieldType(i);
public Order Read(IExcelDataReader dataReader) =>
new Order
{
OrderNo = dataReader.GetString(0),
OrderDate = DateTime.Parse(dataReader.GetString(1)),
RecurringInvoiceActive = (int)dataReader.GetDouble(2),
RecurringInvoiceRepeatTimes = string.Empty,
RecurringInvoiceEndDate = string.Empty,
RecurringInvoiceSendMethod = string.Empty,
RecurringInvoiceSendFrequency = string.Empty,
RecurringInvoiceSendFrequencyUnit = string.Empty,
NextRecurringInvoiceDate = string.Empty,
SalesPersonEmployeeNo = string.Empty,
SalesPersonName = string.Empty,
ProjectCode = string.Empty,
SubprojectCode = string.Empty,
ProjectName = dataReader.GetString(13),
ProjectManagerCode = string.Empty,
ProjectManagerName = string.Empty,
ProjectBillable = string.Empty,
ProjectStartDate = string.Empty,
ProjectEndDate = string.Empty,
ProjectStatus = string.Empty,
ProjectContactPerson = string.Empty,
DepartmentCode = string.Empty,
DepartmentName = string.Empty,
DepartmentManagerCode = string.Empty,
DepartmentManagerName = string.Empty,
CustomerNo = string.Empty,
ContactName = dataReader.GetString(26),
ContactGroup = string.Empty,
CustomerSince = string.Empty,
IsVatFree = (int)dataReader.GetDouble(29),
Phone = dataReader.GetFieldType(30) == typeof(string) ? dataReader.GetString(30) : dataReader.GetDouble(30).ToString(),
Email = dataReader.GetString(31),
Web = dataReader.GetString(32),
OrganizationNo = dataReader.GetString(33),
MailAddress1 = dataReader.GetString(34),
MailAddress2 = dataReader.GetString(35),
MailPostcode = (int)dataReader.GetDouble(36),
MailCity = dataReader.GetString(37),
MailCountry = dataReader.GetString(38),
DeliveryAddress1 = dataReader.GetString(39),
DeliveryAddress2 = dataReader.GetString(40),
DeliveryPostcode = dataReader.GetFieldType(41) == typeof(string) ? dataReader.GetString(41) : dataReader.GetValue(41) == null ? string.Empty : dataReader.GetValue(41).ToString(),
DeliveryCity = dataReader.GetString(42),
DeliveryCountry = dataReader.GetString(43),
BankAccount = dataReader.GetString(44),
IBAN = dataReader.GetString(45),
SWIFT = dataReader.GetString(46),
InvoiceDelivery = dataReader.GetString(47),
ContactPersonFirstName = dataReader.GetString(48),
ContactPersonLastName = dataReader.GetString(49),
ContactPersonPhone = dataReader.GetFieldType(50) == typeof(string) ? dataReader.GetString(50) : dataReader.GetDouble(50).ToString(),
ContactPersonEmail = dataReader.GetString(51),
Reference = dataReader.GetString(52),
PaymentTerms = dataReader.GetString(53),
MergeWithPreviousOrder = dataReader.GetString(54),
Currency = dataReader.GetString(55),
ProductCode = (int)dataReader.GetDouble(56),
ProductName = dataReader.GetString(57),
ProductGroup = dataReader.GetString(58),
ProductDescription = dataReader.GetString(59),
ProductType = dataReader.GetString(60),
ProductUnit = dataReader.GetString(61),
ProductSalesPrice = dataReader.GetFieldType(62) == typeof(string) ? dataReader.GetString(62) : dataReader.GetDouble(62).ToString(),
ProductCostPrice = dataReader.GetString(63),
ProductSalesAccount = dataReader.GetDouble(64),
ProductSalesAccountName = dataReader.GetString(65),
ProductAltSalesAccount = dataReader.GetDouble(66),
ProductAltSalesAccountName = dataReader.GetString(67),
ProductGTIN = dataReader.GetString(68),
Discount = dataReader.GetDouble(69),
Quantity = dataReader.GetValue(70) == null ? 1 : (int)dataReader.GetDouble(70),
Description = dataReader.GetString(71),
OrderLineUnitPrice = dataReader.GetString(72),
SortOrder = dataReader.GetString(73),
};
public void ReadFromExcel(string sheetName, IExcelDataReader dataTable)
{
this.sheetName = sheetName;
filedList = new List <FieldData>();
for (int i = 0; i < dataTable.FieldCount; i++)
{
filedList.Add(new FieldData());
}
for (int rowIndex = 0; rowIndex < dataTable.RowCount; rowIndex++)
{
dataTable.Read();
for (int columnIndex = 0; columnIndex < dataTable.FieldCount; columnIndex++)
{
FieldData fieldData = filedList[columnIndex];
//根据有效数据的第一行决定本行的实际类型 也就是第一行数据一定不能为空
if (rowIndex == DATA_START_ROW_INDEX)
{
var type = dataTable.GetFieldType(columnIndex);
if (type != null)
{
fieldData.fieldType = type;
}
}
var data = dataTable.GetValue(columnIndex);
if (data != null)
{
fieldData.dataList.Add(data.ToString());
}
else
{
fieldData.dataList.Add("");
}
}
}
var exportSchema = GetCellString(0, 1);
isNeedExprot = true;
if (exportSchema != "base" && exportSchema != "tiny")
{
isNeedExprot = false;
return;
}
InitExportBaseInfo();
DeleteNoneDateCell(); //删掉非数据行的字段
CheckSetDefalutValue(); //给与为空的字段默认值
MergeArrayField();
DoItemFields();
DoIntArrayFields();
}
public DataTable GetSheetData(IExcelDataReader excelReader)
{
if (excelReader == null)
{
return(null);
}
// Create the table with the spreadsheet name
DataTable table = new DataTable(excelReader.Name);
table.Clear();
string value = null;
bool rowIsEmpty;
while (excelReader.Read())
{
DataRow row = table.NewRow();
rowIsEmpty = true;
for (int i = 0; i < excelReader.FieldCount; i++)
{
// If the column is null and this is the first row, skip
// to next iteration (do not want to include empty columns)
if (excelReader.IsDBNull(i) &&
(excelReader.Depth == 1 || i > table.Columns.Count - 1))
{
continue;
}
//value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
if (excelReader.GetFieldType(i).ToString() == "System.Double")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDouble(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.Int")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetInt32(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.Bool")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetBoolean(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.DateTime")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDateTime(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.TimeSpan")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetDateTime(i).ToString();
}
if (excelReader.GetFieldType(i).ToString() == "System.String")
{
value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i).ToString();
}
if (excelReader.Depth == 0)
{
table.Columns.Add(value);
}
else
{
row[table.Columns[i]] = value;
}
if (!string.IsNullOrEmpty(value))
{
rowIsEmpty = false;
}
}
if (excelReader.Depth != 1 && !rowIsEmpty)
{
table.Rows.Add(row);
}
}
return(table);
}