DataSet写入Excel
protected void ExportExcel_Bn_Click(object sender, EventArgs e)
{
FileInfo File = new FileInfo(Server.MapPath("_resourses\\UpExcel\\StudentModel.xls"));//Excel文件模板
string sql = "select XH as 学号,XM as 姓名,XB as 性别,YXDM as 院系代码,ZYDM as 专业代码,BJDM as 班级代码,XJZT as 学籍状态,CSNY as 出生年月,SYD as 生源地,JGM as 籍贯,SFZH as 身份证号,MZ as 民族编码,ZZMM as 政治面貌码 from T_YXSJZB_XSJBXX where BDPCBH='" + BDPCBH.SelectedValue + "'";
DataSet ds = DataHelper.GetDataSet(sql);
string NewAddStudent = Server.MapPath("_resourses\\UpExcel\\StudentOutFull.xls");//填充新文件为Excel文件的名称
FileInfo NewFile = new FileInfo(NewAddStudent);//实例新文件Excel文件
if (NewFile.Exists) //判断新文件是否存在
{
NewFile.Delete(); //删除文件
File.CopyTo(NewAddStudent);//复制新文件
SavetoExcel(NewAddStudent, ds);//把数据写入excel
Response.Redirect("_resourses\\UpExcel\\StudentOutFull.xls");
}
else
{
File.CopyTo(NewAddStudent);//复制新文件
SavetoExcel(NewAddStudent, ds);//把数据写入excel
Response.Redirect("_resourses\\UpExcel\\StudentOutFull.xls");
}
//string path = Server.MapPath("_resourses\\UpExcel\\学生信息");
//if (File.Exists)
//{
// ExcelCtrol.DelExcel(Server.MapPath("_resourses\\UpExcel\\学生信息.xls"));
// ExcelCtrol.ToExcel(ds, path);
// Response.Redirect("_resourses\\UpExcel\\学生信息.xls");
//}
//else
//{
// ExcelCtrol.ToExcel(ds, path);
// Response.Redirect("_resourses\\UpExcel\\学生信息.xls");
//}
}
public bool SavetoExcel(string Path, DataSet ds)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
int RowCount = ds.Tables[0].Rows.Count;
for (int j = 0; j < RowCount; j++)
{
string xh = ds.Tables[0].Rows[j][0].ToString();//学号
string xm = ds.Tables[0].Rows[j][1].ToString();//姓名
string xb = ds.Tables[0].Rows[j][2].ToString();//性别
string xy = ds.Tables[0].Rows[j][3].ToString();//院系代码
string zy = ds.Tables[0].Rows[j][4].ToString();//专业代码
string bj = ds.Tables[0].Rows[j][5].ToString();//班级代码
string xj = ds.Tables[0].Rows[j][6].ToString();//学籍状态
string CSNY = ds.Tables[0].Rows[j][7].ToString();//出生年月
string syd = ds.Tables[0].Rows[j][8].ToString();//生源地
string jg = ds.Tables[0].Rows[j][9].ToString();//籍贯
string sfz = ds.Tables[0].Rows[j][10].ToString();//身份证号
string mz = ds.Tables[0].Rows[j][11].ToString();//民族编码
string zz = ds.Tables[0].Rows[j][12].ToString();//政治面貌码
cmd.CommandText = "INSERT INTO [sheet1$] (学号,姓名,性别,院系代码,专业代码,班级代码,学籍状态,出生年月,生源地,籍贯,身份证号,民族编码,政治面貌码) VALUES('" + xh + "','" + xm + "','" + xb + "','" + xy + "','" + zy + "','" + bj + "','" + xj + "','" + CSNY + "','" + syd + "','" + jg + "','" + sfz + "','" + mz + "','" + zz + "')";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.Dispose();
return true;
}
catch (System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
}
return false;
}
