I have an old website developed in asp.net 2.0 and C#, I want to save data from database or DataTable To Excel File.
I searched a lot in the internet but ONLY found solutions using the HttpResponce 'Responce.Write' but this solution will not work if the file is big so I want a solution to save the excel file physically on the hard drive.
3 Answers
In most cases you would even need to have MS Excel installed in the machine where you want to run your application, in order to use Excel related libraries (ie Microsoft.Office.Interop namespace).Could you use CSV files? This could be much simpler. Just load your DataTable object into a FileStream then save the file.
What environment do you have there, which version of Excel the result file should target to? Depending on that, the answer can be different. But more or less generic one - try to use GemBox.Spreadsheet, which doesn't require Excel to be installed but it has limitations in the free version.
i found the solution if i have no Excel installed on the machine and decided to share it with public
Solution 1 (if you need to save the excel file physically on the hard disk)
you will need a gridview to put the data in it first without any css or styles just get the selected data from database with the format you need on excel file
code behind
Just Note you need to make the GridView visible true first to render it to file if it is visible false then nothing to render then you can make it false again as it is used only to hold data then render it to file
Solution 2 (if you need to just create the file on the fly then just download it to client directly)
Just note that i found these functions on my search but i don't try then as i need the first solution but only wanted to share the other solution for public as well
Not the answer you're looking for? Browse other questions tagged c#asp.netexport-to-excelc#-2.0asp.net-2.0 or ask your own question.
I searched a lot in the internet but ONLY found solutions using the HttpResponce 'Responce.Write' but this solution will not work if the file is big so I want a solution to save the excel file physically on the hard drive.
3 Answers
In most cases you would even need to have MS Excel installed in the machine where you want to run your application, in order to use Excel related libraries (ie Microsoft.Office.Interop namespace).Could you use CSV files? This could be much simpler. Just load your DataTable object into a FileStream then save the file.
What environment do you have there, which version of Excel the result file should target to? Depending on that, the answer can be different. But more or less generic one - try to use GemBox.Spreadsheet, which doesn't require Excel to be installed but it has limitations in the free version.
i found the solution if i have no Excel installed on the machine and decided to share it with public
Solution 1 (if you need to save the excel file physically on the hard disk)
you will need a gridview to put the data in it first without any css or styles just get the selected data from database with the format you need on excel file
code behind
Just Note you need to make the GridView visible true first to render it to file if it is visible false then nothing to render then you can make it false again as it is used only to hold data then render it to file
Solution 2 (if you need to just create the file on the fly then just download it to client directly)
Just note that i found these functions on my search but i don't try then as i need the first solution but only wanted to share the other solution for public as well
Not the answer you're looking for? Browse other questions tagged c#asp.netexport-to-excelc#-2.0asp.net-2.0 or ask your own question.
I am trying to export data from dataset to excel and save it directly to a given path without giving me the option to open,save or cancel.
6 Answers
Using ExcelLibrary this is a one liner ...
See also Create Excel (.XLS and .XLSX) file from C#
This C# Excel library can also be used to export the dataset. More details about how to export can be found here.
Check this DataSetToExcel
and c# (WinForms-App) export DataSet to Excel
C# Save Excel File
In the first link change the code as follows:
Remove the all code that initially starts and try the following
Here's another C# library, which lets you export from a DataSet to an Excel 2007 .xlsx file, using the OpenXML libraries.
All of the source code is provided, free of charge, along with a demo application, and you can use this in your ASP.Net, WPF and WinForms applications.
Once you've added the class to your application, it just takes one function call to export your data into an Excel file.
It doesn't get much easier than that.
Good luck !
Hi i found a perfect solution Here
Just replace 'missing.value' with System.Type.Missing in the code. Also remove
oWB.Close(System.Type.Missing, System.Type.Missing, System.Type.Missing);and
oXL.Quit();from the code. Otherwise your excel will get closed automatically as soon as it open.
Export Outlook Contacts As Excel File
It's not the greatest solution but here is what I did, it opens a new excel document then copies what is in the dataset, all you need to do is sort out the columns and save it.
Btw totes my first post to answer a question, hope it helps
Not the answer you're looking for? Browse other questions tagged c#excelexport-to-excel or ask your own question.
Recently, I had to resolve an issue of a corrupt report file of an enterprise application in production. People suggested I use EPPlus, and I was NOT disappointed.
Join the DZone community and get the full member experience.
Join For FreePrior to the availability of Open XML SDK and other Excel libraries in .NET, manipulating Microsoft Excel files from ASP.NET web applications was a daunting task. Open XML SDK makes it easy to manipulate documents that conform to the Office Open XML file format specifications — with a trade-off of writing plenty of lines of code. Similar to OpenXML, ClosedXML is another elegant library that further simplifies the process of reading and writing to Excel files and hides the complexities involved, without needing to deal with XML documents.
Recently, I had to resolve an issue of a corrupt report file of an enterprise application in production. Reviewing the logs indicated that the reporting module threw a System.OutOfMemory
exception that originated from ClosedXML. The Excel file being created had five sheets with around 400,000 rows each and ClosedXML could not handle this large amount of data.
EPPlus
Apparently, a lot of folks have had the same issue using the library, and many suggested different workarounds or alternative libraries. Catching my attention from the various suggestions is the EPPlus library, which has been around for a while but never really had much publicity like OpenXML and ClosedXML. The library is available for use via Nugget.
To test the performance and robustness of this library, I decided to throw a large dataset at it. I tried to make it create five sheets in a single workbook and save 1M rows in each sheet. It took 61 seconds on a Dell Latitude E5470 (Intel Core i5, 8GB RAM, 250GB SSD) for the library to process and save the records. It handled the large data graciously and also with fewer lines of codes compared to ClosedXML. See the source code below.
The next time you need to process a large dataset and save it to an Excel file, you ought to try EPPlus library.
Like This Article? Read More From DZone
Free DZone Refcard
Software Usage Analytics for>, DZone MVB. See the original article here.Opinions expressed by DZone contributors are their own.
I tried to export the dataset which have 4 tables to the excel sheet, Unfortunately I can't. I have code to export data table to excel. So instead of dataset, I called the 'ExportToExcel' function which I have in my code to export datatable to excel 4 times. But once it created the first sheet, it stops the control flow. Control doesn't call the second function ('ExportToExcel(dsResult.Tables[2], 'AthleteSentCount');') Here is the code
If any body knows to export the dataset to the excel with the bar chart please help me. Else please give your solution to the problem.
Thanks in advance.
4 Answers
First of all your function start streaming content
to end user and END it with
Moreover if you fix this your code will product 4 excel files.
There is an existing code for creating Excel sheets on stack overflow: How to add additional worksheets to an Excel from DataTableThe only change you will have to made will be saving xlsx to stream and transmit this stream to user.
Written in WCF & ASMX
But I used EPPuls. dlll file its Open Source dll. Please check below link for Reference. http://epplus.codeplex.com/.
Code :
C# Save Datatable To Excel
You are welcome to download and use my free C# library to export a DataSet to a 'real' Excel .xlsx file.
It uses the Microsoft OpenXML libraries, and takes just one line of code.
It looks like your code is for an ASP.Net web application, so using my library, your code would just look like this:
Just one line of code, and you'll get a real Excel file written to your page's Response.
It will contain one Worksheet per DataTable in your DataSet.
Here is some souce code sample about how to export dataset to Excel. You can use ExcelWorksheet.easy_insertDataSet method to insert multiple datatables.
About the other question, check this code for bar chart and adjust the code to generate bar chart like this: