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)); }