Solved

Export datatable to excel format issues

Posted on 2010-09-23
8
889 Views
Last Modified: 2013-12-16
I have a small code snippet to export my data in the datatable to a generic spreadsheet.  I want to format the spreadsheet so that column names clear, reduce font size to 10, but I am not sure how to do this in my current code.  I thought I could use Stringbuilder's appendformat property or insert, but this is not clear to me.  Please advise.
private void exportToExcel()

    {

        string postdate = Convert.ToString(DateTime.Now.ToString("MMddyy_hhmmsstt"));

        string dirName = @"C:\AvReport\";

        string strFilePath = "AVInventory_" + postdate + ".csv";

        DirectoryInfo dInfo = new DirectoryInfo(dirName);



        StringBuilder sb = new StringBuilder();



        Response.Clear();

        Response.Buffer = true;

        Response.Charset = "";

        Response.ContentType = "application/excel";

        Response.AddHeader("Content-Disposition", "attachment; filename=" + strFilePath);



        if (!dInfo.Exists)

            dInfo.Create();         

      

        DataTable dt = new DataTable();

        

        string mc = ConfigurationManager.ConnectionStrings["SiteDB"].ConnectionString;



        using (SqlConnection conn = new SqlConnection(mc))

        {

            conn.Open();



            using (SqlDataAdapter ada = new SqlDataAdapter("SELECT * from shwAll", conn))

            {

                ada.Fill(dt);



                //column names

                for (int k = 0; k < dt.Columns.Count; k++)

                {

                    sb.Append(dt.Columns[k].ColumnName + ',');                    

                }



                sb.Append("\r\n");

                for (int i = 0; i < dt.Rows.Count; i++)

                {

                    for (int k = 0; k < dt.Columns.Count; k++)

                    {

                        sb.Append(dt.Rows[i][k].ToString().Replace(",", ";") + ',');

                    }

                    sb.Append("\r\n");

                }



                Response.Write(sb.ToString());

                Response.Flush();   

                Response.End();    

          

            }        



            lblconfirmation.Visible = true;

           lblconfirmation.Text = "<span class='alert'>The file <u>" + strFilePath + "</u> was successfully saved!</span>";



        }

    }

Open in new window

0
Comment
Question by:ayoZen
  • 5
  • 3
8 Comments
 
LVL 16

Expert Comment

by:kris_per
ID: 33745492

You are sending the csv data to the response. You can not keep the excel cell/font formattings in csv file.

You have to create .xls file and then read the data of xls file and write that data in response.

To create .xls file first, you can use:
Excel interop => http://msdn.microsoft.com/en-us/library/ms173186(VS.80).aspx

OR ado.net => http://support.microsoft.com/kb/316934
http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx


0
 

Author Comment

by:ayoZen
ID: 33745628
Hello Kris_per.  Thanks for your reply.  I opted not to use the Excel Interop, as the production server doesn't have excel installed, nor will the network group add it to that server.  Are there any other options for formatting?
0
 
LVL 16

Expert Comment

by:kris_per
ID: 33745747

The other option is to use ADO.NET (ole db driver) to create excel files...
http://support.microsoft.com/kb/316934
http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

Though I am not sure if font/formatting details can be set using ado.net...

One thought is to have a template xls file which are already formatted with proper size/font, etc...and then using ado.net (above links) export the data to appropriate cells and then write the .xls file data to response...
0
 
LVL 16

Expert Comment

by:kris_per
ID: 33745773

There are some 3rd party libraries to create xls file without using excel interop like Aspose => http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ayoZen
ID: 33746564
the file will be generated daily and the user wants to be able to archive the files.  We can't use the 3rd party, but you mentioned the following:

<<One thought is to have a template xls file which are already formatted with proper size/font, etc...and then using ado.net (above links) export the data to appropriate cells and then write the .xls file data to response...>>

could this template be created everytime the user needs it?
0
 
LVL 16

Accepted Solution

by:
kris_per earned 500 total points
ID: 33747348

Create an excel file in ms excel in another machine where ms excel is installed.
In Sheet2 copy some temporary data without any formatting (Sheet2 will be just a data-holder)
In Sheet1 format the cells, like heading with big font, thick borders, etc; for the data refer to the cells of Sheet2 using formula. So when you change the data in Sheet2, values in Sheet1 also will change, but the format will remain the same.
Copy this template file (say AVInventory_template.xls) to the production server in a folder (could be web app's App_Data folder).
When exporting using ado.net(oledb driver), copy this template file to another file with a unique file name so that multiple user requests at the same time will not cross-over the same file. Export the data to Sheet2 of the uniquely named template file (dont touch Sheet1 so formatting will not be disturbed).
After exporting, read the data from the file and write to response.

This is just my thought and I hope it will work.

0
 
LVL 16

Expert Comment

by:kris_per
ID: 33747401

Just remembered your notes on 'daily generation and archiving'. That wouldn't be an issue. Instead of making a unique file name for every request, you can name the exported file with current date. When a request comes, it can look for the file name having today's date. If it is already there, it will not do export and it will just read and write to response. If it is not there, then it can export first, (may be copy it to another archive folder) and then send to response. Any subsequest requests will use that file.
0
 

Author Closing Comment

by:ayoZen
ID: 33757397
The expert provided a great alternative to my problem!  Thank you again!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now