C#导出Excel,某单元格内容长度超过255 的解决方法
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 + "] [color=#FF0000][b]memo[/b][/color],"; } 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(); } }
strCmd += "[" + dc.ColumnName + "] memo,";
只需要將該列首個單元格指定為memo類型就可以了!
此方法还没检验过,有待测试。。。。