/// <summary>/// 设置文件保存位置，/// </summary>/// <returns>返回文件路径</returns>private string GetFilePath(){string FilePath = string.Empty;OpenFileDialog openFileDialog1 = new OpenFileDialog();//设置文件类型 |*.txt|All files(*.*)|*.* openFileDialog1.Filter = "xls|*.xls|xlsx|*.xlsx|csv|*.csv|All files(*.*)|*.*";openFileDialog1.DefaultExt = ".xls";//设置默认文件类型显示顺序 openFileDialog1.FilterIndex = 2;//保存对话框是否记忆上次打开的目录 openFileDialog1.RestoreDirectory = true;//点了保存按钮进入 if (openFileDialog1.ShowDialog() == DialogResult.OK){//获得文件路径 FilePath = openFileDialog1.FileName.ToString();//获取文件名，不带路径 //fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);//获取文件路径，不带文件名 // FilePath1 = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));//给文件名前加上时间 //newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;//在文件名里加字符 //saveFileDialog1.FileName.Insert(1,"dameng");// System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog1.OpenFile();//输出文件//fs输出带文字或图片的文件，就看需求了 }return FilePath;}///数据返回到DataSetinternal DataSet ToDataSet(string filePath){string fileType = System.IO.Path.GetExtension(filePath);if (string.IsNullOrEmpty(fileType)) return null;if (fileType.ToLower() == ".xls"){connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";}else if (fileType.ToLower() == ".xlsx"){connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";}else if (fileType.ToLower() == ".csv"){connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath.Remove(filePath.LastIndexOf("\\") + 1) + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";}else {MessageBox.Show("文件格式不不符合要求，此系统只支持导入xls，xlsx，csv 三种格式，详情咨询软件供应商");}string sql_F = "Select * FROM [{0}]";OleDbConnection conn = new OleDbConnection ();OleDbDataAdapter da = null;DataTable dtSheetName = null;DataSet ds = new DataSet();try{conn.ConnectionString=connStr;conn.Open(); string SheetName = "";dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });da = new OleDbDataAdapter();for (int i = 0; i < dtSheetName.Rows.Count; i++){SheetName = Convert.ToString(dtSheetName.Rows[i]["TABLE_NAME"]);if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$")){continue;}da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);DataSet dsItem = new DataSet();da.Fill(ds, "Mdt1");DataTable table = ds.Tables["Mdt1"];DataRow row = table.NewRow();da.Update(ds, "Mdt1");}}catch (Exception ex){MessageBox.Show(ex.ToString());}finally{// 关闭连接if (conn.State == ConnectionState.Open){conn.Close();da.Dispose();conn.Dispose();}}return ds;}
OleDb 写入Excel模块
还要注意此连接字符串
Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0"
没有后面那些属性，否则就要报出错误“操作必须使用一个可更新的查询。”
其实就是修改读取到的(excel所指向)Dataset。显示的替换为我们外来的dataset。
public static void DSToExcel(string Path,DataSet oldds) { //先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构 string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0"; OleDbConnection myConn = new OleDbConnection(strCon) ; string strCom="select * from [Sheet0$]"; myConn.Open ( ) ; OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom, myConn ) ; System.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(myCommand); //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。 builder.QuotePrefix="[";//获取insert语句中保留字符（起始位置） builder.QuoteSuffix="]"; //获取insert语句中保留字符（结束位置） DataSet newds=new DataSet(); myCommand.Fill(newds ,"Table1") ; for(int i=0;i<oldds.Tables[0].Rows.Count;i++) { //在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。//在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added DataRow nrow = newds.Tables["Table1"].NewRow(); for(int j=0;j<newds.Tables[0].Columns.Count;j++) { nrow[j]=oldds.Tables[0].Rows[i][j]; } newds.Tables["Table1"].Rows.Add(nrow); } myCommand.Update(newds,"Table1"); myConn.Close(); }
