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

Top 5 Contributors of the Month
Sandeep Singh

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:


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