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