/// <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) { int iRowIndex = -1; int iColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(AnchorCellReference, out iRowIndex, out iColumnIndex)) { this.ImportText(FileName, iRowIndex, iColumnIndex, Options); } }
/// <summary> /// Clone an instance of SLTextImportOptions. /// </summary> /// <returns>An SLTextImportOptions object.</returns> public SLTextImportOptions Clone() { SLTextImportOptions tio = new SLTextImportOptions(); tio.DataFieldType = this.DataFieldType; tio.iDefaultFixedWidth = this.iDefaultFixedWidth; tio.UseTabDelimiter = this.UseTabDelimiter; tio.UseSemicolonDelimiter = this.UseSemicolonDelimiter; tio.UseCommaDelimiter = this.UseCommaDelimiter; tio.UseSpaceDelimiter = this.UseSpaceDelimiter; tio.UseCustomDelimiter = this.UseCustomDelimiter; tio.CustomDelimiter = this.CustomDelimiter; tio.MergeDelimiters = this.MergeDelimiters; tio.HasTextQualifier = this.HasTextQualifier; tio.TextQualifier = this.TextQualifier; tio.iImportStartRowIndex = this.iImportStartRowIndex; tio.Culture = this.Culture; tio.NumberStyles = this.NumberStyles; tio.Encoding = this.Encoding; tio.PreserveSpace = this.PreserveSpace; foreach (int key in this.dictColumnFormat.Keys) { tio.dictColumnFormat[key] = this.dictColumnFormat[key]; } foreach (string format in this.listCustomDateFormats) { tio.listCustomDateFormats.Add(format); } foreach (int key in this.dictFixedWidth.Keys) { tio.dictFixedWidth[key] = this.dictFixedWidth[key]; } return(tio); }
/// <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> /// 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) { int iRowIndex = -1; int iColumnIndex = -1; if (SLTool.FormatCellReferenceToRowColumnIndex(AnchorCellReference, out iRowIndex, out iColumnIndex)) { this.ImportText(FileName, iRowIndex, iColumnIndex, Options); } }
/// <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> /// Clone an instance of SLTextImportOptions. /// </summary> /// <returns>An SLTextImportOptions object.</returns> public SLTextImportOptions Clone() { SLTextImportOptions tio = new SLTextImportOptions(); tio.DataFieldType = this.DataFieldType; tio.iDefaultFixedWidth = this.iDefaultFixedWidth; tio.UseTabDelimiter = this.UseTabDelimiter; tio.UseSemicolonDelimiter = this.UseSemicolonDelimiter; tio.UseCommaDelimiter = this.UseCommaDelimiter; tio.UseSpaceDelimiter = this.UseSpaceDelimiter; tio.UseCustomDelimiter = this.UseCustomDelimiter; tio.CustomDelimiter = this.CustomDelimiter; tio.MergeDelimiters = this.MergeDelimiters; tio.HasTextQualifier = this.HasTextQualifier; tio.TextQualifier = this.TextQualifier; tio.iImportStartRowIndex = this.iImportStartRowIndex; tio.Culture = this.Culture; tio.NumberStyles = this.NumberStyles; tio.Encoding = this.Encoding; tio.PreserveSpace = this.PreserveSpace; foreach (int key in this.dictColumnFormat.Keys) { tio.dictColumnFormat[key] = this.dictColumnFormat[key]; } foreach (string format in this.listCustomDateFormats) { tio.listCustomDateFormats.Add(format); } foreach (int key in this.dictFixedWidth.Keys) { tio.dictFixedWidth[key] = this.dictFixedWidth[key]; } return tio; }