excel的导入问题 excel 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 string strConn = "Data Source=.;DataBase=Database;Uid=sa;Pwd=sa"; //链接SQL数据库 protected void Page_Load(object sender, EventArgs e) { SqlConnection cn = new SqlConnection(strConn); cn.Open(); SqlDataAdapter sda = new SqlDataAdapter("select * from suserss", cn); DataSet ds = new DataSet(); sda.Fill(ds, "suserss"); this.GridView1.DataSource = ds.Tables["suserss"]; this.GridView1.DataKeyNames = new string[] { "gasId" }; this.GridView1.DataBind(); } /// <summary> /// 查询EXCEL电子表格添加到DATASET /// </summary> public DataSet ExecleDs(string filenameurl, string table) { string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn); odda.Fill(ds, table); return ds; } protected void Button1_Click(object sender, EventArgs e) { if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Response.Write("<script>alert('请您选择Excel文件')</script> "); return;//当无文件时,返回 } string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xls") { Response.Write("<script>alert('只可以选择Excel文件')</script>"); return;//当选择的不是Excel文件时,返回 } SqlConnection cn = new SqlConnection(strConn); cn.Open(); string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName; //获取Execle文件名 DateTime日期函数 // string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径 string savePath = Server.MapPath(("upfiles/") + filename); FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上 DataSet ds = ExecleDs(savePath, filename); //调用自定义方法 DataRow[] dr = ds.Tables[filename].Select(); //定义一个DataRow数组 int rowsnum = ds.Tables[filename].Rows.Count; if (rowsnum == 0) { Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示 } else { for (int i = 0; i < dr.Length; i++) { string gasId = dr[i]["车辆ID"].ToString(); string carName = dr[i]["车辆"].ToString();//日期 excel列名【名称不能变,否则就会出错】 string gasBeginTime = dr[i]["起始加油时间"].ToString();//编号 列名 以下类似 decimal beginLc = Convert.ToDecimal(dr[i]["起始里程"].ToString()); string gasEndTime = dr[i]["最后加油时间"].ToString(); decimal endLc = Convert.ToDecimal(dr[i]["最后里程"].ToString()); decimal gLs = Convert.ToDecimal(dr[i]["公里数"].ToString()); decimal gasNum = Convert.ToDecimal(dr[i]["加油量"].ToString()); decimal gasHao = Convert.ToDecimal(dr[i]["油耗量"].ToString()); string yearMonth = dr[i]["年月"].ToString(); string sqlcheck = "select count(*) from suserss where carName='" + carName + "'And yearMonth='" + yearMonth + "'"; //检查车辆是否存在 SqlCommand sqlcmd = new SqlCommand(sqlcheck,cn); int count = Convert.ToInt32(sqlcmd.ExecuteScalar()); if (count < 1) { string insertstr = "insert into suserss (gasId,carName, gasBeginTime, beginLc, gasEndTime, endLc, gLs, gasNum, gasHao, yearMonth) values('" + gasId + "','" + carName + "','" + gasBeginTime + "'," + beginLc + ",'" + gasEndTime + "'," + endLc + "," + gLs + "," + gasNum + "," + gasHao + ",'" + yearMonth + "')"; SqlCommand cmd = new SqlCommand(insertstr, cn); try { cmd.ExecuteNonQuery(); } catch (MembershipCreateUserException ex) //捕捉异常 { Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>"); } } else { Response.Write("<script>alert('内容重复!禁止导入');location='default.aspx'</script></script> "); continue; } } Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>"); } cn.Close(); } }这个是代码?请问应该怎么改? 在循环里使用Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");完全是坑爹的行为你应该定义个int变量,标识到底是导入成功了还是失败,有无报错,然后写个分支语句,只输出一次script脚本 你确定你的数据全部导入了?另外,别把输出语句写在for循环里面,加入有N多条数据重复是什么效果 你在这 if (count < 1)设个断点,单步执行,看每句有没有数据,count的值string sqlcheck = "select count(*) from suserss where carName='" + carName + "' And yearMonth='" + yearMonth + "'"; //检查车辆是否存在这语句and前得有空格, 把多个Word数据导入到Excel同一个数据表中private void btn_Read_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Word.Application word = new Microsoft.Office.Interop.Word.Application();//实例化Word对象 Microsoft.Office.Interop.Word.Table table;//声明Word表格对象 int P_int_TableCount = 0, P_int_Row = 0, P_int_Column = 0;//定义3个变量,分别用来存储表格数量、表格中的行数、列数 string P_str_Content;//存储Word表格的单元格内容 object missing = System.Reflection.Missing.Value;//获取缺少的object类型值 string[] P_str_Names = txt_Word.Text.Split(',');//存储所有选择的Word文件名 object P_obj_Name;//存储遍历到的Word文件名 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 //打开指定的Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表 for (int i = 0; i < P_str_Names.Length - 1; i++)//遍历所有选择Word文件名 { P_obj_Name = P_str_Names[i];//记录遍历到的Word文件名 //打开Word文档 Microsoft.Office.Interop.Word.Document document = word.Documents.Open(ref P_obj_Name, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing); P_int_TableCount = document.Tables.Count;//获取Word文档中表格的数量 if (P_int_TableCount > 0)//判断表格数量是否大于0 { for (int j = 1; j <= P_int_TableCount; j++)//遍历所有表格 { table = document.Tables[j];//记录遍历到的表格 P_int_Row = table.Rows.Count;//获取表格行数 P_int_Column = table.Columns.Count;//获取表格列数 for (int row = 1; row <= P_int_Row; row++)//遍历表格中的行 { for (int column = 1; column <= P_int_Column; column++)//遍历表格中的列 { P_str_Content = table.Cell(row, column).Range.Text;//获取遍历到的单元格内容 newWorksheet.Cells[i + row, column] = P_str_Content.Substring(0, P_str_Content.Length - 2);//将遍历到的单元格内容添加到Excel单元格中 } } } } else { if (P_int_Row == 0)//判断前面是否已经填充过表格 newWorksheet.Cells[i + P_int_Row + 1, 1] = document.Content.Text;//直接将Word文档内容添加到工作表中 else newWorksheet.Cells[i + P_int_Row, 1] = document.Content.Text;//直接将Word文档内容添加到工作表中 }//codego.net/tags/11/1/ document.Close(ref missing, ref missing, ref missing);//关闭Word文档 } excel.Application.DisplayAlerts = false;//不显示提示对话框 workbook.Save();//保存工作表 workbook.Close(false, missing, missing);//关闭工作表 word.Quit(ref missing, ref missing, ref missing);//退出Word应用程序 MessageBox.Show("已经成功将多个Word文档的内容合并到了Excel的同一个数据表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } 谢谢大家的帮助,我其实是不会改的,这个代码是在CSDN这里下的,他是可以用的,但是在excel中多输入几条数据在导入是,数据可以导入进去,但是跳出的对话框不对,是禁止导入。想问一下大家应该怎么改一下。 将SQL Server数据查询结果导入到Excel private void btn_Excel_Click(object sender, EventArgs e) { if (dgv_Info.Rows.Count == 0)//判断是否有数据 return;//返回 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 excel.Application.Workbooks.Add(true);//在Excel中添加一个工作簿 excel.Visible = true;//设置Excel显示 //生成字段名称 for (int i = 0; i < dgv_Info.ColumnCount; i++) { excel.Cells[1, i + 1] = dgv_Info.Columns[i].HeaderText;//将数据表格控件中的列表头填充到Excel中 }//codego.net/tags/11/1/ //填充数据 for (int i = 0; i < dgv_Info.RowCount - 1; i++)//遍历数据表格控件的所有行 { for (int j = 0; j < dgv_Info.ColumnCount; j++)//遍历数据表格控件的所有列 { if (dgv_Info[j, i].ValueType == typeof(string))//判断遍历到的数据是否是字符串类型 { excel.Cells[i + 2, j + 1] = "'" + dgv_Info[j, i].Value.ToString();//填充Excel表格 } else { excel.Cells[i + 2, j + 1] = dgv_Info[j, i].Value.ToString();//填充Excel表格 } } } } 关于fckeditor 用法 【请教】TextBox 失去焦点时,计算3个TextBox的和,写到第四个TextBox上 asp.net乱码问题? 应该是触发器问题 一个关于页面状态的问题? 怎样在一个iframe的控件事件里激发另一个iframe刷新页面呢? 关于JS脚本解决控件刷屏的问题~~~ 请教~~~ 我想让数据库查询出来的记录集的某些字段用一定的格式显示,比如,加粗,超级连接该怎么办》 如何在服务器端控制页面刷新? 如何根据一个网页对话框的返回值,改变WebForm上某一控件的值或属性 初学者问一下经验求指点 asp.net如何读取报文体中的参数值?
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from suserss", cn);
DataSet ds = new DataSet();
sda.Fill(ds, "suserss");
this.GridView1.DataSource = ds.Tables["suserss"];
this.GridView1.DataKeyNames = new string[] { "gasId" };
this.GridView1.DataBind(); }
/// <summary>
/// 查询EXCEL电子表格添加到DATASET
/// </summary>
public DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds, table);
return ds;
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
// string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
string savePath = Server.MapPath(("upfiles/") + filename);
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[filename].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[filename].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string gasId = dr[i]["车辆ID"].ToString();
string carName = dr[i]["车辆"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string gasBeginTime = dr[i]["起始加油时间"].ToString();//编号 列名 以下类似
decimal beginLc = Convert.ToDecimal(dr[i]["起始里程"].ToString());
string gasEndTime = dr[i]["最后加油时间"].ToString();
decimal endLc = Convert.ToDecimal(dr[i]["最后里程"].ToString());
decimal gLs = Convert.ToDecimal(dr[i]["公里数"].ToString());
decimal gasNum = Convert.ToDecimal(dr[i]["加油量"].ToString());
decimal gasHao = Convert.ToDecimal(dr[i]["油耗量"].ToString());
string yearMonth = dr[i]["年月"].ToString();
string sqlcheck = "select count(*) from suserss where carName='" + carName + "'And yearMonth='" + yearMonth + "'"; //检查车辆是否存在
SqlCommand sqlcmd = new SqlCommand(sqlcheck,cn);
int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
if (count < 1)
{
string insertstr = "insert into suserss (gasId,carName, gasBeginTime, beginLc, gasEndTime, endLc, gLs, gasNum, gasHao, yearMonth) values('" + gasId + "','" + carName + "','" + gasBeginTime + "'," + beginLc + ",'" + gasEndTime + "'," + endLc + "," + gLs + "," + gasNum + "," + gasHao + ",'" + yearMonth + "')"; SqlCommand cmd = new SqlCommand(insertstr, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
else
{
Response.Write("<script>alert('内容重复!禁止导入');location='default.aspx'</script></script> ");
continue;
}
}
Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");
} cn.Close(); }
}
这个是代码?请问应该怎么改?
你应该定义个int变量,标识到底是导入成功了还是失败,有无报错,然后写个分支语句,只输出一次script脚本
这语句and前得有空格,
private void btn_Read_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Word.Application word = new Microsoft.Office.Interop.Word.Application();//实例化Word对象
Microsoft.Office.Interop.Word.Table table;//声明Word表格对象
int P_int_TableCount = 0, P_int_Row = 0, P_int_Column = 0;//定义3个变量,分别用来存储表格数量、表格中的行数、列数
string P_str_Content;//存储Word表格的单元格内容
object missing = System.Reflection.Missing.Value;//获取缺少的object类型值
string[] P_str_Names = txt_Word.Text.Split(',');//存储所有选择的Word文件名
object P_obj_Name;//存储遍历到的Word文件名
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
for (int i = 0; i < P_str_Names.Length - 1; i++)//遍历所有选择Word文件名
{
P_obj_Name = P_str_Names[i];//记录遍历到的Word文件名
//打开Word文档
Microsoft.Office.Interop.Word.Document document = word.Documents.Open(ref P_obj_Name, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);
P_int_TableCount = document.Tables.Count;//获取Word文档中表格的数量
if (P_int_TableCount > 0)//判断表格数量是否大于0
{
for (int j = 1; j <= P_int_TableCount; j++)//遍历所有表格
{
table = document.Tables[j];//记录遍历到的表格
P_int_Row = table.Rows.Count;//获取表格行数
P_int_Column = table.Columns.Count;//获取表格列数
for (int row = 1; row <= P_int_Row; row++)//遍历表格中的行
{
for (int column = 1; column <= P_int_Column; column++)//遍历表格中的列
{
P_str_Content = table.Cell(row, column).Range.Text;//获取遍历到的单元格内容
newWorksheet.Cells[i + row, column] = P_str_Content.Substring(0, P_str_Content.Length - 2);//将遍历到的单元格内容添加到Excel单元格中
}
}
}
}
else
{
if (P_int_Row == 0)//判断前面是否已经填充过表格
newWorksheet.Cells[i + P_int_Row + 1, 1] = document.Content.Text;//直接将Word文档内容添加到工作表中
else
newWorksheet.Cells[i + P_int_Row, 1] = document.Content.Text;//直接将Word文档内容添加到工作表中
}//codego.net/tags/11/1/
document.Close(ref missing, ref missing, ref missing);//关闭Word文档
}
excel.Application.DisplayAlerts = false;//不显示提示对话框
workbook.Save();//保存工作表
workbook.Close(false, missing, missing);//关闭工作表
word.Quit(ref missing, ref missing, ref missing);//退出Word应用程序
MessageBox.Show("已经成功将多个Word文档的内容合并到了Excel的同一个数据表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void btn_Excel_Click(object sender, EventArgs e)
{
if (dgv_Info.Rows.Count == 0)//判断是否有数据
return;//返回
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
excel.Application.Workbooks.Add(true);//在Excel中添加一个工作簿
excel.Visible = true;//设置Excel显示
//生成字段名称
for (int i = 0; i < dgv_Info.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv_Info.Columns[i].HeaderText;//将数据表格控件中的列表头填充到Excel中
}//codego.net/tags/11/1/
//填充数据
for (int i = 0; i < dgv_Info.RowCount - 1; i++)//遍历数据表格控件的所有行
{
for (int j = 0; j < dgv_Info.ColumnCount; j++)//遍历数据表格控件的所有列
{
if (dgv_Info[j, i].ValueType == typeof(string))//判断遍历到的数据是否是字符串类型
{
excel.Cells[i + 2, j + 1] = "'" + dgv_Info[j, i].Value.ToString();//填充Excel表格
}
else
{
excel.Cells[i + 2, j + 1] = dgv_Info[j, i].Value.ToString();//填充Excel表格
}
}
}
}