[原]用c＃做了一个桌面程序用来分离excel文件里的数据时出现“操作必须使用一个可更新的查询”错误的解决

关键在Main.cs文件中用黄色着重注明的代码。

出错时：
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + outputPath + filename + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/"";

修改后：
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + outputPath + filename + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=0/"";

代码如下：

Main.Designer.cs

namespace Client {     partial class Main     {         /// <summary>         /// 必需的设计器变量。         /// </summary>         private System.ComponentModel.IContainer components = null;         /// <summary>         /// 清理所有正在使用的资源。         /// </summary>         /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>         protected override void Dispose(bool disposing)         {             if (disposing && (components != null))             {                 components.Dispose();             }             base.Dispose(disposing);         }         #region Windows 窗体设计器生成的代码         /// <summary>         /// 设计器支持所需的方法 - 不要         /// 使用代码编辑器修改此方法的内容。         /// </summary>         private void InitializeComponent()         {             this.label1 = new System.Windows.Forms.Label();             this.tbSourceFile = new System.Windows.Forms.TextBox();             this.btnSourceFile = new System.Windows.Forms.Button();             this.tbMessage = new System.Windows.Forms.TextBox();             this.btnSubmit = new System.Windows.Forms.Button();             this.tbLineCount = new System.Windows.Forms.TextBox();             this.label3 = new System.Windows.Forms.Label();             this.SuspendLayout();             //             // label1             //             this.label1.AutoSize = true;             this.label1.Location = new System.Drawing.Point(20, 20);             this.label1.Name = "label1";             this.label1.Size = new System.Drawing.Size(47, 12);             this.label1.TabIndex = 0;             this.label1.Text = "源文件:";             //             // tbSourceFile             //             this.tbSourceFile.Location = new System.Drawing.Point(80, 20);             this.tbSourceFile.Name = "tbSourceFile";             this.tbSourceFile.Size = new System.Drawing.Size(360, 21);             this.tbSourceFile.TabIndex = 1;             //             // btnSourceFile             //             this.btnSourceFile.Location = new System.Drawing.Point(480, 20);             this.btnSourceFile.Name = "btnSourceFile";             this.btnSourceFile.Size = new System.Drawing.Size(75, 23);             this.btnSourceFile.TabIndex = 2;             this.btnSourceFile.Text = "选择";             this.btnSourceFile.UseVisualStyleBackColor = true;             this.btnSourceFile.Click += new System.EventHandler(this.btnSourceFile_Click);             //             // tbMessage             //             this.tbMessage.BackColor = System.Drawing.SystemColors.Window;             this.tbMessage.Location = new System.Drawing.Point(20, 100);             this.tbMessage.Multiline = true;             this.tbMessage.Name = "tbMessage";             this.tbMessage.ReadOnly = true;             this.tbMessage.ScrollBars = System.Windows.Forms.ScrollBars.Vertical;             this.tbMessage.Size = new System.Drawing.Size(420, 100);             this.tbMessage.TabIndex = 6;             //             // btnSubmit             //             this.btnSubmit.Location = new System.Drawing.Point(480, 100);             this.btnSubmit.Name = "btnSubmit";             this.btnSubmit.Size = new System.Drawing.Size(75, 23);             this.btnSubmit.TabIndex = 7;             this.btnSubmit.Text = "提交";             this.btnSubmit.UseVisualStyleBackColor = true;             this.btnSubmit.Click += new System.EventHandler(this.btnSubmit_Click);             //             // tbLineCount             //             this.tbLineCount.Location = new System.Drawing.Point(80, 60);             this.tbLineCount.Name = "tbLineCount";             this.tbLineCount.Size = new System.Drawing.Size(100, 21);             this.tbLineCount.TabIndex = 9;             this.tbLineCount.Text = "1500";             //             // label3             //             this.label3.AutoSize = true;             this.label3.Location = new System.Drawing.Point(20, 60);             this.label3.Name = "label3";             this.label3.Size = new System.Drawing.Size(35, 12);             this.label3.TabIndex = 8;             this.label3.Text = "行数:";             //             // Main             //             this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);             this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;             this.ClientSize = new System.Drawing.Size(592, 216);             this.Controls.Add(this.tbLineCount);             this.Controls.Add(this.label3);             this.Controls.Add(this.btnSubmit);             this.Controls.Add(this.tbMessage);             this.Controls.Add(this.btnSourceFile);             this.Controls.Add(this.tbSourceFile);             this.Controls.Add(this.label1);             this.Name = "Main";             this.Text = "Main";             this.Load += new System.EventHandler(this.Main_Load);             this.ResumeLayout(false);             this.PerformLayout();         }         #endregion         private System.Windows.Forms.Label label1;         private System.Windows.Forms.TextBox tbSourceFile;         private System.Windows.Forms.Button btnSourceFile;         private System.Windows.Forms.TextBox tbMessage;         private System.Windows.Forms.Button btnSubmit;         private System.Windows.Forms.TextBox tbLineCount;         private System.Windows.Forms.Label label3;     } }

 

Main.cs

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Drawing; using System.Linq; using System.Text; using System.Windows; using System.Windows.Forms; using System.IO; namespace Client {     public partial class Main : Form     {         public Main()         {             InitializeComponent();         }         private void btnSourceFile_Click(object sender, EventArgs e)         {             OpenFileDialog opf = new OpenFileDialog();             opf.ShowDialog();             //opf.Filter = "*.xls";             tbSourceFile.Text = opf.FileName;         }         private void Main_Load(object sender, EventArgs e)         {         }         private void btnSubmit_Click(object sender, EventArgs e)         {             try             {                 DataTable dt = GetSourceFileData(tbSourceFile.Text);                 SpliteSourceFile(dt, int.Parse(tbLineCount.Text));             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }         }         public string CopyTemplateFile(string destinationPath)         {             string filename = Guid.NewGuid().ToString() + ".xls";             File.Copy(Path.GetFullPath(Application.StartupPath + @"/Template/Template.xls"), destinationPath + filename);             return filename;         }         public DataTable GetSourceFileData(string filename)         {             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/"";             OleDbConnection connection = new OleDbConnection(strConn);             string strExcel = "";             OleDbDataAdapter myCommand = null;             DataTable dt = new DataTable();             strExcel = "select * from [sheet1$]";             try             {                 connection.Open();                 myCommand = new OleDbDataAdapter(strExcel, strConn);                 myCommand.Fill(dt);                 return dt;             }             finally             {                 connection.Close();                 connection.Dispose();             }         }         protected string GetOutputPath()         {             DateTime dt = DateTime.Now;             string path = Path.GetFullPath(@"Output/" + dt.ToString("yyyyMMddHHmmss") + @"/");             if (Directory.Exists(path) == false)             {                 DirectoryInfo di = Directory.CreateDirectory(path);                 di.Attributes = FileAttributes.Normal;                 tbMessage.AppendText(path);                 tbMessage.AppendText("/r/n");             }             return path;         }         protected void SpliteSourceFile(DataTable dt, int lineCount)         {             int count = dt.Rows.Count;             int fileCount = count/lineCount;             if (count % lineCount > 0)                 fileCount++;             string outputPath = GetOutputPath();             for (int i = 0; i < fileCount; i++)             {                 string filename = CopyTemplateFile(outputPath);                 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + outputPath + filename + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=0/"";                 OleDbConnection connection = new OleDbConnection(strConn);                 OleDbCommand command = null;                 try                 {                     connection.Open();                     for (int j = i * lineCount; j < (i + 1) * lineCount && j < count; j++)                     {                         DataRow dr = dt.Rows[j];                         try                         {                             command = connection.CreateCommand();                             command.CommandText = "insert into [sheet1$] values(:p0,:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14)";                             command.Parameters.AddWithValue("p0", dr[0]);                             command.Parameters.AddWithValue("p1", dr[1]);                             command.Parameters.AddWithValue("p2", dr[2]);                             command.Parameters.AddWithValue("p3", dr[3]);                             command.Parameters.AddWithValue("p4", dr[4]);                             command.Parameters.AddWithValue("p5", dr[5]);                             command.Parameters.AddWithValue("p6", dr[6]);                             command.Parameters.AddWithValue("p7", dr[7]);                             command.Parameters.AddWithValue("p8", dr[8]);                             command.Parameters.AddWithValue("p9", dr[9]);                             command.Parameters.AddWithValue("p10", dr[10]);                             command.Parameters.AddWithValue("p11", dr[11]);                             command.Parameters.AddWithValue("p12", dr[12]);                             command.Parameters.AddWithValue("p13", dr[13]);                             command.Parameters.AddWithValue("p14", dr[14]);                             command.ExecuteNonQuery();                         }                         finally                         {                             command.Dispose();                         }                     }                 }                 finally                 {                     connection.Close();                     connection.Dispose();                 }             }         }     } }