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);
}
public bool CreateEmpty(string workSheetName, string fileName)
{
try {
using (ExcelPackage pck = new ExcelPackage(new FileInfo(fileName))) {
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(workSheetName);
ws.Cells["A1"].Value = "";
pck.Save();
}
} catch (Exception e) {
Debug.WriteLine(e.Message);
ExcelReaderEventHub.OnSaveError(null, new ExcelSaveErrorEventArgs(fileName, e.Message, e.StackTrace));
OnSaveError(null, new ExcelSaveErrorEventArgs(fileName, e.Message, e.StackTrace));
}
return(true);
}
public void SaveBase <T>(string fileName, IEnumerable <T> result, Action <ExcelPackage, DataTable> opt)
{
try {
var i = 1;
var parts = result.SplitList(800000).ToList();
foreach (var prt in parts)
{
try {
if (i > 1)
{
var filInfo = new FileInfo(fileName);
var dir = filInfo.Directory;
var name = filInfo.Name;
fileName = $"{dir}\\Part_{i}_{name}";
}
Console.WriteLine($"Salvando {fileName}...");
var json = JsonConvert.SerializeObject(prt, Formatting.Indented);
DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));
using (ExcelPackage pck = new ExcelPackage(new FileInfo(fileName))) {
opt.Invoke(pck, dt);
pck.Save();
}
Console.WriteLine($"{fileName} foi salvo!!");
} catch (Exception e) {
Console.WriteLine(e.Message);
ExcelReaderEventHub.OnSaveError(null, new ExcelSaveErrorEventArgs(fileName, e.Message, e.StackTrace));
OnSaveError(null, new ExcelSaveErrorEventArgs(fileName, e.Message, e.StackTrace));
}
i++;
}
} catch (Exception e) {
Console.WriteLine($"Um erro ocorreu: {e.Message}");
ExcelReaderEventHub.OnSaveError(null, new ExcelSaveErrorEventArgs(fileName, e.Message, e.StackTrace));
OnSaveError(null, new ExcelSaveErrorEventArgs(fileName, e.Message, e.StackTrace));
}
}
public bool SaveBase <T>(IEnumerable <T> result, string workSheetName, string fileName)
{
try {
DataTable dt = result.ToDataTable();
using (ExcelPackage pck = new ExcelPackage(new FileInfo(fileName))) {
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(workSheetName);
ws.Cells["A1"].LoadFromDataTable(dt, true);
pck.Save();
}
} catch (Exception e) {
Debug.WriteLine(e.Message);
ExcelReaderEventHub.OnSaveError(null, new ExcelSaveErrorEventArgs(fileName, e.Message, e.StackTrace));
OnSaveError(null, new ExcelSaveErrorEventArgs(fileName, e.Message, e.StackTrace));
}
return(true);
}
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);
}
