<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Sabbaticalimport.aspx.cs" Inherits="hr_Sabbatical_Sabbaticalimport" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>公休导入</title>
<link href="../../common/css/common.css" rel="stylesheet" type="text/css" />
<link href="../../common/themes/default/easyui.css" rel="stylesheet" type="text/css" />
<link href="../../common/themes/icon.css" rel="stylesheet" type="text/css" />
<script src="../../common/js/jquery-1.7.1.min.js" type="text/javascript"></script>
<script src="../../common/js/common.js" type="text/javascript"></script>
<script src="../../common/DatePicker/WdatePicker.js" type="text/javascript"></script>
<script src="../../common/js/jquery.easyui.min.js" type="text/javascript"></script>
<script src="../../common/js/jquery.urldecoder.min.js" type="text/javascript"></script>
<script src="../../common/js/loading.js" type="text/javascript"></script>
<script src="js/SabbaticalEdit.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
loading.getLoadingHtml({ status: '0', explain: '正在为您处理，请稍后...' });
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div class="title" style ="overflow:hidden;">
<table style="width:100%;" border="0" cellpadding="0" cellspacing="0">
<tr>
<td style="text-align:left;height:41px;color:#2153A0;font-size:14px;font-weight:bold;text-indent:10px;">
公休导入
<span class="back"><a href="SabbaticalList.aspx?">返回</a></span>
</td>
<td>
</td>
</tr>
</table>
</div>
<div>
<div>
<asp:Label ID="lblMessage" runat="server" Font-Bold="True" ForeColor="Red" EnableViewState="false"></asp:Label>
</div>
<div><a href="../exceltemplate/员工公休信息.xls">下载公休基本信息模板.xls</a></div>
<div>
<asp:FileUpload ID="fileSocial" class="btn" style="width:200px;margin-right:20px;height:25px;" runat="server"></asp:FileUpload>
<asp:Button ID="btnUploadSocial" runat="server" Text="导入公休信息" class="btn" style="width:100px;margin-right:20px;height:25px;" OnClientClick="return CheckResult();" onclick="btnUploadSocial_Click" /><br />
</div>
</div>
</form>
</body>
</html>
using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.IO;
using System.Text;
using Common;
using BLL.Hr;
using ChengJian.Com.UI;
public partial class hr_Sabbatical_Sabbaticalimport : System.Web.UI.Page
{
private const string UPLOADPATH = "uploadpath";
private BaseFunction bFunction = new BaseFunction();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUploadSocial_Click(object sender, EventArgs e)
{
string msg = "导入公休信息结果<br /><br />";
if (fileSocial.HasFile)
{
try
{
StringBuilder message = new StringBuilder();
DataTable excelData = getFileDataTable(sender, message);
insertSocial(excelData, message);
if (string.IsNullOrEmpty(message.ToString())) msg += "导入成功！";
else msg += message.ToString();
lblMessage.Text = msg;
}
catch (Exception ex)
{
lblMessage.Text = msg + ex.Message;
}
}
else
{
lblMessage.Text = msg + "请选择文件！";
}
}
private DataTable getFileDataTable(object sender, StringBuilder message)
{
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
string path = ConfigHelper.GetConfigStr(UPLOADPATH);
path += "drawWorkloadExcel";
DataTable excelData = null;
try
{
string query = null;
string connString = "";
string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
string strFileType = string.Empty;
Button button = (Button)sender;
strFileType = System.IO.Path.GetExtension(fileSocial.FileName).ToString().ToLower();
if (strFileType == ".xls" || strFileType == ".xlsx")
{
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path += "/" + strFileName + strFileType;
fileSocial.SaveAs(path);
}
else
{
message.Append("只允许上传Excel文件类型！");
return null;
}
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed) conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();
query = "select * from [" + tableName + "]";
cmd = new OleDbCommand(query, conn);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
excelData = ds.Tables[0];
}
catch (Exception ex)
{
excelData = null;
message.Append(ex.Message);
}
finally
{
da.Dispose();
conn.Close();
conn.Dispose();
if (File.Exists(path))
{
File.Delete(path);
}
}
return excelData;
}
private Hashtable getMemberHt()
{
Hashtable ht = new Hashtable();
string sql = "select SysId, MemberMsgCode from sz_hr_Members where IsDel = 0 and membertype = '0';";
DataTable data = bFunction.GetDataTable(sql);
if (data != null)
{
for (int i = 0; i < data.Rows.Count; i++)
{
if (data.Rows[i]["MemberMsgCode"].ToString()==""||data.Rows[i]["MemberMsgCode"].ToString()==null)
{
ht.Add("0000" + i + "", data.Rows[i]["SysId"].ToString());
}
else
{
ht.Add(data.Rows[i]["MemberMsgCode"].ToString(), data.Rows[i]["SysId"].ToString());
}
}
}
return ht;
}
private void insertSocial(DataTable data, StringBuilder message)
{
sz_StaffSabbaticalBLL dal=new sz_StaffSabbaticalBLL();
string Createtime=DateTime.Now.ToString("yyyy-MM-dd");
if (data != null)
{
Hashtable ht = getMemberHt();
for (int i = 0; i < data.Rows.Count; i++)
{
string SysId = UUIDGenearte.getUUID();
string MemberMsgCode = data.Rows[i][1].ToString();
string MemberName = data.Rows[i][0].ToString();
if (string.IsNullOrEmpty(MemberMsgCode))
{
message.Append("Excel第" + (i + 2) + "行人员:" + MemberName + ",编号：" + MemberMsgCode + "在人员基本信息中不存在，导入失败！<br />");
continue;
}
string MemberSysId = ht[MemberMsgCode] != null ? ht[MemberMsgCode].ToString() : string.Empty;
if (string.IsNullOrEmpty(MemberSysId))
{
message.Append("Excel第" + (i + 2) + "行人员:" + MemberName + ",编号：" + MemberMsgCode + "在人员基本信息中不存在，导入失败！<br />");
continue;
}
string Staffname = data.Rows[i][0].ToString().Trim();
string StaffSysId = data.Rows[i][1].ToString().Trim();
string RestCount = data.Rows[i][2].ToString().Trim();
string UsedCount = data.Rows[i][3].ToString().Trim();
try
{
bool result =dal.AddStaffSabbatical(SysId,Staffname,StaffSysId,RestCount,UsedCount,Createtime);
if (result) { }
else
{
message.Append("Excel第" + (i + 2) + "行导入失败, 填写信息有误, 请核实！<br />");
}
}
catch (Exception ex)
{
message.Append("异常信息：" + ex.Message + "<br />");
}
}
}
}
}
浙公网安备 33010602011771号