C# WPF - Copy SQLite Database to Excel

Question

I am currently running a C# project, which processes a lot of data and stores it in a SQLite database. However I would like to export these files from the database to a simple excel sheet, for example create some file, where each table in the database is a sheet in excel, just a plain copy.

At the moment I am doing the same thing with a .csv streamwriter and it is very slow because i have an amount of like 140000 datasets. This means it would require to copy the table as a whole or do it blockwise.

I did not find any code snippets how to do this in c# with sqlite. Do you have any ideas, how I could do this?

Answer 1

I never did this in SQLite and i also think output directly is always better, but i'm curious about this.

So i have written this try-out:

using System;
using System.Data.SQLite;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelSqlite
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            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 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            string cs = "URI=file:test.db";
            string data = String.Empty;

            int i = 0;
            int j = 0;

            using (SQLiteConnection con = new SQLiteConnection(cs))
            {
                con.Open();

                string stm = "SELECT * FROM Contacts";

                using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read()) // Reading Rows
                        {
                            for (j = 0; j <= rdr.FieldCount - 1; j++) // Looping throw colums
                            {
                                data = rdr.GetValue(j).ToString();
                                xlWorkSheet.Cells[i + 1, j + 1] = data;
                            }
                            i++;
                        }
                    }
                }
                con.Close();
            }

            xlWorkBook.SaveAs("sqliteToExcel.xls", 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);
        }

        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

Hope so, this will lead you to the right direction.