当前位置: 代码迷 >> ASP.NET >> C#导出Excel,某单元格内容长度超过255,就不行了?该如何处理
  详细解决方案

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

热度:8304   发布时间:2013-02-25 00:00:00.0
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个,分批导出。
  相关解决方案