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

Kran2022 241 Reputation points
2022-12-28T10:55:39.587+00:00

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?

274573-image.png

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;  
				}  
  
			}  
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
6,940 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
1,942 questions
XAML
XAML
A language based on Extensible Markup Language (XML) that enables developers to specify a hierarchy of objects with a set of properties and logic.
557 questions
No comments
{count} votes

Accepted answer
  1. Hui Liu-MSFT 14,561 Reputation points Microsoft Employee
    2022-12-29T09:06:15.843+00:00

    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:
    274792-image.png

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

    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.


0 additional answers

Sort by: Most helpful