string sqlExcelColumn = "";
string mExportFileName = "C:\\TEST\\Export\\Module\\TOC_MODULE_LOTID_CHECKOUT.xls";
string mSourceName = "C:\\TEST\\Source\\Module\\TOC_MODULE_LOTID_CHECKOUT.xls";
System.IO.File.Copy(mSourceName,mExportFileName,true);
System.IO.File.SetAttributes(mExportFileName,System.IO.FileAttributes.Normal);
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+mExportFileName+";Extended Properties=\"Excel 8.0;HDR=No\"";
objConn = new System.Data.OleDb.OleDbConnection(ConnStr);
objConn.Open(); System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
DataView dvQuery = Query.DoQuery(goodsql);
for(int i=0;i<dvQuery.Count;i++)
{
// int InsertIndex = 1;
StringBuilder sqlExcelColumn_p = new StringBuilder("");
sqlExcelColumn_p.AppendFormat("UPDATE [{0}${1}{2}:{3}{2}] SET ","PVI","A",Convert.ToString(i+5),"DX");
for(int j=0;j<dvQuery.Table.Columns.Count;j++)
{
sqlExcelColumn_p.Append("F"+sRow+"='"+dvQuery[i][dvQuery.Table.Columns[j].Caption].ToString()+"'"+",");
}
sqlExcelColumn_p.Remove(sqlExcelColumn_p.Length-1,1);
objCmd.CommandText = sqlExcelColumn_p.ToString();
objCmd.ExecuteNonQuery(); }上面的语句是我在C#里面往excel写数据的语句 我是一行一行的进行修改的 但是现在出现两问题是 一当我写到128列的时候就写不进去了 二 当每一格写到255个字符的时候写会报错 '字段太小而不能接受所要添加的数据的数量' 不知道有没有哪位高手知道是什么原因
string mExportFileName = "C:\\TEST\\Export\\Module\\TOC_MODULE_LOTID_CHECKOUT.xls";
string mSourceName = "C:\\TEST\\Source\\Module\\TOC_MODULE_LOTID_CHECKOUT.xls";
System.IO.File.Copy(mSourceName,mExportFileName,true);
System.IO.File.SetAttributes(mExportFileName,System.IO.FileAttributes.Normal);
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+mExportFileName+";Extended Properties=\"Excel 8.0;HDR=No\"";
objConn = new System.Data.OleDb.OleDbConnection(ConnStr);
objConn.Open(); System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
DataView dvQuery = Query.DoQuery(goodsql);
for(int i=0;i<dvQuery.Count;i++)
{
// int InsertIndex = 1;
StringBuilder sqlExcelColumn_p = new StringBuilder("");
sqlExcelColumn_p.AppendFormat("UPDATE [{0}${1}{2}:{3}{2}] SET ","PVI","A",Convert.ToString(i+5),"DX");
for(int j=0;j<dvQuery.Table.Columns.Count;j++)
{
sqlExcelColumn_p.Append("F"+sRow+"='"+dvQuery[i][dvQuery.Table.Columns[j].Caption].ToString()+"'"+",");
}
sqlExcelColumn_p.Remove(sqlExcelColumn_p.Length-1,1);
objCmd.CommandText = sqlExcelColumn_p.ToString();
objCmd.ExecuteNonQuery(); }上面的语句是我在C#里面往excel写数据的语句 我是一行一行的进行修改的 但是现在出现两问题是 一当我写到128列的时候就写不进去了 二 当每一格写到255个字符的时候写会报错 '字段太小而不能接受所要添加的数据的数量' 不知道有没有哪位高手知道是什么原因
建议您换一种方法写数据!
private void SaveAs(DataGridView dgvAgeWeekSex)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl 2000-2003 (*.xls)|*.xls|Execl 2007 (*.xlsx)|*.xlsx";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Dialog";
saveFileDialog.ShowDialog();
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
for (int i = 0; i < dgvAgeWeekSex.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dgvAgeWeekSex.Columns[i].HeaderText;
}
sw.WriteLine(str);
for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dgvAgeWeekSex.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
Anton,I'm unclear why you say you can create a longtext field in the old XLS, when
the hard limit for a text cell in an XLS is 1024 characters.I read that in Excel (12) 2007 the limit was raised to 32k, but I wasn't
sure how that translates into column type.So I tried modifying my test app to create text field af various lengths.
I successfully created Excel spreadsheets in all three formats with 2048
characters of text in cells. I didn't try to got longer.按他说的,97excel之类的老版本里单元格最大字符长度限制是1024个