前臺頁面代碼:
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>
后臺代碼:
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