public void Load(IWorkbook workbook, Stream stream, Encoding encoding, object arg, string singleSheet = "")
{
bool autoSpread = true;
int bufferLines = CSVFormat.DEFAULT_READ_BUFFER_LINES;
RangePosition targetRange = RangePosition.EntireRange;
CSVFormatArgument csvArg = arg as CSVFormatArgument;
if (csvArg != null)
{
autoSpread = csvArg.AutoSpread;
bufferLines = csvArg.BufferLines;
targetRange = csvArg.TargetRange;
}
Worksheet sheet = null;
if (workbook.Worksheets.Count == 0)
{
sheet = workbook.CreateWorksheet("Sheet1");
workbook.Worksheets.Add(sheet);
}
else
{
while (workbook.Worksheets.Count > 1)
{
workbook.Worksheets.RemoveAt(workbook.Worksheets.Count - 1);
}
sheet = workbook.Worksheets[0];
sheet.Reset();
}
CSVFormat.Read(stream, sheet, targetRange, encoding, bufferLines, autoSpread);
}
public object Load(IWorkbook workbook, Stream stream, Encoding encoding, object arg)
{
CSVFormatArgument csvArg = arg as CSVFormatArgument;
if (csvArg == null)
{
csvArg = new CSVFormatArgument();
}
Worksheet sheet = null;
if (workbook.Worksheets.Count == 0)
{
sheet = workbook.CreateWorksheet("Sheet1");
workbook.Worksheets.Add(sheet);
}
else
{
while (workbook.Worksheets.Count > 1)
{
workbook.Worksheets.RemoveAt(workbook.Worksheets.Count - 1);
}
sheet = workbook.Worksheets[0];
sheet.Reset();
}
CSVFormat.Read(stream, sheet, encoding, csvArg);
return(csvArg);
}
public void Save(IWorkbook workbook, Stream stream, Encoding encoding, object arg)
{
CSVFormatArgument csvArg = arg as CSVFormatArgument;
if (workbook.Worksheets.Count != 1)
{
throw new NotSupportedException("Saving entire workbook as CSV is not supported, use Worksheet.ExportAsCSV instead.");
}
var sheet = workbook.Worksheets[0]; // workbook.GetWorksheetByName("Sheet1");
sheet.ExportAsCSV(stream, RangePosition.EntireRange, null, arg);
//int fromRow = 0, fromCol = 0, toRow = 0, toCol = 0;
//if (args != null)
//{
// object arg;
// if (args.TryGetValue("fromRow", out arg)) fromRow = (int)arg;
// if (args.TryGetValue("fromCol", out arg)) fromCol = (int)arg;
// if (args.TryGetValue("toRow", out arg)) toRow = (int)arg;
// if (args.TryGetValue("toCol", out arg)) toCol = (int)arg;
//}
}
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
}