Can I import INTO excel from a data source without iteration?

Question

Currently I have an application that takes information from a SQLite database and puts it to Excel. However, I'm having to take each DataRow, iterate through each item, and put each value into it's own cell and determine highlighting. What this is causing is 20 minutes to export a 9000 record file into Excel. I'm sure it can be done quicker than that. My thoughts are that I could use a data source to fill the Excel Range and then use the column headers and row numbers to format only those rows that need to be formatted. However, when I look online, no matter what I seem to type, it always shows examples of using Excel as a database, nothing about importing into excel. Unless I'm forgetting a key word or to. Now, this function has to be done in code as it's part of a bigger application. Otherwise I would just have Excel connect to the DB and pull the information itself. Unfortunately that's not the case. Any information that could assist me in quick loading an excel sheet would be appreciated. Thanks.

Additional Information:
Another reason why the pulling of the information from the DB has to be done in code is that not every computer this is loaded on will have Excel on it. The person using the application may simply be told to export the data and email it to their supervisor. The setup app includes the needed dlls for the application to make the proper format.

Example Code (Current):

    For Each strTemp In strColumns
        excelRange = worksheet.Cells(1, nCounter)
        excelRange.Select()
        excelRange.Value2 = strTemp
        excelRange.Interior.Color = System.Drawing.Color.Gray.ToArgb()
        excelRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing)
        nCounter += 1
    Next

Now, this is only example code in terms of the iteration I'm doing. Where I'm really processing the information from the database I'm iterating through a dataTable's Rows, then iterating through the items in the dataRow and doing essentially the same as above; value by value, selecting the range and putting the value in the cell, formatting the cell if it's part of a report (not always gray), and moving onto the next set of data. What I'd like to do is put all of the data in the excel sheet (A2:??, not a row, but multiple rows) then iterate through the reports and format each row then. That way, the only time I iterate through all of the records is when every record is part of a report.

Ideal Code

excelRange = worksheet.Cells("A2", "P9000")
excelRange.DataSource = ds 'ds would be a queried dataSet, and I know there is no excelRange.DataSource.
'Iteration code to format cells

Update:

I know my examples were in VB, but it's because I was also trying to write a VB version of the application since my boss prefers VB. However, here's my final code using a Recordset. The ConvertToRecordset function was obtained from here.

        private void CreatePartSheet(Excel.Worksheet excelWorksheet)
        {
            _dataFactory.RevertDatabase();
            excelWorksheet.Name = "Part Sheet";
            string[] strColumns = Constants.strExcelPartHeaders;
            CreateSheetHeader(excelWorksheet, strColumns);

            System.Drawing.Color clrPink = System.Drawing.Color.FromArgb(203, 192, 255);
            System.Drawing.Color clrGreen = System.Drawing.Color.FromArgb(100, 225, 137);

            string[] strValuesAndTitles = {/*...Column Names...*/};

            List<string> lstColumns = strValuesAndTitles.ToList<string>();

            System.Data.DataSet ds = _dataFactory.GetDataSet(Queries.strExport);
            ADODB.Recordset rs = ConvertToRecordset(ds.Tables[0]);
            excelRange = excelWorksheet.get_Range("A2", "ZZ" + rs.RecordCount.ToString());
            excelRange.Cells.CopyFromRecordset(rs, rs.RecordCount, rs.Fields.Count);
            int nFieldCount = rs.Fields.Count;

            for (int nCounter = 0; nCounter < rs.RecordCount; nCounter++)
            {
                int nRowCounter = nCounter + 2;
                List<ReportRecord> rrPartReports = _lstReports.FindAll(rr => rr.PartID == nCounter).ToList<ReportRecord>();
                excelRange = (Excel.Range)excelWorksheet.get_Range("A" + nRowCounter.ToString(), "K" + nRowCounter.ToString());
                excelRange.Select();
                excelRange.NumberFormat = "@";

                if (rrPartReports.Count > 0)
                {
                    excelRange.Interior.Color = System.Drawing.Color.FromArgb(230, 216, 173).ToArgb(); //Light Blue

                    foreach (ReportRecord rr in rrPartReports)
                    {
                        if (lstColumns.Contains(rr.Title))
                        {
                            excelRange = (Excel.Range)excelWorksheet.Cells[nRowCounter, lstColumns.IndexOf(rr.Title) + 1];
                            excelRange.Interior.Color = rr.Description.ToUpper().Contains("TAG") ? clrGreen.ToArgb() : clrPink.ToArgb();

                            if (rr.Description.ToUpper().Contains("TAG"))
                            {
                                rs.Find("PART_ID=" + (nCounter + 1).ToString(), 0, ADODB.SearchDirectionEnum.adSearchForward, "");
                                excelRange.AddComment(Environment.UserName + ":  " + _dataFactory.GetTaggedPartPrevValue(rs.Fields["POSITION"].Value.ToString(), rr.Title));
                            }
                        }
                    }
                }

                if (nRowCounter++ % 500 == 0)
                {
                    progress.ProgressComplete = ((double)nRowCounter / (double)rs.RecordCount) * (double)100;
                    Notify();
                }
            }

            rs.Close();

            excelWorksheet.Columns.AutoFit();
            progress.Message = "Done Exporting to Excel";
            Notify();
            _dataFactory.RestoreDatabase();
        }

Answer 1

Try this out:

http://office.microsoft.com/en-au/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx

Answer 2

I'd normally recommend using Excel to pull in the data from SQLite. Use Excel's "Other Data Sources". You could then choose your OLE DB provider, use a connection string, what-have-you.

It sounds, however, that the real value of your code is the formatting of the cells, rather than the transfer of data.

Perhaps refactor the process to:

• have Excel import the data
• use your code to open the Excel spreadsheet, and apply formatting

I'm not sure if that is an appropriate set of processes for you, but perhaps something to consider?

Answer 3

Perhaps post some code, and we might be able to track down any issues.

I'd consider this chain of events:

• query the SQLite database for your dataset.
• move the data out of ADO.NET objects, and into POCO objects. Stop using DataTables/Rows.
• use For Each to insert into Excel.

Answer 4

I have used the Excel XML file format in the past to write directly to an output file or stream. It may not be appropriate for your application, but writing XML is much faster and bypasses the overhead of interacting with the Excel Application. Check out this Introduction to Excel XML post.

Update: There are also a number of libraries (free and commercial) which can make creating excel document easier for example excellibrary which doesn't support the new format yet. There are others mentioned in the answers to Create Excel (.XLS and .XLSX) file from C#

Answer 5

Can you use ODBC?

''http://www.ch-werner.de/sqliteodbc/

dbName = "c:\docs\test"
scn = "DRIVER=SQLite3 ODBC Driver;Database=" & dbName _
& ";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"

Set cn = CreateObject("ADODB.Connection")
cn.Open scn

Set rs = CreateObject("ADODB.Recordset")
rs.Open "select * from test", cn

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

BTW, Excel is quite happy with HTML and internal style sheets.

Answer 6

Excel has the facility to write all the data from a ADO or DAO recordset in a single operation using the CopyFromRecordset method.

Code snippet:

    Sheets("Sheet1").Range("A1").CopyFromRecordset rst