• 导入Excel表的前台页面和后台代码


    <%@ 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="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>
                            &nbsp;
                        </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="200px;margin-right:20px;height:25px;" runat="server"></asp:FileUpload>
                    <asp:Button ID="btnUploadSocial" runat="server" Text="导入公休信息" class="btn" style="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 />");
                    }
                }
            }
        }
    }
  • 相关阅读:
    PL/SQL 自动补全[转]
    关闭开发环境的开发者模式
    删除带外键的表【foreign key constraint fails】报错
    CSSS选择器总结
    XSS漏洞初窥(通过dvwa平台进测试)
    Oracle 闪回 找回数据
    Oracle 函数 Function
    Oracle 存储过程 PROCEDURE
    Oracle 备份与恢复
    Web应用程序架构的比较
  • 原文地址:https://www.cnblogs.com/Zpyboke/p/5242681.html
Copyright © 2020-2023  润新知