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); }