Generate xlsx excel from Datatable without using external library

Question

Hi All &
@RLWA32
@Karen Payne MVP
I tried to understand the code from the below github link generate excel file without using any libraries but it looks complicated, could you please explain to me how can i use the code from the below link using my datatable to generate xlsx excel file? My goal is to read the .db file and generate excel file in Windows 10 where no MS office installed. Thanks so much
[https://github.com/karenpayneoregon/ExcelUnleashed/blob/master/ExcelOperations/OleDbWork/Operations.cs][1]
The below method generates excel file from data table with filter in the first row of the file:

 public void ExportSats(string dbpath)  
    {  
        ExcelUtlity obj = new ExcelUtlity();  
     System.Data.DataTable dt = GetTable2(dbpath);  
     string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + "Data" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";  
     obj.WriteDataTableToExcel(dt, "Stats", path, "Details");  
     System.Windows.MessageBox.Show("Excel created/saved on the desktop.xlsx");  

        
    }  
    ////https://www.sqlite.org/lang_datefunc.html  
    public System.Data.DataTable GetTable2(string dbpath)  
    {  
     //SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + dbpath);  

      
     using (SQLiteConnection m_dbConnection = new SQLiteConnection(dbpath))  
     {  
     m_dbConnection.Open();  

      
     string query1 = "select Oid,(Select strftime('%d/%m/%Y',CreatedOn)) as Date,(Select strftime('%H:%M',CreatedOn) )AS Time," from Stats";  

      
     SQLiteCommand sqlCom = new SQLiteCommand(query1.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["A1"].Offset[0, Idx].Value = dataTable.Columns[Idx].ColumnName;  
     //row header styles  
     excelSheet.Range["A1"].Offset[0, Idx].Font.Size = 14;  
     excelSheet.Range["A1"].Offset[0, Idx].Font.Name = "Arial";  
     excelSheet.Range["A1"].Offset[0, Idx].Font.FontStyle = "Bold";  
     excelSheet.Range["A1"].Offset[0, Idx].Font.Color = ColorTranslator.ToOle(System.Drawing.Color.White);  
     excelSheet.Range["A1"].Offset[0, Idx].Interior.Color = ColorTranslator.ToOle(System.Drawing.Color.Gray);  

      

      
     }  
     for (int Idx = 0; Idx < dataTable.Rows.Count; Idx++)  
     {  
     excelSheet.Range["A2"].Offset[Idx].Resize[1, dataTable.Columns.Count].Value =  
     dataTable.Rows[Idx].ItemArray;  
     }  

          
     excelSheet.Activate();  
     excelSheet.Application.ActiveWindow.FreezePanes = true;  
     Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)excelSheet.Rows[1];  

      
     firstRow.AutoFilter(1,  
     Type.Missing,  
     Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,  
     Type.Missing,  
     true);  

      
     excelSheet.Columns.AutoFit();  

      
     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

@Kran2022 , Welcome to Microsoft Q&A, based on my search, I find that another method to use DocumentFormat.OpenXml to generate the excel file based on the Datatable.

Please install the nuget-package DocumentFormat.OpenXml first of all and you could try the following code.

code.txt

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

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.