C#使用NPOI 操作excel

DataTable导入导出

请自行添加以下动态库引用

NPOI.dll

NPOI.OOXML.dll

NPOI.OpenXml4Net.dll

NPOI.OpenXmlFormats.dll

using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ExcelHelper
{
    class ExcelHelper
    {

        public static void WriteToExcel(DataTable dt, string filePathAndName, string sheetname)
        {
            if (!string.IsNullOrEmpty(filePathAndName) && null != dt && dt.Rows.Count > 0)
            {
                XSSFWorkbook book = new XSSFWorkbook();
                ISheet sheet = book.CreateSheet(sheetname);
                ICellStyle styleone = book.CreateCellStyle();
                IFont font1 = book.CreateFont();
                font1.Color = 255;//颜色:色
                font1.FontHeightInPoints = 14;
                styleone.SetFont(font1);
                HSSFColor hssfcolor = new HSSFColor();
                styleone.FillBackgroundColor = 22;
                for (int i = 0; i < dt.Columns.Count; i++)
                    sheet.SetColumnWidth(i, 25 * 256);                       //设置列宽

                IRow row = sheet.CreateRow(0);
                row.Height = 100 * 5;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); //列名
                    sheet.GetRow(0).GetCell(i).CellStyle = styleone;
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filePathAndName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }
        }

        public static DataTable ReadExcel(string filePath, bool ToLower = false)//Excel内容读出到DataTable，excel每行每列单元格需整齐
        {
            if (string.IsNullOrEmpty(filePath))
            {
                return null;
            }
            IWorkbook hssfworkbook;
            if (filePath.Contains(".xlsx"))
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new XSSFWorkbook(file);
                }

            }
            else
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }

            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            int idx = 0;
            while (idx < 3)
            {
                try
                {
                    int count = sheet.GetRow(idx).LastCellNum;
                    for (int j = 0; j < count; j++)
                    {
                        string col = sheet.GetRow(idx).Cells[j].ToString().Trim();
                        if (ToLower)
                            col = col.ToLower();
                        dt.Columns.Add(col);
                        dt.Columns[j].ColumnName = col;
                    }
                    break;
                }
                catch { }
                idx++;
                rows.MoveNext();
            }
            while (rows.MoveNext())
            {
                IRow row;
                if (filePath.Contains(".xlsx"))
                {
                    row = (XSSFRow)rows.Current;
                }
                else
                {
                    row = (HSSFRow)rows.Current;
                }

                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = "";
                    }
                    else
                    {
                        dr[i] = cell;
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
    }
}