private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateDate(int nColumnIndex, int nRowIndex, DateTime dtValue, UInt32Value styleId)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString();
cell.DataType = CellValues.Date;
cell.CellValue = new CellValue(dtValue.ToUniversalTime().ToString("s"));
cell.StyleIndex = styleId;
return cell;
}
private Row GenerateRowForProperty(UInt32Value rowIndex, int linesToAdd, ProjectFile projectFile, String property)
{
Row row = new Row() { RowIndex = (UInt32Value)(rowIndex + linesToAdd), Spans = new ListValue<StringValue>() { InnerText = "3:9" }, DyDescent = 0.25D };
ProjectProperty projectProperty = projectFile.ProjectProperties.FirstOrDefault(prop => prop.Type == property);
if (projectProperty != null)
{
int sharedStringIndexForType = 0;
for (int i = 0; i < SharedStrings.Count; i++)
{
if (SharedStrings[i].Text.Text == projectProperty.Type)
{
sharedStringIndexForType = i;
break;
}
}
List<Cell> cells = new List<Cell>()
{
new Cell(){CellReference = GetCell(String.Format("C{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 7U,DataType = CellValues.SharedString, CellValue = new CellValue(sharedStringIndexForType.ToString(CultureInfo.InvariantCulture))},
new Cell(){CellReference = GetCell(String.Format("D{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 8U,DataType = CellValues.Number, CellValue = new CellValue(projectProperty.Rate.ToString(CultureInfo.InvariantCulture))},
new Cell(){CellReference = GetCell(String.Format("F{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 9U,DataType = CellValues.Number, CellValue = new CellValue(projectProperty.Words.ToString(CultureInfo.InvariantCulture))},
new Cell(){CellReference = GetCell(String.Format("G{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 9U,DataType = CellValues.Number, CellValue = new CellValue(projectProperty.Characters.ToString(CultureInfo.InvariantCulture))},
new Cell(){CellReference = GetCell(String.Format("H{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 9U,DataType = CellValues.Number, CellValue = new CellValue(projectProperty.ValueByWords.ToString(CultureInfo.InvariantCulture))},
new Cell(){CellReference = GetCell(String.Format("I{0}", rowIndex.Value), linesToAdd),StyleIndex = (UInt32Value) 9U,DataType = CellValues.SharedString, CellValue = new CellValue("15")}
};
row.Append(cells.ConvertAll(c => (OpenXmlElement) c));
}
return row;
}
protected override IEnumerable<Cell> GetCellsInRow(UInt32Value SheetId, UInt32Value RowId)
{
foreach (var item in _xlBook.Sheets.Single(x => x.Id == SheetId.Value).Cells.Where(x => x.Row == RowId.Value))
{
yield return CovertCell(new Cell() { CellReference = item.Reference }, item.Value, item.Type);
}
}
private static UInt32Value CreateCellFormat(
Stylesheet styleSheet,
UInt32Value fontIndex,
UInt32Value fillIndex,
UInt32Value numberFormatId)
{
CellFormat cellFormat = new CellFormat();
if (fontIndex != null)
cellFormat.FontId = fontIndex;
if (fillIndex != null)
cellFormat.FillId = fillIndex;
if (numberFormatId != null)
{
cellFormat.NumberFormatId = numberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
}
styleSheet.CellFormats.Append(cellFormat);
UInt32Value result = styleSheet.CellFormats.Count;
styleSheet.CellFormats.Count++;
return result;
}
public CellData(int nColIndex,
string value,
bool bIsString,
uint style_index)
{
this.Col = nColIndex;
this.Value = DomUtil.ReplaceControlCharsButCrLf(value, '*');
this.StyleIndex = new UInt32Value(style_index);
this.IsString = bIsString;
}
public CellData(int nColIndex,
string value,
bool bIsString,
uint style_index)
{
this.Col = nColIndex;
this.Value = value;
this.StyleIndex = new UInt32Value(style_index);
this.IsString = bIsString;
}
public void UInt32SerDe()
{
var jsonSerializer = new JsonSerializer <UInt32Value>(schemaRegistryClient);
var jsonDeserializer = new JsonDeserializer <UInt32Value>();
var v = new UInt32Value {
Value = 1234
};
var bytes = jsonSerializer.SerializeAsync(v, new SerializationContext(MessageComponentType.Value, testTopic)).Result;
Assert.Equal(v.Value, jsonDeserializer.DeserializeAsync(bytes, false, new SerializationContext(MessageComponentType.Value, testTopic)).Result.Value);
}
private static bool Render(TableElement element, ICollection <Style> styles, SheetData shData, WorkbookPart workbookpart)
{
uint styleId;
if (element.Table.Rows.Count == 0)
{
//throw new Exception("The table is empty");
return(true);
}
if (element.Headers.Count != 0)
{
styleId = GetStyleId(element.HeaderStyle, styles);
var row = shData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
foreach (var head in element.Headers)
{
var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(head),
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
StyleIndex = UInt32Value.FromUInt32(styleId + 1)
};
row.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Cell>(cell);
}
workbookpart.Workbook.Save();
}
styleId = GetStyleId(element.TableStyle, styles);
for (int i = 0; i < element.Table.Rows.Count; i++)
{
var row = shData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
for (int j = 0; j < element.Table.Columns.Count; j++)
{
var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(element.Table.Rows[i][j].ToString()),
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
StyleIndex = UInt32Value.FromUInt32(styleId + 1)
};
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
return(false);
}
private void PageSetting(PageSizeValues pageSizeValue, PageOrientationValues pageOrientationValue)
{
UInt32Value width = 11906U;
UInt32Value height = 16838U;
UInt32Value left = 1080U;
UInt32Value right = 1080U;
int top = 1080;
int bottom = 1080;
switch (pageSizeValue)
{
case PageSizeValues.A4:
width = 11906U;
height = 16838U;
break;
case PageSizeValues.A3:
width = 16783U;
height = 23757U;
break;
}
switch (pageOrientationValue)
{
case PageOrientationValues.Landscape:
break;
case PageOrientationValues.Portrait:
UInt32Value sweep = width;
width = height;
height = sweep;
int top_sweep = top;
top = (int)left.Value;
left = (uint)top_sweep;
break;
}
pageSize.Width = width;
pageSize.Height = height;
pageSize.Orient = pageOrientationValue;
pageMargin.Top = top;
pageMargin.Bottom = bottom;
pageMargin.Left = left;
pageMargin.Right = right;
pageMargin.Header = (UInt32Value)720U;
pageMargin.Footer = (UInt32Value)720U;
pageMargin.Gutter = (UInt32Value)0U;
}
private static UInt32Value createEmptyBorder(Stylesheet styleSheet)
{
styleSheet.Borders.Count = styleSheet.Borders.Count == null ? 0 : styleSheet.Borders.Count;
Border tempBorder = new Border(new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder());
styleSheet.Borders.Append(tempBorder);
UInt32Value res = styleSheet.Borders.Count;
styleSheet.Borders.Count++;
return(res);
}
/// <summary>
/// http://stackoverflow.com/questions/8405025/set-print-area-openxml-with-excel
/// https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname(v=office.14).aspx
/// </summary>
/// <param name="spreadsheetDoc"></param>
/// <param name="sheetName"></param>
/// <param name="columnStart">Ex: "$A"</param>
/// <param name="columnEnd">EX: "$B"</param>
/// null if just not set print areas column or row
public static void SetPrintTitles(WorkbookPart workbookPart, string sheetName, string columnStart, string columnEnd, string rowStart, string rowEnd)
{
String definedName = sheetName;
var definedNames = workbookPart.Workbook.Descendants <DefinedNames>().FirstOrDefault();
DefinedName name = null;
UInt32Value locSheetId;
if (definedNames == null)
{
definedNames = new DefinedNames();
workbookPart.Workbook.Append(definedNames);
workbookPart.Workbook.Save();
locSheetId = UInt32Value.FromUInt32(0);
}
else
{
int defineNameCount = definedNames.Descendants <DefinedName>().Count();
locSheetId = UInt32Value.FromUInt32((UInt32)defineNameCount);
}
//_xlnm.Print_Area la tham so de set up cho khoang khong in
if (rowStart == null && rowEnd == null && columnEnd != null && columnStart != null)
{
name = new DefinedName()
{
Name = "_xlnm.Print_Titles",
LocalSheetId = locSheetId
,
Text = String.Format("{0}!${1}:${2}", sheetName, columnStart.Replace("$", ""), columnEnd.Replace("$", ""))
};
}
else if (rowStart != null && rowEnd != null && columnEnd == null && columnStart == null)
{
name = new DefinedName()
{
Name = "_xlnm.Print_Titles",
LocalSheetId = locSheetId
,
Text = String.Format("{0}!${1}:${2}", sheetName, rowStart.Replace("$", ""), rowEnd.Replace("$", ""))
};
}
else if (rowStart != null && rowEnd != null && columnEnd != null && columnStart != null)
{
name = new DefinedName()
{
Name = "_xlnm.Print_Titles", LocalSheetId = locSheetId
,
Text = String.Format("{0}!${1}:${2},{0}!${3}:${4}", sheetName, columnStart.Replace("$", ""), columnEnd.Replace("$", ""), rowStart.Replace("$", ""), rowEnd.Replace("$", ""))
};
}
definedNames.Append(name);
workbookPart.Workbook.Save();
}
private static Fills CreateFills()
{
// fill 0
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
// fill 1 (in-built fill)
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
// fill 2
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
var fillColor = Color.LightSkyBlue;
patternFill.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValueFromColor(fillColor)
};
patternFill.BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValueFromColor(fillColor)
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
// fill 3
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
fillColor = Color.Orange;
patternFill.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValueFromColor(fillColor)
};
patternFill.BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValueFromColor(fillColor)
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return(fills);
}
//.....................................................................
/// <summary>
///
/// </summary>
/// <param name="styleSheet"></param>
/// <param name="fontName"></param>
/// <param name="fontSize"></param>
/// <param name="isBold"></param>
/// <param name="foreColor"></param>
/// <returns></returns>
private UInt32Value CreateFont(Stylesheet styleSheet, string fontName, double?fontSize, bool isBold, System.Drawing.Color foreColor)
{
Font font = new Font( );
if (!string.IsNullOrEmpty(fontName))
{
FontName name = new FontName( )
{
Val = fontName
};
font.Append(name);
}
if (fontSize.HasValue)
{
FontSize size = new FontSize( )
{
Val = fontSize.Value
};
font.Append(size);
}
if (isBold == true)
{
Bold bold = new Bold( );
font.Append(bold);
}
Color color = new Color( )
{
Rgb = new HexBinaryValue( )
{
Value = System.Drawing.ColorTranslator.ToHtml(
System.Drawing.Color.FromArgb(
foreColor.A,
foreColor.R,
foreColor.G,
foreColor.B)).Replace("#", "")
}
};
font.Append(color);
styleSheet.Fonts.Append(font);
UInt32Value result = styleSheet.Fonts.Count;
styleSheet.Fonts.Count++;
return(result);
}
/// <summary>
/// Returns the column from a cell reference
///</summary>
public static UInt32Value RowFromReference(string cellReference)
{
string numberString = string.Join(null, System.Text.RegularExpressions.Regex.Split(cellReference, "[^\\d]"));
uint result;
var ret = new UInt32Value();
if (uint.TryParse(numberString, out result))
{
ret.Value = result;
}
return(ret);
}
public static void AppendDateCell(this OpenXmlWriter writer, string cellReference, DateTime dateTimeValue)
{
string cellStringValue = dateTimeValue.ToOADate().ToString(CultureInfo.InvariantCulture);
bool bHasBlankTime = (dateTimeValue.Date == dateTimeValue);
writer.WriteElement(new Cell
{
CellValue = new CellValue(cellStringValue),
CellReference = cellReference,
StyleIndex = UInt32Value.FromUInt32(bHasBlankTime ? (uint)2 : (uint)1),
DataType = CellValues.Number
});
}
private static CellStyles CreateCellStyles()
{
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
return(css);
}
/// <summary>
/// Create cell format and add the cellformats collection
/// </summary>
private static CellFormat CreateCellFormat(CellFormats cfs, UInt32Value NumberFormatId)
{
CellFormat cf = new CellFormat();
cf.NumberFormatId = NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
return(cf);
}
private void SetNumberingFormat(uint numberingFormatId, string formatCode = null)
{
NumberingFormat numberingFormat = new NumberingFormat();
numberingFormat.NumberFormatId = UInt32Value.FromUInt32(numberingFormatId);
if (formatCode != null)
{
numberingFormat.FormatCode = StringValue.FromString(formatCode);
}
ActualNumberingFormats.Append(numberingFormat);
SetNumberingFormatsCount();
}
//创建一个单元格样式
//todo 如果参数下次再多，就需要考虑把同类的细节参数放到一个新建立参数类中,以提升代码阅读性。
private static UInt32Value createCellFormat(Stylesheet styleSheet, UInt32Value borderid, UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value numberFormatId, HorizontalAlignmentValues horizontal = HorizontalAlignmentValues.Left)
{
if (styleSheet.CellFormats.Count == null)
{
styleSheet.CellFormats.Count = (UInt32Value)0;
}
CellFormat cellFormat = null;
if (horizontal == HorizontalAlignmentValues.Left)
{
cellFormat = new CellFormat();
}
else
{
Alignment alignment = new Alignment();
alignment.Horizontal = horizontal;
alignment.Vertical = VerticalAlignmentValues.Center;
cellFormat = new CellFormat(alignment);
}
cellFormat.BorderId = 0;
if (fontIndex != null)
{
cellFormat.ApplyFont = true;
cellFormat.FontId = fontIndex;
}
if (borderid != null)
{
cellFormat.ApplyBorder = true;
cellFormat.BorderId = borderid;
}
if (fillIndex != null)
{
cellFormat.FillId = fillIndex;
cellFormat.ApplyFill = true;
}
if (numberFormatId != null)
{
cellFormat.NumberFormatId = numberFormatId;
cellFormat.ApplyNumberFormat = true;
}
styleSheet.CellFormats.Append(cellFormat);
UInt32Value result = styleSheet.CellFormats.Count;
styleSheet.CellFormats.Count++;
return(result);
}
private void GetPageSetting(ref PageSize pageSize, ref PageMargin pageMargin, string paperSize = "A4", bool isVertical = true)
{
//string str_paperSize = "Letter";//A4,B4
UInt32Value width = 15840U;
UInt32Value height = 12240U;
int top = 1440;
UInt32Value left = 1440U;
switch (paperSize)
{
case "A4": // 210mm×297mm
width = 16840U; //80
height = 11905U; //40
break;
case "A3": //297mm×420mm
width = 23760U;
height = 16800U;
break;
case "B4": //257mm×364mm
width = 20636U;
height = 14570U;
break;
case "16k":
default:
break;
}
if (!isVertical)
{
UInt32Value sweep = width;
width = height;
height = sweep;
int top_sweep = top;
top = (int)left.Value;
left = (uint)top_sweep;
}
pageSize.Width = width;
pageSize.Height = height;
pageSize.Orient = new EnumValue <PageOrientationValues>(isVertical ? PageOrientationValues.Landscape : PageOrientationValues.Portrait);
pageMargin.Top = top;
pageMargin.Bottom = top;
pageMargin.Left = left;
pageMargin.Right = left;
pageMargin.Header = (UInt32Value)720U;
pageMargin.Footer = (UInt32Value)720U;
pageMargin.Gutter = (UInt32Value)0U;
}
//添加一个工作表
public void AddWorksheetPart(string sheetName)
{
Sheet sheet = workbookPart.Workbook.Sheets.Elements <Sheet>().
FirstOrDefault(s => s != null && s.Name != null && s.Name == sheetName);
WorksheetPart worksheetPart = null;
if (sheet != null)
{
worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
}
else
{
worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
}
//Worksheet
if (worksheetPart.Worksheet == null)
{
worksheetPart.Worksheet = new Worksheet();
}
//SheetData
if (worksheetPart.Worksheet.Elements <SheetData>().FirstOrDefault() == null)
{
//workbookPart.AddNewPart<WorksheetPart>()：添加一个新的WorksheetPart
worksheetPart.Worksheet.AppendChild(new SheetData());
}
worksheetPart.Worksheet.Save();
//Sheet
if (sheet == null)
{
sheet = workbookPart.Workbook.Sheets.Elements <Sheet>().LastOrDefault();
//定义默认的sheetId值，当工作薄中一个工作表也没有时，便以1作为新添加的Sheet元素的Id值
UInt32Value sheetId = 1;
if (sheet != null)
{
sheetId = sheet.SheetId + 1;
}
//AppendChild：添加一个子元素
workbookPart.Workbook.Sheets.AppendChild(new Sheet()
{
//Id为字符串类型的Id属性
Id = workbookPart.GetIdOfPart(worksheetPart),
//SheetId为数值类型的Id属性
SheetId = sheetId,
//工作表的名称
Name = sheetName
});
}
workbookPart.Workbook.Save();
//此处解释同上方CreateExcel()方法中一致
reLoad();
}
// Remove all the comments in the slides by a certain author.
public static void DeleteCommentsByAuthorInPresentation(string fileName, string author)
{
if (String.IsNullOrEmpty(fileName) || String.IsNullOrEmpty(author))
{
throw new ArgumentNullException("File name or author name is NULL!");
}
using (PresentationDocument doc = PresentationDocument.Open(fileName, true))
{
// Get the specifed comment author.
IEnumerable <CommentAuthor> commentAuthors =
doc.PresentationPart.CommentAuthorsPart.CommentAuthorList.Elements <CommentAuthor>()
.Where(e => e.Name.Value.Equals(author));
// Iterate through all the matching authors.
foreach (CommentAuthor commentAuthor in commentAuthors)
{
UInt32Value authorId = commentAuthor.Id;
// Iterate through all the slides and get the slide parts.
foreach (SlidePart slide in doc.PresentationPart.SlideParts)
{
SlideCommentsPart slideCommentsPart = slide.SlideCommentsPart;
// Get the list of comments.
if (slideCommentsPart != null && slide.SlideCommentsPart.CommentList != null)
{
IEnumerable <Comment> commentList =
slideCommentsPart.CommentList.Elements <Comment>().Where(e => e.AuthorId == authorId.Value);
List <Comment> comments = new List <Comment>();
comments = commentList.ToList <Comment>();
foreach (Comment comm in comments)
{
// Delete all the comments by the specified author.
slideCommentsPart.CommentList.RemoveChild <Comment>(comm);
}
// If the commentPart has no existing comment.
if (slideCommentsPart.CommentList.ChildElements.Count == 0)
{
// Delete this part.
slide.DeletePart(slideCommentsPart);
}
}
}
// Delete the comment author from the comment authors part.
doc.PresentationPart.CommentAuthorsPart.CommentAuthorList.RemoveChild <CommentAuthor>(commentAuthor);
}
}
}
public static PageMargin PageMarginFromPredefined(PredefinedPageMargins marginType)
{
var margins = PageMargins(marginType);
return(new PageMargin
{
Top = Int32Value.FromInt32(Convert.ToInt32(margins.top.ToDxa())),
Left = UInt32Value.FromUInt32(Convert.ToUInt32(margins.left.ToDxa())),
Right = UInt32Value.FromUInt32(Convert.ToUInt32(margins.right.ToDxa())),
Bottom = Int32Value.FromInt32(Convert.ToInt32(margins.bottom.ToDxa())),
Header = UInt32Value.FromUInt32(Convert.ToUInt32(margins.header.ToDxa())),
Footer = UInt32Value.FromUInt32(Convert.ToUInt32(margins.footer.ToDxa()))
});
}
private void Intialize()
{
DateStyleIndex = CreateCellFormat(
Document.WorkbookPart.WorkbookStylesPart.Stylesheet,
null,
null,
UInt32Value.FromUInt32(14));
DateTimeStyleIndex = CreateCellFormat(
Document.WorkbookPart.WorkbookStylesPart.Stylesheet,
null,
null,
UInt32Value.FromUInt32(22));
}
private UInt32Value GetCellFormatIndex(WorkbookStylesPart stylesPart, UInt32Value borderId)
{
UInt32Value index = 0U;
for (int i = 0; i < stylesPart.Stylesheet.CellFormats.ChildElements.Count; i++)
{
if ((((CellFormat)stylesPart.Stylesheet.CellFormats.ElementAt(i))).BorderId == borderId)
{
index = Convert.ToUInt32(i);
break;
}
}
return(index);
}
/// <summary>
/// Insert date from datatable to worksheet.
/// </summary>
private static void InsertTableData(QueryResult queryResult, SheetData sheetData, WorkbookStylesPart stylesPart)
{
// Create excel columns to define a column widths.
int columnIndx = 0;
var cols = new Columns( );
foreach (ResultColumn column in queryResult.Columns)
{
if (!column.IsHidden)
{
double width = GetColumnWidth(column.DisplayName);
cols.Append(CreateColumnData(columnIndx, width));
columnIndx++;
}
}
sheetData.Append(cols);
UInt32Value headerStyleIndex = CreateHeaderStyles(stylesPart.Stylesheet);
// Add column names to the first row
var header = new Row
{
RowIndex = ( UInt32 )1
};
int headerColInx = 0;
foreach (ResultColumn column in queryResult.Columns)
{
if (!column.IsHidden)
{
Cell headerCell = CreateTextCell(
column.DisplayName,
headerStyleIndex);
SetCellLocation(headerCell, headerColInx, 1);
header.AppendChild(headerCell);
headerColInx++;
}
}
sheetData.AppendChild(header);
// Loop through each data row
int excelRowIndex = 2;
foreach (DataRow row in queryResult.DataTable.Rows)
{
Row excelRow = CreateContentRow(queryResult, row, excelRowIndex++);
sheetData.AppendChild(excelRow);
}
}
/// <summary>
/// Gets OpenXml <see cref="SharedStringTable"/> from document.
/// </summary>
/// <param name="documentContext">Source document context.</param>
/// <param name="autoCreate">Create if not exists.</param>
/// <returns><see cref="SharedStringTable"/> instance.</returns>
public static SharedStringTable GetSharedStringTable(this DocumentContext documentContext, bool autoCreate = true)
{
SharedStringTablePart workbookSharedStringsPart = documentContext.GetWorkbookSharedStringsPart(autoCreate);
if (autoCreate && workbookSharedStringsPart.SharedStringTable == null)
{
SharedStringTable sharedStringTable = new SharedStringTable();
sharedStringTable.Count = UInt32Value.FromUInt32(0);
sharedStringTable.UniqueCount = UInt32Value.FromUInt32(0);
workbookSharedStringsPart.SharedStringTable = sharedStringTable;
}
return(workbookSharedStringsPart.SharedStringTable);
}
public MultipleSheetEntity([NotNull] ResourceEntity resourceEntity, int index, [NotNull][ItemNotNull] ISet <string> uniqueNames)
{
Contract.Requires(resourceEntity != null);
Contract.Requires(uniqueNames != null);
_resourceEntity = resourceEntity;
var sheetName = GetSheetName(resourceEntity, uniqueNames);
_sheetName = sheetName;
Id = "Id" + index + 1;
_sheetId = UInt32Value.FromUInt32((uint)index + 1);
}
private UInt32Value GetNextDrawingId()
{
var drawingId = new UInt32Value((uint)1);
foreach (var drawing in _mainDocumentPart.Document.Body.Descendants <Drawing>())
{
if (drawing.Inline != null && drawing.Inline.DocProperties.Id > drawingId)
{
drawingId = drawing.Inline.DocProperties.Id;
}
}
return(drawingId > 1 ? ++drawingId : drawingId);
}
public static void AddDateFormat(string excelFilePath)
{
string text = "01-31-1947";
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(excelFilePath, true))
{
// get the stylesheet from the current sheet
var stylesheet = spreadsheetDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
// cell formats are stored in the stylesheet's NumberingFormats
var numberingFormats = stylesheet.NumberingFormats;
// cell format string
const string dateFormatCode = "dd/mm/yyyy";
// first check if we find an existing NumberingFormat with the desired formatcode
var dateFormat =
numberingFormats.OfType <NumberingFormat>()
.FirstOrDefault(format => format.FormatCode == dateFormatCode);
// if not: create it
if (dateFormat == null)
{
dateFormat = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(164),
// Built-in number formats are numbered 0 - 163. Custom formats must start at 164.
FormatCode = StringValue.FromString(dateFormatCode)
};
numberingFormats.AppendChild(dateFormat);
// we have to increase the count attribute manually ?!?
numberingFormats.Count = Convert.ToUInt32(numberingFormats.Count());
// save the new NumberFormat in the stylesheet
stylesheet.Save();
}
// get the (1-based) index of the dateformat
var dateStyleIndex = numberingFormats.ToList().IndexOf(dateFormat) + 1;
var worksheetPart = GetWorksheetPartByName(spreadsheetDoc, "Sheet1");
Row row1 = worksheetPart.Worksheet.GetFirstChild <SheetData>().Elements <Row>().FirstOrDefault();
int counter = 0;
Cell cell = row1.Elements <Cell>().FirstOrDefault();
DateTime dateTime = DateTime.Parse(text);
double oaValue = dateTime.ToOADate();
cell.CellValue = new CellValue(oaValue.ToString(CultureInfo.InvariantCulture));
cell.StyleIndex = Convert.ToUInt32(dateStyleIndex);
worksheetPart.Worksheet.Save();
spreadsheetDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
}
/// <summary>
/// Creayes borders
/// </summary>
/// <param name="styleSheet">The style sheet.</param>
static void CreateBorders(Stylesheet styleSheet)
{
Borders borders = new Borders();
borders.Append(new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
});
//Border Index 1
borders.Append(new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
});
//Border Index 2
borders.Append(new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
});
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
styleSheet.Append(borders);
}
public static void PageSetupUpdate(WorksheetPart worksheetPart, OrientationValues landscapeOrPortrait,
DoubleValue marginLeft, DoubleValue marginRight, DoubleValue marginTop, DoubleValue marginBottom, DoubleValue marginHeader, DoubleValue marginFooter,
Boolean isFitToPage, UInt32Value FitToHeight, UInt32Value FitToWidth, UInt32Value pageSize, string headerLeft, string headerCenter, string headerRight, string footerLeft, string footerRight)
{
Worksheet ws = worksheetPart.Worksheet;
//page setup them moi pagesetup properties
SheetProperties sp = new SheetProperties(new PageSetupProperties());
ws.SheetProperties = sp;
PrintOptions printOp = new PrintOptions();
printOp.HorizontalCentered = true;
ws.AppendChild(printOp);
PageMargins pageMargins = new PageMargins();
pageMargins.Left = marginLeft;
pageMargins.Right = marginRight;
pageMargins.Top = marginTop;
pageMargins.Bottom = marginBottom;
pageMargins.Header = marginHeader;
pageMargins.Footer = marginFooter;
ws.AppendChild(pageMargins);
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(isFitToPage);
DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup();
pgOr.Orientation = landscapeOrPortrait;
pgOr.PaperSize = pageSize;
pgOr.FitToHeight = FitToHeight;
pgOr.FitToWidth = FitToWidth;
ws.AppendChild(pgOr);
HeaderFooter headerFooter1 = new HeaderFooter();
OddHeader oddHeader1 = new OddHeader();
oddHeader1.Text = "&L&\"Times New Roman,Regular\"" + headerLeft + "&C&\"Times New Roman,Regular\"" + headerCenter + "&R&\"Times New Roman,Regular\"" + headerRight;
OddFooter oddFooter1 = new OddFooter();
oddFooter1.Text = "&L&\"Times New Roman,Regular\"" + footerLeft + "&C&P&R&\"Times New Roman,Regular\"" + footerRight;
headerFooter1.Append(oddHeader1);
headerFooter1.Append(oddFooter1);
ws.AppendChild(headerFooter1);
//save worksheet properties
//worksheetPart.Worksheet.Save();
}
private static Borders CreateBorders()
{
var borders = new Borders();
// boarder index 0
borders.AppendChild(new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
});
// boarder Index 1
borders.AppendChild(new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
});
// boarder Index 2
borders.AppendChild(new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
});
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
return(borders);
}
/// <summary>
/// 转换主题色
/// </summary>
/// <param name="themeIndex">主题色索引</param>
/// <param name="tint">主题色百分比</param>
/// <param name="themColor">主题色色值</param>
/// <returns>返回Rgb格式颜色</returns>
internal static System.Drawing.Color ThemColorDeal(UInt32Value themeIndex, DoubleValue tint, string themColor)
{
#region 主题色处理
var val = double.Parse(tint.Value.ToString());
var persent = int.Parse((double.Parse(val.ToString("0.00")) * 100).ToString());
var rgbColor = ThemeColor.Hx16ToRgb("#" + themColor);
int h, s, v;
ThemeColor.RgbToHsv(rgbColor.R, rgbColor.G, rgbColor.B, out h, out s, out v);
if (themeIndex > 1)
{
if (persent == 80)
{
s = Convert.ToInt32(s * 0.2);
v = 95;
}
else if (persent == 60)
{
s = Convert.ToInt32(s * 0.4);
v = 89;
}
else if (persent == 40)
{
s = Convert.ToInt32(s * 0.6);
v = 84;
}
else if (persent < 0)
{
s = s + 5;
v = Convert.ToInt32(Convert.ToDouble((persent + 100)) / 100 * v);
}
}
else
{
if (val < 0)
{
v = 100 + persent;
}
else
{
v = v - persent;
}
if (v < 0)
{
v = v * -1;
}
}
#endregion
return(ThemeColor.HsvToRgb(h, s, v));
}
public uint MergeAndRegisterFont(Font fNew, UInt32Value baseFontsIdx, bool doSave)
{
Stylesheet stylesheet = this.EnsureStylesheet();
uint num = this.MergeAndRegisterStyleElement <Font, Fonts>(fNew, stylesheet.Fonts, new Func <Font, Font, Font>(this.MergeFont), new Func <Font, Font, bool>(this.compareFont), baseFontsIdx, doSave);
if (stylesheet.Fonts.Count != stylesheet.Fonts.Count <OpenXmlElement>())
{
stylesheet.Fonts.Count = (UInt32Value)(uint)stylesheet.Fonts.Count <OpenXmlElement>();
if (doSave)
{
stylesheet.Save();
}
}
return(num);
}
public uint MergeAndRegisterFill(Fill fNew, UInt32Value baseFillsIdx, bool doSave)
{
Stylesheet stylesheet = this.EnsureStylesheet();
uint num = this.MergeAndRegisterStyleElement <Fill, Fills>(fNew, stylesheet.Fills, new Func <Fill, Fill, Fill>(this.fillCombine), new Func <Fill, Fill, bool>(this.fillCompare), baseFillsIdx, doSave);
if (stylesheet.Fills.Count != stylesheet.Fills.Count <OpenXmlElement>())
{
stylesheet.Fills.Count = (UInt32Value)(uint)stylesheet.Fills.Count <OpenXmlElement>();
if (doSave)
{
stylesheet.Save();
}
}
return(num);
}
public TableRow GenerateTableFooterRow(CellProps[] cellProps, UInt32Value height)
{
// Add table row properties
TableRow tableRow1 = new TableRow();
TableRowProperties tableRowProperties1 = generateTableRowProperties(height);
tableRow1.Append(tableRowProperties1);
foreach (CellProps cp in cellProps) {
if (cp.span == 0) continue;
TableCell tableCell1 = new TableCell();
TableCellProperties tableCellProperties1 = new TableCellProperties();
TableCellBorders tableCellBorders1;
if (cp.boxed) {
tableCellBorders1 = generateTableCellBordersBox();
} else {
tableCellBorders1 = generateTableCellBordersPlain();
}
Shading shading1 = new Shading() { Val = ShadingPatternValues.Clear, Color = "auto", Fill = "auto" };
NoWrap noWrap1 = new NoWrap();
TableCellVerticalAlignment tableCellVerticalAlignment1 = new TableCellVerticalAlignment() { Val = TableVerticalAlignmentValues.Center };
HideMark hideMark1 = new HideMark();
tableCellProperties1.Append(tableCellBorders1);
tableCellProperties1.Append(shading1);
tableCellProperties1.Append(noWrap1);
tableCellProperties1.Append(tableCellVerticalAlignment1);
tableCellProperties1.Append(hideMark1);
Paragraph paragraph1 = new Paragraph();
ParagraphProperties paragraphProperties1 = new ParagraphProperties();
Justification justification1 = new Justification() { Val = JustificationValues.Right };
ParagraphMarkRunProperties paragraphMarkRunProperties1 = new ParagraphMarkRunProperties();
Bold bold1 = new Bold();
BoldComplexScript boldComplexScript1 = new BoldComplexScript();
Color color1 = new Color() { Val = "333399" };
FontSize fontSize1 = new FontSize() { Val = "18" };
FontSizeComplexScript fontSizeComplexScript1 = new FontSizeComplexScript() { Val = "18" };
paragraphMarkRunProperties1.Append(bold1);
paragraphMarkRunProperties1.Append(boldComplexScript1);
paragraphMarkRunProperties1.Append(color1);
paragraphMarkRunProperties1.Append(fontSize1);
paragraphMarkRunProperties1.Append(fontSizeComplexScript1);
paragraphProperties1.Append(justification1);
paragraphProperties1.Append(paragraphMarkRunProperties1);
Run run1 = new Run();
RunProperties runProperties1 = new RunProperties();
Bold bold2 = new Bold();
BoldComplexScript boldComplexScript2 = new BoldComplexScript();
Color color2 = new Color() { Val = "333399" };
FontSize fontSize2 = new FontSize() { Val = "18" };
FontSizeComplexScript fontSizeComplexScript2 = new FontSizeComplexScript() { Val = "18" };
runProperties1.Append(bold2);
runProperties1.Append(boldComplexScript2);
runProperties1.Append(color2);
runProperties1.Append(fontSize2);
runProperties1.Append(fontSizeComplexScript2);
run1.Append(runProperties1);
if (cp.text != null) {
Text text1 = new Text();
text1.Text = cp.text;
run1.Append(text1);
}
paragraph1.Append(paragraphProperties1);
paragraph1.Append(run1);
tableCell1.Append(tableCellProperties1);
tableCell1.Append(paragraph1);
tableRow1.Append(tableCell1);
}
return tableRow1;
}
// Creates an TableRowProperties instance and adds its children.
private TableRowProperties generateTableRowProperties(UInt32Value height)
{
TableRowProperties tableRowProperties1 = new TableRowProperties();
TableRowHeight tableRowHeight1 = new TableRowHeight() { Val = (UInt32Value)height };
tableRowProperties1.Append(tableRowHeight1);
return tableRowProperties1;
}
public static void UpdateValue(
WorkbookPart wbPart,
Worksheet ws,
string addressName,
string value,
UInt32Value styleIndex,
bool isString)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = InsertCellInWorksheet(ws, addressName);
if (isString)
{
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = InsertSharedStringItem(wbPart, value);
cell.CellValue = new CellValue(stringIndex.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(value);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
if (styleIndex > 0)
cell.StyleIndex = styleIndex;
// Save the worksheet.
ws.Save();
}
public static Fills CreateFillsCollection(Stylesheet stylesheet, UInt32Value capacity)
{
var fills = new Fills { Count = capacity };
stylesheet.Append(fills);
return fills;
}
public static Fonts CreateFontsCollection(Stylesheet stylesheet, UInt32Value capacity)
{
var fonts = new Fonts { Count = capacity, KnownFonts = true };
stylesheet.Append(fonts);
return fonts;
}
/// <summary>
/// Update the value of a existing cell
/// </summary>
/// <param name="cellReference">Address of the cell</param>
/// <param name="cellStringValue">Value to update</param>
/// <param name="excelRow">Row of the cell</param>
private static void UpdateStringCellValue(string cellReference, string cellStringValue, Row excelRow, UInt32Value styleIndex = null)
{
Cell currentCell = excelRow.Elements<Cell>().First(cell => cell.CellReference.Value == cellReference);
currentCell.CellValue = new CellValue(cellStringValue);
currentCell.DataType = new EnumValue<CellValues>(CellValues.String);
if (styleIndex != null)
currentCell.StyleIndex = styleIndex;
}
public static Borders CreateBordersCollection(Stylesheet stylesheet, UInt32Value capacity)
{
var borders = new Borders { Count = capacity };
stylesheet.Append(borders);
return borders;
}
internal static OpenXmlSimpleType CreateTargetValueObject(RedirectedRestriction redirectedRestriction)
{
OpenXmlSimpleType simpleValue = null;
switch (redirectedRestriction.AttributeId)
{
// property name: Width
case 2387:
simpleValue = new UInt32Value();
break;
// property name: VerticalSpace
case 2389:
simpleValue = new UInt32Value();
break;
// property name: HorizontalSpace
case 2390:
simpleValue = new UInt32Value();
break;
// property name: X
case 2394:
simpleValue = new Int32Value();
break;
// property name: Y
case 2396:
simpleValue = new Int32Value();
break;
// property name: Before
case 2409:
simpleValue = new UInt32Value();
break;
// property name: After
case 2412:
simpleValue = new UInt32Value();
break;
// property name: Line
case 2415:
simpleValue = new Int32Value();
break;
// property name: Left
case 2417:
simpleValue = new Int32Value();
break;
// property name: Start
case 2418:
simpleValue = new Int32Value();
break;
// property name: Right
case 2421:
simpleValue = new Int32Value();
break;
// property name: End
case 2422:
simpleValue = new Int32Value();
break;
// property name: Hanging
case 2425:
simpleValue = new UInt32Value();
break;
// property name: FirstLine
case 2427:
simpleValue = new UInt32Value();
break;
// property name: Val
case 2473:
simpleValue = new Int32Value();
break;
// property name: Val
case 2474:
simpleValue = new UInt32Value();
break;
// property name: Width
case 2589:
simpleValue = new Int32Value();
break;
// property name: Distance
case 2643:
simpleValue = new UInt32Value();
break;
// property name: Space
case 2650:
simpleValue = new UInt32Value();
break;
// property name: Val
case 2680:
simpleValue = new StringValue();
break;
// property name: Width
case 2686:
simpleValue = new UInt32Value();
break;
// property name: Space
case 2687:
simpleValue = new UInt32Value();
break;
// property name: Width
case 2704:
simpleValue = new UInt32Value();
break;
// property name: Val
case 2732:
simpleValue = new UInt32Value();
break;
// property name: LegacySpace
case 2738:
simpleValue = new UInt32Value();
break;
// property name: LegacyIndent
case 2739:
simpleValue = new Int32Value();
break;
// property name: Val
case 2769:
simpleValue = new Int32Value();
break;
// property name: Percent
case 2847:
simpleValue = new Int32Value();
break;
// property name: Val
case 2872:
simpleValue = new HexBinaryValue();
break;
// property name: FontSize
case 2924:
simpleValue = new Int32Value();
break;
// property name: Val
case 2986:
simpleValue = new StringValue();
break;
// property name: Delay
case 2991:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3071:
simpleValue = new StringValue();
break;
// property name: AutoAdvance
case 3079:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3080:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3084:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3089:
simpleValue = new StringValue();
break;
// property name: AdvanceAfterTime
case 3150:
simpleValue = new UInt32Value();
break;
// property name: ShapeId
case 3153:
simpleValue = new StringValue();
break;
default:
Debug.Assert(false);
break;
}
return simpleValue;
}
/// <summary>
/// Append text in a cell
/// </summary>
/// <param name="cellReference">Reference</param>
/// <param name="cellStringValue">Value</param>
/// <param name="excelRow">Excel row</param>
/// <param name="styleIndex">Style</param>
private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow, UInt32Value styleIndex)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.StyleIndex = styleIndex;
cell.Append(cellValue);
excelRow.Append(cell);
}
internal static OpenXmlSimpleType[] CreatePossibleMembers(UnionValueRestriction unionValueRestriction)
{
OpenXmlSimpleType[] simpleValues = new OpenXmlSimpleType[unionValueRestriction.UnionTypes.Length];
switch (unionValueRestriction.UnionId)
{
// ST_AnimationDgmBuildType
case 25:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Drawing.AnimationBuildValues>();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.Drawing.AnimationDiagramOnlyBuildValues>();
break;
// ST_AnimationChartBuildType
case 27:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Drawing.AnimationBuildValues>();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.Drawing.AnimationChartOnlyBuildValues>();
break;
// ST_AdjCoordinate
case 45:
simpleValues[0] = new Int64Value();
simpleValues[1] = new StringValue();
break;
// ST_AdjAngle
case 46:
simpleValues[0] = new Int32Value();
simpleValues[1] = new StringValue();
break;
// ST_LayoutShapeType
case 145:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Drawing.ShapeTypeValues>();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.OutputShapeValues>();
break;
// ST_ParameterVal
case 183:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.HorizontalAlignmentValues>();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.VerticalAlignmentValues>();
simpleValues[2] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.ChildDirectionValues>();
simpleValues[3] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.ChildAlignmentValues>();
simpleValues[4] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.SecondaryChildAlignmentValues>();
simpleValues[5] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.LinearDirectionValues>();
simpleValues[6] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.SecondaryLinearDirectionValues>();
simpleValues[7] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.StartingElementValues>();
simpleValues[8] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.BendPointValues>();
simpleValues[9] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.ConnectorRoutingValues>();
simpleValues[10] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.ArrowheadStyleValues>();
simpleValues[11] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.ConnectorDimensionValues>();
simpleValues[12] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.RotationPathValues>();
simpleValues[13] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.CenterShapeMappingValues>();
simpleValues[14] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.NodeHorizontalAlignmentValues>();
simpleValues[15] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.NodeVerticalAlignmentValues>();
simpleValues[16] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.FallbackDimensionValues>();
simpleValues[17] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.TextDirectionValues>();
simpleValues[18] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.PyramidAccentPositionValues>();
simpleValues[19] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.PyramidAccentTextMarginValues>();
simpleValues[20] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.TextBlockDirectionValues>();
simpleValues[21] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.TextAnchorHorizontalValues>();
simpleValues[22] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.TextAnchorVerticalValues>();
simpleValues[23] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.TextAlignmentValues>();
simpleValues[24] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.AutoTextRotationValues>();
simpleValues[25] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.GrowDirectionValues>();
simpleValues[26] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.FlowDirectionValues>();
simpleValues[27] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.ContinueDirectionValues>();
simpleValues[28] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.BreakpointValues>();
simpleValues[29] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.OffsetValues>();
simpleValues[30] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.HierarchyAlignmentValues>();
simpleValues[31] = new Int32Value();
simpleValues[32] = new DoubleValue();
simpleValues[33] = new BooleanValue();
simpleValues[34] = new StringValue();
simpleValues[35] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.ConnectorPointValues>();
break;
// ST_ModelId
case 184:
simpleValues[0] = new Int32Value();
simpleValues[1] = new StringValue();
break;
// ST_FunctionValue
case 207:
simpleValues[0] = new Int32Value();
simpleValues[1] = new BooleanValue();
simpleValues[2] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.DirectionValues>();
simpleValues[3] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.HierarchyBranchStyleValues>();
simpleValues[4] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.AnimateOneByOneValues>();
simpleValues[5] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.AnimationLevelStringValues>();
simpleValues[6] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.ResizeHandlesStringValues>();
break;
// ST_FunctionArgument
case 209:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Drawing.Diagrams.VariableValues>();
break;
// ST_NonZeroDecimalNumber
case 368:
simpleValues[0] = new Int32Value();
simpleValues[1] = new Int32Value();
break;
// ST_MarkupId
case 375:
simpleValues[0] = new Int32Value();
simpleValues[1] = new Int32Value();
break;
// ST_HexColor
case 404:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Wordprocessing.AutomaticColorValues>();
simpleValues[1] = new HexBinaryValue();
break;
// ST_DecimalNumberOrPercent
case 507:
simpleValues[0] = new StringValue();
simpleValues[1] = new Int32Value();
break;
// ST_SignedHpsMeasure_O14
case 525:
simpleValues[0] = new IntegerValue();
simpleValues[1] = new StringValue();
break;
// ST_HpsMeasure_O14
case 528:
simpleValues[0] = new UInt32Value();
simpleValues[1] = new StringValue();
break;
// ST_SignedTwipsMeasure_O14
case 531:
simpleValues[0] = new IntegerValue();
simpleValues[1] = new StringValue();
break;
// ST_TwipsMeasure_O14
case 534:
simpleValues[0] = new UInt32Value();
simpleValues[1] = new StringValue();
break;
// ST_TransitionEightDirectionType
case 544:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Presentation.TransitionSlideDirectionValues>();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.Presentation.TransitionCornerDirectionValues>();
break;
// ST_TLTimeAnimateValueTime
case 561:
simpleValues[0] = new Int32Value();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.Presentation.IndefiniteTimeDeclarationValues>();
break;
// ST_TLTime_O12
case 603:
simpleValues[0] = new UInt32Value();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.Presentation.IndefiniteTimeDeclarationValues>();
break;
// ST_TLTime_O14
case 604:
simpleValues[0] = new StringValue();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.Presentation.IndefiniteTimeDeclarationValues>();
break;
// ST_PublishDate
case 689:
simpleValues[0] = new DateTimeValue();
simpleValues[1] = new DateTimeValue();
simpleValues[2] = new StringValue();
break;
// ST_ChannelDataPoint
case 697:
simpleValues[0] = new DecimalValue();
simpleValues[1] = new BooleanValue();
break;
// ST_ChannelPropertyName
case 701:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardChannelPropertyNameValues>();
simpleValues[1] = new StringValue();
break;
// ST_BrushPropertyName
case 704:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardBrushPropertyNameValues>();
simpleValues[1] = new StringValue();
break;
// ST_ChannelName
case 707:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardChannelNameValues>();
simpleValues[1] = new StringValue();
break;
// ST_Units
case 719:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardLengthUnitsValues>();
simpleValues[1] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardPerLengthUnitsValues>();
simpleValues[2] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardTimeUnitsValues>();
simpleValues[3] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardPerTimeUnitsValues>();
simpleValues[4] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardMassForceUnitsValues>();
simpleValues[5] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardPerMassForceUnitsValues>();
simpleValues[6] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardAngleUnitsValues>();
simpleValues[7] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardPerAngleUnitsValues>();
simpleValues[8] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardOtherUnitsValues>();
simpleValues[9] = new EnumValue<DocumentFormat.OpenXml.InkML.StandardPerOtherUnitsValues>();
simpleValues[10] = new StringValue();
break;
// ST_BrushPropertyValue
case 732:
simpleValues[0] = new DecimalValue();
simpleValues[1] = new BooleanValue();
simpleValues[2] = new EnumValue<DocumentFormat.OpenXml.InkML.PenTipShapeValues>();
simpleValues[3] = new EnumValue<DocumentFormat.OpenXml.InkML.RasterOperationValues>();
simpleValues[4] = new StringValue();
break;
// ST_Ref
case 746:
simpleValues[0] = new StringValue();
simpleValues[1] = new UInt32Value();
break;
// ST_CtxNodeType
case 747:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Office2010.Ink.KnownContextNodeTypeValues>();
simpleValues[1] = new StringValue();
break;
// ST_SemanticType
case 750:
simpleValues[0] = new EnumValue<DocumentFormat.OpenXml.Office2010.Ink.KnownSemanticTypeValues>();
simpleValues[1] = new UInt32Value();
break;
// ST_PointsOrInt
case 753:
simpleValues[0] = new ListValue<StringValue>();
simpleValues[1] = new Int32Value();
break;
default:
Debug.Assert(false);
break;
}
Debug.Assert(simpleValues.Length > 0);
return simpleValues;
}
private UInt32Value GetNextImageId()
{
var imageId = new UInt32Value((uint)1);
foreach (var drawing in _mainDocumentPart.Document.Body.Descendants<Drawing>())
{
var nvPr = drawing.Inline.Graphic.GraphicData.GetFirstChild<Pic.NonVisualPictureProperties>();
if (nvPr != null && nvPr.NonVisualDrawingProperties.Id > imageId)
{
imageId = nvPr.NonVisualDrawingProperties.Id;
}
}
return imageId > 1 ? ++imageId : imageId;
}
public void OpenXmlSimpleTypeConverterTest()
{
// 1. Base64BinaryValue
Base64BinaryValue base64 = new Base64BinaryValue();
base64 = "AA3322";
Assert.True("AA3322" == base64);
Assert.Equal("AA3322", base64.Value);
base64 = Base64BinaryValue.FromString("1234");
Assert.Equal("1234", base64.ToString());
Assert.Equal("1234", Base64BinaryValue.ToString(base64));
// 2. BooleanValue
BooleanValue booleanValue = new BooleanValue();
booleanValue = true;
Assert.True(booleanValue);
Assert.True(booleanValue.Value);
booleanValue = BooleanValue.FromBoolean(false);
Assert.False(booleanValue);
Assert.Equal(false, BooleanValue.ToBoolean(booleanValue));
// 3. ByteValue
ByteValue byteValue = new ByteValue();
Byte bt = 1;
byteValue = bt;
Assert.True(bt == byteValue);
Assert.Equal(bt, byteValue.Value);
bt = 2;
byteValue = ByteValue.FromByte(bt);
Assert.Equal(bt, ByteValue.ToByte(byteValue));
// 4. DateTimeValue
DateTimeValue dtValue = new DateTimeValue();
DateTime dt = DateTime.Now;
dtValue = dt;
Assert.True(dt == dtValue);
dt = DateTime.Now.AddDays(1);
dtValue = DateTimeValue.FromDateTime(dt);
Assert.Equal(dt, dtValue.Value);
Assert.Equal(dt, DateTimeValue.ToDateTime(dt));
// 5. DecimalValue
DecimalValue decimalValue = new DecimalValue();
decimal dcm = 10;
decimalValue = dcm;
Assert.True(dcm == decimalValue);
decimalValue = DecimalValue.FromDecimal(20);
Assert.Equal(20, decimalValue.Value);
Assert.Equal(20, DecimalValue.ToDecimal(decimalValue));
// 6. DoubleValue
DoubleValue doubleValue = new DoubleValue();
double dbl = 1.1;
doubleValue = dbl;
Assert.True(dbl == doubleValue);
doubleValue = DoubleValue.FromDouble(2.2);
Assert.Equal(2.2, doubleValue.Value);
Assert.Equal(2.2, DoubleValue.ToDouble(doubleValue));
// 7. HexBinaryValue
HexBinaryValue hexBinaryValue = new HexBinaryValue();
string hex = "0X99CCFF";
hexBinaryValue = hex;
Assert.True(hex == hexBinaryValue);
hex = "111111";
hexBinaryValue = HexBinaryValue.FromString(hex);
Assert.Equal(hex, hexBinaryValue.Value);
Assert.Equal(hex, HexBinaryValue.ToString(hexBinaryValue));
// 8. Int16
Int16Value int16Value = new Int16Value();
Int16 int16 = 16;
int16Value = int16;
Assert.True(int16 == int16Value);
int16 = 17;
int16Value = Int16Value.FromInt16(int16);
Assert.Equal(int16, int16Value.Value);
Assert.Equal(int16, Int16Value.ToInt16(int16Value));
// 9. Int32
Int32Value int32Value = new Int32Value();
Int32 int32 = 32;
int32Value = int32;
Assert.True(int32 == int32Value);
int32 = 33;
int32Value = Int32Value.FromInt32(int32);
Assert.Equal(int32, int32Value.Value);
Assert.Equal(int32, Int32Value.ToInt32(int32Value));
// 10. Int64
Int64Value int64Value = new Int64Value();
Int64 int64 = 64;
int64Value = int64;
Assert.True(int64 == int64Value);
int64 = 17;
int64Value = Int64Value.FromInt64(int64);
Assert.Equal(int64, int64Value.Value);
Assert.Equal(int64, Int64Value.ToInt64(int64Value));
// 11. IntegerValue
IntegerValue integerValue = new IntegerValue();
int integer = 64;
integerValue = integer;
Assert.True(integer == integerValue);
integer = 17;
integerValue = IntegerValue.FromInt64(integer);
Assert.Equal(integer, integerValue.Value);
Assert.Equal(integer, IntegerValue.ToInt64(integerValue));
// 12. OnOffValue
OnOffValue onOffValue = new OnOffValue();
onOffValue = true;
Assert.True(onOffValue);
onOffValue = OnOffValue.FromBoolean(false);
Assert.Equal(false, onOffValue.Value);
Assert.Equal(false, OnOffValue.ToBoolean(onOffValue));
// 13. SByteValue
SByteValue sbyteValue = new SByteValue();
SByte sbt = SByte.MaxValue;
sbyteValue = sbt;
Assert.True(sbt == sbyteValue);
sbt = SByte.MinValue;
sbyteValue = SByteValue.FromSByte(sbt);
Assert.Equal(sbt, sbyteValue.Value);
Assert.Equal(sbt, SByteValue.ToSByte(sbt));
// 14. SingleValue
SingleValue singleValue = new SingleValue();
Single single = Single.MaxValue;
singleValue = single;
Assert.True(single == singleValue);
single = Single.NaN;
singleValue = SingleValue.FromSingle(single);
Assert.Equal(single, singleValue.Value);
Assert.Equal(single, SingleValue.ToSingle(singleValue));
// 15. StringValue
StringValue stringValue = new StringValue();
String str = "Ethan";
stringValue = str;
Assert.True(str == stringValue);
str = "Yin";
stringValue = StringValue.FromString(str);
Assert.Equal(str, stringValue.Value);
Assert.Equal(str, stringValue.ToString());
Assert.Equal(str, StringValue.ToString(stringValue));
// 16. TrueFalseBlankValue
TrueFalseBlankValue tfbValue = new TrueFalseBlankValue();
tfbValue = true;
Assert.True(tfbValue);
tfbValue = TrueFalseBlankValue.FromBoolean(false);
Assert.Equal(false, tfbValue.Value);
Assert.Equal(false, TrueFalseBlankValue.ToBoolean(tfbValue));
// 17. TrueFalseValue
TrueFalseValue tfValue = new TrueFalseValue();
tfValue = true;
Assert.True(tfValue);
tfValue = TrueFalseValue.FromBoolean(false);
Assert.Equal(false, tfValue.Value);
Assert.Equal(false, TrueFalseValue.ToBoolean(tfValue));
// 18. UInt16Value
UInt16Value uint16Value = new UInt16Value();
UInt16 uint16 = UInt16.MaxValue;
uint16Value = uint16;
Assert.True(uint16 == uint16Value);
uint16 = UInt16.MinValue;
uint16Value = UInt16Value.FromUInt16(uint16);
Assert.Equal(uint16, uint16Value.Value);
Assert.Equal(uint16, UInt16Value.ToUInt16(uint16Value));
// 19. UInt32Value
UInt32Value uint32Value = new UInt32Value();
UInt32 uint32 = UInt32.MaxValue;
uint32Value = uint32;
Assert.True(uint32 == uint32Value);
uint32 = UInt32.MinValue;
uint32Value = UInt32Value.FromUInt32(uint32);
Assert.Equal(uint32, uint32Value.Value);
Assert.Equal(uint32, UInt32Value.ToUInt32(uint32Value));
// 20. UInt64Value
UInt64Value uint64Value = new UInt64Value();
UInt64 uint64 = UInt64.MaxValue;
uint64Value = uint64;
Assert.True(uint64 == uint64Value);
uint64 = UInt64.MinValue;
uint64Value = UInt64Value.FromUInt64(uint64);
Assert.Equal(uint64, uint64Value.Value);
Assert.Equal(uint64, UInt64Value.ToUInt64(uint64Value));
}
/// <summary>
/// Add text to specified cell, the specified color set
/// </summary>
/// <param name="columnName"></param>
/// <param name="rowIndex"></param>
/// <param name="cellContent"></param>
/// <param name="cellStyleId"></param>
private void InsertTextToCell(string columnName, uint rowIndex, string cellContent, UInt32Value cellStyleId)
{
// Insert cell A1 into the new worksheet.
Cell cell = GetNewCell(columnName, rowIndex);
//insert cell content into shared strings table
int index = InsertSharedStringItem(cellContent);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
cell.StyleIndex = cellStyleId.Value;
}
public static bool UpdateValue(
WorkbookPart wbPart,
string sheetName, string addressName, string value,
UInt32Value styleIndex, bool isString)
{
// Assume failure.
bool updated = false;
Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where(
(s) => s.Name == sheetName).FirstOrDefault();
if (sheet != null)
{
Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
DocumentFormat.OpenXml.Spreadsheet.Cell cell = InsertCellInWorksheet(ws, addressName);
if (isString)
{
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = InsertSharedStringItem(wbPart, value);
cell.CellValue = new CellValue(stringIndex.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(value);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
if (styleIndex > 0)
cell.StyleIndex = styleIndex;
// Save the worksheet.
ws.Save();
updated = true;
}
return updated;
}
protected virtual StringPoint GenerateStringPoint(UInt32Value idx, string text)
{
StringPoint stringPoint1 = new StringPoint() { Index = idx };
NumericValue numericValue1 = new NumericValue();
numericValue1.Text = text;
stringPoint1.Append(numericValue1);
return stringPoint1;
}
internal static OpenXmlSimpleType CreateTargetValueObject(RedirectedRestriction redirectedRestriction)
{
OpenXmlSimpleType simpleValue = null;
switch (redirectedRestriction.AttributeId)
{
// property name: Width
case 2591:
simpleValue = new StringValue();
break;
// property name: Percent
case 2849:
simpleValue = new StringValue();
break;
// property name: FontSize
case 2926:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3073:
simpleValue = new UInt32Value();
break;
// property name: ShapeId
case 3082:
simpleValue = new UInt32Value();
break;
// property name: ShapeId
case 3086:
simpleValue = new UInt32Value();
break;
// property name: ShapeId
case 3091:
simpleValue = new UInt32Value();
break;
// property name: AdvanceAfterTime
case 3152:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3155:
simpleValue = new UInt32Value();
break;
default:
Debug.Assert(false);
break;
}
return simpleValue;
}
public static Font CreateCustomFont(
Fonts fonts,
DoubleValue fontSize,
UInt32Value color,
StringValue fontName,
bool isBold = false,
bool isItalic = false,
bool isUnderline = false)
{
return CreateFont(fonts, fontSize, color, fontName, FamilyNumberingCustom, UsedCharSet, isBold, isItalic, isUnderline);
}
internal static OpenXmlSimpleType CreateTargetValueObject(RedirectedRestriction redirectedRestriction)
{
OpenXmlSimpleType simpleValue = null;
switch (redirectedRestriction.AttributeId)
{
// property name: Width
case 2389:
simpleValue = new StringValue();
break;
// property name: VerticalSpace
case 2391:
simpleValue = new StringValue();
break;
// property name: HorizontalSpace
case 2392:
simpleValue = new StringValue();
break;
// property name: X
case 2396:
simpleValue = new StringValue();
break;
// property name: Y
case 2398:
simpleValue = new StringValue();
break;
// property name: Before
case 2411:
simpleValue = new StringValue();
break;
// property name: After
case 2414:
simpleValue = new StringValue();
break;
// property name: Line
case 2417:
simpleValue = new StringValue();
break;
// property name: Left
case 2419:
simpleValue = new StringValue();
break;
// property name: Start
case 2420:
simpleValue = new StringValue();
break;
// property name: Right
case 2423:
simpleValue = new StringValue();
break;
// property name: End
case 2424:
simpleValue = new StringValue();
break;
// property name: Hanging
case 2427:
simpleValue = new StringValue();
break;
// property name: FirstLine
case 2429:
simpleValue = new StringValue();
break;
// property name: Val
case 2475:
simpleValue = new StringValue();
break;
// property name: Val
case 2476:
simpleValue = new StringValue();
break;
// property name: Width
case 2591:
simpleValue = new StringValue();
break;
// property name: Distance
case 2645:
simpleValue = new StringValue();
break;
// property name: Space
case 2652:
simpleValue = new StringValue();
break;
// property name: Val
case 2682:
simpleValue = new StringValue();
break;
// property name: Width
case 2688:
simpleValue = new StringValue();
break;
// property name: Space
case 2689:
simpleValue = new StringValue();
break;
// property name: Width
case 2706:
simpleValue = new StringValue();
break;
// property name: Val
case 2734:
simpleValue = new StringValue();
break;
// property name: LegacySpace
case 2740:
simpleValue = new StringValue();
break;
// property name: LegacyIndent
case 2741:
simpleValue = new StringValue();
break;
// property name: Val
case 2771:
simpleValue = new StringValue();
break;
// property name: Percent
case 2849:
simpleValue = new StringValue();
break;
// property name: Val
case 2874:
simpleValue = new EnumValue<DocumentFormat.OpenXml.Wordprocessing.StylePaneSortMethodsValues>();
break;
// property name: FontSize
case 2926:
simpleValue = new StringValue();
break;
// property name: Val
case 2988:
simpleValue = new StringValue();
break;
// property name: Delay
case 2993:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3073:
simpleValue = new UInt32Value();
break;
// property name: AutoAdvance
case 3081:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3082:
simpleValue = new UInt32Value();
break;
// property name: ShapeId
case 3086:
simpleValue = new UInt32Value();
break;
// property name: ShapeId
case 3091:
simpleValue = new UInt32Value();
break;
// property name: AdvanceAfterTime
case 3152:
simpleValue = new StringValue();
break;
// property name: ShapeId
case 3155:
simpleValue = new UInt32Value();
break;
default:
Debug.Assert(false);
break;
}
return simpleValue;
}
public static Font CreateFont(
Fonts fonts,
DoubleValue fontSize,
UInt32Value color,
StringValue fontName,
Int32Value fontFamilyNumbering,
Int32Value fontCharSet,
bool isBold,
bool isItalic,
bool isUnderline,
FontSchemeValues? fontScheme = null)
{
var font = new Font();
font.Append(new FontSize { Val = fontSize });
font.Append(new Color { Theme = color });
font.Append(new FontName { Val = fontName });
font.Append(new FontFamilyNumbering { Val = fontFamilyNumbering });
font.Append(new FontCharSet { Val = fontCharSet });
if (fontScheme.HasValue)
{
font.Append(new FontScheme { Val = fontScheme.Value });
}
if (isBold)
{
font.Append(new Bold());
}
if (isItalic)
{
font.Append(new Italic());
}
if (isUnderline)
{
font.Append(new Underline());
}
fonts.Append(font);
return font;
}
private static DocumentFormat.OpenXml.Spreadsheet.Cell OpenXML_CreateNumber(int nColumnIndex, int nRowIndex, string sText, UInt32Value styleId)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.CellReference = OpenXML_GetColumnName(nColumnIndex) + nRowIndex.ToString();
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(sText);
cell.StyleIndex = styleId;
return cell;
}
public BorderProperties(BorderStyleValues? borderStyle, UInt32Value color)
{
this.borderStyle = borderStyle;
this.color = color;
}
public TableRow GenerateTableRow(CellProps[] cellProps, UInt32Value height)
{
TableRow tableRow1 = new TableRow();
TableRowProperties tableRowProperties1 = generateTableRowProperties(height);
tableRow1.Append(tableRowProperties1);
foreach (CellProps cp in cellProps) {
if (cp.span == 0) continue;
TableCell tableCell1 = new TableCell();
TableCellProperties tableCellProperties1 = new TableCellProperties();
TableCellBorders tableCellBorders1 = generateTableCellBordersPlain();
Shading shading1 = new Shading() { Val = ShadingPatternValues.Clear, Color = "auto", Fill = "auto" };
NoWrap noWrap1 = new NoWrap();
TableCellVerticalAlignment tableCellVerticalAlignment1 = new TableCellVerticalAlignment() { Val = TableVerticalAlignmentValues.Center };
HideMark hideMark1 = new HideMark();
if (cp.span > 1) {
GridSpan span = new GridSpan() { Val = (Int32Value)cp.span };
tableCellProperties1.Append(span);
}
tableCellProperties1.Append(tableCellBorders1);
tableCellProperties1.Append(shading1);
tableCellProperties1.Append(noWrap1);
tableCellProperties1.Append(tableCellVerticalAlignment1);
tableCellProperties1.Append(hideMark1);
Paragraph paragraph1 = new Paragraph();
ParagraphProperties paragraphProperties1 = new ParagraphProperties();
Justification justification1 = new Justification() { Val = cp.align };
ParagraphMarkRunProperties paragraphMarkRunProperties1 = new ParagraphMarkRunProperties();
FontSize fontSize1 = new FontSize() { Val = "18" };
FontSizeComplexScript fontSizeComplexScript1 = new FontSizeComplexScript() { Val = "18" };
paragraphMarkRunProperties1.Append(fontSize1);
paragraphMarkRunProperties1.Append(fontSizeComplexScript1);
paragraphProperties1.Append(justification1);
paragraphProperties1.Append(paragraphMarkRunProperties1);
Run run1 = new Run();
RunProperties runProperties1 = new RunProperties();
FontSize fontSize2 = new FontSize() { Val = "18" };
FontSizeComplexScript fontSizeComplexScript2 = new FontSizeComplexScript() { Val = "18" };
runProperties1.Append(fontSize2);
runProperties1.Append(fontSizeComplexScript2);
run1.Append(runProperties1);
if (cp.text != null) {
Text text1 = new Text();
text1.Text = cp.text;
run1.Append(text1);
}
paragraph1.Append(paragraphProperties1);
paragraph1.Append(run1);
tableCell1.Append(tableCellProperties1);
tableCell1.Append(paragraph1);
tableRow1.Append(tableCell1);
}
return tableRow1;
}
private UInt32Value GetNextDrawingId()
{
var drawingId = new UInt32Value((uint)1);
foreach (var drawing in _mainDocumentPart.Document.Body.Descendants<Drawing>())
{
if (drawing.Inline != null && drawing.Inline.DocProperties.Id > drawingId)
{
drawingId = drawing.Inline.DocProperties.Id;
}
}
return drawingId > 1 ? ++drawingId : drawingId;
}
//
// Write or Update a cell value of the given worksheet
//
public bool upDateValue(string sheetName, string addr, string Value, UInt32Value styleIndex, bool isString)
{
bool update = false;
//
// Seek the worksheet into the workbook, if found return true else return false
//
Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
if (sheet != null)
{
//
// If found select the worksheet and insert the cell at the given address
//
Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
Cell cell = insertCellInWorkSheet(ws, addr);
//
// If content is a string inser the cell value and update ShareStringTable
// else insert it as number
//
if (isString)
{
int stringIndex = insertSharedStringItem(wbPart, Value);
cell.CellValue = new CellValue(stringIndex.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(Value);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
// Update style
if (styleIndex > 0) cell.StyleIndex = styleIndex;
//
// Save the WorkSheet
//
ws.Save();
update = true;
}
return update;
}
