/// <summary>
/// Write xlsx spreadsheet file of a list of T objects
/// Maximum of 24 columns
/// </summary>
/// <typeparam name="T">Type of objects passed in</typeparam>
/// <param name="fileName">Full path filename for the new spreadsheet</param>
/// <param name="defs">A list of sheet definitions used to create the spreadsheet</param>
public static void Create <T>(
string fileName,
IEnumerable <SheetDefinition <T> > defs)
{
// open a template workbook
using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create workbook part
var workbookPart = myWorkbook.AddWorkbookPart();
// add stylesheet to workbook part
var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
// create workbook
var workbook = new Workbook();
// add work sheets
var sheets = new Sheets();
var list = defs.ToList();
for (var i = 0; i < list.Count(); i++)
{
sheets.AppendChild(CreateSheet(i + 1, list[i], workbookPart));
}
workbook.AppendChild(sheets);
// add workbook to workbook part
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1, DataTable dt, SpreadsheetDocument document)
{
Workbook workbook1 = new Workbook();
Sheets sheets = workbook1.AppendChild <Sheets>(new Sheets());
int i = 1;
//List<string> docss = new List<string>();
string wsname = dt.TableName;
//foreach (DataSet ds in dss)
//{
// lsdss = (ds.Tables[1]).Select().ToList<DataRow>();
//string doc_set = ds.Tables[1].TableName;
//wsname = ds.Tables[1].TableName;
//if (!docss.Contains(doc_set))
//{
WorksheetPart worksheetPart = workbookPart1.AddNewPart <WorksheetPart>();
OpenXmlElement[] oes = new OpenXmlElement[2];
oes[0] = new SheetData();
worksheetPart.Worksheet = new Worksheet(oes);
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = (UInt32)i, Name = wsname
};
sheets.Append(sheet);
worksheetPart.Worksheet.Save();
workbookPart1.Workbook = workbook1;
}
/// <summary>
/// Write xlsx spreadsheet file of a list of T objects
/// Maximum of 24 columns
/// </summary>
/// <typeparam name="T">Type of objects passed in</typeparam>
/// <param name="fileName">Full path filename for the new spreadsheet</param>
/// <param name="def">A sheet definition used to create the spreadsheet</param>
public static void Create <T>(
string fileName,
SheetDefinition <T> def)
{
// open a template workbook
using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create workbook part
var workbookPart = myWorkbook.AddWorkbookPart();
// add stylesheet to workbook part
var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
// create workbook
var workbook = new Workbook();
// add work sheet
var sheets = new Sheets();
sheets.AppendChild(CreateSheet(1, def, workbookPart));
workbook.AppendChild(sheets);
// add workbook to workbook part
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
public Sheet CreateSheet(string name)
{
var sheetData = new SheetData();
var worksheetPart = WorkbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = Workbook.AppendChild(new Sheets());
uint newSheetId;
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Any())
{
newSheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>()
.Select(_ => _.SheetId.Value)
.Max() + 1;
}
else
{
newSheetId = 1;
}
var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = newSheetId,
Name = name
};
sheets.Append(sheet);
return(new Sheet(this, sheet));
}
private void CreatePartsForExcel(SpreadsheetDocument package)
{
workbookPart1 = package.AddWorkbookPart();
var sheets1 = new Sheets();
var workbook1 = new Workbook();
workbook1.AppendChild(sheets1);
workbookPart1.Workbook = workbook1;
}
/// <summary>
/// Default document initialization.
/// </summary>
private DocumentContext InitDocument(SpreadsheetDocument document)
{
if (document.WorkbookPart == null)
{
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbookPart.Workbook = workbook;
BookViews bookViews = new BookViews();
WorkbookView workbookView = new WorkbookView()
{
XWindow = -120,
YWindow = -120,
WindowWidth = (UInt32Value)19440U,
WindowHeight = (UInt32Value)15000U,
ActiveTab = 0,
};
bookViews.Append(workbookView);
Sheets sheets = new Sheets();
workbook.AppendChild(bookViews);
workbook.AppendChild(sheets);
}
var documentContext = new DocumentContext(document, _documentMetadata);
// Init Stylesheet.
InitStylesheet(documentContext);
// Add empty string.
documentContext.GetOrAddSharedString(string.Empty);
// External customization
var customizeFunc = _documentMetadata?.GetValue(ExcelDocumentMetadata.ConfigureDocument);
customizeFunc?.Invoke(documentContext);
return(documentContext);
}
/// <summary>
/// Generates the workbook parts with an empty workbook
/// </summary>
private void GenerateEmptyChildElements()
{
//Add workbook part
WorkbookPart = Document.AddWorkbookPart();
Workbook = new Workbook();
WorkbookPart.Workbook = Workbook;
//Adds an empty sheets section
Sheets = new Sheets();
Workbook.AppendChild(Sheets);
//Adds an empty stylesheet
_workbookStylesPart = WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet = CreateDefaultStyleSheet();
_workbookStylesPart.Stylesheet = Stylesheet;
}
public ExcelTabularBook()
{
_outputStream = new MemoryStream();
_tempFilePath = "";
_package = SpreadsheetDocument.Create(_outputStream, SpreadsheetDocumentType.Workbook);
var coreFilePropertiesPart = _package.AddNewPart<CoreFilePropertiesPart>(CORE_FILE_PROPERTIES_PART_ID);
GenerateCoreFilePropertiesPart(coreFilePropertiesPart);
_workbookPart = _package.AddWorkbookPart();
WorkbookStylesPart workbookStylesPart = _workbookPart.AddNewPart<WorkbookStylesPart>(WORKBOOK_STYLES_PART_ID);
GenerateWorkbookStylesPart().Save(workbookStylesPart);
_workbook = AddWorkBook(_workbookPart);
_workbook.AppendChild(new Sheets());
}
private void ExportToExcel(object sender, EventArgs e)
{
JObject json = JObject.Parse(textBox1.Text);
JObject playlist = (JObject)json["playlist"];
// 创建文档
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument document = SpreadsheetDocument.Create("out.xlsx", SpreadsheetDocumentType.Workbook);
// 准备文件结构中的各XML
// Add a WorkbookPart to the document.
WorkbookPart workbookPart = document.AddWorkbookPart();
// Add a SharedStringTablePart to the WorkbookPart.
SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart <SharedStringTablePart>();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// 生成WorkbookPart的XML结构
Workbook workbook = workbookPart.Workbook = new Workbook();
// Add Sheets to the Workbook.
Sheets sheets = workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
sheets.Append(new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = (string)playlist["name"]
});
// 生成SharedStringTablePart的XML结构
SharedStringTable sharedStringTable = sharedStringTablePart.SharedStringTable = new SharedStringTable();
// 生成WorksheetPart的XML结构
Worksheet worksheet = worksheetPart.Worksheet = new Worksheet();
// Get the sheetData cell table.
SheetData sheetData = worksheet.AppendChild(new SheetData());
// 插入数据
AddDatas(sheetData, sharedStringTable, (JArray)playlist["tracks"]);
// 关闭文档
document.Close();
MessageBox.Show("导出成功！");
}
public string CreateDiscrepanciesReport(string fileName)
{
try
{
if (!DatabaseBuilder.sqliteConnection.State.ToString().Equals("Open"))
{
DatabaseBuilder.sqliteConnection.Open();
}
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
LogWriter.LogStatusUpdate("Creating STIG Discrepancies workbook framework.");
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
workbookStylesPart.Stylesheet = _openXmlStylesheetCreator.CreateStylesheet();
Workbook workbook = workbookPart.Workbook = new Workbook();
Sheets sheets = workbook.AppendChild(new Sheets());
StartReport(workbookPart, sheets);
LogWriter.LogStatusUpdate("Writing STIG Discrepancies findings.");
WriteFindingsToReport();
LogWriter.LogStatusUpdate("Finalizing STIG Discrepancies workbook.");
EndReport();
_openXmlCellDataHandler.CreateSharedStringPart(workbookPart, sharedStringMaxIndex,
sharedStringDictionary);
}
return("Excel report creation successful");
}
catch (Exception exception)
{
string error = $"Unable to create '{fileName}' (Excel Report).";
LogWriter.LogErrorWithDebug(error, exception);
return("Excel report creation failed - see log for details");
}
finally
{
DatabaseBuilder.sqliteConnection.Close();
}
}
/// <summary>
/// Write xlsx spreadsheet file of a list of T objects
/// Maximum of 24 columns
/// </summary>
/// <typeparam name="T">Type of objects passed in</typeparam>
/// <param name="fileName">Full path filename for the new spreadsheet</param>
/// <param name="def">A sheet definition used to create the spreadsheet</param>
public static void Create <T>(string fileName, SheetDefinition <T>[] defs)
{
// open a template workbook
using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create workbook part
var workbookPart = myWorkbook.AddWorkbookPart();
// add stylesheet to workbook part
var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
//styles.Append(new CellFormats());
//styles.CellFormats.Append(new CellFormat());
//styles.CellFormats.Append(new CellFormat() { ApplyNumberFormat = true, NumberFormatId = 22 });
styles.Save(stylesPart);
//var cc = myWorkbook.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ToList();
// create workbook
var workbook = new Workbook();
int sheetIndex = 1;
var sheets = new Sheets();
foreach (var def in defs)
{
// add work sheet
sheets.AppendChild(CreateSheet(sheetIndex, def, workbookPart));
sheetIndex++;
}
workbook.AppendChild(sheets);
CreateShareStringPart(myWorkbook);
// add workbook to workbook part
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
private OpenXmlWriter CreateWriter()
{
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var sheets = new Sheets();
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = sheetName
};
sheets.AppendChild(sheet);
var workBook = new Workbook();
workbookPart.Workbook = workBook;
workBook.AppendChild(sheets);
return(OpenXmlWriter.Create(worksheetPart, Encoding.UTF8));
}
private static SpreadsheetDocument CreateFile(string newFilePath, string firstSheetName, out DefaultCellStyle defaultCellStyle)
{
//建立xlsx文件
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(newFilePath, SpreadsheetDocumentType.Workbook, true);
//建立xl,worksheets目录(会默认生成0字节的workbook和worksheet,以及2个res文档)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
//建立workbook文档,设定模式的worksheet (sheet的3个属性必须填写,特别的是name是这里设定,有点不符合常见的抽象思维)
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = firstSheetName
};
sheets.Append(sheet);
workbook.AppendChild <Sheets>(sheets);
workbookpart.Workbook = workbook;
//建立默认的worksheet文档(可以先workbook,后worksheet)
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;//给默认的worksheet赋值,否则0字节.
//建立样式文件
createDeafultStyle(workbookpart, out defaultCellStyle);
spreadsheetDocument.Save();
return(spreadsheetDocument);
}
//void InitBasicStylePart(WorkbookPart workbookPart) {
// WorkbookStylesPart stylesPart;
// if (!workbookPart.GetPartsOfType<WorkbookStylesPart>().Any()) {
// stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
// stylesPart.Stylesheet = new Stylesheet();
// var stylesheet = stylesPart.Stylesheet;
// stylesheet.Fonts = new DocumentFormat.OpenXml.Spreadsheet.Fonts(
// new Font(
// new FontSize() { Val = 11 },
// new Color() { Theme = 1 }
// )
// );
// stylesheet.CellStyleFormats = new CellStyleFormats();
// stylesheet.CellStyleFormats.Append(new CellFormat());
// stylesheet.CellFormats = new CellFormats();
// }
// var cellFormat = stylesheet.CellFormats.Elements<CellFormat>().FirstOrDefault(cf => cf.FormatId == cellStyle.FormatId)
// ?? stylesheet.CellFormats.AppendChild(new CellFormat() {
// FormatId = cellStyle.FormatId,
// });
// if (stylesheet.CellStyles == null) {
// stylesheet.CellStyles = new CellStyles();
// }
// var cellStyles = stylesheet.CellStyles;
// var cellStyle = cellStyles.Elements<CellStyle>().FirstOrDefault(cs => cs.Name == "Hyperlink")
// ?? cellStyles.AppendChild(new CellStyle() {
// Name = "Hyperlink",
// BuiltinId = 8,
// FormatId = 0 //index 0 from cellstyleformats
// });
//}
void Save(SpreadsheetDocument spreadsheetDocument)
{
//Create workbook parts
var workbookPart = spreadsheetDocument.AddWorkbookPart();
//Sets workbook
var workbook = new Workbook();
workbookPart.Workbook = workbook;
//Shared string
var sharedStringPart = workbookPart.AddNewPart <SharedStringTablePart>();
sharedStringPart.SharedStringTable = new SharedStringTable();
//Set theme
using (var stream = GetType().GetTypeInfo().Assembly.GetManifestResourceStream("Genexcel.Resources.Office.theme1.xml")) {
using (var reader = new StreamReader(stream)) {
var xml = reader.ReadToEnd();
workbookPart.AddNewPart <ThemePart>();
workbookPart.ThemePart.Theme = new Theme(xml);
}
}
//Set styles
using (var stream = GetType().GetTypeInfo().Assembly.GetManifestResourceStream("Genexcel.Resources.Office.styles.xml")) {
using (var reader = new StreamReader(stream)) {
var xml = reader.ReadToEnd();
workbookPart.AddNewPart <WorkbookStylesPart>();
workbookPart.WorkbookStylesPart.Stylesheet = new Stylesheet(xml);
}
}
//Adiciona lista sheets
var sheets = workbook.AppendChild(new Sheets());
//Adiciona as planilhas ao workbook
uint sheetId = 1;
int sharedStringsIndex = 0;
foreach (var s in _sheets)
{
//Criar worksheet part no workbookpart
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var worksheet = new Worksheet();
//Columns
if (s.HasCustomColumn)
{
var columns = new Columns();
worksheet.Append(columns);
Column currentColElement = null;
Models.Column currentColModel = new Models.Column(); //fake current
for (uint i = 0; i < s.Columns.Length; i++)
{
var col = s.Columns[i];
if (col == currentColModel)
{
currentColElement.Max = i + 1;
}
else
{
currentColElement = new Column()
{
//Style = 1,
Min = i + 1,
Max = i + 1,
//CustomWidth = false,
Width = Models.Column.DEFAULT_WIDTH
};
if (col != null)
{
currentColElement.CustomWidth = true;
currentColElement.Width = col.Width;
}
columns.Append(currentColElement);
}
currentColModel = col;
}
//Column currentColumnElement = null;
//Models.Column currentColumnModel = null;
//foreach (var col in s.Columns) {
// Column colElement;
// if(col == currentColumnModel) { colElement = currentColumnElement }
// if(col == null) {
// }
// columns.Append(new Column() {
// Min = (uint)col.Min,
// Max = (uint)col.Max,
// Width = col.Width,
// Style = 1,
// CustomWidth = true
// });
//}
}
var sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
var name = s.Name ?? "Plan";
name = name.Length > _sheetNameLengthLimit?
name.Substring(0, _sheetNameLengthLimit) :
name;
// Append a new worksheet and associate it with the workbook.
var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId++,
Name = name
};
sheets.Append(sheet);
Dictionary <uint, Row> dctRows = new Dictionary <uint, Row>();
Dictionary <string, Cell> dctCells = new Dictionary <string, Cell>();
foreach (var c in s.GetCells())
{
// Insert cell A1 into the new worksheet.
Cell cell; // = InsertCellInWorksheet(ColTranslate(c.Col), (uint)c.Row, worksheetPart);
//Worksheet worksheet = worksheetPart.Worksheet;
//SheetData sheetData = worksheet.GetFirstChild<SheetData>();
var columnName = ColTranslate(c.Col);
var rowIndex = (uint)c.Row;
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (dctRows.ContainsKey(rowIndex))
{
row = dctRows[rowIndex];
}
//else if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) {
// row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
//}
else
{
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
dctRows[rowIndex] = row;
}
// If there is not a cell with the specified column name, insert one.
//if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) {
//return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
//}
if (dctCells.ContainsKey(cellReference))
{
cell = dctCells[cellReference];
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
//Cell refCell = null;
//foreach (var cell in row.Elements<Cell>()) {
// if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) {
// refCell = cell;
// break;
// }
//}
var newCell = new Cell()
{
CellReference = cellReference
};
//row.InsertBefore(newCell, refCell);
row.Append(newCell);
dctCells[cellReference] = newCell;
//worksheet.Save();
cell = newCell;
}
var value = c.Value;
if (value is string)
{
int index;
var str = value.ToString();
//str = "TESTE VELOCIDADE";
if (_dctSharedStrings.ContainsKey(str))
{
index = _dctSharedStrings[str];
}
else
{
index = sharedStringsIndex++;
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(str)));
//shareStringPart.SharedStringTable.Save();
_dctSharedStrings[str] = index;
}
//int index = InsertSharedStringItem(value.ToString(), sharedStringPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
else if (value is int ||
value is decimal ||
value is long ||
value is short ||
value is double ||
value is float ||
value is byte)
{
var toString = value.GetType().GetTypeInfo()
.GetDeclaredMethods("ToString")
.First(m => m.GetParameters().Any(p => p.ParameterType == typeof(IFormatProvider))); //.GetMethod("ToString", new Type[] { typeof(CultureInfo) }).GetMethodInfo();
var formattedValue = toString.Invoke(value, new object[] { new CultureInfo("en-US") }).ToString();
cell.CellValue = new CellValue(formattedValue);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
if (!string.IsNullOrWhiteSpace(c.Hyperlink))
{
var rId = $"r{Guid.NewGuid().ToString()}";
//if (workbookPart.GetPartsOfType<Relat>().Any()) {
// shareStringPart = worksheet.getp.GetPartsOfType<SharedStringTablePart>().First();
//} else {
// shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
//}
var rel = worksheetPart.AddHyperlinkRelationship(new Uri(c.Hyperlink), true, rId);
var hyperlinks = worksheet.Elements <Hyperlinks>().FirstOrDefault();
if (hyperlinks == null)
{
hyperlinks = worksheet.AppendChild(new Hyperlinks());
}
hyperlinks.Append(new DocumentFormat.OpenXml.Spreadsheet.Hyperlink()
{
Reference = cell.CellReference,
Id = rId
});
cell.StyleIndex = 2; //Hyperlink, should be an enum
}
}
//Charts
foreach (var ch in s.Charts)
{
//https://msdn.microsoft.com/en-us/library/office/cc820055.aspx#How the Sample Code Works
// Add a new drawing to the worksheet.
var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
var chartPart = drawingsPart.AddNewPart <ChartPart>();
var chartSpace = new ChartSpace();
chartPart.ChartSpace = chartSpace;
chartSpace.Append(new Date1904()
{
Val = false
});
chartSpace.Append(new EditingLanguage()
{
Val = "en-US"
});
chartSpace.Append(new RoundedCorners()
{
Val = false
});
var chart = chartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
//chartSpace.Append(new ChartShapeProperties(
// new SolidFill(
// new SchemeColor() { Val = SchemeColorValues.Background1 }
// ),
// new DocumentFormat.OpenXml.Drawing.Outline(
// new SolidFill(
// new SchemeColor(
// new LuminanceModulation() { Val = 15000 },
// new LuminanceOffset() { Val = 85000 }
// ) { Val = SchemeColorValues.Text1 }
// )
// ) {
// Width = 9525,
// CapType = LineCapValues.Flat,
// CompoundLineType = CompoundLineValues.Single,
// Alignment = PenAlignmentValues.Center
// }
// ));
//Dont know
chart.AppendChild(new Title(
new Overlay()
{
Val = false
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = 0,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 1400,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
)
)
)
));
//Allow showing title on top
chart.AppendChild(new AutoTitleDeleted()
{
Val = false
});
//Create plot area
var plotArea = chart.AppendChild(new PlotArea());
var layout = plotArea.AppendChild(new Layout());
if (ch is Models.AreaChart || ch is Models.BarChart)
{
#region init chart
var chObject = ch as Models.Chart;
OpenXmlCompositeElement chartElement;
if (ch is Models.AreaChart)
{
chartElement = plotArea.AppendChild(
//Dont know what extensions are for
new AreaChart(new Grouping()
{
Val = GroupingValues.Standard
})
);
}
else
{
chartElement = plotArea.AppendChild(
//Dont know what extensions are for
new BarChart(
new BarDirection()
{
Val = BarDirectionValues.Column
},
new BarGrouping()
{
Val = BarGroupingValues.Clustered
})
);
}
chartElement.AppendChild(new VaryColors()
{
Val = false
});
#endregion
#region data
foreach (var dts in chObject.Data.Datasets)
{
var index = (uint)chObject.Data.Datasets.IndexOf(dts);
OpenXmlCompositeElement chartSeries;
if (ch is Models.AreaChart)
{
chartSeries = chartElement.AppendChild(new AreaChartSeries());
}
else
{
chartSeries = chartElement.AppendChild(new BarChartSeries());
}
chartSeries.Append(
new Index()
{
Val = index
},
new Order()
{
Val = index
},
new SeriesText()
{
NumericValue = new NumericValue(dts.Title)
},
new ChartShapeProperties(
new SolidFill(new SchemeColor()
{
Val = SchemeColorValues.Accent1
}),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
)
);
if (ch is Models.BarChart)
{
chartSeries.Append(new InvertIfNegative()
{
Val = false
});
}
//Eixo x (labels)
var categoryAxisData = chartSeries.AppendChild(new CategoryAxisData());
var strLit = categoryAxisData.AppendChild(new StringLiteral());
strLit.Append(new PointCount()
{
Val = (uint)chObject.Data.Labels.Count
});
foreach (var lbl in chObject.Data.Labels)
{
strLit.AppendChild(new StringPoint()
{
Index = (uint)chObject.Data.Labels.IndexOf(lbl)
})
.Append(new NumericValue(lbl));
}
var values = chartSeries.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());
var numLit = values.AppendChild(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount()
{
Val = (uint)chObject.Data.Labels.Count
});
foreach (var lbl in chObject.Data.Labels)
{
var lblIndex = chObject.Data.Labels.IndexOf(lbl);
var val = dts.Data.Count > lblIndex ? dts.Data[lblIndex] : 0;
numLit.AppendChild(new NumericPoint()
{
Index = (uint)chObject.Data.Labels.IndexOf(lbl)
})
.Append(new NumericValue(val.ToString()));
}
// numLit.AppendChild(new NumericPoint() { Index = new UInt32Value(0u) })
// .Append
//(new NumericValue("28"));
}
#endregion
#region options?
//Not required for a valid xlsx
chartElement
.AppendChild(
new DataLabels(
new ShowLegendKey()
{
Val = false
},
new ShowValue()
{
Val = false
},
new ShowCategoryName()
{
Val = false
},
new ShowSeriesName()
{
Val = false
},
new ShowPercent()
{
Val = false
},
new ShowBubbleSize()
{
Val = false
}
)
);
if (ch is Models.BarChart)
{
chartElement.Append(new GapWidth()
{
Val = 219
});
chartElement.Append(new Overlap()
{
Val = -27
});
}
#endregion
#region Axis
chartElement.Append(new AxisId()
{
Val = 48650112u
});
chartElement.Append(new AxisId()
{
Val = 48672768u
});
// Add the Category Axis.
var catAx = plotArea
.AppendChild(
new CategoryAxis(
new AxisId()
{
Val = 48650112u
},
new Scaling(
new Orientation()
{
Val = DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax
}
),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = AxisPositionValues.Bottom
},
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new MajorTickMark()
{
Val = ch is Models.AreaChart ? TickMarkValues.Outside : TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.None
},
new TickLabelPosition()
{
Val = TickLabelPositionValues.NextTo
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 15000
},
new LuminanceOffset()
{
Val = 85000
}
)
{
Val = SchemeColorValues.Text1
}
)
)
{
Width = 9525,
CapType = LineCapValues.Flat,
CompoundLineType = CompoundLineValues.Single,
Alignment = PenAlignmentValues.Center
}
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = -60000000,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 900,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
),
new EndParagraphRunProperties()
{
Language = "en-US"
}
)
),
new CrossingAxis()
{
Val = 48672768U
},
new Crosses()
{
Val = CrossesValues.AutoZero
},
new AutoLabeled()
{
Val = true
},
new LabelAlignment()
{
Val = LabelAlignmentValues.Center
},
new LabelOffset()
{
Val = 100
},
new NoMultiLevelLabels()
{
Val = false
}
)
);
// Add the Value Axis.
var valAx = plotArea
.AppendChild(
new ValueAxis(
new AxisId()
{
Val = 48672768u
},
new Scaling(new Orientation()
{
Val = DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = AxisPositionValues.Left
},
new MajorGridlines(
new ChartShapeProperties(
new DocumentFormat.OpenXml.Drawing.Outline(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 15000
},
new LuminanceOffset()
{
Val = 85000
}
)
{
Val = SchemeColorValues.Text1
}
),
new Round()
)
{
Width = 9525,
CapType = LineCapValues.Flat,
CompoundLineType = CompoundLineValues.Single,
Alignment = PenAlignmentValues.Center
},
new EffectList()
)
),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new MajorTickMark()
{
Val = TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.None
},
new TickLabelPosition()
{
Val = TickLabelPositionValues.NextTo
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = -60000000,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 900,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
),
new EndParagraphRunProperties()
{
Language = "en-US"
}
)
),
new CrossingAxis()
{
Val = 48650112U
},
new Crosses()
{
Val = CrossesValues.AutoZero
},
new CrossBetween()
{
Val = ch is Models.AreaChart ? CrossBetweenValues.MidpointCategory : CrossBetweenValues.Between
})
);
// Add the chart Legend.
//Legend legend = chart.AppendChild(new Legend(new LegendPosition() { Val = new EnumValue<LegendPositionValues>(LegendPositionValues.Right) },
// new Layout()));
chart.Append(new PlotVisibleOnly()
{
Val = true
});
chart.Append(new DisplayBlanksAs()
{
Val = ch is Models.AreaChart ? DisplayBlanksAsValues.Zero : DisplayBlanksAsValues.Gap
});
chart.Append(new ShowDataLabelsOverMaximum()
{
Val = false
});
#endregion
// Save the chart part.
chartPart.ChartSpace.Save();
}
#region position?
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("0"),
new ColumnOffset("0"),
new RowId("0"),
new RowOffset("0")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("8"),
new ColumnOffset("0"),
new RowId("15"),
new RowOffset("0")));
// Append a GraphicFrame to the TwoCellAnchor object.
var graphicFrame = twoCellAnchor.AppendChild(new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u),
Name = "Chart 1"
}, new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
#endregion
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
}
var validator = new OpenXmlValidator();
var errors = validator.Validate(spreadsheetDocument);
if (errors.Any())
{
var sbError = new StringBuilder();
sbError.Append("ERROR: ");
foreach (var e in errors)
{
sbError.Append($"***{e.Node.ToString()}:{e.Description}***");
}
throw new Exception(sbError.ToString());
}
workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
