/// <summary>
/// 读取校历Excel并保存到SQL
/// </summary>
/// <param name="fileName">服务器端Excel文件路径</param>
/// <param name="identity">sql表名</param>
/// <returns></returns>
public static string ReadCalendarExcel(string fileName, string identity)
{
List <string> SheetName = new List <string>();
SheetName = GetSheetName(fileName);
string strSQL = "";
if (SheetName[0] != "Sheet1$")
{
return("指定的Excel文件的工作表不为“Sheet1”,当前的表名为" + SheetName[0]);
}
strSQL = "select * from [Sheet1$]";
ReadExcelToDataSet(fileName, strSQL);
if (CheckExcelTableCalendar())
{
DataTable dt = ds.Tables["ExcelInfo"];
// CalenderToSQLServer(identity);
return(ExcelToSQLServer.DataTableToSQLServer(dt, identity, 3));
}
else
{
return("选择的Excel文件中的内容与数据与数据库中的要求不匹配，请确认！");
}
}
/// <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>
/// 将datat批量导入数据库，中转作用
/// </summary>
/// <param name="dt"></param>
/// <param name="table"></param>
/// <param name="num"></param>
/// <returns></returns>
public static string InsertForSql(DataTable dt, string table, int num)
{
return(ExcelToSQLServer.DataTableToSQLServer(dt, table, num));
}