public void WriteColumn(string path, DataTable value, int column = 1, int workSheet = 0) { DataTable tbl = new DataTable(); try { var watchGlobal = Stopwatch.StartNew(); var fileInfo = new FileInfo(path); using (ExcelPackage excelPackage = new ExcelPackage(fileInfo)) { ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[workSheet]; var lastow = worksheet.Dimension.End.Row; worksheet.Cells[lastow++, column].LoadFromDataTable(value, false); excelPackage.Save(); } watchGlobal.Stop(); var elapsedMsGlobal = watchGlobal.ElapsedMilliseconds; var tGlobal = TimeSpan.FromMilliseconds(elapsedMsGlobal); string finalTime = string.Format("{0:D2}h:{1:D2}m:{2:D2}s:{3:D3}ms", tGlobal.Hours, tGlobal.Minutes, tGlobal.Seconds, tGlobal.Milliseconds); } catch (Exception e) { Console.WriteLine(e.Message); ExcelReaderEventHub.OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace)); } }
public Dictionary <string, DataTable> GetDataTableFromAllWorksheets(string path, int workSheet = 0, bool hasHeader = true) { var dts = new Dictionary <string, DataTable>(); try { var watchGlobal = Stopwatch.StartNew(); var fileInfo = new FileInfo(path); ExcelReaderEventHub.OnOpenStart(null, new ExcelOpenStartEventArgs(path)); OnOpenStart(null, new ExcelOpenStartEventArgs(path)); using (var pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = File.OpenRead(path)) { pck.Load(stream); } var worksheets = pck.Workbook.Worksheets; foreach (var ws in worksheets) { dts[ws.Name.Trim()] = CreateDataTable(path, hasHeader, fileInfo, ws); } } watchGlobal.Stop(); var elapsedMsGlobal = watchGlobal.ElapsedMilliseconds; var tGlobal = TimeSpan.FromMilliseconds(elapsedMsGlobal); string finalTime = string.Format("{0:D2}h:{1:D2}m:{2:D2}s:{3:D3}ms", tGlobal.Hours, tGlobal.Minutes, tGlobal.Seconds, tGlobal.Milliseconds); ExcelReaderEventHub.OnOpenEnd(null, new ExcelOpenEndEventArgs(path, finalTime)); OnOpenEnd(null, new ExcelOpenEndEventArgs(path, finalTime)); } 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(dts); }
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); }