一.方法

  1.OleD方法实现该功能。

  2.本次随笔内容只包含读取Excel内容,并另存为。

 

二.代码

  (1)找到文档代码

1
2
3
4
5
6
7
8
OpenFileDialog openFile = new OpenFileDialog();
          openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
          openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
          openFile.Multiselect = false;
          if (openFile.ShowDialog() == false) { return; }
           
          var filePath = openFile.FileName;
          string fileType = System.IO.Path.GetExtension(filePath)

 

  (2)打开文档代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
string connStr = "";
            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            string sql_F = "select *  from [sheet1$]";
 
            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
 
            try
            {
                conn = new OleDbConnection(connStr);
                conn.Open();

  sql_F是Linq语句,查询sheet1内所有数据,如果有特定筛选可以把*换成筛选内容,这个大家都懂,sheet1被重命名过的就更改下语句内sheet1。该语句用在后面筛选。

  OleDbDataAdapter 用于和上述linq语句检索字符串。

  OleDbConnection 定义个与数据源的连接。

  (3)获取cell数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
try
           {
               conn = new OleDbConnection(connStr);
               conn.Open();
 
                da = new OleDbDataAdapter(sql_F, connStr);
 
                   DataTable dsItem = new DataTable();
                   da.Fill(dsItem);
                   foreach (DataRow item in dsItem.Rows) {
                     string row1=  item[0].ToString();
                     string row2 = item[1].ToString();
                     string row3 = item[2].ToString();
                     string row4 = item[3].ToString();
                       MessageBox.Show(row1 + " " + row2 + " " + row3 + " " + row4);
                   }
           }
           catch (Exception ex)
           {
 
           }

  conn为数据源实例化。

  da为查询得出的数据。

  将da的内容放到一个新的dataTable中,然后读取该dataTable的每一行(Row)或列(column)等。

 

  (4)释放

复制代码
 finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    da.Dispose();
                    conn.Dispose();
                }
            }
复制代码

  该文档被打开后,记得释放和关闭,否则会一直在内存里。

 

三.实例代码

posted on   Khandasas  阅读(1117)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示