public string Getbase64PictureTest1(ISheet sheet)
{
List <PicturesInfo> picturesInfoList = sheet.GetAllPictureInfos();
JArray picturesInfoListObj = new JArray();
foreach (var picturesInfo in picturesInfoList)
{
JObject picturesInfoObj = new JObject();
picturesInfoObj.Add("startrow", picturesInfo.MinRow);
picturesInfoObj.Add("startcol", picturesInfo.MinCol);
picturesInfoObj.Add("endrow", picturesInfo.MaxRow);
picturesInfoObj.Add("endcol", picturesInfo.MaxCol);
picturesInfoObj.Add("picturedata", Convert.ToBase64String(picturesInfo.PictureData));
switch (picturesInfo.AnchorType)
{
case AnchorType.MoveAndResize:
picturesInfoObj.Add("AnchorType", 0);
break;
case AnchorType.MoveDontResize:
picturesInfoObj.Add("AnchorType", 2);
break;
case AnchorType.DontMoveAndResize:
picturesInfoObj.Add("AnchorType", 3);
break;
default:
break;
}
picturesInfoListObj.Add(picturesInfoObj);
}
return(picturesInfoListObj.ToString());
}
// 获取图片信息
public JArray Getbase64Picture(ISheet sheet)
{
List <PicturesInfo> picturesInfoList = sheet.GetAllPictureInfos();
JArray picturesInfoListObj = new JArray();
foreach (var picturesInfo in picturesInfoList)
{
JObject picturesInfoObj = new JObject();
picturesInfoObj.Add("startrow", picturesInfo.MinRow);
picturesInfoObj.Add("startcol", picturesInfo.MinCol);
picturesInfoObj.Add("endrow", picturesInfo.MaxRow);
picturesInfoObj.Add("endcol", picturesInfo.MaxCol);
picturesInfoObj.Add("picturedata", Convert.ToBase64String(picturesInfo.PictureData));
picturesInfoListObj.Add(picturesInfoObj);
}
return(picturesInfoListObj);
}
// 获取图片信息
public string Getbase64PictureTest(int k)
{
k = k - 1;
ISheet sheet = wb.GetSheetAt(k);
List <PicturesInfo> picturesInfoList = sheet.GetAllPictureInfos();
JArray picturesInfoListObj = new JArray();
foreach (var picturesInfo in picturesInfoList)
{
JObject picturesInfoObj = new JObject();
picturesInfoObj.Add("startrow", picturesInfo.MinRow);
picturesInfoObj.Add("startcol", picturesInfo.MinCol);
picturesInfoObj.Add("endrow", picturesInfo.MaxRow);
picturesInfoObj.Add("endcol", picturesInfo.MaxCol);
picturesInfoObj.Add("picturedata", Convert.ToBase64String(picturesInfo.PictureData));
picturesInfoListObj.Add(picturesInfoObj);
}
return(picturesInfoListObj.ToString());
}
public static List <PicturesInfo> GetAllPictureInfos(this ISheet sheet)
{
return(sheet.GetAllPictureInfos(null, null, null, null));
}
/// <summary>
/// 将excel文件内容读取到DataTable数据表中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadExcelToDataTable(string fileName, out List <PicturesInfo> picturesInfos, string sheetName = null, bool isFirstRowColumn = true)
{
picturesInfos = new List <PicturesInfo>();
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
if (!File.Exists(fileName))
{
return(null);
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//根据文件流创建excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fs);
//IWorkbook workbook = new HSSFWorkbook(fs);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
//获取图片信息
picturesInfos = sheet.GetAllPictureInfos();
IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.ToString().Trim();
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
dataRow[j] = GetCellValue(row.GetCell(j));
//var cell = row.GetCell(j);
//if (cell != null) //同理，没有数据的单元格都默认是null
//{
// dataRow[j] = row.GetCell(j).ToString();
//}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 复制行
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="startRowIndex">起始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <returns></returns>
public static int CopyRows(this ISheet sheet, int startRowIndex, int endRowIndex)
{
int span = endRowIndex - startRowIndex + 1;
int newStartRowIndex = startRowIndex + span;
// 插入空行
sheet.InsertRows(newStartRowIndex, span);
// 复制行
for (var i = startRowIndex; i <= endRowIndex; i++)
{
IRow sourceRow = sheet.GetRow(i);
IRow targetRow = sheet.GetRow(i + span);
targetRow.Height = sourceRow.Height;
targetRow.ZeroHeight = sourceRow.ZeroHeight;
// 复制单元格
foreach (ICell sourceCell in sourceRow.Cells)
{
ICell targetCell = targetRow.GetCell(sourceCell.ColumnIndex);
if (null == targetCell)
{
targetCell = targetRow.CreateCell(sourceCell.ColumnIndex);
}
if (null != sourceCell.CellStyle)
{
targetCell.CellStyle = sourceCell.CellStyle;
}
if (null != sourceCell.CellComment)
{
targetCell.CellComment = sourceCell.CellComment;
}
if (null != sourceCell.Hyperlink)
{
targetCell.Hyperlink = sourceCell.Hyperlink;
}
var cfrs = sourceCell.GetConditionalFormattingRules();// 复制条件样式
if (null != cfrs && cfrs.Length > 0)
{
targetCell.AddConditionalFormattingRules(cfrs);
}
targetCell.SetCellType(sourceCell.CellType);
// 复制值
switch (sourceCell.CellType)
{
case CellType.Numeric:
targetCell.SetCellValue(sourceCell.NumericCellValue);
break;
case CellType.String:
targetCell.SetCellValue(sourceCell.StringCellValue);
break;
case CellType.Formula:
targetCell.SetCellValue(sourceCell.CellFormula);
break;
case CellType.Blank:
targetCell.SetCellValue(sourceCell.StringCellValue);
break;
case CellType.Boolean:
targetCell.SetCellValue(sourceCell.BooleanCellValue);
break;
case CellType.Error:
targetCell.SetCellValue(sourceCell.ErrorCellValue);
break;
}
}
}
// 获取模板行内的合并区域
var regions = sheet.GetMergedRegionInfos(startRowIndex, endRowIndex, null, null);
// 复制合并区域
foreach (var regionInfo in regions)
{
regionInfo.FirstRow += span;
regionInfo.LastRow += span;
sheet.AddMergedRegion(regionInfo);
}
// 获取模板行内的图片
var pictures = sheet.GetAllPictureInfos(startRowIndex, endRowIndex, null, null);
// 复制图片
foreach (var pictureInfo in pictures)
{
pictureInfo.FirstRow += span;
pictureInfo.LastRow += span;
sheet.AddPicture(pictureInfo);
}
return(span);
}
private void StartExcelImport(object filePathObj)
{
string filePath = @"C:\Users\Tsu\Desktop\2个插件\测试.xls";
IWorkbook workbook = null;
ISheet sheet = null;
try
{
var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (filePath.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (filePath.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (sheet == null) //取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
//excel总行数
int rowCount = sheet.LastRowNum;
tIPViewModel.MaxLength = rowCount;
var pictureList = sheet.GetAllPictureInfos();
for (int i = 2; i < rowCount; i++)
{
IRow curRow = sheet.GetRow(i);
if (curRow.Cells.Count == 0)
{
Console.WriteLine("NO." + i + "行为空行!");
continue;
}
FaceObj _FaceObj = new FaceObj();//实例OrderInf，添加到新的窗口的list中
_FaceObj.Tmplate = new List <FaceObjTemplate>();
//人脸对象添加时间
DateTime dt0 = Convert.ToDateTime(DateTime.Now);
TimeSpan dt0Span = new TimeSpan(dt0.Ticks);
DateTime dt01 = new DateTime(1970, 1, 1);
TimeSpan dt01Span = new TimeSpan(dt01.Ticks);
long longDPTemplateStarTime0 = Convert.ToInt64(dt0Span.TotalSeconds - dt01Span.TotalSeconds);
_FaceObj.DTm = longDPTemplateStarTime0;
#region 读取excel 生成人脸对象
foreach (var item in ExcelColNo)
{
switch (ExcelColNo.IndexOf(item))
{
//人脸uuid
case 0:
string StringName = System.Guid.NewGuid().ToString();
StringName = StringName.Replace("-", "");
_FaceObj.TcUuid = StringName;
break;
//姓名
case 1:
if (item != 0)
{
_FaceObj.TcName = curRow.GetCell(item).ToString();
Console.WriteLine(i + _FaceObj.TcName);
}
else
{
_FaceObj.TcName = _FaceObj.TcUuid;
}
break;
//人脸首选模板标识序号
case 2:
_FaceObj.NMain_ftID = 0;
break;
//类型
case 3:
if (item != 0)
{
_FaceObj.NType = 0;
}
break;
//敏感等级
case 4:
if (item != 0)
{
_FaceObj.NSST = Convert.ToInt32(curRow.GetCell(item).ToString());
}
break;
//额外信息
case 5:
if (item != 0)
{
_FaceObj.NExten = Convert.ToInt32(curRow.GetCell(item).ToString());
}
break;
//性别
case 6:
if (item != 0)
{
_FaceObj.NSex = 0;
}
break;
//年龄
case 7:
if (item != 0)
{
_FaceObj.NAge = Convert.ToInt32(curRow.GetCell(item).ToString());
}
break;
//备注
case 8:
if (item != 0)
{
_FaceObj.TcRemarks = curRow.GetCell(item).ToString();
}
break;
//模板1
case 9:
if (item != 0)
{
FaceObjTemplate _FaceObjTemplate = new FaceObjTemplate();
var picture = GetPictureByRowCol(pictureList, i, item);
if (picture != null)
{
_FaceObj.Tmplate.Add(_FaceObjTemplate);
_FaceObj.Tmplate.Last().Img = picture.PictureData;
}
}
break;
//模板2
case 10:
if (item != 0)
{
FaceObjTemplate _FaceObjTemplate = new FaceObjTemplate();
var picture = GetPictureByRowCol(pictureList, i, item);
if (picture != null)
{
_FaceObj.Tmplate.Add(_FaceObjTemplate);
_FaceObj.Tmplate.Last().Img = picture.PictureData;
}
}
break;
//模板3
case 11:
if (item != 0)
{
FaceObjTemplate _FaceObjTemplate = new FaceObjTemplate();
var picture = GetPictureByRowCol(pictureList, i, item);
if (picture != null)
{
_FaceObj.Tmplate.Add(_FaceObjTemplate);
_FaceObj.Tmplate.Last().Img = picture.PictureData;
}
}
break;
//模板4
case 12:
if (item != 0)
{
FaceObjTemplate _FaceObjTemplate = new FaceObjTemplate();
var picture = GetPictureByRowCol(pictureList, i, item);
if (picture != null)
{
_FaceObj.Tmplate.Add(_FaceObjTemplate);
_FaceObj.Tmplate.Last().Img = picture.PictureData;
}
}
break;
//模板5
case 13:
if (item != 0)
{
FaceObjTemplate _FaceObjTemplate = new FaceObjTemplate();
var picture = GetPictureByRowCol(pictureList, i, item);
if (picture != null)
{
_FaceObj.Tmplate.Add(_FaceObjTemplate);
_FaceObj.Tmplate.Last().Img = picture.PictureData;
}
}
break;
}
}
#endregion
//异步上传人脸对象
while (true)
{
if (upingCount <= maxUpCount)
{
//Console.WriteLine("第" + i + "次运行");
AddFaceObjDelegate.BeginInvoke(_FaceObj, new AsyncCallback(PicHandle), _FaceObj);
upingCount++;
break;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// excel转换成实体
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static Dictionary <string, ExcelEntity> ExcelToExcelEntity(string filePath)
{
Dictionary <string, ExcelEntity> excelEntity = new Dictionary <string, ExcelEntity>();
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null;
bool isO3Excel = false;
if (filePath.Last() == 's')
{
workbook = new HSSFWorkbook(fs);
isO3Excel = true;
}
else
{
workbook = new XSSFWorkbook(fs);
isO3Excel = false;
}
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
string sheetName = sheet.SheetName;
excelEntity.Add(sheetName, new ExcelEntity());
//获取表格信息
for (int rowIndex = sheet.FirstRowNum; rowIndex <= sheet.LastRowNum; rowIndex++)
{
for (int colIndex = sheet.GetRow(rowIndex).FirstCellNum; colIndex <= sheet.GetRow(rowIndex).LastCellNum; colIndex++)
{
if (excelEntity[sheetName].CellEntities == null)
{
excelEntity[sheetName].CellEntities = new List <CellEntity>();
}
CellEntity cell = new CellEntity();
ICell excelCell = null;
if (isO3Excel)
{
excelCell = sheet.GetRow(rowIndex).GetCell(colIndex) as HSSFCell;
}
else
{
excelCell = sheet.GetRow(rowIndex).GetCell(colIndex) as XSSFCell;
}
cell.Cell = excelCell;
cell.FirstRow = rowIndex;
cell.FirstCol = colIndex;
int rowSpan;
int colSpan;
cell.IsMerge = ExcelHelper.IsMergeCell(sheet, rowIndex, colIndex, out rowSpan, out colSpan);
cell.RowSpan = rowSpan;
cell.ColSpan = colSpan;
excelEntity[sheetName].CellEntities.Add(cell);
}
}
//获取excel的图片信息
excelEntity[sheetName].Pictures = sheet.GetAllPictureInfos();
}
}
return(excelEntity);
}
/// <summary>
/// 获取所有图片信息
/// 参考地址：https://www.cnblogs.com/hanzhaoxin/p/4442369.html
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public static IEnumerable <PictureInfo> GetAllPictureInfo(this ISheet sheet)
{
return(sheet.GetAllPictureInfos(null, null, null, null));
}
public static Dictionary <int, PicturesInfo> GetAllPictureInfos(this ISheet sheet)
{
return(sheet.GetAllPictureInfos(null, null, null, null));
}