国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

打開APP
userphoto
未登錄

開通VIP,暢享免費電子書等14項超值服

開通VIP
Excel文件上傳并導(dǎo)入Sqlserver數(shù)據(jù)庫

Excel文件上傳并導(dǎo)入Sqlserver數(shù)據(jù)庫

前臺頁面代碼:

View Code
 1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ImportUsers.aspx.cs" Inherits="SystemMang_UserMang_ImportUsers" %>
2
3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4 <html xmlns="http://www.w3.org/1999/xhtml">
5 <head runat="server">
6 <title>導(dǎo)入用戶</title>
7 <link href="http://www.cnblogs.com/Public/css/yg_style.css" rel="stylesheet" type="text/css" />
8 <link href="http://www.cnblogs.com/Public/css/main.css" rel="stylesheet" type="text/css" />
9 </head>
10 <body>
11 <form id="form1" runat="server">
12 <div id="tanc">
13 <div id="tanc_top">
14 <div id="tanc_top_left">
15 <!--<img src="http://www.cnblogs.com/Public/Images/chuangx_t_57.jpg" width="41" height="25" align="absmiddle" />-->
16 <span>導(dǎo)入用戶</span>
17 </div>
18 <div id="tanc_top_right">
19 </div>
20 </div>
21 <table width="100%" style="height: 200px">
22 <tr>
23 <td colspan="2" nowrap="nowrap" align="center">
24 導(dǎo)入文件:<input id="fileImport" runat="server" type="file" />
25 </td>
26 <td style="width: 20px">
27 </td>
28 </tr>
29 <tr>
30 <td colspan="2" align="center">
31 <asp:Label ID="lblReturn" runat="server"></asp:Label>
32 </td>
33 </tr>
34 <tr>
35 <td>
36 </td>
37 </tr>
38 <tr>
39 <td colspan="4" align="center">
40 <div id="divLog" runat="server" style="width: 450px; height: 150px; overflow: scroll">
41 </div>
42 </td>
43 <td>
44 </td>
45 <td>
46 </td>
47 </tr>
48 </table>
49 <div id="tanc_down">
50 <table>
51 <tr>
52 <td align="center">
53 <asp:Button ID="btnImport" runat="server" CssClass="button_load" Text="導(dǎo)入" OnClick="btnImport_Click" />
54 </td>
55 </tr>
56 </table>
57 </div>
58 </div>
59 </form>
60 </body>
61 </html>

 

 

后臺代碼:

 

View Code

 

ExcelHelper代碼:

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
//using Ifmsoft.Framework.Common;
//using Ifmsoft.Framework.ExceptionHandling;

namespace Ifmsoft.Framework.Data
{
    public enum ExcelEdition
    {
        Excel97,
        Excel2000,
        Excel2003,
        Excel2007
    }

    public class ExcelHelper
    {
        private ExcelHelper() { }

        /// <summary>
        /// 獲得Excel連接串
        /// </summary>
        /// <param name="filePath">文件路徑</param>
        /// <param name="edition">Excel版本號</param>
        /// <returns>Excel連接串</returns>
        private static string CreateConnection(string filePath, ExcelEdition edition)
        {
            switch (edition)
            {
                case ExcelEdition.Excel97:
                    return " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath +
                                ";Extended Properties=Excel 5.0";
                case ExcelEdition.Excel2000:
                    return " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath +
                                ";Extended Properties=Excel 8.0";
                case ExcelEdition.Excel2003:
                    return " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath +
                                ";Extended Properties=Excel 8.0";
                case ExcelEdition.Excel2007:
                    return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
                                ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                default:
                    return " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath +
                                ";Extended Properties=Excel 8.0";
            }
        }

        /// <summary>
        /// 格式化工作表名稱“[sheet1$]”
        /// </summary>
        /// <remarks>
        /// 履  歷:
        ///     NO 日期        版本     姓名            內(nèi)容
        ///     1   2008/10/06  V0L01    IFM)于鵬        初版
        /// </remarks>
        /// <param name="sheetName">工作表名稱</param>
        /// <returns></returns>
        public static string FormatSheetName(string sheetName)
        {
            return "[" + sheetName + "$]";
        }

        /// <summary>
        /// 讀取Excel文件到Dataset
        /// </summary>
        /// <remarks>
        /// 履  歷:
        ///     NO 日期        版本     姓名            內(nèi)容
        ///     1   2008/10/06  V0L01    IFM)于鵬        初版
        /// </remarks>
        /// <param name="filePath">文件路徑</param>
        /// <param name="edition">Excel版本號</param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(string filePath, ExcelEdition edition)
        {
            return ExecuteDataset(filePath, "[sheet1$]", edition);
        }

        /// <summary>
        /// 讀取Excel文件到Dataset
        /// </summary>
        /// <remarks>
        /// 履  歷:
        ///     NO 日期        版本     姓名            內(nèi)容
        ///     1   2008/10/06  V0L01    IFM)于鵬        初版
        /// </remarks>
        /// <param name="filePath">文件路徑</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="edition">Excel版本號</param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(string filePath, string sheetName, ExcelEdition edition)
        {
            if (string.IsNullOrEmpty(filePath) || string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException("參數(shù)不能為空");
            }
            if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");

            string connectionString = CreateConnection(filePath, edition);

            string commandText = " SELECT * FROM " + sheetName;
            DataSet ds = new DataSet();

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();

                using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, connection))
                {
                    da.Fill(ds, sheetName);
                    connection.Close();
                }
            }

            return ds;
        }

        /// <summary>
        /// 讀取Excel文件到DataTable
        /// </summary>
        /// <remarks>
        /// 履  歷:
        ///     NO 日期        版本     姓名            內(nèi)容
        ///     1   2008/10/06  V0L01    IFM)于鵬        初版
        /// </remarks>
        /// <param name="filePath">文件路徑</param>
        /// <param name="edition">Excel版本號</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string filePath, ExcelEdition edition)
        {
            return ExecuteDataTable(filePath, "[sheet1$]", edition);
        }

        /// <summary>
        /// 讀取Excel文件到DataTable
        /// </summary>       
        /// <remarks>
        /// 履  歷:
        ///     NO 日期        版本     姓名            內(nèi)容
        ///     1   2008/10/06  V0L01    IFM)于鵬        初版
        /// </remarks>
        /// <param name="filePath">文件路徑</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="edition">Excel版本號</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string filePath, string sheetName, ExcelEdition edition)
        {
            //ValidationManager.ArgumentNotNullOrEmpty(filePath, "filePath");
            //ValidationManager.ArgumentNotNullOrEmpty(sheetName, "sheetName");
            if (string.IsNullOrEmpty(filePath) || string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException("參數(shù)不能為空");
            }
            if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");

            string connectionString = CreateConnection(filePath, edition);

            string commandText = " SELECT * FROM " + sheetName;
            DataTable dt = new DataTable();

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();

                using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, connection))
                {
                    da.Fill(dt);
                    connection.Close();
                }
            }
            return dt;
        }

        /// <summary>
        /// 寫入Excel文檔(需有符合格式的文件模板)
        /// </summary>
        /// <remarks>
        /// 履  歷:
        ///     NO 日期        版本     姓名            內(nèi)容
        ///     1   2008/10/06  V0L01    IFM)于鵬        初版
        /// </remarks>
        /// <param name="filePath">文件名稱</param>
        /// <param name="dt">數(shù)據(jù)源</param>
        /// <param name="edition">Excel版本信息</param>
        public static string DataTableToExcel(string filePath, string outputDirectory, DataTable dt, ExcelEdition edition)
        {
            return DataTableToExcel(filePath, outputDirectory, "[sheet1$]", GetColumnNames(dt), dt, edition);
        }

        /// <summary>
        /// 寫入Excel文檔(需有符合格式的文件模板)
        /// </summary>
        /// <remarks>
        /// 履  歷:
        ///     NO 日期        版本     姓名            內(nèi)容
        ///     1   2008/10/06  V0L01    IFM)于鵬        初版
        /// </remarks>
        /// <param name="filePath">文件名稱</param>
        /// <param name="sheetName">工作表名稱</param>
        /// <param name="dt">數(shù)據(jù)源</param>
        /// <param name="edition">Excel版本信息</param>
        public static string DataTableToExcel(string filePath, string outputDirectory, string sheetName,
                                            DataTable dt, ExcelEdition edition)
        {
            return DataTableToExcel(filePath, outputDirectory, sheetName, GetColumnNames(dt), dt, edition);
        }

        /// <summary>
        /// 寫入Excel文檔(需有符合格式的文件模板)
        /// </summary>
        /// <remarks>
        /// 履  歷:
        ///     NO 日期        版本     姓名            內(nèi)容
        ///     1   2008/10/06  V0L01    IFM)于鵬        初版
        /// </remarks>
        /// <param name="filePath">文件名稱</param>
        /// <param name="sheetName">工作表名稱</param>
        /// <param name="columnNames">列名</param>
        /// <param name="dt">數(shù)據(jù)源</param>
        /// <param name="edition">Excel版本信息</param> 
        public static string DataTableToExcel(string filePath, string outputDirectory, string sheetName,
                                            string columnNames, DataTable dt, ExcelEdition edition)
        {
            //參數(shù)檢查
            if (string.IsNullOrEmpty(filePath) || string.IsNullOrEmpty(sheetName) || string.IsNullOrEmpty(outputDirectory))
            {
                throw new ArgumentNullException("參數(shù)不能為空");
            }
            if (!Directory.Exists(outputDirectory)) throw new DirectoryNotFoundException("目錄不存在");
            //if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");
            if (File.Exists(filePath))
            {
                FileInfo fileInfo = new FileInfo(filePath);
                if (fileInfo.Attributes.ToString().IndexOf("ReadOnly") != -1)
                {
                    try
                    {
                        fileInfo.Attributes = FileAttributes.Normal;
                    }
                    catch (Exception)
                    {
                        throw new ArgumentException("改變文件只讀屬性時出錯,請手動去掉模板文件的只讀屬性");
                    }

                }
            }
            else
            {
                throw new FileNotFoundException("文件不存在");
            }
            if (null == dt) return null;

            //根據(jù)模板生成文件
            string destFileName = CopyFromTemplate(filePath, outputDirectory);

            //根據(jù)Excel版本生成連接串
            string connectionString = CreateConnection(destFileName, edition);

            OleDbTransaction transaction = null;
            OleDbCommand cmd = null;
            OleDbConnection connection = null;
            try
            {
                connection = new OleDbConnection(connectionString);

                //創(chuàng)建工作區(qū)格式
                CreateSheet(connection, sheetName, columnNames);

                if (connection.State != ConnectionState.Open) connection.Open();
                transaction = connection.BeginTransaction();        //開啟事務(wù)

                cmd = new OleDbCommand();
                cmd.Connection = connection;
                cmd.Transaction = transaction;

                foreach (DataRow row in dt.Rows)
                {
                    //插入記錄
                    cmd.CommandText = GetInsertRecordString(dt, row, sheetName);
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();                               //提交事務(wù)
            }
            catch (Exception ex)
            {
                transaction.Rollback();                             //回滾事務(wù)
                connection.Close();

                if (File.Exists(destFileName))
                    File.Delete(destFileName);                      //刪除Excel文件

                //包裝異常再次拋出
                throw new ApplicationException("寫入Excel失敗");
                //throw ExceptionManager.WrapExcepion(ex, "寫入Excel失敗," + ex.Message, typeof(ExcelAccessException));
            }
            finally
            {
                cmd.Dispose();
                transaction.Dispose();
                connection.Dispose();
            }
            return destFileName;
        }

        ///// <summary>
        ///// 寫入Excel文檔(需有符合格式的文件模板)
        ///// </summary>
        ///// <remarks>
        ///// 履  歷:
        /////     NO 日期        版本     姓名            內(nèi)容
        /////     1   2008/10/06  V0L01    IFM)于鵬        初版
        ///// </remarks>
        ///// <param name="filePath">文件名稱</param>
        ///// <param name="sheetName">工作表名稱</param>
        ///// <param name="columnNames">列名</param>
        ///// <param name="dt">數(shù)據(jù)源</param>
        ///// <param name="edition">Excel版本信息</param> 
        //public static void DataTableToExcel(string filePath, string outputDirectory, string sheetName,
        //                                    string columnNames, DataTable dt, ExcelEdition edition)
        //{
        //    //參數(shù)檢查
        //    //ValidationManager.ArgumentNotNullOrEmpty(filePath, "filePath");
        //    //ValidationManager.ArgumentNotNullOrEmpty(outputDirectory, "outputDirectory");
        //    //ValidationManager.ArgumentNotNullOrEmpty(sheetName, "sheetName");
        //    if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");
        //    if (!Directory.Exists(outputDirectory)) throw new DirectoryNotFoundException("目錄不存在");
        //    if (null == dt) return;

        //    //根據(jù)模板生成文件
        //    string destFileName = CopyFromTemplate(filePath, outputDirectory);

        //    //根據(jù)Excel版本生成連接串
        //    string connectionString = CreateConnection(destFileName, edition);

        //    OleDbTransaction transaction = null;
        //    OleDbCommand cmd = null;
        //    OleDbConnection connection = null;
        //    try
        //    {
        //        connection = new OleDbConnection(connectionString);

        //        //創(chuàng)建工作區(qū)格式
        //        CreateSheet(connection, sheetName, columnNames);

        //        if (connection.State != ConnectionState.Open) connection.Open();
        //        transaction = connection.BeginTransaction();        //開啟事務(wù)

        //        cmd = new OleDbCommand();
        //        cmd.Connection = connection;
        //        cmd.Transaction = transaction;

        //        foreach (DataRow row in dt.Rows)
        //        {
        //            //插入記錄
        //            cmd.CommandText = GetInsertRecordString(dt, row, sheetName);
        //            cmd.ExecuteNonQuery();
        //        }
        //        transaction.Commit();                               //提交事務(wù)
        //    }
        //    catch (Exception ex)
        //    {
        //        transaction.Rollback();                             //回滾事務(wù)
        //        connection.Close();

        //        if (File.Exists(destFileName))
        //            File.Delete(destFileName);                      //刪除Excel文件

        //        //包裝異常再次拋出
        //        //throw ExceptionManager.WrapExcepion(ex, "寫入Excel失敗," + ex.Message, typeof(ExcelAccessException));
        //    }
        //    finally
        //    {
        //        cmd.Dispose();
        //        transaction.Dispose();
        //        connection.Dispose();
        //    }
        //}

        /// <summary>
        /// 獲取 Excel 連接對象
        /// (用于讀取 Excel 2000 及更高版本文件)。
        /// </summary>
        /// <param name="fullPath">文件的完全路徑。</param>
        /// <param name="firstRowAsHeader">是否將第一行作為表頭處理。</param>
        /// <param name="modeValue">Excel 導(dǎo)入混合類型,通常使用 ImportMixedType.Text。</param>
        /// <returns>Excel 連接對象。</returns>
        public static OleDbConnection GetExcelConnection(string fullPath, bool firstRowAsHeader, ImportMixedType modeValue)
        {
            return GetExcelConnection(fullPath, firstRowAsHeader, modeValue, ExcelVersion.Excel8);
        }

        /// <summary>
        /// 獲取 Excel 連接對象。
        /// </summary>
        /// <param name="fullPath">文件的完全路徑。</param>
        /// <param name="firstRowAsHeader">是否將第一行作為表頭處理。</param>
        /// <param name="modeValue">Excel 導(dǎo)入混合類型,通常使用 ImportMixedType.Text。</param>
        /// <param name="excelVersionNumber">Excel 文件版本號。</param>
        /// <returns>Excel 連接對象。</returns>
        public static OleDbConnection GetExcelConnection(string fullPath, bool firstRowAsHeader, ImportMixedType modeValue, ExcelVersion excelVersionNumber)
        {
            if (fullPath == null)
                throw new ArgumentNullException("fullPath", "參數(shù)值不能為空引用。");

            if (fullPath.Trim() == string.Empty)
                throw new ArgumentException("參數(shù)值不能為空字符串。", "fullPath");

            if (!File.Exists(fullPath))
                throw new FileNotFoundException(string.Format("訪問給定路徑下的文件失敗。文件路徑:{0}", fullPath));

            string hdrString = "No";

            if (firstRowAsHeader)
            {
                hdrString = "Yes";
            }

            string excelVersionString = string.Empty;
            switch (excelVersionNumber)
            {
                case ExcelVersion.Excel5:
                    excelVersionString = "Excel 5.0";
                    break;

                case ExcelVersion.Excel8:
                    excelVersionString = "Excel 8.0";
                    break;
            }

            // 構(gòu)造連接字符串
            string connectionString = string.Format(@"
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};
Extended Properties=""{1};HDR={2};IMEX={3};""
", fullPath, excelVersionString, hdrString, modeValue);

            return new OleDbConnection(connectionString);

            #region Excel 連接串詳解
            //
            // Connection String -- 連接字符串
            //
            // # 在書寫連接字符串須十分仔細,因為如果連接串中存在錯誤,
            //   Jet Engine 不會給出恰當(dāng)?shù)腻e誤細節(jié)。
            //
            // # Syntax 語法如下:
            // Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Full Path of Excel File>; Extended Properties="Excel 8.0; HDR=No; IMEX=1"
            //
            //
            // Extended Properties -- 擴展屬性
            //
            // # Excel <Excel 8.0/Excel 5.0>
            //     指定 Excel Sheet 的版本。
            //     對于 Excel 2000 及更高版本,應(yīng)選用 Excel 8.0;
            //     對于其他版本,應(yīng)選用 Excel 5.0。
            //
            // # HDR= <Yes/No>
            //     該屬性用于為各列指定列頭。
            //     若該值為 Yes,則首行被作為列頭處理;
            //     若該值為 No,則由系統(tǒng)自動產(chǎn)生列頭,形如:F1, F2 等。
            //
            // # IMEX= <0/1/2>
            //     IMEX 指 IMport EXport mode。該屬性具有三種可能的值。
            //
            //         * IMEX=0 和 IMEX=2 將導(dǎo)致 ImportMixedTypes 的值被忽略而使用 'Majority Types’ 的缺省值。
            //                            這種情況下,將獲取前 8 行數(shù)據(jù)及其各列的數(shù)據(jù)類型。
            //
            //         * IMEX=1 是將 ImportMixedTypes 值設(shè)置為 Text 類型的唯一方式。
            //                  這樣,每樣?xùn)|東都被作為文本處理。
            //
            //
            // 以上信息參考:
            // http://www.codeproject.com/useritems/Excel_Connectivity.asp
            //
            #endregion Excel 連接串詳解
        }

        /// <summary>
        /// 讀取給定連接給定表的內(nèi)容至 DataTable。
        /// </summary>
        /// <param name="cn">給定連接</param>
        /// <param name="sheetName">給定 WorkSheet 的名稱</param>
        /// <returns>包含給定 Sheet 數(shù)據(jù)的 DataTable</returns>
        public static System.Data.DataTable ExecuteDataTable(OleDbConnection cn, string sheetName)
        {
            System.Data.DataTable dt = null;

            if (sheetName == null)
                throw new ArgumentNullException("sheetName", "參數(shù)值不能為空引用。");

            if (sheetName.Trim() == string.Empty)
                throw new ArgumentException("參數(shù)值不能為空字符串。", "sheetName");

            string queryString = string.Format("SELECT * FROM [{0}$]", sheetName);

            dt = new System.Data.DataTable(sheetName);

            OleDbDataAdapter da = new OleDbDataAdapter(queryString, cn);

            da.Fill(dt);

            return dt;
        }

        #region 私有方法
        /// <summary>
        /// 根據(jù)模板創(chuàng)建副本
        /// </summary>
        /// <param name="filePath">原始文件路徑</param>
        /// <param name="outputDirectory">輸入路徑</param>
        /// <returns>新文件路徑</returns>
        private static string CopyFromTemplate(string filePath, string outputDirectory)
        {
            FileInfo file = new FileInfo(filePath);
            string destFileName = Path.Combine(outputDirectory, file.Name.Replace(file.Extension, string.Empty) +
                                                "-" + DateTime.Now.ToString("yyyy-MM-dd") + "(" + Guid.NewGuid() + ")" + file.Extension);
            File.Copy(filePath, destFileName);

            return destFileName;
        }

        /// <summary>
        /// 創(chuàng)建工作區(qū)
        /// </summary>
        /// <param name="connection">OleDB連接</param>
        /// <param name="sheetName">工作區(qū)名稱</param>
        /// <param name="columnNames">列名</param>
        private static void CreateSheet(OleDbConnection connection, string sheetName, string columnNames)
        {
            string createTableScript = GetCreateTableString(sheetName, columnNames.Split(','));

            connection.Open();

            using (OleDbCommand cmd = new OleDbCommand(createTableScript, connection))
            {
                cmd.ExecuteNonQuery();
            }

            connection.Close();
        }

        /// <summary>
        /// 獲得創(chuàng)建工作區(qū)格式腳本
        /// </summary>
        /// <param name="sheetName">工作區(qū)名稱</param>
        /// <param name="names">列名</param>
        /// <returns>創(chuàng)建腳本</returns>
        private static string GetCreateTableString(string sheetName, string[] names)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(string.Format("CREATE TABLE {0} (", sheetName));
            for (int i = 0; i < names.Length; i++)
            {
                if (i == names.Length - 1)
                    sb.Append(string.Format("{0} VARCHAR(100))", names[i]));
                else
                    sb.Append(string.Format("{0} VARCHAR(100),", names[i]));
            }

            return sb.ToString();
        }

        /// <summary>
        /// 獲得插入記錄腳本
        /// </summary>
        /// <param name="dt">數(shù)據(jù)源</param>
        /// <param name="row">當(dāng)前數(shù)據(jù)行</param>
        /// <returns>插入記錄腳本</returns>
        private static string GetInsertRecordString(DataTable dt, DataRow row, string sheetName)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(string.Format("INSERT INTO {0} VALUES(", sheetName));
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (i == dt.Columns.Count - 1)
                    sb.Append(string.Format("'{0}')", row[i].ToString().Replace("'", "''")));
                else
                    sb.Append(string.Format("'{0}',", row[i].ToString().Replace("'", "''")));
            }

            return sb.ToString();
        }

        /// <summary>
        /// 獲得列名
        /// </summary>
        /// <param name="dt">數(shù)據(jù)源</param>
        /// <returns>全部列名</returns>
        private static string GetColumnNames(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            foreach (DataColumn column in dt.Columns)
            {
                sb.Append(string.Format("[{0}],", column.ColumnName));
            }
            string columnNames = sb.ToString().TrimEnd(',');
            return columnNames;
        }
        #endregion

        #region 枚舉

        /// <summary>
        /// Excel 導(dǎo)入混合類型。
        /// </summary>
        public enum ImportMixedType
        {
            /// <summary>
            /// 多值(數(shù))類型 0,慎用此值!
            /// </summary>
            Majority0 = 0,
            /// <summary>
            /// 文本類型,通常使用該值。
            /// </summary>
            Text = 1,
            /// <summary>
            /// 多值(數(shù))類型 2,慎用此值!
            /// </summary>
            Majority2 = 2
        }

        /// <summary>
        /// Excel 文件版本。
        /// 讀取 Excel 2000 及更高版本文件時應(yīng)選用 Excel8;
        /// 讀取其他版本文件時均選用 Excel5。
        /// </summary>
        public enum ExcelVersion
        {
            /// <summary>
            /// 讀取低于 Excel 2000 的版本文件時應(yīng)選用此值。
            /// </summary>
            Excel5 = 0,
            /// <summary>
            /// 讀取 Excel 2000 及更高版本文件時應(yīng)選用此值。
            /// </summary>
            Excel8 = 1
        }

        #endregion 枚舉
    }
}

BatchInsertUserInfo方法代碼:

#region 批量導(dǎo)入用戶信息
        /// <summary>
        /// 批量導(dǎo)入用戶信息
        /// </summary>
        /// <param name="dt">導(dǎo)入用戶數(shù)據(jù)集</param>
        /// <returns>沒有成功導(dǎo)入用戶AccountId列表</returns>
        public string BatchInsertUserInfo(DataTable dt,string downLoad,ref string fileName)
        {
            StringBuilder unsuccessUserList = new StringBuilder();
            DataSet ds = new DataSet();
            //導(dǎo)入的總行數(shù)
            int allRows = dt.Rows.Count;
            //導(dǎo)入失敗行數(shù)
            int falRows = 0;
            for (int i = 0; i < allRows; i++)
            {
               
                //// 檢查班級和小組是否為空 為空返回true
                //bool boolval = false ;
                ////判斷模板中每列的字符串為空 如果為空 不去導(dǎo)入這條用戶信息
                //for (int col = 0; col < dt.Columns.Count; col++)
                //{
                //    if (dt.Rows[i][col].ToString().Trim() == string.Empty)
                //    {
                //        boolval = true;
                //        break;
                //    }
                //}
                //if (boolval)
                //{
                //    falRows++;
                //    //輸入數(shù)據(jù)為空 不插入這行
                //    // 把導(dǎo)入失敗的用戶AccountID加入到返回結(jié)果集中

                //    unsuccessUserList.Append("<tr><td>" + dt.Rows[i]["UserName"].ToString() + " コンテンツに空値を入力されました " + "</td></tr>");
                  

                //    // 跳出本次循環(huán)
                //    continue;
                //}
                // 檢查用戶名是否已經(jīng)存在
                bool returnVal = CheckUserNameExist(dt.Rows[i]["UserName"].ToString().Trim());
                if (!returnVal)
                {
                    falRows++;
                    //輸入數(shù)據(jù)為空 不插入這行
                    // 把導(dǎo)入失敗的用戶AccountID加入到返回結(jié)果集中

                    unsuccessUserList.Append("<tr><td>" + dt.Rows[i]["UserName"].ToString() + " 已存在" + "</td></tr>");
                   

                    // 跳出本次循環(huán)
                    continue;
                }
                //// 檢查用戶部門是否存在
               
                //以逗號拆分班和組字串
                //string[] arrClassTeam = dt.Rows[i]["ClassNameTeamName"].ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                //bool boolct = false;
                //boolct = CheckUserClassTeamRepeat(arrClassTeam);
                //if (!boolct)
                //{

                //    //定義兩個數(shù)組 一個裝班級名稱   一個裝小組名稱
                //    string[] arrClass = new string[arrClassTeam.Length];
                //    string[] arrTeam = new string[arrClassTeam.Length];
                //    SplitStringToArray(arrClass, arrTeam, arrClassTeam);
                //    //去庫中檢驗是否存在班級名和小組名稱
                //    ds = GetClassIDAndTeamID(arrClass, arrTeam);
                //    //如果班級名稱、小組名稱 與 查詢出來的班ID 和組ID長度一致 說明班名稱和組名稱沒錯
                //    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                //    {
                //        if (ds.Tables[0].Rows.Count == arrClass.Length)
                //        {
                //            boolct = false;
                //        }
                //        else
                //        {
                //            boolct = true;
                //        }
                //    }
                //    else
                //    {
                //        boolct = true;
                //    }
                //}
                //判斷用戶是否重復(fù)  模板中的每一項是否為空  模板中班級和小組是否正確
                //if(boolct)
                //{
                //    falRows++;
                //    // 把導(dǎo)入失敗的用戶AccountID加入到返回結(jié)果集中

                //    unsuccessUserList.Append("<tr><td>" + dt.Rows[i]["UserName"].ToString() + " ご入力されたクラスまたはグループ情報が不正です" + "</td></tr>");
                   

                //    // 跳出本次循環(huán)
                //    continue;
                //}
                //**********************
                //注釋掉討論區(qū)同步
                //**********************
                //try
                //{
                // 獲得用戶數(shù)據(jù)實體
                UserMangEntity entity = new UserMangEntity();
                #region  用戶基本信息
                entity.UserName = dt.Rows[i]["UserName"].ToString();
                //討論區(qū)中密碼明文
                string passWord = dt.Rows[i]["Password"].ToString();
                ///密碼加密出理
                SHA1Managed shaPassWord = new SHA1Managed();
                byte[] hashPassWord = shaPassWord.ComputeHash(Encoding.UTF8.GetBytes(dt.Rows[i]["Password"].ToString()));
               
                entity.Password = Convert.ToBase64String(hashPassWord);
                entity.UserStatus = 1;  //啟用
                entity.UserType = int.Parse(dt.Rows[i]["UserTypeID"].ToString());
                entity.OUKeyID = dt.Rows[i]["OUKeyID"].ToString().Trim();
                DataSet getOUds = GetOUNameByCode(entity);//根據(jù)導(dǎo)入的OUKeyID獲得組織名稱
                entity.ProvinceID = "500000";
                entity.CityID = "500000";
                entity.County = "500000";
                entity.UserActualName = dt.Rows[i]["Name"].ToString();
                entity.EMail = dt.Rows[i]["Email"].ToString();
               
                #endregion
                #region 后臺用戶基本信息,保持同步 add  姜浩 2011/11/11
                entity.OPAdminUserName=dt.Rows[i]["UserName"].ToString();
                entity.OPAdminUserPassword=Convert.ToBase64String(hashPassWord);
                entity.OPActualName = dt.Rows[i]["Name"].ToString();
                entity.OPStatus = 1;
                entity.OPRemark = "default";
                entity.OPAdminType = 0;
              
               
                #endregion
                //    //同步討論區(qū)
                //    int WSUserType = entity.UserType > 0 ? 3 : 0;  //學(xué)生是0 教師是1      
                //    ForumWSDL ncservice = new ForumWSDL();
                //    ncservice.Url = System.Configuration.ConfigurationManager.AppSettings["ForumService.ForumWSDL"];
                bool flag = true;
                //    try
                //    {
                //        flag = ncservice.RegisterUsers(entity.UserName, passWord, entity.EMail, WSUserType);
                //    }
                //    catch(Exception ex)
                //    {
                //        //unsuccessUserList = ex.Message;
                //        //同步失敗
                //    }
                //  
                if (!flag)
                {
                    //失敗
                    unsuccessUserList.Append("<tr><td>" + dt.Rows[i]["UserName"].ToString() + " 與網(wǎng)絡(luò)上公告欄的數(shù)據(jù)相同,請稍后再試。" + "</td></tr>");
                    continue;
                }
                // 檢查所屬部門是否存在或為空,默認導(dǎo)入根級別
                bool returnOUVal = CheckOUNameExist(entity.OUKeyID);
                if (!returnOUVal)
                {

                    entity.OUID = GlobalConst.RootOUID;
                    DataSet OUds = GetOUName(entity);
                    entity.OUName = OUds.Tables[0].Rows[0][0].ToString();
                    entity.OUIsPrimary = "0";
                    int result = 0;
                    result = InsertStudentUserInfo(entity, ds);
                   
                    //輸入數(shù)據(jù)為空 不插入這行
                    // 把導(dǎo)入的用戶AccountID加入到返回結(jié)果集中

                    unsuccessUserList.Append("<tr><td>" + dt.Rows[i]["UserName"].ToString() + " 用戶已經(jīng)導(dǎo)入,但所屬部門不存在,其所屬機構(gòu)掛在"+ OUds.Tables[0].Rows[0][0].ToString()  + "</td></tr>");
                    continue;
                }
                //添加用戶信息
                else
                {
                    entity.OUName = getOUds.Tables[0].Rows[0][0].ToString();
                    entity.OPDepartment = getOUds.Tables[0].Rows[0][0].ToString();
                    DataSet OUds = GetOUID(entity);
                    entity.OUID = OUds.Tables[0].Rows[0][0].ToString();
                    entity.OUIsPrimary = "0";
                    int result = 0;
                    result = InsertStudentUserInfo(entity, ds);
                    //if (result < 1)
                    //{
                    //    //刪除討論區(qū)用戶
                    //    ncservice.DeleteUser(entity.UserName);
                    //}
                }
                //}
                //catch (Exception ex)
                //{
                //    WriteError(ex.Message,downLoad);
                //    // 把導(dǎo)入失敗的用戶AccountID加入到返回結(jié)果集中

                //    unsuccessUserList.Append("<tr><td>" + dt.Rows[i]["UserName"].ToString() + " インポート失敗 " + "</td></tr>");
                  

                //    // 跳出本次循環(huán)
                //    continue;
                //}
            }
            WriteError(unsuccessUserList.ToString(),downLoad);
            fileName = unsuccessUserList.ToString();
            return "導(dǎo)入用戶 " + allRows.ToString() + "人、成功 " + (allRows - falRows).ToString() + " 人、失敗 " + falRows.ToString() + "人";
        }
        #endregion

本站僅提供存儲服務(wù),所有內(nèi)容均由用戶發(fā)布,如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,請點擊舉報。
打開APP,閱讀全文并永久保存 查看更多類似文章
猜你喜歡
類似文章
2012,我的C#全能Excel操作(無需Office,不使用XML)
asp.net 將DataTable中的數(shù)據(jù)導(dǎo)出到Excel并下載方法
C# 數(shù)據(jù)導(dǎo)出到Excel
導(dǎo)出Excel
c# 讀取excel文件的三種方法
C# EXCEL 導(dǎo)入導(dǎo)出類(OLEDB的方式)的示例代碼詳情
更多類似文章 >>
生活服務(wù)
分享 收藏 導(dǎo)長圖 關(guān)注 下載文章
綁定賬號成功
后續(xù)可登錄賬號暢享VIP特權(quán)!
如果VIP功能使用有故障,
可點擊這里聯(lián)系客服!

聯(lián)系客服