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

本文主要是介绍C#通过NPOI 读、写Excel数据；合并单元格、简单样式修改；通过读取已有的Excel模板另存为文件，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1 需要引用的DLL

2 调用示例

public static void WriteExcel()
{string templateFile = @"F:\12312\excel.xlsx"; // 文件必须存在string outFile = @"F:\12312\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";string picPath = @"F:\12312\test.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 "";}}}}

这篇关于C#通过NPOI 读、写Excel数据；合并单元格、简单样式修改；通过读取已有的Excel模板另存为文件的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---