Export Sql Data Table To Excel

Jun 6, 2008

hi ,

Can any body help me export data from sql database to excel? I am using datagrid method but it is not transferring complete table's data at a time through LAN .i.e. it works on local host ,but not working if I accessing this project from other computer.

 

 

public class gridviewexport

{

      public gridviewexport()

      {

            //

            // TODO: Add constructor logic here

            //

      }

 

    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)

                {

                    gridviewexport.PrepareControlForExport(gv.HeaderRow);

                    table.Rows.Add(gv.HeaderRow);

                }

 

                //  add each of the data rows to the table

                foreach (GridViewRow row in gv.Rows)

                {

                    gridviewexport.PrepareControlForExport(row);

                    table.Rows.Add(row);

                }

 

                //  add the footer row to the table

                if (gv.FooterRow != null)

                {

                    gridviewexport.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())

            {

                gridviewexport.PrepareControlForExport(current);

            }

        }

    }

 

 

}

View 4 Replies


ADVERTISEMENT

How To Export Data From DB Table To Excel Sheet

Dec 5, 2005

Hi .,
  Can any one guide me in exporting data from DB table to excel sheet .
 
Thanks,
vijay
 

View 3 Replies View Related

Export Data From Excel Into An Existing SQL Table

Dec 17, 2007

Hi everyone, I am new with SQL and I tried to use the code below to export data from Excel into an existing SQL table, but I keep on receiving the following message.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


To export data from Excel to existing SQL Server table,

Insert into dbo.Base_Intraday Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:Shortcuts6 - Bolsa de Valores1 - Bolsa de Valores - Bovespa;HDR=YES',
'SELECT * FROM [Link$]')

Can anyone help me on this?

Thanks

View 9 Replies View Related

Export To Excel - Number Formatted Cells Export To Excel As 'General' ?

Feb 5, 2007

Anyone know why cells within a matrix that are formatted as numeric export to Excel with a cell format proprty of "General"? Cells within a table however export with an appropriate format.

Thanks

View 1 Replies View Related

Export Table To Excel

Aug 1, 2007

how should I go about exporting a couple of columns of data from a specific database table to an excel spreadsheet stored locally on a client machine (the one the script is being executed from). I am using SQL server 2000 SP3.

I'm fairly new to using SQL server, and not experienced at all with Transact-SQL.

View 12 Replies View Related

Reporting Services :: SSRS Export To Excel Showing Data Type As General For All Data Types

Sep 16, 2015

One of my report has different data types like decimal,percentage and integer values.

When I exported the report to excel , all the values are showing as "general" data type.

How to get excel data type same as ssrs report data type by default when exported to excel?

View 2 Replies View Related

Export Data To Excel

Nov 17, 2005

Hi,
can any help me in how to export data from sqlserver 6.5 to Excel sheet.

View 4 Replies View Related

Export Data From Excel To MDF

Feb 28, 2004

Hi,

I have some newer software which utilizes the SQL Desktop Engine (MSDE) and some older 16-bit application. I want to export the database from the old app to the newer software. If I can export the data from the old app to an excel spreadsheet, can I import that into the MDF file? Is this at all possible? What kind of other software would I need? This is my first experience in this area.

Thanks

View 4 Replies View Related

Export Data To Excel Through SP

Apr 3, 2008

Hi All

Thanks in Advance...

I want to export data from SQL Server 2005 to Excel. Now I made a sp for that, since the SP will return multiple result sets and all data (all resultsets) need to export through SSIS package.

Can anyone please help me out how to do this.

Michael Raj. A



MichaelRaj Arokiyasamy

View 2 Replies View Related

Export SQL Data To Excel

Apr 18, 2008

Hello People,

I am Seema a first user in this forum. I am new to SQl and also .net. I am having a timesheet database where in I need to export those datas to Excel using stored procedure.

If I use a DBMail it works fine, but the issue is I am not able to pass parameters in query. For example the following code works fine,
DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<H1>GSR Time Entry</H1>' +
N'<table border="1">' +
N'<tr><th>Employee ID</th><th>Employee Name</th>' +
N'<th>Age</th>' +

CAST ( ( SELECT td = E.Employeeid, '',
td = E.Name, '',
td = E.Age, ''
FROM Employee as E
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='trial@yahoo.com',
@profile_name = 'seema',
@subject = 'Time Sheet',
@body = @tableHTML,
@body_format = 'HTML'

This code works fine, but in the same query when I try to send parameters say 'dept' it doesnot recognise the database at all.

I need a stored procedure which will export SQL data to an excel and I should be able to pass parameters and get data, i.e., I should be able to use the following query and the result should be exported in Excel.

ALTER procedure [dbo].[sp_email]
(
@dept varchar (50),
@exp varchar (50)
)
as
begin
select EmployeeID,Name,Age
from employee where DepartmentName= @dept and
Experience = @exp

Please help and any help and reply will be very much appreciated

Regards,

Seema



seema

View 1 Replies View Related

Export Data To Excel

Feb 23, 2008

Hi,
I am currently running the following query to export Data from a table to a ExcelSheet(test.xls)

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C: est.xls;',
'SELECT * FROM [Sheet1$]') select * from SYSCUST_AUDIT



Everything is working fine.NP's so far.
The thing what I am really looking for is,Is there a way where I can write a query and it should dynamically create a Excel sheet and export the data from the table.

Now I have manually created test.xls.
What I am looking for is ,Write some query which dynamically creates
test_'Date'.xls and export the data from the table.So after 5 days,My c:folder should have following files

test_'date1'.xls
test_'date2'.xls
test_'date3'.xls
test_'date4'.xls
test_'date5'.xls

Thanks in advance

View 1 Replies View Related

SQL 2012 :: Export Table To Excel

Jan 27, 2015

while exporting database table to ms excel, i m getting yellow marked icon on selected columns. Why is it so?

View 3 Replies View Related

NEW SQL- How To Export Table To Excel Spreadsheet?

Dec 18, 2005

Hi All,

I am new to SQL program. I did little management for SQL 2000 before.
I need to export from a table or view to excel spreadsheet for company's marketing resourece. Is there any easy simple way to do it?

Thanks all for your help.

Perry Yang

View 11 Replies View Related

Export SQL Table Names To Excel.

Jan 12, 2007

Hi,

I was wondering if there is a way to export just the table names of all the tables in a sql database to an excel spreadsheet. Can I also see the table structure, like which are the parent tables and which are the child tables? Please let me know. I'm using SQL server 2005.

Thanks.

View 5 Replies View Related

Export Table To Excel With Leading 0s

Feb 7, 2008

Hello,

I am trying to export bunch of tables to excel files, and some table columns contain leading 0s. I tried to use bcp and found that it's not possible to directly export to excel file with leading 0s. I don't want to export to txt or csv file format since the client needs to update excel file as well.

We now think to use SSIS to do the transformation, but got problem. I used OLF DB as source, Excel as destination. I directly connect OLF DB to Excel destination, but always got error message which said unicode cannot convert to non-unicode. Can anyone explain how I get this error since we don't use unicode in the tables, and I make sure the table excel created used varchar and smallint?

Thanks in advance.

View 7 Replies View Related

How To Export Data From Sqlserver To Excel

May 22, 2007

 
 
how to export data from sqlserver to excel

View 2 Replies View Related

How Do I Using C# To Export Data To Microsoft Excel From MS-SQL

Jun 20, 2007

I am newbie, can anyone gave me sample of coding or related tutirial to
connect to database and click a button to export to microsoft excel!!??
Thanks a lot,

View 2 Replies View Related

Excel Export Binary Data?

Oct 18, 2007

hi
i used to export my tables to excel file
but now i have tables which have binary (image) data
what happens to them?
is there any way to backup these data?

View 1 Replies View Related

Data Export Problem With Excel

May 17, 2000

HI,
I tried to export a table from pubs to Excel..
i am getting this error:

Error source: Microsoft JET Database Engine
Error Description: Cannot start your application.The workgroup file is missing or opened exclusively by another user.

What i have to do. Pls suggest me.
thank u.

--Kavira

View 1 Replies View Related

Export Data In Excel Or .csv File

Jul 25, 2007

hi all

I tried in enterprise manager to export data in .csv file or excel sheet
but cant get can any help me to export data

View 9 Replies View Related

Export Data From Server To Excel

Jul 22, 2015

I faced with an issue: there is a need to export data from SQL Server to excel.How to do this? I'm on SQL Server 2008 R2

View 7 Replies View Related

Export Data To Excel 2007

Jun 12, 2007

Hi Guys,

I am trying to export data from database to excel 2007 file on my machine.
I have downloaded the provider for office 2007 and I have XP with sp2 and SQL server 2005 with SP2.
I havn't installed office 2007 on my machine,but I have a excel file which is created in office 2007.To get acess of read and write i have downloaded office compatibilty pack too,So I can read and write into the file.
Now I am creating one SSIS package to export data into excel file.But I m not able to coause I am getting some errors like

"test connection failed because of an error in intializing provider. Invalid UDl file"

"Test connection is failed because of an error in intializing provider.Not a valid file name".

Please help or suggest how to export data from database to 2007 excel file.

Yogesh V. Desai. | SQLDBA|

View 10 Replies View Related

Export Data From Sqlserver To Excel

Jan 24, 2008

All, I wanted to export the data from sqlserver table and import into excel. Is that possible??? Please let me know . Thanks

View 1 Replies View Related

Problem On Export Data To Excel

Apr 15, 2008

I want use the OPENROWSET to export data into an excel file.

such as this:





Code Snippet

INSERT INTO

OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0;HDR=YES;DATABASE=C:Output.xls', 'select * from [Sheet1$]')

SELECT TOP 65000

FileNo,
InvoiceNo,
PO,
CONVERT(varchar,IssueDate,23) as IssueDate,
CONVERT(varchar,ReceivedDate,23) as ReceivedDate,
CONVERT(varchar,AuthorizationDate,23) as AuthorizationDate,
Supplier,
Currency,
FreightForwarder,
Buyer,
PaymentTerm,
Incoterm,
OrderType

from VIEW_Query_import_Shipment;

when rows in the query result is less than 20000 everything goes well, but when rows upto 30000 or more , an error occoured:





Code Snippet

String or binary data would be truncated.
The statement has been terminated.
I have readed the manual and google the web for several days but can't get any solution.

Is there anyone know anything about this?

Please help me , thanks a lot.

View 4 Replies View Related

Export Report Data To Excel

May 3, 2007

Hello



I have a requirement where the end-user's preferred solution is to export report results from Reporting Services into a spreadsheet so that the user can run a series of Excel macros on the resulting data. This is only going to serve as a quick fix until we develop a longer term solution.



My question is: is there a way to export report results from RS to an existing spreadsheet or spreadsheet template that has the macro code in it, and perhaps even initiate the macro. I would like to avoid the user having to cut and paste from the report spreadsheet to the spreadsheet with the macro in it -- reduce human error.



Any assistance would be greatly appreciated

View 2 Replies View Related

Export Mssql Data To Excel Using Vb6

Jan 22, 2008

Where is the correct forum for that,
Thanks,
Dani.

View 5 Replies View Related

Cannot Export To Excel With Huge Data

Aug 19, 2007

Hi All!

I have an Issue.

I am calling a rdl file through the Url and i am passing the Format=Excel in the Url.

Eg. http://harinarayana/ReportServer/......&Format=Excel&...........

If the data is around more than some 20000 records, its not able to export and

a Error like "The Service is not available " is being displayed.

Does anyone have any solution for scenarios like this? It would be of great help to me.

Regards

Hari

View 1 Replies View Related

Export Dynamic Pivot Table To Excel

Feb 26, 2015

I know that this is an Excel question, but I guess it is much more likely that an SQL person using dynamic pivot tables had stepped on this, rather than any advanced Excel user.

I am exporting a dynamic pivot table to Excel through a Stored Procedure. If the Stored Procedure that executes the dynamic pivot table returns 7 columns in one run, and 4 columns in the following update, then I have 3 orphaned columns that are still displayed in the spreadsheet. There isn't any content related to them, but the empty columns with their headers are bothering enough.

I've been trying to play with the data connection properties, but nothing deletes unused columns from former data executions.

View 1 Replies View Related

Any Way To Export Table To Excel File With Leading 0s?

Feb 7, 2008

I have to export about 50 excel files from different tables. Some tables contain leading 0s in the columns. If I use bcp to export to xls file, the leading 0s will be missing. If bcp to csv or txt file, there will be no issue at all. But I have to export to xls file in order to let client update that xls file and reload to tables again.

Is there any other ways to do this task?

Thanks in advance.

View 23 Replies View Related

SQL 2012 :: Export Data To Excel Using Query?

Mar 4, 2015

I need to export the data directly using a query from sql server. This is just a temporary extract. Copy pasting the result in excel is giving mis-alignment.

View 1 Replies View Related

SQL Server 2008 :: Export Data To Excel?

Jun 9, 2015

Using below statement to export a table from sql server 2008 to EXCEL 2010

Insert into Openrowset
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:ExportXLS.xlsx;' ,
'SELECT * FROM [employees$]')
SELECT name,id,group,agency FROM dbo.employees

getting below ERROR

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

below changes also done.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

View 2 Replies View Related

Simple Data Dump Export To Excel?

May 21, 2007

Any ideas on how to export from excel and simply get rs to do a data dump, not pull over any of the formatting, just simple columns and rows of data?

View 1 Replies View Related

Export MSsql Data To Excel Files

May 12, 2007

Hi

i have a MSsql file wich i want to export information from.
Every post in the database should be exportet to a own single file with all the information about the post.
So from a single question to the database i would like to have a lot of files named by a speciel filed i the post.
I also want to be able to design what the excel file should look like.

How can i do this?

Greatful for help.

/Mitmit

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved