private void AutoAppend(Worksheet sheet, int row, int col)
{
if (sheet.RowCount < row + 1)
{
sheet.AppendRows(row - sheet.RowCount + 1);
}
if (sheet.ColumnCount < col + 1)
{
sheet.AppendCols(col - sheet.ColumnCount + 1);
}
}
public static void Read(Stream stream, Worksheet sheet, RangePosition targetRange,
Encoding encoding = null, int bufferLines = DEFAULT_READ_BUFFER_LINES, bool autoSpread = true)
{
targetRange = sheet.FixRange(targetRange);
string[] lines = new string[bufferLines];
List <object>[] bufferLineList = new List <object> [bufferLines];
for (int i = 0; i < bufferLineList.Length; i++)
{
bufferLineList[i] = new List <object>(256);
}
#if DEBUG
var sw = System.Diagnostics.Stopwatch.StartNew();
#endif
int row = targetRange.Row;
int totalReadLines = 0;
using (var sr = new StreamReader(stream, encoding))
{
sheet.SuspendDataChangedEvents();
int maxCols = 0;
try
{
bool finished = false;
while (!finished)
{
int readLines = 0;
for (; readLines < lines.Length; readLines++)
{
var line = sr.ReadLine();
if (line == null)
{
finished = true;
break;
}
lines[readLines] = line;
totalReadLines++;
if (!autoSpread && totalReadLines > targetRange.Rows)
{
finished = true;
break;
}
}
if (autoSpread && row + readLines > sheet.RowCount)
{
int appendRows = bufferLines - (sheet.RowCount % bufferLines);
if (appendRows <= 0)
{
appendRows = bufferLines;
}
sheet.AppendRows(appendRows);
}
for (int i = 0; i < readLines; i++)
{
var line = lines[i];
var toBuffer = bufferLineList[i];
toBuffer.Clear();
foreach (Match m in lineRegex.Matches(line))
{
toBuffer.Add(m.Groups["item"].Value);
if (toBuffer.Count >= targetRange.Cols)
{
break;
}
}
if (maxCols < toBuffer.Count)
{
maxCols = toBuffer.Count;
}
if (autoSpread && maxCols >= sheet.ColumnCount)
{
sheet.SetCols(maxCols + 1);
}
}
sheet.SetRangeData(row, targetRange.Col, readLines, maxCols, bufferLineList);
row += readLines;
}
}
finally
{
sheet.ResumeDataChangedEvents();
}
sheet.RaiseRangeDataChangedEvent(new RangePosition(
targetRange.Row, targetRange.Col, maxCols, totalReadLines));
}
#if DEBUG
sw.Stop();
System.Diagnostics.Debug.WriteLine("load csv file: " + sw.ElapsedMilliseconds + " ms, rows: " + row);
#endif
}
public static void Read(Stream stream, Worksheet sheet, Encoding encoding, CSVFormatArgument csvArg)
{
var autoSpread = csvArg.AutoSpread;
var lineRegex = csvArg.LineRegex;
var targetRange = sheet.FixRange(csvArg.TargetRange);
var bufferLines = csvArg.BufferLines;
// Don't auto-format cells to prevent accidental data corruption
sheet.DisableSettings(WorksheetSettings.Edit_AutoFormatCell);
string[] lines = new string[bufferLines];
List <object>[] bufferLineList = new List <object> [bufferLines];
for (int i = 0; i < bufferLineList.Length; i++)
{
bufferLineList[i] = new List <object>(256);
}
#if DEBUG
var sw = System.Diagnostics.Stopwatch.StartNew();
#endif
int row = targetRange.Row;
int totalReadLines = 0;
using (var sr = new StreamReader(stream, encoding))
{
sheet.SuspendDataChangedEvents();
int maxCols = 0;
try
{
bool finished = false;
while (!finished)
{
int readLines = 0;
for (; readLines < lines.Length; readLines++)
{
var line = sr.ReadLine();
if (line == null)
{
finished = true;
break;
}
lines[readLines] = line;
totalReadLines++;
if (!autoSpread && totalReadLines > targetRange.Rows)
{
finished = true;
break;
}
}
if (autoSpread && row + readLines > sheet.RowCount)
{
int appendRows = bufferLines - (sheet.RowCount % bufferLines);
if (appendRows <= 0)
{
appendRows = bufferLines;
}
sheet.AppendRows(appendRows);
}
for (int i = 0; i < readLines; i++)
{
var line = lines[i];
var toBuffer = bufferLineList[i];
toBuffer.Clear();
foreach (Match m in lineRegex.Matches(line))
{
if (m.Index != line.Length) // ignore an empty match at end of line
{
toBuffer.Add(m.Groups["item"].Value);
}
if (toBuffer.Count >= targetRange.Cols)
{
break;
}
}
if (maxCols < toBuffer.Count)
{
maxCols = toBuffer.Count;
}
if (autoSpread && maxCols >= sheet.ColumnCount)
{
sheet.SetCols(maxCols + 1);
}
}
sheet.SetRangeData(row, targetRange.Col, readLines, maxCols, bufferLineList);
row += readLines;
}
}
finally
{
sheet.ResumeDataChangedEvents();
}
sheet.RaiseRangeDataChangedEvent(new RangePosition(
targetRange.Row, targetRange.Col, maxCols, totalReadLines));
}
#if DEBUG
sw.Stop();
System.Diagnostics.Debug.WriteLine("load csv file: " + sw.ElapsedMilliseconds + " ms, rows: " + row);
#endif
}