Member Avatar for babbu

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

  • 6 Contributors
  • 8 Replies
  • 201 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by Cameronsmith63

Dani AI

Generated

Note about terms and limits: a "workbook" is the Excel file; a "worksheet" (sheet) is a single tab inside that file. Excel 97–2003 (.xls) is limited to 65,536 rows × 256 columns; Excel 2007+ (.xlsx) supports 1,048,576 rows × 16,384 columns. A single cell can hold up to 32,767 characters. These limits determine whether a single sheet is sufficient or whether automatic splitting into additional sheets is needed — the choice raised.

Practical options (tradeoffs and when to use them): the SSMS Import/Export wizard is fine for one-off exports, but not for repeatable automation. For large, automated exports from SQL Server, the bcp utility or an SSIS package is far more reliable and faster. For web apps, the HTML-Rendered-as-Excel trick (as in ’s example) is quick but produces HTML that Excel opens — it loses true data typing and does not scale. ’s OleDb approach can create real Excel files but depends on Jet/ACE drivers and has 32/64-bit gotchas on servers.

Small examples (new, alternative approaches):

bcp (SQL Server) quick export to CSV:

bcp "SELECT Col1,Col2 FROM MyDb.dbo.MyTable" queryout "C:\data\out.csv" -c -t, -SServerName -T

(Replace -T with -U user -P pass for SQL authentication.)

MySQL writing CSV from server:

SELECT col1, col2
INTO OUTFILE '/tmp/out.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM mytable;

(Note: file path is on the DB server and requires FILE privilege.)

If embedding in a .NET app, pulling data into a DataTable and writing a true .xlsx with a library (ClosedXML/NPOI) avoids Excel automation on servers. Example pattern:

using (var wb = new XLWorkbook()) {
  var ws = wb.Worksheets.Add("Sheet1");
  ws.Cell(1,1).InsertTable(dataTable);
  wb.SaveAs("C:\\path\\file.xlsx");
}

Troubleshooting notes: prefer CSV or native .xlsx over Interop on servers; watch encoding (use UTF-8 or BOM for Excel), decimal/date locales, commas/newlines inside fields (quote them), and split into sheets/chunk files when row limits are reached. For scheduled, repeatable exports, use SSIS / SQL Agent or bcp; for application-driven exports, use a library that produces real XLSX files.

Recommended Answers

All 8 Replies

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for babbu

some kind of sample code will be very helpful

Member Avatar for 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

Member Avatar for zunkan

Hi

Best Website For Learning :

please visit our site :

news,articles,shop,forums :

http://www.zunkan.ir

Member Avatar for Cameronsmith63

Or you can write a sql command that writes a file with all the data that you selected. If it must be in c#, create a SProc that writes this file, then call it through your c# app. Keep SQL in SQL.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.