获取excel中每行的图片
来源:互联网 发布:mac 虚拟机装office 编辑:程序博客网 时间:2024/06/06 03:48
最近有个项目需要将excel中每行插入图片导入数据库。首次导图片参考了好多资料,今天把自己的整理一份,以供今后参考并和大家分享。
先定义一个图片实体类
public class PicturesInfo { public int MinRow { get; set; } public int MaxRow { get; set; } public int MinCol { get; set; } public int MaxCol { get; set; } public Byte[] PictureData { get; private set; } public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, Byte[] pictureData) { this.MinRow = minRow; this.MaxRow = maxRow; this.MinCol = minCol; this.MaxCol = maxCol; this.PictureData = pictureData; } }
excel插件扩展类
public static class NpoiExtend { public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet) { return sheet.GetAllPictureInfos(null, null, null, null); } public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal = true) { if (sheet is HSSFSheet) //2003 { return GetAllPictureInfos((HSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal); } else if (sheet is XSSFSheet) //2007 { return GetAllPictureInfos((XSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal); } else { throw new Exception("未处理类型,没有为该类型添加:GetAllPicturesInfos()扩展方法!"); } } private static List<PicturesInfo> GetAllPictureInfos(HSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal) { List<PicturesInfo> picturesInfoList = new List<PicturesInfo>(); var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer; if (null != shapeContainer) { var shapeList = shapeContainer.Children; foreach (var shape in shapeList) { if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor) { var picture = (HSSFPicture)shape; var anchor = (HSSFClientAnchor)shape.Anchor; if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal)) { picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data)); } } } } return picturesInfoList; } private static List<PicturesInfo> GetAllPictureInfos(XSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal) { List<PicturesInfo> picturesInfoList = new List<PicturesInfo>(); var documentPartList = sheet.GetRelations(); foreach (var documentPart in documentPartList) { if (documentPart is XSSFDrawing) { var drawing = (XSSFDrawing)documentPart; var shapeList = drawing.GetShapes(); foreach (var shape in shapeList) { if (shape is XSSFPicture) { var picture = (XSSFPicture)shape; //var anchor = picture.GetPreferredSize(); var anchor =(XSSFClientAnchor) shape.GetAnchor(); if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal)) { picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data)); } } } } } return picturesInfoList; } private static bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol, int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal) { int _rangeMinRow = rangeMinRow ?? pictureMinRow; int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow; int _rangeMinCol = rangeMinCol ?? pictureMinCol; int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol; if (onlyInternal) { return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow && _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol); } else { return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >= Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) && (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >= Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol))); } } }
应用测试
#region 应用测试 public static List<PicturesInfo> GetTableFromExcel(HttpPostedFile fileData) { try { if (fileData != null) { if (fileData.ContentLength == 0) { return null; } } Stream streamfile = fileData.InputStream; XSSFWorkbook hssfworkbook = new XSSFWorkbook(streamfile);//2007以上版本 NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0);//第一行为标题行 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 //handling header. for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum+1; j <= cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = GetCellValue(row.GetCell(j)); } } table.Rows.Add(dataRow); } List<PicturesInfo> picture_list = new List<PicturesInfo>(); picture_list = NpoiExtend.GetAllPictureInfos(sheet, 1, rowCount, 0, cellCount, false); return picture_list; } catch (Exception ex) { return null; } } /// <summary> /// 根据Excel列类型获取列的值 /// </summary> /// <param name="cell">Excel列</param> /// <returns></returns> private static string GetCellValue(ICell cell) { if (cell == null) return string.Empty; switch (cell.CellType) { case CellType.Blank: return string.Empty; case CellType.Boolean: return cell.BooleanCellValue.ToString(); case CellType.Error: return cell.ErrorCellValue.ToString(); case CellType.Numeric: case CellType.Unknown: default: return cell.ToString(); case CellType.String: return cell.StringCellValue; case CellType.Formula: try { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return cell.ToString(); } catch { return cell.NumericCellValue.ToString(); } } } #endregion
阅读全文
0 0
- 获取excel中每行的图片
- NPOI导入每行带图片的EXCEL
- poi 获取excel中图片
- 获取UITableView每行中不同的UITextField输入的内容
- 获取textview每行的内容
- TextView获取每行的内容
- 关于POI对Excel2007中图片的处理,获取图片在excel中对应的位置
- poi获取Excel表中图片位置
- 获取BMP每行像素的字节数!
- 获取jqGrid中每行的子元素td并修改其属性
- Excel批量去除每行的前面几个字符
- VB中设置MSHFlexGrid每行的颜色
- 遍历DataList中每行的简单方法
- SQL查询每行中最大值的技巧
- SQL查询每行中最大值的技巧
- vim中删除每行行尾的空格
- awk 计算文件中每行的值
- kettle的job中执行每行
- 解决Error:All flavors must now belong to a named flavor dimension. Learn more at https://d.android.com
- AlertDialog 源码解析一
- Null value was assigned to a property of primitive type setter
- Java中多态性的实现
- 用pandas里的groupby函数实现把一个大文件按某一列的类型分成多个文件
- 获取excel中每行的图片
- iOS不同屏幕适配字体大小
- js继承方式
- 【CCF】201509_3 模板生成器
- cout的格式化控制
- leetcode160-Intersection of Two Linked Lists
- 自定义URL用scheme唤起Activity
- Java多线程编程:Callable、Future和FutureTask浅析
- SQL-已存在数据的表主键设置递增