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

Question

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);

You're storing the column names into columns but you're not using them anymore. You're just writing data -which only contains the values- to your Excel file. — ɐsɹǝʌ ǝɔıʌ
– ɐsɹǝʌ ǝɔıʌ, Commented Dec 28, 2022 at 15:18
@ɐsɹǝʌǝɔıʌ : thanks for poinging the mistake, How can write the 'GetName' into to the file? thanks — user838691
– user838691, Commented Dec 28, 2022 at 15:25

MarkPflug · Accepted Answer · 2022-12-28 20:39:30Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related