MES_Client/TGMES/Core.StlMes.Client.Judge/Commons/ExcelHelper.cs

using System;
using System.Data;
using System.IO;
using System.Windows.Forms;

namespace Core.StlMes.Client.Judge.Commons
{
    public class ExcelHelper
    {
        public static DataTable ExcelToDataTable(bool isFirstRowColumn = true, bool isFirstRowTitle = true)
        {
            DataTable dt = new DataTable();
            string fileName = "";
            var openFileDialog = new OpenFileDialog
            {
                Filter = "外购熔炼成分|*.xls;*.xlsx"
            };
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                fileName = openFileDialog.FileName;
            }
            //excel工作表
            //NPOI.SS.UserModel.ISheet sheet = null;
            //数据开始行(排除标题行)
            int startRow = 0;
            try
            {
                if (!File.Exists(fileName))
                {
                    return null;
                }
                //根据指定路径读取文件
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                //根据文件流创建excel数据结构
                NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
                //IWorkbook workbook = new HSSFWorkbook(fs);

                //sheet页数 workbook.NumberOfSheets
                for (int k = 0; k < 1; k++)
                {
                    NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(k);
                    if (sheet != null)
                    {
                        NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(isFirstRowTitle ? 1 : 0);
                        //一行最后一个cell的编号 即总的列数
                        int cellCount = firstRow.LastCellNum;
                        //如果第一行是标题列名
                        if (isFirstRowColumn)
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
                                if (cell != null)
                                {
                                    string cellValue = cell.StringCellValue;
                                    if (cellValue == null) break;
                                    dt.Columns.Add(cellValue);
                                }
                            }
                            startRow = sheet.FirstRowNum + 1 + (isFirstRowTitle ? 1 : 0);
                        }
                        else
                        {
                            startRow = sheet.FirstRowNum;
                        }
                        //最后一列的标号
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                            if (row == null) continue; //没有数据的行默认是null　　　　　　　
                            DataRow dr = dt.NewRow();
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                if (row.GetCell(j) == null) continue;
                                dr[j] = row.GetCell(j).ToString();
                            }
                            dt.Rows.Add(dr);
                        }
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("EXCEL格式错误:" + ex.Message);
                return null;
            }
        }
    }
}