export .db table to excel with header/column and values-CodePudding

Hi All: Uisng SQLiteDataReader i'm getting the table values but i'm not getting the column value using SQLiteDataReader GetName

My goal is to i want data with column name in excel file which is same as query then followed by the table values. wWhat is the mistake here? thanks

I think i'm making mistake in the for loop?

    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = xlWorkBook.Worksheets[1]; // (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    xlWorkSheet.Name = "Stats";

    string cs = @"C:\\db_test\\Database.db";
    string data = String.Empty;

    int i = 0;
    int j = 0;

    using (SQLiteConnection con = new SQLiteConnection("Data Source=C:\\db_test\\Database.db"))
    {
        con.Open();

        //string stm = "SELECT Oid from Stats";
        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");

        using (SQLiteCommand cmd = new SQLiteCommand(query.ToString(), con))
        {
            using (SQLiteDataReader rdr = cmd.ExecuteReader())
            {
            
                while (rdr.Read()) // Reading Rows
                {
                    var columns = new List<string>();
                    

                    for (j = 0; j <= rdr.FieldCount - 1; j  ) // Looping throw colums
                    {
                        columns.Add(rdr.GetName(j));
                        data = rdr.GetValue(j).ToString();
                        xlWorkSheet.Cells[i   1, j   1] = data;

                    }
                    i  ;
                }
            }
        }
        con.Close();
    }
    string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)  "\\"  "Statictics_"   DateTime.Now.ToString("yyyyMMdd")   ".xls";
    
    xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);

CodePudding user response：

It looks like you likely got the solution you needed from the comments on your post, but I'll offer another. I maintain a library, Sylvan.Data.Excel that will be easier and much faster than Excel interop. The library is open source and has a permissive license (MIT).

Writing to an .xlsx file only takes a couple lines of code.

SQLiteDataReader rdr = GetData(); // get your data reader from Sqlite

using var writer = ExcelDataWriter.Create("mydata.xlsx");
writer.Write(rdr, "Stats");

While my library supports reading most common Excel formats, it only supports writing .xlsx files. It is the fastest Excel library for .NET. For comparison, using Excel interop (the code your provided above) takes ~15 minutes to write the 65k records that Sylvan can write in less than half a second. My library also doesn't require that Excel be installed on the machine, its only dependency is the core .NET runtime libraries.