C# - EXPORT Dataset To EXCEL

Feb 12, 2011

I am using the following code to export fields from a database table into excel. What I want to do is be able to write a SQL statement to retrieve fields from multiple tables and export them into excel. This code only allows me to export one table. Also, how can I display a save prompt dialog?

protected void export_Click(object sender, EventArgs e)
{
string sql = null;
string data = null;
string path = save_as.Text;
int i = 0;
int j = 0;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
SqlConnection cnn = new SqlConnection(GetConnectionString());
cnn.Open();
sql = "SELECT Story, CreationDate FROM Story";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 1, j + 1] = data;
}
}
xlWorkBook.SaveAs(path+".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
//MessageBox.Show("Excel file created , you can find the file c:\csharp.net-informations.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
//MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}

View 2 Replies


Similar Messages:

Export Excel File From Dataset In C#?

May 29, 2010

i have data in sql server i want to export to data with asp.net C#

View 3 Replies

Web Forms :: Export A Big Dataset To Excel Via Web?

Feb 24, 2010

Actually I have a SQL query that a user exports to excel in our web server. I did it in c# with Visual Studio 2003 (.Net v1.1): I get the data from a SQL server into a Dataset, then with this method I create the XLS file:

private void ExportAsExcelFile ( DataSet dtExport )
{
Response.Clear() ;
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.UTF8 ;
Response.Charset = string.Empty ;
Response.AddHeader ( "Content-Disposition", "attachment;filename=data.xls" ) ;
System.IO.StringWriter objStringWriter = new StringWriter () ;
System.Web.UI.HtmlTextWriter objHtmlTextWriter = new System.Web.UI.HtmlTextWriter(objStringWriter) ;
DataGrid objDatagrid = new DataGrid () ;
objDatagrid.DataSource = dtExport ;
objDatagrid.DataBind () ;
objDatagrid.RenderControl ( objHtmlTextWriter ) ;
Response.Write (objStringWriter.ToString()) ;
Response.End () ;
}

Well, this works when the SQL query has 10.000 rows and so on. But now the query has more than 200.000 rows, and I´m getting a TimeOut Error trying to export the data. I read about generate this big files with a Store Procedure and the SQL Commad: BCP [URL], but I tryed and it fails. I have no permissions to execute this command. Is there any other way to export this SQL data to Excel via a ASPX page?

View 1 Replies

VS 2005 - Export Dataset To Excel

Sep 9, 2011

I have a dataset wity multiple table, the same dataset need to export to excel

dataset.table[0] data
dataset.table[1] data in the same excel sheet

View 1 Replies

VS 2008 - Export Dataset To Excel Attachment

Jul 12, 2012

I need to give users of my asp.net/vb.net web application the ability to click a button which will then send them an e-mail with the contents of a SQL query attached as an Excel e-mail attachment.

I can manage all of this apart from getting the contents of a VB dataset object into an Excel file on my server.

I know how to export a gridview to Excel at the client end, but my boss has been adamant that he wants the button to send an e-mail with the results attached - I have no need to display a gridview at all.

How to export the contents of a datareader or dataset directly into an Excel file at the server side?

View 12 Replies

Web Forms :: Export The Dataset Values Into Excel In Application

Mar 9, 2011

I am trying to Export the dataset values into Excel in my application, its working for first time. Again if i click the export button [second time] the button does't cause postback and the entire page is not postback.

View 4 Replies

C# - Numbers Are Converted As Text When Export Dataset To Excel

Jul 8, 2010

Iam exporting dataset to excel , the numbers on the sheet shown as text when export it to excel. Here is the code:

public class Excel
{
const int rowLimit = 65000;
private static string getWorkbookTemplate()
{
StringBuilder sb = new StringBuilder(818);
sb.AppendFormat(@"<?xml version=""1.0""?>{0}", Environment.NewLine);
sb.AppendFormat(@"<?mso-application progid=""Excel.Sheet""?>{0}", Environment.NewLine);
sb.AppendFormat(@"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
sb.AppendFormat(@" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}", Environment.NewLine);
sb.AppendFormat(@" xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}", Environment.NewLine);
sb.AppendFormat(@" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
sb.AppendFormat(@" xmlns:html=""[URL]
sb.AppendFormat(@" <Styles>{0}", Environment.NewLine);
sb.AppendFormat(@" <Style ss:ID=""Default"" ss:Name=""Normal"">{0}", Environment.NewLine);
sb.AppendFormat(@" <Alignment ss:Vertical=""Bottom""/>{0}", Environment.NewLine);
sb.AppendFormat(@" <Borders/>{0}", Environment.NewLine);
sb.AppendFormat(@" <Font ss:FontName=""Verdana"" x:Family=""Swiss"" ss:Size=""12"" ss:Color=""#0000A0""/>{0}", Environment.NewLine);
sb.AppendFormat(@" <Interior/>{0}", Environment.NewLine);
sb.AppendFormat(@" <NumberFormat/>{0}", Environment.NewLine);
sb.AppendFormat(@" <Protection/>{0}", Environment.NewLine);
sb.AppendFormat(@" </Style>{0}", Environment.NewLine);
sb.AppendFormat(@" <Style ss:ID=""s62"">{0}", Environment.NewLine);
sb.AppendFormat(@" <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine);
sb.AppendFormat(@" ss:Bold=""1""/>{0}", Environment.NewLine);
sb.AppendFormat(@" </Style>{0}", Environment.NewLine);
sb.AppendFormat(@" <Style ss:ID=""s63"">{0}", Environment.NewLine);
sb.AppendFormat(@" <NumberFormat ss:Format=""Short Date""/>{0}", Environment.NewLine);
sb.AppendFormat(@" </Style>{0}", Environment.NewLine);
sb.AppendFormat(@" </Styles>{0}", Environment.NewLine);
sb.Append(@"{0}
</Workbook>");
return sb.ToString();
}
private static string replaceXmlChar(string input)
{
input = input.Replace("&", "&");
input = input.Replace("<", "<");
input = input.Replace(">", ">");
input = input.Replace(""", """);
input = input.Replace("'", "&apos;");
return input;
}
private static string getCell(Type type, object cellData)
{
Object data = (cellData is DBNull) ? "" : cellData;
if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("<Cell><Data ss:Type="Number">{0}</Data></Cell>", data);
if (type.Name.Contains("Date") && data.ToString() != string.Empty)
{
return string.Format("<Cell ss:StyleID="s63"><Data ss:Type="DateTime">{0}</Data></Cell>", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));
}
return string.Format("<Cell><Data ss:Type="String">{0}</Data></Cell>", replaceXmlChar(data.ToString()));
}
private static string getWorksheets(DataSet source)
{
StringWriter sw = new StringWriter();
if (source == null || source.Tables.Count == 0)
{
sw.Write("<Worksheet ss:Name="Sheet1">
<Table>
<Row><Cell><Data ss:Type="String"></Data></Cell></Row>
</Table>
</Worksheet>");
return sw.ToString();
}
foreach (DataTable dt in source.Tables)
{
if (dt.Rows.Count == 0)
sw.Write("<Worksheet ss:Name="" + replaceXmlChar(dt.TableName) + "">
<Table>
<Row><Cell ss:StyleID="s62"><Data ss:Type="String"></Data></Cell></Row>
</Table>
</Worksheet>");
else
{
//write each row data
int sheetCount = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if ((i % rowLimit) == 0)
{
//add close tags for previous sheet of the same data table
if ((i / rowLimit) > sheetCount)
{
sw.Write("
</Table>
</Worksheet>");
sheetCount = (i / rowLimit);
}
sw.Write("
<Worksheet ss:Name="" + replaceXmlChar(dt.TableName) +
(((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "">
<Table>");
//write column name row
sw.Write("
<Row>");
foreach (DataColumn dc in dt.Columns)
sw.Write(string.Format("<Cell ss:StyleID="s62"><Data ss:Type="String">{0}</Data></Cell>", replaceXmlChar(dc.ColumnName)));
sw.Write("</Row>");
}
sw.Write("
<Row>");
foreach (DataColumn dc in dt.Columns)
sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));
sw.Write("</Row>");
}
sw.Write("
</Table>
</Worksheet>");
}
}
return sw.ToString();
}
public static string GetExcelXml(DataTable dtInput, string filename)
{
string excelTemplate = getWorkbookTemplate();
DataSet ds = new DataSet();
ds.Tables.Add(dtInput.Copy());
string worksheets = getWorksheets(ds);
string excelXml = string.Format(excelTemplate, worksheets);
return excelXml;
}
public static string GetExcelXml(DataSet dsInput, string filename)
{
string excelTemplate = getWorkbookTemplate();
string worksheets = getWorksheets(dsInput);
string excelXml = string.Format(excelTemplate, worksheets);
return excelXml;
}
public static void ToExcel(DataSet dsInput, string filename, HttpResponse response)
{
string excelXml = GetExcelXml(dsInput, filename);
response.Clear();
response.AppendHeader("Content-Type", "application/vnd.ms-excel");
response.AppendHeader("Content-disposition", "attachment; filename=" + filename);
response.Write(excelXml);
response.Flush();
response.End();
}
public static void ToExcel(DataTable dtInput, string filename, HttpResponse response)
{
DataSet ds = new DataSet();
ds.Tables.Add(dtInput.Copy());
ToExcel(ds, filename, response);
}
}

View 1 Replies

Export DataSet To Multiple Excel Sheets And Download Into A Zip File Using C#?

Jan 21, 2011

how to Export DataSet to Multiple Excel Sheets and download those file into a zip in C# asp.net?

View 2 Replies

How To Export A Dataset To Excel And Raise A File Download Dialog From Webmethod

Aug 6, 2010

I am using the following code to export a datset to excel sheet.

[Code]....

Problem is that its not raising file download and hence no export is taking place.The same code works fine in a normal method.But with the webmethod its not working.

View 3 Replies

DataSource Controls :: How To Export The Current DataSet Bound To A Gridview To Excel

Mar 12, 2011

I have a GridView populated by an ObjectDataSource. I also have several DropDownLists filtering the ObjectDataSource. I found a link providing a way to export a DataSet (or DataTable) to Excel here:

http://msmvps.com/blogs/deborahk/archive/2009/07/23/writing-data-from-a-datatable-to-excel.aspx

ow I can ensure that filters applied to the ObjectDataSource when the method is called are applied. In short, how can I ensure what the UI is displaying is what the file will include when exported?

View 2 Replies

Forms Data Controls :: Export Multiple Grids And Dataset To Excel Sheet

Feb 9, 2010

In a page im having multiple data grids with some data tables.How can i export all these to excel sheet without any change in the alignment.

View 6 Replies

Forms Data Controls :: How To Export Dataset To Excel When Click Gridview Linkbutton

Jun 28, 2010

I want to Export dataset data to excel ,when i click gridview linkbutton.

My Scenior is : Actually i am Passing a parameter from griview linkbutton.Depending on the parameter value .I want to export dataset data to excel.

View 7 Replies

Crystal Reports :: Export Dataset To Excel From Crystal Report

Dec 17, 2010

I want to export dataset to Excel. I did the following ways:

1) bind a dataset to a crystal report,
2) export crystal report to Excel Can it be done without displaying crystal report and only Excel file? Or, I have to find out a way export to Excel directly from dataset?

View 1 Replies

When Export Grid View Export In Excel It That String Shows In Single Line?

Jul 24, 2010

i have in grid view that grid view have very long string. when i export grid view export in excel it that string shows in single line

View 1 Replies

Web Forms :: Export To Excel Function Errors When Trying To Render The Grid For The Export?

Apr 27, 2010

I have a web form that contains a GridView, which impliments Search in Grid with a TextBox and Button control in the footer. This works as I have it now. I have also implimented a Export to Excel function. The Export to Excel works by itself if the Search in Grid function is not included in the footer.The Export to Excel function errors when trying to render the Grid for the export. Is there a better way to incorporate both functions and accomplish the same thing? Am I missing something simple?

[Code]...

View 21 Replies

VS 2008 - Uploading Excel Data To Dataset, Then DataSet To SQL Server?

Jan 18, 2010

What I am thinking is this? Is it possible for me to upload the data from an excel file to dataset of my application first, so that the user can view the data in a gridview to review it first, before the user strike the save button, to save it in the database. So, that in case there is a problem, the gridview will high light all the data with an error. So the user can easily pull out the excel and correct the data before saving it in the database.

View 4 Replies

Forms Data Controls :: Gridview - Export To Excel And Formatting Excel From Right To Left

Jun 10, 2010

How can I export data from a Gridview to excel and once exported the data should be formatted in right to left (arabic) manner. How can I achieve this?

View 2 Replies

Visual Studio :: Exporting A .rdlc To Excel / Analagous Export Of That Report To Excel?

Nov 2, 2010

I've got an .rdlc report which I print from my .net app (C# Visual Studio 2005), where I use a RemoteReportPrinter object. I give it as parameters the server name, report name, report parameters, etc. Works fine. My question is, I would live to do an analagous export of that report to Excel. Set it up exactly the same, but have it export to Excel rather than print, probably using some other type of object than RemoteReportPrinter.

View 1 Replies

Data Controls :: Export GridView To Excel And Save Excel File To Folder?

Apr 6, 2014

i use this code to export data into word.

Protected Sub Export(sender As Object, e As EventArgs) Handles btn_printexcel.Click
Response.Clear()
Response.Buffer = True

[Code]....

but the export docs will be downloaded into the user computer.

what should i do so that the exported docs is save into the server?

View 1 Replies

Databases :: Export To Excel Multiple Sheets Without COM Interop Or Excel Object?

Feb 18, 2011

i want to export multiple tables in a dataset to excel workbook with each tables as sheets without using COM Interop or Excel Object.

View 2 Replies

Forms Data Controls :: Export Datagrid To Excel Without Excel Component?

Sep 22, 2010

We have the requirement to export the data from the grid excel. We can't install the Excel on the server. The excel generated need to be in the printable format. The excel generated needs to have custom headers, footers, repeatble rows, columns and landscape / portrait orientation style.

View 1 Replies

Forms Data Controls :: Export Gridview To Ms Excel - Set Excel Page Orientation As Landscape

Jan 22, 2010

I am exporting a gridview in MS Excel as mentioned in the following link:

[URL]

I want to set orientation of excel pages as landscape. Is there any way to do that? Any header information to be added for file being exported to set orientation of pages as landscape?

View 2 Replies

Forms Data Controls :: Export To Excel - Desing And Build Big Html Table Into Excel?

Feb 25, 2011

i have table something like this on click of export to excel i need to save that or export that to excel file .can u plz help me how i can do this.one more thing how i can desing and build that big html table into excel is there any simple way i can do the same

View 1 Replies

How To Export Results In Sheet To Excel Without Installing Excel On Server

Sep 22, 2010

We are deveopingawebiste in asp.net. There are tempelates of each aspx page made in the form of excel sheet. Upon completion of the online calculation, the user is to be allowed to export the results to excel and save them. There would be obviously no formulae in such sheet

I do not want to install Excel on theserver and instead want to utilize the Excel installed on the user's computer for this purpose as every user's computer is expected to have Excel installed. Is this possible to do? How to do this?

I am not an expert in this. let me know if you would need any more information or clarification.

View 2 Replies

Export GridView To Excel Without Losing Grid Lines In Excel?

Mar 4, 2010

I have a GridView that I want to export to Excel. When I use the sample code I find online, it exports the content to Excel just fine, but for some reason it also clears out all grid lines outside of my exported table.

For your average excel user this is easy enough to fix, but I need this solution to work for everyone.

So then is there a way to export the data in a GridView into an Excel Workbook so that it looks like it was just typed into Excel? I've pasted the code I am using below, assume that a GridView called toPrint exists and has accurate data.

Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + name + "_Registration_Forms.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
Page.EnableViewState = false;
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
toPrint.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

EDIT: Found one partial solution. If I export as a comma-delimited list and set the header to be a CSV file, it opens fine and all grid lines (even those outside of the exported data) are showing. The only problem with this of course is having to strip out every comma and newline character from my values before exporting them.

View 1 Replies







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