windows应用程序，读取excel文件并解析

实现了两种方法：

用OleDb和NPOI读取excel文件。无疑NPOI是最优的，因为：

1、第一种方法需要安装excel程序；

2、第一种方法只能读取xls文件，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.IO;

using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

namespace Upload
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        DataTable GetDataFromExcelByConn(bool hasTitle = false)
        {
            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() == DialogResult.Cancel) return null;
            var filePath = openFile.FileName;
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType)) return null;

            using (DataSet ds = new DataSet())
            {
                string strCon = string.Format("Provider=Microsoft.ACE.OLEDB.{0}.0;" +
                                "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
                                "data source={3};",
                                (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);
                string strCom = " SELECT * FROM [Sheet1$]";
                using (OleDbConnection myConn = new OleDbConnection(strCon))
                using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
                {
                    myConn.Open();
                    myCommand.Fill(ds);
                }
                if (ds == null || ds.Tables.Count <= 0) return null;
                return ds.Tables[0];
            }
        }

        string cellValue = "";

        private void button2_Click(object sender, EventArgs e)
        {
            DataTable data = GetDataFromExcelByNPOI();

            textBox1.Text = "";
            //取出所有标题
            for (int i = 0; i < data.Columns.Count; i++)
            {
                textBox1.Text += ("{" + data.Columns[i] + "}");
            }

            textBox1.Text += "\r\n";
            textBox1.Text += "//---------以上是标题，以下是单元格---------------------------";
            textBox1.Text += "\r\n";
            //取出所有标题以外的单元格
            for (int i = 0; i < data.Rows.Count; i++)
            {
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    if (data.Rows[i][j].ToString() == "")
                    {
                        cellValue = "------";
                    }
                    else
                    {
                        cellValue = data.Rows[i][j].ToString();
                    }
                    textBox1.Text += ("{" + cellValue + "}");
                }
                textBox1.Text += "\r\n";
            }
        }

        DataTable GetDataFromExcelByNPOI()
        {
            IWorkbook workbook;

            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() == DialogResult.Cancel) return null;
            var filePath = openFile.FileName;
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType)) return null;

            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (fileType == ".xls")
                {
                    workbook = new HSSFWorkbook(file);
                }
                else if (fileType == ".xlsx")
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    return null;
                }


                ISheet sheet = workbook.GetSheetAt(0);//取第一个表
                DataTable table = new DataTable();

                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;
                
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                    table.Columns.Add(column);
                }

                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = GetCellValue(row.GetCell(j));
                        }
                    }

                    table.Rows.Add(dataRow);
                }
                return table;
            }

        }

        private object GetCellValue(ICell cell)
        {
            object value = null;
            try
            {
                if (cell.CellType != CellType.Blank)
                {
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            // Date comes here
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                value = cell.DateCellValue;
                            }
                            else
                            {
                                // Numeric type
                                value = cell.NumericCellValue;
                            }
                            break;
                        case CellType.Boolean:
                            // Boolean type
                            value = cell.BooleanCellValue;
                            break;
                        case CellType.Formula:
                            value = cell.CellFormula;
                            break;
                        default:
                            // String type
                            value = cell.StringCellValue;
                            break;
                    }
                }
            }
            catch (Exception)
            {
                value = "";
            }

            return value;
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            DataTable data = GetDataFromExcelByConn();
            textBox1.Text = data.Rows[0][0].ToString() + "\r\n" + data.Rows[0][1].ToString();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}