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 }