/// <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; } ///数据返回到DataSet internal 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(); }