4

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?

1

1 Answer 1

3

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.