PrintExpressCode/Common/ExcelHelper.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;
namespace PrintExpressCode
{
    /// <summary>
    /// Excel的辅助类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 读取excel到datatable中
        /// </summary>
        /// <param name="excelPath">excel地址</param>
        /// <param name="sheetIndex">sheet索引</param>
        /// <returns>成功返回datatable，失败返回null</returns>
        public static DataTable ImportExcel(string excelPath, int sheetIndex)
        {

            IWorkbook workbook = null;//全局workbook
            ISheet sheet;//sheet
            DataTable table = null;
            try
            {
                FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在
                if (fileInfo.Exists)
                {
                    FileStream fileStream = fileInfo.OpenRead();//打开文件，得到文件流
                    switch (fileInfo.Extension)
                    {
                        //xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开
                        case ".xls": workbook = new HSSFWorkbook(fileStream); break;
                        case ".xlsx": workbook = new XSSFWorkbook(fileStream); break;
                        default: break;
                    }
                    fileStream.Close();//关闭文件流
                }
                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);//读取到指定的sheet
                    table = new DataTable();//初始化一个table

                    IRow headerRow = sheet.GetRow(0);//获取第一行，一般为表头
                    int cellCount = headerRow.LastCellNum;//得到列数

                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列
                        table.Columns.Add(column);
                    }
                    //遍历读取cell
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {