private void writeTables(OpenXmlWriter writer, WorksheetPart part, ref int tableCount)
{
if (!_tables.Any())
{
return;
}
var countAtt = new OpenXmlAttribute("count", null, _tables.Count.ToString());
writer.WriteStartElement(new TableParts(), new List <OpenXmlAttribute>()
{
countAtt
});
foreach (var table in _tables)
{
var tableId = "table" + tableCount;
var tableDefinition = part.AddNewPart <TableDefinitionPart>(tableId);
tableDefinition.Table = table.Value.GetTableDefinition(tableCount, table.Key.ColumnIndex, table.Key.RowIndex);
var idAtt = new OpenXmlAttribute("id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", tableId);
writer.WriteStartElement(new TablePart(), new List <OpenXmlAttribute>()
{
idAtt
});
writer.WriteEndElement();
tableCount++;
}
writer.WriteEndElement();
}
public static DrawingsPart GetDrawingsPart(this WorksheetPart sheet)
{
if (sheet.DrawingsPart == null)
{
sheet.AddNewPart <DrawingsPart>();
}
return(sheet.DrawingsPart);
}
// Adds child parts and generates content of the specified part.
public void CreateWorksheetPart(WorksheetPart part)
{
SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = part.AddNewPart <SpreadsheetPrinterSettingsPart>("rId1");
GenerateSpreadsheetPrinterSettingsPart1Content(spreadsheetPrinterSettingsPart1);
GeneratePartContent(part);
}
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);
}
}
// TODO: cleanup?
public static void InsertTrackingURI(this SpreadsheetDocument workbook, string url)
{
WorkbookPart wbPart = workbook.WorkbookPart;
var uri = new System.Uri(url);
Sheet sheet = wbPart.Workbook.Descendants <Sheet>().First();
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
DrawingsPart drawingsPart;
if (wsPart.DrawingsPart == null)
{
drawingsPart = wsPart.AddNewPart <DrawingsPart>();
}
else
{
drawingsPart = wsPart.DrawingsPart;
}
var extRel = drawingsPart.AddExternalRelationship("http://schemas.openxmlformats.org/officeDocument/2006/relationships/image", uri);
uint id;
if (drawingsPart.WorksheetDrawing == null)
{
id = 1;
}
else
{
id = Convert.ToUInt32(drawingsPart.WorksheetDrawing.Count()) + 1;
}
Random r = new Random();
int rInt = r.Next(300, 2000);
if (wsPart.Worksheet.Elements <Drawing>().Count() == 0)
{
GenerateDrawingsPart1Content(drawingsPart, extRel.Id, id, rInt, 0, rInt, 0, false, 0, 0);
}
else
{
GenerateDrawingsPart1Content(drawingsPart, extRel.Id, id, rInt, 0, rInt, 0, true, 0, 0);
}
wsPart.GetIdOfPart(drawingsPart);
List <string> drawingsList = new List <string>();
foreach (var drawing in wsPart.Worksheet.Elements <Drawing>())
{
drawingsList.Add(drawing.Id);
}
string drPartId = wsPart.GetIdOfPart(drawingsPart);
Drawing drawingNew = new Drawing()
{
Id = drPartId
};
if (!drawingsList.Contains(drPartId))
{
wsPart.Worksheet.Append(drawingNew);
}
}
private static void WriteTable(WorksheetPart worksheetPart, uint tableID, object[,] values, uint nRows, uint nColumns)
{
string range = ToRange(1, nRows, 1, nColumns);
var autoFilter = new AutoFilter
{
Reference = range
};
var tableColumns = new TableColumns()
{
Count = nColumns
};
for (int c = 0; c < nColumns; c++)
{
var column = new TableColumn()
{
Id = (UInt32Value)(c + 1U),
Name = values[0, c].ToString()
};
tableColumns.Append(column);
}
var tableStyleInfo = new TableStyleInfo()
{
Name = "TableStyleMedium2",
ShowFirstColumn = true,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = true
};
var table = new Table()
{
Id = tableID,
Name = $"Table{tableID}",
DisplayName = $"Table{tableID}",
Reference = range,
TotalsRowShown = false
};
table.Append(autoFilter);
table.Append(tableColumns);
table.Append(tableStyleInfo);
//var tableParts = worksheetPart.Worksheet.AppendChild<TableParts>(new TableParts());
//tableParts.AppendChild<TablePart>(new TablePart());
var tableDefinitionPart = worksheetPart.AddNewPart <TableDefinitionPart>($"rId{tableID}");
tableDefinitionPart.Table = table;
}
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();
}
public ChartBuilder(ChartOptions options, WorksheetPart worksheetPart)
{
this.Options = options;
if (worksheetPart.DrawingsPart == null)
{
drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
}
else
{
drawingsPart = worksheetPart.DrawingsPart;
}
if (drawingsPart.WorksheetDrawing == null)
{
// WorksheetDrawing is used to position the chart on the worksheet
drawingsPart.WorksheetDrawing = new SS.WorksheetDrawing();
drawingsPart.WorksheetDrawing.AddNamespaceDeclaration("xdr", NamespaceSpreadsheetDrawing);
drawingsPart.WorksheetDrawing.AddNamespaceDeclaration("a", NamespaceMain);
var drawing = new Drawing {
Id = worksheetPart.GetIdOfPart(drawingsPart)
};
worksheetPart.Worksheet.Append(drawing);
worksheetPart.Worksheet.Save();
}
// Add a new chart part, chart space, and chart
chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new C.ChartSpace();
chartPart.ChartSpace.AddNamespaceDeclaration("c", NamespaceChart);
chartPart.ChartSpace.AddNamespaceDeclaration("a", NamespaceMain);
chartPart.ChartSpace.AddNamespaceDeclaration("r", NamespaceRelationships);
chart = chartPart.ChartSpace.AppendChild <C.Chart>(new C.Chart());
// Add a new plot area and layout
plotArea = chart.AppendChild <C.PlotArea>(new C.PlotArea());
plotArea.AppendChild <C.Layout>(new C.Layout());
this.AppendOptions();
}
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();
}
//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();
}
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();
}
private void DefineTable(WorksheetPart worksheetPart, LineItem[] lineItems)
{
TableDefinitionPart tableDefinitionPart = worksheetPart.AddNewPart <TableDefinitionPart>("rId" + (worksheetPart.TableDefinitionParts.Count() + 1));
int tableNo = worksheetPart.TableDefinitionParts.Count();
string reference = $"A1:{((char)(64 + _fields.Length))}{lineItems.Length}";
Table table = new Table()
{
Id = (UInt32)tableNo, Name = "Table" + tableNo, DisplayName = "Table" + tableNo, Reference = reference, TotalsRowShown = false
};
AutoFilter autoFilter = new AutoFilter()
{
Reference = reference
};
TableColumns tableColumns = new TableColumns()
{
Count = (UInt32)_fields.Length
};
for (int i = 0; i < _fields.Length; i++)
{
tableColumns.Append(new TableColumn()
{
Id = (UInt32)(i + 1), Name = _fields[i]
});
}
table.Append(autoFilter);
table.Append(tableColumns);
tableDefinitionPart.Table = table;
TableParts tableParts = new TableParts()
{
Count = (UInt32)1
};
TablePart tablePart = new TablePart()
{
Id = "rId" + tableNo
};
tableParts.Append(tablePart);
worksheetPart.Worksheet.Append(tableParts);
}
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);
}
/// <summary>
/// Creates all the necessary parts for the workbook
/// </summary>
/// <param name="document">The spreadsheet document to create</param>
private void CreateParts(SpreadsheetDocument document)
{
WorkbookPart workbookPart = document.AddWorkbookPart();
GenerateWorkbookContent(workbookPart);
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPart1Content(workbookStylesPart);
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1");
GenerateWorksheetContent(worksheetPart);
TableDefinitionPart tableDefinitionPart = worksheetPart.AddNewPart <TableDefinitionPart>("rId2");
GenerateTableDefinition(tableDefinitionPart);
}
public static void InsertNamedSheetView(string xlsxPath)
{
if (xlsxPath == null)
{
throw new ArgumentNullException(nameof(xlsxPath));
}
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(xlsxPath, true))
{
WorksheetPart worksheetPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <WorksheetPart>().First();
NamedSheetViewsPart namedSheetViewsPart = worksheetPart.AddNewPart <NamedSheetViewsPart>();
NamedSheetView namedSheetView = new NamedSheetView();
namedSheetView.Id = "{" + System.Guid.NewGuid().ToString().ToUpper() + "}";
namedSheetView.Name = "testview";
namedSheetViewsPart.NamedSheetViews = new NamedSheetViews(
namedSheetView);
namedSheetViewsPart.NamedSheetViews.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
}
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);
}
public static void AddImage(WorksheetPart worksheetPart,
Stream imageStream, string imgDesc,
int colNumber, int rowNumber)
{
// We need the image stream more than once, thus we create a memory copy
MemoryStream imageMemStream = new MemoryStream();
imageStream.Position = 0;
imageStream.CopyTo(imageMemStream);
imageStream.Position = 0;
var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
if (!worksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any())
{
worksheetPart.Worksheet.Append(new Drawing {
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
}
if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
}
var worksheetDrawing = drawingsPart.WorksheetDrawing;
Bitmap bm = new Bitmap(imageMemStream);
var imagePart = drawingsPart.AddImagePart(GetImagePartTypeByBitmap(bm));
imagePart.FeedData(imageStream);
A.Extents extents = new A.Extents();
var extentsCx = bm.Width * (long)(914400 / bm.HorizontalResolution);
var extentsCy = bm.Height * (long)(914400 / bm.VerticalResolution);
bm.Dispose();
var colOffset = 0;
var rowOffset = 0;
var nvps = worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>();
var nvpId = nvps.Count() > 0
? (UInt32Value)worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1
: 1U;
var oneCellAnchor = new Xdr.OneCellAnchor(
new Xdr.FromMarker
{
ColumnId = new Xdr.ColumnId((colNumber - 1).ToString()),
RowId = new Xdr.RowId((rowNumber - 1).ToString()),
ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()),
RowOffset = new Xdr.RowOffset(rowOffset.ToString())
},
new Xdr.Extent {
Cx = extentsCx, Cy = extentsCy
},
new Xdr.Picture(
new Xdr.NonVisualPictureProperties(
new Xdr.NonVisualDrawingProperties {
Id = nvpId, Name = "Picture " + nvpId, Description = imgDesc
},
new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks {
NoChangeAspect = true
})
),
new Xdr.BlipFill(
new A.Blip {
Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print
},
new A.Stretch(new A.FillRectangle())
),
new Xdr.ShapeProperties(
new A.Transform2D(
new A.Offset {
X = 0, Y = 0
},
new A.Extents {
Cx = extentsCx, Cy = extentsCy
}
),
new A.PresetGeometry {
Preset = A.ShapeTypeValues.Rectangle
}
)
),
new Xdr.ClientData()
);
worksheetDrawing.Append(oneCellAnchor);
}
public Drawing BuildDrawing(WorksheetPart worksheetPart, List <ExcelImage> excelImages)
{
var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
var worksheetDrawing = new WorksheetDrawing();
var oneCellAnchors = new List <OneCellAnchor>();
foreach (var excelImage in excelImages)
{
var imagePart = drawingsPart.AddImagePart(excelImage.Type);
using (var stream = new MemoryStream(excelImage.ImageBytes))
{
imagePart.FeedData(stream);
}
long extentsCx, extentsCy;
using (var stream = new MemoryStream(excelImage.ImageBytes))
{
var bm = new Bitmap(stream);
extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
bm.Dispose();
}
const int colOffset = 0;
const int rowOffset = 0;
var nvps = worksheetDrawing.Descendants <NonVisualDrawingProperties>();
var nvpId = nvps.Any() ?
(UInt32Value)worksheetDrawing.Descendants <NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 :
1U;
var oneCellAnchor = new OneCellAnchor(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker
{
ColumnId = new ColumnId((excelImage.ColNumber - 1).ToString()),
RowId = new RowId((excelImage.RowNumber - 1).ToString()),
ColumnOffset = new ColumnOffset(colOffset.ToString()),
RowOffset = new RowOffset(rowOffset.ToString())
},
new Extent {
Cx = extentsCx, Cy = extentsCy
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(
new NonVisualPictureProperties(
new NonVisualDrawingProperties {
Id = nvpId, Name = "Picture " + nvpId
},
new NonVisualPictureDrawingProperties(new DocumentFormat.OpenXml.Drawing.PictureLocks {
NoChangeAspect = true
})
),
new BlipFill(
new DocumentFormat.OpenXml.Drawing.Blip {
Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
},
new DocumentFormat.OpenXml.Drawing.Stretch(new DocumentFormat.OpenXml.Drawing.FillRectangle())
),
new ShapeProperties(
new DocumentFormat.OpenXml.Drawing.Transform2D(
new DocumentFormat.OpenXml.Drawing.Offset {
X = 0, Y = 0
},
new DocumentFormat.OpenXml.Drawing.Extents {
Cx = extentsCx, Cy = extentsCy
}
),
new DocumentFormat.OpenXml.Drawing.PresetGeometry {
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle
}
)
),
new ClientData()
);
oneCellAnchors.Add(oneCellAnchor);
}
using (var drawingsPartWriter = OpenXmlWriter.Create(drawingsPart))
{
drawingsPartWriter.WriteStartElement(worksheetDrawing);
foreach (var oneCellAnchor in oneCellAnchors)
{
drawingsPartWriter.WriteElement(oneCellAnchor);
}
drawingsPartWriter.WriteEndElement();
drawingsPartWriter.Close();
}
return(new Drawing
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
}
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 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);
}
public static void AddWorksheet(SpreadsheetDocument sDoc, WorksheetDfn worksheetData, int tableCounter)
{
Regex validSheetName = new Regex(@"^[^'*\[\]/\\:?][^*\[\]/\\:?]{0,30}$");
if (!validSheetName.IsMatch(worksheetData.Name))
{
throw new InvalidSheetNameException(worksheetData.Name);
}
// throw WorksheetAlreadyExistsException if a sheet with the same name (case-insensitive) already exists in the workbook
string UCName = worksheetData.Name.ToUpper();
XDocument wXDoc = sDoc.WorkbookPart.GetXDocument();
if (wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Attributes(SSNoNamespace.name)
.Select(a => ((string)a).ToUpper())
.Contains(UCName))
{
throw new WorksheetAlreadyExistsException(worksheetData.Name);
}
// create the worksheet with the supplied name
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
int?size = (int?)vector.Attribute(SSNoNamespace.size);
if (size == null)
{
size = 1;
}
else
{
size = size + 1;
}
vector.SetAttributeValue(SSNoNamespace.size, size);
vector.Add(
new XElement(VT.lpstr, worksheetData.Name));
XElement i4 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.Elements(VT.variant)
.Elements(VT.i4)
.FirstOrDefault();
if (i4 != null)
{
i4.Value = ((int)i4 + 1).ToString();
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
}
WorkbookPart workbook = sDoc.WorkbookPart;
string rId = "R" + Guid.NewGuid().ToString().Replace("-", "");
WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>(rId);
XDocument wbXDoc = workbook.GetXDocument();
XElement sheets = wbXDoc.Descendants(S.sheets).FirstOrDefault();
sheets.Add(
new XElement(S.sheet,
new XAttribute(SSNoNamespace.name, worksheetData.Name.ToString()),
new XAttribute(SSNoNamespace.sheetId, sheets.Elements(S.sheet).Count() + 1),
new XAttribute(R.id, rId)));
workbook.PutXDocument();
string ws = S.s.ToString();
string relns = R.r.ToString();
using (Stream partStream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write))
{
using (XmlWriter partXmlWriter = XmlWriter.Create(partStream))
{
partXmlWriter.WriteStartDocument();
partXmlWriter.WriteStartElement("worksheet", ws);
partXmlWriter.WriteStartElement("sheetData", ws);
int numColumnHeadingRows = 0;
int numColumns = 0;
int numColumnsInRows = 0;
int numRows;
if (worksheetData.ColumnHeadings != null)
{
RowDfn row = new RowDfn
{
Cells = worksheetData.ColumnHeadings
};
SerializeRows(sDoc, partXmlWriter, new[] { row }, 1, out numColumns, out numColumnHeadingRows);
}
SerializeRows(sDoc, partXmlWriter, worksheetData.Rows, numColumnHeadingRows + 1, out numColumnsInRows,
out numRows);
int totalRows = numColumnHeadingRows + numRows;
int totalColumns = Math.Max(numColumns, numColumnsInRows);
if (worksheetData.ColumnHeadings != null && worksheetData.TableName != null)
{
partXmlWriter.WriteEndElement();
string rId2 = "R" + Guid.NewGuid().ToString().Replace("-", "");
partXmlWriter.WriteStartElement("tableParts", ws);
partXmlWriter.WriteStartAttribute("count");
partXmlWriter.WriteValue(1);
partXmlWriter.WriteEndAttribute();
partXmlWriter.WriteStartElement("tablePart", ws);
partXmlWriter.WriteStartAttribute("id", relns);
partXmlWriter.WriteValue(rId2);
TableDefinitionPart tdp = worksheetPart.AddNewPart <TableDefinitionPart>(rId2);
XDocument tXDoc = tdp.GetXDocument();
XElement table = new XElement(S.table,
new XAttribute(SSNoNamespace.id, tableCounter),
new XAttribute(SSNoNamespace.name, worksheetData.TableName),
new XAttribute(SSNoNamespace.displayName, worksheetData.TableName),
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString()),
new XAttribute(SSNoNamespace.totalsRowShown, 0),
new XElement(S.autoFilter,
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString())),
new XElement(S.tableColumns,
new XAttribute(SSNoNamespace.count, totalColumns),
worksheetData.ColumnHeadings.Select((ch, i) =>
new XElement(S.tableColumn,
new XAttribute(SSNoNamespace.id, i + 1),
new XAttribute(SSNoNamespace.name, ch.Value)))),
new XElement(S.tableStyleInfo,
new XAttribute(SSNoNamespace.name, "TableStyleMedium2"),
new XAttribute(SSNoNamespace.showFirstColumn, 0),
new XAttribute(SSNoNamespace.showLastColumn, 0),
new XAttribute(SSNoNamespace.showRowStripes, 1),
new XAttribute(SSNoNamespace.showColumnStripes, 0)));
tXDoc.Add(table);
tdp.PutXDocument();
}
}
}
sDoc.WorkbookPart.WorkbookStylesPart.PutXDocument();
sDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
//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();
}
private static void AddTablePart(WorksheetPart sheetPart, TableColumn[] columns, int rowCount, WorkbookPart workBookPart)
{
if (sheetPart == null)
{
throw new ArgumentNullException(nameof(sheetPart));
}
if (columns == null)
{
throw new ArgumentNullException(nameof(columns));
}
if (rowCount < 0)
{
throw new ArgumentOutOfRangeException(nameof(rowCount));
}
if (workBookPart == null)
{
throw new ArgumentNullException(nameof(workBookPart));
}
var rangeReference = GetXlsTableRangeReference(columns.Length, rowCount);
var ignoredErrors = new IgnoredErrors(
new IgnoredError
{
NumberStoredAsText = true,
SequenceOfReferences = new ListValue <StringValue>
{
InnerText = rangeReference
}
}
);
// Ignored errors must be added before table parts.
sheetPart.Worksheet.Append(ignoredErrors);
var tableDefinitionPart = sheetPart.AddNewPart <TableDefinitionPart>();
var autoFilter = new AutoFilter {
Reference = rangeReference
};
var tableColumns = new TableColumns {
Count = (uint)columns.Length
};
var styleInfo = new TableStyleInfo
{
Name = "TableStyleMedium2",
ShowFirstColumn = false,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = false
};
var tableId = workBookPart.WorksheetParts
.Select(x => x.TableDefinitionParts.Where(y => y.Table != null)
.Select(y => (uint)y.Table.Id).DefaultIfEmpty(0U).Max()).DefaultIfEmpty(0U).Max() + 1;
var table =
new DocumentFormat.OpenXml.Spreadsheet.Table(autoFilter, tableColumns, styleInfo)
{
Id = tableId,
Name = "Table" + tableId,
DisplayName = "Table" + tableId,
Reference = rangeReference,
TotalsRowShown = false
};
for (var i = 0; i < columns.Length; i++)
{
table.TableColumns.Append(
new DocumentFormat.OpenXml.Spreadsheet.TableColumn
{
Id = (uint)(i + 1),
Name = columns[i].ColumnName
});
}
tableDefinitionPart.Table = table;
var tableParts = new TableParts(
new TablePart
{
Id = sheetPart.GetIdOfPart(tableDefinitionPart)
}
)
{
Count = 1U
};
sheetPart.Worksheet.Append(tableParts);
}
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);
}
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);
}
//------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------
//Tableau
public static void AddTableDefinitionPart(WorksheetPart part, List <string> titres, int nbLi, int nbCol)
{
TableDefinitionPart tableDefinitionPart1 = part.AddNewPart <TableDefinitionPart>("vId1");
GenerateTableDefinitionPart1Content(tableDefinitionPart1, titres, nbLi, nbCol);
}
protected void AddDrawingsPart(string id)
{
DrawingsPart = WorksheetPart.AddNewPart <DrawingsPart>(id);
}
public static void AddImage(WorksheetPart worksheetPart,
Stream imageStream, string imgDesc,
int colNumber, int rowNumber, int width, int height)
{
// Necesitamos la transmisión de imágenes más de una vez, así creamos una copia de memoria
MemoryStream imageMemStream = new MemoryStream();
imageStream.Position = 0;
imageStream.CopyTo(imageMemStream);
imageStream.Position = 0;
var drawingsPart = worksheetPart.DrawingsPart;
if (drawingsPart == null)
{
drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
}
if (!worksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any())
{
worksheetPart.Worksheet.Append(new Drawing {
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
}
if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
}
var worksheetDrawing = drawingsPart.WorksheetDrawing;
// Bitmap bm = new Bitmap(imageMemStream);
Bitmap bm = new Bitmap(ConvertImage(imageMemStream, ImageFormat.Jpeg));
bm.SetResolution(60, 60);
var imagePart = drawingsPart.AddImagePart(GetImagePartTypeByBitmap(bm));
//imagePart.FeedData(imageStream);
imagePart.FeedData(ConvertImage(imageStream, ImageFormat.Jpeg));
A.Extents extents = new A.Extents();
// var extentsCx = bm.Width * (long)(914400 / bm.HorizontalResolution);
//var extentsCy = bm.Height * (long)(914400 / bm.VerticalResolution);
//dividir las medidas hecnas en el paint por 1.35 esa era la medida para
//el programa
//var extentsCx = width * (long)(914400 / bm.HorizontalResolution);
//var extentsCy = height * (long)(914400 / bm.VerticalResolution);
var extentsCx = width * (long)(571500 / bm.HorizontalResolution);
var extentsCy = height * (long)(571500 / bm.VerticalResolution);
bm.Dispose();
var colOffset = 0;
var rowOffset = 0;
var nvps = worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>();
var nvpId = nvps.Count() > 0
? (UInt32Value)worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1
: 1U;
var oneCellAnchor = new Xdr.OneCellAnchor(
new Xdr.FromMarker
{
ColumnId = new Xdr.ColumnId((colNumber - 1).ToString()),
RowId = new Xdr.RowId((rowNumber - 1).ToString()),
ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()),
RowOffset = new Xdr.RowOffset(rowOffset.ToString())
},
new Xdr.Extent {
Cx = extentsCx, Cy = extentsCy
},
new Xdr.Picture(
new Xdr.NonVisualPictureProperties(
new Xdr.NonVisualDrawingProperties {
Id = nvpId, Name = "Picture " + nvpId, Description = imgDesc
},
new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks {
NoChangeAspect = true
})
),
new Xdr.BlipFill(
new A.Blip {
Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print
},
new A.Stretch(new A.FillRectangle())
),
new Xdr.ShapeProperties(
new A.Transform2D(
new A.Offset {
X = 0, Y = 0
},
new A.Extents {
Cx = extentsCx, Cy = extentsCy
}
),
new A.PresetGeometry {
Preset = A.ShapeTypeValues.Rectangle
}
)
),
new Xdr.ClientData()
);
worksheetDrawing.Append(oneCellAnchor);
}
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);
}
public override Xdr.WorksheetDrawing AddWorksheetDrawing(WorksheetPart worksheetPart)
{
var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
return(drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing());
}
