C#通过NPOI 读、写Excel数据；合并单元格、简单样式修改；通过读取已有的Excel模板另存为文件

1 需要引用的DLL

2 调用示例

public static void WriteExcel()
{
    string templateFile = @"F:12312excel.xlsx"; // 文件必须存在
    string outFile = @"F:12312" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
    string picPath = @"F:12312test.jpg";

    IWorkbook workbook = ExcelHelper.GetReadWorkbook(templateFile);

    ISheet sheet = workbook.GetSheetAt(0);

    try
    {
        ExcelHelper.SetCellValue(sheet, 20, 0, "这里是第1行第1列内容");
        ExcelHelper.SetCellValue(sheet, 0, 1, "这里是第1行第2列内容");

        ExcelHelper.SetCellValue(sheet, 1, 0, "这里是第2行第1列内容");
        ExcelHelper.SetCellValue(sheet, 1, 1, "这里是第2行第2列内容");

        // Height：单位是1/20个点，所以要想得到一个点的话，需要乘以20。
        sheet.GetRow(1).Height = 44 * 20; // 给第2行设置行高

        // Width： 单位是1/256个字符宽度，所以要乘以256才是一整个字符宽度
        sheet.SetColumnWidth(1, 50 * 256); // 给第1列设置宽度

        ExcelHelper.SetCellValue(sheet, 2, 0, "这里是第3行第1列内容，需要设置字体样式");

        // 从第3行到第6行，第1列到第4列合并单元格
        ExcelHelper.SetCellRangeAddress(sheet, 2, 5, 0, 3);

        // 给合并之后的单元格加边框，并设置字体大小、居中、字体颜色、背景色
        ExcelHelper.AddRengionBorder(workbook, sheet, 2, 5, 0, 3);

        // 插入图片
        var bitmap = (Bitmap)Image.FromFile("1.bmp");
        ExcelHelper.InsertImage(workbook, sheet, 7, 16, 0, 2, bitmap);

        ExcelHelper.Save(workbook, outFile);

        Process.Start(outFile);

    }
    catch (Exception ex)
    {
        throw ex;
    }
}

3 工具类

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;


namespace Demo_Excel
{
    /// <summary>
    /// Excel导入导出帮助类--通过插件NPOI来实现导入导出操作
    /// 常见异常：
    /// 1.未添加ICSharpCode.SharpZipLib.dll
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 获取读取文件的IWorkbook对象
        /// </summary>
        /// <param name="filename">文件路径</param>
        /// <returns></returns>
        public static IWorkbook GetReadWorkbook(string filename)
        {
            FileStream fs = File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            IWorkbook workbook;

            string fileExt = Path.GetExtension(filename).ToLower();
            switch (fileExt)
            {
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                default:
                    throw new Exception("不支持的文件类型");
            }
            fs.Close();
            return workbook;
        }

        /// <summary>
        /// 获取读取文件的IWorkbook对象
        /// </summary>
        /// <param name="filename">文件路径</param>
        /// <returns></returns>
        public static IWorkbook GetWriteWorkbook(string filename)
        {
            if (string.IsNullOrWhiteSpace(filename))
                throw new Exception("不支持的文件类型");

            string fileExt = Path.GetExtension(filename).ToLower();

            switch (fileExt)
            {
                case ".xlsx": return new XSSFWorkbook();
                case ".xls": return new HSSFWorkbook();
                default: throw new Exception("不支持的文件类型");
            }
        }

        public static void Save(IWorkbook workbook, string filename)
        {
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存文件  
            using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
                fs.Close();
            }
        }

        /// <summary>
        /// 根据Excel模板更新Excel数据
        /// </summary>
        /// <param name="sourcefile">模板文件的路径</param>
        /// <param name="outfile">输出文件的内容</param>
        /// <param name="sheetIndex">模板文件在sheet中的编号</param>
        /// <param name="dictionary">用于更新数据的键值对，key：模板中需要录入信息的标识；Value：录入信息的内容</param>
        /// <returns></returns>
        public static int UpdataExcel(string sourcefile, string outfile, int sheetIndex, Dictionary<string, string> dictionary)
        {

            var allKeys = dictionary.Keys.ToArray();

            IWorkbook workbook = GetReadWorkbook(sourcefile);

            ISheet sheet = workbook.GetSheetAt(sheetIndex);

            int endRow = sheet.LastRowNum;
            for (int i = 0; i < endRow; i++)
            {
                var row = sheet.GetRow(i);
                for (int j = 0; j < row.LastCellNum; j++)
                {
                    var data = GetCellString(row.GetCell(j));
                    if (allKeys.Contains(data))
                    {
                        row.Cells[j].SetCellValue(dictionary[data]);
                    }
                }
            }

            Save(workbook, outfile);

            return 0;
        }

        /// <summary>
        /// 根据Excel模板更新Excel数据
        /// </summary>
        /// <param name="sourcefile">模板文件的路径</param>
        /// <param name="outfile">输出文件的内容</param>
        /// <param name="sheetIndex">模板文件在sheet中的编号</param>
        /// <param name="dictionary">用于更新数据的键值对，key：模板中需要录入信息的单元格的位置,X:行，Y:列；Value：录入信息的内容</param>
        /// <returns></returns>
        public static int UpdataExcel(string sourcefile, string outfile, int sheetIndex, Dictionary<Point, string> dictionary)
        {

            IWorkbook workbook = GetReadWorkbook(sourcefile);

            ISheet sheet = workbook.GetSheetAt(sheetIndex);

            foreach (var key in dictionary.Keys)
            {
                SetCellValue(sheet, key.X, key.Y, dictionary[key]);
            }

            Save(workbook, outfile);

            return 0;
        }

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="data">要导入的数据</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int Write(string fileName, DataTable data, string sheetName, bool isColumnWritten)
        {
            try
            {
                IWorkbook workbook = GetWriteWorkbook(fileName);

                ISheet sheet = workbook.CreateSheet(sheetName);

                int count = 0;//写入数据行

                if (isColumnWritten)
                {
                    //读取标题  
                    IRow rowHeader = sheet.CreateRow(count++);
                    for (int i = 0; i < data.Columns.Count; i++)
                    {
                        ICell cell = rowHeader.CreateCell(i);
                        cell.SetCellValue(data.Columns[i].ColumnName);
                    }
                }

                //读取数据  
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    IRow rowData = sheet.CreateRow(count++);
                    for (int j = 0; j < data.Columns.Count; j++)
                    {
                        ICell cell = rowData.CreateCell(j);
                        cell.SetCellValue(data.Rows[i][j].ToString());
                    }
                }

                Save(workbook, fileName);

                return count;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int Write(string fileName, DataTable data, bool isColumnWritten)
        {
            int ret = Write(fileName, data, "Sheet1", isColumnWritten);
            return ret;
        }

        /// <summary>
        /// 将DataTable数据导入到excel中(包含列名，工作簿名称为：Sheet1)
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="data">要导入的数据</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int Write(string fileName, DataTable data)
        {
            int ret = Write(fileName, data, true);
            return ret;
        }

        /// <summary>
        /// 读取Excel数据到DataTable
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="sheetIndex">sheet索引</param>
        /// <param name="isFirstRowCellName">第一行数据是否为列名</param>
        /// <param name="data">存储读取的数据</param>
        /// <returns></returns>
        public static int Read(string fileName, int sheetIndex, bool isFirstRowCellName, out DataTable data)
        {
            data = new DataTable();
            try
            {
                IWorkbook workbook = GetReadWorkbook(fileName);

                ISheet sheet = workbook.GetSheetAt(sheetIndex);

                if (isFirstRowCellName)
                {
                    IRow firstRow = sheet.GetRow(0);
                    var list = ReadDataRow(firstRow);
                    data.Columns.AddRange(list.Select(t => new DataColumn(t)).ToArray());
                }
                else
                {
                    int nMaxCol = 0;
                    for (int i = 0; i < sheet.LastRowNum; i++)
                    {
                        nMaxCol = Math.Max(nMaxCol, sheet.GetRow(i).LastCellNum);
                    }
                    for (int i = 0; i < nMaxCol; i++)
                    {
                        data.Columns.Add($"列{i + 1}");
                    }
                }

                int startRow = !isFirstRowCellName ? 0 : 1;
                int endRow = sheet.LastRowNum;
                var ret2 = Read(sheet, startRow, endRow, ref data);

                if (ret2 < 0) return -1;

                return data.Rows.Count;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 读取Excel数据到DataTable
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="isFirstRowCellName">第一行数据是否为列名</param>
        /// <param name="data">存储读取的数据</param>
        /// <returns></returns>
        public static int Read(string fileName, string sheetName, bool isFirstRowCellName, out DataTable data)
        {
            data = new DataTable();
            try
            {

                IWorkbook workbook = GetReadWorkbook(fileName);

                ISheet sheet = workbook.GetSheet(sheetName);
                Console.WriteLine(sheet.SheetName);
                if (isFirstRowCellName)
                {
                    IRow firstRow = sheet.GetRow(0);
                    var list = ReadDataRow(firstRow);
                    data.Columns.AddRange(list.Select(t => new DataColumn(t)).ToArray());
                }
                else
                {
                    int nMaxCol = 0;
                    for (int i = 0; i < sheet.LastRowNum; i++)
                    {
                        nMaxCol = Math.Max(nMaxCol, sheet.GetRow(i).LastCellNum);
                    }
                    for (int i = 0; i < nMaxCol; i++)
                    {
                        data.Columns.Add($"列{i + 1}");
                    }
                }

                int startRow = !isFirstRowCellName ? 0 : 1;
                int endRow = !isFirstRowCellName ? 0 : 1;
                var ret = Read(sheet, startRow, endRow, ref data);
                if (ret < 0)
                    return -1;

                return data.Rows.Count;
            }
            catch (Exception ex)
            {

                return -1;
            }
        }

        /// <summary>
        /// 读取Excel数据到DataTable
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="isFirstRowCellName">第一行数据是否为列名</param>
        /// <param name="data">存储读取的数据</param>
        /// <returns></returns>
        public static int Read(string fileName, bool isFirstRowCellName, out DataTable data)
        {
            int ret = Read(fileName, "sheet1", isFirstRowCellName, out data);
            return ret;
        }

        /// <summary>
        /// 读取Excel数据到DataTable
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="data">存储读取的数据</param>
        /// <returns></returns>
        public static int Read(string fileName, out DataTable data)
        {
            int ret = Read(fileName, "sheet1", false, out data);
            return ret;
        }

        /// <summary>
        /// 从指定行开始读取所有sheet的数据到DataTable(DataTable列名已创建)
        /// </summary>
        /// <param name="sheet">工作簿</param>
        /// <param name="startRow">指定读取起始行</param>
        /// <param name="endRow">指定读取结束行</param>
        /// <param name="data">存储读取的数据</param>
        /// <returns></returns>
        public static int Read(ISheet sheet, int startRow, int endRow, ref DataTable data)
        {
            endRow += 1;
            for (int i = startRow; i < endRow; i++)
            {
                var sheetRow = sheet.GetRow(i);
                if (sheetRow == null)
                {
                    data.Rows.Add();
                }
                else
                {
                    var list = ReadDataRow(sheetRow);
                    var row = data.NewRow();

                    int count = Math.Min(list.Count, data.Columns.Count);
                    for (int j = 0; j < count; j++)
                    {
                        row[j] = list[j];
                    }
                    data.Rows.Add(row);
                }

            }
            return data.Rows.Count;
        }

        /// <summary>
        /// 读取数据行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static List<string> ReadDataRow(ISheet sheet, int index) => ReadDataRow(sheet.GetRow(index));

        /// <summary>
        /// 读取数据行
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public static List<string> ReadDataRow(IRow row)
        {
            List<string> result = null;
            if (row != null)
            {
                result = new List<string>();
                int startColumn = 0;
                int endColumn = row.LastCellNum;
                for (int i = startColumn; i < endColumn; i++)
                {
                    result.Add(GetCellString(row.GetCell(i)));
                }
            }
            return result;
        }
        
        /// <summary>
        /// 往EXCEL指定单元格插入图片
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="firstRow"> 起始单元格行序号，从0开始计算</param>
        /// <param name="lastRow">  终止单元格行序号，从0开始计算</param>
        /// <param name="firstCell"> 起始单元格列序号，从0开始计算</param>
        /// <param name="lastCell">  终止单元格列序号，从0开始计算</param>
        /// <param name="bitmap">插入的图片</param> 
        public static void InsertImage(IWorkbook workbook, ISheet sheet, int firstRow, int lastRow, int firstCell, int lastCell, Bitmap bitmap)
        {
            // 将图片转换为字节数组
            byte[] imgBytes = BitmapToBytes(bitmap);

            int pictureIdx = workbook.AddPicture(imgBytes, PictureType.PNG);

            if (workbook is XSSFWorkbook)
            {
                XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();

                // dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
                // dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
                // dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
                // dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
                // col1：起始单元格列序号，从0开始计算；
                // row1：起始单元格行序号，从0开始计算，如例子中col1 = 0,row1 = 0就表示起始单元格为A1；
                // col2：终止单元格列序号，从0开始计算；
                // row2：终止单元格行序号，从0开始计算，如例子中col2 = 2,row2 = 2就表示起始单元格为C3；
                XSSFClientAnchor anchor = new XSSFClientAnchor(10, 10, 0, 0, firstCell, firstRow, lastCell, lastRow);

                //把图片插到相应的位置
                XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            }
            else
            {
                HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, firstCell, firstRow, lastCell, lastRow);

                //把图片插到相应的位置
                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            }

        }

        /// <summary>
        /// 单元格设置内容
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex">第几行，从0开始</param>
        /// <param name="cellIndex">第几列，从0开始</param>
        /// <param name="value">内容（字符串）</param>
        public static void SetCellValue(ISheet sheet, int rowIndex, int cellIndex, string value)
        {
            if (sheet.GetRow(rowIndex) == null)
            {
                sheet.CreateRow(rowIndex);
            }
            if (sheet.GetRow(rowIndex).GetCell(cellIndex) == null)
            {
                sheet.GetRow(rowIndex).CreateCell(cellIndex);
            }
            sheet.GetRow(rowIndex).GetCell(cellIndex).SetCellValue(value);
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            for (int r = rowstart; r <= rowend; r++)
            {
                for (int c = colstart; c <= colend; c++)
                {
                    if (sheet.GetRow(r) == null)
                    {
                        sheet.CreateRow(r); // 如果行不存在，则创建行
                    }
                    if (sheet.GetRow(r).GetCell(c) == null)
                    {
                        sheet.GetRow(r).CreateCell(c); // 如果列不存在，则创建列
                    }
                }
            }
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        /// <summary>
        /// 加范围边框和设置字体大小、颜色、背景色、居中
        /// </summary>
        /// <param name="firstRow">起始行</param>
        /// <param name="lastRow">结束行</param>
        /// <param name="firstCell">起始列</param>
        /// <param name="lastCell">结束列</param>
        /// <returns></returns>
        public static void AddRengionBorder(IWorkbook workbook, ISheet sheet, int firstRow, int lastRow, int firstCell, int lastCell)
        {
            for (int i = firstRow; i < lastRow; i++)
            {
                for (int n = firstCell; n < lastCell; n++)
                {
                    ICell cell;
                    cell = sheet.GetRow(i).GetCell(n);
                    if (cell == null)
                    {
                        cell = sheet.GetRow(i).CreateCell(n);
                    }
                    ICellStyle style = sheet.Workbook.CreateCellStyle();
                    style.BorderTop = BorderStyle.Thin;
                    style.BorderBottom = BorderStyle.Thin;
                    style.BorderLeft = BorderStyle.Thin;
                    style.BorderRight = BorderStyle.Thin;
                    style.Alignment = HorizontalAlignment.Center;   //水平对齐 :居中
                    style.VerticalAlignment = VerticalAlignment.Center; //垂直对齐  ：居中

                    if (i == firstRow) //第一行
                    {
                        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index; // 背景色：黑色
                        style.FillPattern = FillPattern.SolidForeground;

                        IFont font = workbook.CreateFont(); //创建一个字体颜色
                        font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;  //字体颜色：白色      
                        font.FontHeightInPoints = 18;//字体大小       

                        style.SetFont(font); //给样式设置字体
                    }

                    cell.CellStyle = style;
                }

            }
        }

        /// <summary>
        /// Bitmap转换为字节数组
        /// </summary>
        /// <param name="bitmap"></param>
        /// <returns></returns>
        private static byte[] BitmapToBytes(Bitmap bitmap)
        {
            // 1.先将BitMap转成内存流
            MemoryStream ms = new MemoryStream();
            bitmap.Save(ms, ImageFormat.Bmp);
            ms.Seek(0, SeekOrigin.Begin);
            // 2.再将内存流转成byte[]并返回
            byte[] bytes = new byte[ms.Length];
            ms.Read(bytes, 0, bytes.Length);
            ms.Dispose();
            return bytes;
        }

        /// <summary>
        /// 获取单元格数据
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellString(ICell cell)
        {
            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.Unknown:
                        return "";
                    case CellType.Numeric:
                        return cell.NumericCellValue.ToString();
                    case CellType.String:
                        return cell.StringCellValue;
                    case CellType.Formula:
                        return cell.CellFormula;
                    case CellType.Blank:
                        return "";
                    case CellType.Boolean:
                        return cell.BooleanCellValue.ToString();
                    case CellType.Error:
                        return "";
                    default:
                        return "";
                }
            }
            else
            {
                return "";
            }
        }
    }

}

代码007普通

打赏 收藏 海报 链接