C # Input error in Excel when string data is more than 255 characters

Question

I am trying to export some data to Excel. I am using OLEDB 12. The connectio line looks like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'" 

And I use the INSERT query. But whenever the data in the target column exceeds 255 characters, I get an exception.

 Exception Details: System.Data.OleDb.OleDbException: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. 

Is there a similar entry in SO: Excel cannot insert more than 255 characters? but this is not in C #,

I also referred to http://support.microsoft.com/kb/213841 and did not get any solution.

Please, help.

Answer 1

What I did finally:

Since I could not collect enough answers and could not solve the problem, I switched to the Excel object (Office Interop), and now there is no problem.

Answer 2

At first I thought that you can determine the data type (in memo / text) of the cells before writing the data, but this is not possible from this article http://support.microsoft.com/kb/278973 (About halfway down, it clearly indicates that data type definition in excel is not possible).

The “best” solution I could offer you is to cut your data into 255 char pieces and paste it into the excel file in the nieghbouring columns. from there you could use some kind of excel intercept to merge them together.

Answer 3

Try using this code, can it 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) { } }