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 "";
}
}
}
}
2291
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
