/// <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="AnchorCellReference">The anchor cell reference, such as "A1".</param>
/// <param name="Options">Text import options.</param>
public void ImportText(string FileName, string AnchorCellReference, SLTextImportOptions Options)
{
var iRowIndex = -1;
var iColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(AnchorCellReference, out iRowIndex, out iColumnIndex))
{
ImportText(FileName, iRowIndex, iColumnIndex, Options);
}
}
/// <summary>
/// Simple example where the caller will create the Excel file if missing
/// using the method above CreateNewFile. pTextFile is a tab delimited text
/// file known to be validate which is imported into a specific WorkSheet.
///
/// Calling this method:
/// var ops = new SpreadSheetLightLibrary.Examples();
/// var excelFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SpreadSheetLight.xlsx");
/// var textFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "import1.txt");
/// var sheetName = "People";
///
/// if (File.Exists(excelFileName))
/// {
/// File.Delete(excelFileName);
/// }
///
/// ops.CreateNewFile(excelFileName);
/// ops.ImportTabDelimitedTextFile(textFileName, excelFileName, sheetName);
///
/// </summary>
/// <param name="pTextFileName"></param>
/// <param name="pExcelFileName"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public bool ImportTabDelimitedTextFile(string pTextFileName, string pExcelFileName, string pSheetName)
{
try
{
using (SLDocument doc = new SLDocument(pExcelFileName))
{
var sheets = doc.GetSheetNames(false);
if (sheets.Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower()))
{
doc.SelectWorksheet(pSheetName);
doc.ClearCellContent();
}
else
{
doc.AddWorksheet(pSheetName);
}
var tio = new SLTextImportOptions();
doc.ImportText(pTextFileName, "A1", tio);
// setting column widths
doc.AutoFitColumn("A");
doc.SetColumnWidth(2, 5);
// do not need Sheet1
if (sheets.FirstOrDefault((sheetName) => sheetName.ToLower() == "sheet1") != null)
{
if (pSheetName.ToLower() != "sheet1")
{
doc.DeleteWorksheet("Sheet1");
}
}
doc.Save();
return(true);
}
}
catch (Exception)
{
return(false);
}
}
public SLDocument LoadTextFile(SLDocument sldocWorksheet, string strTextFileFullPath)
{
sldocWorksheet.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Delimited");
SLTextImportOptions sltioWorksheetOptions = new SLTextImportOptions();
sltioWorksheetOptions.Culture = new CultureInfo("en-US");
SLWorksheetStatistics sldocstatsWorksheetStats = sldocWorksheet.GetWorksheetStatistics();
int iLastColumnWithData = sldocstatsWorksheetStats.EndColumnIndex;
for (int iCounter = 0; iCounter < iLastColumnWithData; iCounter++)
{
sltioWorksheetOptions.SetColumnFormat(iCounter, SLTextImportColumnFormatValues.Text);
}
sldocWorksheet.ImportText(strTextFileFullPath, "A1", sltioWorksheetOptions);
return(sldocWorksheet);
}
/// <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;
}
var 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);
}
var caDelimiters = listDelimiters.ToArray();
var sso = Options.MergeDelimiters ? StringSplitOptions.RemoveEmptyEntries : StringSplitOptions.None;
string sDataLine;
var listData = new List <string>();
double fData;
var dtData = DateTime.Now;
var 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
var sDateSeparatorRegex = @"[\s/\-\+\.']+";
string sData;
int i;
int iRowIndex, iColumnIndex;
int iNextSubstringIndex, iFixedWidth;
var tiColumnFormat = SLTextImportColumnFormatValues.General;
var listYears = new List <string> {
"yyyy", "yy"
};
var listMonths = new List <string> {
"MMMM", "MMM", "MM", "M"
};
var listDays = new List <string> {
"dd", "d"
};
var listMDY = GetPossibleDateFormatsForImportParsing(listMonths, listDays, listYears);
var listDMY = GetPossibleDateFormatsForImportParsing(listDays, listMonths, listYears);
var listYMD = GetPossibleDateFormatsForImportParsing(listYears, listMonths, listDays);
var listMYD = GetPossibleDateFormatsForImportParsing(listMonths, listYears, listDays);
var listDYM = GetPossibleDateFormatsForImportParsing(listDays, listYears, listMonths);
var listYDM = GetPossibleDateFormatsForImportParsing(listYears, listDays, listMonths);
iRowIndex = AnchorRowIndex;
var iRowCounter = 0;
using (var 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)
{
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,
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,
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,
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,
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,
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,
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;
}
}
}
