ExcelHelper导出

using System;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//引入命名空间
using System.Runtime.InteropServices;//DllImport特性命名空间
using System.Windows.Forms;//窗体命名空间
using System.Collections.Generic;//List命名空间
using System.Data;//Dataset命名空间
using System.IO;//文件命名空间
using NPOI.HSSF.UserModel;//Excel NPOI命名空间，这个需要更新最新版本
using NPOI.SS.UserModel;//Excel NPOI命名空间，这个需要更新最新版本
using Microsoft.Office.Interop.Excel;//Worksheet 命名空间，如果程序中没有需要去管理NuGet程序包中更新

namespace WindowsFormsApp1
{
    public class ExcelHelper
    {
        /// <summary>
        /// sql导出
        /// </summary>
        /// <param name="sql"></param>
        public static void ExcelExport(DataSet ds, List<string> lis, string fileName)
        {
            //创建工作簿
            IWorkbook workbook = new HSSFWorkbook();
            //创建工作表
            ISheet sheet = workbook.CreateSheet(fileName);
            //创建第一行
            IRow cells = sheet.CreateRow(0);

            for (int i = 0; i < lis.Count; i++)
            {
                cells.CreateCell(i).SetCellValue(lis[i]);
            }
            int index = 0;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                //循环添加行
                IRow row = sheet.CreateRow(index + 1);
                int count = 0;
                while (count != lis.Count)
                {
                    //获取当前字段的类型
                    Type type = dr[count].GetType();
                    //循环添加单元格
                    TypeTransformation(type, count, dr, row);
                    count++;
                }
                index++;
            }
            //获取当前程序的工作目录
            string dis = Directory.GetCurrentDirectory();
            //获取指定字符串的目录信息
            string path = Path.GetDirectoryName(dis);
            path = Path.GetDirectoryName(path);
            //获取目录下的ErrorLog文件
            string file = path + "\\" + fileName + ".xls";
            //判断文件是否存在，不存在就创建
            if (!Directory.Exists(file))
            {
                Directory.CreateDirectory(file);
            }
            //C: \Users\Administrator\source\repos\WindowsFormsApp1\WindowsFormsApp1\StudentInfo.xls
            string  filepath= Path.GetFileName(file);
            //判断文件是否打开
            if (!IsFileOpen(filepath))
            {
                using (FileStream fs=File.OpenWrite(filepath))
                {
                    workbook.Write(fs);
                }
            }
            else
            {
                MessageBox.Show("请关闭文件！重新导出...");
                return;
            }
            
            System.Windows.Forms.MessageBox.Show("导出成功。。");
        }
        /// <summary>
        /// 判断文件类型方法
        /// </summary>
        /// <param name="type">文件类型</param>
        /// <param name="count">当前单元格的下标</param>
        /// <param name="dr">数据源</param>
        /// <param name="row">行</param>
        private static void TypeTransformation(Type type, int count, DataRow dr, IRow row)
        {
            switch (type.Name)
            {
                case "Int32":
                    row.CreateCell(count).SetCellValue(Convert.ToInt32(dr[count] ?? "0"));
                    break;
                case "String":
                case "Decimal":
                    row.CreateCell(count).SetCellValue(dr[count].ToString());
                    break;
                case "DBNull":
                case "":
                    row.CreateCell(count).SetCellType(CellType.Blank);
                    break;
                case "DateTime":
                    row.CreateCell(count).SetCellValue(Convert.ToDateTime(dr[count]));
                    break;
                case "Boolean":
                    row.CreateCell(count).SetCellValue(Convert.ToBoolean(dr[count]));
                    break;
                case "Char":
                    row.CreateCell(count).SetCellValue(Convert.ToChar(dr[count]));
                    break;
                default:
                    System.Windows.Forms.MessageBox.Show("该数据类型不属于此类，无法转换...");
                    break;
            }
        }
        [DllImport("kernel32.dll")]
        public static extern IntPtr _lopen(string lpPathName, int iReadWrite);

        [DllImport("kernel32.dll")]
        public static extern bool CloseHandle(IntPtr hObject);
        public const int OF_READWRITE = 2;
        public const int OF_SHARE_DENY_NONE = 0x40;
        public static readonly IntPtr HFILE_ERROR = new IntPtr(-1);

        /// <summary>
        /// 文件是否被打开
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static bool IsFileOpen(string path)
        {
            if (!File.Exists(path))
            {
                return false;
            }
            IntPtr vHandle = _lopen(path, OF_READWRITE | OF_SHARE_DENY_NONE);//windows Api上面有定义扩展方法
            if (vHandle == HFILE_ERROR)
            {
                return true;
            }
            CloseHandle(vHandle);
            return false;
        }
    }
}

调用案例

 导出结果