EXCEL常用的类

using  System;
using  System.Data;
using  System.Data.OleDb;

namespace  my
{
    /**//// <summary>
    /// Excel 的摘要说明。
    /// </summary>
    public class Excel
    {
        public Excel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            
        }

        public static DataSet SelectExcel_AllSheet1(string path,string condition)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source="+path+";"+
                "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter myCommand = new OleDbDataAdapter("select * FROM [Sheet1$] "+condition, strConn);
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet);
            return myDataSet;
        }

        public static DataSet SelectExcel(string path,string sqltext)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source="+path+";"+
                "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter myCommand = new OleDbDataAdapter(sqltext, strConn);
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet);
            return myDataSet;
        }
        
        //获取EXCEL文件的表名
        public static string[] ObtainTableName(string path)
    {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            int i = dt.Rows.Count;
            string[] strTableName=new string[i];
            for(int s=0;s<i;s++)
            {
                strTableName[s] = dt.Rows[s]["TABLE_NAME"].ToString();
            }
            return strTableName;
    } 
        
        
        public static bool DataSetToExcelText(DataSet ds)
        {
            bool result=false;
            
            if (ds.Tables[0]!=null)
            {
                string filename=OpenSaveDialog();
                if ((filename!=null) && (filename!=""))
                {
                    StreamWriter sw=new StreamWriter(filename,false,Encoding.Unicode);
                    try
                    {
//                        StreamWriter sw=new StreamWriter(filename,false,Encoding.Unicode);
                        int i=0;
                        int j=0;
                        j=ds.Tables[0].Columns.Count;
                        //文件列头
                        string s="";
                        while (i<j)
                        {
                            s=s+ds.Tables[0].Columns[i].ColumnName+"\t";
                            i=i+1;
                        }
                        sw.WriteLine(s);
                        //写数据
                        foreach (DataRow r in ds.Tables[0].Rows )
                        {
                            i=0;
                            s="";
                            while (i<j)
                            {
                                if (r[i].ToString().Trim()=="")
                                {
                                    s=s+" "+"\t";
                                }
                                else
                                {
                                    s=s+r[i].ToString().Trim()+"\t";
                                }
                                i=i+1;
                            }
                            sw.WriteLine(s);
                        }
                        result=true;
                        MessageBox.Show("导出成功！");
                    }
                    catch
                    {
                        result=false;
                        MessageBox.Show("导出失败！");
                    }
                    finally
                    {
                        sw.Close();
                    }
                }
            }
                                   
            return result;
        }

        public static bool DataSetToExcelText(DataTable dt)
        {
            bool result=false;
            
            if (dt!=null)
            {
                string filename=OpenSaveDialog();
                if ((filename!=null) && (filename!=""))
                {
                    StreamWriter sw=new StreamWriter(filename,false,Encoding.Unicode);
                    try
                    {
                        //                        StreamWriter sw=new StreamWriter(filename,false,Encoding.Unicode);
                        int i=0;
                        int j=0;
                        j=dt.Columns.Count;
                        //文件列头
                        string s="";
                        while (i<j)
                        {
                            s=s+dt.Columns[i].ColumnName+"\t";
                            i=i+1;
                        }
                        sw.WriteLine(s);
                        //写数据
                        foreach (DataRow r in dt.Rows )
                        {
                            i=0;
                            s="";
                            while (i<j)
                            {
                                if (r[i].ToString().Trim()=="")
                                {
                                    s=s+" "+"\t";
                                }
                                else
                                {
                                    s=s+r[i].ToString().Trim()+"\t";
                                }
                                i=i+1;
                            }
                            sw.WriteLine(s);
                        }
                        result=true;
                        MessageBox.Show("导出成功！");
                    }
                    catch
                    {
                        result=false;
                        MessageBox.Show("导出失败！");
                    }
                    finally
                    {
                        sw.Close();
                    }
                }
            }
                                   
            return result;
        }

        private static string OpenSaveDialog()
        {
            string result="";
            SaveFileDialog sfd=new SaveFileDialog();
            sfd.Filter="Excel文件(*.xls)|*.xls|文本文件(*.txt)|*.txt";
            sfd.OverwritePrompt=true;
            if (sfd.ShowDialog()==DialogResult.OK)
            {
                result=sfd.FileName;
            }
            return result;
        }
        
        
        
        
    }
}  

转载于:https://www.cnblogs.com/hzuIT/articles/793094.html