/// <summary>
/// excel文件导入
/// </summary>
/// <param name="excelFilePath">excel文件路径</param>
/// <param name="fileName">文件名</param>
public void ExcelFileUpload(string excelFilePath, string fileName)
{
IWorkbook workbook = null; //新建IWorkbook对象
FileStream fileStream = new FileStream(excelFilePath + fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fileStream); //xlsx数据读入workbook
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fileStream); //xls数据读入workbook
}
for (int s = 0; s < workbook.NumberOfSheets; s++) //遍历所有工作簿
{
ISheet sheet = workbook.GetSheetAt(s); //获取工作表
IRow row; // = sheet.GetRow(0); //新建当前工作表行数据
for (int i = 2; i < sheet.LastRowNum; i++) //从第3行开始读取EXCEL工作簿表格每行
{
row = sheet.GetRow(i); //row读入第i行数据
if (row == null) //如果A列数据为空，退出循环
{
break;
}
else
{
if (row.GetCell(0).ToString() == "TOTAL")//如果A列数据为空，退出循环
{
break;
}
}
if (row != null) // 行不为空时，对数据进行处理
{
Model.gzmx model = new Model.gzmx(); //新建zxkc数据模型对像
model.year = slt_year.Value;
model.month = slt_month.Value;
model.depart = GetStringValue(row.GetCell(0));
model.departid = GetStringValue(row.GetCell(1));
model.empid = GetStringValue(row.GetCell(3));
model.name = GetStringValue(row.GetCell(2));
model.gzjb = ""; //GetStringValue(row.GetCell(4));
model.yfje = row.GetCell(4).NumericCellValue; //GetDoubleValue(row.GetCell(3).StringCellValue);
model.kqtk = row.GetCell(5).NumericCellValue; //GetDoubleValue(row.GetCell(4));
model.kk = row.GetCell(6).NumericCellValue; //GetDoubleValue(row.GetCell(5));
model.gsk = row.GetCell(7).NumericCellValue; //GetDoubleValue(row.GetCell(6));
model.gjj2 = row.GetCell(8).NumericCellValue; //GetDoubleValue(row.GetCell(7));
model.gjj = row.GetCell(9).NumericCellValue; //GetDoubleValue(row.GetCell(8));
model.bxkk = row.GetCell(10).NumericCellValue; //GetDoubleValue(row.GetCell(7));
model.sfje = row.GetCell(12).NumericCellValue; //GetDoubleValue(row.GetCell(8));
bll.Add(model);
}
}
}
fileStream.Close();
workbook.Close();
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.gzmx model)
{
return(dal.Update(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.gzmx model)
{
return(dal.Add(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.gzmx model)
{
int newId;
using (SqlConnection conn = new SqlConnection(DbHelperSql.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into t_gzmx(");
strSql.Append("year,month,depart,departid,empid,name,gzjb,yfje,kqtk,kk,gsk,gjj2,gjj,bxkk,sfje)");
strSql.Append(" values (");
strSql.Append("@year,@month,@depart,@departid,@empid,@name,@gzjb,@yfje,@kqtk,@kk,@gsk,@gjj2,@gjj,@bxkk,@sfje)");
SqlParameter[] parameters =
{
new SqlParameter("@year", SqlDbType.VarChar, 50),
new SqlParameter("@month", SqlDbType.VarChar, 50),
new SqlParameter("@depart", SqlDbType.VarChar, 50),
new SqlParameter("@departid", SqlDbType.VarChar, 50),
new SqlParameter("@empid", SqlDbType.VarChar, 50),
new SqlParameter("@name", SqlDbType.VarChar, 50),
new SqlParameter("@gzjb", SqlDbType.VarChar, 50),
new SqlParameter("@yfje", SqlDbType.Decimal),
new SqlParameter("@kqtk", SqlDbType.Decimal),
new SqlParameter("@kk", SqlDbType.Decimal),
new SqlParameter("@gsk", SqlDbType.Decimal),
new SqlParameter("@gjj2", SqlDbType.Decimal),
new SqlParameter("@gjj", SqlDbType.Decimal),
new SqlParameter("@bxkk", SqlDbType.Decimal),
new SqlParameter("@sfje", SqlDbType.Decimal)
};
parameters[0].Value = model.year;
parameters[1].Value = model.month;
parameters[2].Value = model.depart;
parameters[3].Value = model.departid;
parameters[4].Value = model.empid;
parameters[5].Value = model.name;
parameters[6].Value = model.gzjb;
parameters[7].Value = model.yfje;
parameters[8].Value = model.kqtk;
parameters[9].Value = model.kk;
parameters[10].Value = model.gsk;
parameters[11].Value = model.gjj2;
parameters[12].Value = model.gjj;
parameters[13].Value = model.bxkk;
parameters[14].Value = model.sfje;
DbHelperSql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.gzmx GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id,year,month,empid,name,yfje,kqtk,kk,gsk,gjj2,gjj,bxkk,sfje from t_gzmx ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
Model.gzmx model = new Model.gzmx();
DataSet ds = DbHelperSql.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
{
model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
}
if (ds.Tables[0].Rows[0]["year"] != null && ds.Tables[0].Rows[0]["year"].ToString() != "")
{
model.year = ds.Tables[0].Rows[0]["year"].ToString();
}
if (ds.Tables[0].Rows[0]["month"] != null && ds.Tables[0].Rows[0]["month"].ToString() != "")
{
model.month = ds.Tables[0].Rows[0]["month"].ToString();
}
if (ds.Tables[0].Rows[0]["empid"] != null && ds.Tables[0].Rows[0]["empid"].ToString() != "")
{
model.empid = ds.Tables[0].Rows[0]["empid"].ToString();
}
if (ds.Tables[0].Rows[0]["name"] != null && ds.Tables[0].Rows[0]["name"].ToString() != "")
{
model.name = ds.Tables[0].Rows[0]["name"].ToString();
}
if (ds.Tables[0].Rows[0]["yfje"] != null && ds.Tables[0].Rows[0]["yfje"].ToString() != "")
{
model.yfje = double.Parse(ds.Tables[0].Rows[0]["yfje"].ToString());
}
if (ds.Tables[0].Rows[0]["kqtk"] != null && ds.Tables[0].Rows[0]["kqtk"].ToString() != "")
{
model.kqtk = double.Parse(ds.Tables[0].Rows[0]["kqtk"].ToString());
}
if (ds.Tables[0].Rows[0]["kk"] != null && ds.Tables[0].Rows[0]["kk"].ToString() != "")
{
model.kk = double.Parse(ds.Tables[0].Rows[0]["kk"].ToString());
}
if (ds.Tables[0].Rows[0]["gsk"] != null && ds.Tables[0].Rows[0]["gsk"].ToString() != "")
{
model.gsk = double.Parse(ds.Tables[0].Rows[0]["gsk"].ToString());
}
if (ds.Tables[0].Rows[0]["gjj2"] != null && ds.Tables[0].Rows[0]["gjj2"].ToString() != "")
{
model.gjj2 = double.Parse(ds.Tables[0].Rows[0]["gjj2"].ToString());
}
if (ds.Tables[0].Rows[0]["gjj"] != null && ds.Tables[0].Rows[0]["gjj"].ToString() != "")
{
model.gjj = double.Parse(ds.Tables[0].Rows[0]["gjj"].ToString());
}
if (ds.Tables[0].Rows[0]["bxkk"] != null && ds.Tables[0].Rows[0]["bxkk"].ToString() != "")
{
model.bxkk = double.Parse(ds.Tables[0].Rows[0]["bxkk"].ToString());
}
if (ds.Tables[0].Rows[0]["sfje"] != null && ds.Tables[0].Rows[0]["sfje"].ToString() != "")
{
model.sfje = double.Parse(ds.Tables[0].Rows[0]["sfje"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.gzmx model)
{
using (SqlConnection conn = new SqlConnection(DbHelperSql.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update t_gzmx set ");
strSql.Append("year=@year,");
strSql.Append("month=@month,");
strSql.Append("name=@name,");
strSql.Append("yfje=@yfje,");
strSql.Append("kqtk=@kqtk,");
strSql.Append("kk=@kk,");
strSql.Append("gsk=@gsk,");
strSql.Append("gjj2=@gjj2,");
strSql.Append("gjj=@gjj,");
strSql.Append("bxkk=@bxkk,");
strSql.Append("sfje=@sfje");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@year", SqlDbType.VarChar, 50),
new SqlParameter("@month", SqlDbType.VarChar, 50),
new SqlParameter("@empid", SqlDbType.VarChar, 50),
new SqlParameter("@name", SqlDbType.VarChar, 50),
new SqlParameter("@yfje", SqlDbType.Decimal),
new SqlParameter("@kqtk", SqlDbType.Decimal),
new SqlParameter("@kk", SqlDbType.Decimal),
new SqlParameter("@gsk", SqlDbType.Decimal),
new SqlParameter("@gjj2", SqlDbType.Decimal),
new SqlParameter("@gjj", SqlDbType.Decimal),
new SqlParameter("@bxkk", SqlDbType.Decimal),
new SqlParameter("@sfje", SqlDbType.Decimal),
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = model.year;
parameters[1].Value = model.month;
parameters[2].Value = model.empid;
parameters[3].Value = model.name;
parameters[4].Value = model.yfje;
parameters[5].Value = model.kqtk;
parameters[6].Value = model.kk;
parameters[7].Value = model.gsk;
parameters[8].Value = model.gjj2;
parameters[9].Value = model.gjj;
parameters[10].Value = model.bxkk;
parameters[11].Value = model.sfje;
parameters[12].Value = model.id;
DbHelperSql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
trans.Commit();
}
catch
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
