/// <summary>
/// �õ�һ������ʵ��
/// </summary>
public LabMS.Model.ResearchCost GetModel(int key)
{
//�ñ���������Ϣ�����Զ�������/�����ֶ�
StringBuilder strSql=new StringBuilder();
strSql.Append("select top 1 [key], [ID],ChongJingZiHao,XiangMuMingCheng,XiangMuLieBie,FuZeRen,DanWeiMingCheng,ShiDaoJingFei,RuanJianJingFei,GuangLiFeiBiLi,GuangLiFei,JingBanRen,ShiDaoShiJian,HuiChuYinHang from ResearchCost ");
strSql.Append(" where [key]=@Key");
SqlParameter[] parameters = {
new SqlParameter("@Key", key)
};
LabMS.Model.ResearchCost model=new LabMS.Model.ResearchCost();
DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
if(ds.Tables[0].Rows.Count>0)
{
model.Key = int.Parse(ds.Tables[0].Rows[0]["Key"].ToString());
model.ID=ds.Tables[0].Rows[0]["ID"].ToString();
model.ChongJingZiHao=ds.Tables[0].Rows[0]["ChongJingZiHao"].ToString();
model.XiangMuMingCheng=ds.Tables[0].Rows[0]["XiangMuMingCheng"].ToString();
model.XiangMuLieBie=ds.Tables[0].Rows[0]["XiangMuLieBie"].ToString();
model.FuZeRen=ds.Tables[0].Rows[0]["FuZeRen"].ToString();
model.DanWeiMingCheng=ds.Tables[0].Rows[0]["DanWeiMingCheng"].ToString();
model.ShiDaoJingFei=ds.Tables[0].Rows[0]["ShiDaoJingFei"].ToString();
model.RuanJianJingFei=ds.Tables[0].Rows[0]["RuanJianJingFei"].ToString();
model.GuangLiFeiBiLi=ds.Tables[0].Rows[0]["GuangLiFeiBiLi"].ToString();
model.GuangLiFei=ds.Tables[0].Rows[0]["GuangLiFei"].ToString();
model.JingBanRen=ds.Tables[0].Rows[0]["JingBanRen"].ToString();
model.ShiDaoShiJian=ds.Tables[0].Rows[0]["ShiDaoShiJian"].ToString();
model.HuiChuYinHang=ds.Tables[0].Rows[0]["HuiChuYinHang"].ToString();
return model;
}
else
{
return null;
}
}
/// <summary>
/// ����
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnSubmit_Click(object sender, EventArgs e)
{
string fileName = String.Empty;
try
{
#region Validation
if (!ExcelFile.HasFile)
{
WriteBackScript("alert('�����ļ�����Ϊ��')");
return;
}
else
{
string extension = ExtendBLL.FileUtility.GetFileExtension(ExcelFile.FileName);
if (!extension.Equals("xls") && !extension.Equals("xlsx"))
{
WriteBackScript("alert('�����ļ���ʽ����ȷ������ΪExcel�ļ�!')");
return;
}
fileName = AppDomain.CurrentDomain.BaseDirectory + "\\ResearchData\\TmpFiles\\" + Guid.NewGuid().ToString() + "." + extension;
}
#endregion
// Upload File
ExcelFile.SaveAs(fileName);
#region Get Excel
MSExcel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
try
{
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
MSExcel.Workbooks workBooks = excelApp.Workbooks;
MSExcel.Workbook workbook = (MSExcel.Workbook)workBooks.Open(fileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
if (workbook == null)
{
WriteBackScript("alert('������������!')");
return;
}
MSExcel.Worksheet worksheet = (MSExcel.Worksheet)workbook.ActiveSheet;
if (worksheet == null)
{
WriteBackScript("alert('Excel���������')");
return;
}
// ��ȡ��һ�е�ֵ
object[,] firstRow = (object[,])worksheet.get_Range("1:1", Missing.Value).Value2;
System.Collections.Generic.List<string> titles = new System.Collections.Generic.List<string>();
foreach (object column in firstRow)
{
if (column != null)
{
titles.Add(column.ToString());
}
}
System.Collections.Generic.List<string> expectedTitles = new System.Collections.Generic.List<string>();
#region �жϱ���
switch (DataType.SelectedValue)
{
case "1":
ExtendBLL.ResearchResult rr = new ExtendBLL.ResearchResult();
expectedTitles = rr.GetTitles();
break;
case "2":
ExtendBLL.ResearchCost rc = new ExtendBLL.ResearchCost();
expectedTitles = rc.GetTitles();
break;
case "3":
ExtendBLL.ResearchArticle ra = new ExtendBLL.ResearchArticle();
expectedTitles = ra.GetTitles();
break;
case "4":
default:
ExtendBLL.ResearchProject rp = new ExtendBLL.ResearchProject();
expectedTitles = rp.GetTitles();
break;
}
if (expectedTitles.Count != titles.Count)
{
WriteBackScript("alert('Excel�ļ�ģ�岻ƥ��!')");
return;
}
for (int i = titles.Count - 1; i > -1; i--)
{
if (!titles[i].Equals(expectedTitles[i]))
{
WriteBackScript("alert('Excel�ļ�ģ�岻ƥ��!')");
return;
}
}
#endregion
#region ��������
bool allOk = true;
string type = Request.Params["type"].ToString();
LabMS.Model.ResearchImportType importType = type.Equals("1") ? LabMS.Model.ResearchImportType.Add :
(type.Equals("2") ? LabMS.Model.ResearchImportType.RecoverAdd : LabMS.Model.ResearchImportType.DeleteAdd);
double timeTmp = 0.0d; // ʱ��
switch (DataType.SelectedValue)
{
case "1":
ExtendBLL.ResearchResult rr = new ExtendBLL.ResearchResult();
System.Collections.Generic.List<LabMS.Model.ResearchResult> models = new System.Collections.Generic.List<LabMS.Model.ResearchResult>();
LabMS.Model.ResearchResult rrInfo;
for (int i = 2; i < worksheet.Rows.Count; i++)
{
object[,] row = (object[,])worksheet.get_Range(string.Format("{0}:{0}", i), Missing.Value).Value2;
bool allEmpty = true;
for (int j = titles.Count - 1; j > -1; j--)
{
foreach (object c in row)
{
allEmpty &= (c == null);
}
}
if (allEmpty) { break; } // ��ֵ
if (row[1, 1] == null) // ID
{
continue;
}
rrInfo = new LabMS.Model.ResearchResult();
rrInfo.ID = (row[1, 1] == null) ? String.Empty : row[1, 1].ToString();
rrInfo.ChengGuoMingCheng = (row[1, 2] == null) ? String.Empty : row[1, 2].ToString();
rrInfo.ChengGuoLeiBie = (row[1, 3] == null) ? String.Empty : row[1, 3].ToString();
rrInfo.ChengGuoZuoZe = (row[1, 4] == null) ? String.Empty : row[1, 4].ToString();
rrInfo.DanWeiMingCheng = (row[1, 5] == null) ? String.Empty : row[1, 5].ToString();
rrInfo.BanZhengShiJian = (row[1, 6] == null) ? String.Empty : row[1, 6].ToString();
rrInfo.XueYuanShenHe = (row[1, 7] == null) ? String.Empty : row[1, 7].ToString();
rrInfo.XueXiaoShenHe = (row[1, 8] == null) ? String.Empty : row[1, 8].ToString();
rrInfo.ChengGuoDengJi = (row[1, 9] == null) ? String.Empty : row[1, 9].ToString();
rrInfo.ShuMingZuoZe = (row[1, 10] == null) ? String.Empty : row[1, 10].ToString();
rrInfo.ChengGuoBianHao = (row[1, 11] == null) ? String.Empty : row[1, 11].ToString();
rrInfo.ShiFouJiangLi = (row[1, 12] == null) ? String.Empty : row[1, 12].ToString();
rrInfo.ShuMingDanWei = (row[1, 13] == null) ? String.Empty : row[1, 13].ToString();
rrInfo.ChengGuoLieXing = (row[1, 14] == null) ? String.Empty : row[1, 14].ToString();
if (double.TryParse(rrInfo.BanZhengShiJian, out timeTmp))
{
DateTime dt = new DateTime(1900, 1, 1);
rrInfo.BanZhengShiJian = dt.AddDays(timeTmp - 2).ToShortDateString();
}
else
{
allOk = false;
continue;
}
models.Add(rrInfo);
}
rr.Insert(models, importType);
break;
case "2":
// worksheet.get_Range("L:L", Missing.Value).AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatNone,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ExtendBLL.ResearchCost rc = new ExtendBLL.ResearchCost();
System.Collections.Generic.List<LabMS.Model.ResearchCost> rcModels = new System.Collections.Generic.List<LabMS.Model.ResearchCost>();
LabMS.Model.ResearchCost rcInfo;
for (int i = 2; i < worksheet.Rows.Count; i++)
{
object[,] row = (object[,])worksheet.get_Range(string.Format("{0}:{0}", i), Missing.Value).Value2;
bool allEmpty = true;
for (int j = titles.Count - 1; j > -1; j--)
{
foreach (object c in row)
{
allEmpty &= (c == null);
}
}
if (allEmpty) { break; } // ��ֵ
if (row[1, 1] == null) // ID
{
continue;
}
rcInfo = new LabMS.Model.ResearchCost();
rcInfo.ID = (row[1, 1] == null) ? String.Empty : row[1, 1].ToString();
rcInfo.ChongJingZiHao = (row[1, 2] == null) ? String.Empty : row[1, 2].ToString();
rcInfo.XiangMuMingCheng = (row[1, 3] == null) ? String.Empty : row[1, 3].ToString();
rcInfo.XiangMuLieBie = (row[1, 4] == null) ? String.Empty : row[1, 4].ToString();
rcInfo.FuZeRen = (row[1, 5] == null) ? String.Empty : row[1, 5].ToString();
rcInfo.DanWeiMingCheng = (row[1, 6] == null) ? String.Empty : row[1, 6].ToString();
rcInfo.ShiDaoJingFei = (row[1, 7] == null) ? String.Empty : row[1, 7].ToString();
rcInfo.RuanJianJingFei = (row[1, 8] == null) ? String.Empty : row[1, 8].ToString();
rcInfo.GuangLiFeiBiLi = (row[1, 9] == null) ? String.Empty : row[1, 9].ToString();
rcInfo.GuangLiFei = (row[1, 10] == null) ? String.Empty : row[1, 10].ToString();
rcInfo.JingBanRen = (row[1, 11] == null) ? String.Empty : row[1, 11].ToString();
rcInfo.ShiDaoShiJian = (row[1, 12] == null) ? String.Empty : row[1, 12].ToString();
rcInfo.HuiChuYinHang = (row[1, 13] == null) ? String.Empty : row[1, 13].ToString();
if (double.TryParse(rcInfo.ShiDaoShiJian, out timeTmp))
{
DateTime dttemp = new DateTime(1900, 1, 1);
dttemp = dttemp.AddDays(timeTmp - 2);
rcInfo.ShiDaoShiJian = dttemp.ToShortDateString();
}
else // ���ڲ���ȷ
{
allOk = false;
continue;
}
rcModels.Add(rcInfo);
}
rc.Insert(rcModels, importType);
break;
case "3":
ExtendBLL.ResearchArticle ra = new ExtendBLL.ResearchArticle();
System.Collections.Generic.List<LabMS.Model.ResearchArticle> raModels = new System.Collections.Generic.List<LabMS.Model.ResearchArticle>();
LabMS.Model.ResearchArticle raInfo;
for (int i = 2; i < worksheet.Rows.Count; i++)
{
object[,] row = (object[,])worksheet.get_Range(string.Format("{0}:{0}", i), Missing.Value).Value2;
bool allEmpty = true;
for (int j = titles.Count - 1; j > -1; j--)
{
foreach (object c in row)
{
allEmpty &= (c == null);
}
}
if (allEmpty) { break; } // ��ֵ
if (row[1, 1] == null) // ID
{
continue;
}
raInfo = new LabMS.Model.ResearchArticle();
raInfo.ID = (row[1, 1] == null) ? String.Empty : row[1, 1].ToString();
raInfo.LunWenTiMuZhongWen = (row[1, 2] == null) ? String.Empty : row[1, 2].ToString();
raInfo.JiangLiZuoZe = (row[1, 3] == null) ? String.Empty : row[1, 3].ToString();
raInfo.JiangLiZuoZeDanWei = (row[1, 4] == null) ? String.Empty : row[1, 4].ToString();
raInfo.QiKanMingCheng = (row[1, 5] == null) ? String.Empty : row[1, 5].ToString();
raInfo.QiKanNianJuan = (row[1, 6] == null) ? String.Empty : row[1, 6].ToString();
raInfo.QiKanChuBanNianYue = (row[1, 7] == null) ? String.Empty : row[1, 7].ToString();
raInfo.JianSuoHao = (row[1, 8] == null) ? String.Empty : row[1, 8].ToString();
raInfo.XueYuanShenHe = (row[1, 9] == null) ? String.Empty : row[1, 9].ToString();
raInfo.XueXiaoShenHe = (row[1, 10] == null) ? String.Empty : row[1, 10].ToString();
raInfo.ShuRuRen = (row[1, 11] == null) ? String.Empty : row[1, 11].ToString();
raInfo.ShuRuShiJian = (row[1, 12] == null) ? String.Empty : row[1, 12].ToString();
raInfo.ShiFouRenLing = (row[1, 13] == null) ? String.Empty : row[1, 13].ToString();
raInfo.LunWenShuMingZuoZe = (row[1, 14] == null) ? String.Empty : row[1, 14].ToString();
raInfo.EIHeXinJianSuo = (row[1, 15] == null) ? String.Empty : row[1, 15].ToString();
raInfo.CSCDKuoZhanQiKan = (row[1, 16] == null) ? String.Empty : row[1, 16].ToString();
raInfo.QiKanShuXing = (row[1, 17] == null) ? String.Empty : row[1, 17].ToString();
raInfo.QiKanGuoBie = (row[1, 18] == null) ? String.Empty : row[1, 18].ToString();
raInfo.CSCDHeXinYuanQiKan = (row[1, 19] == null) ? String.Empty : row[1, 19].ToString();
raInfo.SCIEJianSuo = (row[1, 20] == null) ? String.Empty : row[1, 20].ToString();
raInfo.GaoShuiPingQiKan = (row[1, 21] == null) ? String.Empty : row[1, 21].ToString();
raInfo.LunWenLeiBie = (row[1, 22] == null) ? String.Empty : row[1, 22].ToString();
raInfo.CSSCIYuanQiKan = (row[1, 23] == null) ? String.Empty : row[1, 23].ToString();
raInfo.EIKuoZhanYuanJianSuo = (row[1, 24] == null) ? String.Empty : row[1, 24].ToString();
if (double.TryParse(raInfo.ShuRuShiJian, out timeTmp))
{
DateTime dt = new DateTime(1900, 1, 1);
raInfo.ShuRuShiJian = dt.AddDays(timeTmp - 2).ToShortDateString();
}
else
{
allOk = false;
continue;
}
raModels.Add(raInfo);
}
ra.Insert(raModels, importType);
break;
case "4":
default:
ExtendBLL.ResearchProject rp = new ExtendBLL.ResearchProject();
System.Collections.Generic.List<LabMS.Model.ResearchProject> rpModels = new System.Collections.Generic.List<LabMS.Model.ResearchProject>();
LabMS.Model.ResearchProject rpInfo;
for (int i = 2; i < worksheet.Rows.Count; i++)
{
object[,] row = (object[,])worksheet.get_Range(string.Format("{0}:{0}", i), Missing.Value).Value2;
bool allEmpty = true;
for (int j = titles.Count - 1; j > -1; j--)
{
foreach (object c in row)
{
allEmpty &= (c == null);
}
}
if (allEmpty) { break; } // ��ֵ
if (row[1, 1] == null) // ID
{
continue;
}
rpInfo = new LabMS.Model.ResearchProject();
rpInfo.ID = (row[1, 1] == null) ? String.Empty : row[1, 1].ToString();
rpInfo.XiangMuBianHao = (row[1, 2] == null) ? String.Empty : row[1, 2].ToString();
rpInfo.XiangMuPiZhunHao = (row[1, 3] == null) ? String.Empty : row[1, 3].ToString();
rpInfo.XiangMuMingCheng = (row[1, 4] == null) ? String.Empty : row[1, 4].ToString();
rpInfo.FuZeRen = (row[1, 5] == null) ? String.Empty : row[1, 5].ToString();
rpInfo.DanWeiMingCheng = (row[1, 6] == null) ? String.Empty : row[1, 6].ToString();
rpInfo.XiangMuLeiBie = (row[1, 7] == null) ? String.Empty : row[1, 7].ToString();
rpInfo.JingFeiBenHao = (row[1, 8] == null) ? String.Empty : row[1, 8].ToString();
rpInfo.HeTongJingFei = (row[1, 9] == null) ? String.Empty : row[1, 9].ToString();
rpInfo.GuoBoJingFei = (row[1, 10] == null) ? String.Empty : row[1, 10].ToString();
rpInfo.YiDaoJingFei = (row[1, 11] == null) ? String.Empty : row[1, 11].ToString();
rpInfo.RuanJianJingFei = (row[1, 12] == null) ? String.Empty : row[1, 12].ToString();
rpInfo.XiangMuKaiShiShiJian = (row[1, 13] == null) ? String.Empty : row[1, 13].ToString();
rpInfo.XiangMuJieShuShiJian = (row[1, 14] == null) ? String.Empty : row[1, 14].ToString();
rpInfo.GuangLiRen = (row[1, 15] == null) ? String.Empty : row[1, 15].ToString();
rpInfo.WeiTuoDanWei = (row[1, 16] == null) ? String.Empty : row[1, 16].ToString();
rpInfo.KeiTiZuChengYuan = (row[1, 17] == null) ? String.Empty : row[1, 17].ToString();
rpInfo.WeiTuoDanWeiGuangLiBuMen = (row[1, 18] == null) ? String.Empty : row[1, 18].ToString();
rpInfo.HeTongNianDu = (row[1, 19] == null) ? String.Empty : row[1, 19].ToString();
rpInfo.YiJiLeiBie = (row[1, 20] == null) ? String.Empty : row[1, 20].ToString();
#region ʱ��
if (double.TryParse(rpInfo.XiangMuJieShuShiJian, out timeTmp))
{
DateTime dt = new DateTime(1900, 1, 1);
rpInfo.XiangMuJieShuShiJian = dt.AddDays(timeTmp - 2).ToShortDateString();
}
else
{
allOk =false;
continue;
}
if (double.TryParse(rpInfo.XiangMuKaiShiShiJian, out timeTmp))
{
DateTime dt = new DateTime(1900, 1, 1);
rpInfo.XiangMuKaiShiShiJian = dt.AddDays(timeTmp - 2).ToShortDateString();
}
else
{
allOk = false;
continue;
}
if (double.TryParse(rpInfo.HeTongNianDu, out timeTmp))
{
DateTime dt = new DateTime(1900, 1, 1);
rpInfo.HeTongNianDu = dt.AddDays(timeTmp - 2).ToShortDateString();
}
else
{
allOk = false;
continue;
}
#endregion
rpModels.Add(rpInfo);
}
rp.Insert(rpModels, importType);
break;
}
if (!allOk)
{
WriteBackScript("alert('�����е�ʱ���ʽ����ȷ,�����ļ���ѡ��[�ӵ���(���������ظ�����)]��ʽ���µ�������!');");
return;
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
}
}
#endregion
WriteBackScript("alert('�������ݳɹ�!')");
}
catch (Exception ex)
{
WriteBackScript(string.Format("alert('{0}')", Server.HtmlEncode(ex.Message)));
}
}