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