protected void bExportExcel_Click(object sender, EventArgs e) { if (ds == null || ds.Tables.Count < 1) { ErrorMsg.Text = "没有数据可供导出"; return; } string filepath = Server.MapPath(ddlYearNum.SelectedValue.ToString() + "ReaportDropOut.xls"); try { ExcelItem excel = new ExcelItem(filepath); ExcelUtility.ExportToExcel(ds, excel); DownloadFile(filepath); } catch (Exception ex) { ErrorMsg.Text = "错误:" + ex.Message; } finally { if (File.Exists(filepath)) { File.Delete(filepath); } } }
protected void bExport_Click(object sender, EventArgs e) { if (dtable == null || dtable.Rows.Count < 1) { Chart1.Visible = false; ErrorMsg.Text = "没有统计结果,无法导出"; return; } ErrorMsg.Text = ""; DataSet ds = new DataSet(); ds.Tables.Add(dtable); string filepath = Server.MapPath(ddlYearNum.SelectedValue.ToString() + "StatisticsContactor.xls"); try { ExcelItem excel = new ExcelItem(filepath); ExcelUtility.ExportToExcel(ds, excel); DownloadFile(filepath); } catch (Exception ex) { ErrorMsg.Text = "错误:" + ex.Message; } finally { if (File.Exists(filepath)) { File.Delete(filepath); } ds.Tables.Clear(); } }
protected void bImport_Click(object sender, EventArgs e) { bool isFileOk = false; string path = ""; if (fulExcel.HasFile) { try { string fn = fulExcel.FileName; string ftype = fn.Substring(fn.Length - 4, 4); if (ftype.ToLower() != ".xls") { ErrorMsg.Text = "文件格式错误,只能导入2003格式的excel"; Response.Write(ftype); return; } string Name = Guid.NewGuid().ToString(); path = Server.MapPath(Name + ".xls"); fulExcel.SaveAs(path); isFileOk = true; } catch (Exception ex) { Response.Write(ex.Message); } } else { ErrorMsg.Text = " 没有选择上传文件!"; } if (isFileOk) { try { ExcelItem excel = new ExcelItem(path); string connectionstring = CY.GFive.Configuration.ConfigurationManager.Providers.DALProviders.Default.ConnectionString; ExcelUtility.ImportFromStuedntExcel(excel, connectionstring, "Student", ExcelUtility.STUDENT_TABLE_COLUMN_MAPPING, true); ErrorMsg.Text = "导入成功!"; } catch (Exception exs) { ErrorMsg.Text = "导入文件失败:" + exs.Message; } } if (File.Exists(path)) { File.Delete(path); } }
/// <summary> /// 指定数据源Excel文件,待导入的数据库连接和表以及Excel表单中列与目标数据库表列的匹配规则, /// 将数据从Excel中导入到SQL Server数据库 /// <remarks>该函数不需要建立对Excel的数据连接,直接使用</remarks> /// </summary> /// <param name="excelItem">数据Excel源</param> /// <param name="destinationDBConnectionString">目标数据库</param> /// <param name="tableName">目标表</param> /// <param name="match">匹配规则</param> public static void ImportFromExcel(ExcelItem excelItem, String destinationDBConnectionString, String tableName, List<SqlBulkCopyColumnMapping> match) { ExcelUtility.LoadExcel(excelItem); ExcelUtility.BuildOleConnection(); try { ExcelUtility.OpenOleConnection(); // 默认导入为: 导入Excel的第一个表单 String firstSheetName = ExcelUtility.GetFirstSheetName(); // 获取第一个表单的名字 String sqlImport = string.Format("SELECT * FROM [{0}]", firstSheetName); // 参数{0}表示第一个表单 OleDbCommand command = new OleDbCommand(sqlImport, ExcelUtility.OleConnection); OleDbDataReader reader = command.ExecuteReader(); // 利用SqlBulkCopy对象导入数据 using (SqlBulkCopy copy = new SqlBulkCopy(destinationDBConnectionString)) { copy.DestinationTableName = tableName; for (int i = 0; i < match.Count; i++) { copy.ColumnMappings.Add(match[i]); } copy.WriteToServer(reader); } } catch (Exception ex) { throw ex; } finally { ExcelUtility.UnloadExcel(excelItem); ExcelUtility.CloseOleConnection(); } }
/// <summary> /// 导入学生信息数据到SQL Server数据库中, 注意该导入的目标数据库只适用于SQL Server /// </summary> /// <param name="excelItem">被导入的Excel文件</param> /// <param name="destinationDBConnectionString">目标SQL Server数据库连接</param> /// <param name="tableName">目标表名</param> /// <param name="match">匹配规则</param> public static void ImportFromStuedntExcel(ExcelItem excelItem, String destinationDBConnectionString, String tableName, List<SqlBulkCopyColumnMapping> match) { ExcelUtility.LoadExcel(excelItem); ExcelUtility.BuildOleConnection(); ExcelUtility.sqlConnectionString = destinationDBConnectionString; ExcelUtility.BuildSqlConnection(); try { ExcelUtility.OpenOleConnection(); ExcelUtility.OpenSqlConnection(); DataTable table = ExcelToTable(excelItem); DataTable tableEx = new DataTable(); // 对table进行格式化验证和处理 DataColumn dc = new DataColumn("性别copy", System.Type.GetType("System.Int32")); DataColumn dc1 = new DataColumn("修读专业代码", System.Type.GetType("System.String")); int tempStudentCode = 0; table.Columns.Add(dc); table.Columns.Add(dc1); DataColumn dc2 = new DataColumn("银行卡号", System.Type.GetType("System.String")); DataColumn dc3 = new DataColumn("学号", System.Type.GetType("System.String")); tableEx.Columns.Add(dc2); tableEx.Columns.Add(dc3); DataColumn dc4 = new DataColumn("学生状态", System.Type.GetType("System.String")); table.Columns.Add(dc4); int nbr = 0; foreach (DataRow dr in table.Rows) { // 学号问题 // 暂不生成学号, 用临时学号代替存入数据库 tempStudentCode++; string ad = dr["入学年月"].ToString(); try { dr["校内学号"] = DateTime.Parse(ad).Year.ToString() + tempStudentCode.ToString("D4"); } catch (Exception ex) { dr["校内学号"] = DateTime.Now.Year.ToString() + tempStudentCode.ToString("D4"); } // 学生状态问题,全部初始化为:未注册 dr["学生状态"] = "UnRegister"; // 专业与专业编码问题 string proName = dr["修读专业"].ToString(); string proCode = ""; SqlCommand command = new SqlCommand(); command.CommandText = "select ProCode from Profess where ProName = '" + proName + "'"; command.Connection = ExcelUtility.SqlConnection; SqlDataReader reader = command.ExecuteReader(); if (reader != null && !reader.IsClosed) { if (reader.Read()) { if (!reader.IsDBNull(0)) proCode = reader.GetString(0); } reader.Close(); } dr["修读专业代码"] = proCode; // 性别问题 if (dr["性别"].Equals("男")) { dr["性别copy"] = 1; } else if (dr["性别"].Equals("女")) { dr["性别copy"] = 2; } else { dr["性别copy"] = dr["性别"]; } // 学生数据扩展表问题 DataRow dr1 = tableEx.NewRow(); dr1["学号"] = dr["校内学号"]; dr1["银行卡号"] = dr["银行卡号"]; tableEx.Rows.Add(dr1); } // 利用SqlBulkCopy对象导入数据 using (SqlBulkCopy copy = new SqlBulkCopy(destinationDBConnectionString)) { // 导入学生数据 copy.DestinationTableName = tableName; for (int i = 0; i < match.Count; i++) { copy.ColumnMappings.Add(match[i]); } copy.WriteToServer(table); // 导入学生数据扩展表 copy.DestinationTableName = "StudentEx"; copy.ColumnMappings.Clear(); copy.ColumnMappings.Add("学号", "StdCode"); copy.ColumnMappings.Add("银行卡号", "CardCode"); copy.WriteToServer(tableEx); } } catch (Exception ex) { throw ex; } finally { ExcelUtility.UnloadExcel(excelItem); ExcelUtility.CloseSqlConnection(); ExcelUtility.CloseOleConnection(); } }
/// <summary> /// 从DataSet中将数据导入Excel文件中 /// <remarks>该函数需要建立对Excel的数据连接,不可直接使用</remarks> /// </summary> /// <param name="dataSource">被导出的数据集</param> /// <param name="desExcelFile">导入的Excel文件</param> public static void ExportToExcel(ExcelItem desExcelFile) { // 检查待保存的目标Excel文件是否存在 if (desExcelFile.Path == null || desExcelFile.Path == "") { // 错误提示 } if (!desExcelFile.Path.EndsWith(".xls")) { desExcelFile.Path += ".xls"; } if (!File.Exists(desExcelFile.Path)) { // 无文件存在报错 } // 检查源数据集的数据是否有效 if (ExcelUtility.dataSource.Tables.Count < 1) { // 没有数据的错误提示 } // 建表并写入数据 string sqlCreate = "Create Table {0} ({1})"; string sqlInsert = "Insert Into {0} ({1}) values({2})"; string fieldsCreate = String.Empty; string fieldsInsert = String.Empty; string fieldsParam = String.Empty; OleDbCommand command = new OleDbCommand(); int iTableNbr = 1; foreach (DataTable dt in ExcelUtility.dataSource.Tables) { // 清空 fieldsCreate = string.Empty; fieldsInsert = string.Empty; fieldsParam = string.Empty; // 生成SQL语句 foreach (DataColumn dc in dt.Columns) { fieldsCreate += dc.ColumnName + " varchar,"; fieldsInsert += dc.ColumnName + ","; fieldsParam += "@" + dc.ColumnName + ","; } if (fieldsCreate.EndsWith(",")) { fieldsCreate = fieldsCreate.TrimEnd(','); fieldsInsert = fieldsInsert.TrimEnd(','); fieldsParam = fieldsParam.TrimEnd(','); } try { // tableName: 为新的的Excel表单起名 String tableName = dt.TableName + DateTime.Now.Millisecond.ToString() + iTableNbr; // 执行建表 command.Connection = ExcelUtility.oleConnection; command.CommandText = string.Format(sqlCreate, tableName, fieldsCreate); command.ExecuteNonQuery(); // 有可能会抛出SQL语句的异常 // 插入数据SQL语句 command.CommandText = string.Format(sqlInsert, tableName, fieldsInsert, fieldsParam); // 开始导出数据 for (int r = 0; r < dt.Rows.Count; r++) { command.Parameters.Clear(); foreach (DataColumn dc in dt.Columns) { command.Parameters.AddWithValue("@" + dc.ColumnName, dt.Rows[r][dc].ToString()); } command.ExecuteNonQuery(); // 有可能会抛出已经存在sheet的异常, 与dt.TableName 有关 } ++iTableNbr; } catch (Exception ex) { throw ex; // 如果报数据引擎找不到某表单,是因为将被导出的Excel文件被用户打开,提示用户关闭Excel文件 } finally { } } }
/// <summary> /// 从DataSet数据集中将数据导入Excel文件 /// <remarks>该函数无需建立对Excel的数据连接,直接使用</remarks> /// </summary> /// <param name="srcData">DataSet数据集</param> /// <param name="desExcelFile">导入的Excel文件</param> public static void ExportToExcel(DataSet srcData, ExcelItem desExcelFile) { // 检查待保存的目标Excel文件是否存在 if (desExcelFile.Path == null || desExcelFile.Path == "") { // 错误提示 } if (!desExcelFile.Path.EndsWith(".xls")) { desExcelFile.Path += ".xls"; } if (!File.Exists(desExcelFile.Path)) { // 无文件存在报错 //Exception ex = new Exception("找不到目标文件"); //throw ex; //CreateBlankExcel(desExcelFile.Path); } try { ExcelUtility.LoadExcel(desExcelFile); // 注意@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelUtility.excelItem.Path + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;"""; // 有IMEX=1;而下面的oleConnection 没有IMEX=1;在有IMEX=1;系统无法自动创建一个2003的EXCEL.为了让数据导出时,系统自动创一个 // 2003的Excel, 采用了没有IMEX=1;的ole连接 string strOleConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelUtility.excelItem.Path + @";Extended Properties=""Excel 8.0;HDR=YES;"""; ExcelUtility.oleConnectionString = strOleConnection; ExcelUtility.BuildOleConnection(); ExcelUtility.OpenOleConnection(); // 构建DataSet对象 ExcelUtility.LoadDataSet(srcData); if (ExcelUtility.dataSource.Tables.Count < 1) { // 报没有数据表的错误 } // 建表并写入数据 string sqlCreate = "Create Table {0} ({1})"; string sqlInsert = "Insert Into {0} ({1}) values({2})"; string fieldsCreate = String.Empty; string fieldsInsert = String.Empty; string fieldsParam = String.Empty; OleDbCommand command = new OleDbCommand(); // iTableNbr 生成的Excel表单的数量 int iTableNbr = 1; foreach (DataTable dt in ExcelUtility.dataSource.Tables) { // 清空 fieldsCreate = string.Empty; fieldsInsert = string.Empty; fieldsParam = string.Empty; // 生成SQL语句 foreach (DataColumn dc in dt.Columns) { fieldsCreate += dc.ColumnName + " varchar,"; fieldsInsert += dc.ColumnName + ","; fieldsParam += "@" + dc.ColumnName + ","; } if (fieldsCreate.EndsWith(",")) { fieldsCreate = fieldsCreate.TrimEnd(','); fieldsInsert = fieldsInsert.TrimEnd(','); fieldsParam = fieldsParam.TrimEnd(','); } // tableName: 为新的的Excel表单起名 String tableName = dt.TableName + DateTime.Now.Millisecond.ToString() + iTableNbr; // 执行建表 command.Connection = ExcelUtility.oleConnection; command.CommandText = string.Format(sqlCreate, tableName, fieldsCreate); command.ExecuteNonQuery(); // 有可能会抛出SQL语句的异常 // 插入数据SQL语句 command.CommandText = string.Format(sqlInsert, tableName, fieldsInsert, fieldsParam); // 开始导出数据 for (int r = 0; r < dt.Rows.Count; r++) { command.Parameters.Clear(); foreach (DataColumn dc in dt.Columns) { command.Parameters.AddWithValue("@" + dc.ColumnName, dt.Rows[r][dc].ToString()); } command.ExecuteNonQuery(); // 有可能会抛出已经存在sheet的异常, 与dt.TableName 有关 } ++iTableNbr; } } catch (Exception ex) { } finally { ExcelUtility.ClearDataSet(); ExcelUtility.CloseOleConnection(); ExcelUtility.UnloadExcel(desExcelFile); } }
/// <summary> /// 卸载Excel文件 /// </summary> /// <param name="excelItem"></param> public static void UnloadExcel(ExcelItem excelItem) { ExcelUtility.excelItem = null; isExcelLoad = false; }
/// <summary> /// 将Excel转化成一个DataTable /// </summary> /// <param name="excelItem">Excel文件</param> /// <returns></returns> public static DataTable ExcelToTable(ExcelItem excelItem) { // 获取第一张表单名称 String sheetName = GetFirstSheetName(); // 构建查询语句 String select = String.Format("SELECT * FROM [{0}]", sheetName); // 生成DataSet OleDbDataAdapter dataAdapter = new OleDbDataAdapter(select, ExcelUtility.oleConnection); DataSet ds = new DataSet(); dataAdapter.Fill(ds); // return ds.Tables[0]; }
/// <summary> /// 加载Excel文件 /// </summary> /// <param name="excelItem"></param> public static void LoadExcel(ExcelItem excelItem) { if (true) // 如果文件存在,并且无人操作 { ExcelUtility.excelItem = excelItem; isExcelLoad = true; } else { } }
protected void ExprotExcel_Click(object sender, EventArgs e) { ErrorMsg.Text = ""; if (ds.Tables.Count < 1) { ErrorMsg.Text = "无统计数据,无法导出"; return; } string filepath = Server.MapPath(Guid.NewGuid().ToString() + "ReaportScoreUnion.xls"); try { ExcelItem excel = new ExcelItem(filepath); ExcelUtility.ExportToExcel(ds, excel); DownloadFile(filepath); } catch (Exception ex) { ErrorMsg.Text = "错误:" + ex.Message; } finally { if (File.Exists(filepath)) { File.Delete(filepath); } } }
/// <summary> /// 将课表导入Excel /// </summary> /// <param name="courseTable"></param> public static void ExportSchedule(Hashtable courseTable, ref string excelPath, out string actualExcelPath) { actualExcelPath = string.Empty; try { DataSet classTableSet = new DataSet(); IDictionaryEnumerator it = courseTable.GetEnumerator(); for (int j = 0; j < courseTable.Count; j++) { it.MoveNext(); // 实际课程表 DataTable ClassTable = new DataTable(); ClassTable.Columns.Add("时段"); ClassTable.Columns.Add("周一"); ClassTable.Columns.Add("周二"); ClassTable.Columns.Add("周三"); ClassTable.Columns.Add("周四"); ClassTable.Columns.Add("周五"); for (int d = 0; d < 4; d++) { DataRow dr1 = ClassTable.NewRow(); dr1[0] = "第" + (d + 1) + "节"; dr1[1] = ""; dr1[2] = ""; dr1[3] = ""; dr1[4] = ""; ClassTable.Rows.Add(dr1); } ClassTable.TableName = ((StudentsGroup)it.Key).GetName(); for (int i = 0; i < ((Schedule)it.Value).GetSlots().Count; i++) { List<List<CourseClass>> slot = ((Schedule)it.Value).GetSlots(); int day = i / 4; int time = i % 4; DataRow dr = ClassTable.Rows[time]; if (slot[i].Count >= 1) { dr[day + 1] = slot[i].First().GetCourse().GetName(); } else { dr[day + 1] = ""; } } classTableSet.Tables.Add(ClassTable); } actualExcelPath = ExcelUtility.CreateBlankExcel(excelPath); ExcelItem excelItem = new ExcelItem(excelPath); ExcelUtility.ExportToExcel(classTableSet, excelItem); } catch (Exception ex) { throw ex; } finally { } }
public static void ExportSchedule(Hashtable courseTable, ref string excelPath1, ref string excelPath2, out string actualExcelPath1,out string actualExcelPath2) { actualExcelPath1 = string.Empty; actualExcelPath2 = string.Empty; try { DataSet classTableSet = new DataSet(); DataSet teacherTableSet = new DataSet(); IDictionaryEnumerator it = courseTable.GetEnumerator(); for (int j = 0; j < courseTable.Count; j++) { it.MoveNext(); // 实际课程表 DataTable ClassTable = new DataTable(); ClassTable.Columns.Add("时段"); ClassTable.Columns.Add("周一"); ClassTable.Columns.Add("周二"); ClassTable.Columns.Add("周三"); ClassTable.Columns.Add("周四"); ClassTable.Columns.Add("周五"); for (int d = 0; d < 4; d++) { DataRow dr1 = ClassTable.NewRow(); dr1[0] = "第" + (d + 1) + "大节"; dr1[1] = ""; dr1[2] = ""; dr1[3] = ""; dr1[4] = ""; ClassTable.Rows.Add(dr1); } ClassTable.TableName = ((StudentsGroup)it.Key).GetName(); for (int i = 0; i < ((Schedule)it.Value).GetSlots().Count; i++) { List<List<CourseClass>> slot = ((Schedule)it.Value).GetSlots(); int day = i / 4; int time = i % 4; DataRow dr = ClassTable.Rows[time]; if (slot[i].Count >= 1) { dr[day + 1] = slot[i].First().GetCourse().GetName(); string teacherName = slot[i].First().GetProfessor().GetName(); if (teacherTableSet.Tables.Contains(teacherName)) { // 加课 DataRow dr1 = teacherTableSet.Tables[teacherName].NewRow(); dr1["课程名"] = slot[i].First().GetCourse().Name; dr1["所属班级"] = slot[i].First().GetGroups().First().GetName(); dr1["上课时间"] = "周" + (day+1).ToString() + "第" + (time + 1).ToString() + "大节"; teacherTableSet.Tables[teacherName].Rows.Add(dr1); } else { // 建Table并加课 teacherTableSet.Tables.Add(teacherName); teacherTableSet.Tables[teacherName].Columns.Add("课程名"); teacherTableSet.Tables[teacherName].Columns.Add("所属班级"); teacherTableSet.Tables[teacherName].Columns.Add("上课时间"); DataRow dr2 = teacherTableSet.Tables[teacherName].NewRow(); dr2["课程名"] = slot[i].First().GetCourse().Name; dr2["所属班级"] = slot[i].First().GetGroups().First().GetName(); dr2["上课时间"] = "周" + (day + 1).ToString() + "第" + (time + 1).ToString() + "大节"; teacherTableSet.Tables[teacherName].Rows.Add(dr2); } } else { dr[day + 1] = ""; } } classTableSet.Tables.Add(ClassTable); } actualExcelPath1 = ExcelUtility.CreateBlankExcel(excelPath1); actualExcelPath2 = ExcelUtility.CreateBlankExcel(excelPath2); ExcelItem excelItem1 = new ExcelItem(excelPath1); ExcelItem excelItem2 = new ExcelItem(excelPath2); // 导出学生课表 ExcelUtility.ExportToExcel(classTableSet, excelItem1); // 导出老师课表 ExcelUtility.ExportToExcel(teacherTableSet, excelItem2); } catch (Exception ex) { throw ex; } finally { } }
/// <summary> /// 从数据库表导出数据到Excel /// <remarks>该函数无需建立对Excel的数据连接,直接使用</remarks> /// </summary> /// <param name="srcDBConnectionString"></param> /// <param name="srcTable"></param> /// <param name="desExcelFile"></param> public static void ExportToExcel(String srcDBConnectionString, String srcTable, ExcelItem desExcelFile) { // 检查待保存的目标Excel文件是否存在 if (desExcelFile.Path == null || desExcelFile.Path == "") { // 错误提示 } if (!desExcelFile.Path.EndsWith(".xls")) { desExcelFile.Path += ".xls"; } if (!File.Exists(desExcelFile.Path)) { // 无文件存在报错 } try { ExcelUtility.LoadExcel(desExcelFile); ExcelUtility.BuildOleConnection(); ExcelUtility.BuildSqlConnection(srcDBConnectionString); ExcelUtility.OpenOleConnection(); ExcelUtility.OpenSqlConnection(); // 构建DataSet对象 String select = "select * from {0}"; SqlDataAdapter da = new SqlDataAdapter(string.Format(select, srcTable), ExcelUtility.sqlConnection); ExcelUtility.DataSource = new DataSet(); da.Fill(ExcelUtility.dataSource); if (ExcelUtility.dataSource.Tables.Count < 1) { // 报没有数据表的错误 } // 建表并写入数据 string sqlCreate = "Create Table {0} ({1})"; string sqlInsert = "Insert Into {0} ({1}) values({2})"; string fieldsCreate = String.Empty; string fieldsInsert = String.Empty; string fieldsParam = String.Empty; OleDbCommand command = new OleDbCommand(); // iTableNbr 生成的Excel表单的数量 int iTableNbr = 1; foreach (DataTable dt in ExcelUtility.dataSource.Tables) { // 清空 fieldsCreate = string.Empty; fieldsInsert = string.Empty; fieldsParam = string.Empty; // 生成SQL语句 foreach (DataColumn dc in dt.Columns) { fieldsCreate += dc.ColumnName + " varchar,"; fieldsInsert += dc.ColumnName + ","; fieldsParam += "@" + dc.ColumnName + ","; } if (fieldsCreate.EndsWith(",")) { fieldsCreate = fieldsCreate.TrimEnd(','); fieldsInsert = fieldsInsert.TrimEnd(','); fieldsParam = fieldsParam.TrimEnd(','); } // 执行建表 command.Connection = ExcelUtility.oleConnection; command.CommandText = string.Format(sqlCreate, srcTable, fieldsCreate); command.ExecuteNonQuery(); // 有可能会抛出SQL语句的异常 // 插入数据SQL语句 command.CommandText = string.Format(sqlInsert, srcTable, fieldsInsert, fieldsParam); // 开始导出数据 for (int r = 0; r < dt.Rows.Count; r++) { command.Parameters.Clear(); foreach (DataColumn dc in dt.Columns) { command.Parameters.AddWithValue("@" + dc.ColumnName, dt.Rows[r][dc].ToString()); } command.ExecuteNonQuery(); // 有可能会抛出已经存在sheet的异常, 与dt.TableName 有关 } ++iTableNbr; } } catch (Exception ex) { throw ex; } finally { ExcelUtility.ClearDataSet(); ExcelUtility.CloseSqlConnection(); ExcelUtility.CloseOleConnection(); ExcelUtility.UnloadExcel(desExcelFile); } }