如何将数据库表的查询结果导出EXCEL表中
我给你一段代码吧:其实网站里面有很多的代码:
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 Button8_Click(object sender, EventArgs e)//确定添加键
{
string strConn = "Data Source=.;DataBase=gongnuan;Uid=sa;Pwd=sa";
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write(" ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
Response.Write("");
return;//当选择的不是Excel文件时,返回
}
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("~\\upfiles\\") filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write(""); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i )
{
string pininputdate = dr[i]["excel列名"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string insertstr = "insert语句";
SqlCommand cmd = new SqlCommand(insertstr, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("");
}
}
Response.Write("");
}
cn.Close();
}
你可以好好研究下
急需 php+mysql网站,后台如何将数据库中的一个表...
///
/// Excel数据导出
///ds代表按SQL条件查出数据的集合
///
public void CreateExcel(DataSet ds, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" FileName);
string colHeaders = "", ls_item = "";
//定义表对象与行对象,同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题,各标题之间以t分割,最后一个列标题后加回车符
for (i = 0; i < cl; i )
{
if (i == (cl - 1))//最后一列,加n
{
colHeaders = dt.Columns[i].Caption.ToString() "\n";
}
else
{
colHeaders = dt.Columns[i].Caption.ToString() "\t";
}
}
resp.Write("店仓ID\t店仓类型\n");
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流,并且e79fa5e98193e58685e5aeb931333330343933置空ls_item以便下行数据
for (i = 1; i < cl; i )
{
if (i == (cl - 1))//最后一列,加n
{
ls_item = row[i].ToString() "\n";
}
else
{
ls_item = row[i].ToString() "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
查询数据库的数据并显示在jsp页面的表格里,怎么把...
///
/// Excel数据导出
///ds代表按SQL条件查出数据的集合
///
public void CreateExcel(DataSet ds, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" FileName);
string colHeaders = "", ls_item = "";
//定义表对象与行对象,同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题,各标题之间以t分割,最后一个列标题后加回车符
for (i = 0; i < cl; i )
{
if (i == (cl - 1))//最后一列,加n
{
colHeaders = dt.Columns[i].Caption.ToString() "\n";
}
else
{
colHeaders = dt.Columns[i].Caption.ToString() "\t";
}
}
resp.Write("店仓ID\t店仓类型\n");
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流,并且e79fa5e98193e58685e5aeb931333330343933置空ls_item以便下行数据
for (i = 1; i < cl; i )
{
if (i == (cl - 1))//最后一列,加n
{
ls_item = row[i].ToString() "\n";
}
else
{
ls_item = row[i].ToString() "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
利用EXCEL表格为数据库制作查询网页
//$result 是你数据库里的数据 ,下面的代码就可以直接用 很简单的 ,不明白的直接问我就好了
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: attachment; filename=ABC_Users.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo iconv("utf8","gb2312","用户id\t");
echo iconv("utf8","gb2312","用户名\t");
echo iconv("utf8","gb2312","手机\t\n");
foreach($result as $key=>$val){\
echo iconv("utf8","gb2312",$val['id'])."\t";
echo iconv("utf8","gb2312",$val['name'])."\t";
echo iconv("utf8","gb2312",$val['mobile'])."\t\n";
}
jsp页面中导入excel表中的数据到数据库中
///
/// Excel数据导出
///ds代表按SQL条件查出数据的集合
///
public void CreateExcel(DataSet ds, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" FileName);
string colHeaders = "", ls_item = "";
//定义表对象与行对象,同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题,各标题之间以t分割,最后一个列标题后加回车符
for (i = 0; i < cl; i )
{
if (i == (cl - 1))//最后一列,加n
{
colHeaders = dt.Columns[i].Caption.ToString() "\n";
}
else
{
colHeaders = dt.Columns[i].Caption.ToString() "\t";
}
}
resp.Write("店仓ID\t店仓类型\n");
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流,并且e79fa5e98193e58685e5aeb931333330343933置空ls_item以便下行数据
for (i = 1; i < cl; i )
{
if (i == (cl - 1))//最后一列,加n
{
ls_item = row[i].ToString() "\n";
}
else
{
ls_item = row[i].ToString() "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}