将数据写入Excel

Question

我正在将数据从ASP.NET应用程序中的DataView写入到Excel文档中。在第一行我有标题，第二行被跳过，在第三行数据开始。有没有可能强制它从第二行开始数据？这就是我正在使用的。

    string lFilename = "Data.xls";
    string lDistributorFolder = Server.MapPath(".") + "\\Portals\\0\\Distributors\\" + _currentUser.UserID.ToString() + "\\";
    string lTemplateFolder = System.Configuration.ConfigurationManager.AppSettings["CPCeCommerceTemplates"];
    System.IO.Directory.CreateDirectory(lDistributorFolder);

        File.Copy(lTemplateFolder + lFilename, lDistributorFolder + lFilename, true);
        string lConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lDistributorFolder + "\\" + lFilename + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
        DbProviderFactory lFactory = DbProviderFactories.GetFactory("System.Data.OleDb");
        int lSequence = 0;


        using (DbConnection lConnection = lFactory.CreateConnection())
        {
            lConnection.ConnectionString = lConnectionString;
            lConnection.Open();

            foreach (DataRowView rowView in dv)
            {
                DataRow row = rowView.Row;

                lSequence++;

                using (DbCommand lCommand = lConnection.CreateCommand())
                {
                    lCommand.CommandText = "INSERT INTO [Sheet1$] ";
                    lCommand.CommandText += "([First Name],[Last Name],[Title],[Company],[Address],[Address 2],[City],[State],[Zip],[Country],[Work phone],[Email],[Website],[Stamp Time],[Campaign],[Source],[Business Unit],[Market Segment],[Notes],[Other Source Detail],[Description],[Sales Employee firstname],[Sales Employee last name],[Reason],[Status],[Category],[Priority]) ";
                    lCommand.CommandText += "VALUES(";
                    lCommand.CommandText += "\"" + row["name"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["lastname"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["title"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["company"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["address"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["address2"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["city"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["state"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["zip"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["country"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["workphone"].ToString() + "\",";
                    lCommand.CommandText += "\"" + row["email"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["website"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["stamptime"].ToString() + "\",";
                    lCommand.CommandText += "\"" + row["campaign"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["source"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + string.Empty + "\",";
                    lCommand.CommandText += "\"" + row["market"].ToString() + "\",";
                    lCommand.CommandText += "\"" + row["qc"].ToString().Replace("\"", "\"\"").Replace(" ", " ") + "\",";
                    lCommand.CommandText += "\"" + row["othersourcedetail"].ToString() + "\",";
                    lCommand.CommandText += "\"" + string.Empty + "\",";
                    lCommand.CommandText += "\"" + string.Empty + "\",";
                    lCommand.CommandText += "\"" + string.Empty + "\",";
                    lCommand.CommandText += "\"" + "Lead" + "\",";
                    lCommand.CommandText += "\"" + "Open" + "\",";
                    lCommand.CommandText += "\"" + "Lead" + "\",";
                    lCommand.CommandText += "\"" + "High" + "\"";
                    lCommand.CommandText += ")";
                    lCommand.ExecuteNonQuery();
                }
            }

            lConnection.Close();
        }

谢谢!

Answer 1

只是在谷歌上搜索了一下，因为它很有趣，下面这篇文章是在相关领域：

Inserting a row into Excel Spreadsheet via C# and OleDb

吸引我眼球的是他的insert语句：

INSERT INTO [{0}${1}:{1}] Values({2})

这似乎意味着您可以在insert语句中的sheet.named范围名称后添加行/列位置。

也许值得一试。