Excel操作类（Npoi）

1.  读写excel
2. 向excel追加数据
3. 指定追加数据颜色

using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.POIFS.FileSystem;
using System.Collections.Generic;
using System.Windows.Forms;

namespace Excel
{
    public class ExcelHelper
    {
        /// <summary>
        /// 向已存在的excel追加数据
        /// </summary>
        /// <param name="excelPath">已存在的excel路径</param>
        /// <param name="rowIndex">追加行索引</param>
        /// <param name="cellData">追加列索引,列索引-单元格值</param>
        public static void AddExcelData(string excelPath, int rowIndex, IDictionary<int, string> cellData, bool? Result = null)
        {
            FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读取流
            IWorkbook workbook;
            if (excelPath.Contains(".xlsx"))
                workbook = new XSSFWorkbook(fs);
            else
                workbook = new HSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);//获取工作表

            设置列宽
            //SetColumnWidth(sheet, 0, 20);
            //SetColumnWidth(sheet, 1, 10);
            //修改行

            //插入行
            //sheet.ShiftRows(sheet.PhysicalNumberOfRows, sheet.LastRowNum, 1, true, false);
            //增加行
            IRow row = sheet.CreateRow(sheet.PhysicalNumberOfRows); //得到表头
            if (rowIndex >= 0)
                row = sheet.GetRow(rowIndex);
            //设置行高
            row.Height = 256;

            IFont font1 = workbook.CreateFont();
            font1.FontHeightInPoints = 14;
            if (Result == true)
            {
                font1.Color = 11;//颜色:绿
            }
            if (Result == false)
            {
                font1.Color = 10;//颜色:红
            }

            ICell cell = null;
            ICellStyle style = null;
            foreach (KeyValuePair<int, string> keyValue in cellData)
            {
                //if (keyValue.Key == 1)
                //{
                //    cell = row.CreateCell(keyValue.Key);
                //    cell.SetCellValue(keyValue.Value);

                //    style = workbook.CreateCellStyle();
                //    //设置左对齐
                //    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                //    //设置斜线
                //    style.BorderDiagonal = BorderDiagonal.BACKWARD;
                //    style.BorderDiagonalLineStyle = NPOI.SS.UserModel.BorderStyle.THIN;
                //    //设置换行(若要单元格内换行必须加下面一句)
                //    style.WrapText = true;
                //    cell.CellStyle = style;
                //}
                //else
                {
                    cell = row.CreateCell(keyValue.Key);
                    cell.SetCellValue(keyValue.Value);

                    //设置居中
                    style = workbook.CreateCellStyle();
                    style.VerticalAlignment = VerticalAlignment.Center;
                    //设置左对齐
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                    style.FillForegroundColor = 100;

                    if (Result != null)
                    {
                        style.SetFont(font1);
                    }
                    //设置换行(若要单元格内换行必须加下面一句)
                    //style.WrapText = true;
                    cell.CellStyle = style;
                }
            }
            if (File.Exists(excelPath))
                File.Delete(excelPath);

            FileStream fout = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);//写入流
            fout.Flush();
            workbook.Write(fout);//写入文件
            workbook = null;
            fout.Close();
        }
        /// <summary>
        /// 12 蓝色 11绿色 10红色
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePathAndName"></param>
        /// <param name="sheetname"></param>
        public static void WriteToExcel(DataTable dt, string filePathAndName, string sheetname, bool? Result = null)
        {
            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 = 12;//颜色:蓝色
                font1.FontHeightInPoints = 14;
                styleone.SetFont(font1);
                HSSFColor hssfcolor = new HSSFColor();
                styleone.FillBackgroundColor = 22; 

                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;
                }

                styleone = book.CreateCellStyle();
                font1 = book.CreateFont();
                if (Result == true)
                {
                    font1.Color = 11;//颜色:绿
                }
                if (Result == false)
                {
                    font1.Color = 10;//颜色:红
                }
                font1.FontHeightInPoints = 14;
                styleone.SetFont(font1);
                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]));
                        if (Result != null)
                            row2.GetCell(j).CellStyle = styleone;
                    }
                }

                for (int i = 0; i < dt.Columns.Count; i++)
                    sheet.AutoSizeColumn(i);//设置列宽自动

                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;
                }
            }
        }

        /// <summary>
        /// Excel 文件内容读取
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="ToLower">标题自动转换为小写字母</param>
        /// <param name="IsHeader">表格第一行是否为表格标题</param>
        /// <returns></returns>
        public static DataTable ReadExcel(string filePath, bool ToLower = false, bool IsHeader = true)//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();
            }
            if (IsHeader)
                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;
        }


        static object LockObj = false;
        static string file = Application.StartupPath + "\\DB\\Records.xlsx";
        /// <summary>
        /// 12 蓝色 11绿色 10绿色
        /// </summary>
        /// <param name="Result"></param>
        /// <param name="dicCol"></param>
        /// <param name="dic"></param>
        public static void SaveRecords(bool Result, Dictionary<int, string> dicCol, Dictionary<int, string> dic)
        {
            dicCol.Add(dicCol.Count, "TestResult");
            dic.Add(dic.Count, Result ? "Pass" : "Fail");
            dicCol.Add(dicCol.Count, "TestTime");
            dic.Add(dic.Count, DateTime.Now.ToString());

            lock (LockObj)
            {
                if (!File.Exists(file))//"SN,Result,Voltage,DateTime\r\n"
                {
                    DataTable dt = new DataTable();
                    object[] objs = new object[dicCol.Count];
                    for (int i = 0; i < dicCol.Count; i++)
                    {
                        dt.Columns.Add(dicCol[i]);
                        objs[i] = dic[i];
                    }
                    dt.Rows.Add(objs);
                    WriteToExcel(dt, file, "records", Result);
                }
                else
                {
                    AddExcelData(file, -1, dic, Result);
                }
            }
        }
    }
}