internal ExcelParagraphCollection(ExcelDrawing drawing, XmlNamespaceManager ns, XmlNode topNode, string path, string[] schemaNodeOrder, float defaultFontSize = 11) :
base(ns, topNode)
{
_drawing = drawing;
_defaultFontSize = defaultFontSize;
AddSchemaNodeOrder(schemaNodeOrder, new string[] { "strRef", "rich", "f", "strCache", "bodyPr", "lstStyle", "p", "ptCount", "pt", "pPr", "lnSpc", "spcBef", "spcAft", "buClrTx", "buClr", "buSzTx", "buSzPct", "buSzPts", "buFontTx", "buFont", "buNone", "buAutoNum", "buChar", "buBlip", "tabLst", "defRPr", "r", "br", "fld", "endParaRPr" });
_path = path;
var pars = TopNode.SelectNodes(path, NameSpaceManager);
foreach (XmlElement par in pars)
{
_paragraphs.Add(par);
var nl = par.SelectNodes("a:r", NameSpaceManager);
if (nl != null)
{
foreach (XmlNode n in nl)
{
if (_list.Count == 0 || n.ParentNode != _list[_list.Count - 1].TopNode.ParentNode)
{
_paragraphs.Add((XmlElement)n.ParentNode);
}
_list.Add(new ExcelParagraph(drawing._drawings, ns, n, "", schemaNodeOrder));
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog chonfile = new OpenFileDialog();
chonfile.Filter = "Mời các anh chọn file excel (*.xlsx)|*.xlsx";
if (chonfile.ShowDialog() == DialogResult.OK)
{
//dung thu vien epplus
ExcelPackage filechon = new ExcelPackage(new FileInfo(chonfile.FileName));
ExcelWorksheet ws = filechon.Workbook.Worksheets[1];
ExcelDrawing hinh1 = ws.Drawings[0];
// var hinh2 = ws.Drawings[1];
int cotdau = hinh1.From.Column;
int dongdau = hinh1.From.Row;
int cotcuoi = hinh1.To.Column;
int dongcuoi = hinh1.To.Row;
txtnhap.Text = cotdau.ToString() + " " + cotcuoi.ToString() + " " + dongdau.ToString() + " " + dongcuoi.ToString();
// dung thu vien spire
Workbook workbook = new Workbook();
workbook.LoadFromFile(chonfile.FileName);
Worksheet sheet = workbook.Worksheets[1];
int soanh = sheet.Pictures.Count();
Spire.Xls.ExcelPicture picture = sheet.Pictures[1];
foreach (var anh in sheet.Pictures)
{
}
picture.Picture.Save(@"image.png", ImageFormat.Png);
lbthongbao.Text = "ok so anh la:" + soanh.ToString();
}
}
/// <summary>
/// Sets the <see cref="OfficeOpenXml.Drawing.Chart.ExcelChart" /> location on the host.
/// </summary>
/// <param name="chart"><see cref="OfficeOpenXml.Drawing.Chart.ExcelChart" /> object.</param>
/// <param name="location">Location model definition.</param>
private static void FormatFromModel(this ExcelDrawing chart, LocationModel location)
{
var coordenates =
location.LocationType.Equals(KnownElementLocation.ByAlignment)
? new Point(1, 1)
: ((CoordenatesModel)location.Mode).TableCoordenates;
chart.SetPosition(coordenates.Y - 1, 0, coordenates.X - 1, 0);
}
internal ExcelParagraphCollection(ExcelDrawing drawing, XmlNamespaceManager ns, XmlNode topNode, string path, string[] schemaNodeOrder) :
base(ns, topNode)
{
_drawing = drawing;
var nl = topNode.SelectNodes(path + "/a:r", NameSpaceManager);
AddSchemaNodeOrder(schemaNodeOrder, new string[] { "strRef", "rich", "f", "strCache", "bodyPr", "lstStyle", "p", "ptCount", "pt", "pPr", "lnSpc", "spcBef", "spcAft", "buClrTx", "buClr", "buSzTx", "buSzPct", "buSzPts", "buFontTx", "buFont", "buNone", "buAutoNum", "buChar", "buBlip", "tabLst", "defRPr", "r", "br", "fld", "endParaRPr" });
if (nl != null)
{
foreach (XmlNode n in nl)
{
_list.Add(new ExcelParagraph(drawing._drawings, ns, n, "", schemaNodeOrder));
}
}
_path = path;
}
public EPPlusDrawing(ExcelDrawing drawing)
{
_drawing = drawing;
}
public void addSeries(string chartName, double[,] data, string dataName, string infoChart = "", string infoData = "")
{
// Finding the chart
ExcelDrawing objChart = null;
int nCharts = charts_.Count;
for (int i = 0; i != nCharts; ++i)
{
ExcelDrawing chart = charts_[i];
if (chart.Name == chartName)
{
objChart = chart;
lastSheet_ = chartSheets_[i];
break;
}
}
if (objChart == null)
{
return;
}
// Check if the chart is currently in use
ChartPosition pos;
int iRow, jCol;
if (!posCharts_.ContainsKey(objChart))
{
pos = new ChartPosition()
{
header = new Position {
row = ChartPosition.lastRow + 1, col = 1
},
length = data.GetLength(0),
availablePosition = new Position {
row = ChartPosition.lastRow + 3, col = 1
}
};
// Write the header
workSheet_.Cells[pos.header.row, pos.header.col].Value = objChart.Name + infoChart;
posCharts_.Add(objChart, pos);
ChartPosition.lastRow += pos.length + 3;
}
else
{
pos = posCharts_[objChart];
}
// Add the function values
iRow = pos.availablePosition.row;
jCol = pos.availablePosition.col;
int nData = data.GetLength(0);
for (int k = 0; k != nData; ++k)
{
workSheet_.Cells[iRow + k, jCol].Value = data[k, 0];
workSheet_.Cells[iRow + k, jCol + 1].Value = data[k, 1];
}
workSheet_.Cells[pos.header.row + 1, jCol].Value = infoData; // Set the data info
workSheet_.Cells[pos.header.row + 1, jCol + 1].Value = dataName; // Set the name
// Retrieving the data address
ExcelScatterChart scatterChart = (ExcelScatterChart)objChart;
string xVals = ExcelRange.GetAddress(iRow, jCol,
iRow + nData - 1, jCol);
string yVals = ExcelRange.GetAddress(iRow, jCol + 1,
iRow + nData - 1, jCol + 1);
xVals = ExcelRange.GetFullAddress(workSheetName_, xVals);
yVals = ExcelRange.GetFullAddress(workSheetName_, yVals);
// Creating the serie
ExcelScatterChartSerie serie = scatterChart.Series.Add(yVals, xVals);
// Using the standard markers when custom ones are not available
List <MarkerProperty> markers = customMarkers_[chartName];
if (markers == null || markers.Count == 0)
{
markers = standardMarkers_;
}
MarkerProperty markerProperties = markers[indMarkers_[chartName]];
// Using the standard lines when custom ones are not available
List <LineProperty> lines = customLines_[chartName];
if (lines == null || lines.Count == 0)
{
lines = standardLines_;
}
LineProperty lineProperties = lines[indLines_[chartName]];
int transparency = lineProperties.isTransparent ? 100 : 0; // Perecentage
// Specifying the properties
serie.Border.Fill.Color = Color.Black; // Line color
serie.Border.LineStyle = lineProperties.lineStyle; // Line style
serie.Border.Fill.Transparancy = transparency; // Line transparency
serie.Border.Width = 1.0; // Line width
if (serie.Marker != null)
{
serie.Marker.Border.Fill.Color = Color.Black; // Marker border color
serie.Marker.Border.Width = 0.75; // Marker border width
serie.Marker.Size = 5; // Marker size
// Marker fill color
if (markerProperties.fillColor != Color.Transparent)
{
serie.Marker.Fill.Color = markerProperties.fillColor;
}
else
{
serie.Marker.Fill.Style = eFillStyle.NoFill;
}
// Marker style
if (lineProperties.isMarkersEnabled)
{
serie.Marker.Style = markerProperties.style;
}
else
{
serie.Marker.Style = eMarkerStyle.None;
}
// Increment markers and lines indices
++indMarkers_[chartName];
if (indMarkers_[chartName] >= markers.Count)
{
indMarkers_[chartName] = 0;
}
}
++indLines_[chartName];
if (indLines_[chartName] >= lines.Count)
{
indLines_[chartName] = 0;
}
// Legend
serie.Header = dataName;
// Shifting data locations
pos.availablePosition.col = pos.availablePosition.col + 2;
pos.length = Math.Max(pos.length, nData);
int lastRowColumn = pos.availablePosition.row + pos.length;
if (lastRowColumn > ChartPosition.lastRow)
{
ChartPosition.lastRow = lastRowColumn;
}
}
public Drawing(ExcelDrawing excelDrawing)
{
_excelDrawing = excelDrawing;
}
private static void CopyDrawing(ExcelPackage pck, XmlNamespaceManager nsm, ExcelWorksheet Copy, ExcelWorksheet added)
{
//First copy the drawing XML
string xml = Copy.Drawings.DrawingXml.OuterXml;
var uriDraw = new Uri(string.Format("/xl/drawings/drawing{0}.xml", added.SheetId), UriKind.Relative);
var part = pck.ZipPackage.CreatePart(uriDraw, "application/vnd.openxmlformats-officedocument.drawing+xml", pck.Compression);
StreamWriter streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
streamDrawing.Write(xml);
streamDrawing.Flush();
XmlDocument drawXml = new XmlDocument();
drawXml.LoadXml(xml);
//Add the relationship ID to the worksheet xml.
var drawRelation = added.Part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, uriDraw), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/drawing");
XmlElement e = added.WorksheetXml.SelectSingleNode("//d:drawing", nsm) as XmlElement;
e.SetAttribute("id", ExcelPackage.schemaRelationships, drawRelation.Id);
for (int i = 0; i < Copy.Drawings.Count; i++)
{
ExcelDrawing draw = Copy.Drawings[i];
//draw.AdjustPositionAndSize(); //Adjust position for any change in normal style font/row size etc.
if (draw is ExcelChart chart)
{
xml = chart.ChartXml.InnerXml;
var UriChart = XmlHelper.GetNewUri(pck.ZipPackage, "/xl/charts/chart{0}.xml");
var chartPart = pck.ZipPackage.CreatePart(UriChart, ContentTypes.contentTypeChart, pck.Compression);
StreamWriter streamChart = new StreamWriter(chartPart.GetStream(FileMode.Create, FileAccess.Write));
streamChart.Write(xml);
streamChart.Flush();
//Now create the new relationship to the copied chart xml
var prevRelID = draw.TopNode.SelectSingleNode("xdr:graphicFrame/a:graphic/a:graphicData/c:chart/@r:id", Copy.Drawings.NameSpaceManager).Value;
var rel = part.CreateRelationship(UriHelper.GetRelativeUri(uriDraw, UriChart), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/chart");
XmlAttribute relAtt = drawXml.SelectSingleNode(string.Format("//c:chart/@r:id[.='{0}']", prevRelID), Copy.Drawings.NameSpaceManager) as XmlAttribute;
relAtt.Value = rel.Id;
}
else if (draw is ExcelPicture pic)
{
IPictureContainer container = pic;
var uri = container.UriPic;
var img = PictureStore.ImageToByteArray(pic.Image);
var ii = added.Workbook._package.PictureStore.AddImage(img, null, pic.ContentType);
var rel = part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, ii.Uri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/image");
//Fixes problem with invalid image when the same image is used more than once.
XmlNode relAtt =
drawXml.SelectSingleNode(
string.Format(
"//xdr:pic/xdr:nvPicPr/xdr:cNvPr/@name[.='{0}']/../../../xdr:blipFill/a:blip/@r:embed",
pic.Name), Copy.Drawings.NameSpaceManager);
if (relAtt != null)
{
relAtt.Value = rel.Id;
}
}
else if (draw is ExcelTableSlicer slicer)
{
var name = pck.Workbook.GetSlicerName(slicer.Name);
var newSlicer = added.Drawings[i] as ExcelTableSlicer;
newSlicer.Name = name;
newSlicer.SlicerName = name;
//The slicer still reference the copied slicers cache. We need to create a new cache for the copied slicer.
newSlicer.CreateNewCache();
}
else if (draw is ExcelPivotTableSlicer ptSlicer)
{
var name = pck.Workbook.GetSlicerName(ptSlicer.Name);
var newSlicer = added.Drawings[i] as ExcelPivotTableSlicer;
newSlicer.Name = name;
newSlicer.SlicerName = name;
//The slicer still reference the copied slicers cache. We need to create a new cache for the copied slicer.
newSlicer.CreateNewCache(ptSlicer.Cache._field);
}
else if (draw is ExcelControl ctrl)
{
var UriCtrl = XmlHelper.GetNewUri(pck.ZipPackage, "/xl/ctrlProps/ctrlProp{0}.xml");
var ctrlPart = pck.ZipPackage.CreatePart(UriCtrl, ContentTypes.contentTypeControlProperties, pck.Compression);
StreamWriter streamChart = new StreamWriter(ctrlPart.GetStream(FileMode.Create, FileAccess.Write));
streamChart.Write(ctrl.ControlPropertiesXml.OuterXml);
streamChart.Flush();
var prevRelID = ctrl._control.RelationshipId;
var rel = added.Part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, UriCtrl), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/ctrlProp");
XmlAttribute relAtt = added.WorksheetXml.SelectSingleNode(string.Format("//d:control/@r:id[.='{0}']", prevRelID), added.NameSpaceManager) as XmlAttribute;
relAtt.Value = rel.Id;
}
}
//rewrite the drawing xml with the new relID's
streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
streamDrawing.Write(drawXml.OuterXml);
streamDrawing.Flush();
//Copy the size variables to the copy.
for (int i = 0; i < Copy.Drawings.Count; i++)
{
var draw = Copy.Drawings[i];
var c = added.Drawings[i];
if (c != null)
{
c._left = draw._left;
c._top = draw._top;
c._height = draw._height;
c._width = draw._width;
}
if (c is ExcelChart chart)
{
for (int j = 0; i < chart.Series.Count; i++)
{
var s = chart.Series[j];
var a = new ExcelAddressBase(s.Series);
if (a.WorkSheetName.Equals(Copy.Name))
{
s.Series = ExcelAddressBase.GetFullAddress(added.Name, a.LocalAddress);
}
a = new ExcelAddressBase(s.XSeries);
if (a.WorkSheetName.Equals(Copy.Name))
{
s.XSeries = ExcelAddressBase.GetFullAddress(added.Name, a.LocalAddress);
}
}
}
}
}
public Picture(ExcelDrawing excelDrawing) : base(excelDrawing)
{
}
internal ExcelDrawingNoFill(ExcelDrawing drawing) : base()
{
}
