如何使用Oledb Query将大字符串（10000个字符串）插入Excel工作表 [英] How to Insert Large String(10000 Character String) into excel Sheet using Oledb Query

问题描述

嗨朋友，



我创建了web应用程序，在这个应用程序中有一部分插入大字符串（字符串超过10000个字符）的值为excel使用oledb查询表。

i已经尝试了以下代码但我得到了

该字段太小而无法接受您尝试添加的数据量。请尝试插入或粘贴较少的数据此错误。

  string  con =  @  Provider = Microsoft.Jet.OLEDB.4.0; Data Source = E：\Mohan.G\WorkTimeSheet\Test .xls;扩展属性= Excel 8.0;; 
 使用（OleDbConnection connection =  new  OleDbConnection（con））
 {
 connection.Open（）; 
 <跨度类= 代码关键字>串 STR = <跨度类= 代码串>  <跨度类= 代码串> bsdhjbfdggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggdfgdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddshjfbdhjsfsbjfsbfhjsfbjhbfjhbfjhsbhfshjfjfbjhfjshfjhsfjshfjdhsfjsjfdsjfjjglfgjlkjgoig / dfdfgflkdglkfgfjlkgkjldfglklfggjoitglklkfjdgoidjgldmgldgogloieewrlkrj / k.asuhdidkahdsdhisuahdiuhsikudhiusadhiusdhishdisiduhidhsihahudihiuhisahdiuhiai; 
  string  sqlStquery =  插入[Sheet1] $]（[ID]，[Name]）值（' +  0  +  '，' + str +  '） ; 
 
 OleDbDataAdapter dbadapter =  new  OleDbDataAdapter（sqlStquery，connection）; 
 dbadapter.SelectCommand.ExecuteNonQuery（）; 
 dbadapter.Dispose（）; 
 connection.Close（）; 
 connection.Dispose（）; 
} 

所以任何人都可以建议我如何使用Oledb Query在Excel表格中插入超过10000个字符的字符串值。





在此先感谢，

Mohan G

解决方案

（[ID]，[Name]）值（' + 0 + '，' + str + '）;

OleDbDataAdapter dbadapter = new OleDbDataAdapter（sqlStquery，connection）;
dbadapter.SelectCommand.ExecuteNonQuery（）;
dbadapter.Dispose（）;
连接.Close（）;
connection.Dispose（）;
}

所以任何人都可以告诉我如何使用Oledb Query将超过10000个字符串的值插入到Excel工作表中。





先谢谢，

Mohan G

使用此库而不是可能大小受限的OLEDB： http：// epplus.codeplex.com/ [ ^ ]

< br>

有已知的限制......如何绕过它？请参阅：

Excel无法插入更多内容超过255个字符？ [ ^ ]

Excel单元格值被OLEDB提供程序截断 [ ^ ]



如需了解更多信息，请参阅：

< a href =http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx> Excel规范和限制 [ ^ ]

ADO提供商属性和设置 [ ^ ]

使用MS Excel（xls / xlsx）使用MDAC和Oledb [ ^ ]

如何使用ADO.NET检索和使用Visual Basic .NET修改Excel工作簿中的记录 [ ^

Hi Friends,

I created web application, in this application there is a part of insert Large string (String having more than 10000 characters) values in to excel sheet using oledb query.
i have tried the below code but i am getting
"The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data" this error.

string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Mohan.G\WorkTimeSheet\Test.xls;Extended Properties=Excel 8.0;";
            using (OleDbConnection connection = new OleDbConnection(con))
            {
                connection.Open();
                        string str ="bsdhjbfdggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggdfgdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddshjfbdhjsfsbjfsbfhjsfbjhbfjhbfjhsbhfshjfjfbjhfjshfjhsfjshfjdhsfjsjfdsjfjjglfgjlkjgoig/dfdfgflkdglkfgfjlkgkjldfglklfggjoitglklkfjdgoidjgldmgldgogloieewrlkrj/k.asuhdidkahdsdhisuahdiuhsikudhiusadhiusdhishdisiduhidhsihahudihiuhisahdiuhiai";
                string sqlStquery = "Insert into [Sheet1$] ([ID],[Name]) Values ('" + 0 + "','" + str + "')";

                OleDbDataAdapter dbadapter = new OleDbDataAdapter(sqlStquery, connection);
                dbadapter.SelectCommand.ExecuteNonQuery();
                dbadapter.Dispose();
                connection.Close();
                connection.Dispose();
            }

so can any one suggest me how to insert more than 10000 character string value into Excel sheet using Oledb Query.


Thanks in Advance,
Mohan G

解决方案

([ID],[Name]) Values ('" + 0 + "','" + str + "')"; OleDbDataAdapter dbadapter = new OleDbDataAdapter(sqlStquery, connection); dbadapter.SelectCommand.ExecuteNonQuery(); dbadapter.Dispose(); connection.Close(); connection.Dispose(); }

so can any one suggest me how to insert more than 10000 character string value into Excel sheet using Oledb Query.


Thanks in Advance,
Mohan G

Use this library instead of OLEDB which might be size limited : http://epplus.codeplex.com/[^]

There are known limitations... How to go around it? Please see:
Excel unable to insert more than 255 chars?[^]
Excel cell-values are truncated by OLEDB-provider[^]

For further information, please see:
Excel specifications and limits[^]
ADO Provider Properties and Settings[^]
Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^]
How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET[^]

这篇关于如何使用Oledb Query将大字符串（10000个字符串）插入Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！