.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

How to write a Data Table to EXCEL - Fastest Method

Posted By:Sajid P K       Posted Date: November 15, 2010    Points: 15    Category: ADO.NET    URL: http://www.dotnetspark.com  

This code segment Explains, How to Export data from Data table to Excel using .Net.
 

We all know that, Exporting data from a .NET application to Excel is a very common requirement.   Here first we will Copy date to bi-directional array. Then transfer array to   range.  Please Refer Code below

 

Click Here for other Method ( CEL - By - Cell copy  )

 

public static void ExportToExcel(System.Data.DataTable dt, Worksheet excelSheet)
		{
			    // Copy the DataTable to an object array
				object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

				// Copy the column names to the first row of the object array
				for (int col = 0; col < dt.Columns.Count; col++)
				{
					rawData[0, col] = dt.Columns[col].ColumnName;
				}

				// Copy the values to the object array
				for (int col = 0; col < dt.Columns.Count; col++)
				{
					for (int row = 0; row < dt.Rows.Count; row++)
					{
						rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
					}
				}
            
            
               // Fast data export to Excel
				string excelRange = string.Format("A1:{0}{1}",
                    LastCoulmLetter(dt.Columns.Count), dt.Rows.Count + 1);

				excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;			

		}

 

 

  public static string   LastCoulmLetter( int  coulmnCount ) 
      {
                  
          string finalColLetter = string.Empty;
          string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
          int colCharsetLen = colCharset.Length;

          if (coulmnCount > colCharsetLen)
          {
              finalColLetter = colCharset.Substring(
                  (coulmnCount - 1) / colCharsetLen - 1, 1);
          }

          finalColLetter += colCharset.Substring(
                  (coulmnCount - 1) % colCharsetLen, 1);

          return finalColLetter;
      }

 

 


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend