Generate Excel to Export Data using Datatable

Generate Excel is a Major Feature which is used frequently in Web Applications as well as

in Console Applications. So i am going to present an article about generating excel with MS-Excel installed on and without MS-Excel installed on target computer.

Problem with Excel Automation
The technique that is most frequently used to transfer data to an Excel workbooks is Automation. With Automation, you can call methods and properties that are specific to Excel tasks, but this solution has many drawbacks. Some of them are described in the Microsoft Knowledge Base. Additionally, you have to manage the lifetime of the temporary Excel files(.xls) created on the server. Also, it is slow, because Excel runs in a separate process.

• You can generate excel without MS-Office installed on target computer..
• You Can Export Data Table data to created Excel

class GenerateExcel
 
{

//  This function will Genereate Excel without MS-Office installed on target computer

 public void CreateExcel(string fileName)
        {
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
                Console.WriteLine("** Excel Deleted ***");
            }
            if (!File.Exists(fileName))
            {
                FileStream stream = new FileStream(filename, FileMode.OpenOrCreate); 

                stream.Close();

                Console.WriteLine("** Excel Created ***");
            }
        }
  
        public void createExcelwithMSOffice(string fileName)
        {
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
                Console.WriteLine("** Excel Deleted ***");
                swlog.WriteLine("***  Excel Exists....Excel Deleted.....  ***");
            }
            if (!File.Exists(fileName))
            {
                Application xl = null;
                _Workbook wb = null;
                object misValue = System.Reflection.Missing.Value;
 
                xl = new Application();
                xl.SheetsInNewWorkbook = 1;
                wb = (_Workbook)(xl.Workbooks.Add(Missing.Value));
                wb.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                wb.Close(true, misValue, misValue);
                Console.WriteLine("** Excel Created ***");
                swlog.WriteLine("***    New Excel Created     ***");
            }
        }
 
 //  This function will export Datatable Data to Created Excel......

        public void ExportToSpreadsheet(System.Data.DataTable table, string name)
        {
            StreamWriter sw = null;
            sw = new StreamWriter(name);
            string ColValue;
            string ColName = "";
            if (table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    ColName = table.Columns[i].ColumnName.ToString() + "\t";
                    sw.Write(ColName);
                }
                sw.WriteLine("\t");
                foreach (DataRow row in table.Rows)
                {
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        ColName = table.Columns[i].ColumnName.ToString();
                        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(" ", "");
                        ColValue = ColValue.ToString().Replace("-Select-;", "");
                        if (table.Columns[i].DataType.Name == "Boolean")
                        {
                            ColValue = ColValue.ToString().Replace("True", "Yes");
                            ColValue = ColValue.ToString().Replace("False", "No");
                        }
                        sw.Write(ColValue);
                    }
                    sw.WriteLine("\t");
                }
                sw.Close();
                Console.WriteLine("** Data Exported to Excel ***");
            }
            else
            {
                Console.WriteLine("** No Data to Export in Excel ***");
                Environment.Exit(0);
            }
        }
}

Calling the functions..

class Program
    {
        static void Main(string[] args)
        {
           System.Data.DataTable dt = new DataTable();
           GenerateExcel oexcel = new GenerateExcel ();
           string filename=@"C:\demo.xls";
           oexcel.CreateExcel(filename);
           oexcel.ExportToSpreadsheet(dt,filename);
         }
    } 

I hope this article gives you a head start in working with Excel files from .NET and C#.