小弟做了个导出excel,在用excel导入SQL,单导出来没问题,但是把导出来的excel在导进SQL就有问题.但是新建一个excel导入SQL又没问题,问题就出在,我导出来的只一个内似与excel的工作薄,我要把工作薄转换为excel表的格式,在把excel表里的名字改成Sheet1在导就行,转换格式没什么问题,但是又要转换excel表里的表里就有点麻烦,我导出来的时候又不能改excel表里的名字。所以请高手帮忙。修改我的代码,和给段源代码都可以,或者给个导出导入excel的小项目也可以,求求大家啦!本人邮箱[email protected] MSN: [email protected]
导出代码:
ds = new DataSet();
ds = FEGCRM.Access.AlanSheetDA.Get("","");
DataTable dt = ds.Tables["Alan"];
dt.Columns["Id"].ColumnName = "ID";
dt.Columns["LotNo"].ColumnName = "订单批号";
dt.Columns["Enterprise"].ColumnName = "业";
StringWriter sw = new StringWriter();
GridView dv = new GridView();
dv.DataSource = dt;
dv.DataBind();
dv.AllowPaging = false; Response.ClearContent();
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=Sheet1.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/excel";
HtmlTextWriter htw = new HtmlTextWriter(sw);
dv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
导入代码:
string strFullFileName = DateTime.Now.ToString("yyyymmddhhmmss") + ".xls";
string strpath = Server.MapPath("excelfile") + strFullFileName;//确定一个文件名
fileup.PostedFile.SaveAs(strpath);
string mystring = "provider = microsoft.jet.oledb.4.0 ; data source = " + strpath + ";extended properties=excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myda = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myds = new DataSet();
myda.Fill(myds);
try
{
Edit_data(myds.Tables[0]);//传参数进行导入
}
catch (Exception ex)
{
Label1.Text = "导入数据出现错误...";
}
ds = new DataSet();
ds = FEGCRM.Access.AlanSheetDA.Get("", "");
this.GridItem.DataSource = ds;
if (ds.Tables[0].Rows.Count != 0)
{
string m_SqlNo = ds.Tables[0].Rows[0]["Id"].ToString();
SetOrderDetail(m_SqlNo);
}
以上代码导入肯定没错,导出的时候导的不是非正规的excel表格,请高手指点,还有就是导出的时候如何修改excel表里的表名,就是如何把保存的文件名和excel表里的表里分开因为我导入的时候表名只能用Sheet1,所以我导出的时候excel表里的表里一定要是Sheet1,请高手指点.
导出代码:
ds = new DataSet();
ds = FEGCRM.Access.AlanSheetDA.Get("","");
DataTable dt = ds.Tables["Alan"];
dt.Columns["Id"].ColumnName = "ID";
dt.Columns["LotNo"].ColumnName = "订单批号";
dt.Columns["Enterprise"].ColumnName = "业";
StringWriter sw = new StringWriter();
GridView dv = new GridView();
dv.DataSource = dt;
dv.DataBind();
dv.AllowPaging = false; Response.ClearContent();
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=Sheet1.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/excel";
HtmlTextWriter htw = new HtmlTextWriter(sw);
dv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
导入代码:
string strFullFileName = DateTime.Now.ToString("yyyymmddhhmmss") + ".xls";
string strpath = Server.MapPath("excelfile") + strFullFileName;//确定一个文件名
fileup.PostedFile.SaveAs(strpath);
string mystring = "provider = microsoft.jet.oledb.4.0 ; data source = " + strpath + ";extended properties=excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myda = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myds = new DataSet();
myda.Fill(myds);
try
{
Edit_data(myds.Tables[0]);//传参数进行导入
}
catch (Exception ex)
{
Label1.Text = "导入数据出现错误...";
}
ds = new DataSet();
ds = FEGCRM.Access.AlanSheetDA.Get("", "");
this.GridItem.DataSource = ds;
if (ds.Tables[0].Rows.Count != 0)
{
string m_SqlNo = ds.Tables[0].Rows[0]["Id"].ToString();
SetOrderDetail(m_SqlNo);
}
以上代码导入肯定没错,导出的时候导的不是非正规的excel表格,请高手指点,还有就是导出的时候如何修改excel表里的表名,就是如何把保存的文件名和excel表里的表里分开因为我导入的时候表名只能用Sheet1,所以我导出的时候excel表里的表里一定要是Sheet1,请高手指点.
有人说做成模板,那么如何做模版,如何填数据?您可以在论坛上给我留言,也可以发邮件给我:[email protected],叩谢