/// <summary>
/// Set the column data format type.
/// </summary>
/// <param name="ColumnIndex">
/// The column index in the data source. This is 1-based indexing, so it's 1 for the 1st data
/// source column, 2 for the 2nd data source column and so on.
/// </param>
/// <param name="ColumnFormat">The column data format type.</param>
public void SetColumnFormat(int ColumnIndex, SLTextImportColumnFormatValues ColumnFormat)
{
if (ColumnIndex >= 1)
{
dictColumnFormat[ColumnIndex] = ColumnFormat;
}
}
/// <summary>
/// Import a text file as a data source, with the first data row and first data column at a specific cell.
/// </summary>
/// <param name="FileName">The file name.</param>
/// <param name="AnchorRowIndex">The row index of the anchor cell.</param>
/// <param name="AnchorColumnIndex">The column index of the anchor cell.</param>
/// <param name="Options">Text import options.</param>
public void ImportText(string FileName, int AnchorRowIndex, int AnchorColumnIndex, SLTextImportOptions Options)
{
if (Options == null)
{
Options = new SLTextImportOptions();
}
if (AnchorRowIndex < 1)
{
AnchorRowIndex = 1;
}
if (AnchorColumnIndex < 1)
{
AnchorColumnIndex = 1;
}
List <char> listDelimiters = new List <char>();
if (Options.UseTabDelimiter)
{
listDelimiters.Add('\t');
}
if (Options.UseSemicolonDelimiter)
{
listDelimiters.Add(';');
}
if (Options.UseCommaDelimiter)
{
listDelimiters.Add(',');
}
if (Options.UseSpaceDelimiter)
{
listDelimiters.Add(' ');
}
if (Options.UseCustomDelimiter)
{
listDelimiters.Add(Options.CustomDelimiter);
}
char[] caDelimiters = listDelimiters.ToArray();
StringSplitOptions sso = Options.MergeDelimiters ? StringSplitOptions.RemoveEmptyEntries : StringSplitOptions.None;
string sDataLine;
List <string> listData = new List <string>();
double fData;
DateTime dtData = DateTime.Now;
string sDateData = string.Empty;
// There's the space separator: \s
// Example: 05 Oct 2013
// Then the typical slash separator: /
// Example: 05/10/2013 or 10/05/2013
// Then the dash: - (we do \- to escape for regex)
// Example: 05-10-2013 or 10-05-2013
// Then the plus sign: + (we do \+ to escape for regex).
// The plus sign occurs for expanded year representions, such as the year +12345
// Will SpreadsheetLight survive beyond the year 9999? Who knows?
// See this for more information:
//http://en.wikipedia.org/wiki/ISO_8601
// The dot (or period) is also used: . (we do \. to escape for regex)
// Then the single quote is used when the century portion of the year is shortened.
// Example: 05.10.2013 or 05.10.'13
// See this for more information:
//http://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe
string sDateSeparatorRegex = @"[\s/\-\+\.']+";
string sData;
int i;
int iRowIndex, iColumnIndex;
int iNextSubstringIndex, iFixedWidth;
SLTextImportColumnFormatValues tiColumnFormat = SLTextImportColumnFormatValues.General;
List <string> listYears = new List <string> {
"yyyy", "yy"
};
List <string> listMonths = new List <string> {
"MMMM", "MMM", "MM", "M"
};
List <string> listDays = new List <string> {
"dd", "d"
};
List <string> listMDY = GetPossibleDateFormatsForImportParsing(listMonths, listDays, listYears);
List <string> listDMY = GetPossibleDateFormatsForImportParsing(listDays, listMonths, listYears);
List <string> listYMD = GetPossibleDateFormatsForImportParsing(listYears, listMonths, listDays);
List <string> listMYD = GetPossibleDateFormatsForImportParsing(listMonths, listYears, listDays);
List <string> listDYM = GetPossibleDateFormatsForImportParsing(listDays, listYears, listMonths);
List <string> listYDM = GetPossibleDateFormatsForImportParsing(listYears, listDays, listMonths);
iRowIndex = AnchorRowIndex;
int iRowCounter = 0;
using (StreamReader sr = new StreamReader(FileName, Options.Encoding))
{
while (sr.Peek() > -1)
{
sDataLine = sr.ReadLine();
++iRowCounter;
if (iRowCounter < Options.ImportStartRowIndex)
{
continue;
}
listData.Clear();
if (Options.DataFieldType == SLTextImportDataFieldTypeValues.Delimited)
{
listData = new List <string>(sDataLine.Split(caDelimiters, sso));
}
else
{
// else is fixed width
iNextSubstringIndex = 0;
iFixedWidth = Options.DefaultFixedWidth;
// use i temporarily for tracking column indices
i = 1;
if (Options.dictFixedWidth.ContainsKey(i))
{
iFixedWidth = Options.dictFixedWidth[i];
}
while (iNextSubstringIndex + iFixedWidth <= sDataLine.Length)
{
listData.Add(sDataLine.Substring(iNextSubstringIndex, iFixedWidth));
iNextSubstringIndex += iFixedWidth;
++i;
iFixedWidth = Options.DefaultFixedWidth;
if (Options.dictFixedWidth.ContainsKey(i))
{
iFixedWidth = Options.dictFixedWidth[i];
}
}
// if still need to do substring, but the fixed width exceeded the string length...
if (iNextSubstringIndex < sDataLine.Length)
{
// then take the rest of the string
listData.Add(sDataLine.Substring(iNextSubstringIndex));
}
// no else because all the data has been fixed-width-separated by now.
}
iColumnIndex = AnchorColumnIndex;
for (i = 0; i < listData.Count; ++i)
{
tiColumnFormat = SLTextImportColumnFormatValues.General;
// +1 because i is zero-based
if (Options.dictColumnFormat.ContainsKey(i + 1))
{
tiColumnFormat = Options.dictColumnFormat[i + 1];
}
switch (tiColumnFormat)
{
case SLTextImportColumnFormatValues.General:
// We try to parse as a floating point number first.
// Failing that, we try to parse as date with any given custom date formats.
// If fail that or there are no custom date formats given, we try to parse
// as date in a general manner.
// If fail *that*, then we throw in the towel and just set as text.
if (double.TryParse(listData[i], Options.NumberStyles, Options.Culture, out fData))
{
SetCellValue(iRowIndex, iColumnIndex, fData);
}
else
{
SetDateIfFailThenSetAsTextForImportParsing(listData[i], iRowIndex, iColumnIndex, Options.listCustomDateFormats, Options.Culture, Options.HasTextQualifier, Options.TextQualifier, Options.PreserveSpace);
}
break;
case SLTextImportColumnFormatValues.Text:
sData = listData[i];
if (Options.HasTextQualifier)
{
sData = sData.Trim(Options.TextQualifier);
}
if (!Options.PreserveSpace)
{
sData = sData.Trim();
}
SetCellValue(iRowIndex, iColumnIndex, sData);
break;
case SLTextImportColumnFormatValues.DateMDY:
// we try to make the date string as compact and as close to what
// we have as date formats as possible, before trying the date combos.
sDateData = Regex.Replace(listData[i], sDateSeparatorRegex, " ").Trim();
if (DateTime.TryParseExact(sDateData, listMDY.ToArray(), Options.Culture, System.Globalization.DateTimeStyles.None, out dtData))
{
SetCellValue(iRowIndex, iColumnIndex, dtData);
}
else
{
SetDateIfFailThenSetAsTextForImportParsing(listData[i], iRowIndex, AnchorColumnIndex + i, Options.listCustomDateFormats, Options.Culture, Options.HasTextQualifier, Options.TextQualifier, Options.PreserveSpace);
}
break;
case SLTextImportColumnFormatValues.DateDMY:
sDateData = Regex.Replace(listData[i], sDateSeparatorRegex, " ").Trim();
if (DateTime.TryParseExact(sDateData, listDMY.ToArray(), Options.Culture, System.Globalization.DateTimeStyles.None, out dtData))
{
SetCellValue(iRowIndex, iColumnIndex, dtData);
}
else
{
SetDateIfFailThenSetAsTextForImportParsing(listData[i], iRowIndex, AnchorColumnIndex + i, Options.listCustomDateFormats, Options.Culture, Options.HasTextQualifier, Options.TextQualifier, Options.PreserveSpace);
}
break;
case SLTextImportColumnFormatValues.DateYMD:
sDateData = Regex.Replace(listData[i], sDateSeparatorRegex, " ").Trim();
if (DateTime.TryParseExact(sDateData, listYMD.ToArray(), Options.Culture, System.Globalization.DateTimeStyles.None, out dtData))
{
SetCellValue(iRowIndex, iColumnIndex, dtData);
}
else
{
SetDateIfFailThenSetAsTextForImportParsing(listData[i], iRowIndex, AnchorColumnIndex + i, Options.listCustomDateFormats, Options.Culture, Options.HasTextQualifier, Options.TextQualifier, Options.PreserveSpace);
}
break;
case SLTextImportColumnFormatValues.DateMYD:
sDateData = Regex.Replace(listData[i], sDateSeparatorRegex, " ").Trim();
if (DateTime.TryParseExact(sDateData, listMYD.ToArray(), Options.Culture, System.Globalization.DateTimeStyles.None, out dtData))
{
SetCellValue(iRowIndex, iColumnIndex, dtData);
}
else
{
SetDateIfFailThenSetAsTextForImportParsing(listData[i], iRowIndex, AnchorColumnIndex + i, Options.listCustomDateFormats, Options.Culture, Options.HasTextQualifier, Options.TextQualifier, Options.PreserveSpace);
}
break;
case SLTextImportColumnFormatValues.DateDYM:
sDateData = Regex.Replace(listData[i], sDateSeparatorRegex, " ").Trim();
if (DateTime.TryParseExact(sDateData, listDYM.ToArray(), Options.Culture, System.Globalization.DateTimeStyles.None, out dtData))
{
SetCellValue(iRowIndex, iColumnIndex, dtData);
}
else
{
SetDateIfFailThenSetAsTextForImportParsing(listData[i], iRowIndex, iColumnIndex, Options.listCustomDateFormats, Options.Culture, Options.HasTextQualifier, Options.TextQualifier, Options.PreserveSpace);
}
break;
case SLTextImportColumnFormatValues.DateYDM:
sDateData = Regex.Replace(listData[i], sDateSeparatorRegex, " ").Trim();
if (DateTime.TryParseExact(sDateData, listYDM.ToArray(), Options.Culture, System.Globalization.DateTimeStyles.None, out dtData))
{
SetCellValue(iRowIndex, iColumnIndex, dtData);
}
else
{
SetDateIfFailThenSetAsTextForImportParsing(listData[i], iRowIndex, iColumnIndex, Options.listCustomDateFormats, Options.Culture, Options.HasTextQualifier, Options.TextQualifier, Options.PreserveSpace);
}
break;
}
if (tiColumnFormat != SLTextImportColumnFormatValues.Skip)
{
++iColumnIndex;
}
}
++iRowIndex;
}
}
}
/// <summary>
/// Set the column data format type.
/// </summary>
/// <param name="ColumnIndex">The column index in the data source. This is 1-based indexing, so it's 1 for the 1st data source column, 2 for the 2nd data source column and so on.</param>
/// <param name="ColumnFormat">The column data format type.</param>
public void SetColumnFormat(int ColumnIndex, SLTextImportColumnFormatValues ColumnFormat)
{
if (ColumnIndex >= 1)
{
this.dictColumnFormat[ColumnIndex] = ColumnFormat;
}
}
