C#导出Excel,某单元格内容长度超过255,就不行了?该如何处理

C#导出Excel,某单元格内容长度超过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个,分批导出。