import data from sql to excel

[babbu]

hie
m using visual studio 2005 and sql 2005
i want to export my sql database to excel..

[sknake]

That is not enough information. You can use the import/export wizard built in the SQL Server Management studio. Excel has a limit ~65535 if i recall correctly (may be more in the newer version) so you might not be able to export all of your data. Also do you want the data in one excel file with multiple workbooks, or multiple excel files with a single workbook? More background information on your end goal and database would help out in the decision making process.

[babbu]

That is not enough information. You can use the import/export wizard built in the SQL Server Management studio. Excel has a limit ~65535 if i recall correctly (may be more in the newer version) so you might not be able to export all of your data. Also do you want the data in one excel file with multiple workbooks, or multiple excel files with a single workbook? More background information on your end goal and database would help out in the decision making process.

in excel 07 the limit is 1048567. however even if this many rows are filled up cant we use another sheet.
and i was looking forward to export it in a single excel file with multiple workbooks.
however id also like to understnd how it can be exported to single workbook- multiple excel files.

[MeSampath]

Hi,

I would like to suggest following steps;

1) use OLEDB client to export the data Excel.
2) Connect Excel using OleDb methods. for detailed connection strings, have a look at
http://www.connectionstrings.com/excel
3) Using excel object you can create multiple sheets as well as columns.
4) use that excel sheets as tables and using data adapters export the data.

Good luck.

[babbu]

some kind of sample code will be very helpful

[vinnijain]

Read the following article:
http://www.dotnetheaven.com/UploadFile/rahul4_saxena/DataInExcelSheet05302007080823AM/DataInExcelSheet.aspx

[vinnijain]

try the following code:

/*********************for exporting data to the excel sheet***************/
/************************************************* ************************/
protected void btnCreateExcel_Click(object sender, EventArgs e)

{

try

{

ExportGridDataToExcel(gvDataExcel, "EmpInfo");

//empinfo is the excel file name that i want to create

}

catch (Exception ex)

{

Response.Write(ex.Message);

}

finally

{

Response.End();

}

}

public void ExportGridDataToExcel(GridView gvData, string strFileName)

{

Response.Clear();

Response.AddHeader("Content-disposition", string.Format("attachement;filename={0}.xls", strFileName));

Response.Charset = "";

Response.ContentType = "application/vnd.xls";

StringWriter swExcel = new StringWriter();

HtmlTextWriter htwExcel = new HtmlTextWriter(swExcel);

gvData.RenderControl(htwExcel);

Response.Write(swExcel.ToString());

}

public override void VerifyRenderingInServerForm(Control control)

{

}

Hope it helps