private static DataTable DisposeDataTable(DataTable dt)
{
dt.Columns.Add("SplitTimeAndArea");//单节课信息
dt.Columns["SplitTimeAndArea"].SetOrdinal(3);
DataTable TabCourseSimple = dt.Clone();//中转作用，保存拆分
TabCourseSimple.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
SplitToDataTable.SplitTimeAndArea(dt.Rows[i], 4, TabCourseSimple, 3, new char[] { '@' });
}
//表列结构[TeacherDepartment],[TeacherID],[TeacherName],[SplitTimeAndArea],[TimeAndArea]
TabCourseSimple.Columns.Add("CourseAllWeek"); //所有周
TabCourseSimple.Columns.Add("CourseWeek"); //星期数
TabCourseSimple.Columns.Add("CourseTime"); //节数
TabCourseSimple.Columns.Add("CourseAddress"); //教室地址
TabCourseSimple.Columns["CourseAllWeek"].SetOrdinal(3);
TabCourseSimple.Columns["CourseWeek"].SetOrdinal(4);
TabCourseSimple.Columns["CourseTime"].SetOrdinal(5);
TabCourseSimple.Columns["CourseAddress"].SetOrdinal(6);
for (int i = 0; i < TabCourseSimple.Rows.Count; i++)
{
SplitToDataTable.SplitTimeAndArea2(TabCourseSimple.Rows[i]);
}
TabCourseSimple.Columns.RemoveAt(7);
//ConnHelper.DataTableToSQLServer("TabTeacherAllCourse", TabCourseSimple);
//表列结构[TeacherDepartment],[TeacherID],[TeacherName],CourseAllWeek,CourseWeek,CourseTime,CourseAddress,[TimeAndArea]
return(TabCourseSimple);
}
/// <summary>
/// 读取课程Excel并保存到SQL
/// </summary>
/// <param name="fileName"></param>
/// <param name="identity"></param>
/// <returns></returns>
public static string ReadCoursesExcel(string fileName, string identity)
{
List <string> SheetName = new List <string>();
SheetName = GetSheetName(fileName);
string strSQL = "";
if (SheetName[0] != identity + "$")
{
return("指定的Excel文件的工作表名不为" + identity + ",当前的表名为" + SheetName[0]);
}
strSQL = "select * from [" + SheetName[0] + "]";
ReadExcelToDataSet(fileName, strSQL);
if (CheckExcelTableCourses())
{
//CoursesTOSQLServer(identity);
//在原表的基础上添加两列数据并指定添加的位置，使得与数据库目标表结构相同
DataTable dt = ds.Tables["ExcelInfo"];
dt.Columns.Add("TeacherID");
dt.Columns["TeacherID"].SetOrdinal(1);
dt.Columns.Add("TeacherName");
dt.Columns["TeacherName"].SetOrdinal(2);
dt.Columns.Add("CourseID");
dt.Columns.Add("CourseName");
dt.Columns["CourseID"].SetOrdinal(5);
dt.Columns["CourseName"].SetOrdinal(6);
for (int i = 0; i < dt.Rows.Count; i++)
{
SplitToDataTable.SplitTeacherIDAndTeacherName(dt.Rows[i]);
}
//移除已经拆分完毕的列--任课教师
dt.Columns.Remove(dt.Columns[3]);
dt.Columns.Remove(dt.Columns[6]);
return(ExcelToSQLServer.DataTableToSQLServer(DisposeDataTable(dt), "TabTeacherAllCourse", 21));
}
else
{
return("选择的Excel文件中的内容与数据库要求不匹配。请确认！");
}
}
/// <summary>
/// 数据表拆分主方法
/// </summary>
/// <returns></returns>
public static string TabTeacherAllCourse()
{
//查找不重复的课程信息（课程信息 伪主键）sql语句去重查找↓
string strSql = "select distinct [TimeAndArea],[TeacherDepartment],[TeacherID],[TeacherName],[CourseAllWeek],[CourseWeek],[CourseTime],[CourseAddress],[TimeAndArea],[CourseID],[CourseName] ,[t1] ,[t2] ,[t3] ,[Class] from [TabTeacherAllCourse]";
DataTable dt = ConnHelper.GetDistinceColoum(strSql);
dt.Columns.RemoveAt(0);
//表列结构[TeacherDepartment],[TeacherID],[TeacherName],CourseAllWeek,CourseWeek,CourseTime,CourseAddress,[TimeAndArea]
DataTable TabCourseSimple = dt.Clone();//中转作用，保存拆分
TabCourseSimple.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
SplitToDataTable.SplitTimeAndArea(dt.Rows[i], 3, TabCourseSimple, 3, new char[] { ' ' });
}
return(ConnHelper.DataTableToSQLServer("TabTeacherCourseWeek", TabCourseSimple));
}
