asp.net Excel_MyTest

 自己的随便的一段测试代码,可以打开Excel 8.0->*.xls 和 Excel 12.0->*.xlsx

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;
using System.IO;

namespace Excel_081103
{
    public partial class Form1 : Form
    {
        string filename_in = null;
        string filename_Ext = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (filename_in == "" || filename_in == null)
                return;
            ArrayList al = new ArrayList();
            label2.Text = "Wait";
            if (filename_Ext == ".xls")
                al = ExcelSheetNameOld(filename_in);
            if (filename_Ext == ".xlsx")
                al = ExcelSheetName(filename_in);
            label2.Text = "OK!";
            comboBox1.DataSource = al;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (filename_in == "" || filename_in == null)
                return;
            if (comboBox1.DataSource == null)
                return;
            DataSet ds = new DataSet();
            string s = comboBox1.SelectedValue.ToString();
            if (filename_Ext == ".xls")
            {
                ds = ExcelDataSourceOld(filename_in, s);
            }
            if (filename_Ext == ".xlsx")
                ds = ExcelDataSource(filename_in, s);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "Excel 8.0|*.xls|Excel 12.0|*.xlsx|All Files|*.*";
            openFileDialog1.Multiselect = false;
            openFileDialog1.ShowDialog();
        }

        private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            filename_in = openFileDialog1.FileName;
            filename_Ext = filename_in.Substring(filename_in.LastIndexOf("."));
            label1.Text = filename_in;
        }

        #region old_xls

        public DataSet ExcelDataSourceOld(string filepath, string sheetname)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            DataSet ds = new DataSet();
            try
            {
                OleDbConnection conn = new OleDbConnection(strConn);
                string str = "select * from [" + sheetname + "]";
                OleDbDataAdapter oada = new OleDbDataAdapter(str, strConn);
                oada.Fill(ds);
                int a = ds.Tables[0].Rows.Count;
                MessageBox.Show(@"K.O!!","Excel Show Message");
                return ds;
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message, "Excel Show Message");
                return ds;
            }
        }

        public ArrayList ExcelSheetNameOld(string filepath)
        {
            ArrayList al = new ArrayList();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            try
            {
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                conn.Close();
                foreach (DataRow dr in sheetNames.Rows)
                {
                    al.Add(dr[2]);
                }
                label2.Text = "OK!";
                return al;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "Excel Show Message");
                return al;
            }
        }

        public void ExportExcel(DataTable dt, StreamWriter w)
        {
            try
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    w.Write(dt.Columns[i]);
                    w.Write(' ');
                }
                w.Write(" ");

                object[] values = new object[dt.Columns.Count];
                foreach (DataRow dr in dt.Rows)
                {
                    values = dr.ItemArray;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        w.Write(values[i]);
                        w.Write(' ');
                    }
                    w.Write(" ");
                }
                w.Flush();
                w.Close();
            }
            catch
            {
                w.Close();
            }
        }

        #endregion

        private DataSet ExcelDataSource(string filepath, string sheetname)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass App = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel._Workbook WorkBook = null;
            Microsoft.Office.Interop.Excel._Worksheet WorkSheet;
            DataSet ds=new DataSet();

            App.UserControl = true;

            //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            //System.Globalization.CultureInfo OldCI = System.Threading.Thread.CurrentThread.CurrentCulture;

            try
            {
                WorkBook = App.Workbooks.Open(filepath,
                    0,
                    true,
                    5,
                    "",
                    "",
                    true,
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                    "\t",
                    false,
                    false,
                    0,
                    true,
                    1,
                    0);

                //System.Threading.Thread.CurrentThread.CurrentCulture = OldCI;

                WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Sheets[sheetname];

                object temp = null;
                int colNum = 0;
                int rowNum = 0;
                for (int i = 0; i < WorkSheet.Columns.Count; i++)
                {
                    if (((Microsoft.Office.Interop.Excel.Range)WorkSheet.Cells[1, i + 1]).Value2 == null)
                        break;
                    colNum = i + 1;
                }
                for (int i = 0; i < WorkSheet.Rows.Count; i++)
                {
                    if (((Microsoft.Office.Interop.Excel.Range)WorkSheet.Cells[i + 1, 1]).Value2 == null)
                        break;
                    rowNum = i + 1;
                }
                FileInfo MyFile;
                StreamWriter sw;
                MyFile = new FileInfo(@"e:\test.txt");
                sw = MyFile.CreateText();
                sw.WriteLine("OK");
                sw.WriteLine("colNum=" + colNum.ToString() + " rowNum=" + rowNum);
                for (int i = 0; i < rowNum; i++)
                {
                    for (int j = 0; j < colNum; j++)
                    {
                        temp = ((Microsoft.Office.Interop.Excel.Range)WorkSheet.Cells[i + 1, j + 1]).Value2;

                        sw.Write(temp.ToString() + "\t|");
                    }
                    sw.WriteLine();
                }
                string ws = WorkSheet.Name;
                int ra = WorkSheet.Columns.Count;
                int dd = WorkSheet.Rows.Count;
                MessageBox.Show(@"See e:\test.txt", "Excel Show Message");
                return ds;//just test
            }
            catch (Exception e)
            {
                WorkBook.Close(false, filepath, false);
                App.Quit();
                MessageBox.Show(e.Message, "Excel Show Message");
                return ds;
            }
            finally
            {
                sw.Close();
            }
        }

        private ArrayList ExcelSheetName(string filepath)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass App = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel._Workbook WorkBook = null;
            ArrayList al = new ArrayList();

            App.UserControl = true;
            try
            {
                WorkBook = App.Workbooks.Open(filepath,
                    0,
                    true,
                    5,
                    "",
                    "",
                    true,
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                    "\t",
                    false,
                    false,
                    0,
                    true,
                    1,
                    0);

                foreach (Microsoft.Office.Interop.Excel.Worksheet ws in WorkBook.Sheets)
                {
                    al.Add(ws.Name);
                }
                label2.Text = "OK!";
                return al;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "Excel Show Message");
                return al;
            }
        }
    }
}