private void btnCalculate_Click(object sender, EventArgs e) { using (Stream buffer = this.WriteFormulas()) { //load Workbook workbook = new Workbook(); workbook.LoadFromStream(buffer); //calculate formula Object b3 = workbook.CaculateFormulaValue("Sheet1!$B$3"); Object c3 = workbook.CaculateFormulaValue("Sheet1!$C$3"); String formula = "Sheet1!$B$3 + Sheet1!$C$3"; Object value = workbook.CaculateFormulaValue(formula); String message = String.Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula, value); MessageBox.Show(message); } }
public string XuLiCongThuc(int idBangLuong, string MaDonVi, string MaNhanVien) { Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); DataTable data = DataController.DataHandler.GetInstance().ExecuteDataTable("DTHSoft_GetAllBangThanhToanLuong", "@idBangLuong", "@MaDonVi", "@MaNhanVien", idBangLuong, MaDonVi, MaNhanVien); var congthuc = (from t in dataContext.BangCongThucLuongs orderby t.TenCot ascending, t.MaBoPhan ascending, t.ChucVu ascending, t.ViTriCongViec ascending, t.ThamNien ascending select new bangtam.bangcongthuc { PRKEY = t.PRKEY, TenCot = t.TenCot, CongThuc = t.CongThuc, TieuDeCot = t.TieuDeCot, ChucVu = t.ChucVu, ViTriCongViec = t.ViTriCongViec, MaBoPhan = t.MaBoPhan, ThamNien = t.ThamNien, EditAble = t.EditAble }).ToList(); List <string> colTableName = GetColumnNames(data); //lay ten cot trong bang List <string> tencot = (from t in typeof(DAL.BangThanhToanLuong).GetProperties() select t.Name).ToList(); //lay ten cot trong csdl //chuyển công thức về dạng định dạng giống y hệt excel foreach (var item in congthuc) { foreach (var abc in congthuc) { item.CongThuc = item.CongThuc.Replace("[" + abc.TenCot.ToString() + "]", ChuyenChiSoSangTenCotExcel(abc.TenCot - 1) + "1"); } } string strsql = "UPDATE TienLuong.BangThanhToanLuong SET "; string strmain = ""; Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; sheet.Name = "Data Source"; int socot = data.Columns.Count; string hihi; List <string> cotTinhBiLoi = new List <string>(); foreach (DataRow item in data.Rows) { for (int i = 4; i <= socot - 6; i++) { try { hihi = GetCongThuc(ref congthuc, i, item["MaBoPhan"] == System.DBNull.Value ? "" : item["MaBoPhan"].ToString(), item["MaChucVu"] == System.DBNull.Value ? "" : item["MaChucVu"].ToString(), item["MaCongViec"] == System.DBNull.Value ? "" : item["MaCongViec"].ToString(), item["ThamNien"] == System.DBNull.Value ? 0 : int.Parse(item["ThamNien"].ToString())); if (hihi != "") { // hihi = congthuc.Where(p => p.TenCot == i).SingleOrDefault().CongThuc;// congthuc.Where(p => p.TenCot == "[" + i.ToString() + "]").SingleOrDefault().CongThuc; sheet.Range[1, i].NumberValue = Math.Round((double)workbook.CaculateFormulaValue(hihi)); sheet.Range[1, i].NumberFormat = "0"; item[i - 1] = Math.Round(double.Parse(sheet.Range[1, i].NumberValue.ToString())); } else if (i != 4) { sheet.Range[1, i].Value2 = Math.Round(double.Parse(item[i - 1].ToString())); } else { sheet.Range[1, i].Value2 = double.Parse(item[i - 1].ToString()); } } catch (Exception e) { if (!cotTinhBiLoi.Contains(congthuc.FirstOrDefault(p => p.TenCot == i).TieuDeCot)) { cotTinhBiLoi.Add(congthuc.FirstOrDefault(p => p.TenCot == i).TieuDeCot); } } } strmain = ""; foreach (var bien in tencot) { if (colTableName.Contains(bien)) { if (bien != "ID") { strmain = strmain + " " + bien + " = " + item[bien].ToString().Replace(',', '.') + " ,"; } } } if (strmain.Length > 0) { strmain = strmain.Remove(strmain.LastIndexOf(',')); DataController.DataHandler.GetInstance().ExecuteNonQuery(strsql + strmain + " WHERE ID = " + item["ID"].ToString()); } if (item["SO_TAI_KHOAN"].ToString().Length > 0) { DataController.DataHandler.GetInstance().ExecuteNonQuery(strsql + " ATM = " + item["ThucLinh"].ToString().Replace(',', '.') + " , " + " TienMat = 0" + " WHERE ID = " + item["ID"].ToString()); } else { DataController.DataHandler.GetInstance().ExecuteNonQuery(strsql + " TienMat = " + item["ThucLinh"].ToString().Replace(',', '.') + " , " + " ATM = 0" + " WHERE ID = " + item["ID"].ToString()); } } return(cotTinhBiLoi.Count > 0 ? "Tính toán công thức bị lỗi tại các cột: " + string.Join(",", cotTinhBiLoi.ToArray()) : ""); #region code cu bỏ //foreach (var item in data) //{ // sheet.Range["E1"].NumberValue = item.LuongCoBan; // sheet.Range["F1"].NumberValue = item.LuongTrachNhiem; // sheet.Range["G1"].NumberValue = item.PhuCapTienAn; // sheet.Range["H1"].NumberValue = item.PhuCapChucVu; // // sheet.Range["I1"].Formula = congthuc.Where(p => p.TenCot == "[9]").SingleOrDefault().CongThuc; // // sheet.Range["I1"].NumberValue = sheet.Range["I1"].FormulaNumberValue; // DAL.BangThanhToanLuong uuu = dataContext.BangThanhToanLuongs.Where(p => p.ID == item.ID).FirstOrDefault(); // uuu.PhuCapKiemNhiem = double.Parse(workbook.CaculateFormulaValue(congthuc.Where(p => p.TenCot == "[9]").SingleOrDefault().CongThuc).ToString()); // // double x = sheet.Range["I1"].FormulaNumberValue; //} //Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); //xlApp.Visible = true; //Excel.Workbook wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); //Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; //Excel.Range a1zz1 = ws.get_Range("A1:ZZ1"); //foreach (DataRow item in data.Rows) //{ // for (int i = 4; i < 44; i++) // { // try // { // Excel.Range cell = (Excel.Range)a1zz1[1, i]; // if (congthuc.Where(p => p.TenCot == "[" + i.ToString() + "]").SingleOrDefault().CongThuc != "") // { // cell.Formula = congthuc.Where(p => p.TenCot == "[" + i.ToString() + "]").SingleOrDefault().CongThuc; // item[i] = cell.Value2; // } // else // cell.Value2 = item[i].ToString(); // //sheet.Range[1, i + 1].Value = item[i].ToString(); // //sheet.Range[1, i + 1].Value2 = item[i]; // //sheet.Range[1, i + 1].NumberValue = double.Parse(item[i].ToString()); // //if (congthuc.Where(p => p.TenCot == "[" + (i + 1).ToString() + "]").SingleOrDefault().CongThuc != "") // //{ // // // hihi = congthuc.Where(p => p.TenCot == "[" + (i + 1).ToString() + "]").SingleOrDefault().CongThuc; // // // sheet.Range[1, i + 1].Formula = hihi;// workbook.CaculateFormulaValue(hihi).ToString(); // // item[i] = workbook.CaculateFormulaValue(sheet.Range[1, i + 1].Formula); // // sheet.Range[1, i + 1].Value = item[i].ToString(); // // sheet.Range[1, i + 1].Value2 = item[i]; // // sheet.Range[1, i + 1].NumberValue = double.Parse(item[i].ToString()); // //} // } // catch (Exception e) // { // // throw new Exception(e.Message); // } // } // } //Workbook workbook = new Workbook(); //Worksheet sheet = workbook.Worksheets[0]; //sheet.Name = "Data Source"; //string hihi; //foreach (DataRow item in data.Rows) //{ // for (int i = 4; i < 44; i++) // { // try // { // sheet.Range[1, i + 1].Value = item[i].ToString(); // sheet.Range[1, i + 1].Value2 = item[i]; // sheet.Range[1, i + 1].NumberValue = double.Parse(item[i].ToString()); // if (congthuc.Where(p => p.TenCot == "[" + (i + 1).ToString() + "]").SingleOrDefault().CongThuc != "") // { // hihi = congthuc.Where(p => p.TenCot == "[" + (i + 1).ToString() + "]").SingleOrDefault().CongThuc; // sheet.Range[1, i + 1].Formula = hihi;// workbook.CaculateFormulaValue(hihi).ToString(); // item[i] = workbook.CaculateFormulaValue(sheet.Range[1, i + 1].Formula); // sheet.Range[1, i + 1].Value = item[i].ToString(); // sheet.Range[1, i + 1].Value2 = item[i]; // sheet.Range[1, i + 1].NumberValue = double.Parse(item[i].ToString()); // } // } // catch (Exception e) // { // // throw new Exception(e.Message); // } // } //} //foreach (var item in data) //{ // sheet.Range["E1"].NumberValue = item.LuongCoBan; // sheet.Range["F1"].NumberValue = item.LuongTrachNhiem; // sheet.Range["G1"].NumberValue = item.PhuCapTienAn; // sheet.Range["H1"].NumberValue = item.PhuCapChucVu; // // sheet.Range["I1"].Formula = congthuc.Where(p => p.TenCot == "[9]").SingleOrDefault().CongThuc; // // sheet.Range["I1"].NumberValue = sheet.Range["I1"].FormulaNumberValue; // DAL.BangThanhToanLuong uuu = dataContext.BangThanhToanLuongs.Where(p => p.ID == item.ID).FirstOrDefault(); // uuu.PhuCapKiemNhiem = double.Parse(workbook.CaculateFormulaValue(congthuc.Where(p => p.TenCot == "[9]").SingleOrDefault().CongThuc).ToString()); // // double x = sheet.Range["I1"].FormulaNumberValue; //} #endregion }