一、设置许可
ExcelHelper.SetLicense(OfficeOpenXml.LicenseContext.NonCommercial);
二、初始化表格
public static RunInformation InitExcel(string path,int sheetNum,int ColWidth,int RowHeight)
{
RunInformation runinfo = new RunInformation();
try
{
FileInfo fileinfo = new FileInfo(path);
if (!fileinfo.Exists)
{
runinfo.Flag = false;
runinfo.Info = $"不存在该文件";
return runinfo;
}
else
{
excelPakage = new ExcelPackage(fileinfo);
workSheet = excelPakage.Workbook.Worksheets[sheetNum];
if (null != workSheet)
{
//设置
workSheet.DefaultColWidth = ColWidth;
workSheet.DefaultRowHeight = RowHeight;
if (null == workSheet.Dimension)
{
runinfo.Flag = true;
runinfo.Info = $"表:{workSheet.Name}没有Cells";
return runinfo;
}
else
{
//设置所有行列均为居中对齐
for (int row = 1; row <= workSheet.Dimension.Rows; row++)
{
// 获取当前行的范围
var rowRange = workSheet.Cells[row, 1, row, workSheet.Dimension.Columns];
// 设置水平对齐方式
rowRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// 设置垂直对齐方式
rowRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
// 循环设置所有列的对齐方式
for (int col = 1; col <= workSheet.Dimension.Columns; col++)
{
// 获取当前列的范围
var colRange = workSheet.Cells[1, col, workSheet.Dimension.Rows, col];
// 设置水平对齐方式
colRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// 设置垂直对齐方式
colRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
}
runinfo.Flag = true;
runinfo.Info = $"初始化工作表:{workSheet.Name}成功.";
}
else
{
runinfo.Flag = false;
runinfo.Info = $"没有此表:{workSheet.Name}";
}
}
}
catch(Exception ex)
{
runinfo.Flag = false;
runinfo.Info = $"初始化工作表:{workSheet.Name}失败,错误信息:{ex.Message+"\r\n"+ex.StackTrace}.";
}
return runinfo;
}
三、指定单元格插入值
public static RunInformation AddValue(ExcelWorksheet ws, string cell,object value)
{
RunInformation info = new RunInformation();
try
{
ws.Cells[cell].Value = value;
info.Flag = true;
info.Info = "添加数据成功";
}
catch (Exception ex)
{
info.Flag = false;
info.Info = $"添加数据失败,错误信息:{ex.Message}";
}
return info;
}
四、插入图片
/// <summary>
/// 插入图片
/// </summary>
/// <param name="name">图片名称</param>
/// <param name="fileinfo">图片路径</param>
/// <param name="pixceltop">图片左上角点X</param>
/// <param name="pixcelleft">图片左上角点Y</param>
/// <param name="pixcelwidth">图片宽</param>
/// <param name="pixcelheight">图片高</param>
/// <returns></returns>
public static RunInformation AddPicture( ExcelWorksheet ws, string name,FileInfo
fileinfo,int pixceltop,int pixcelleft,int
pixcelwidth,int pixcelheight)
{
RunInformation info = new RunInformation();
try
{
ExcelPicture pic = ws.Drawings.AddPicture(name, fileinfo);
pic.SetPosition(pixceltop,pixcelleft);
pic.SetSize(pixcelwidth,pixcelheight);
info.Flag = true;
info.Info = "添加图片成功";
}
catch (Exception ex)
{
info.Flag = false;
info.Info = $"添加图片失败,错误信息:{ex.Message}";
}
return info;
}
五、插入形状
/// <summary>
/// 插入形状
/// </summary>
/// <param name="ws">表</param>
/// <param name="name">图片名称</param>
/// <param name="fileinfo">图片路径</param>
/// <param name="pixceltop">图片左上角点X</param>
/// <param name="pixcelleft">图片左上角点Y</param>
/// <param name="pixcelwidth">图片宽</param>
/// <param name="pixcelheight">图片高</param>
/// <param name="eShapestyle">形状类型</param>
/// <param name="color">颜色</param>
/// <param name="FontSize">字体大小</param>
/// <param name="IsBold">是否加粗</param>
/// <param name="text">文本</param>
/// <returns></returns>
public static RunInformation AddShape(ExcelWorksheet ws, string name, eShapeStyle eShapestyle, int pixceltop, int pixcelleft, int pixcelwidth, int pixcelheight,Color color,float FontSize,bool IsBold,string text)
{
RunInformation info = new RunInformation();
try
{
ExcelShape shape = ws.Drawings.AddShape(name, eShapestyle);
shape.Font.Color = color;//设置形状的字体颜色
shape.Font.Size = FontSize;//字体大小
shape.Font.Bold = IsBold;//字体粗细
shape.Fill.Style = eFillStyle.NoFill;//设置形状的填充样式
shape.Border.Fill.Style = eFillStyle.NoFill;//边框样式
shape.SetPosition(pixceltop, pixcelleft);
shape.SetSize(pixcelwidth, pixcelheight);
shape.Text = text;//形状的内容
info.Flag = true;
info.Info = "添加形状成功";
}
catch (Exception ex)
{
info.Flag = false;
info.Info = $"添加形状失败,错误信息:{ex.Message}";
}
return info;
}
六、完整代码
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;
using XZHLib.SoftBaseLib;
namespace XZHLib
{
/// <summary>
/// 操作excel,使用此类一定要先执行SetLicense方法
/// </summary>
public class ExcelHelper
{
/// <summary>
/// 许可(非商用)
/// </summary>
/// <param name="licenseContext"></param>
public static void SetLicense(LicenseContext licenseContext)
{
ExcelPackage.LicenseContext = licenseContext;
}
/// <summary>
/// excel文件
/// </summary>
public static ExcelPackage excelPakage;
/// <summary>
/// 操作的表
/// </summary>
public static ExcelWorksheet workSheet;
/// <summary>
/// 初始化Excel
/// </summary>
/// <param name="path">excel路径</param>
/// <param name="sheetNum">第几个表格</param>
/// <param name="ColWidth">列宽</param>
/// <param name="RowHeight">行高</param>
/// <returns>工具运行结果</returns>
public static RunInformation InitExcel(string path,int sheetNum,int ColWidth,int RowHeight)
{
RunInformation runinfo = new RunInformation();
try
{
FileInfo fileinfo = new FileInfo(path);
if (!fileinfo.Exists)
{
runinfo.Flag = false;
runinfo.Info = $"不存在该文件";
return runinfo;
}
else
{
excelPakage = new ExcelPackage(fileinfo);
workSheet = excelPakage.Workbook.Worksheets[sheetNum];
if (null != workSheet)
{
//设置
workSheet.DefaultColWidth = ColWidth;
workSheet.DefaultRowHeight = RowHeight;
if (null == workSheet.Dimension)
{
runinfo.Flag = true;
runinfo.Info = $"表:{workSheet.Name}没有Cells";
return runinfo;
}
else
{
//设置所有行列均为居中对齐
for (int row = 1; row <= workSheet.Dimension.Rows; row++)
{
// 获取当前行的范围
var rowRange = workSheet.Cells[row, 1, row, workSheet.Dimension.Columns];
// 设置水平对齐方式
rowRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// 设置垂直对齐方式
rowRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
// 循环设置所有列的对齐方式
for (int col = 1; col <= workSheet.Dimension.Columns; col++)
{
// 获取当前列的范围
var colRange = workSheet.Cells[1, col, workSheet.Dimension.Rows, col];
// 设置水平对齐方式
colRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// 设置垂直对齐方式
colRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
}
runinfo.Flag = true;
runinfo.Info = $"初始化工作表:{workSheet.Name}成功.";
}
else
{
runinfo.Flag = false;
runinfo.Info = $"没有此表{workSheet.Name}";
}
}
}
catch(Exception ex)
{
runinfo.Flag = false;
runinfo.Info = $"初始化工作表:{workSheet.Name}失败,错误信息:{ex.Message+"\r\n"+ex.StackTrace}.";
}
return runinfo;
}
/// <summary>
/// 指定单元格插入值
/// </summary>
/// <param name="ws">表</param>
/// <param name="cell">单元格</param>
/// <param name="value">值</param>
/// <returns></returns>
public static RunInformation AddValue(ExcelWorksheet ws, string cell,object value)
{
RunInformation info = new RunInformation();
try
{
ws.Cells[cell].Value = value;
info.Flag = true;
info.Info = "添加数据成功";
}
catch (Exception ex)
{
info.Flag = false;
info.Info = $"添加数据失败,错误信息:{ex.Message}";
}
return info;
}
/// <summary>
/// 插入图片
/// </summary>
/// <param name="name">图片名称</param>
/// <param name="fileinfo">图片路径</param>
/// <param name="pixceltop">图片左上角点X</param>
/// <param name="pixcelleft">图片左上角点Y</param>
/// <param name="pixcelwidth">图片宽</param>
/// <param name="pixcelheight">图片高</param>
/// <returns></returns>
public static RunInformation AddPicture( ExcelWorksheet ws, string name,FileInfo fileinfo,int pixceltop,int pixcelleft,int pixcelwidth,int pixcelheight)
{
RunInformation info = new RunInformation();
try
{
ExcelPicture pic = ws.Drawings.AddPicture(name, fileinfo);
pic.SetPosition(pixceltop,pixcelleft);
pic.SetSize(pixcelwidth,pixcelheight);
info.Flag = true;
info.Info = "添加图片成功";
}
catch (Exception ex)
{
info.Flag = false;
info.Info = $"添加图片失败,错误信息:{ex.Message}";
}
return info;
}
/// <summary>
/// 插入形状
/// </summary>
/// <param name="ws">表</param>
/// <param name="name">图片名称</param>
/// <param name="fileinfo">图片路径</param>
/// <param name="pixceltop">图片左上角点X</param>
/// <param name="pixcelleft">图片左上角点Y</param>
/// <param name="pixcelwidth">图片宽</param>
/// <param name="pixcelheight">图片高</param>
/// <param name="eShapestyle">形状类型</param>
/// <param name="color">颜色</param>
/// <param name="FontSize">字体大小</param>
/// <param name="IsBold">是否加粗</param>
/// <param name="text">文本</param>
/// <returns></returns>
public static RunInformation AddShape(ExcelWorksheet ws, string name, eShapeStyle eShapestyle, int pixceltop, int pixcelleft, int pixcelwidth, int pixcelheight,Color color,float FontSize,bool IsBold,string text)
{
RunInformation info = new RunInformation();
try
{
ExcelShape shape = ws.Drawings.AddShape(name, eShapestyle);
shape.Font.Color = color;//设置形状的字体颜色
shape.Font.Size = FontSize;//字体大小
shape.Font.Bold = IsBold;//字体粗细
shape.Fill.Style = eFillStyle.NoFill;//设置形状的填充样式
shape.Border.Fill.Style = eFillStyle.NoFill;//边框样式
shape.SetPosition(pixceltop, pixcelleft);
shape.SetSize(pixcelwidth, pixcelheight);
shape.Text = text;//形状的内容
info.Flag = true;
info.Info = "添加形状成功";
}
catch (Exception ex)
{
info.Flag = false;
info.Info = $"添加形状失败,错误信息:{ex.Message}";
}
return info;
}
}
}