C#导出Excel，某单元格内容长度超过255，就不行了？

吕津 2012-06-18 07:31:33 


CREATE TABLE [dbo].[T_Simple](

	[Simple] [varchar](500) NULL

)



insert [T_Simple](simple)

values('cdefghijklmnopqrstuvwxyzabcdefghijklcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrsghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz1')


測試數據長度是256（臨界值）


下載Excel的代碼如下：



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.

...全文
2615 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
吕津 2014-06-03
  • 打赏
  • 举报
 回复 


public class ExcelIO

    {

        public static void WriteToExcel(DataTable dtSource, string downloadPath, string modelPath, string downloadFolder)

        {

            

            try

            {//每次导出之前，先行清理下临时文件夹

                string[] files = System.IO.Directory.GetFiles(downloadFolder);

                foreach (string file in files)

                {

                    File.Delete(file);

                }



                System.IO.File.Copy(modelPath, downloadPath, true);



            }

            catch (Exception ex)

            {//如果临时文件夹中的文件有进程在访问，什么也不做

            }

            System.Data.OleDb.OleDbConnection OleDb_Conn = new OleDbConnection();

            string ExcelConn = " Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;IMEX=2';Data Source='%ExcelPath%' ";

            string strOleConn = ExcelConn;

            strOleConn = strOleConn.Replace("%ExcelPath%", downloadPath);

            OleDb_Conn.ConnectionString = strOleConn;

            try

            {

                if (OleDb_Conn.State == ConnectionState.Closed)

                    OleDb_Conn.Open();



                OleDbCommand OleDb_Comm = new OleDbCommand();

                OleDb_Comm.Connection = OleDb_Conn;

                string strSheetName = "Sheet1$";                

                string strCmd = "";



                try

                {//为了兼容格式比较长的单元格（比如超过255字段），需要先行删除已有字段

                    strCmd = "drop table [" + strSheetName + "]";

                    OleDb_Comm.CommandText = strCmd;

                    OleDb_Comm.ExecuteNonQuery();

                }

                catch

                {



                }



                try

                {//然后创建新的字段，所有字段都设定为memo类型

                    strCmd = "create Table [" + strSheetName + "](";



                    foreach (DataColumn dc in dtSource.Columns)

                    {

                        strCmd += "[" + dc.ColumnName + "] memo,";

                    }



                    strCmd = strCmd.Trim().Substring(0, strCmd.Length - 1);

                    strCmd += ")";

                    OleDb_Comm.CommandText = strCmd;

                    OleDb_Comm.ExecuteNonQuery();

                }

                catch

                {

                	

                }               



                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("'", "\"").Replace("\r\n", " ") + "',";

                        }

                        strCmd = strCmd.Substring(0, strCmd.Length - 1);

                        strCmd += ")";

                        OleDb_Comm.CommandText = strCmd;                        

                        OleDb_Comm.ExecuteNonQuery();

                    }

                }

                

            }

            catch (Exception ex)

            {

                throw ex;

            }

            finally

            {

                if (OleDb_Conn.State == ConnectionState.Open)

                    OleDb_Conn.Close();

            }

        }



        public static void DownloadFile(System.Web.UI.Page page, string strFile)

        {

            page.Response.Clear();

            page.Response.ContentType = "application/octet-stream";

            page.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(System.IO.Path.GetFileName(strFile).Trim()) + "\"");

            page.Response.Flush();

            page.Response.WriteFile(strFile);

            page.Response.End();

        }

    }






 protected void butToExcel_Click(object sender, EventArgs e)

        {

            List<IWantToBuy> list = _presenter.GetIWantToBuyList_Buy(ExcelMaxRow);

            //准备数据源DataTable

            DataTable dt = DB_List.ToDataTable(list);

            //为了保证并发导出数据时的冲突，文件名称精确到毫秒

            string timestamp = DateTime.Now.ToString("yyyyMMddhhmmssfff");

            string downloadPath = System.Web.HttpContext.Current.Server.MapPath("~/Templates/ExportExcel/Download/Admin-Buy-Pending" + timestamp + ".xls");

            string modelPath = System.Web.HttpContext.Current.Server.MapPath("~/Templates/ExportExcel/Model/Admin-Buy-Pending.xls");

            string downloadFolder = System.Web.HttpContext.Current.Server.MapPath("~/Templates/ExportExcel/Download/");

            ExcelIO.WriteToExcel(dt, downloadPath, modelPath, downloadFolder);

            ExcelIO.DownloadFile(this.Page, downloadPath);           

        }

请务必注意：
（1）请事先准备Excel文件作为模板。
（2）请在目标工作表（比如Sheet1）中准备好对应的字段名。

导出的样例如下：


网络上提供的方法五法八门，很多让人郁闷，所以我这个方法差不多可以解决一些通用的需求。
我的博文地址：
http://blog.csdn.net/lvjin110/article/details/27366017
吕津 2014-05-29
  • 打赏
  • 举报
 回复 

protected void butToExcel_Click(object sender, EventArgs e)
        {
            List<IWantToBuy> list = _presenter.GetIWantToBuyList_Buy(ExcelMaxRow);
            DataTable dt = DB_List.ToDataTable(list);
            string downloadPath = System.Web.HttpContext.Current.Server.MapPath("~/Templates/ExportExcel/Download/Admin-Buy-Pending.xls");
            string modelPath = System.Web.HttpContext.Current.Server.MapPath("~/Templates/ExportExcel/Model/Admin-Buy-Pending.xls");
            ExcelIO.WriteToExcel(dt,downloadPath,modelPath);
            ExcelIO.DownloadFile(this.Page, downloadPath);           
        }
吕津 2014-05-29
  • 打赏
  • 举报
 回复 

public static void WriteToExcel(DataTable dtSource, string downloadPath, string modelPath)
        {
            try
            {
                if (System.IO.File.Exists(downloadPath))
                    System.IO.File.Delete(downloadPath);
                System.IO.File.Copy(modelPath, downloadPath, true);

            }
            catch (Exception ex)
            {
                throw ex;
                return;
            }
            System.Data.OleDb.OleDbConnection OleDb_Conn = new OleDbConnection();
            string ExcelConn = " Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;IMEX=2';Data Source='%ExcelPath%' ";
            string strOleConn = ExcelConn;
            strOleConn = strOleConn.Replace("%ExcelPath%", downloadPath);
            OleDb_Conn.ConnectionString = strOleConn;
            try
            {
                if (OleDb_Conn.State == ConnectionState.Closed)
                    OleDb_Conn.Open();
                OleDbCommand OleDb_Comm = new OleDbCommand();
                OleDb_Comm.Connection = OleDb_Conn;
                string strSheetName = "Sheet1$";
                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 + "] memo,";
                }

                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("'", "\"").Replace("\r\n", " ") + "',";
                        }
                        strCmd = strCmd.Substring(0, strCmd.Length - 1);
                        strCmd += ")";
                        OleDb_Comm.CommandText = strCmd;
                        OleDb_Comm.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (OleDb_Conn.State == ConnectionState.Open)
                    OleDb_Conn.Close();
            }
        }


public static void DownloadFile(System.Web.UI.Page page, string strFile)
        {
            page.Response.Clear();
            page.Response.ContentType = "application/octet-stream";
            page.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(System.IO.Path.GetFileName(strFile).Trim()) + "\"");
            page.Response.Flush();
            page.Response.WriteFile(strFile);
            page.Response.End();
        }
yuan31415 2013-06-24
  • 打赏
  • 举报
 回复
楼主，我怎么试了你的方法不行吖，是不是还有其他地方要修改呢
levine2008 2013-05-27
  • 打赏
  • 举报
 回复
不行的，兄弟
段传涛 2012-06-20
  • 打赏
  • 举报
 回复
是吗 ？ 我试试
同时祝贺。
吕津 2012-06-20
  • 打赏
  • 举报
 回复
strCmd += "[" + dc.ColumnName + "] memo,";
吕津 2012-06-20
  • 打赏
  • 举报
 回复
問題解決！

只需要將該列首個單元格指定為memo類型就可以了！





public static void ToExcel(DataTable dtSource, string strPath, string strSheetName)

    {        

        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 + "] memo,";

            }            



            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();

        }

    }



http://flyspirit99.blogspot.com/2007/07/export-more-than-255-characters-into.html

------------------------------------------------------
Export more than 255 characters into Excel by OLEDB
I create a excel using ole db provider, like below

string connString = "Data Source=" + fullPath + "; Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=Excel 8.0;";

OleDbConnection myConn=new OleDbConnection(connString);
OleDbCommand myCommand=new OleDbCommand();
myCommand.CommandText = "CREATE TABLE Report (msisdn string, comment string )";

// connect an excel file, if this file doesn't exist, it will be created
myConn.Open();
// create a workbook in this file
myCommand.ExecuteNonQuery();

The problem is when import more than 255 characters into Excel, error occurs:

OLEDBException: The field is too small to accept the amount of data you attempted to add.....

This is because string in Excel has the limitation of 255 characters. To work around this, change the datatype to memo

myCommand.CommandText = "CREATE TABLE Report (msisdn string, comment memo)";


熙风 2012-06-19
  • 打赏
  • 举报
 回复
将Excel的字段改成"备注"字段或者合并单元格之后再导出试试。。。。
段传涛 2012-06-19
  • 打赏
  • 举报
 回复
如果导入的话
使用函数将长度超过255个字符的列切割为多个少于255的列，导入数据库后在进行合并。
或者修改注册表。
导出 ， 没有办法啊。看看孟子的解释。

孟子E章 2012-06-19
  • 打赏
  • 举报
 回复
官方的文档
http://office.microsoft.com/zh-cn/excel-help/excel-specifications-and-limits-HP005199291.aspx
段传涛 2012-06-19
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 的回复:]

引用 3 楼 的回复:
excel就是这样，最大列个数不能超过255个
如果超过，截取前255个，分批导出。


閣下說的沒有錯，不過大家好像沒有理解我描述的問題：
我的問題是一個單元格超過255個字符就不行了，不是255列。
[/Quote]
可能是他描述错了，
其实就是字符数，一个单元格的字符。
吕津 2012-06-19
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 的回复:]
excel就是这样，最大列个数不能超过255个
如果超过，截取前255个，分批导出。
[/Quote]

閣下說的沒有錯，不過大家好像沒有理解我描述的問題：
我的問題是一個單元格超過255個字符就不行了，不是255列。
暖枫无敌 2012-06-18
  • 打赏
  • 举报
 回复
excel就是这样，最大列个数不能超过255个
如果超过，截取前255个，分批导出。
段传涛 2012-06-18
  • 打赏
  • 举报
 回复
这个好像 没有什么办法，只能限制字符。
顶。
dandy99 2012-06-18
  • 打赏
  • 举报
 回复
nvarchar(100)
中的100加大试试，比如1000
利用NOPI到处Excel时，Cell内容自动换行
利用NOPI到处Excel时，Cell内容自动换行;
c#生成EXCEL下拉列表单元格（vs2005版）
c#生成EXCEL下拉列表单元格（vs2005版） EXCEL 互操作
C#基于NPOI生成具有精确列宽行高的Excel文件的方法
主要介绍了C#基于NPOI生成具有精确列宽行高的Excel文件的方法,是C#项目开发中非常实用的技巧,需要的朋友可以参考下
Excel模板导出（针对复杂模板情况）
Excel模板导出（针对复杂模板情况）
NPOI导入导出excel文档
NPOI导入导出excel，支持2003，2007
.NET社区

62,240

社区成员

669,008

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见，支持健康理性的辩论和互动，反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧

+ 用AI写文章