public static decimal GetCal(string f, List <string> args)
{
decimal result = 0;
Expression ep = new Expression(f);
if (!ep.HasErrors())
{
List <string> t = ExtractFromString(ep.ParsedExpression.ToString(), "[", "]");
foreach (var v in t)
{
string tmp = args[SysUtil.GetExcelColumnIntValue(v) - 1].Trim();
if (tmp == string.Empty)
{
ep.Parameters[v] = 0;
}
else
{
ep.Parameters[v] = Convert.ToDecimal(tmp);
}
}
return(Convert.ToDecimal(ep.Evaluate()));
}
else
{
throw new Exception("公式计算错误");
}
return(result);
}
private void btnImport_Click(object sender, EventArgs e)
{
btnImport.Enabled = false; //防止误操作
dgvSals.Rows.Clear(); //清空原数据
//检查数据是否填写
if (checkInput() == false)
{
MessageBox.Show("请检查输入项", this.funName, MessageBoxButtons.OK);
btnImport.Enabled = true;
return;
}
if (cbxAgentCs.SelectedItem == null)
{
MessageBox.Show("请选择国别", this.funName, MessageBoxButtons.OK);
btnImport.Enabled = true;
return;
}
//参数缓存保存至文件
TmpData td = new TmpData
{
Title1 = txtYearTitle1.Text.Trim(),
Title2 = txtYearTitle2.Text.Trim(),
Annu = txtYearAnnu.Text.Trim(),
Rate1 = txtERate1.Text.Trim(),
Rate2 = txtERate2.Text.Trim(),
Date1 = dtDeclare.Value,
Date2 = dtTableOn.Value
};
SaveToFile(td);
//获取模板
int tmpID = (int)db.AGENT_COUNTRies.SingleOrDefault(a => a.AC_ID == ((AGENT_COUNTRY)cbxAgentCs.SelectedItem).AC_ID).AC_TIID;
TEMPLATE_INFO ti = db.TEMPLATE_INFOs.SingleOrDefault(a => a.TI_ID == tmpID);
//获取年金限额
decimal YearAnnuLimit = Convert.ToDecimal(txtYearAnnu.Text.Trim());
ExcelHelper excel = new ExcelHelper();
try
{
excel.OpenFile(txtDir.Text, 1, true);
int rowCount = excel.GetDataRowsCount((int)ti.TI_DATAROW);
//检查列数是否一致
if (excel.GetColumnsCount() != ti.TI_COLSCOUNT)
{
MessageBox.Show("文件输入项与模板列数不一致，请检查后再导入");
btnImport.Enabled = true;
return;
}
//检查表头是否一致
if (!cbxNeedCheck.Checked)
{
if (TemplateUtil.CheckTitle(tmpID, excel.GetHeader((int)ti.TI_HEARDERROW)) == false)
{
MessageBox.Show("导入文件表头与模板不一致，请检查", this.funName, MessageBoxButtons.OK);
btnImport.Enabled = true;
return;
}
}
//获取EXCEL薪资数据,姓名为KEY,各月数据LIST
Dictionary <string, List <List <string> > > lists = excel.GetRows((int)ti.TI_DATAROW, rowCount, SysUtil.GetExcelColumnIntValue(ti.TI_NAMECOL));
//检查纳税人是否存在
List <string> namenotexsist = new List <string>();
foreach (var name in lists.Keys)
{
if (namenotexsist.Contains(name) == false)
{
if (db.TAX_PLAYERs.SingleOrDefault(a => a.TP_NAME == name && a.TP_ACID == this.ACID) == null)
{
namenotexsist.Add(name);
}
}
}
if (namenotexsist.Count > 0)
{
MessageBox.Show("以下员工信息尚不存在：" + string.Join <string>(";", namenotexsist), this.funName);
btnImport.Enabled = true;
return;
}
Names = lists.Keys.ToList(); //需要导入的已纳税人姓名,下述按姓名
TaxAlready = new Dictionary <string, decimal>(); //已纳税数据缓存
decimal rate2 = Convert.ToDecimal(txtERate2.Text); //美元人民币汇率
foreach (var item in lists) //每个纳税人
{
string name = item.Key;
var value = item.Value;
foreach (var v in value) //每个纳税人,每月记录
{
var newv = v.ToList(); //每个纳税人,每月每项数据
string sBonus = v[SysUtil.GetExcelColumnIntValue(ti.TI_BONUSCOL) - 1]; //年度奖金
decimal annu = Convert.ToDecimal(v[SysUtil.GetExcelColumnIntValue(ti.TI_ANNUCOL) - 1]); //企业年金
decimal fortax = FormulaC.GetCal(ti.TI_SALF, v); //计算税前
if (Math.Abs(annu) > Math.Abs(YearAnnuLimit)) //企业年金与年金限额比较
{
fortax += (Math.Abs(annu) - Math.Abs(YearAnnuLimit));
}
newv.Insert(0, name); //姓名
newv.Insert(1, v[SysUtil.GetExcelColumnIntValue(ti.TI_MONTHCOL) - 1]); //月份
newv.Insert(2, fortax.ToString()); //税前
newv.Insert(3, v[SysUtil.GetExcelColumnIntValue(ti.TI_REALCOL) - 1]); //实发美元
newv.Insert(4, (Convert.ToDecimal(newv[3]) * rate2).ToString("")); //实发RMB
newv.Insert(5, ""); //已纳税美元
newv.Insert(6, ""); //已纳税RMB
newv.Insert(7, v[SysUtil.GetExcelColumnIntValue(ti.TI_BONUSCOL) - 1]); //奖金
object[] oA = new object[] { false }; //首项为是否选中
object[] oB = oA.Concat(newv.ToArray()).ToArray();
dgvSals.Rows.Add(oB.ToArray()); //加入数据表显示
}
}
//显示数据统计信息
UpdateInfo();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Close();
btnImport.Enabled = true;
}
}