C#导出Excel,某单元格内容长度超过255,就不行了?该如何处理
C#导出Excel,某单元格内容长度超过255,就不行了?
測試數據長度是256(臨界值)
下載Excel的代碼如下:
如果單元格的數據長度小於256,沒有問題。
如果大於等於256,就不能導出Excel
錯誤信息是:
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
------解决方案--------------------
nvarchar(100)
中的100加大试试,比如1000
------解决方案--------------------
这个好像 没有什么办法,只能限制字符。
顶。
------解决方案--------------------
excel就是这样,最大列个数不能超过255个
如果超过,截取前255个,分批导出。
- SQL code
CREATE TABLE [dbo].[T_Simple]( [Simple] [varchar](500) NULL ) insert [T_Simple](simple) values('cdefghijklmnopqrstuvwxyzabcdefghijklcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrsghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz1')
測試數據長度是256(臨界值)
下載Excel的代碼如下:
- C# code
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; /// <summary> /// WriteExcel 的摘要描述 /// </summary> public class WriteExcel { public static void ToExcel(DataTable dtSource, string strPath, string strSheetName) { strPath = @"C:\temp\BooklistInfo.xls"; System.Data.OleDb.OleDbConnection OleDb_Conn = new System.Data.OleDb.OleDbConnection(); OleDb_Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No';" + "Data Source=\"" + strPath + "\""; try { OleDb_Conn.Open(); System.Data.OleDb.OleDbCommand OleDb_Comm = new System.Data.OleDb.OleDbCommand(); OleDb_Comm.Connection = OleDb_Conn; string strCmd; try { strCmd = "drop table [" + strSheetName + "]"; OleDb_Comm.CommandText = strCmd; OleDb_Comm.ExecuteNonQuery(); } catch { } strCmd = "create Table [" + strSheetName + "]("; foreach (DataColumn dc in dtSource.Columns) { strCmd += "[" + dc.ColumnName + "] nvarchar(100),"; } strCmd = strCmd.Trim().Substring(0, strCmd.Length - 1); strCmd += ")"; OleDb_Comm.CommandText = strCmd; OleDb_Comm.ExecuteNonQuery(); foreach (DataRow dr in dtSource.Rows) { if (dr.RowState != System.Data.DataRowState.Deleted) { strCmd = "insert into [" + strSheetName + "] values("; foreach (DataColumn dc in dtSource.Columns) { strCmd += "'" + dr[dc.ColumnName].ToString().Trim().Replace("'","") + "',"; } strCmd = strCmd.Substring(0, strCmd.Length - 1); strCmd += ")"; OleDb_Comm.CommandText = strCmd; OleDb_Comm.ExecuteNonQuery(); } } OleDb_Conn.Close(); } catch (Exception ex) { throw ex; } finally { OleDb_Conn.Close(); } }
如果單元格的數據長度小於256,沒有問題。
如果大於等於256,就不能導出Excel
錯誤信息是:
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
------解决方案--------------------
nvarchar(100)
中的100加大试试,比如1000
------解决方案--------------------
这个好像 没有什么办法,只能限制字符。
顶。
------解决方案--------------------
excel就是这样,最大列个数不能超过255个
如果超过,截取前255个,分批导出。