苏飞论坛

标题: 三种Excel的文件（*.xls|*.csv|*.xlsx）能用同一种方式读取吗？Filter属性怎么写？ [打印本页]

作者: 马东锋 时间: 2015-11-19 10:31
标题: 三种Excel的文件（*.xls|*.csv|*.xlsx）能用同一种方式读取吗？Filter属性怎么写？
   DataTable dt = new DataTable();
      private void button2_Click(object sender, EventArgs e)
      {
         string filepath = string.Empty;
         string savePath = string.Empty;
         MessageBox.Show("选择excel文件");

         OpenFileDialog ofd = new OpenFileDialog();
         ofd.Filter = "Excel文件|*.xls";

         ofd.Multiselect = false;//只能选单个文件

         ofd.ShowReadOnly = true; //设定文件是否只读
         if (ofd.ShowDialog() == DialogResult.OK)
         {
            filepath = ofd.FileName;
         }

         MessageBox.Show("选择存储的位置");
         FolderBrowserDialog dbd = new FolderBrowserDialog();
         if (dbd.ShowDialog() == DialogResult.OK)
         {
            savePath = dbd.SelectedPath;
         }
         ReadExcel(filepath,savePath);
      }
      public void ReadExcel(string sExcelFile,string txtSavePath)
      {
         DataTable ExcelTable;
         DataSet ds = new DataSet();
         //Excel的连接
         //string StrConExcel2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
         OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";" + "Extended Properties=\"Excel 8.0;HDR=NO\"");
         objConn.Open();
         DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);

         int number = schemaTable.Rows.Count;
         for (int n = 0; n < number; n++)
         {
            string tableName = schemaTable.Rows[n][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
            string strSql = "select * from [" + tableName + "]";
            OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
            OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
            myData.Fill(ds, tableName);//填充数据
            objConn.Close();

            ExcelTable = ds.Tables[tableName];

            int iColums = ExcelTable.Columns.Count;//列数
            int iRows = ExcelTable.Rows.Count;//行数

            List<string> list_yuming = new List<string>();
            string strLine = string.Empty;
            for (int i = 0; i < ExcelTable.Rows.Count; i++)
            {
                  for (int j = 0; j < ExcelTable.Columns.Count; j++)
                  {
                     if(!string.IsNullOrEmpty(ExcelTable.Rows[j].ToString()))
                     {
                        strLine += ExcelTable.Rows[j].ToString() + ",";
                     }
                  }
                  if (!string.IsNullOrEmpty(strLine))
                  {
                     strLine = strLine.Substring(0, (strLine.Count() - 1));
                     list_yuming.Add(strLine);
                  }

                  strLine = string.Empty;
            }

            tableName=tableName.Substring(0,(tableName.Count()-1));
            string txtPath = txtSavePath + "\\" + tableName + ".txt";
            StreamWriter sw = new StreamWriter(txtPath, true, Encoding.Default);
            for (int i = 0; i < list_yuming.Count; i++)
            {
                  sw.WriteLine(list_yuming);
            }
            sw.Close();
         }

         MessageBox.Show("ok");
      }

作者: 站长苏飞 时间: 2015-11-19 10:40
可以啊使用|分开继续向后加就行了

作者: 马东锋 时间: 2015-11-19 10:46

站长苏飞发表于 2015-11-19 10:40
可以啊使用|分开继续向后加就行了

(, 下载次数: 142)