Export datatable data in Excel

Hi to all.

Normally we work in database but some time we need reporting or get data in soft copy to send email or for record purpose. So we export data in excel file.hear i show you how to export data from datatable.

first get data from database to in datatable which we show in starting session.

I genrate one function that help to export in Excel.

 public bool DatatabletoExcel(DataTable Dt)
        {
            bool result = false;
            try
            {
                //create dialog for create file
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.FileName = "File Name";
                sfd.DefaultExt = "xls";
                sfd.Filter = "xlsx files(*.xlsx)|*.xlsx";
                if (sfd.ShowDialog() != System.Windows.Forms.DialogResult.OK)
                {
                    return false;
                }
                //create excel work sheet for export data
                Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet Tempworksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                Tempworksheet .Name = "worksheet";
                int iRow = 1;
                //create header
                Tempworksheet .Cells[iRow, 1] = "Header1";
                Tempworksheet .Cells[iRow, 1].Font.Bold = true;
             
                iRow++;
                Tempworksheet .Cells[iRow, 1] = "Header2";
                Tempworksheet .Cells[iRow, 1].Font.Bold = true;
                iRow++;
             
                iRow++;
                //add column header
                for (int i = 1; i < Dt.Columns.Count + 1; i++)
                {
                    Tempworksheet .Cells[iRow, i] = Dt.Columns[i - 1].ColumnName;
                    Tempworksheet .Cells[iRow, i].Font.Bold = true;
                }
                //add data
                for (int i = 0; i < Dt.Rows.Count; i++, iRow++)
                {
                    for (int j = 0; j < Dt.Columns.Count; j++)
                    {
                        Tempworksheet .Cells[iRow + 2, j + 1] = Dt.Rows[i][j].ToString();
                    }
                }
                Tempworksheet .Rows.Font.Size = 12;
             
                string ExcelFileName = sfd.FileName;
                workbook.SaveAs(ExcelFileName);
                workbook.Close(false, ExcelFileName);
                ExcelApp.Quit();
                ExcelApp = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
                MessageBox.Show("Success message\n  '" + sfd.FileName + "'", "EXPORT", MessageBoxButtons.OK, MessageBoxIcon.Information);
                result = true;
            }
            catch (Exception ex)
            { throw ex; }
            return result;
        }
This function input a datatable and export in excel. and on success return True value and give message.
it also option to choose file name what you want to exported file and choose location,

this function basicaly using on  Microsoft.Office.Interop.Excel library to create excel file and write in it.

Thanks to read
If you like this post like and share.and any problem say us.

Comments

Post a Comment

Popular posts from this blog

Populate combo box for month and year in c#

Image
Hi to all. In this post we populate combo box for month and year. First take two combo box one for month and second for year.Name them  CmbMonth and  CmbYear . we create function for fill data in combo box. For month combo box         private void Fill_CmbMonth()         {             try             {                              //clear all data from combobox                 CmbMonth.Items.Clear();                 //add default item                 CmbMonth.Items.Add("Select");                 //fill array from System.Globalization.DateTimeFormatInfo.InvariantInfo                 var ...
Read more

How to get month days count from date or month and year

Hi all . Window form application need some calculation of date and days,so require no of days in current month or any date of month. So for that we use on c# in build function to get days count. System.DateTime.DaysInMonth(Year(int), Month(int)); This function output number of days in integer. And we need to input year and month number in integer value. If you have date then we get month and year from it. DateTime NowDate = new DateTime(); int iMonth=NowDate.Month; int iYear=NowDate.Year; If you have month name so need to get number of month int iMonth=DateTime.ParseExact("MonthName", "MMMM", CultureInfo.InvariantCulture).Month For this code need to include using System.Globalization; library. Thanks to read this post. comment if any problem in code.
Read more

CRUD Operations in win form application with access database

Hi to all, In first post we connect with access database now in this post we make operations in database. First we create one method that help to execute sql query in database. public bool Executquary (string sQuary)         {             try             { //check connection                 if (dbConnection.State != ConnectionState.Open)                 {                     dbConnection.Open();                 } //create command                 OleDbCommand cmd = new OleDbCommand(sQuary);                 cmd.Connection = dbConnection;                 try                 { /...
Read more