private void WriteCommentPart(WorksheetCommentsPart wcp, VmlDrawingPart vdp) { var listCommentKeys = slws.Comments.Keys.ToList(); listCommentKeys.Sort(new SLCellReferencePointComparer()); var bAuthorFound = false; var iAuthorIndex = 0; SLComment comm; var i = 0; SLRowProperties rp; SLColumnProperties cp; SLCellPoint pt; // just in case if (slws.Authors.Count == 0) { if (DocumentProperties.Creator.Length > 0) { slws.Authors.Add(DocumentProperties.Creator); } else { slws.Authors.Add(SLConstants.ApplicationName); } } var iDataRange = 1; // hah! optional... we'll see... var iOptionalShapeTypeId = 202; var sShapeTypeId = string.Format("_x0000_t{0}", iOptionalShapeTypeId); var iShapeIdBase = iDataRange * 1024; var iRowID = 0; var iColumnID = 0; double fRowRemainder = 0; double fColumnRemainder = 0; long lRowEMU = 0; long lColumnEMU = 0; long lRowRemainder = 0; long lColumnRemainder = 0; var iEMU = 0; double fMargin = 0; double fFrac = 0; var iFrac = 0; var sFrac = string.Empty; ImagePart imgp; var sFileName = string.Empty; // image data in base 64, relationship ID var dictImageData = new Dictionary <string, string>(); // not supporting existing VML drawings. But if supporting, process the VmlDrawingPart for // ImageParts. // Apparently, Excel chokes if a "non-standard" relationship ID is given // to VML drawings. It seems to only accept the form "rId{num}", and {num} seems // to have to start from 1. I don't know if Excel will also choke if you jump // from 1 to 3, but I *do* know you can't even start from rId3. // Excel VML seems to be particularly strict on this... // The error originated by having a "non-standard" relationship ID for a // VML image. Say "R2dk723lgsjg2" or whatever. Then fire up Excel and open // that spreadsheet. Then save. Then open it again. You'll get an error. // Apparently, Excel will put "rId1" on the tag o:relid. The error is that // the original o:relid with "R2dk723lgsjg2" as the value is still there. // Meaning the o:relid attribute is duplicated, hence the error. // Why don't I just use the number of vdp.Parts or even vdp.ImageParts to // get the next valid relationship ID? I don't know. Paranoia? // The existing relationship IDs *might* be in sequential order, but you never // know what Excel accepts... If you can get me the Microsoft Excel developer // who can explain this, I'll gladly change the algorithm... // So why the dictionary? Apparently, Excel also chokes if there are duplicates of // the VML image. So even 2 unique relationship IDs that *happens* to have identical // image data will tie Excel into knots. I am so upset with Excel right now... // I know it will keep file size down if only unique image data is stored, but still... var sbVml = new StringBuilder(); sbVml.Append( "<xml xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"); sbVml.Append("<o:shapelayout v:ext=\"edit\">"); sbVml.AppendFormat("<o:idmap v:ext=\"edit\" data=\"{0}\"/>", iDataRange); sbVml.Append("</o:shapelayout>"); sbVml.AppendFormat( "<v:shapetype id=\"{0}\" coordsize=\"21600,21600\" o:spt=\"{1}\" path=\"m,l,21600r21600,l21600,xe\">", sShapeTypeId, iOptionalShapeTypeId); sbVml.Append("<v:stroke joinstyle=\"miter\"/><v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>"); sbVml.Append("</v:shapetype>"); using (var oxwComment = OpenXmlWriter.Create(wcp)) { oxwComment.WriteStartElement(new Comments()); oxwComment.WriteStartElement(new Authors()); for (i = 0; i < slws.Authors.Count; ++i) { oxwComment.WriteElement(new Author(slws.Authors[i])); } oxwComment.WriteEndElement(); oxwComment.WriteStartElement(new CommentList()); for (i = 0; i < listCommentKeys.Count; ++i) { pt = listCommentKeys[i]; comm = slws.Comments[pt]; bAuthorFound = false; for (iAuthorIndex = 0; iAuthorIndex < slws.Authors.Count; ++iAuthorIndex) { if (comm.Author.Equals(slws.Authors[iAuthorIndex])) { bAuthorFound = true; break; } } if (!bAuthorFound) { iAuthorIndex = 0; } oxwComment.WriteStartElement(new Comment { Reference = SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex), AuthorId = (uint)iAuthorIndex }); oxwComment.WriteElement(comm.rst.ToCommentText()); oxwComment.WriteEndElement(); sbVml.AppendFormat("<v:shape id=\"_x0000_s{0}\" type=\"#{1}\"", iShapeIdBase + i + 1, sShapeTypeId); sbVml.Append(" style='position:absolute;"); if (!comm.HasSetPosition) { comm.Top = pt.RowIndex - 1 + SLConstants.DefaultCommentTopOffset; comm.Left = pt.ColumnIndex + SLConstants.DefaultCommentLeftOffset; if (comm.Top < 0) { comm.Top = 0; } if (comm.Left < 0) { comm.Left = 0; } } if (comm.UsePositionMargin) { sbVml.AppendFormat("margin-left:{0}pt;", comm.LeftMargin.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.AppendFormat("margin-top:{0}pt;", comm.TopMargin.ToString("0.##", CultureInfo.InvariantCulture)); } else { iRowID = (int)Math.Floor(comm.Top); fRowRemainder = comm.Top - iRowID; iColumnID = (int)Math.Floor(comm.Left); fColumnRemainder = comm.Left - iColumnID; lRowEMU = 0; lColumnEMU = 0; for (iEMU = 1; iEMU <= iRowID; ++iEMU) { if (slws.RowProperties.ContainsKey(iEMU)) { rp = slws.RowProperties[iEMU]; lRowEMU += rp.HeightInEMU; } else { lRowEMU += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } if (slws.RowProperties.ContainsKey(iRowID + 1)) { rp = slws.RowProperties[iRowID + 1]; lRowRemainder = Convert.ToInt64(fRowRemainder * rp.HeightInEMU); lRowEMU += lRowRemainder; } else { lRowRemainder = Convert.ToInt64(fRowRemainder * slws.SheetFormatProperties.DefaultRowHeightInEMU); lRowEMU += lRowRemainder; } for (iEMU = 1; iEMU <= iColumnID; ++iEMU) { if (slws.ColumnBreaks.ContainsKey(iEMU)) { cp = slws.ColumnProperties[iEMU]; lColumnEMU += cp.WidthInEMU; } else { lColumnEMU += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } if (slws.ColumnProperties.ContainsKey(iColumnID + 1)) { cp = slws.ColumnProperties[iColumnID + 1]; lColumnRemainder = Convert.ToInt64(fColumnRemainder * cp.WidthInEMU); lColumnEMU += lColumnRemainder; } else { lColumnRemainder = Convert.ToInt64(fColumnRemainder * slws.SheetFormatProperties.DefaultColumnWidthInEMU); lColumnEMU += lColumnRemainder; } fMargin = lColumnEMU / (double)SLConstants.PointToEMU; sbVml.AppendFormat("margin-left:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture)); fMargin = lRowEMU / (double)SLConstants.PointToEMU; sbVml.AppendFormat("margin-top:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture)); } if (comm.AutoSize) { sbVml.Append("width:auto;height:auto;"); } else { sbVml.AppendFormat("width:{0}pt;", comm.Width.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.AppendFormat("height:{0}pt;", comm.Height.ToString("0.##", CultureInfo.InvariantCulture)); } sbVml.AppendFormat("z-index:{0};", i + 1); sbVml.AppendFormat("visibility:{0}'", comm.Visible ? "visible" : "hidden"); if (!comm.Fill.HasFill) { // use #ffffff ? sbVml.Append(" fillcolor=\"window [65]\""); } else if (comm.Fill.Type == SLFillType.NoFill) { sbVml.Append(" filled=\"f\""); sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLFillType.SolidFill) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLFillType.GradientFill) { if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2")); } } else if (comm.Fill.Type == SLFillType.BlipFill) { // don't have to do anything } else if (comm.Fill.Type == SLFillType.PatternFill) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.PatternForegroundColor.DisplayColor.R.ToString("x2"), comm.Fill.PatternForegroundColor.DisplayColor.G.ToString("x2"), comm.Fill.PatternForegroundColor.DisplayColor.B.ToString("x2")); } if (comm.LineColor != null) { sbVml.AppendFormat(" strokecolor=\"#{0}{1}{2}\"", comm.LineColor.Value.R.ToString("x2"), comm.LineColor.Value.G.ToString("x2"), comm.LineColor.Value.B.ToString("x2")); } if (comm.fLineWeight != null) { sbVml.AppendFormat(" strokeweight=\"{0}pt\"", comm.fLineWeight.Value.ToString("0.##", CultureInfo.InvariantCulture)); } sbVml.Append(" o:insetmode=\"auto\">"); sbVml.Append("<v:fill"); if ((comm.Fill.Type == SLFillType.SolidFill) || (comm.Fill.Type == SLFillType.GradientFill)) { if (comm.Fill.Type == SLFillType.SolidFill) { fFrac = 100.0 - (double)comm.Fill.SolidColor.Transparency; } else { fFrac = 100.0 - comm.bFromTransparency; } iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" opacity=\"{0}\"", sFrac); } } if (comm.Fill.Type == SLFillType.SolidFill) { sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLFillType.GradientFill) { if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2")); } else { sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } fFrac = 100.0 - comm.bToTransparency; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac); } sbVml.Append(" rotate=\"t\""); if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.Append(" colors=\""); for (var iGradient = 0; iGradient < comm.Fill.GradientColor.GradientStops.Count; ++iGradient) { // you take the position/gradient value straight fFrac = (double)comm.Fill.GradientColor.GradientStops[iGradient].Position; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } if (iGradient > 0) { sbVml.Append(";"); } sbVml.AppendFormat("{0} #{1}{2}{3}", sFrac, comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.B.ToString("x2")); } sbVml.Append("\""); } if (comm.Fill.GradientColor.IsLinear) { // use temporarily // VML increases angles in counter-clockwise direction, // otherwise we'd just use the angle straight from the property //...fFrac = 360.0 - (double)comm.Fill.GradientColor.Angle; fFrac = (double)comm.Fill.GradientColor.Angle; sbVml.AppendFormat(" angle=\"{0}\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.Append(" focus=\"100%\" type=\"gradient\""); } else { switch (comm.Fill.GradientColor.PathType) { case A.PathShadeValues.Shape: sbVml.Append(" focusposition=\"50%,50%\" focus=\"100%\" type=\"gradientradial\""); break; case A.PathShadeValues.Rectangle: case A.PathShadeValues.Circle: // because there's no way to do a circular gradient with VML... switch (comm.Fill.GradientColor.Direction) { case SLGradientDirectionValues.Center: sbVml.Append(" focusposition=\"50%,50%\""); break; case SLGradientDirectionValues.CenterToBottomLeftCorner: // so the "centre" is at the top-right sbVml.Append(" focusposition=\"100%,0%\""); break; case SLGradientDirectionValues.CenterToBottomRightCorner: // so the "centre" is at the top-left sbVml.Append(" focusposition=\"0%,0%\""); break; case SLGradientDirectionValues.CenterToTopLeftCorner: // so the "centre" is at the bottom-right sbVml.Append(" focusposition=\"100%,100%\""); break; case SLGradientDirectionValues.CenterToTopRightCorner: // so the "centre" is at the bottom-left sbVml.Append(" focusposition=\"0%,100%\""); break; } sbVml.Append(" focus=\"100%\" type=\"gradientradial\""); break; } } } else if (comm.Fill.Type == SLFillType.BlipFill) { var sRelId = "rId1"; using (var fs = new FileStream(comm.Fill.BlipFileName, FileMode.Open)) { var ba = new byte[fs.Length]; fs.Read(ba, 0, ba.Length); var sImageData = Convert.ToBase64String(ba); if (dictImageData.ContainsKey(sImageData)) { sRelId = dictImageData[sImageData]; comm.Fill.BlipRelationshipID = sRelId; } else { // if we haven't found a viable relationship ID by 10 million iterations, // then we have serious issues... for (var iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum) { sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture)); // we could use a hashset to store the relationship IDs so we // don't use the ContainsValue() because ContainsValue() is supposedly // slow... I'm not gonna care because if this algorithm slows enough // that ContainsValue() is inefficient, that means there are enough VML // drawings to choke a modestly sized art museum. if (!dictImageData.ContainsValue(sRelId)) { break; } } imgp = vdp.AddImagePart(SLDrawingTool.GetImagePartType(comm.Fill.BlipFileName), sRelId); fs.Position = 0; imgp.FeedData(fs); comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp); dictImageData[sImageData] = sRelId; } } sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID); // all this to get from "myawesomepicture.jpg" to "myawesomepicture" sFileName = comm.Fill.BlipFileName; // use temporarily iFrac = sFileName.LastIndexOfAny("\\/".ToCharArray()); sFileName = sFileName.Substring(iFrac + 1); iFrac = sFileName.LastIndexOf("."); sFileName = sFileName.Substring(0, iFrac); sbVml.AppendFormat(" o:title=\"{0}\"", sFileName); sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); sbVml.Append(" recolor=\"t\" rotate=\"t\""); fFrac = 100.0 - (double)comm.Fill.BlipTransparency; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac); } if (comm.Fill.BlipTile) { sbVml.Append(" type=\"tile\""); sbVml.AppendFormat(" size=\"{0}%,{1}%\"", comm.Fill.BlipScaleX.ToString("0.##", CultureInfo.InvariantCulture), comm.Fill.BlipScaleY.ToString("0.##", CultureInfo.InvariantCulture)); } else { sbVml.Append(" type=\"frame\""); // use temporarily //fFrac = (50.0 - (double)comm.Fill.BlipLeftOffset) + (50.0 - (double)comm.Fill.BlipRightOffset); fFrac = 100.0 - (double)comm.Fill.BlipLeftOffset - (double)comm.Fill.BlipRightOffset; sbVml.AppendFormat(" size=\"{0}%,", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); fFrac = 100.0 - (double)comm.Fill.BlipTopOffset - (double)comm.Fill.BlipBottomOffset; sbVml.AppendFormat("{0}%\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); } } else if (comm.Fill.Type == SLFillType.PatternFill) { var sRelId = "rId1"; using (var ms = new MemoryStream()) { using (var bm = SLDrawingTool.GetVmlPatternFill(comm.Fill.PatternPreset)) { bm.Save(ms, ImageFormat.Png); } var ba = new byte[ms.Length]; ms.Read(ba, 0, ba.Length); var sImageData = Convert.ToBase64String(ba); if (dictImageData.ContainsKey(sImageData)) { sRelId = dictImageData[sImageData]; comm.Fill.BlipRelationshipID = sRelId; } else { // go check the "normal" image part additions for comments... for (var iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum) { sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture)); if (!dictImageData.ContainsValue(sRelId)) { break; } } imgp = vdp.AddImagePart(ImagePartType.Png, sRelId); ms.Position = 0; imgp.FeedData(ms); comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp); dictImageData[sImageData] = sRelId; } } sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID); sbVml.AppendFormat(" o:title=\"{0}\"", SLDrawingTool.ConvertToVmlTitle(comm.Fill.PatternPreset)); sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.PatternBackgroundColor.DisplayColor.R.ToString("x2"), comm.Fill.PatternBackgroundColor.DisplayColor.G.ToString("x2"), comm.Fill.PatternBackgroundColor.DisplayColor.B.ToString("x2")); sbVml.Append(" recolor=\"t\" type=\"pattern\""); } sbVml.Append("/>"); if ((comm.LineStyle != StrokeLineStyleValues.Single) || (comm.vLineDashStyle != null)) { sbVml.Append("<v:stroke"); switch (comm.LineStyle) { case StrokeLineStyleValues.Single: // don't have to do anything break; case StrokeLineStyleValues.ThickBetweenThin: sbVml.Append(" linestyle=\"thickBetweenThin\"/>"); break; case StrokeLineStyleValues.ThickThin: sbVml.Append(" linestyle=\"thickThin\"/>"); break; case StrokeLineStyleValues.ThinThick: sbVml.Append(" linestyle=\"thinThick\"/>"); break; case StrokeLineStyleValues.ThinThin: sbVml.Append(" linestyle=\"thinThin\"/>"); break; } if (comm.vLineDashStyle != null) { switch (comm.vLineDashStyle.Value) { case SLDashStyleValues.Solid: sbVml.Append(" dashstyle=\"solid\"/>"); break; case SLDashStyleValues.ShortDash: sbVml.Append(" dashstyle=\"shortdash\"/>"); break; case SLDashStyleValues.ShortDot: sbVml.Append(" dashstyle=\"shortdot\"/>"); break; case SLDashStyleValues.ShortDashDot: sbVml.Append(" dashstyle=\"shortdashdot\"/>"); break; case SLDashStyleValues.ShortDashDotDot: sbVml.Append(" dashstyle=\"shortdashdotdot\"/>"); break; case SLDashStyleValues.Dot: sbVml.Append(" dashstyle=\"dot\"/>"); break; case SLDashStyleValues.Dash: sbVml.Append(" dashstyle=\"dash\"/>"); break; case SLDashStyleValues.LongDash: sbVml.Append(" dashstyle=\"longdash\"/>"); break; case SLDashStyleValues.DashDot: sbVml.Append(" dashstyle=\"dashdot\"/>"); break; case SLDashStyleValues.LongDashDot: sbVml.Append(" dashstyle=\"longdashdot\"/>"); break; case SLDashStyleValues.LongDashDotDot: sbVml.Append(" dashstyle=\"longdashdotdot\"/>"); break; } } if (comm.vEndCap != null) { switch (comm.vEndCap.Value) { case StrokeEndCapValues.Flat: sbVml.Append(" endcap=\"flat\"/>"); break; case StrokeEndCapValues.Round: sbVml.Append(" endcap=\"round\"/>"); break; case StrokeEndCapValues.Square: sbVml.Append(" endcap=\"square\"/>"); break; } } sbVml.Append("/>"); } if (comm.HasShadow) { sbVml.AppendFormat("<v:shadow on=\"t\" color=\"#{0}{1}{2}\" obscured=\"t\"/>", comm.ShadowColor.R.ToString("x2"), comm.ShadowColor.G.ToString("x2"), comm.ShadowColor.B.ToString("x2")); } sbVml.Append("<v:path o:connecttype=\"none\"/>"); sbVml.Append("<v:textbox style='mso-direction-alt:auto;"); switch (comm.Orientation) { case SLCommentOrientationValues.Horizontal: // don't have to do anything break; case SLCommentOrientationValues.TopDown: sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:top-to-bottom;"); break; case SLCommentOrientationValues.Rotated270Degrees: sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:bottom-to-top;"); break; case SLCommentOrientationValues.Rotated90Degrees: sbVml.Append("layout-flow:vertical;"); break; } if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft) { sbVml.Append("direction:RTL;"); } // no else because don't have to do anything if (comm.AutoSize) { sbVml.Append("mso-fit-shape-to-text:t;"); } sbVml.Append("'><div"); if ((comm.HorizontalTextAlignment != SLHorizontalTextAlignmentValues.Distributed) || (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft)) { sbVml.Append(" style='"); switch (comm.HorizontalTextAlignment) { case SLHorizontalTextAlignmentValues.Left: sbVml.Append("text-align:left;"); break; case SLHorizontalTextAlignmentValues.Justify: sbVml.Append("text-align:justify;"); break; case SLHorizontalTextAlignmentValues.Center: sbVml.Append("text-align:center;"); break; case SLHorizontalTextAlignmentValues.Right: sbVml.Append("text-align:right;"); break; case SLHorizontalTextAlignmentValues.Distributed: // don't have to do anything break; } if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft) { sbVml.Append("direction:rtl;"); } sbVml.Append("'"); } sbVml.Append("></div>"); sbVml.Append("</v:textbox>"); sbVml.Append("<x:ClientData ObjectType=\"Note\">"); sbVml.Append("<x:MoveWithCells/>"); sbVml.Append("<x:SizeWithCells/>"); // anchors are bloody hindering awkward inconvenient to calculate... //sbVml.Append("<x:Anchor>"); //sbVml.Append("2, 15, 2, 14, 4, 23, 6, 19"); //sbVml.Append("</x:Anchor>"); sbVml.Append("<x:AutoFill>False</x:AutoFill>"); switch (comm.HorizontalTextAlignment) { case SLHorizontalTextAlignmentValues.Left: // don't have to do anything break; case SLHorizontalTextAlignmentValues.Justify: sbVml.Append("<x:TextHAlign>Justify</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Center: sbVml.Append("<x:TextHAlign>Center</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Right: sbVml.Append("<x:TextHAlign>Right</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Distributed: sbVml.Append("<x:TextHAlign>Distributed</x:TextHAlign>"); break; } switch (comm.VerticalTextAlignment) { case SLVerticalTextAlignmentValues.Top: // don't have to do anything break; case SLVerticalTextAlignmentValues.Justify: sbVml.Append("<x:TextVAlign>Justify</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Center: sbVml.Append("<x:TextVAlign>Center</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Bottom: sbVml.Append("<x:TextVAlign>Bottom</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Distributed: sbVml.Append("<x:TextVAlign>Distributed</x:TextVAlign>"); break; } sbVml.AppendFormat("<x:Row>{0}</x:Row>", pt.RowIndex - 1); sbVml.AppendFormat("<x:Column>{0}</x:Column>", pt.ColumnIndex - 1); if (comm.Visible) { sbVml.Append("<x:Visible/>"); } sbVml.Append("</x:ClientData>"); sbVml.Append("</v:shape>"); } oxwComment.WriteEndElement(); // this is for Comments oxwComment.WriteEndElement(); } sbVml.Append("</xml>"); using (var mem = new MemoryStream(Encoding.ASCII.GetBytes(sbVml.ToString()))) { vdp.FeedData(mem); } }
/// <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; } }
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> /// Adds all the comments defined in the List to the current worksheet. /// </summary> /// <param name="worksheetPart">Worksheet Part of file.</param> /// <param name="commentsToAddList">List of CellComment which contain cell coordinates and the text value to set as comment.</param> public static void InsertComments(WorksheetPart worksheetPart, List <CellComment> commentsToAddList) { if (commentsToAddList.Any()) { string commentsVmlXml = string.Empty; // Create all the comment VML Shape XML foreach (var commentToAdd in commentsToAddList) { commentsVmlXml += GetCommentVMLShapeXML(ConvertColumnNumberToName(commentToAdd.col), commentToAdd.row.ToString()); } // The VMLDrawingPart should contain all the definitions for how to draw every comment shape for the worksheet VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart <VmlDrawingPart>(); using (XmlTextWriter writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8)) { writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n <o:shapelayout v:ext=\"edit\">\r\n <o:idmap v:ext=\"edit\" data=\"1\"/>\r\n" + "</o:shapelayout><v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\"\r\n path=\"m,l,21600r21600,l21600,xe\">\r\n <v:stroke joinstyle=\"miter\"/>\r\n <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>\r\n </v:shapetype>" + commentsVmlXml + "</xml>"); } // Create the comment elements foreach (var commentToAdd in commentsToAddList) { WorksheetCommentsPart worksheetCommentsPart = worksheetPart.WorksheetCommentsPart ?? worksheetPart.AddNewPart <WorksheetCommentsPart>(); // We only want one legacy drawing element per worksheet for comments if (worksheetPart.Worksheet.Descendants <LegacyDrawing>().SingleOrDefault() == null) { string vmlPartId = worksheetPart.GetIdOfPart(vmlDrawingPart); LegacyDrawing legacyDrawing = new LegacyDrawing() { Id = vmlPartId }; worksheetPart.Worksheet.Append(legacyDrawing); } Comments comments; bool appendComments = false; if (worksheetPart.WorksheetCommentsPart.Comments != null) { comments = worksheetPart.WorksheetCommentsPart.Comments; } else { comments = new Comments(); appendComments = true; } // We only want one Author element per Comments element if (worksheetPart.WorksheetCommentsPart.Comments == null) { Authors authors = new Authors(); Author author = new Author { Text = "Author Name" }; authors.Append(author); comments.Append(authors); } CommentList commentList; bool appendCommentList = false; if (worksheetPart.WorksheetCommentsPart.Comments != null && worksheetPart.WorksheetCommentsPart.Comments.Descendants <CommentList>().SingleOrDefault() != null) { commentList = worksheetPart.WorksheetCommentsPart.Comments.Descendants <CommentList>().Single(); } else { commentList = new CommentList(); appendCommentList = true; } Comment comment = new Comment() { Reference = string.Concat(ConvertColumnNumberToName(commentToAdd.col), commentToAdd.row), AuthorId = (UInt32Value)0U }; CommentText commentTextElement = new CommentText(); Run run = new Run(); RunProperties runProperties = new RunProperties(); Bold bold = new Bold(); FontSize fontSize = new FontSize() { Val = 8D }; Color color = new Color() { Indexed = (UInt32Value)81U }; RunFont runFont = new RunFont() { Val = "Tahoma" }; RunPropertyCharSet runPropertyCharSet = new RunPropertyCharSet() { Val = 1 }; runProperties.Append(bold); runProperties.Append(fontSize); runProperties.Append(color); runProperties.Append(runFont); runProperties.Append(runPropertyCharSet); Text text = new Text { Text = commentToAdd.text }; run.Append(runProperties); run.Append(text); commentTextElement.Append(run); comment.Append(commentTextElement); commentList.Append(comment); // Only append the Comment List if this is the first time adding a comment if (appendCommentList) { comments.Append(commentList); } // Only append the Comments if this is the first time adding Comments if (appendComments) { worksheetCommentsPart.Comments = comments; } } } }