是否可以不重复地从数据源导入INTO excel? [英] Can I import INTO excel from a data source without iteration?

问题描述

当前，我有一个应用程序，该应用程序从SQLite数据库获取信息并将其放入Excel.但是，我必须获取每个DataRow，遍历每个项目，并将每个值放入其自己的单元格并确定突出显示.这是导致20分钟内将9000个记录文件导出到Excel中的原因.我敢肯定它可以比那更快地完成.我的想法是，我可以使用数据源填充Excel范围，然后使用列标题和行号仅格式化那些需要格式化的行.但是，当我联机查看时，无论输入什么内容，它始终显示使用Excel作为数据库的示例，与导入excel无关.除非我忘记了关键词或.现在，此功能必须在代码中完成，因为它是更大应用程序的一部分.否则，我只能将Excel连接到数据库并提取信息本身.不幸的是事实并非如此.任何可以帮助我快速加载excel工作表的信息将不胜感激.谢谢.

附加信息:
之所以必须以代码形式从数据库中提取信息的另一个原因是，并不是每台装载此计算机的计算机都具有Excel在上面.使用该应用程序的人可能只是被告知要导出数据并将其通过电子邮件发送给其主管.设置应用程序包括应用程序所需的dll，以使应用程序生成正确的格式.

示例代码(当前):

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

现在，就我正在执行的迭代而言，这只是示例代码.在真正处理数据库信息的地方，我遍历了dataTable的Rows，然后遍历了dataRow中的项，并且基本上与上述操作相同；逐个值地选择值，选择范围并将其放在单元格中，如果该单元格是报表的一部分(不总是灰色)，则将该单元格格式化，然后移至下一组数据.我想做的是将所有数据放入excel工作表(A2:??，不是一行，而是多行)，然后遍历报告并格式化每一行.这样，我遍历所有记录的唯一时间就是每条记录都属于报表的一部分.

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.

理想代码

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

更新:

我知道我的示例在VB中，但这是因为我也试图编写VB版本的应用程序，因为我的老板更喜欢VB.但是，这是我使用Recordset的最终代码.可以从此处获得.

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

推荐答案

可以使用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

顺便说一句，Excel对HTML和内部样式表非常满意.

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

这篇关于是否可以不重复地从数据源导入INTO excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！