Excel导入

[yaoleshi]

我将Gridview中的数据导入到Excel中时,怎么老提示说:

丢失文件:................\css\style.css


这是什么原因,怎么解决.

打开文件后,Gridview中的数据没有.如:

“GridView1”上同时定义了 DataSource 和 DataSourceID。请移除一个定义。

而且它是把整个页面都导出来了.我只需要把Gridview中的数据导入到Excel中就可以了,请问怎么解决.

我都弄了好几天了,没弄出来!

各位帮帮忙解决解决!

[luofuxian]

1楼的应该可以了,虽然是VB的,我学C#,跟我看过的差不多,只是把GridView里的东西传到Excel中

[yaoleshi]

各位帮帮忙 看看咯 问题出在那

[yagebu1983]

学习了！
帮你顶！！！

[luckyboy101]

[Quote=引用 1 楼 blue_maple 的回复:]
给你个2003datagrid 保存到excel的例子
response.Clear()

'Set the content type to Excel.
response.AddHeader("content-disposition", "attachment;filename=" & sTitle & ".xls")
response.ContentType = "application/vnd.ms-excel"
response.ContentEncoding = System.Text.Encoding.UTF8

'Clear Controls
datagridClearControls(sourceDataGr…
[/Quote]

[blue_maple]

给你个2003datagrid 保存到excel的例子
response.Clear()

'Set the content type to Excel.
response.AddHeader("content-disposition", "attachment;filename=" & sTitle & ".xls")
response.ContentType = "application/vnd.ms-excel"
response.ContentEncoding = System.Text.Encoding.UTF8

'Clear Controls
datagridClearControls(sourceDataGrid)

Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)

'Get the HTML for the control.
sourceDataGrid.RenderControl(hw)

'Write the HTML back to the browser.
response.Write(tw.ToString())

' End the response.
response.End()

[menu_852]

[xuebin_hu]

把你的代码帖出来,这样才能看具体问题!

[yaoleshi]

页面代码

 <asp:ObjectDataSource ID="ObjectDataSource1"  EnablePaging="true" TypeName="rsmdemo.Components.BLL" 
          SelectMethod="SearchAllAuthLog" SelectCountMethod="GetAuthLogCount" runat="server" EnableCaching="false" SortParameterName="sortExpression">
          <SelectParameters >
          <asp:ControlParameter ControlID="txtKeyWord" Name="keyword" 
          ConvertEmptyStringToNull="false" PropertyName="Text" Type="string"/>
          </SelectParameters>
          </asp:ObjectDataSource>
           <asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1" AutoGenerateColumns="False" 
                          AllowPaging="True"  EnableViewState="False" AllowSorting="True">
	 <HeaderStyle BackColor="#89BCE9" /> 
	
	 <Columns>
	 <asp:BoundField DataField="FullName" HeaderText="Full Name" SortExpression="FullName"/>
	 <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" SortExpression="EmployeeID"/>
	 <asp:BoundField DataField="CardID" HeaderText="Card ID" SortExpression="CardID"/>
     <asp:TemplateField HeaderText="Authentication Staus">
         <ItemTemplate>
             <asp:Label ID="AuthFlag" runat="server" Text='<%# GetAuthStatus(Eval("AuthFlag")) %>'></asp:Label>
         </ItemTemplate>
     </asp:TemplateField>
     <asp:BoundField DataField="Content" HeaderText="Description"/>
	 <asp:BoundField DataField="AuthDate" HeaderText="Last Authenticated Time"/>
	 </Columns>
          </asp:GridView>     

[yaoleshi]

页面时间调用代码

protected void btnExport_Click(object sender, EventArgs e)
    {
        try
        {
            PrepareGridViewForExport(GridView1);
            ExportGridView();

            GlobalUtil.LogInfo(GlobalUtil.GetResourceText("log_export_syslog"));
        }
        catch (Exception ex)
        {
            GlobalUtil.HandleException(ex, Session, Server);
        }
    }
//方法
private void PrepareGridViewForExport(Control gv)
    {

        LinkButton lb = new LinkButton();

        Literal l = new Literal();

        string name = String.Empty;

        for (int i = 0; i < gv.Controls.Count; i++)
        {

            if (gv.Controls[i].GetType() == typeof(LinkButton))
            {

                l.Text = (gv.Controls[i] as LinkButton).Text;

                gv.Controls.Remove(gv.Controls[i]);

                gv.Controls.AddAt(i, l);

            }

            else if (gv.Controls[i].GetType() == typeof(DropDownList))
            {

                l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

                gv.Controls.Remove(gv.Controls[i]);

                gv.Controls.AddAt(i, l);

            }

            else if (gv.Controls[i].GetType() == typeof(CheckBox))
            {

                l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";

                gv.Controls.Remove(gv.Controls[i]);

                gv.Controls.AddAt(i, l);

            }

            if (gv.Controls[i].HasControls())
            {

                PrepareGridViewForExport(gv.Controls[i]);

            }

        }

    }
 private void ExportGridView()
    {
        GridViewExportUtil.Export("SysLog.xls", GridView1);
    }

[yaoleshi]

底层方法

/// <summary>
    /// 
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="gv"></param>
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

    /// <summary>
    /// Replace any of the contained controls with literals
    /// </summary>
    /// <param name="control"></param>
    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }

[suyiming]

可以直接用存储过程
zjx的 存储过程

create proc p_exporttb
@sqlstr varchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(250),   --文件名
@sheetname varchar(250)=''  --要创建的工作表名,默认为文件名
as 
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')

--检查文件是否已经存在
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
 set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
       +';CREATE_DB="'+@sql+'";DBQ='+@sql
else
 set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
    +';DATABASE='+@sql+'"'

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)

select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case 
   when b.name like '%char' 
   then case when a.length>255 then 'memo'
    else 'text('+cast(a.length as varchar)+')' end
   when b.name like '%int' or b.name='bit' then 'int'
   when b.name like '%datetime' then 'datetime'
   when b.name like '%money' then 'money'
   when b.name like '%text' then 'memo'
   else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','sql_variant','varbinary','binary','timestamp')
 and a.id=(select id from tempdb..sysobjects where name=@tbname)

if @@rowcount=0 return

select @sql='create table ['+@sheetname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
   ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

set @sql='drop table ['+@tbname+']'

exec(@sql)
return


lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist





GO