导出到 Excel

Question

我正在使用 C# 导出到 Excel，但在某些记录上出现此错误。

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data

通过谷歌搜索，我发现它与大小限制有关，但我找不到解决方法。有什么想法吗？

请求代码：

string lFilename = Leads.xls";
string lDistributorFolder = Server.MapPath(".") + "\\Portals\\0\\Distributors\\" + _currentUser.UserID.ToString() + "\\";
string lTemplateFolder = System.Configuration.ConfigurationManager.AppSettings["Templates"];
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 (GridDataItem lItem in grdLeadList.Items)
  {
    lSequence++;

    using (DbCommand lCommand = lConnection.CreateCommand())
      {
           lCommand.CommandText = "INSERT INTO [ColderLeads$] ";
           lCommand.CommandText += "(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21) ";
           lCommand.CommandText += "VALUES(";
           lCommand.CommandText += "\"" + lSequence.ToString() + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gLeadNumber].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gSource].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gAccountName].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gCreatedOn].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gContactFullName].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gPriority].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gStreet1].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gStreet2].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gZIP].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gCity].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += "\"" + lItem.Cells[_gState].Text.Replace(" ", " ") + "\",";
           lCommand.CommandText += ")";
           lCommand.ExecuteNonQuery();
         }
      }

     lConnection.Close();
  }

谢谢！

Answer 1

您使用的是 Excel 2003 吗？如果是这样，则 Excel 工作表的大小存在限制。它有 65,536 行 x 256 列。请参阅以下链接更多信息。

列宽也有限制。这是 255 个字符。在将 C# 导出到 Excel 之前，尝试修剪 C# 中的所有字段。您还可以做的是，当工作簿达到最大行长度时，创建另一个工作表。