Export To Excel with Text more than 300 Characters not working

Question

I have field called "Comments" in my DataTable which has few records more than 255 characters.When we export the DataTable to Excel using the below code, the data is pushed into Excel but the Comments field record which has more than 255 characters overlaps other cells in Excel and the next column record is pushed to next row.

Code:

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=XXXXXX.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
foreach (DataRow dr in dt.Rows){
tab = "";
for (int i = 0; i < dt.Columns.Count; i++){
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();

Can you guys please help

In addition to my comment below, I'd point out that you don't appear to be creating an Excel document, but rather a tab-delimited text file. One should not expect a comma- or tab-delimited text document to work the same way as a "native" Excel document. (Put another way, because you're importing/converting a text document into Excel, there are probably additional restrictions at play.) — David, Aug 22 '12 at 16:13

score 1 · Answer 1 · answered Aug 21 '12 at 10:32

1

Because it's Excel's limits

Worksheet and workbook specifications and limits

Column width 255 characters

Reference: Excel specifications and limits

answered Aug 21 '12 at 10:32

fankt

1,007
1
7
16

Excel shows all the content in that cell but pushes the next cell values to another row . If 255 characters is limitation then it should not display all the content for that cell,but it does – Jagadeesh Aug 21 '12 at 10:40
2

I believe newer versions of Excel can handle more than 255 characters, but only when using the XLSX format (the XLS format is still restricted to 255 chars). I'm guessing you're running a newer version of Excel, which is why you're seeing what you are. I would be really surprised if you were able to do this: I've used a variety of tools to export information to XLS files, and I have *never* been able to export more than 255 characters. – David Aug 22 '12 at 16:06
@David I use `LONGTEXT` type when creating table, and can export more than 255 characters in `*.xls` files. Reference: [Data Type Support (OLE DB) | Microsoft Docs](https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms713678(v=vs.85)) – Jun Yu Jul 26 '21 at 05:22

score 0 · Accepted Answer · edited May 23 '17 at 11:49

Use EPPlus excel library and create real excel files, it's free and open source and works really well.

I tested this scenario and fill cell with text longer than 4096 bytes, and it works OK, but with excel 2010 and xslx format.

Here is the example code :

  ExcelPackage ePack = new ExcelPackage(new FileInfo("c:\\temp\\temp.xlsx"));
  ExcelWorksheet ws = ePack.Workbook.Worksheets.Add("Sheet1");
  string longText = "Lorem ipsum ..... <snipped>";
  ws.Cells[1, 1].Value = longText;
  ePack.Save();

btw. you can find here example how to export DataTable to excel file :

https://stackoverflow.com/a/9569827/351383

I am working with the EPlus. Will update you on the status . Thank You — Jagadeesh, Aug 23 '12 at 09:49

Naveen Desosha · Answer 3 · 2013-10-23T14:05:52.750

Try to use this code, may it ll help

    public static void DataSetsToExcel(DataSet dataSet, string filepath)
    {
        try
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";
            string tablename = "";
            DataTable dt = new DataTable();
            foreach (System.Data.DataTable dataTable in dataSet.Tables)
            {
                dt = dataTable;
                tablename = dataTable.TableName;
                using (OleDbConnection con = new OleDbConnection(connString))
                {
                    con.Open();
                    StringBuilder strSQL = new StringBuilder();
                    strSQL.Append("CREATE TABLE ").Append("[" + tablename + "]");
                    strSQL.Append("(");
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
                    }
                    strSQL = strSQL.Remove(strSQL.Length - 1, 1);
                    strSQL.Append(")");

                    OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
                    cmd.ExecuteNonQuery();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        strSQL.Clear();
                        StringBuilder strfield = new StringBuilder();
                        StringBuilder strvalue = new StringBuilder();
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            strfield.Append("[" + dt.Columns[j].ColumnName + "]");
                            strvalue.Append("'" + dt.Rows[i][j].ToString().Replace("'", "''") + "'");
                            if (j != dt.Columns.Count - 1)
                            {
                                strfield.Append(",");
                                strvalue.Append(",");
                            }
                            else
                            {
                            }
                        }
                        if (strvalue.ToString().Contains("<br/>"))
                        {
                            strvalue = strvalue.Replace("<br/>", Environment.NewLine);
                        }
                        cmd.CommandText = strSQL.Append(" insert into [" + tablename + "]( ")
                            .Append(strfield.ToString())
                            .Append(") values (").Append(strvalue).Append(")").ToString();
                        cmd.ExecuteNonQuery();
                    }
                    con.Close();
                }
            }
        }
        catch (Exception ex)
        {                
        }
    }

Export To Excel with Text more than 300 Characters not working

3 Answers3

Linked