private DataTable CreateDataTable(string path, bool hasHeader, FileInfo fileInfo, ExcelWorksheet ws)
{
DataTable tbl = new DataTable();
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : $"Column {firstRowCell.Start.Column}");
}
var startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
try {
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.Rows.Add();
foreach (var cell in wsRow)
{
if (cell.Start.Column <= tbl.Columns.Count)
{
row[cell.Start.Column - 1] = cell.Text;
}
}
var razao = (double)rowNum / (double)ws.Dimension.End.Row;
var percent = razao * 100;
percent = Math.Round(percent, 2);
ExcelReaderEventHub.OnOpenProgress(null, new ExcelOpenProgressEventArgs(path, ws.Dimension.End.Row, rowNum, $"Abrindo {fileInfo.Name} {percent}% concluido ..."));
OnOpenProgress(null, new ExcelOpenProgressEventArgs(path, ws.Dimension.End.Row, rowNum, $"Abrindo {fileInfo.Name} {percent}% concluido ..."));
} catch (Exception e) {
Console.WriteLine(e.Message);
ExcelReaderEventHub.OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace));
OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace));
}
}
return(tbl);
}
public DataTable GetDataTableFromCSV(string path, int workSheet = 0, bool hasHeader = true)
{
DataTable dt = new DataTable();
try {
var ci1 = new CultureInfo("pt-BR");
var fileInfo = new FileInfo(path);
ExcelReaderEventHub.OnOpenStart(null, new ExcelOpenStartEventArgs(path));
OnOpenStart(null, new ExcelOpenStartEventArgs(path));
StreamReader file = new StreamReader(path, Encoding.GetEncoding(ci1.TextInfo.ANSICodePage));
var lines = file.ReadToEnd().Split(new char[] { '\n' });
var totalLines = lines.Count();
StreamReader sr = new StreamReader(path, Encoding.GetEncoding(ci1.TextInfo.ANSICodePage));
string[] headers = sr.ReadLine().Split(';');
var h = 0;
foreach (string header in headers)
{
dt.Columns.Add(hasHeader ? header : $"column{h}");
h++;
}
var l = 0;
while (!sr.EndOfStream)
{
try {
string[] rows = Regex.Split(sr.ReadLine(), ";(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
DataRow dr = dt.NewRow();
for (int i = 0; i < headers.Length; i++)
{
var collum = dt.Columns[i].ColumnName;
dr[i] = rows[i];
}
dt.Rows.Add(dr);
var razao = (double)l / (double)totalLines;
var percent = razao * 100;
percent = Math.Round(percent, 2);
ExcelReaderEventHub.OnOpenProgress(null, new ExcelOpenProgressEventArgs(path, totalLines, l, $"Abrindo {fileInfo.Name} {percent}% concluido ..."));
OnOpenProgress(null, new ExcelOpenProgressEventArgs(path, totalLines, l, $"Abrindo {fileInfo.Name} {percent}% concluido ..."));
} catch (Exception e) {
Debug.WriteLine(e.Message);
ExcelReaderEventHub.OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace));
OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace));
}
l++;
}
} catch (Exception e) {
Debug.WriteLine(e.Message);
ExcelReaderEventHub.OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace));
OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace));
}
return(dt);
}
