Export data to excel from table with modified values/add col to excel

Question

Hi All: Morning
I'm able to extract data from the database and save it as excel file. But how can i add column names? then add the data to each column?

From the query i have a datetime field CreatedOn example: 12/01/2022 14:34:23, but i need to seperate to Date, Time, Year, Month, Day , Hour & DayofWeek then add it to the excel file instead of CreatedOn datetime field?

Method to read .db and save as excel file on the desktop:

  private void Button_Click_4(object sender, RoutedEventArgs e)  
		{  
			ExcelUtlity obj = new ExcelUtlity();  
			System.Data.DataTable dt = GetTable2();  
			string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + "Statictics_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";  
			obj.WriteDataTableToExcel(dt, "Data", path, "Details");  
			System.Windows.MessageBox.Show("Excel created testPersonExceldata.xlsx");  
		}  
		  
  
		public System.Data.DataTable GetTable2()  
		{  
			SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=C:\\db_test\\Database.db");  
			m_dbConnection.Open();  
  
			StringBuilder query = new StringBuilder();  
			query.Append("SELECT");  
			query.Append("[Oid], [CreatedOn], [OrderOid] ");  
			query.Append(",[OrderLineOid], [OrderNumber], [Product] ");  
			query.Append(",[PaperTypeName], [OutputProfile], [OutputProfileChannel] ");  
			query.Append("FROM Stats");  
  
			SQLiteCommand sqlCom = new SQLiteCommand(query.ToString(), m_dbConnection);  
			SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);  
			System.Data.DataTable dt = new System.Data.DataTable();  
			sda.Fill(dt);  
			m_dbConnection.Close();  
			return dt;  
		}  
  
		public class ExcelUtlity  
		{  
			public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)  
			{  
				Application excel;  
				Workbook excelworkBook;  
				Worksheet excelSheet;  
				Range excelCellrange;  
				try  
				{  
					excel = new Application();  
  
					excel.Visible = false;  
					excel.DisplayAlerts = false;  
					excelworkBook = excel.Workbooks.Add(Type.Missing);  
					excelSheet = (Worksheet)excelworkBook.ActiveSheet;  
					excelSheet.Name = worksheetName;  
  
					//excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();  
					int rowcount = 2;  
					for (int Idx = 0; Idx < dataTable.Columns.Count; Idx++)  
					{  
						excelSheet.Range["A2"].Offset[0, Idx].Value = dataTable.Columns[Idx].ColumnName;  
					}  
					for (int Idx = 0; Idx < dataTable.Rows.Count; Idx++)  
					{  
						excelSheet.Range["A3"].Offset[Idx].Resize[1, dataTable.Columns.Count].Value =  
						dataTable.Rows[Idx].ItemArray;  
					}  
					excelworkBook.SaveAs(saveAsLocation); ;  
					excelworkBook.Close();  
					excel.Quit();  
					return true;  
				}  
				catch (Exception ex)  
				{  
                    System.Windows.MessageBox.Show(ex.Message);  
					return false;  
				}  
				finally  
				{  
					excelSheet = null;  
					excelCellrange = null;  
					excelworkBook = null;  
				}  
  
			}  

Answer 1

For the problem of importing database data into Excel, you could refer to the following code.

using System;  
using System.Data.SQLite;  
using System.Windows;  
using Microsoft.Office.Interop.Excel;  
using Application = Microsoft.Office.Interop.Excel.Application;  
using Window = System.Windows.Window;  
  
namespace ExcelDemo  
{  
    public partial class MainWindow : Window  
    {  
        public MainWindow()  
        {  
            InitializeComponent();  
              
        }  
        public System.Data.DataTable GetTable()  
        {  
            SQLiteConnection.CreateFile("MyDatabase.sqlite");  
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");  
            m_dbConnection.Open();  
            string sql = "create table MyData ( ID varchar(20), Date varchar(20),  Time varchar(20) , Year varchar(20), Month varchar(20),Day varchar(20),Hour varchar(20),DayOfWeek varchar(20),  OrderLineOid varchar(20) ,Product varchar(20) )";  
            
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);  
            command.ExecuteNonQuery();  
  
            sql = "INSERT INTO MyData(ID, Date,Time,Year,Month,Day,Hour,DayOfWeek,OrderLineOid,Product) VALUES('1', '01/12/2022','14:34','2022','12','1','14','4','1','9×13' )";  
            command = new SQLiteCommand(sql, m_dbConnection);  
            command.ExecuteNonQuery();  
  
            sql = "INSERT INTO MyData(ID, Date,Time,Year,Month,Day,Hour,DayOfWeek,OrderLineOid,Product) VALUES('2', '01/12/2022','14:34','2022','12','1','14','4','2','10×10' )";  
            command = new SQLiteCommand(sql, m_dbConnection);  
            command.ExecuteNonQuery();  
  
            SQLiteCommand sqlCom = new SQLiteCommand("Select * From MyData", m_dbConnection);  
            SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);  
            System.Data.DataTable dt = new System.Data.DataTable();  
            sda.Fill(dt);  
            m_dbConnection.Close();  
            return dt;  
        }  
        private void btn_Click(object sender, RoutedEventArgs e)  
        {  
            ExcelUtlity obj = new ExcelUtlity();  
            System.Data.DataTable dt = GetTable();  
            obj.WriteDataTableToExcel(dt, "Data", "C:\\...\\testPersonExceldata.xlsx", "Details");  
            MessageBox.Show("Excel created testPersonExceldata.xlsx");  
        }  
    }  
    public class ExcelUtlity  
    {  
        public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)  
        {  
            Application excel;  
            Workbook excelworkBook;  
            Worksheet excelSheet;  
            Range excelCellrange;  
            try  
            {  
                excel = new Application();  
  
                excel.Visible = false;  
                excel.DisplayAlerts = false;  
                excelworkBook = excel.Workbooks.Add(Type.Missing);  
                excelSheet = (Worksheet)excelworkBook.ActiveSheet;  
                excelSheet.Name = worksheetName;  
                
                excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();  
                int rowcount = 2;  
                for (int Idx = 0; Idx < dataTable.Columns.Count; Idx++)  
                {  
                    excelSheet.Range["A2"].Offset[0, Idx].Value = dataTable.Columns[Idx].ColumnName;  
                }  
                for (int Idx = 0; Idx < dataTable.Rows.Count; Idx++)  
                {  
                    excelSheet.Range["A3"].Offset[Idx].Resize[1, dataTable.Columns.Count].Value =  
                    dataTable.Rows[Idx].ItemArray;  
                }  
                excelworkBook.SaveAs(saveAsLocation); ;  
                excelworkBook.Close();  
                excel.Quit();  
                return true;  
            }  
            catch (Exception ex)  
            {  
                MessageBox.Show(ex.Message);  
                return false;  
            }  
            finally  
            {  
                excelSheet = null;  
                excelCellrange = null;  
                excelworkBook = null;  
            }  
  
        }  
    }  
}  

The result:

----------------------------------------------------------------------------

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.