public static string CreateNewExcel(string file, List <DataTable> listdt) { List <string> sheetName = ExcelHelper.fnGetSheet(file); using (SpreadsheetDocument excel = SpreadsheetDocument.Open(file, true)) { //读取多个Sheet表 for (int i = 0; i < sheetName.Count; i++) { //获取一个工作表的数据 WorksheetPart worksheet = ExcelHelper.GetWorksheetPartByName(excel, sheetName[i]); //单元格行 foreach (OpenXmlPowerTools.Row row in worksheet.Rows()) { //单元格列 foreach (OpenXmlPowerTools.Cell cell in row.Cells()) { //多个DataTable for (int j = 0; j < listdt.Count; j++) { //DataTable列名 for (int col = 0; col < listdt[j].Columns.Count; col++) { string colName = "{" + listdt[j].Columns[col].ColumnName + "}"; if (string.IsNullOrEmpty(cell.SharedString)) { break; } //判断单元格数据是否与列名一致 if (cell.SharedString.ToString().ToLower() == colName.ToString().ToLower()) { //循环添加行 for (int r = 0; r < listdt[j].Rows.Count; r++) { ExcelHelper.InsertDataIntoWorkSheet((uint)(int.Parse(row.RowId) + r), (uint)cell.ColumnIndex, listdt[j].Rows[r][col].ToString(), cell.Style, excel.WorkbookPart, worksheet); } } } } } } } } return(null); }
public static ExcelEntity ReadExcelDetail(SpreadsheetDocument excel, List <string> sheetName, string file) { ExcelEntity ee = new ExcelEntity(); #region SheetStyle公用样式表 #region 主题色 ThemePart themPart = excel.WorkbookPart.ThemePart; var themColor = ThemeColor.GetThemeColorList(themPart); #endregion //获取样式设置 Stylesheet styleSheet = excel.WorkbookPart.WorkbookStylesPart.Stylesheet; #region 样式列表 CellFormats cellFormats = styleSheet.CellFormats; List <CellFormatsList> cellFormatsList = new List <CellFormatsList>(); int index = 0; foreach (CellFormat cell in cellFormats.ChildElements) { if (cell != null) { CellFormatsList cfl = new CellFormatsList(); cfl.styleIndex = index; if (cell.NumberFormatId != null) { cfl.numFmtId = int.Parse(cell.NumberFormatId); } if (cell.FontId != null) { cfl.fontId = int.Parse(cell.FontId); } if (cell.FillId != null) { cfl.fillId = int.Parse(cell.FillId); } if (cell.BorderId != null) { cfl.borderId = int.Parse(cell.BorderId); } if (cell.ApplyAlignment != null) { cfl.applyAlignment = int.Parse(cell.ApplyAlignment); } if (cell.ApplyBorder != null) { cfl.applyBorder = int.Parse(cell.ApplyBorder); } if (cell.ApplyFont != null) { cfl.applyFont = int.Parse(cell.ApplyFont); } if (cell.ApplyNumberFormat != null) { cfl.applyNumberFormat = int.Parse(cell.ApplyNumberFormat); } if (cell.Alignment != null) { string ver = cell.Alignment.Vertical; string hor = cell.Alignment.Horizontal; if (!string.IsNullOrEmpty(ver)) { if (ver == "center") { cfl.vertical = "htMiddle"; } else { cfl.vertical = "ht" + ver.Substring(0, 1).ToUpper() + ver.Substring(1, ver.Length - 1); } } else { cfl.vertical = "htBottom"; } if (!string.IsNullOrEmpty(hor)) { cfl.horizontal = "ht" + hor.Substring(0, 1).ToUpper() + hor.Substring(1, hor.Length - 1); } else { cfl.horizontal = "htLeft"; } } cellFormatsList.Add(cfl); index++; } } ee.CellFormatsList = cellFormatsList; #endregion #region 数据类型列表 NumberingFormats numberFormats = styleSheet.NumberingFormats; List <NumFmtsList> numFmtList = new List <NumFmtsList>(); if (numberFormats != null) { foreach (NumberingFormat cell in numberFormats.ChildElements) { NumFmtsList nfl = new NumFmtsList(); if (cell.NumberFormatId != null) { nfl.numFmtId = (int)cell.NumberFormatId.Value; } if (cell.FormatCode != null) { nfl.formatCode = cell.FormatCode.Value; } numFmtList.Add(nfl); } } ee.NumFmtsList = numFmtList; #endregion #region 字体样式 Fonts fonts = styleSheet.Fonts; List <FontsList> fontsList = new List <FontsList>(); foreach (Font cell in fonts.ChildElements) { FontsList fl = new FontsList(); if (cell.FontSize != null) { fl.fontsize = cell.FontSize.Val + "px"; } if (cell.FontName != null) { fl.fontname = cell.FontName.Val; } if (cell.Color != null) { if (cell.Color.Rgb != null && !string.IsNullOrEmpty(cell.Color.Rgb.ToString())) { fl.color = "#" + cell.Color.Rgb.ToString().Substring(2, 6); } } if (cell.Italic != null) { fl.italic = "italic"; } if (cell.Bold != null) { fl.bold = "bold"; } if (cell.Underline != null) { fl.underline = "underline"; } fontsList.Add(fl); } ee.FontsList = fontsList; #endregion #region 填充色样式 Fills fills = styleSheet.Fills; List <FillsList> fillsList = new List <FillsList>(); foreach (Fill cell in fills.ChildElements) { FillsList fl = new FillsList(); if (cell.PatternFill != null) { fl.patternType = cell.PatternFill.PatternType; if (cell.PatternFill.ForegroundColor != null) { if (cell.PatternFill.ForegroundColor.Rgb != null) { fl.fgColor = "#" + cell.PatternFill.ForegroundColor.Rgb.ToString().Substring(2, 6); } if (cell.PatternFill.ForegroundColor.Theme != null) { UInt32Value themeIndex = cell.PatternFill.ForegroundColor.Theme; DoubleValue tint = cell.PatternFill.ForegroundColor.Tint; if (tint != null) { var newColor = ThemeColor.ThemColorDeal(themeIndex, tint, themColor[themeIndex]); fl.fgColor = "#" + newColor.Name.Substring(2, 6); fl.fgColor = "#" + newColor.Name.Substring(2, 6); } else { fl.fgColor = "#" + themColor[themeIndex]; fl.fgColor = "#" + themColor[themeIndex]; } } } } fillsList.Add(fl); } ee.FillsList = fillsList; #endregion #region 边框样式 Borders borders = styleSheet.Borders; List <BordersList> bordersList = new List <BordersList>(); var defaultBorderStyle = "1px solid #000"; foreach (Border cell in borders.ChildElements) { BordersList bl = new BordersList(); if (cell.LeftBorder != null) { if (cell.LeftBorder.Style != null) { bl.left = defaultBorderStyle; } } if (cell.RightBorder != null) { if (cell.RightBorder.Style != null) { bl.right = defaultBorderStyle; } } if (cell.TopBorder != null) { if (cell.TopBorder.Style != null) { bl.top = defaultBorderStyle; } } if (cell.BottomBorder != null) { if (cell.BottomBorder.Style != null) { bl.bottom = defaultBorderStyle; } } if (cell.DiagonalBorder != null) { if (cell.DiagonalBorder.Style != null) { bl.diagonal = cell.DiagonalBorder.Style; } } bordersList.Add(bl); } ee.BordersList = bordersList; #endregion #endregion List <SheetDataList> sheetDataList = new List <SheetDataList>(); List <PictureInfo> pictures = null; for (int i = 0; i < sheetName.Count; i++) { SheetDataList sdl = new SheetDataList(); int RowCount = 0; int ColumnCount = 0; //得到工作表dimension WorksheetPart worksheet = ExcelHelper.GetWorksheetPartByName(excel, sheetName[i]); #region 获取单个Sheet表的数据 #region //批注 WorksheetCommentsPart comments = worksheet.WorksheetCommentsPart; List <CommentCellsList> commentLists = new List <CommentCellsList>(); if (comments != null) { CommentList commentList = (CommentList)comments.Comments.ChildElements[1]; //批注列表 foreach (Comment comment in commentList.ChildElements) { CommentCellsList ccl = new CommentCellsList(); //坐标 var cell = GetCellXY(comment.Reference).Split('_'); var columnRow = int.Parse(cell[0].ToString()) - 1; var columnCol = GetColumnIndex(cell[1]); //批注内容 var commentVal = comment.InnerText; ccl.row = columnRow; ccl.col = columnCol; ccl.comment = comment.InnerText; //var commentCell = "{\"Row\":\""+ columnRow + "\",\"Col\":\"" + columnCol + ",\"Comment\":\"" + commentVal + "\"}"; commentLists.Add(ccl); } } sdl.Comments = commentLists; #endregion #region //获取合并单元格 IEnumerable <MergeCells> mergeCells = worksheet.Worksheet.Elements <MergeCells>(); List <MergeCellsList> mergeCellList = new List <MergeCellsList>(); if (mergeCells.Count() > 0) { for (int k = 0; k < mergeCells.First().ChildElements.Count; k++) { MergeCell mergeCell = (MergeCell)mergeCells.First().ChildElements[k]; var reference = mergeCell.Reference.ToString().Split(':'); var startCell = GetCellXY(reference[0]).Split('_'); var endCell = GetCellXY(reference[1]).Split('_'); MergeCellsList mcl = new MergeCellsList(); mcl.row = int.Parse(startCell[0]) - 1; mcl.rowspan = int.Parse(endCell[0]) - int.Parse(startCell[0]) + 1; mcl.col = GetColumnIndex(startCell[1]); mcl.colspan = GetColumnIndex(endCell[1]) - mcl.col + 1; //mcl.reference = mergeCell.Reference.ToString(); mergeCellList.Add(mcl); } } sdl.MergeCells = mergeCellList; #endregion //获取超链接列表 //var hyperlinks = worksheet.RootElement.Descendants<Hyperlinks>().First().Cast<Hyperlink>(); #region //读取图片 DrawingsPart drawingPart = worksheet.GetPartsOfType <DrawingsPart>().ToList().FirstOrDefault(); pictures = new List <PictureInfo>(); if (drawingPart != null) { int tempIndex = 1; foreach (var part in drawingPart.Parts) { PictureInfo pic = new PictureInfo(); ImagePart imgPart = (ImagePart)part.OpenXmlPart; System.Drawing.Image img1 = System.Drawing.Image.FromStream(imgPart.GetStream()); var newFilename = Guid.NewGuid().ToString("N") + ".png"; string[] sArray = Regex.Split(file, "UserFile", RegexOptions.IgnoreCase); string newFilePath = sArray[0] + "_Temp\\" + newFilename; img1.Save(newFilePath); //pic.Image = img1; pic.RefId = part.RelationshipId;//"rId" + imgPart.Uri.ToString().Split('/')[3].Split('.')[0].Substring(5); pic.ImageUrl = newFilePath; pic.ImageName = newFilename; pic.ImgHeight = img1.Height; pic.ImgWidth = img1.Width; pictures.Add(pic); tempIndex++; } //获取图片定位 var worksheetDrawings = drawingPart.WorksheetDrawing.Where(c => c.ChildElements.Any (a => a.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture")).ToList(); foreach (var worksheetDrawing in worksheetDrawings) { if (worksheetDrawing.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor") { TwoCellAnchor anchor = (TwoCellAnchor)worksheetDrawing; DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picDef = (DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture) anchor.ChildElements.FirstOrDefault(c => c.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture"); if (picDef != null) { var embed = picDef.BlipFill.Blip.Embed; if (embed != null) { var picMapping = pictures.FirstOrDefault(c => c.RefId == embed.InnerText); picMapping.FromCol = int.Parse(anchor.FromMarker.ColumnId.InnerText); picMapping.FromRow = int.Parse(anchor.FromMarker.RowId.InnerText); } } // anchor.FromMarker.RowId + anchor.FromMarker.ColumnId } } } sdl.PictureList = pictures; #endregion #region 读取表格数据 List <SheetDatas> sheetDatas = new List <SheetDatas>(); if (worksheet.Rows().Count() > 0) { RowCount = int.Parse((worksheet.Rows().Last()).RowId); } int TempColumn = 0; foreach (OpenXmlPowerTools.Row row in worksheet.Rows()) { foreach (OpenXmlPowerTools.Cell cell in row.Cells()) { #region 读取超链接 //var hyperlink = hyperlinks.SingleOrDefault(c => c.Reference.Value == cell.Column); //if (hyperlink != null) //{ // var hyperlinksRelation = worksheet.HyperlinkRelationships.SingleOrDefault(c => c.Id == hyperlink.Id); // if (hyperlinksRelation != null) // { // //这是最终我们需要的超链接 // var url = hyperlinksRelation.Uri.ToString(); // } //} #endregion TempColumn = (row.Cells().Last()).ColumnIndex; if (ColumnCount < TempColumn) { ColumnCount = TempColumn + 1; } SheetDatas sheetData = new SheetDatas(); sheetData.RowId = int.Parse(row.RowId); sheetData.ColumnId = cell.ColumnIndex; sheetData.Column = cell.Column; sheetData.Type = cell.Type; sheetData.Value = cell.Value; sheetData.SharedString = cell.SharedString; sheetData.Formula = cell.Formula; sheetData.StyleId = cell.Style; #region 样式赋值 if (sheetData.StyleId != null) { CellFormatsList cfl = cellFormatsList[(int)sheetData.StyleId]; sheetData.FontName = fontsList[cfl.fontId].fontname; sheetData.FontSize = fontsList[cfl.fontId].fontsize; sheetData.FontColor = fontsList[cfl.fontId].color; sheetData.FontBold = fontsList[cfl.fontId].bold; sheetData.Italic = fontsList[cfl.fontId].italic; sheetData.Underline = fontsList[cfl.fontId].underline; sheetData.AligmentVertical = cfl.vertical; sheetData.AligmentHorizontal = cfl.horizontal; sheetData.FillType = fillsList[cfl.fillId].patternType; sheetData.FillForegroundColor = fillsList[cfl.fillId].fgColor; sheetData.FillBackgroundColor = fillsList[cfl.fillId].bgColor; sheetData.LeftBorder = bordersList[cfl.borderId].left; sheetData.RightBorder = bordersList[cfl.borderId].right; sheetData.TopBorder = bordersList[cfl.borderId].top; sheetData.BottomBorder = bordersList[cfl.borderId].bottom; sheetData.DiagonalBorder = bordersList[cfl.borderId].diagonal; } #endregion if (cell.Style != null) { var cellf = cellFormatsList[(int)cell.Style]; if (cellf.applyNumberFormat > 0 && cell.Type == null && cell.Value != null) { //for (int n = 0; n < numFmtList.Count; n++) //{ // if (numFmtList[n].numFmtId == cellf.numFmtId|| cellf.numFmtId == 14) // { // sheetData.Type = "s"; // sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString(); // break; // } //} if (cellf.numFmtId == 58) { sheetData.Type = "s"; sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToString("M月d日"); } else if (cellf.numFmtId == 14) { sheetData.Type = "s"; sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString(); } else { for (int n = 0; n < numFmtList.Count; n++) { if (numFmtList[n].numFmtId == cellf.numFmtId) { sheetData.Type = "s"; sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString(); break; } } } } else { sheetData.Value = cell.Value; } } sheetDatas.Add(sheetData); } } if (ColumnCount < 5) { ColumnCount = 5; } if (RowCount < 20) { RowCount = 20; } sdl.SheetData = sheetDatas; #endregion sdl.SheetName = sheetName[i]; sdl.SheetId = "sheet" + (i + 1); sdl.TotalRow = (RowCount + 1); sdl.TotalColumn = ColumnCount; sheetDataList.Add(sdl); //htmlStr = EMW.API.Serializer.ObjectToString(sheetDataList);// //htmlStr = "{\"SheetData\":" + htmlStr + ",\"Comments\":" + EMW.API.Serializer.ObjectToString(commentLists) + ",\"MergeCells\":" + EMW.API.Serializer.ObjectToString(mergeCellList) + ",\"TotalRow\":" + (RowCount + 1) + ",\"TotalColumn\":" + ColumnCount + ",\"SheetName\":\"" + sheetName[i] + "\"}"; //htmlSheet.Add(htmlStr); #endregion } ee.SheetDataList = sheetDataList; return(ee); }
/// <summary> /// 读取Excel中数据,包括样式 /// </summary> /// <param name="excel"></param> /// <param name="sheetName"></param> /// <param name="file"></param> /// <returns></returns> public static List <SheetDataList> ReadExcelDetailTest(SpreadsheetDocument excel, List <string> sheetName, string file) { try { #region //获取样式设置 Stylesheet styleSheet = excel.WorkbookPart.WorkbookStylesPart.Stylesheet; //获取Excel文件主题色 ThemePart themPart = excel.WorkbookPart.ThemePart; var themColor = ThemeColor.GetThemeColorList(themPart); #region 样式列表 CellFormats cellFormats = styleSheet.CellFormats; List <CellFormatsList> cellFormatsList = new List <CellFormatsList>(); int index = 0; foreach (CellFormat cell in cellFormats.ChildElements) { if (cell != null) { CellFormatsList cfl = new CellFormatsList(); cfl.styleIndex = index; if (cell.NumberFormatId != null) { cfl.numFmtId = int.Parse(cell.NumberFormatId); } if (cell.FontId != null) { cfl.fontId = int.Parse(cell.FontId); } if (cell.FillId != null) { cfl.fillId = int.Parse(cell.FillId); } if (cell.BorderId != null) { cfl.borderId = int.Parse(cell.BorderId); } if (cell.ApplyAlignment != null) { cfl.applyAlignment = int.Parse(cell.ApplyAlignment); } if (cell.ApplyBorder != null) { cfl.applyBorder = int.Parse(cell.ApplyBorder); } if (cell.ApplyFont != null) { cfl.applyFont = int.Parse(cell.ApplyFont); } if (cell.ApplyNumberFormat != null) { cfl.applyNumberFormat = int.Parse(cell.ApplyNumberFormat); } if (cell.Alignment != null) { string ver = cell.Alignment.Vertical; string hor = cell.Alignment.Horizontal; string wra = cell.Alignment.WrapText; if (!string.IsNullOrEmpty(ver)) { if (ver == "center") { cfl.vertical = "htMiddle"; } else { cfl.vertical = "ht" + ver.Substring(0, 1).ToUpper() + ver.Substring(1, ver.Length - 1); } } else { cfl.vertical = "htBottom"; } if (!string.IsNullOrEmpty(hor)) { cfl.horizontal = "ht" + hor.Substring(0, 1).ToUpper() + hor.Substring(1, hor.Length - 1); } else { cfl.horizontal = "htLeft"; } cfl.wraptext = wra; } cellFormatsList.Add(cfl); index++; } } #endregion #region 数据类型列表 NumberingFormats numberFormats = styleSheet.NumberingFormats; List <NumFmtsList> numFmtList = new List <NumFmtsList>(); if (numberFormats != null) { foreach (NumberingFormat cell in numberFormats.ChildElements) { NumFmtsList nfl = new NumFmtsList(); if (cell.NumberFormatId != null) { nfl.numFmtId = (int)cell.NumberFormatId.Value; } if (cell.FormatCode != null) { nfl.formatCode = cell.FormatCode.Value; } numFmtList.Add(nfl); } } #endregion #region 字体样式 Fonts fonts = styleSheet.Fonts; List <FontsList> fontsList = new List <FontsList>(); foreach (Font cell in fonts.ChildElements) { FontsList fl = new FontsList(); if (cell.FontSize != null) { fl.fontsize = cell.FontSize.Val + "px"; } if (cell.FontName != null) { fl.fontname = cell.FontName.Val; } if (cell.Color != null) { if (cell.Color.Rgb != null && !string.IsNullOrEmpty(cell.Color.Rgb.ToString())) { fl.color = "#" + cell.Color.Rgb.ToString().Substring(2, 6); } } if (cell.Italic != null) { fl.italic = "italic"; } if (cell.Bold != null) { fl.bold = "bold"; } if (cell.Underline != null) { fl.underline = "underline"; } fontsList.Add(fl); } #endregion #region 填充色样式 Fills fills = styleSheet.Fills; List <FillsList> fillsList = new List <FillsList>(); foreach (Fill cell in fills.ChildElements) { FillsList fl = new FillsList(); if (cell.PatternFill != null) { fl.patternType = cell.PatternFill.PatternType; if (cell.PatternFill.ForegroundColor != null) { if (cell.PatternFill.ForegroundColor.Rgb != null) { fl.fgColor = "#" + cell.PatternFill.ForegroundColor.Rgb.ToString().Substring(2, 6); } if (cell.PatternFill.ForegroundColor.Theme != null) { UInt32Value themeIndex = cell.PatternFill.ForegroundColor.Theme; DoubleValue tint = cell.PatternFill.ForegroundColor.Tint; if (tint != null) { var newColor = ThemeColor.ThemColorDeal(themeIndex, tint, themColor[themeIndex]); fl.fgColor = "#" + newColor.Name.Substring(2, 6); fl.fgColor = "#" + newColor.Name.Substring(2, 6); } else { fl.fgColor = "#" + themColor[themeIndex]; fl.fgColor = "#" + themColor[themeIndex]; } } } } fillsList.Add(fl); } #endregion #region 边框样式 Borders borders = styleSheet.Borders; List <BordersList> bordersList = new List <BordersList>(); var defaultBorderStyle = "1px solid #000"; foreach (Border cell in borders.ChildElements) { BordersList bl = new BordersList(); if (cell.LeftBorder != null) { if (cell.LeftBorder.Style != null) { bl.left = defaultBorderStyle; } } if (cell.RightBorder != null) { if (cell.RightBorder.Style != null) { bl.right = defaultBorderStyle; } } if (cell.TopBorder != null) { if (cell.TopBorder.Style != null) { bl.top = defaultBorderStyle; } } if (cell.BottomBorder != null) { if (cell.BottomBorder.Style != null) { bl.bottom = defaultBorderStyle; } } if (cell.DiagonalBorder != null) { if (cell.DiagonalBorder.Style != null) { bl.diagonal = cell.DiagonalBorder.Style; } } bordersList.Add(bl); } #endregion #endregion List <SheetDataList> listSDL = new List <SheetDataList>(); List <PictureInfo> pictures = null; //获取多个Sheet数据和样式 for (int i = 0; i < sheetName.Count; i++) { //总行数和总列数 int RowCount = 0, ColumnCount = 0; SheetDataList sdl = new SheetDataList(); //获取一个工作表的数据 WorksheetPart worksheet = ExcelHelper.GetWorksheetPartByName(excel, sheetName[i]); #region //批注 WorksheetCommentsPart comments = worksheet.WorksheetCommentsPart; List <CommentCellsList> commentLists = new List <CommentCellsList>(); if (comments != null) { CommentList commentList = (CommentList)comments.Comments.ChildElements[1]; //批注列表 foreach (Comment comment in commentList.ChildElements) { CommentCellsList ccl = new CommentCellsList(); //坐标 var cell = GetCellXY(comment.Reference).Split('_'); var columnRow = int.Parse(cell[0].ToString()) - 1; var columnCol = GetColumnIndex(cell[1]); //批注内容 var commentVal = comment.InnerText; ccl.row = columnRow; ccl.col = columnCol; ccl.comment = comment.InnerText; //var commentCell = "{\"Row\":\""+ columnRow + "\",\"Col\":\"" + columnCol + ",\"Comment\":\"" + commentVal + "\"}"; commentLists.Add(ccl); } } sdl.Comments = commentLists; #endregion #region //获取合并单元格 IEnumerable <MergeCells> mergeCells = worksheet.Worksheet.Elements <MergeCells>(); List <MergeCellsList> mergeCellList = new List <MergeCellsList>(); if (mergeCells.Count() > 0) { for (int k = 0; k < mergeCells.First().ChildElements.Count; k++) { MergeCell mergeCell = (MergeCell)mergeCells.First().ChildElements[k]; var reference = mergeCell.Reference.ToString().Split(':'); var startCell = GetCellXY(reference[0]).Split('_'); var endCell = GetCellXY(reference[1]).Split('_'); MergeCellsList mcl = new MergeCellsList(); mcl.row = int.Parse(startCell[0]) - 1; mcl.rowspan = int.Parse(endCell[0]) - int.Parse(startCell[0]) + 1; mcl.col = GetColumnIndex(startCell[1]); mcl.colspan = GetColumnIndex(endCell[1]) - mcl.col + 1; //mcl.reference = mergeCell.Reference.ToString(); mergeCellList.Add(mcl); } } sdl.MergeCells = mergeCellList; #endregion #region //读取图片 DrawingsPart drawingPart = worksheet.GetPartsOfType <DrawingsPart>().ToList().FirstOrDefault(); pictures = new List <PictureInfo>(); if (drawingPart != null) { int tempIndex = 1; foreach (var part in drawingPart.Parts) { PictureInfo pic = new PictureInfo(); ImagePart imgPart = (ImagePart)part.OpenXmlPart; System.Drawing.Image img1 = System.Drawing.Image.FromStream(imgPart.GetStream()); var newFilename = Guid.NewGuid().ToString("N") + ".png"; string[] sArray = Regex.Split(file, "UserFile", RegexOptions.IgnoreCase); string newFilePath = sArray[0] + "_Temp\\" + newFilename; img1.Save(newFilePath); //pic.Image = img1; pic.RefId = part.RelationshipId;//"rId" + imgPart.Uri.ToString().Split('/')[3].Split('.')[0].Substring(5); pic.ImageUrl = newFilePath; pic.ImageName = newFilename; pic.ImgHeight = img1.Height; pic.ImgWidth = img1.Width; pictures.Add(pic); tempIndex++; } //获取图片定位 var worksheetDrawings = drawingPart.WorksheetDrawing.Where(c => c.ChildElements.Any (a => a.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture")).ToList(); foreach (var worksheetDrawing in worksheetDrawings) { if (worksheetDrawing.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor") { TwoCellAnchor anchor = (TwoCellAnchor)worksheetDrawing; DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picDef = (DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture) anchor.ChildElements.FirstOrDefault(c => c.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture"); if (picDef != null) { var embed = picDef.BlipFill.Blip.Embed; if (embed != null) { var picMapping = pictures.FirstOrDefault(c => c.RefId == embed.InnerText); picMapping.FromCol = int.Parse(anchor.FromMarker.ColumnId.InnerText); picMapping.FromRow = int.Parse(anchor.FromMarker.RowId.InnerText); } } // anchor.FromMarker.RowId + anchor.FromMarker.ColumnId } } } sdl.PictureList = pictures; #endregion //读取列宽 IEnumerable <Columns> colsList = worksheet.Worksheet.Elements <Columns>(); #region //读取表格数据 List <SheetDatas> sheetDatas = new List <SheetDatas>(); if (worksheet.Rows().Count() > 0) { RowCount = int.Parse((worksheet.Rows().Last()).RowId); } foreach (OpenXmlPowerTools.Row row in worksheet.Rows()) { int TempColumn = 0; int r = 0; foreach (OpenXmlPowerTools.Cell cell in row.Cells()) { int co = 0; //读取超链接??? //读取单元格数据 SheetDatas sheetData = new SheetDatas(); sheetData.RowId = int.Parse(row.RowId) - 1; sheetData.ColumnId = cell.ColumnIndex; sheetData.Column = cell.Column; sheetData.Type = cell.Type; sheetData.Value = cell.Value; sheetData.SharedString = cell.SharedString; sheetData.Formula = cell.Formula; sheetData.StyleId = cell.Style; //读取列宽(仅限第一行设置列宽) if (colsList.Count() > 0 && r == 0) { Columns col = colsList.ElementAt <Columns>(0); foreach (Column c in col.ChildElements) { if (c.Max == cell.ColumnIndex) { sheetData.Width = c.Width; break; } } } //读取行高(仅限第一列设置行高) if (co == 0) { if (row.RowElement.Attribute("ht") != null) { sheetData.Height = double.Parse(row.RowElement.Attribute("ht").Value); } } #region 样式赋值 if (sheetData.StyleId != null) { CellFormatsList cfl = cellFormatsList[(int)sheetData.StyleId]; //字体样式 sheetData.FontName = fontsList[cfl.fontId].fontname; sheetData.FontSize = fontsList[cfl.fontId].fontsize; sheetData.FontColor = fontsList[cfl.fontId].color; sheetData.FontBold = fontsList[cfl.fontId].bold; sheetData.Italic = fontsList[cfl.fontId].italic; sheetData.Underline = fontsList[cfl.fontId].underline; sheetData.AligmentVertical = cfl.vertical; sheetData.AligmentHorizontal = cfl.horizontal; sheetData.WrapText = cfl.wraptext; //背景色样式 sheetData.FillType = fillsList[cfl.fillId].patternType; sheetData.FillForegroundColor = fillsList[cfl.fillId].fgColor; sheetData.FillBackgroundColor = fillsList[cfl.fillId].bgColor; //边框样式 sheetData.LeftBorder = bordersList[cfl.borderId].left; sheetData.RightBorder = bordersList[cfl.borderId].right; sheetData.TopBorder = bordersList[cfl.borderId].top; sheetData.BottomBorder = bordersList[cfl.borderId].bottom; sheetData.DiagonalBorder = bordersList[cfl.borderId].diagonal; } #endregion //识别文字格式???(日期与数字的区别) sheetDatas.Add(sheetData); TempColumn++; co++; } r++; //计算列数 if (TempColumn > ColumnCount) { ColumnCount = TempColumn; } } sdl.SheetData = sheetDatas; #endregion sdl.SheetName = sheetName[i]; sdl.SheetId = "sheet" + (i + 1); sdl.TotalRow = RowCount < 20 ? 20 : RowCount + 1; sdl.TotalColumn = ColumnCount < 15 ? 15 : ColumnCount + 1; listSDL.Add(sdl); } return(listSDL); } catch (Exception ex) { throw; } }