using System.Data;
|
using System.Drawing.Imaging;
|
using System.Reflection;
|
using Admin.NET.Core.Helper.ExcelHelper;
|
using Furion.FriendlyException;
|
using Microsoft.AspNetCore.Http;
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using Spire.Xls;
|
using ICell = NPOI.SS.UserModel.ICell;
|
|
namespace PandaWork.Infrastructure;
|
|
/// <summary>
|
/// ExcelHelper导入
|
/// </summary>
|
public class ExcelOperation
|
{
|
/// <summary>
|
/// 以时间命名
|
/// </summary>
|
/// <param name="name"></param>
|
/// <returns></returns>
|
public static string GetFileName(string name)
|
{
|
string FileName = $"{name}{DateTime.Now.ToString("yyyyMMddhhmmss")}.xls";
|
return FileName;
|
}
|
/// <summary>
|
/// 以时间命名不带后缀名
|
/// </summary>
|
/// <param name="name"></param>
|
/// <returns></returns>
|
public static string GetFileNameEmpty(string name)
|
{
|
string FileName = $"{name}{DateTime.Now.ToString("yyyyMMddhhmmss")}";
|
return FileName;
|
}
|
/// <summary>
|
/// 导出 返回具体的路径文件
|
/// </summary>
|
/// <param name="dtDictionary">支持多个sheet导入</param>
|
/// <param name="FileName"> xxx.xlsx</param>
|
/// <param name="ProjectPath">路径可为null</param>
|
/// <param name="ExportPath"> 默认 "DownloadExcel\\"</param>
|
/// <returns></returns>
|
public static string ExportExcel(Dictionary<string, DataTable> dtDictionary, string FileName, string ProjectPath = "", string ExportPath = "")
|
{
|
if (dtDictionary is null || dtDictionary.Count == 0) return null;
|
if (string.IsNullOrWhiteSpace(FileName)) FileName = Guid.NewGuid().ToString() + ".xlsx";
|
if (string.IsNullOrWhiteSpace(ProjectPath)) ProjectPath = ExcelOperation.ProjectPath();
|
if (string.IsNullOrWhiteSpace(ExportPath)) ExportPath = "DownloadExcel\\";
|
|
Excel excel = new Excel();
|
foreach (var dt in dtDictionary)
|
{
|
if (dt.Value != null && dt.Value.Rows.Count > 0)
|
{
|
excel.CreateSheet(dt.Value.Columns, dt.Key);
|
foreach (DataRow row in dt.Value.Rows)
|
{
|
excel.SetRowValue(row);
|
}
|
}
|
}
|
string filePath = ProjectPath + ExportPath;
|
if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
|
filePath = filePath + FileName;
|
excel.SaveAs(filePath);
|
// return "\\" + ExportPath + FileName;
|
return filePath;
|
}
|
|
/// <summary>
|
/// 导出 返回个steam流
|
/// </summary>
|
/// <param name="dtDictionary">支持多个sheet导入</param>
|
/// <param name="FileName"> xxx.xlsx</param>
|
/// <param name="ProjectPath">路径可为null</param>
|
/// <param name="ExportPath"> 默认 "DownloadExcel\\"</param>
|
/// <returns></returns>
|
public static Stream ExportExcel(Dictionary<string, DataTable> dtDictionary)
|
{
|
if (dtDictionary is null || dtDictionary.Count == 0) return null;
|
// if (string.IsNullOrWhiteSpace(FileName)) FileName = Guid.NewGuid().ToString() + ".xlsx";
|
//if (string.IsNullOrWhiteSpace(ProjectPath)) ProjectPath = ExcelOperation.ProjectPath();
|
//if (string.IsNullOrWhiteSpace(ExportPath)) ExportPath = "DownloadExcel\\";
|
|
Excel excel = new Excel();
|
foreach (var dt in dtDictionary)
|
{
|
if (dt.Value != null && dt.Value.Rows.Count > 0)
|
{
|
excel.CreateSheet(dt.Value.Columns, dt.Key);
|
foreach (DataRow row in dt.Value.Rows)
|
{
|
excel.SetRowValue(row);
|
}
|
}
|
}
|
MemoryStream memoryStream = excel.SaveAsMemoryStream();
|
return memoryStream;
|
}
|
/// <summary>
|
/// 获取当前项目的目录
|
/// </summary>
|
/// <returns></returns>
|
public static string ProjectPath()
|
{
|
string Paths = System.AppDomain.CurrentDomain.BaseDirectory;
|
return Paths;
|
}
|
|
/// <summary>
|
/// 去除空行
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <returns></returns>
|
private static DataTable RemoveEmpty(DataTable dt)
|
{
|
List<DataRow> removelist = new List<DataRow>();
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
bool rowdataisnull = true;
|
for (int j = 0; j < dt.Columns.Count; j++)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
|
{
|
rowdataisnull = false;
|
}
|
}
|
if (rowdataisnull)
|
{
|
removelist.Add(dt.Rows[i]);
|
}
|
}
|
for (int i = 0; i < removelist.Count; i++)
|
{
|
dt.Rows.Remove(removelist[i]);
|
}
|
return dt;
|
}
|
|
/// <summary>
|
/// 将excel导入到datatable
|
/// </summary>
|
/// <param name="filePath">excel路径</param>
|
/// <param name="isColumnName">第一行是否是列名</param>
|
/// <returns>返回datatable</returns>
|
public static System.Data.DataTable ExcelToDataTable(string filePath, bool isColumnName)
|
{
|
System.Data.DataTable dataTable = null;
|
FileStream fs = null;
|
DataColumn column = null;
|
DataRow dataRow = null;
|
IWorkbook workbook = null;
|
ISheet sheet = null;
|
IRow row = null;
|
ICell cell = null;
|
int startRow = 0;
|
try
|
{
|
using (fs = File.OpenRead(filePath))
|
{
|
|
if (fs == null)
|
{
|
throw Oops.Oh("请上传事件文件");
|
}
|
long length = fs.Length;
|
if (length > 1024 * 1024 * 100) //200M
|
{
|
throw Oops.Oh("上传文件不能超过100M");
|
}
|
fs.Position = 0;
|
// 2007版本
|
if (filePath.ToLower().IndexOf(".xlsx") > 0)
|
workbook = new XSSFWorkbook(fs);
|
// 2003版本
|
else if (filePath.ToLower().IndexOf(".xls") > 0)
|
// workbook = new HSSFWorkbook(fs);
|
workbook = new XSSFWorkbook(fs);
|
else
|
{
|
throw Oops.Oh("请上传事件文件");
|
}
|
|
if (workbook != null)
|
{
|
sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
|
dataTable = new System.Data.DataTable();
|
if (sheet != null)
|
{
|
int rowCount = sheet.LastRowNum;//总行数
|
if (rowCount > 0)
|
{
|
IRow firstRow = sheet.GetRow(0);//第一行
|
int cellCount = firstRow.LastCellNum;//列数
|
|
//构建datatable的列
|
if (isColumnName)
|
{
|
startRow = 1;//如果第一行是列名，则从第二行开始读取
|
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
{
|
cell = firstRow.GetCell(i);
|
if (cell != null)
|
{
|
if (cell.StringCellValue != null)
|
{
|
column = new DataColumn(cell.StringCellValue);
|
dataTable.Columns.Add(column);
|
}
|
}
|
}
|
}
|
else
|
{
|
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
{
|
column = new DataColumn("column" + (i + 1));
|
dataTable.Columns.Add(column);
|
}
|
}
|
|
//填充行
|
for (int i = startRow; i <= rowCount; ++i)
|
{
|
row = sheet.GetRow(i);
|
if (row == null) continue;
|
|
dataRow = dataTable.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; ++j)
|
{
|
cell = row.GetCell(j);
|
if (cell == null)
|
{
|
dataRow[j] = "";
|
}
|
else
|
{
|
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
dataRow[j] = "";
|
break;
|
|
case CellType.Numeric:
|
short format = cell.CellStyle.DataFormat;
|
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
|
if (format == 14 || format == 31 || format == 57 || format == 58)
|
{
|
if (DateUtil.IsCellDateFormatted(cell))
|
{
|
dataRow[j] = cell.DateCellValue.Date.ToString();
|
}
|
}
|
else
|
{
|
dataRow[j] = cell.NumericCellValue;
|
}
|
|
break;
|
|
case CellType.String:
|
dataRow[j] = cell.StringCellValue;
|
break;
|
}
|
}
|
//为创建人赋值
|
// dataRow[cellCount] = userad;
|
}
|
//去除空白行数据
|
bool rowdataisnull = true;
|
for (int k = row.FirstCellNum; k < cellCount - 1; ++k)
|
{
|
if (!string.IsNullOrEmpty(dataRow[k] as string))
|
{
|
rowdataisnull = false;
|
}
|
}
|
if (!rowdataisnull)
|
{
|
dataTable.Rows.Add(dataRow);
|
}
|
}
|
}
|
}
|
}
|
}
|
return dataTable;
|
}
|
catch (Exception ex)
|
{
|
if (fs != null)
|
{
|
fs.Close();
|
}
|
throw Oops.Oh("错误：" + ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 以文件流IFormFile方式导入到DataTable
|
/// </summary>
|
/// <param name="file"></param>
|
/// <returns></returns>
|
public static DataTable TaskFileImport(IFormFile file)
|
{
|
string[] fileExcels = ".xls,.xlsx".Split(',');
|
string fileType = "";
|
|
if (file == null)
|
{
|
throw Oops.Oh("请上传事件文件");
|
}
|
var fileExtension = Path.GetExtension(file.FileName);
|
if (fileExtension == null)
|
{
|
throw Oops.Oh("文件无后缀信息");
|
}
|
long length = file.Length;
|
if (length > 1024 * 1024 * 100) //200M
|
{
|
throw Oops.Oh("上传文件不能超过100M");
|
}
|
if (!fileExcels.Contains(fileExtension))
|
{
|
throw Oops.Oh("上传文件只支持.xls,.xlsx");
|
}
|
|
string filePath = ExcelOperation.ProjectPath() + "DownloadExcel\\";
|
if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
|
var saveName = filePath + $"{DateTime.Now.ToString("yyyyMMddhhmmss")}" + (file.FileName);
|
using (FileStream fs = File.Create(saveName))
|
{
|
file.CopyTo(fs);
|
}
|
var datatel = ExcelToDataTable(saveName, true);
|
return datatel;
|
}
|
|
/// <summary>
|
/// 将excel导入到datatable，指定sheetname
|
/// </summary>
|
/// <param name="filePath">excel路径</param>
|
/// <param name="isColumnName">第一行是否是列名</param>
|
/// <param name="sheetName">Excel的sheet名</param>
|
/// <returns>返回datatable</returns>
|
public static List<DataTable> ExcelToDataTableList(string filePath, bool isColumnName, string[] sheetName)
|
{
|
List<DataTable> dataTableList = null;
|
FileStream fs = null;
|
DataColumn column = null;
|
DataRow dataRow = null;
|
IWorkbook workbook = null;
|
ISheet sheet = null;
|
IRow row = null;
|
ICell cell = null;
|
int startRow = 0;
|
try
|
{
|
using (fs = File.OpenRead(filePath))
|
{
|
// 2007版本
|
if (filePath.IndexOf(".xlsx") > 0)
|
workbook = new XSSFWorkbook(fs);
|
// 2003版本
|
else if (filePath.IndexOf(".xls") > 0)
|
workbook = new HSSFWorkbook(fs);
|
|
if (workbook != null && sheetName != null && sheetName.Length > 0)
|
{
|
dataTableList = new List<DataTable>();
|
//string[] sheetName = { "工单", "工单材料", "杆件", "杆件工时", "配料单", "配料单详情", "物料主数据", "工艺", "工序", "工艺工序", "检验项" };
|
for (int w = 0; w < sheetName.Length; w++)
|
//for (int w = 0; w < workbook.NumberOfSheets; w++)
|
{
|
//sheet = workbook.GetSheetAt(w);//循环读取每个sheet(根据下标获取)
|
sheet = workbook.GetSheet(sheetName[w]);//循环读取每个sheet(根据名称获取)
|
DataTable dataTable = new DataTable();
|
if (sheet != null)
|
{
|
int rowCount = sheet.LastRowNum;//总行数
|
if (rowCount > 0)
|
{
|
IRow firstRow = sheet.GetRow(0);//第一行
|
int cellCount = firstRow.LastCellNum;//列数
|
|
#region 构建datatable的列
|
if (isColumnName)
|
{
|
startRow = 1;//如果第一行是列名，则从第二行开始读取
|
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
{
|
cell = firstRow.GetCell(i);
|
if (cell != null)
|
{
|
if (cell.StringCellValue != null)
|
{
|
column = new DataColumn(cell.StringCellValue);
|
dataTable.Columns.Add(column);
|
}
|
}
|
}
|
}
|
else
|
{
|
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
{
|
column = new DataColumn("column" + (i + 1));
|
dataTable.Columns.Add(column);
|
}
|
}
|
#endregion
|
|
#region 填充行
|
for (int i = startRow; i <= rowCount; ++i)
|
{
|
row = sheet.GetRow(i);
|
if (row == null) continue;
|
|
bool IsNull = true;//是否是空行
|
dataRow = dataTable.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; ++j)
|
{
|
cell = row.GetCell(j);
|
if (cell == null)
|
{
|
dataRow[j] = "";
|
}
|
else
|
{
|
object value = null;
|
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
value = "";
|
break;
|
case CellType.Numeric:
|
short format = cell.CellStyle.DataFormat;
|
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
|
if (format == 14 || format == 31 || format == 57 || format == 58 || format == 176)
|
value = cell.DateCellValue;
|
else
|
value = Math.Round(cell.NumericCellValue, 2);//2位小数
|
break;
|
case CellType.String:
|
value = cell.StringCellValue;
|
break;
|
case CellType.Formula://公式
|
row.GetCell(j).SetCellType(CellType.String);//先改变公式为字符
|
value = cell.StringCellValue;
|
break;
|
default:
|
value = cell.StringCellValue;
|
break;
|
}
|
|
if (!string.IsNullOrWhiteSpace(value.ToString()))
|
{
|
IsNull = false;
|
dataRow[j] = value;
|
}
|
}
|
}
|
if (!IsNull)
|
dataTable.Rows.Add(dataRow);
|
}
|
#endregion
|
}
|
dataTableList.Add(dataTable);
|
}
|
}
|
}
|
}
|
return dataTableList;
|
}
|
catch (Exception ex)
|
{
|
if (fs != null)
|
{
|
fs.Close();
|
}
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 格式化关键字集合
|
/// </summary>
|
/// <typeparam name="T">泛型对象</typeparam>
|
/// <param name="t">关键字集对象</param>
|
/// <returns></returns>
|
public static Dictionary<string, string> getProperties<T>(T t)
|
{
|
Dictionary<string, string> keywords = new Dictionary<string, string>();
|
if (t == null)
|
{
|
return keywords;
|
}
|
System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
|
|
if (properties.Length <= 0)
|
{
|
return keywords;
|
}
|
foreach (System.Reflection.PropertyInfo item in properties)
|
{
|
string name = "{$" + item.Name + "}";
|
object value = item.GetValue(t, null);
|
if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
|
{
|
keywords.Add(name, value.ToString());
|
}
|
else
|
{
|
getProperties(value);
|
}
|
}
|
return keywords;
|
}
|
|
/// <summary>
|
/// NPOI使用ShiftRows向excel插入行，并复制原有样式
|
/// </summary>
|
/// <param name="SavePath">模板文件，包含物理路径</param>
|
/// <param name="list">数据集合</param>
|
/// <param name="startRow">开始插入行索引</param>
|
/// <param name="func">循环赋值的方法</param>
|
public static void ShiftRows<T>(string SavePath, List<T> list, int startRow, Func<IRow, int, bool> func)//开始插入行索引
|
{
|
//创建Excel文件的对象
|
FileStream fs = new FileStream(SavePath, FileMode.Open, FileAccess.ReadWrite);
|
XSSFWorkbook workbook = new XSSFWorkbook(fs);
|
fs.Close();
|
ISheet sheet = workbook.GetSheetAt(0);
|
if (list != null && list.Count > 1)
|
{
|
//插入行
|
sheet.ShiftRows(startRow, sheet.LastRowNum, list.Count - 1, true, false);
|
var rowSource = sheet.GetRow(startRow - 1);
|
var rowStyle = rowSource.RowStyle;//获取当前行样式
|
for (int i = startRow; i < startRow + list.Count - 1; i++)
|
{
|
var rowInsert = sheet.CreateRow(i);
|
if (rowStyle != null) rowInsert.RowStyle = rowStyle;
|
rowInsert.Height = rowSource.Height;
|
for (int col = 0; col < rowSource.LastCellNum; col++)
|
{
|
var cellsource = rowSource.GetCell(col);
|
var cellInsert = rowInsert.CreateCell(col);
|
var cellStyle = cellsource.CellStyle;
|
//设置单元格样式
|
if (cellStyle != null)
|
cellInsert.CellStyle = cellsource.CellStyle;
|
}
|
}
|
}
|
//绑定数据
|
for (int j = startRow; j < startRow + list.Count; j++)
|
{
|
//单元格赋值等其他代码
|
IRow r = sheet.GetRow(j);
|
bool ls = func(r, j);
|
// 标准写法 r.Cells[0].SetCellValue(j + 1);
|
}
|
FileStream success = new FileStream(SavePath, FileMode.Create);
|
workbook.Write(success);
|
success.Close();
|
workbook.Close();
|
}
|
|
/// <summary>
|
/// 数据替换
|
/// </summary>
|
/// <typeparam name="T">实体 替换默认为 {Name} </typeparam>
|
/// <param name="entity"></param>
|
/// <param name="FromSourcePath"></param>
|
/// <param name="ToSourcePath"></param>
|
/// <returns></returns>
|
public static string ExcelReplace<T>(T entity, string FromSourcePath, string ToSourcePath)
|
{
|
FileStream fs = new FileStream(FromSourcePath, FileMode.Open, FileAccess.Read);
|
IWorkbook workbook = WorkbookFactory.Create(fs);
|
ISheet sheet = workbook.GetSheetAt(0);
|
IRow firstRow = sheet.GetRow(0);
|
int cellCount = firstRow.LastCellNum;
|
int rowCount = sheet.LastRowNum;
|
for (int i = 0; i < rowCount; i++)
|
{
|
IRow row = sheet.GetRow(i);
|
for (int j = 0; j < cellCount; j++)
|
{
|
NPOI.SS.UserModel.ICell cell = row.GetCell(j);
|
ReplaceExcelKey<T>(entity, cell);
|
}
|
}
|
byte[] buffer = new byte[1024 * 5];
|
if (File.Exists(ToSourcePath)) File.Delete(ToSourcePath);
|
var file = new FileStream(ToSourcePath, FileMode.Create);
|
workbook.Write(file);
|
file.Close();
|
return ToSourcePath;
|
}
|
|
private static void ReplaceExcelKey<T>(T etity, NPOI.SS.UserModel.ICell cell)
|
{
|
Type entityType = typeof(T);
|
PropertyInfo[] properties = entityType.GetProperties();
|
if (cell != null)
|
{
|
var text = "";
|
if (cell.CellType == CellType.String)//这⾥根据不同的类型进⾏不同的处理
|
{
|
text = cell.ToString();
|
}
|
if (text != "")
|
{
|
foreach (var p in properties)
|
{
|
string propteryName = "{" + p.Name + "}";
|
object value = p.GetValue(etity);
|
if (value == null)
|
{
|
value = "";
|
}
|
if (text.Contains(propteryName))
|
{
|
var v = value.ToString();
|
text = text.Replace(propteryName, v);
|
}
|
cell.SetCellValue(text);
|
}
|
}
|
|
}
|
}
|
|
/// <summary>
|
/// excel转image
|
/// </summary>
|
/// <param name="filename"></param>
|
/// <param name="imagepath"></param>
|
public static string ChangeExcel2Image(string filename, string imagepath)
|
{
|
string filePath = ExcelOperation.ProjectPath() + "DownloadExcel\\";
|
if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
|
var saveName = filePath + $"{DateTime.Now.ToString("yyyyMMddhhmmss")}" + imagepath;
|
Workbook workbook = new Workbook();
|
workbook.LoadFromFile(filename);
|
Worksheet sheet = workbook.Worksheets[0];
|
//sheet.SaveToImage(imagepath); //图片后缀.bmp ,imagepath自己设置
|
// //加载生成图表后的Excel文档
|
//workbook.LoadFromFile("ColumnChart.xlsx");
|
sheet.ToImage(0, 0, 0, 0).Save(saveName, ImageFormat.Jpeg);
|
return saveName;
|
}
|
|
public static string ExcelRepleImages(string TagetPath,string imgpath)
|
{
|
string fileExtensionName = Path.GetExtension(TagetPath);
|
if (fileExtensionName.ToLower() == ".xlsx")
|
{
|
return InsertImageToXLSXExcel(TagetPath, imgpath);
|
}
|
if (fileExtensionName.ToLower() == ".xls")
|
{
|
return InsertImageToXLSExcel(TagetPath, imgpath);
|
}
|
return "";
|
}
|
/// <summary>
|
/// .xlsx后缀的Excel文件添加图片
|
/// </summary>
|
/// <param name="excelPath"></param>
|
/// <param name="imgPath"></param>
|
private static string InsertImageToXLSXExcel(string excelPath, string imgPath)
|
{
|
try
|
{
|
using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流
|
{
|
//创建工作簿
|
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
|
//获取第一个工作表（下标从0起）
|
XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
|
//获取指定图片的字节流
|
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
|
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
|
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
|
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
|
int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.PNG);
|
//创建画布
|
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
|
//设置图片坐标与大小
|
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
|
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第六列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
|
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
|
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
|
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
|
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
|
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 20, 15, 0, 2, 2, 6);
|
//正式在指定位置插入图片
|
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
|
//创建一个新的Excel文件流，可以和原文件名不一样，
|
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
|
FileStream file = new FileStream("E:/图片3333333333.xlsx", FileMode.Create);
|
//将已插入图片的Excel流写入新创建的Excel中
|
xssfworkbook.Write(file);
|
file.Close();
|
//关闭工作簿
|
xssfworkbook.Close();
|
}
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
return excelPath;
|
}
|
|
/// <summary>
|
/// .xls后缀的Excel文件添加图片
|
/// </summary>
|
/// <param name="TagetPath"></param>
|
/// <param name="imgpath"></param>
|
private static string InsertImageToXLSExcel(string TagetPath, string imgpath)
|
{
|
FileStream file = new FileStream(TagetPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
|
IWorkbook hssfworkbook = new HSSFWorkbook(file);
|
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("领料单");
|
|
System.Drawing.Image image = System.Drawing.Image.FromFile(imgpath);
|
MemoryStream ms = new MemoryStream();
|
image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
|
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
|
//设置图片坐标与大小
|
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
|
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第六列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
|
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
|
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
|
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
|
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
|
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 20, 15, 0, 2, 2, 6);
|
anchor.AnchorType = AnchorType.MoveDontResize;
|
int index = hssfworkbook.AddPicture(ms.ToArray(), PictureType.PNG);
|
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, index);
|
|
int idex1 = hssfworkbook.GetSheetIndex("领料单");
|
hssfworkbook.SetSheetName(idex1, "领料单");
|
|
FileStream fs2 = new FileStream("E:/图片操2222222作.xls", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
|
hssfworkbook.Write(fs2);
|
fs2.Close();
|
image.Dispose();
|
file.Close();
|
return TagetPath;
|
}
|
}
|
