private void btnBrowse_Click(object sender, EventArgs e)
{
dialogOpenExcel.Filter = @"Excel Files|*.xlsx";
if (dialogOpenExcel.ShowDialog() != DialogResult.OK)
{
return;
}
try
{
bool flagError;
List <int> InvalidRows;
DataTable table;
int tongNV;
double tongTamUng, tongLuongDieuchinh, tongThuchikhac, tongMucdongBHXH;
using (var excelPkg = new ExcelPackage())
{
using (var stream = new FileStream(dialogOpenExcel.FileName, FileMode.Open))
{
excelPkg.Load(stream);
ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets[1];
WorksheetToDataTable(oSheet, out InvalidRows, out flagError, out table);
var distinctRows = (from DataRow row in table.Rows select row).DistinctBy(item => item["UserFullCode"].ToString().TrimStart().TrimEnd().ToLower()).ToList();
tongNV = distinctRows.Count;
tongTamUng = (from DataRow row in distinctRows select(double) row["TamUng"]).Sum();
tongLuongDieuchinh = (from DataRow row in distinctRows select(double) row["LuongDieuChinh"]).Sum();
tongThuchikhac = (from DataRow row in distinctRows select(double) row["ThuChiKhac"]).Sum();
tongMucdongBHXH = (from DataRow row in distinctRows select(double) row["MucDongBHXH"]).Sum();
}
}
if (flagError)
{
var chuoi = "Không thể đọc giá trị tại các dòng \n{0} ";
var chuoi1 = string.Format(chuoi, InvalidRows.ToArray());
MessageBox.Show(chuoi1, Resources.Caption_Loi, MessageBoxButtons.OK);
}
else
{
dgrdDThuchiExcel.DataSource = table;
lbTongNV.Text = string.Format("Tổng số NV: {0}", tongNV);
lbTongTamung.Text = string.Format("Tổng tạm ứng: {0:###,###,###,##0.000}", tongTamUng);
lbTongLuongdieuchinh.Text = string.Format("Tổng lương điều chỉnh: {0:###,###,###,##0.000}", tongLuongDieuchinh);
lbTongThuchikhac.Text = string.Format("Tổng thu chi khác: {0:###,###,###,##0.000}", tongThuchikhac);
lbTongMucDongBHXH.Text = string.Format("Tổng các mức đóng BHXH: {0:###,##0.00}", tongMucdongBHXH);
}
}
catch (Exception ex)
{
lg.Error(string.Format("[{0}]_[{1}]\n", this.Name, System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
MessageBox.Show(Resources.Text_LoiDocFileExcel, Resources.Caption_ThongBao, MessageBoxButtons.OK);
}
}
public bool Load()
{
LoadError = string.Empty;
if (!File.Exists(FileName))
{
LoadError = "File does not exist.";
return(false);
}
LastFileChange = File.GetLastWriteTime(FileName);
DataRows = new List <CsvData>();
try
{
var lineNumber = 0;
using var fs = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
using var fileData = new StreamReader(fs, FileEncoding, true);
while (!fileData.EndOfStream)
{
lineNumber++;
var lineData = fileData.ReadLine();
if (string.IsNullOrEmpty(lineData))
{
continue;
}
if (lineNumber == 1)
{
DetectSeparator(lineData);
Columns.Clear();
var columns = lineData
.Split(Separator)
.Select(TextEscaping.RemoveQuotes);
Columns.AddRange(columns);
continue;
}
if (RemoveQuotes)
{
while (!fileData.EndOfStream)
{
var quotes1 = lineData.ToCharArray().Count(ch => ch == '"');
var quotes2 = lineData.ToCharArray().Count(ch => ch == '\'');
if (quotes1 == 0 && quotes2 == 0)
{
break;
}
if (quotes1 > 0 && (quotes1 & 1) == 0)
{
break;
}
if (quotes2 > 0 && (quotes2 & 1) == 0)
{
break;
}
lineData += fileData.ReadLine();
}
}
var data = new CsvData(lineNumber, Columns, lineData);
var fields = lineData.Split(Separator);
if (RemoveQuotes)
{
fields = fields
.Select(TextEscaping.RemoveQuotes)
.ToArray();
}
data.Fields.AddRange(fields);
if (data.Fields.Count == Columns.Count)
{
DataRows.Add(data);
}
else
{
LoadError = "Invalid rows detected.";
InvalidRows.Add(data);
}
}
FileEncoding = fileData.CurrentEncoding;
fileData.Close();
return(true);
}
catch (IOException ex)
{
LoadError = ex.Message;
Trace.TraceError(ex.Message);
}
return(false);
}
