Exporting in MS Excel by MS Excel way

Title:       Export in excel by excel way
Author:      Dimitar Nikolaev Madjarov 
Email:       madjarov_d_n@yahoo.com
Environment: Windows 2000, XP, 2003, MS Visual Studio 2003,
             ASP.NET, IIS v5.0, 5.1, 6.0
Keywords:    Export in Excel as reporting action
Level:       "Intermediate"
Description: An article which describe how to make export in excel in ASP.NET
Section      ASP.NET
SubSection   Reporting

• Download article source - 443 Kb

Introduction

Everyone developer know for basic problem when he or she has to made an export from an ASP.NET application and when the export is done  in case that we have an data field from data type string with value as for an example 08-12 when we open in MS Excel  the exported file/data are automatically convert from "MS Excel" in next data "08.December" which is very boredom and not very good  option according end user who use our application.  
This may to become a very big problem for us as developers. If you try to find solution of this case in Internet as you going to looking for information via web based sources as "http://www.google.com/" or "http://www.codeproject.com/" you will find many examples how to make an export in Excel from existing data set, data table or directly from an ASP.Grid but most of the given examples do not solve the problem which I describe above.  
Therefore I will try to provide an export in MS Excel solution which solve all the transformation problems between data types  in MS Excel and ASP.NET.  
The main idea of this article is to use the MS Excel to generate a report as we use the visual part of the MS Excel  and after that to save ready report as an Web page.  
Next step is just to copy generated structure and adapt it for our needs. 

Using the code

As I try to explain what is the main idea I will put here the code of three main methods.  The first one is an method which export in MS Excel the whole data table which it is receive.  
Second one is an method which export only the chosen column's numbers(position) and replace the  column's caption with define new names or captions.   And the last one is just an help method which support of second one to remove all unnecessary columns from an data table. 
You may see the code below.

//
// this method return is an column from dataset is for deleting or not
//
private bool isForDeleting(ArrayList columsForDeleting, string targetColum)
{
   bool actionResult = true;

   if (columsForDeleting.Count > 0   )
   {  
     for (int i = 0;  i <  columsForDeleting.Count; i++)
     {
	if (columsForDeleting[i].ToString() == targetColum)
	{
	     break;
	     actionResult = false;
	}
     }
    }
    else
    {
      actionResult = false;
    }
   return actionResult;
  }

// 
// this method return an string with only chosen one columns with correct column's captions 
// 
private string ConvertToExcelFewFiledsFromTable(DataTable dt, string sheetName, string[] exportFieldsName, int[] tableColums) 
{ 
   // 
   // Define an error export message 
   // 
   const string lenghtError = "The number of colum's names has to be equal of number of column's id!";      
   // 
   // In case that column's names and exported fields are equal as numbers going
   // to export, otherwise generate an export error. 
   // 
   if (exportFieldsName.Length != tableColums.Length) 
   { 
     // 
     // Return an export error.
     //
      return lenghtError; 
   } 
   else 
   { 
     DataTable dtUpdated = new DataTable();
     dtUpdated           = dt.Copy(); 
     // 
     // Going to update and remove necessary Column's captions with new one 
     // 
     for (int i=0; i < dtUpdated.Columns.Count; i++) 
     { 
       for (int j=0; j < exportFieldsName.Length; j++) 
       { 
          if (tableColums[j] == i) 
          { 
            // 
            // Going to update Column's captions with new one
            // 
            dtUpdated.Columns[i].Caption = exportFieldsName[j].ToString().ToUpper();
          } 
         } 
      } 
      
      // 
      // Going to remove not necessary columns 
      //
      string columsNoForDeleting = ""; 
      for (int i=0; i < tableColums.Length; i++)
      {
        columsNoForDeleting = columsNoForDeleting + ";" + dtUpdated.Columns[tableColums[i]].ColumnName.ToString();
      } 
     
      ArrayList columsForRemove = new ArrayList(); 
      for (int j=0; j < dtUpdated.Columns.Count; j++) 
      { 
         if (columsNoForDeleting.IndexOf(dtUpdated.Columns[j].ColumnName.ToString(),0) == -1) 
         { 
            columsForRemove.Add(dtUpdated.Columns[j].ColumnName.ToString()); 
         } 
      }
    
      if (columsForRemove.Count > 0) 
      { 
        dtUpdated.PrimaryKey = null; 
        for (int i=0; i < columsForRemove.Count; i++) 
        { 
          dtUpdated.Columns.Remove(columsForRemove[i].ToString());
        } 
      } 
     
      // 
      // Call again the export function into excel for whole data table 
      // 
      return ConvertToExcelAllFieldsFromTable(dtUpdated, sheetName); 
      } 
    }         

You may see the final export data result as an report on pictures above!

Conclusion

As conclusion I like to say that the way to use MS Excel as an visual designer for our report needs and after that  to "Save as" this Excel template report as a Web page and copy the structure and adapt for an ASP.NET export report  is a very powerful way to design correct reports.  
I hope that this very simple example will be useful for you guys.