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