Problem Exporting Data Into Excel using C#

Question

4.00/5 (2 votes)

See more:

Hi,

I am exporting data into Excel using a datatable. It is working fine and giving me a number of columns in Excel. However, I want to add and make bold some custom column which should appear above the datatable column in Excel.

If I can change their color then please let me know. See code below.

public void ExportToSpreadsheet(DataTable table, string name)
      {
          HttpContext context = HttpContext.Current;
          context.Response.Clear();
          string ColValue;

          string ColName = "";

          for (int i = 0; i < table.Columns.Count; i++)
          {
              ColName = table.Columns[i].ColumnName.ToString() + "\t";
              context.Response.Write(ColName);
          }



          context.Response.Write(Environment.NewLine);
          foreach (DataRow row in table.Rows)
          {
              for (int i = 0; i < table.Columns.Count; i++)
              {
                  //row[i] = row[i].ToString().Trim().Replace("\t", string.Empty) ;
                  ColName = table.Columns[i].ColumnName.ToString();

                  DateTime tempDate = new DateTime();
                  if (DateTime.TryParse(row[i].ToString().Trim(), out tempDate) == true)
                      ColValue = tempDate.ToString("dd/MM/yyyy");
                  else
                  {
                      ColValue = row[i].ToString();
                  }
                  ColValue = ColValue.ToString().Replace(",", string.Empty) + "\t";
                  ColValue = ColValue.ToString().Replace(Environment.NewLine, " ");
                  ColValue = ColValue.ToString().Replace("\n", " ");
                  ColValue = ColValue.ToString().Replace("&nbsp;", "");
                  ColValue = ColValue.ToString().Replace("-Select-;", "");
                  if (table.Columns[i].DataType.Name == "Boolean")
                  {
                      ColValue = ColValue.ToString().Replace("True", "Yes");
                      ColValue = ColValue.ToString().Replace("False", "No");
                  }

                  context.Response.Write(ColValue);

              }

              context.Response.Write(Environment.NewLine);

          }

          context.Response.ContentType = "application/ms-excel";
          context.Response.AppendHeader("Content-Disposition", "attachment; filename = " + name + ".xls");
          context.Response.End();


      }

Posted 9-Mar-11 23:33pm

touseef4pk

Updated 10-Mar-11 0:39am

Slacker007

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Answer 1 · 2011-03-10T01:34:00

Without automating Excel, which you shouldn't really do using server side code (and may run into permissions issues anyway), a way to achieve this is to use the similar code above but output valid HTML and save with the .xls extension.

This will let you bold certain columns + apply general HTML styling.

e.g look at examples here

http://forums.asp.net/t/1214938.aspx[^]

For example, create a new text file on your computer. Paste the following HTML into it & save the file. Then change the file extension to .xls & open it in Excel. Notice we've got bold headers + a 'red' style.

XML

<HTML>
<HEAD>
<style type='text/css'>
    table
    {
        cellpadding: 0px;
        font-size: xx-small;
        border-collapse:collapse;
    }
    td.some-style
    {
        border: 1px solid black;
        color: red;
    }
</style>
</HEAD>
<BODY>
<TABLE border=0>
        <TR>
        <Td></Td>
        <Td></Td>
        <Td></Td>
        <Td class="some-style">This is an example</Td>
        <Td></Td>
     </TR>
    <thead>
        <TR>
        <Th> ProductID</Th>
        <Th> Product Name</Th>
        <Th> Sales Person</Th>
        <Th> Date Closed</Th>
        <Th> Sale Amount</Th>
        </TR>
    </thead>
    <TR>
        <TD>1</TD>
        <TD>Code Project</TD>
        <TD>Dylan Morley</TD>
        <TD>10/03/2010</TD>
        <TD>50.00</TD>
    </TR>
    <TR>
        <TD>2</TD>
        <TD>Microsoft</TD>
        <TD>Dylan Morley</TD>
        <TD>10/03/2010</TD>
        <TD>50.00</TD>
    </TR>
</TABLE>
</BODY>
</HTML>

You could create this HTML quite easily in your export routine

NB: This would only be a valid solution for machines with Excel 2003 or greater installed, which I think is when then first introduced HTML capabilities?