private static void testShape()
{
SpreadsheetDocument SpreadSheet = SpreadsheetDocument.Open(@"C:\Users\Федотов РР\Desktop\test.xlsx", true);
WorksheetPart worksheetPart = SpreadSheet.WorkbookPart.WorksheetParts.First();
OpenXmlElement[] childs = worksheetPart.DrawingsPart.WorksheetDrawing.ChildElements.ToArray();
Shape s = childs.First().Descendants <Shape>().First();
OpenXmlElement[] sp = s.Descendants <ShapeProperties>().First().ChildElements.ToArray();
OpenXmlElement[] ss = s.Descendants <ShapeStyle>().First().ChildElements.ToArray();
OpenXmlElement[] stb = s.Descendants <TextBody>().First().ChildElements.ToArray();
//WordManager.AddShape(sp, ss, stb);
SpreadSheet.Close();
return;
ExcelDoc d2 = new ExcelDoc(@"C:\Users\Федотов РР\Desktop\test2.xlsx", true);
d2.AddSheet("test").CreateColumns(new double[] { 9.140625, 5, 4, 3 });
WorksheetPart wsp = d2.SpreadSheet.WorkbookPart.WorksheetParts.First();
if (wsp.DrawingsPart == null)
{
wsp.AddNewPart <DrawingsPart>("rId1");
wsp.Worksheet.AppendChild(new Drawing()
{
Id = "rId1"
});
wsp.DrawingsPart.WorksheetDrawing = new WorksheetDrawing();
for (int i = 0; i < childs.Length; i++)
{
if (childs[i] is TwoCellAnchor)
{
TwoCellAnchor na = childs[i].Clone() as TwoCellAnchor;
na.FromMarker.ColumnId.Text = "1";
na.FromMarker.RowId.Text = "1";
na.FromMarker.ColumnOffset.Text = ((int)(2.5 * 66691.28)).ToString();
na.FromMarker.RowOffset.Text = ((int)(7.5 * 12700)).ToString();
na.ToMarker.ColumnId.Text = "4";
na.ToMarker.RowId.Text = "4";
na.ToMarker.ColumnOffset.Text = "0";
na.ToMarker.RowOffset.Text = "0";
wsp.DrawingsPart.WorksheetDrawing.AppendChild(na);
}
}
}
d2.Save();
d2.Close();
}
public void SetChartLocation(DrawingsPart drawingsPart, ChartPart chartPart, SpreadsheetLocation location)
{
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor());
// Chart position
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId(location.ColumnIndex.ToString()),
new ColumnOffset("0"),
new RowId(location.RowIndex.ToString()),
new RowOffset("114300")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId((location.ColumnIndex + 12).ToString()),
new ColumnOffset("0"),
new RowId((location.RowIndex + 15).ToString()),
new RowOffset("0")));
DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
twoCellAnchor.AppendChild <DocumentFormat.OpenXml.
Drawing.Spreadsheet.GraphicFrame>(new DocumentFormat.OpenXml.Drawing.
Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
// Chart name
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u), Name = "Chart 1"
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
}
protected static void AppendGraphicFrame(DrawingsPart drawingsPart, ChartPart chartPart)
{
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("2"),
new ColumnOffset("158233"),
new RowId("2"),
new RowOffset("16894")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("17"),
new ColumnOffset("276225"),
new RowId("32"),
new RowOffset("0")));
// Append a GraphicFrame to the TwoCellAnchor object.
DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
twoCellAnchor.AppendChild <DocumentFormat.OpenXml.
Drawing.Spreadsheet.GraphicFrame>(new DocumentFormat.OpenXml.Drawing.
Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(3u),
Name = "Chart 1"
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(
new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
}
private List <OpenXmlImportImages> GetOpenXmlImportImages(SheetEx sheet, WorkbookPart workbookPart)
{
WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(sheet.SheetId);
DrawingsPart drawingPart = wsPart.GetPartsOfType <DrawingsPart>().ToList().FirstOrDefault();
List <OpenXmlImportImages> pictures = new List <OpenXmlImportImages>();
if (drawingPart != null)
{
foreach (var part in drawingPart.Parts)
{
OpenXmlImportImages pic = new OpenXmlImportImages();
ImagePart imgPart = (ImagePart)part.OpenXmlPart;
pic.Image = StreamToBytes(imgPart.GetStream());
pic.RefId = part.RelationshipId;
pictures.Add(pic);
}
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);
}
}
}
}
}
return(pictures);
}
public void TwoCellAnchor()
{
using (Workbook wb = editor.LoadWorkbook("../../TestFiles/ChartsSheets.xlsx"))
{
Assert.NotNull(wb);
Worksheet ws1 = wb.Worksheets["Лист1"];
Assert.NotEquals(0, ws1.Charts.Count);
Chart chart1 = ws1.Charts[0];
Assert.NotNull(chart1);
TwoCellAnchor anchor1 = chart1.TwoCellAnchor;
Assert.NotNull(anchor1);
Assert.Equals("B7", anchor1.TopLeftRef);
Assert.Equals("J21", anchor1.BottomRightRef);
Assert.Equals(561975 / 12700.0, anchor1.TopLeftOffset.x, 1e-13);
Assert.Equals(66675 / 12700.0, anchor1.TopLeftOffset.y, 1e-13);
Assert.Equals(257175 / 12700.0, anchor1.BottomRightOffset.x, 1e-13);
Assert.Equals(142875 / 12700.0, anchor1.BottomRightOffset.y, 1e-13);
}
}
private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title,
Dictionary <string, int> data)
{
// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().
Where(s => s.Name == worksheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
// Add a new drawing to the worksheet.
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
// Add a new chart and set the chart language to English-US.
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>(
new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
Layout layout = plotArea.AppendChild <Layout>(new Layout());
BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
}));
uint i = 0;
// Iterate through each key in the Dictionary collection and add the key to the chart Series
// and add the corresponding value to the chart Values.
foreach (string key in data.Keys)
{
BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
{
Val =
new UInt32Value(i)
},
new Order()
{
Val = new UInt32Value(i)
},
new SeriesText(new NumericValue()
{
Text = key
})));
StringLiteral strLit = barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData()).AppendChild <StringLiteral>(new StringLiteral());
strLit.Append(new PointCount()
{
Val = new UInt32Value(1U)
});
strLit.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(0U)
}).Append(new NumericValue(key));
NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>(
new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild <NumberLiteral>(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount()
{
Val = new UInt32Value(1U)
});
numLit.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(0u)
})
.Append(new NumericValue(data[key].ToString()));
i++;
}
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
//// Add the Category Axis.
CategoryAxis catAx = plotArea.AppendChild <CategoryAxis>(new CategoryAxis(new AxisId()
{
Val = new UInt32Value(48650112u)
}, new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.
OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = new UInt32Value(48672768U)
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new AutoLabeled()
{
Val = new BooleanValue(true)
},
new LabelAlignment()
{
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
},
new LabelOffset()
{
Val = new UInt16Value((ushort)100)
}));
// Add the Value Axis.
ValueAxis valAx = plotArea.AppendChild <ValueAxis>(new ValueAxis(new AxisId()
{
Val = new UInt32Value(48672768u)
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = new StringValue("General"),
SourceLinked = new BooleanValue(true)
}, new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>
(TickLabelPositionValues.NextTo)
}, new CrossingAxis()
{
Val = new UInt32Value(48650112U)
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new CrossBetween()
{
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
}));
// Add the chart Legend.
Legend legend = chart.AppendChild <Legend>(new Legend(new LegendPosition()
{
Val = new EnumValue <LegendPositionValues>(LegendPositionValues.Right)
},
new Layout()));
chart.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
// Save the chart part.
chartPart.ChartSpace.Save();
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("1"),
new ColumnOffset("581025"),
new RowId("1"),
new RowOffset("114300")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("10"),
new ColumnOffset("276225"),
new RowId("16"),
new RowOffset("0")));
// Append a GraphicFrame to the TwoCellAnchor object.
DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
twoCellAnchor.AppendChild <DocumentFormat.OpenXml.
Drawing.Spreadsheet.GraphicFrame>(new DocumentFormat.OpenXml.Drawing.
Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u), Name = "Chart 1"
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
}
//void InitBasicStylePart(WorkbookPart workbookPart) {
// WorkbookStylesPart stylesPart;
// if (!workbookPart.GetPartsOfType<WorkbookStylesPart>().Any()) {
// stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
// stylesPart.Stylesheet = new Stylesheet();
// var stylesheet = stylesPart.Stylesheet;
// stylesheet.Fonts = new DocumentFormat.OpenXml.Spreadsheet.Fonts(
// new Font(
// new FontSize() { Val = 11 },
// new Color() { Theme = 1 }
// )
// );
// stylesheet.CellStyleFormats = new CellStyleFormats();
// stylesheet.CellStyleFormats.Append(new CellFormat());
// stylesheet.CellFormats = new CellFormats();
// }
// var cellFormat = stylesheet.CellFormats.Elements<CellFormat>().FirstOrDefault(cf => cf.FormatId == cellStyle.FormatId)
// ?? stylesheet.CellFormats.AppendChild(new CellFormat() {
// FormatId = cellStyle.FormatId,
// });
// if (stylesheet.CellStyles == null) {
// stylesheet.CellStyles = new CellStyles();
// }
// var cellStyles = stylesheet.CellStyles;
// var cellStyle = cellStyles.Elements<CellStyle>().FirstOrDefault(cs => cs.Name == "Hyperlink")
// ?? cellStyles.AppendChild(new CellStyle() {
// Name = "Hyperlink",
// BuiltinId = 8,
// FormatId = 0 //index 0 from cellstyleformats
// });
//}
void Save(SpreadsheetDocument spreadsheetDocument)
{
//Create workbook parts
var workbookPart = spreadsheetDocument.AddWorkbookPart();
//Sets workbook
var workbook = new Workbook();
workbookPart.Workbook = workbook;
//Shared string
var sharedStringPart = workbookPart.AddNewPart <SharedStringTablePart>();
sharedStringPart.SharedStringTable = new SharedStringTable();
//Set theme
using (var stream = GetType().GetTypeInfo().Assembly.GetManifestResourceStream("Genexcel.Resources.Office.theme1.xml")) {
using (var reader = new StreamReader(stream)) {
var xml = reader.ReadToEnd();
workbookPart.AddNewPart <ThemePart>();
workbookPart.ThemePart.Theme = new Theme(xml);
}
}
//Set styles
using (var stream = GetType().GetTypeInfo().Assembly.GetManifestResourceStream("Genexcel.Resources.Office.styles.xml")) {
using (var reader = new StreamReader(stream)) {
var xml = reader.ReadToEnd();
workbookPart.AddNewPart <WorkbookStylesPart>();
workbookPart.WorkbookStylesPart.Stylesheet = new Stylesheet(xml);
}
}
//Adiciona lista sheets
var sheets = workbook.AppendChild(new Sheets());
//Adiciona as planilhas ao workbook
uint sheetId = 1;
int sharedStringsIndex = 0;
foreach (var s in _sheets)
{
//Criar worksheet part no workbookpart
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var worksheet = new Worksheet();
//Columns
if (s.HasCustomColumn)
{
var columns = new Columns();
worksheet.Append(columns);
Column currentColElement = null;
Models.Column currentColModel = new Models.Column(); //fake current
for (uint i = 0; i < s.Columns.Length; i++)
{
var col = s.Columns[i];
if (col == currentColModel)
{
currentColElement.Max = i + 1;
}
else
{
currentColElement = new Column()
{
//Style = 1,
Min = i + 1,
Max = i + 1,
//CustomWidth = false,
Width = Models.Column.DEFAULT_WIDTH
};
if (col != null)
{
currentColElement.CustomWidth = true;
currentColElement.Width = col.Width;
}
columns.Append(currentColElement);
}
currentColModel = col;
}
//Column currentColumnElement = null;
//Models.Column currentColumnModel = null;
//foreach (var col in s.Columns) {
// Column colElement;
// if(col == currentColumnModel) { colElement = currentColumnElement }
// if(col == null) {
// }
// columns.Append(new Column() {
// Min = (uint)col.Min,
// Max = (uint)col.Max,
// Width = col.Width,
// Style = 1,
// CustomWidth = true
// });
//}
}
var sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
var name = s.Name ?? "Plan";
name = name.Length > _sheetNameLengthLimit?
name.Substring(0, _sheetNameLengthLimit) :
name;
// Append a new worksheet and associate it with the workbook.
var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId++,
Name = name
};
sheets.Append(sheet);
Dictionary <uint, Row> dctRows = new Dictionary <uint, Row>();
Dictionary <string, Cell> dctCells = new Dictionary <string, Cell>();
foreach (var c in s.GetCells())
{
// Insert cell A1 into the new worksheet.
Cell cell; // = InsertCellInWorksheet(ColTranslate(c.Col), (uint)c.Row, worksheetPart);
//Worksheet worksheet = worksheetPart.Worksheet;
//SheetData sheetData = worksheet.GetFirstChild<SheetData>();
var columnName = ColTranslate(c.Col);
var rowIndex = (uint)c.Row;
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (dctRows.ContainsKey(rowIndex))
{
row = dctRows[rowIndex];
}
//else if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) {
// row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
//}
else
{
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
dctRows[rowIndex] = row;
}
// If there is not a cell with the specified column name, insert one.
//if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) {
//return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
//}
if (dctCells.ContainsKey(cellReference))
{
cell = dctCells[cellReference];
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
//Cell refCell = null;
//foreach (var cell in row.Elements<Cell>()) {
// if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) {
// refCell = cell;
// break;
// }
//}
var newCell = new Cell()
{
CellReference = cellReference
};
//row.InsertBefore(newCell, refCell);
row.Append(newCell);
dctCells[cellReference] = newCell;
//worksheet.Save();
cell = newCell;
}
var value = c.Value;
if (value is string)
{
int index;
var str = value.ToString();
//str = "TESTE VELOCIDADE";
if (_dctSharedStrings.ContainsKey(str))
{
index = _dctSharedStrings[str];
}
else
{
index = sharedStringsIndex++;
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(str)));
//shareStringPart.SharedStringTable.Save();
_dctSharedStrings[str] = index;
}
//int index = InsertSharedStringItem(value.ToString(), sharedStringPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
else if (value is int ||
value is decimal ||
value is long ||
value is short ||
value is double ||
value is float ||
value is byte)
{
var toString = value.GetType().GetTypeInfo()
.GetDeclaredMethods("ToString")
.First(m => m.GetParameters().Any(p => p.ParameterType == typeof(IFormatProvider))); //.GetMethod("ToString", new Type[] { typeof(CultureInfo) }).GetMethodInfo();
var formattedValue = toString.Invoke(value, new object[] { new CultureInfo("en-US") }).ToString();
cell.CellValue = new CellValue(formattedValue);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
if (!string.IsNullOrWhiteSpace(c.Hyperlink))
{
var rId = $"r{Guid.NewGuid().ToString()}";
//if (workbookPart.GetPartsOfType<Relat>().Any()) {
// shareStringPart = worksheet.getp.GetPartsOfType<SharedStringTablePart>().First();
//} else {
// shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
//}
var rel = worksheetPart.AddHyperlinkRelationship(new Uri(c.Hyperlink), true, rId);
var hyperlinks = worksheet.Elements <Hyperlinks>().FirstOrDefault();
if (hyperlinks == null)
{
hyperlinks = worksheet.AppendChild(new Hyperlinks());
}
hyperlinks.Append(new DocumentFormat.OpenXml.Spreadsheet.Hyperlink()
{
Reference = cell.CellReference,
Id = rId
});
cell.StyleIndex = 2; //Hyperlink, should be an enum
}
}
//Charts
foreach (var ch in s.Charts)
{
//https://msdn.microsoft.com/en-us/library/office/cc820055.aspx#How the Sample Code Works
// Add a new drawing to the worksheet.
var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
var chartPart = drawingsPart.AddNewPart <ChartPart>();
var chartSpace = new ChartSpace();
chartPart.ChartSpace = chartSpace;
chartSpace.Append(new Date1904()
{
Val = false
});
chartSpace.Append(new EditingLanguage()
{
Val = "en-US"
});
chartSpace.Append(new RoundedCorners()
{
Val = false
});
var chart = chartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
//chartSpace.Append(new ChartShapeProperties(
// new SolidFill(
// new SchemeColor() { Val = SchemeColorValues.Background1 }
// ),
// new DocumentFormat.OpenXml.Drawing.Outline(
// new SolidFill(
// new SchemeColor(
// new LuminanceModulation() { Val = 15000 },
// new LuminanceOffset() { Val = 85000 }
// ) { Val = SchemeColorValues.Text1 }
// )
// ) {
// Width = 9525,
// CapType = LineCapValues.Flat,
// CompoundLineType = CompoundLineValues.Single,
// Alignment = PenAlignmentValues.Center
// }
// ));
//Dont know
chart.AppendChild(new Title(
new Overlay()
{
Val = false
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = 0,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 1400,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
)
)
)
));
//Allow showing title on top
chart.AppendChild(new AutoTitleDeleted()
{
Val = false
});
//Create plot area
var plotArea = chart.AppendChild(new PlotArea());
var layout = plotArea.AppendChild(new Layout());
if (ch is Models.AreaChart || ch is Models.BarChart)
{
#region init chart
var chObject = ch as Models.Chart;
OpenXmlCompositeElement chartElement;
if (ch is Models.AreaChart)
{
chartElement = plotArea.AppendChild(
//Dont know what extensions are for
new AreaChart(new Grouping()
{
Val = GroupingValues.Standard
})
);
}
else
{
chartElement = plotArea.AppendChild(
//Dont know what extensions are for
new BarChart(
new BarDirection()
{
Val = BarDirectionValues.Column
},
new BarGrouping()
{
Val = BarGroupingValues.Clustered
})
);
}
chartElement.AppendChild(new VaryColors()
{
Val = false
});
#endregion
#region data
foreach (var dts in chObject.Data.Datasets)
{
var index = (uint)chObject.Data.Datasets.IndexOf(dts);
OpenXmlCompositeElement chartSeries;
if (ch is Models.AreaChart)
{
chartSeries = chartElement.AppendChild(new AreaChartSeries());
}
else
{
chartSeries = chartElement.AppendChild(new BarChartSeries());
}
chartSeries.Append(
new Index()
{
Val = index
},
new Order()
{
Val = index
},
new SeriesText()
{
NumericValue = new NumericValue(dts.Title)
},
new ChartShapeProperties(
new SolidFill(new SchemeColor()
{
Val = SchemeColorValues.Accent1
}),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
)
);
if (ch is Models.BarChart)
{
chartSeries.Append(new InvertIfNegative()
{
Val = false
});
}
//Eixo x (labels)
var categoryAxisData = chartSeries.AppendChild(new CategoryAxisData());
var strLit = categoryAxisData.AppendChild(new StringLiteral());
strLit.Append(new PointCount()
{
Val = (uint)chObject.Data.Labels.Count
});
foreach (var lbl in chObject.Data.Labels)
{
strLit.AppendChild(new StringPoint()
{
Index = (uint)chObject.Data.Labels.IndexOf(lbl)
})
.Append(new NumericValue(lbl));
}
var values = chartSeries.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());
var numLit = values.AppendChild(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount()
{
Val = (uint)chObject.Data.Labels.Count
});
foreach (var lbl in chObject.Data.Labels)
{
var lblIndex = chObject.Data.Labels.IndexOf(lbl);
var val = dts.Data.Count > lblIndex ? dts.Data[lblIndex] : 0;
numLit.AppendChild(new NumericPoint()
{
Index = (uint)chObject.Data.Labels.IndexOf(lbl)
})
.Append(new NumericValue(val.ToString()));
}
// numLit.AppendChild(new NumericPoint() { Index = new UInt32Value(0u) })
// .Append
//(new NumericValue("28"));
}
#endregion
#region options?
//Not required for a valid xlsx
chartElement
.AppendChild(
new DataLabels(
new ShowLegendKey()
{
Val = false
},
new ShowValue()
{
Val = false
},
new ShowCategoryName()
{
Val = false
},
new ShowSeriesName()
{
Val = false
},
new ShowPercent()
{
Val = false
},
new ShowBubbleSize()
{
Val = false
}
)
);
if (ch is Models.BarChart)
{
chartElement.Append(new GapWidth()
{
Val = 219
});
chartElement.Append(new Overlap()
{
Val = -27
});
}
#endregion
#region Axis
chartElement.Append(new AxisId()
{
Val = 48650112u
});
chartElement.Append(new AxisId()
{
Val = 48672768u
});
// Add the Category Axis.
var catAx = plotArea
.AppendChild(
new CategoryAxis(
new AxisId()
{
Val = 48650112u
},
new Scaling(
new Orientation()
{
Val = DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax
}
),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = AxisPositionValues.Bottom
},
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new MajorTickMark()
{
Val = ch is Models.AreaChart ? TickMarkValues.Outside : TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.None
},
new TickLabelPosition()
{
Val = TickLabelPositionValues.NextTo
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 15000
},
new LuminanceOffset()
{
Val = 85000
}
)
{
Val = SchemeColorValues.Text1
}
)
)
{
Width = 9525,
CapType = LineCapValues.Flat,
CompoundLineType = CompoundLineValues.Single,
Alignment = PenAlignmentValues.Center
}
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = -60000000,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 900,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
),
new EndParagraphRunProperties()
{
Language = "en-US"
}
)
),
new CrossingAxis()
{
Val = 48672768U
},
new Crosses()
{
Val = CrossesValues.AutoZero
},
new AutoLabeled()
{
Val = true
},
new LabelAlignment()
{
Val = LabelAlignmentValues.Center
},
new LabelOffset()
{
Val = 100
},
new NoMultiLevelLabels()
{
Val = false
}
)
);
// Add the Value Axis.
var valAx = plotArea
.AppendChild(
new ValueAxis(
new AxisId()
{
Val = 48672768u
},
new Scaling(new Orientation()
{
Val = DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = AxisPositionValues.Left
},
new MajorGridlines(
new ChartShapeProperties(
new DocumentFormat.OpenXml.Drawing.Outline(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 15000
},
new LuminanceOffset()
{
Val = 85000
}
)
{
Val = SchemeColorValues.Text1
}
),
new Round()
)
{
Width = 9525,
CapType = LineCapValues.Flat,
CompoundLineType = CompoundLineValues.Single,
Alignment = PenAlignmentValues.Center
},
new EffectList()
)
),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new MajorTickMark()
{
Val = TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.None
},
new TickLabelPosition()
{
Val = TickLabelPositionValues.NextTo
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = -60000000,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 900,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
),
new EndParagraphRunProperties()
{
Language = "en-US"
}
)
),
new CrossingAxis()
{
Val = 48650112U
},
new Crosses()
{
Val = CrossesValues.AutoZero
},
new CrossBetween()
{
Val = ch is Models.AreaChart ? CrossBetweenValues.MidpointCategory : CrossBetweenValues.Between
})
);
// Add the chart Legend.
//Legend legend = chart.AppendChild(new Legend(new LegendPosition() { Val = new EnumValue<LegendPositionValues>(LegendPositionValues.Right) },
// new Layout()));
chart.Append(new PlotVisibleOnly()
{
Val = true
});
chart.Append(new DisplayBlanksAs()
{
Val = ch is Models.AreaChart ? DisplayBlanksAsValues.Zero : DisplayBlanksAsValues.Gap
});
chart.Append(new ShowDataLabelsOverMaximum()
{
Val = false
});
#endregion
// Save the chart part.
chartPart.ChartSpace.Save();
}
#region position?
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("0"),
new ColumnOffset("0"),
new RowId("0"),
new RowOffset("0")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("8"),
new ColumnOffset("0"),
new RowId("15"),
new RowOffset("0")));
// Append a GraphicFrame to the TwoCellAnchor object.
var graphicFrame = twoCellAnchor.AppendChild(new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u),
Name = "Chart 1"
}, new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
#endregion
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
}
var validator = new OpenXmlValidator();
var errors = validator.Validate(spreadsheetDocument);
if (errors.Any())
{
var sbError = new StringBuilder();
sbError.Append("ERROR: ");
foreach (var e in errors)
{
sbError.Append($"***{e.Node.ToString()}:{e.Description}***");
}
throw new Exception(sbError.ToString());
}
workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
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;
}
}
private void PlaceImageOnCell(Worksheet worksheet, Bitmap image, int Col, int Row, double colWid, double rowHeight, string type, string imgName = "", string imgDesc = "")//, float? W, float? H)
{
Dictionary <string, ImagePartType> mimeToImagePartType = new Dictionary <string, ImagePartType>()
{
{ "image/bmp", ImagePartType.Bmp },
{ "image/gif", ImagePartType.Gif },
{ "image/jpg", ImagePartType.Jpeg },
{ "image/jpeg", ImagePartType.Jpeg },
{ "image/png", ImagePartType.Png },
{ "image/tiff", ImagePartType.Tiff }
};
try
{
ImagePart imagePart;
ImagePartType ip;
if (!mimeToImagePartType.TryGetValue(type, out ip))
{
ip = ImagePartType.Jpeg;
}
WorksheetDrawing wsd;
DrawingsPart dp;
WorksheetPart worksheetPart = worksheet.WorksheetPart;
double imgMargin = 5;
//string sImagePath = "D:/Documents/Source/Repos/DarrylSite/DarrylSite/Images/DC.png";
if (worksheetPart.DrawingsPart == null)
{
dp = worksheetPart.AddNewPart <DrawingsPart>();
imagePart = dp.AddImagePart(ip, worksheetPart.GetIdOfPart(dp));
wsd = new WorksheetDrawing();
}
else
{
dp = worksheetPart.DrawingsPart;
imagePart = dp.AddImagePart(ip);
dp.CreateRelationshipToPart(imagePart);
wsd = dp.WorksheetDrawing;
}
NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
nvdp.Id = GetNextImageId();
nvdp.Name = imgName;
nvdp.Description = imgDesc;
DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
picLocks.NoChangeAspect = true;
picLocks.NoChangeArrowheads = true;
NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
nvpdp.PictureLocks = picLocks;
NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
nvpp.NonVisualDrawingProperties = nvdp;
nvpp.NonVisualPictureDrawingProperties = nvpdp;
DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
BlipFill blipFill = new BlipFill();
DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
blip.Embed = dp.GetIdOfPart(imagePart);
blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Email;
/*
* string outerXml =
* "<a:ext uri=\"{28A0092B-C50C-407E-A947-70E740481C1C}\">" +
* "<a14:useLocalDpi xmlns:a14 = \"http://schemas.microsoft.com/office/drawing/2010/main\"/>" +
* "</a:ext> ";
*
* string outerXml = "uri=\"{28A0092B-C50C-407E-A947-70E740481C1C}\">";
*/
A14.UseLocalDpi localDpi = new A14.UseLocalDpi();
/*
* ExtensionList extLst = new ExtensionList();
* Extension extsn = new Extension(localDpi);
* extsn.Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}";
* extLst.Append(extsn);
* blip.Append(extLst);
*/
A.BlipExtensionList blipExtLst = new A.BlipExtensionList();
A.BlipExtension blipExt = new A.BlipExtension()
{
Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}"
};
localDpi.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");
blipExt.Append(localDpi);
blipExtLst.Append(blipExt);
blip.Append(blipExtLst);
blip.Append();
blipFill.Blip = blip;
blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
blipFill.Append(stretch);
DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
offset.X = 0;
offset.Y = 0;
t2d.Offset = offset;
//Bitmap bm = new Bitmap(sImagePath);
//http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
//http://stackoverflow.com/questions/1341930/pixel-to-centimeter
//http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
//extents.Cy = (long)image.Height * (long)((float)914400 / image.VerticalResolution);
//extents.Cy = (int)(rowHeight * 72 / 96 * (100 - (2 * imgMargin)) / 100) * 12700;
extents.Cy = (int)((rowHeight * 72 / 96 * 12700) * (((100 - (2 * imgMargin)) / 100)));
//extents.Cx = (int)(((((colWid - 1) * 7) + 12) * 12700) * (.5 * image.Height / image.Width));
//extents.Cx = (int)(((((colWid - 1) * 7) + 12) * 12700) * (.5 * extents.Cy / image.Width));
extents.Cx = image.Width * extents.Cy / image.Height;
;
t2d.Extents = extents;
ShapeProperties sp = new ShapeProperties();
sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
sp.Transform2D = t2d;
DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
sp.Append(prstGeom);
sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
picture.NonVisualPictureProperties = nvpp;
picture.BlipFill = blipFill;
picture.ShapeProperties = sp;
Xdr.FromMarker fromMarker = new Xdr.FromMarker();
Xdr.ToMarker toMarker = new Xdr.ToMarker();
// From
ColumnId columnId1 = new ColumnId();
columnId1.Text = Col.ToString();
ColumnOffset columnOffset1 = new ColumnOffset();
//columnOffset1.Text = "228600";
double colloff = ((((colWid - 1) * 7) + 12) * 12700) * 72 / 96;
colloff -= extents.Cx;
colloff /= 2;
colloff = (int)colloff;
columnOffset1.Text = colloff.ToString();
RowId rowId1 = new RowId();
rowId1.Text = Row.ToString();
RowOffset rowOffset1 = new RowOffset();
double rowoff = (int)((rowHeight * 72 / 96 * 12700) * (imgMargin / 100));
rowOffset1.Text = (rowoff).ToString();
fromMarker.Append(columnId1);
fromMarker.Append(columnOffset1);
fromMarker.Append(rowId1);
fromMarker.Append(rowOffset1);
// To
ColumnId columnId2 = new ColumnId();
ColumnOffset columnOffset2 = new ColumnOffset();
RowId rowId2 = new RowId();
RowOffset rowOffset2 = new RowOffset();
columnId2.Text = (Col).ToString();
columnOffset2.Text = "0";// "152381";
// Margin is accounted for, so take it off the width
columnOffset2.Text = (extents.Cx + colloff).ToString();
rowId2.Text = (Row).ToString();
rowOffset2.Text = "4572000"; // 4572000 = 12700 (#EMUs/pixel) * 45 (Olivia's cell height in Excel Cell units (aka 60 pixels)) * 8 (???)
rowOffset2.Text = (extents.Cy + rowoff).ToString(); //
toMarker.Append(columnId2);
toMarker.Append(columnOffset2);
toMarker.Append(rowId2);
toMarker.Append(rowOffset2);
//Position pos = new Position(); pos.X = Row; pos.Y = Col;
Extent ext = new Extent(); ext.Cx = extents.Cx; ext.Cy = extents.Cy;
TwoCellAnchor anchor = new TwoCellAnchor();
//OneCellAnchor anchor = new OneCellAnchor();
anchor.Append(fromMarker);
anchor.Append(toMarker);
//anchor.Extent = ext;
//anchor.Append(toMarker);
anchor.Append(picture);
anchor.Append(new ClientData());
wsd.Append(anchor);
{
MemoryStream imgStream = new MemoryStream();
ImageCodecInfo[] codecs = ImageCodecInfo.GetImageEncoders();
ImageCodecInfo codec = codecs.First(c => c.MimeType == type);
Guid imgGuid = codec.FormatID;
ImageFormat imf = new ImageFormat(imgGuid);
float h = image.HorizontalResolution;
float v = image.VerticalResolution;
Bitmap compressedImage = new Bitmap(64, 64);//, gr);
Rectangle rect = new Rectangle(0, 0, 64, 64);
Graphics gr = Graphics.FromImage(compressedImage);
gr.DrawImage(image, rect);
compressedImage.SetResolution(48, 48);
compressedImage.Save(imgStream, imf);
gr.Dispose();
compressedImage.Dispose();
imgStream.Position = 0;
imagePart.FeedData(imgStream);
imgStream.Dispose();
}
if (drawing == null)
{
drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imagePart);
worksheet.Append(drawing);
}
wsd.Save(dp);
worksheetPart.Worksheet.Save();
/* Sheets sheets = new Sheets();Sheet sheet = new Sheet();sheet.Name = "Sheet1";sheet.SheetId = 1;sheet.Id = wbp.GetIdOfPart(wsp);sheets.Append(sheet);wb.Append(sheets); */
}
catch (Exception e)
{
ModelState.AddModelError("Caught fatal exception placing image", e);
Trace.TraceError($"OJException: Could not place image {e.Message}");
}
}
public void CreateExcelDoc(string fileName)
{
List <Student> students = new List <Student>();
Initizalize(students);
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Students"
};
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Add drawing part to WorksheetPart
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
sheets.Append(sheet);
workbookPart.Workbook.Save();
// Add a new chart and set the chart language
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.AppendChild(new EditingLanguage()
{
Val = "en-US"
});
Chart chart = chartPart.ChartSpace.AppendChild(new Chart());
chart.AppendChild(new AutoTitleDeleted()
{
Val = true
}); // We don't want to show the chart title
// Create a new Clustered Column Chart
PlotArea plotArea = chart.AppendChild(new PlotArea());
Layout layout = plotArea.AppendChild(new Layout());
BarChart barChart = plotArea.AppendChild(new BarChart(
new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
},
new VaryColors()
{
Val = false
}
));
// Constructing header
Row row = new Row();
int rowIndex = 1;
row.AppendChild(ConstructCell(string.Empty, CellValues.String));
foreach (var month in Months.Short)
{
row.AppendChild(ConstructCell(month, CellValues.String));
}
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
rowIndex++;
// Create chart series
for (int i = 0; i < students.Count; i++)
{
BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(
new Index()
{
Val = (uint)i
},
new Order()
{
Val = (uint)i
},
new SeriesText(new NumericValue()
{
Text = students[i].Name
})
));
// Adding category axis to the chart
CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData());
// Category
// Constructing the chart category
string formulaCat = "Students!$B$1:$G$1";
StringReference stringReference = categoryAxisData.AppendChild(new StringReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
{
Text = formulaCat
}
});
StringCache stringCache = stringReference.AppendChild(new StringCache());
stringCache.Append(new PointCount()
{
Val = (uint)Months.Short.Length
});
for (int j = 0; j < Months.Short.Length; j++)
{
stringCache.AppendChild(new NumericPoint()
{
Index = (uint)j
}).Append(new NumericValue(Months.Short[j]));
}
}
var chartSeries = barChart.Elements <BarChartSeries>().GetEnumerator();
for (int i = 0; i < students.Count; i++)
{
row = new Row();
row.AppendChild(ConstructCell(students[i].Name, CellValues.String));
chartSeries.MoveNext();
string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex);
DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());
NumberReference numberReference = values.AppendChild(new NumberReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
{
Text = formulaVal
}
});
NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache());
numberingCache.Append(new PointCount()
{
Val = (uint)Months.Short.Length
});
for (uint j = 0; j < students[i].Values.Length; j++)
{
var value = students[i].Values[j];
row.AppendChild(ConstructCell(value.ToString(), CellValues.Number));
numberingCache.AppendChild(new NumericPoint()
{
Index = j
}).Append(new NumericValue(value.ToString()));
}
sheetData.AppendChild(row);
rowIndex++;
}
barChart.AppendChild(new DataLabels(
new ShowLegendKey()
{
Val = false
},
new ShowValue()
{
Val = false
},
new ShowCategoryName()
{
Val = false
},
new ShowSeriesName()
{
Val = false
},
new ShowPercent()
{
Val = false
},
new ShowBubbleSize()
{
Val = false
}
));
barChart.Append(new AxisId()
{
Val = 48650112u
});
barChart.Append(new AxisId()
{
Val = 48672768u
});
// Adding Category Axis
plotArea.AppendChild(
new CategoryAxis(
new AxisId()
{
Val = 48650112u
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = 48672768u
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new AutoLabeled()
{
Val = true
},
new LabelAlignment()
{
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
}
));
// Adding Value Axis
plotArea.AppendChild(
new ValueAxis(
new AxisId()
{
Val = 48672768u
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = 48650112u
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new CrossBetween()
{
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
}
));
chart.Append(
new PlotVisibleOnly()
{
Val = true
},
new DisplayBlanksAs()
{
Val = new EnumValue <DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap)
},
new ShowDataLabelsOverMaximum()
{
Val = false
}
);
chartPart.ChartSpace.Save();
// Positioning the chart on the spreadsheet
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(
new ColumnId("0"),
new ColumnOffset("0"),
new RowId((rowIndex + 2).ToString()),
new RowOffset("0")
));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(
new ColumnId("8"),
new ColumnOffset("0"),
new RowId((rowIndex + 12).ToString()),
new RowOffset("0")
));
// Append GraphicFrame to TwoCellAnchor
GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame());
graphicFrame.Macro = string.Empty;
graphicFrame.Append(new NonVisualGraphicFrameProperties(
new NonVisualDrawingProperties()
{
Id = 2u,
Name = "Sample Chart"
},
new NonVisualGraphicFrameDrawingProperties()
));
graphicFrame.Append(new Transform(
new DocumentFormat.OpenXml.Drawing.Offset()
{
X = 0L, Y = 0L
},
new DocumentFormat.OpenXml.Drawing.Extents()
{
Cx = 0L, Cy = 0L
}
));
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic(
new DocumentFormat.OpenXml.Drawing.GraphicData(
new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
}
)
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}
));
twoCellAnchor.Append(new ClientData());
drawingsPart.WorksheetDrawing.Save();
worksheetPart.Worksheet.Save();
}
}
/// <summary>
/// draw the 2D bar chart
/// index start from 1
/// </summary>
/// <param name="startx">index start from 1 for row</param>
/// <param name="starty">index start from 1 for column</param>
/// <param name="columnCount"></param>
/// <param name="rowCount"></param>
public void InsertChartInSpreadsheet(WorksheetPart sheetpart, string sheetName, int startx, int starty, int columnCount, int rowCount, int chart_pointx, int chart_pointy)
{
WorksheetPart worksheetPart = CurrentWorksheetPart;
#region SDK How to example code
// Add a new drawing to the worksheet.
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
// Add a new chart and set the chart language to English-US.
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>(
new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
Layout layout = plotArea.AppendChild <Layout>(new Layout());
BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
}));
#endregion
string sheetName = GetCurrentSheetName();
string columnName = GetColumnName(starty - 1);
string formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1);
CategoryAxisData cad = new CategoryAxisData();
cad.StringReference = new StringReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
};
uint i = 0;
for (int sIndex = 1; sIndex < columnCount; sIndex++)
{
columnName = GetColumnName(starty + sIndex - 1);
formulaString = string.Format("{0}!${1}${2}", sheetName, columnName, startx);
SeriesText st = new SeriesText();
st.StringReference = new StringReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
};
formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1);
DocumentFormat.OpenXml.Drawing.Charts.Values v = new DocumentFormat.OpenXml.Drawing.Charts.Values();
v.NumberReference = new NumberReference()
{
Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
};
BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
{
Val = new UInt32Value(i)
},
new Order()
{
Val = new UInt32Value(i)
}, st, v));
if (sIndex == 1)
{
barChartSeries.AppendChild(cad);
}
i++;
}
#region SDK how to example Code
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis.
CategoryAxis catAx = plotArea.AppendChild <CategoryAxis>(new CategoryAxis(new AxisId()
{
Val = new UInt32Value(48650112u)
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = new UInt32Value(48672768U)
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new AutoLabeled()
{
Val = new BooleanValue(true)
},
new LabelAlignment()
{
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
},
new LabelOffset()
{
Val = new UInt16Value((ushort)100)
}));
// Add the Value Axis.
ValueAxis valAx = plotArea.AppendChild <ValueAxis>(new ValueAxis(new AxisId()
{
Val = new UInt32Value(48672768u)
},
new Scaling(new Orientation()
{
Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberFormat()
{
FormatCode = new StringValue("General"), SourceLinked = new BooleanValue(true)
},
new TickLabelPosition()
{
Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
},
new CrossingAxis()
{
Val = new UInt32Value(48650112U)
},
new Crosses()
{
Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
},
new CrossBetween()
{
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
}));
// Add the chart Legend.
Legend legend = chart.AppendChild <Legend>(new Legend(new LegendPosition()
{
Val = new EnumValue <LegendPositionValues>(LegendPositionValues.Right)
},
new Layout()));
chart.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
// Save the chart part.
chartPart.ChartSpace.Save();
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor());
twoCellAnchor.Append(new FromMarker(new ColumnId("9"),
new ColumnOffset("581025"),
new RowId("17"),
new RowOffset("114300")));
twoCellAnchor.Append(new ToMarker(new ColumnId("17"),
new ColumnOffset("276225"),
new RowId("32"),
new RowOffset("0")));
// Append a GraphicFrame to the TwoCellAnchor object.
DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
twoCellAnchor.AppendChild <DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u), Name = "Chart 1"
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
#endregion
}
