/// <summary>
/// Load CSV data from stream into worksheet.
/// </summary>
/// <param name="s">Input stream to read CSV data.</param>
/// <param name="encoding">Text encoding used to read and decode plain-text from stream.</param>
/// <param name="targetRange">The range used to fill loaded CSV data.</param>
/// <param name="autoSpread">decide whether or not to append rows or columns automatically to fill csv data</param>
/// <param name="bufferLines">decide how many lines int the buffer to read and fill csv data</param>
public object LoadCSV(Stream s, Encoding encoding, RangePosition targetRange, bool autoSpread, int bufferLines)
{
var arg = new CSVFormatArgument
{
AutoSpread = autoSpread,
BufferLines = bufferLines,
TargetRange = targetRange,
};
this.controlAdapter?.ChangeCursor(CursorStyle.Busy);
try
{
CSVFileFormatProvider csvProvider = new CSVFileFormatProvider();
Clear();
csvProvider.Load(this.workbook, s, encoding, arg);
}
finally
{
this.controlAdapter?.ChangeCursor(CursorStyle.PlatformDefault);
}
return(arg);
}
/// <summary>
/// Export spreadsheet as CSV format from specified range.
/// </summary>
/// <param name="s">Stream to write CSV format as stream.</param>
/// <param name="range">Range to be output from this worksheet.</param>
/// <param name="encoding">Text encoding during output text in CSV format.</param>
public void ExportAsCSV(Stream s, RangePosition range, Encoding encoding = null, object arg = null)
{
range = FixRange(range);
int maxRow = Math.Min(range.EndRow, this.MaxContentRow);
int maxCol = Math.Min(range.EndCol, this.MaxContentCol);
bool autoFormat = settings.Has(WorksheetSettings.Edit_AutoFormatCell);
if (encoding == null)
{
encoding = Encoding.Default;
}
var csvArg = arg as CSVFormatArgument;
if (csvArg == null)
{
csvArg = new CSVFormatArgument();
}
var delimiter =
csvArg.LineRegex == CSVFormatArgument.RegexComma ? ',' :
csvArg.LineRegex == CSVFormatArgument.RegexSemicolon ? ';' :
csvArg.LineRegex == CSVFormatArgument.RegexPipe ? '|' :
'\t';
using (var sw = new StreamWriter(s, encoding))
{
StringBuilder sb = new StringBuilder();
for (int r = range.Row; r <= maxRow; r++)
{
if (sb.Length > 0)
{
sw.WriteLine(sb.ToString());
sb.Length = 0;
}
int repeatCount = 0;
for (int c = range.Col; c <= maxCol;)
{
var cell = this.GetCell(r, c);
if (cell == null || !cell.IsValidCell)
{
++repeatCount;
c++;
}
else
{
sb.Append(delimiter, repeatCount);
repeatCount = 1;
var data = cell.Data;
bool quota = false;
//if (!quota)
//{
// if (cell.DataFormat == CellDataFormatFlag.Text)
// {
// quota = true;
// }
//}
if (data is string str)
{
if (autoFormat && !string.IsNullOrEmpty(str) &&
(cell.DataFormat == CellDataFormatFlag.Text ||
str.IndexOf(delimiter) >= 0 || str.IndexOf('"') >= 0 ||
str.StartsWith(" ") || str.EndsWith(" ")))
{
quota = true;
}
}
else
{
str = Convert.ToString(data);
}
if (quota)
{
sb.Append('"');
sb.Append(str.Replace("\"", "\"\""));
sb.Append('"');
}
else
{
sb.Append(str);
}
c += cell.Colspan;
}
}
}
if (sb.Length > 0)
{
sw.WriteLine(sb.ToString());
sb.Length = 0;
}
}
}
