/// <summary>
/// 通过SAX创建工作表
/// </summary>
/// <param name="worksheetPart"></param>
private void CreateSheetBySAX(WorksheetPart worksheetPart, DrawingsPart drawingsPart)
{
using (_writer = OpenXmlWriter.Create(worksheetPart))
{
_rowIndex = 1;
//S: Worksheet
_writer.WriteStartElement(new Worksheet());
if (drawingsPart != null)
{
_writer.WriteElement(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
}
InitSheetViews();
InitColumns();
MergeCells();
FillData();
//E: Worksheet
_writer.WriteEndElement();
_writer.Close();
}
}
public static void supprChart(WorksheetPart worksheetPart, string titre)
{
DrawingsPart a = worksheetPart.DrawingsPart;
DrawingsPart dp = worksheetPart.DrawingsPart;
IEnumerable <ChartPart> cps = a.ChartParts;
ChartPart b = null;
foreach (ChartPart cp in cps)
{
if (cp.ChartSpace.Descendants <Chart.Chart>().First().Descendants <Chart.Title>().First().Descendants <DocumentFormat.OpenXml.Drawing.Run>().First().Text.Text == titre)
{
b = cp;
}
}
//obtention id du modèle
string id = a.GetIdOfPart(b);
a.DeletePart(b);
//Suppression de l'ancre associée
DocumentFormat.OpenXml.Drawing.Spreadsheet.WorksheetDrawing wsd = a.WorksheetDrawing;
foreach (Draw.TwoCellAnchor tca in wsd.Elements <Draw.TwoCellAnchor>())
{
string tmp = tca.Descendants <Chart.ChartReference>().First().Id;
if (tmp == id)
{
tca.Remove();
}
}
}
public static void CreateLineChart(DrawingsPart part, string formulaCat, string formulaVal, int row, bool newe, string title)
{
ChartPart chartPart1 = part.AddNewPart <ChartPart>();
GenerateChartPartContentLine(chartPart1, formulaCat, formulaVal, title);
GeneratePartContentPie(part, chartPart1, row, newe);
}
public DrawingMapping(ExcelContext xlsContext, DrawingsPart targetPart, bool isChartsheet)
: base(targetPart.XmlWriter)
{
this._xlsContext = xlsContext;
this._drawingsPart = targetPart;
this._isChartsheet = isChartsheet;
}
private void AppendSheetData(SheetData sheetData1, WorksheetPart worksheetPart, string drawingrID)
{
for (int rowIndex = 0; rowIndex < m_table.Rows.Count; rowIndex++)
{
Row row = new Row()
{
RowIndex = (UInt32Value)(rowIndex + 1U)
};
DataRow tableRow = m_table.Rows[rowIndex];
for (int colIndex = 0; colIndex < tableRow.ItemArray.Length; colIndex++)
{
Cell cell = new Cell();
CellValue cellValue = new CellValue();
object data = tableRow.ItemArray[colIndex];
if (data is int || data is float || data is double)
{
cellValue.Text = data.ToString();
cell.Append(cellValue);
}
else if (data is string)
{
cell.DataType = CellValues.SharedString;
string text = data.ToString();
if (!sharedStrings.Contains(text))
{
sharedStrings.Add(text);
}
cellValue.Text = sharedStrings.IndexOf(text).ToString();
cell.Append(cellValue);
}
else if (data is Image)
{
DrawingsPart drawingsPart = null;
Xdr.WorksheetDrawing worksheetDrawing = null;
if (worksheetPart.DrawingsPart == null)
{
drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(drawingrID);
worksheetDrawing = new Xdr.WorksheetDrawing();
worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
drawingsPart.WorksheetDrawing = worksheetDrawing;
}
else if (worksheetPart.DrawingsPart != null && worksheetPart.DrawingsPart.WorksheetDrawing != null)
{
drawingsPart = worksheetPart.DrawingsPart;
worksheetDrawing = worksheetPart.DrawingsPart.WorksheetDrawing;
}
string imagerId = GetNextRelationShipId();
Xdr.TwoCellAnchor cellAnchor = AddTwoCellAnchor(rowIndex, 1, rowIndex, 1, imagerId);
worksheetDrawing.Append(cellAnchor);
ImagePart imagePart = drawingsPart.AddNewPart <ImagePart>("image/png", imagerId);
GenerateImagePartContent(imagePart, data as Image);
}
row.Append(cell);
}
sheetData1.Append(row);
}
}
protected static ChartPart CreateChartPart(DrawingsPart drawingsPart)
{
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
return(chartPart);
}
public void WriteDrawings(WorksheetPart worksheetPart, OpenXmlWriter writer)
{
if (!_images.Any() || !_imageDetails.Any())
{
return;
}
var drawingPartId = "drawingPart1";
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(drawingPartId);
int imgCounter = 0;
foreach (var image in _images)
{
var imagePart1 = drawingsPart.AddNewPart <ImagePart>("image/png", "image" + imgCounter);
imgCounter++;
using (var ms = new MemoryStream())
{
image.Save(ms, ImageFormat.Png);
ms.Position = 0;
imagePart1.FeedData(ms);
}
}
using (var drawingsWriter = OpenXmlWriter.Create(drawingsPart))
{
drawingsWriter.WriteStartElement(new Xdr.WorksheetDrawing(), new List <OpenXmlAttribute>(), new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"),
new KeyValuePair <string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main")
});
foreach (var imageDetails in _imageDetails)
{
var twoCellAnchor1 = createImageAnchor(imageDetails);
drawingsWriter.WriteElement(twoCellAnchor1);
}
drawingsWriter.WriteEndElement();
}
var idAtt = new OpenXmlAttribute("id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", drawingPartId);
var pageMargins1 = new PageMargins()
{
Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D
};
// PageSetup pageSetup1 = new PageSetup() { PaperSize = (UInt32Value)9U, Orientation = OrientationValues.Portrait};
writer.WriteElement(pageMargins1);
//writer.WriteElement(pageSetup1);
writer.WriteStartElement(new Drawing(), new List <OpenXmlAttribute>()
{
idAtt
});
writer.WriteEndElement();
}
//https://github.com/OfficeDev/office-content/blob/master/en-us/OpenXMLCon/articles/281776d0-be75-46eb-8fdc-a1f656291175.md
//Here be dragons
internal override void AddData(Statistics stat)
{
// 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.
var chartPart = CreateChartPart(drawingsPart);
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace
.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild(new PlotArea());
plotArea.AppendChild(new Layout());
CreateHistogram(plotArea, stat, 0, 48650112U, 48672768U);
CreateCumulative(plotArea, stat, 1, 438381208U, 438380816U);
// Add the chart Legend.
chart.AppendChild(
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();
AppendGraphicFrame(drawingsPart, chartPart);
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
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());
}
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());
}
public void exec(System.Data.DataTable dt)
{
System.IO.File.Copy(template, copie, true);
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true);
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart wsPart = XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1");
//DocumentFormat.OpenXml.Spreadsheet.Columns columns = new Columns();
//for(int i=0;i<size.Length;i++)
//{
// DocumentFormat.OpenXml.Spreadsheet.Column c = new Column();
// c.CustomWidth = true;
// c.Min = (uint) i+1;
// c.Max = (uint) i+1;
// c.Width = size[i];
// columns.Append(c);
//}
//DocumentFormat.OpenXml.Spreadsheet.Columns cc = new Columns();
//cc.Append(new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = 1, Max = 3, CustomWidth = true, Width = 5 });
////wsPart.Worksheet.Append(cc);
//Worksheet ws = wsPart.Worksheet;
//ws.Append(cc);
DrawingsPart drawingsPart1 = wsPart.AddNewPart <DrawingsPart>("rId1");
XcelWin.GenerateDrawingsPart1Content(drawingsPart1);
ChartPart chartPart1 = drawingsPart1.AddNewPart <ChartPart>("rId1");
XcelWin.GenerateChartPart1Content(chartPart1);
Drawing drawing1 = new Drawing()
{
Id = "rId1"
};
wsPart.Worksheet.Append(drawing1);
workbookPart.Workbook.Save();
myWorkbook.Close();
}
/// <summary>
/// 生成工作表
/// </summary>
public void Generate(bool includeChart = false)
{
DrawingsPart drawingsPart = null;
var worksheetPart = _openXMLExcel.Document.WorkbookPart.AddNewPart <WorksheetPart>();
if (includeChart)
{
drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
}
_openXMLExcel.AddSheetToPart(worksheetPart, _sheetName, _sheetId);
CreateSheetBySAX(worksheetPart, drawingsPart);
if (drawingsPart != null)
{
CreateDrawingBySAX(drawingsPart);
}
}
public override C.Chart AddChart(DrawingsPart drawingsPart, EditingLanguage language)
{
var chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new C.ChartSpace()
{
EditingLanguage = new C.EditingLanguage()
{
Val = language.GetEnumMemberValue()
}
};
return(chartPart.ChartSpace.AppendChild(new C.Chart()
{
PlotArea = new C.PlotArea()
}));
}
public void Write(WorksheetPart part, Chart chart)
{
DrawingsPart drawingsPart1 = part.AddNewPart <DrawingsPart>("rId1");
GenerateDrawingsPart1Content(drawingsPart1, chart);
ChartPart chartPart1 = drawingsPart1.AddNewPart <ChartPart>("rId1");
GenerateChartPart1Content(chartPart1, chart);
var drawing = new Drawing()
{
Id = "rId1"
};
part.Worksheet.Append(drawing);
}
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 Chart getChartByTitle(WorksheetPart wsp, string title)
{
DrawingsPart dp = wsp.DrawingsPart;
IEnumerable <ChartPart> cps = dp.ChartParts;
ChartPart c = cps.ElementAt <ChartPart>(0);
//c.ChartSpace.Descendants<Chart>().First().Descendants<Title>().First();
//c.ChartSpace.Descendants<Chart>().First().Descendants<Title>().First().Des
Chart res = null;
foreach (ChartPart cp in cps)
{
if (cp.ChartSpace.Descendants <Chart>().First().Descendants <Title>().First().Descendants <DocumentFormat.OpenXml.Drawing.Run>().First().Text.Text == title)
{
res = cp.ChartSpace.Descendants <Chart>().First();
}
}
return(res);
}
public static void InsertImage(WorksheetPart sheet1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
{
//Inserting a drawing element in worksheet
//Make sure that the relationship id is same for drawing element in worksheet and its relationship part
int drawingPartId = GetNextRelationShipID(sheet1);
Drawing drawing1 = new Drawing()
{
Id = "rId" + drawingPartId.ToString()
};
//Check whether the WorksheetPart contains VmlDrawingParts (LegacyDrawing element)
if (sheet1.VmlDrawingParts == null)
{
//if there is no VMLDrawing part (LegacyDrawing element) exists, just append the drawing part to the sheet
sheet1.Worksheet.Append(drawing1);
}
else
{
//if VmlDrawingPart (LegacyDrawing element) exists, then find the index of legacy drawing in the sheet and inserts the new drawing element before VMLDrawing part
int legacyDrawingIndex = GetIndexofLegacyDrawing(sheet1);
if (legacyDrawingIndex != -1)
{
sheet1.Worksheet.InsertAt <OpenXmlElement>(drawing1, legacyDrawingIndex);
}
else
{
sheet1.Worksheet.Append(drawing1);
}
}
//Adding the drawings.xml part
DrawingsPart drawingsPart1 = sheet1.AddNewPart <DrawingsPart>("rId" + drawingPartId.ToString());
GenerateDrawingsPart1Content(drawingsPart1, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex);
//Adding the image
ImagePart imagePart1 = drawingsPart1.AddNewPart <ImagePart>("image/jpeg", "rId1");
imagePart1.FeedData(imageStream);
}
//Here be dragons
internal override void AddData(Statistics stat)
{
// 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.
var chartPart = CreateChartPart(drawingsPart);
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
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)
},
new VaryColors()
{
Val = false
}
));
// 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.
BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
{
Val = new UInt32Value((uint)0)
},
new Order()
{
Val = new UInt32Value((uint)0)
},
new SeriesText(new NumericValue()
{
Text = "Timeline"
})));
StringLiteral strLit =
barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData())
.AppendChild <StringLiteral>(new StringLiteral());
strLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Diffs.Count)
});
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((uint)stat.Diffs.Count)
});
uint i = 0;
foreach (var diff in stat.Diffs)
{
strLit.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(diff.TimeStamp.ToString()));
numLit.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(diff.Value.ToString()));
i++;
}
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
AppendCategoryAxis(plotArea, 48650112u, "Time, ms", 48672768U);
AppendValueAxis(plotArea, 48672768u, "Duration, ms", 48650112U);
// 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)
});
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
AppendGraphicFrame(drawingsPart, chartPart);
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
public BaseChart(IGlobal <Y> global, DrawingsPart drawingsPart)
{
_drawingsPart = drawingsPart;
_global = global ?? throw new ArgumentNullException(nameof(global));
}
internal override void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
{
DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();
writer.WriteStartElement(new Drawing()
{
Id = part.GetIdOfPart(drawingsPart)
});
writer.WriteEndElement();
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());
chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
{
Val = false
});
// Create a new clustered column chart.
PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
Layout layout1 = plotArea.AppendChild <Layout>(new Layout());
BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart());
barChart.Append(new BarDirection()
{
Val = BarDirectionValues.Bar
});
barChart.Append(new BarGrouping()
{
Val = BarGroupingValues.Stacked
});
barChart.Append(new GapWidth()
{
Val = (UInt16Value)75U
});
barChart.Append(new Overlap()
{
Val = 100
});
GanttTypeChart ganttChart = new GanttTypeChart(UserSettings);
var groupedData = GanttData
.GroupBy(x => x.Name);
List <GanttDataPairedSeries> ganttDataWithSeries = new List <GanttDataPairedSeries>();
for (int i = 0; i < groupedData.Max(x => x.Count()); i++)
{
// For each series create a hidden one for spacing.
BarChartSeries barChartSeriesHidden = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value((uint)(i * 2))
},
new Order()
{
Val = new UInt32Value((uint)(i * 2))
},
new SeriesText(new NumericValue()
{
Text = "Not Active"
})));
BarChartSeries barChartSeriesValue = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value((uint)(i * 2) + 1)
},
new Order()
{
Val = new UInt32Value((uint)(i * 2) + 1)
},
new SeriesText(new NumericValue()
{
Text = "Time Spent"
})));
ganttChart.SetChartShapeProperties(barChartSeriesHidden, visible: false);
ganttChart.SetChartShapeProperties(barChartSeriesValue, colorPoints: (uint)GanttData.Count);
var ganttData = new List <GanttData>();
foreach (var data in groupedData.Where(x => x.Count() >= i + 1))
{
ganttData.Add(data.ElementAt(i));
}
ganttDataWithSeries.Add(new GanttDataPairedSeries()
{
BarChartSeriesHidden = barChartSeriesHidden,
BarChartSeriesValue = barChartSeriesValue,
Values = ganttData
});
}
ganttChart.SetChartAxis(ganttDataWithSeries, groupedData.ToList());
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis (X axis).
ganttChart.SetGanttCategoryAxis(plotArea);
// Add the Value Axis (Y axis).
ganttChart.SetGanttValueAxis(plotArea, GanttData.Min(x => x.Start), GanttData.Max(x => x.End));
chartContainer.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
ganttChart.SetChartLocation(drawingsPart, chartPart, location);
}
private void LoadImage(Worksheet ws, WorksheetPart wsp, WorkbookPart wp)
{
string path = System.IO.Path.Combine(Server.MapPath("~/Scripts/Images"), "unnamed.png").ToString();
DrawingsPart dp = wsp.AddNewPart <DrawingsPart>();
ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
using (FileStream fs = new FileStream(path, FileMode.Open))
{
imgp.FeedData(fs);
}
NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
nvdp.Id = 1025;
nvdp.Name = "Picture 1";
nvdp.Description = "polymathlogo";
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(imgp);
blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
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;
System.Drawing.Bitmap bm = new System.Drawing.Bitmap(path);
//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.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
bm.Dispose();
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;
Position pos = new Position();
pos.X = 0;
pos.Y = 0;
Extent ext = new Extent();
ext.Cx = extents.Cx;
ext.Cy = extents.Cy;
AbsoluteAnchor anchor = new AbsoluteAnchor();
anchor.Position = pos;
anchor.Extent = ext;
anchor.Append(picture);
anchor.Append(new ClientData());
WorksheetDrawing wsd = new WorksheetDrawing();
wsd.Append(anchor);
Drawing drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imgp);
wsd.Save(dp);
ws.Append(drawing);
}
async void OnButtonClicked(object sender, EventArgs args)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), idMuestreo + "_" + tipoActividad + ".xlsx"), SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Consolidacion del muestreo"
};
//Proyect Data
InsertCell(1, 1, worksheetPart.Worksheet, "Datos del Muestreo");
InsertCell(2, 1, worksheetPart.Worksheet, "Nombre del Proyecto: ");
InsertCell(3, 1, worksheetPart.Worksheet, "Nombre del Muestreo: ");
InsertCell(4, 1, worksheetPart.Worksheet, "Actividad: ");
InsertCell(5, 1, worksheetPart.Worksheet, "ID Muestreo: ");
InsertCell(6, 1, worksheetPart.Worksheet, "Descripcion: ");
InsertCell(2, 2, worksheetPart.Worksheet, nombreProyecto);
InsertCell(3, 2, worksheetPart.Worksheet, nombreMuestreo);
InsertCell(4, 2, worksheetPart.Worksheet, tipoActividad);
InsertCell(5, 2, worksheetPart.Worksheet, idMuestreo);
InsertCell(6, 2, worksheetPart.Worksheet, desMuestreo);
//Name of tables
InsertCell(1, 4, worksheetPart.Worksheet, "Porcentaje general de tareas");
InsertCell(1, 8, worksheetPart.Worksheet, "Porcentaje para tareas productivas");
InsertCell(1, 12, worksheetPart.Worksheet, "Porcentaje para tareas contributivas");
InsertCell(1, 16, worksheetPart.Worksheet, "Porcentaje para tareas no productivas");
InsertCell(1, 20, worksheetPart.Worksheet, "Productividad por día");
//General table (Header and then data)
InsertCell(2, 4, worksheetPart.Worksheet, "Tarea");
InsertCell(2, 5, worksheetPart.Worksheet, "Total");
InsertCell(2, 6, worksheetPart.Worksheet, "Porcentaje");
createTable(worksheetPart.Worksheet, 3, 4, 3, generalData);
//TP table (Header and then data)
InsertCell(2, 8, worksheetPart.Worksheet, "Tarea");
InsertCell(2, 9, worksheetPart.Worksheet, "Total");
InsertCell(2, 10, worksheetPart.Worksheet, "Porcentaje");
createTable(worksheetPart.Worksheet, 3, 8, 3, tpDataA);
//TC table (Header and then data)
InsertCell(2, 12, worksheetPart.Worksheet, "Tarea");
InsertCell(2, 13, worksheetPart.Worksheet, "Total");
InsertCell(2, 14, worksheetPart.Worksheet, "Porcentaje");
createTable(worksheetPart.Worksheet, 3, 12, 3, tcDataA);
//TI table (Header and then data)
InsertCell(2, 16, worksheetPart.Worksheet, "Tarea");
InsertCell(2, 17, worksheetPart.Worksheet, "Total");
InsertCell(2, 18, worksheetPart.Worksheet, "Porcentaje");
createTable(worksheetPart.Worksheet, 3, 16, 3, tiDataA);
//Days table (Header and then data)
createTable(worksheetPart.Worksheet, 2, 20, 6, daysData);
//General Chart
DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
ExcelCharts.CreatePieChart(drawingsPart, "'Consolidacion del muestreo'!$D$3:$D$5", "'Consolidacion del muestreo'!$E$3:$E$5", 8, true, "General");
ExcelCharts.CreatePieChart(drawingsPart, "'Consolidacion del muestreo'!$H$3:$H$" + (2 + tpDataA.Count / 3).ToString(), "'Consolidacion del muestreo'!$I$3:$I$" + (2 + tpDataA.Count / 3).ToString(), 24, false, "TP");
ExcelCharts.CreatePieChart(drawingsPart, "'Consolidacion del muestreo'!$L$3:$L$" + (2 + tcDataA.Count / 3).ToString(), "'Consolidacion del muestreo'!$M$3:$M$" + (2 + tcDataA.Count / 3).ToString(), 40, false, "TC");
ExcelCharts.CreatePieChart(drawingsPart, "'Consolidacion del muestreo'!$P$3:$P$" + (2 + tiDataA.Count / 3).ToString(), "'Consolidacion del muestreo'!$Q$3:$Q$" + (2 + tiDataA.Count / 3).ToString(), 56, false, "TI");
ExcelCharts.CreateLineChart(drawingsPart, "'Consolidacion del muestreo'!$T$3:$T$" + (2 + (daysData.Count - 6) / 6).ToString(), "'Consolidacion del muestreo'!$U$3:$U$" + (2 + (daysData.Count - 6) / 6).ToString(), 72, false, "Productividad x Dia");
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
// string pathN = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), idMuestreo + "_" + tipoActividad + ".xlsx");
// ExcelCharts.CreatePieChart(pathN, 3, "'Consolidacion del muestreo'!$D$3:$D$5", "'Consolidacion del muestreo'!$E$3:$E$5", 8, 2);
ExperimentalFeatures.Enable(ExperimentalFeatures.EmailAttachments);
SendEmail("MAC - Proyecto (" + nombreProyecto + ") - Datos del muestreo (" + nombreMuestreo + ")", "Mensaje enviado usando el app móvil de Muestreo de Actividades Constructivas (MAC) \n Nombre del Proyecto: " + nombreProyecto + "\n Nombre del Muestreo: " + nombreMuestreo + "\n ID muestreo: " + idMuestreo + "\n Tipo Actividad: " + tipoActividad + "\n Descripcion: " + desMuestreo);
}
private static void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1, string relId, uint id, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, bool appendToDrawing, int width, int height)
{
xdr.WorksheetDrawing worksheetDrawing1;
if (!appendToDrawing)
{
worksheetDrawing1 = new xdr.WorksheetDrawing();
worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
}
else
{
worksheetDrawing1 = drawingsPart1.WorksheetDrawing;
}
xdr.TwoCellAnchor twoCellAnchor1 = new xdr.TwoCellAnchor()
{
EditAs = xdr.EditAsValues.OneCell
};
xdr.FromMarker fromMarker1 = new xdr.FromMarker();
xdr.ColumnId columnId1 = new xdr.ColumnId();
columnId1.Text = startColumnIndex.ToString();
xdr.ColumnOffset columnOffset1 = new xdr.ColumnOffset();
columnOffset1.Text = "0";
xdr.RowId rowId1 = new xdr.RowId();
rowId1.Text = startRowIndex.ToString();
xdr.RowOffset rowOffset1 = new xdr.RowOffset();
rowOffset1.Text = "0";
fromMarker1.Append(columnId1);
fromMarker1.Append(columnOffset1);
fromMarker1.Append(rowId1);
fromMarker1.Append(rowOffset1);
xdr.ToMarker toMarker1 = new xdr.ToMarker();
xdr.ColumnId columnId2 = new xdr.ColumnId();
columnId2.Text = endColumnIndex.ToString();
xdr.ColumnOffset columnOffset2 = new xdr.ColumnOffset();
columnOffset2.Text = "0";
xdr.RowId rowId2 = new xdr.RowId();
rowId2.Text = endRowIndex.ToString();
xdr.RowOffset rowOffset2 = new xdr.RowOffset();
rowOffset2.Text = "0";
toMarker1.Append(columnId2);
toMarker1.Append(columnOffset2);
toMarker1.Append(rowId2);
toMarker1.Append(rowOffset2);
xdr.Picture picture1 = new xdr.Picture();
xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new xdr.NonVisualPictureProperties();
xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new xdr.NonVisualDrawingProperties()
{
Id = (UInt32Value)id,
Name = Guid.NewGuid().ToString()
};
xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new xdr.NonVisualPictureDrawingProperties();
A.PictureLocks pictureLocks1 = new A.PictureLocks()
{
NoChangeAspect = true
};
nonVisualPictureDrawingProperties1.Append(pictureLocks1);
nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);
xdr.BlipFill blipFill1 = new xdr.BlipFill();
A.Blip blip1 = new A.Blip()
{
Link = relId,
};
blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
A.Stretch stretch1 = new A.Stretch();
A.FillRectangle fillRectangle1 = new A.FillRectangle();
stretch1.Append(fillRectangle1);
blipFill1.Append(blip1);
blipFill1.Append(stretch1);
xdr.ShapeProperties shapeProperties1 = new xdr.ShapeProperties();
A.Transform2D transform2D1 = new A.Transform2D();
A.Offset offset1 = new A.Offset()
{
X = 0L, Y = 0L
};
A.Extents extents1 = new A.Extents()
{
Cx = (int)Math.Round((decimal)width * 9525),
Cy = (int)Math.Round((decimal)height * 9525)
};
transform2D1.Append(offset1);
transform2D1.Append(extents1);
A.PresetGeometry presetGeometry1 = new A.PresetGeometry()
{
Preset = A.ShapeTypeValues.Rectangle
};
A.AdjustValueList adjustValueList1 = new A.AdjustValueList();
presetGeometry1.Append(adjustValueList1);
shapeProperties1.Append(transform2D1);
shapeProperties1.Append(presetGeometry1);
picture1.Append(nonVisualPictureProperties1);
picture1.Append(blipFill1);
picture1.Append(shapeProperties1);
xdr.ClientData clientData1 = new xdr.ClientData();
twoCellAnchor1.Append(fromMarker1);
twoCellAnchor1.Append(toMarker1);
twoCellAnchor1.Append(picture1);
twoCellAnchor1.Append(clientData1);
worksheetDrawing1.Append(twoCellAnchor1);
if (!appendToDrawing)
{
drawingsPart1.WorksheetDrawing = worksheetDrawing1;
}
}
/// <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 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();
}
}
internal virtual void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
{
DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();
writer.WriteStartElement(new Drawing()
{
Id = part.GetIdOfPart(drawingsPart)
});
writer.WriteEndElement();
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());
// Set chart title
chartContainer.AppendChild(ChartPropertySetter.SetTitle(ChartPropertySetter.ChartProperties.Title));
chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
{
Val = false
});
// Create a new clustered column chart.
PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
uint chartSeriesCounter = 0;
OpenXmlCompositeElement chart = ChartPropertySetter.CreateChart(plotArea);
foreach (var chartDataSeriesGrouped in ChartData.GroupBy(x => x.Series))
{
// Set chart and series depending on type.
OpenXmlCompositeElement chartSeries = ChartPropertySetter.CreateChartSeries(chartDataSeriesGrouped.Key, chartSeriesCounter, chart);
// Every method from chartPropertySetter can be overriden to customize chart export.
ChartPropertySetter.SetChartShapeProperties(chartSeries);
ChartPropertySetter.SetChartAxis(chartSeries, chartDataSeriesGrouped.ToList());
chartSeriesCounter++;
}
chart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
chart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis (X axis).
ChartPropertySetter.SetLineCategoryAxis(plotArea);
// Add the Value Axis (Y axis).
ChartPropertySetter.SetValueAxis(plotArea);
ChartPropertySetter.SetLegend(chartContainer);
ChartPropertySetter.SetChartLocation(drawingsPart, chartPart, location);
}
protected void AddImagePart(string id, string base64Image)
{
ImagePart = DrawingsPart.AddNewPart <ImagePart>("image/tiff", id);
StreamImagePart(base64Image);
}
/// <summary> /// 添加图表 /// </summary> /// <param name="drawingsPart"></param> /// <param name="language"></param> /// <returns></returns> public abstract C.Chart AddChart(DrawingsPart drawingsPart, EditingLanguage language);
// Generates content of drawingsPart1.
//private void GenerateDrawingsPartContent(DrawingsPart drawingsPart, Chart chart)
//{
// var worksheetDrawing = new Xdr.WorksheetDrawing();
// worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
// worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
// var twoCellAnchor = new Xdr.TwoCellAnchor();
// var fromMarker = new Xdr.FromMarker();
// var columnIdFrom = new Xdr.ColumnId();
// columnIdFrom.Text = chart.ColumnFrom.ToString();
// Xdr.ColumnOffset columnOffsetFrom = new Xdr.ColumnOffset();
// columnOffsetFrom.Text = "11908";
// var rowIdFrom = new Xdr.RowId();
// rowIdFrom.Text = chart.RowFrom.ToString();
// Xdr.RowOffset rowOffsetFrom = new Xdr.RowOffset();
// rowOffsetFrom.Text = "9523";
// fromMarker.Append(columnIdFrom);
// fromMarker.Append(columnOffsetFrom);
// fromMarker.Append(rowIdFrom);
// fromMarker.Append(rowOffsetFrom);
// var toMarker = new Xdr.ToMarker();
// var columnIdTo = new Xdr.ColumnId();
// columnIdTo.Text = chart.ColumnTo.ToString();
// Xdr.ColumnOffset columnOffsetTo = new Xdr.ColumnOffset();
// columnOffsetTo.Text = "250032";
// var rowIdTo = new Xdr.RowId();
// rowIdTo.Text = chart.RowTo.ToString();
// Xdr.RowOffset rowOffsetTo = new Xdr.RowOffset();
// rowOffsetTo.Text = "29764";
// toMarker.Append(columnIdTo);
// toMarker.Append(columnOffsetTo);
// toMarker.Append(rowIdTo);
// toMarker.Append(rowOffsetTo);
// var graphicFrame = new Xdr.GraphicFrame() { Macro = "" };
// var nonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties();
// var nonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = chart.TableKey };
// var nonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties();
// var graphicFrameLocks = new A.GraphicFrameLocks();
// nonVisualGraphicFrameDrawingProperties.Append(graphicFrameLocks);
// nonVisualGraphicFrameProperties.Append(nonVisualDrawingProperties);
// nonVisualGraphicFrameProperties.Append(nonVisualGraphicFrameDrawingProperties);
// var transform = new Xdr.Transform();
// var offset = new A.Offset() { X = 0L, Y = 0L };
// var extents = new A.Extents() { Cx = 0L, Cy = 0L };
// transform.Append(offset);
// transform.Append(extents);
// var graphic = new A.Graphic();
// var graphicData = new A.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" };
// var chartReference = new C.ChartReference() { Id = "rId1" };
// chartReference.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
// chartReference.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
// graphicData.Append(chartReference);
// graphic.Append(graphicData);
// graphicFrame.Append(nonVisualGraphicFrameProperties);
// graphicFrame.Append(transform);
// graphicFrame.Append(graphic);
// var clientData = new Xdr.ClientData();
// twoCellAnchor.Append(fromMarker);
// twoCellAnchor.Append(toMarker);
// twoCellAnchor.Append(graphicFrame);
// twoCellAnchor.Append(clientData);
// worksheetDrawing.Append(twoCellAnchor);
// drawingsPart.WorksheetDrawing = worksheetDrawing;
//}
//// Generates content of chartPart1.
//private void GenerateChartPartContent(ChartPart chartPart, Chart chart)
//{
// var chartSpace = new C.ChartSpace();
// chartSpace.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
// chartSpace.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
// chartSpace.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
// var editingLanguage = new C.EditingLanguage() { Val = "en-US" };
// var style = new C.Style() { Val = 10 };
// var _chart = new C.Chart();
// var title = new C.Title();
// var chartText = new C.ChartText();
// var richText = new C.RichText();
// var bodyProperties = new A.BodyProperties();
// var listStyle = new A.ListStyle();
// var paragraph = new A.Paragraph();
// var paragraphProperties = new A.ParagraphProperties();
// var defaultRunProperties = new A.DefaultRunProperties();
// paragraphProperties.Append(defaultRunProperties);
// var run = new A.Run();
// var runProperties = new A.RunProperties() { Language = "en-US", FontSize = 800 };
// var text = new A.Text();
// text.Text = chart.Title;
// run.Append(runProperties);
// run.Append(text);
// //A.Run run2 = new A.Run();
// //A.RunProperties runProperties2 = new A.RunProperties() { Language = "en-US", FontSize = 800, Baseline = 0 };
// //A.Text text2 = new A.Text();
// //text2.Text = title;
// //run2.Append(runProperties2);
// //run2.Append(text2);
// var endParagraphRunProperties = new A.EndParagraphRunProperties() { Language = "en-US", FontSize = 800 };
// paragraph.Append(paragraphProperties);
// paragraph.Append(run);
// //paragraph1.Append(run2);
// paragraph.Append(endParagraphRunProperties);
// richText.Append(bodyProperties);
// richText.Append(listStyle);
// richText.Append(paragraph);
// chartText.Append(richText);
// var layout1 = new C.Layout();
// var manualLayout1 = new C.ManualLayout();
// var leftMode1 = new C.LeftMode() { Val = C.LayoutModeValues.Edge };
// var topMode1 = new C.TopMode() { Val = C.LayoutModeValues.Edge };
// var left1 = new C.Left() { Val = 0.14936699324798144D };
// var top1 = new C.Top() { Val = 7.5867300613079197E-2D };
// manualLayout1.Append(leftMode1);
// manualLayout1.Append(topMode1);
// manualLayout1.Append(left1);
// manualLayout1.Append(top1);
// layout1.Append(manualLayout1);
// title.Append(chartText);
// title.Append(layout1);
// var plotArea = new C.PlotArea();
// var layout2 = new C.Layout();
// var manualLayout2 = new C.ManualLayout();
// var layoutTarget2 = new C.LayoutTarget() { Val = C.LayoutTargetValues.Inner };
// var leftMode2 = new C.LeftMode() { Val = C.LayoutModeValues.Edge };
// var topMode2 = new C.TopMode() { Val = C.LayoutModeValues.Edge };
// var left2 = new C.Left() { Val = 0.10245464404093282D };
// var top2 = new C.Top() { Val = 4.7416814491091287E-2D };
// var width2 = new C.Width() { Val = 0.88919609910728359D };
// // chart height inside word drawing part
// //C.Height height1 = new C.Height() { Val = 0.81899924741893582D }; // original generated value
// var height2 = new C.Height() { Val = 0.86 };
// manualLayout2.Append(layoutTarget2);
// manualLayout2.Append(leftMode2);
// manualLayout2.Append(topMode2);
// manualLayout2.Append(left2);
// manualLayout2.Append(top2);
// manualLayout2.Append(width2);
// manualLayout2.Append(height2);
// layout2.Append(manualLayout2);
// var areaChart = new C.AreaChart();
// var grouping = new C.Grouping() { Val = C.GroupingValues.Standard };
// var areaChartSeries = new C.AreaChartSeries();
// var index = new C.Index() { Val = (UInt32Value)0U };
// var order = new C.Order() { Val = (UInt32Value)0U };
// var seriesText = new C.SeriesText();
// var stringReference = new C.StringReference();
// var formula1 = new C.Formula();
// formula1.Text = chart.AxisX;
// var stringCache = new C.StringCache();
// var pointCount1 = new C.PointCount() { Val = (UInt32Value)1U };
// var stringPoint = new C.StringPoint() { Index = (UInt32Value)0U };
// var numericValue = new C.NumericValue();
// numericValue.Text = chart.LegendTitle;
// stringPoint.Append(numericValue);
// stringCache.Append(pointCount1);
// stringCache.Append(stringPoint);
// stringReference.Append(formula1);
// stringReference.Append(stringCache);
// seriesText.Append(stringReference);
// var values = new C.Values();
// var numberReference = new C.NumberReference();
// var formula2 = new C.Formula();
// formula2.Text = chart.AxisY;
// C.NumberingCache numberingCache = new C.NumberingCache();
// C.FormatCode formatCode = new C.FormatCode();
// formatCode.Text = "0.00%";
// /* years */
// C.PointCount pointCount2 = new C.PointCount() { Val = UInt32Value.FromUInt32((uint)chart.Labels.Count) };
// numberingCache.Append(formatCode);
// numberingCache.Append(pointCount2);
// for (int i = 0; i < chart.Labels.Count; i++)
// {
// C.NumericPoint numericPoint = new C.NumericPoint() { Index = UInt32Value.FromUInt32((uint)i) };
// C.NumericValue _numericValue = new C.NumericValue();
// numericValue.Text = string.Format("{0}E-2", chart.Labels[i]);
// numericPoint.Append(_numericValue);
// numberingCache.Append(numericPoint);
// }
// numberReference.Append(formula2);
// numberReference.Append(numberingCache);
// values.Append(numberReference);
// areaChartSeries.Append(index);
// areaChartSeries.Append(order);
// areaChartSeries.Append(seriesText);
// areaChartSeries.Append(values);
// var axisId1 = new C.AxisId() { Val = (UInt32Value)78173696U };
// var axisId2 = new C.AxisId() { Val = (UInt32Value)78175232U };
// areaChart.Append(grouping);
// areaChart.Append(areaChartSeries);
// areaChart.Append(axisId1);
// areaChart.Append(axisId2);
// var categoryAxis1 = new C.CategoryAxis();
// var axisId3 = new C.AxisId() { Val = (UInt32Value)78173696U };
// var scaling1 = new C.Scaling();
// var orientation1 = new C.Orientation() { Val = C.OrientationValues.MinMax };
// scaling1.Append(orientation1);
// var axisPosition1 = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom };
// var majorTickMark1 = new C.MajorTickMark() { Val = C.TickMarkValues.None };
// var tickLabelPosition1 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
// var crossingAxis1 = new C.CrossingAxis() { Val = (UInt32Value)78175232U };
// var crosses1 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
// var autoLabeled1 = new C.AutoLabeled() { Val = true };
// var labelAlignment1 = new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center };
// var labelOffset1 = new C.LabelOffset() { Val = (UInt16Value)100U };
// categoryAxis1.Append(axisId3);
// categoryAxis1.Append(scaling1);
// categoryAxis1.Append(axisPosition1);
// categoryAxis1.Append(majorTickMark1);
// categoryAxis1.Append(tickLabelPosition1);
// categoryAxis1.Append(crossingAxis1);
// categoryAxis1.Append(crosses1);
// categoryAxis1.Append(autoLabeled1);
// categoryAxis1.Append(labelAlignment1);
// categoryAxis1.Append(labelOffset1);
// var valueAxis1 = new C.ValueAxis();
// var axisId4 = new C.AxisId() { Val = (UInt32Value)78175232U };
// var scaling2 = new C.Scaling();
// var orientation2 = new C.Orientation() { Val = C.OrientationValues.MinMax };
// scaling2.Append(orientation2);
// var axisPosition2 = new C.AxisPosition() { Val = C.AxisPositionValues.Left };
// var majorGridlines1 = new C.MajorGridlines();
// var numberingFormat1 = new C.NumberingFormat() { FormatCode = "0.00%", SourceLinked = true };
// var majorTickMark2 = new C.MajorTickMark() { Val = C.TickMarkValues.None };
// var tickLabelPosition2 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
// var crossingAxis2 = new C.CrossingAxis() { Val = (UInt32Value)78173696U };
// var crosses2 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
// var crossBetween1 = new C.CrossBetween() { Val = C.CrossBetweenValues.MidpointCategory };
// valueAxis1.Append(axisId4);
// valueAxis1.Append(scaling2);
// valueAxis1.Append(axisPosition2);
// valueAxis1.Append(majorGridlines1);
// valueAxis1.Append(numberingFormat1);
// valueAxis1.Append(majorTickMark2);
// valueAxis1.Append(tickLabelPosition2);
// valueAxis1.Append(crossingAxis2);
// valueAxis1.Append(crosses2);
// valueAxis1.Append(crossBetween1);
// var dataTable1 = new C.DataTable();
// var showHorizontalBorder1 = new C.ShowHorizontalBorder() { Val = true };
// var showVerticalBorder1 = new C.ShowVerticalBorder() { Val = true };
// var showOutlineBorder1 = new C.ShowOutlineBorder() { Val = true };
// var showKeys1 = new C.ShowKeys() { Val = true };
// dataTable1.Append(showHorizontalBorder1);
// dataTable1.Append(showVerticalBorder1);
// dataTable1.Append(showOutlineBorder1);
// dataTable1.Append(showKeys1);
// C.ShapeProperties shapeProperties1 = new C.ShapeProperties();
// A.Outline outline1 = new A.Outline();
// A.NoFill noFill1 = new A.NoFill();
// outline1.Append(noFill1);
// shapeProperties1.Append(outline1);
// plotArea.Append(layout2);
// plotArea.Append(areaChart);
// plotArea.Append(categoryAxis1);
// plotArea.Append(valueAxis1);
// plotArea.Append(dataTable1);
// plotArea.Append(shapeProperties1);
// var plotVisibleOnly1 = new C.PlotVisibleOnly() { Val = true };
// var displayBlanksAs1 = new C.DisplayBlanksAs() { Val = C.DisplayBlanksAsValues.Zero };
// _chart.Append(title);
// _chart.Append(plotArea);
// _chart.Append(plotVisibleOnly1);
// _chart.Append(displayBlanksAs1);
// var textProperties1 = new C.TextProperties();
// var bodyProperties2 = new A.BodyProperties();
// var listStyle2 = new A.ListStyle();
// var paragraph2 = new A.Paragraph();
// var paragraphProperties2 = new A.ParagraphProperties();
// var defaultRunProperties2 = new A.DefaultRunProperties() { FontSize = 700 };
// paragraphProperties2.Append(defaultRunProperties2);
// var endParagraphRunProperties2 = new A.EndParagraphRunProperties() { Language = "en-US" };
// paragraph2.Append(paragraphProperties2);
// paragraph2.Append(endParagraphRunProperties2);
// textProperties1.Append(bodyProperties2);
// textProperties1.Append(listStyle2);
// textProperties1.Append(paragraph2);
// var printSettings1 = new C.PrintSettings();
// var headerFooter1 = new C.HeaderFooter();
// var pageMargins1 = new C.PageMargins() { Left = 0.70000000000000018D, Right = 0.70000000000000018D, Top = 0.75000000000000022D, Bottom = 0.75000000000000022D, Header = 0.3000000000000001D, Footer = 0.3000000000000001D };
// var pageSetup1 = new C.PageSetup() { Orientation = C.PageSetupOrientationValues.Landscape };
// printSettings1.Append(headerFooter1);
// printSettings1.Append(pageMargins1);
// printSettings1.Append(pageSetup1);
// chartSpace.Append(editingLanguage);
// chartSpace.Append(style);
// chartSpace.Append(_chart);
// chartSpace.Append(textProperties1);
// chartSpace.Append(printSettings1);
// var chartShapeProperties2 = new ChartShapeProperties();
// var outline2 = new DocumentFormat.OpenXml.Drawing.Outline();
// var noFill2 = new NoFill();
// outline2.Append(noFill2);
// chartShapeProperties2.Append(outline2);
// //chartSpace.Append(chartShapeProperties2);
// chartPart.ChartSpace = chartSpace;
//}
// Generates content of drawingsPart1.
private void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1, Chart chart)
{
Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing();
worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor();
Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
Xdr.ColumnId columnId1 = new Xdr.ColumnId();
columnId1.Text = chart.ColumnFrom.ToString();
Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
columnOffset1.Text = "0";
Xdr.RowId rowId1 = new Xdr.RowId();
rowId1.Text = chart.RowFrom.ToString();
Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
rowOffset1.Text = "0";
fromMarker1.Append(columnId1);
fromMarker1.Append(columnOffset1);
fromMarker1.Append(rowId1);
fromMarker1.Append(rowOffset1);
Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
Xdr.ColumnId columnId2 = new Xdr.ColumnId();
columnId2.Text = chart.ColumnTo.ToString();
Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
columnOffset2.Text = "238124";
Xdr.RowId rowId2 = new Xdr.RowId();
rowId2.Text = chart.RowTo.ToString();
Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
rowOffset2.Text = "20241";
toMarker1.Append(columnId2);
toMarker1.Append(columnOffset2);
toMarker1.Append(rowId2);
toMarker1.Append(rowOffset2);
Xdr.GraphicFrame graphicFrame1 = new Xdr.GraphicFrame()
{
Macro = ""
};
Xdr.NonVisualGraphicFrameProperties nonVisualGraphicFrameProperties1 = new Xdr.NonVisualGraphicFrameProperties();
Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties()
{
Id = (UInt32Value)2U, Name = chart.TableKey
};
A.NonVisualDrawingPropertiesExtensionList nonVisualDrawingPropertiesExtensionList1 = new A.NonVisualDrawingPropertiesExtensionList();
A.NonVisualDrawingPropertiesExtension nonVisualDrawingPropertiesExtension1 = new A.NonVisualDrawingPropertiesExtension()
{
Uri = "{FF2B5EF4-FFF2-40B4-BE49-F238E27FC236}"
};
OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<a16:creationId xmlns:a16=\"http://schemas.microsoft.com/office/drawing/2014/main\" id=\"{D4EA194D-E283-4B88-B3BE-83B3557FCE42}\" />");
nonVisualDrawingPropertiesExtension1.Append(openXmlUnknownElement1);
nonVisualDrawingPropertiesExtensionList1.Append(nonVisualDrawingPropertiesExtension1);
nonVisualDrawingProperties1.Append(nonVisualDrawingPropertiesExtensionList1);
Xdr.NonVisualGraphicFrameDrawingProperties nonVisualGraphicFrameDrawingProperties1 = new Xdr.NonVisualGraphicFrameDrawingProperties();
A.GraphicFrameLocks graphicFrameLocks1 = new A.GraphicFrameLocks();
nonVisualGraphicFrameDrawingProperties1.Append(graphicFrameLocks1);
nonVisualGraphicFrameProperties1.Append(nonVisualDrawingProperties1);
nonVisualGraphicFrameProperties1.Append(nonVisualGraphicFrameDrawingProperties1);
Xdr.Transform transform1 = new Xdr.Transform();
A.Offset offset1 = new A.Offset()
{
X = 0L, Y = 0L
};
A.Extents extents1 = new A.Extents()
{
Cx = 0L, Cy = 0L
};
transform1.Append(offset1);
transform1.Append(extents1);
A.Graphic graphic1 = new A.Graphic();
A.GraphicData graphicData1 = new A.GraphicData()
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
};
C.ChartReference chartReference1 = new C.ChartReference()
{
Id = "rId1"
};
chartReference1.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
chartReference1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
graphicData1.Append(chartReference1);
graphic1.Append(graphicData1);
graphicFrame1.Append(nonVisualGraphicFrameProperties1);
graphicFrame1.Append(transform1);
graphicFrame1.Append(graphic1);
Xdr.ClientData clientData1 = new Xdr.ClientData();
twoCellAnchor1.Append(fromMarker1);
twoCellAnchor1.Append(toMarker1);
twoCellAnchor1.Append(graphicFrame1);
twoCellAnchor1.Append(clientData1);
worksheetDrawing1.Append(twoCellAnchor1);
drawingsPart1.WorksheetDrawing = worksheetDrawing1;
}
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);
}
