static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, DataTable dataTable)
{
var worksheet = new Worksheet();
var sheetData = new SheetData();
ExportDataTable(sheetData, dataTable);
worksheet.Append(sheetData);
if (dataTable.TableName != "Дата")
{
var worksheetExtensionList = new WorksheetExtensionList();
var worksheetExtension = new WorksheetExtension {
Uri = WorksheetExtensionUri
};
var dataValidations = new X14.DataValidations {
Count = (UInt32Value)1U
};
var dataValidation = new X14.DataValidation {
Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true
};
var dataValidationForumla1 = new X14.DataValidationForumla1();
var formula = new Excel.Formula {
Text = "Дата!$B$2:$B$4"
};
dataValidationForumla1.Append(formula);
var referenceSequence = new Excel.ReferenceSequence {
Text = "F2:F4"
};
dataValidation.Append(dataValidationForumla1);
dataValidation.Append(referenceSequence);
dataValidations.Append(dataValidation);
worksheetExtension.Append(dataValidations);
worksheetExtensionList.Append(worksheetExtension);
worksheet.Append(worksheetExtensionList);
}
worksheetPart.Worksheet = worksheet;
}
public override void WriteOpenXml(OpenXmlWriter writer)
{
X14.ConditionalFormatting cf = new X14.ConditionalFormatting();
cf.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main");
ReferenceSequence referenceSequence = new ReferenceSequence();
referenceSequence.Text = $"{SpreadsheetHelper.ExcelColumnFromNumber(RangeStart.ColumnIndex)}{RangeStart.RowIndex}:{SpreadsheetHelper.ExcelColumnFromNumber(RangeEnd.ColumnIndex)}{RangeEnd.RowIndex}";
X14.ConditionalFormattingRule conditionalFormattingRule = new X14.ConditionalFormattingRule()
{
Type = ConditionalFormatValues.IconSet, Priority = 1, Id = Guid.NewGuid().ToString("B").ToUpper()
};
X14.IconSet iconSet1 = new X14.IconSet()
{
IconSetTypes = new EnumValue <IconSetTypeValues>(IconSetTypeValues.FourTrafficLights), ShowValue = false, Custom = true
};
//ConditionalFormattingIcon
var conditionalFormattingIcon1 = new X14.ConditionalFormattingIcon()
{
IconSet = X14.IconSetTypeValues.NoIcons, IconId = (UInt32Value)0U
};
var conditionalFormattingIcon2 = new X14.ConditionalFormattingIcon()
{
IconSet = X14.IconSetTypeValues.ThreeSymbols2, IconId = (UInt32Value)2U
};
var conditionalFormattingIcon3 = new X14.ConditionalFormattingIcon()
{
IconSet = X14.IconSetTypeValues.ThreeSymbols, IconId = (UInt32Value)1U
};
var conditionalFormattingIcon4 = new X14.ConditionalFormattingIcon()
{
IconSet = X14.IconSetTypeValues.ThreeSymbols, IconId = (UInt32Value)0U
};
X14.ConditionalFormattingValueObject conditionalFormattingValueObject1 = new X14.ConditionalFormattingValueObject()
{
Type = X14.ConditionalFormattingValueObjectTypeValues.Numeric
};
Formula formula1 = new Formula();
formula1.Text = "0";
conditionalFormattingValueObject1.Append(formula1);
X14.ConditionalFormattingValueObject conditionalFormattingValueObject2 = new X14.ConditionalFormattingValueObject()
{
Type = X14.ConditionalFormattingValueObjectTypeValues.Numeric
};
Formula formula2 = new Formula();
formula2.Text = "1";
conditionalFormattingValueObject2.Append(formula2);
X14.ConditionalFormattingValueObject conditionalFormattingValueObject3 = new X14.ConditionalFormattingValueObject()
{
Type = X14.ConditionalFormattingValueObjectTypeValues.Numeric
};
Formula formula3 = new Formula();
formula3.Text = "2";
conditionalFormattingValueObject3.Append(formula3);
X14.ConditionalFormattingValueObject conditionalFormattingValueObject4 = new X14.ConditionalFormattingValueObject()
{
Type = X14.ConditionalFormattingValueObjectTypeValues.Numeric
};
Formula formula4 = new Formula();
formula4.Text = "3";
conditionalFormattingValueObject4.Append(formula4);
iconSet1.Append(
conditionalFormattingValueObject1
, conditionalFormattingValueObject2
, conditionalFormattingValueObject3
, conditionalFormattingValueObject4
, conditionalFormattingIcon1
, conditionalFormattingIcon2
, conditionalFormattingIcon3
, conditionalFormattingIcon4
);
conditionalFormattingRule.Append(iconSet1);
cf.Append(conditionalFormattingRule);
cf.Append(referenceSequence);
writer.WriteElement(cf);
}
private void CreateResourcesWorksheet(Dictionary <string, object[]> fieldDictionary, DataTable dataTable,
WorkbookPart workbookPart, Dictionary <string, int> sharedStringIndices)
{
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1");
var worksheet = new Worksheet(new SheetProperties {
CodeName = "Sheet1"
},
new SheetDimension
{
Reference =
string.Format("A1:{0}{1}",
GetColId(fieldDictionary.Count - 1),
dataTable.Rows.Count + 2)
}, new SheetViews(
new SheetView(
new Pane
{
VerticalSplit = 1D,
TopLeftCell = "A3",
ActivePane = PaneValues.BottomLeft,
State = PaneStateValues.Frozen
},
new Selection
{
ActiveCell = "A2",
SequenceOfReferences =
new ListValue <StringValue> {
InnerText = "A2"
}
},
new Selection {
Pane = PaneValues.BottomLeft
})
{
TabSelected = true,
TopLeftCell = "A2",
WorkbookViewId = (UInt32Value)0U
}))
{
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", R_SCHEMA);
worksheet.AddNamespaceDeclaration("mc", MC_SCHEMA);
worksheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA);
var columns = new Columns();
UInt32Value colIndex = 1U;
foreach (var pair in fieldDictionary)
{
var column = new Column
{
Min = colIndex,
Max = colIndex,
Width = 9.140625D,
Style = 1U,
BestFit = true,
CustomWidth = true
};
if (pair.Key.Equals("ID") || pair.Key.Equals("Title"))
{
column.Style = 5U;
}
if (pair.Key.Equals("SharePointAccount") && new Act(_spWeb).IsOnline)
{
column.Hidden = true;
}
columns.AppendChild(column);
colIndex++;
}
columns.AppendChild(new Column {
Min = colIndex, Max = 16384U, Width = 9.140625D, Style = 1U
});
var sheetData = new SheetData();
for (UInt32Value i = 1; i <= dataTable.Rows.Count + 2; i++)
{
var row = new Row {
RowIndex = i
};
if (i == 1)
{
row.Hidden = true;
}
else if (i == 2)
{
row.StyleIndex = 4U;
row.CustomFormat = true;
}
var rowId = (int)(i - 1);
for (int j = 0; j < fieldDictionary.Count; j++)
{
var cellValue = new CellValue();
KeyValuePair <string, object[]> pair = fieldDictionary.ElementAt(j);
object oValue = null;
string value = string.Empty;
if (rowId > 1)
{
oValue = dataTable.Rows[rowId - 2][pair.Key];
if (pair.Key.Equals("ResourceLevel"))
{
foreach (var l in ((IList <string>)pair.Value[2])
.Select(level => level.Split('|'))
.Where(l => l[0].Equals(oValue.ToString())))
{
oValue = l[1];
break;
}
}
value = oValue.ToString();
}
switch (rowId)
{
case 0:
cellValue.Text = sharedStringIndices[pair.Key].ToString(CultureInfo.InvariantCulture);
break;
case 1:
cellValue.Text =
sharedStringIndices[pair.Value[0].ToString()].ToString(CultureInfo.InvariantCulture);
break;
default:
cellValue.Text = sharedStringIndices[value].ToString(CultureInfo.InvariantCulture);
break;
}
var cell = new Cell {
CellReference = GetColId(j) + i, StyleIndex = 1U
};
if (rowId > 1)
{
switch ((SPFieldType)pair.Value[1])
{
case SPFieldType.Counter:
case SPFieldType.Integer:
case SPFieldType.Number:
cellValue.Text = value;
break;
case SPFieldType.DateTime:
cell.StyleIndex = 3U;
cellValue.Text = oValue != null && oValue != DBNull.Value
? ((DateTime)oValue).ToOADate()
.ToString(CultureInfo.InvariantCulture)
: null;
break;
case SPFieldType.Currency:
cell.StyleIndex = 2U;
cellValue.Text = value;
break;
case SPFieldType.Boolean:
cell.DataType = CellValues.Boolean;
cellValue.Text = value;
break;
default:
cell.DataType = CellValues.SharedString;
break;
}
switch (pair.Key)
{
case "ID":
case "Title":
case "Generic":
case "SharePointAccount":
case "Email":
cell.StyleIndex = 5U;
break;
}
}
else
{
cell.DataType = CellValues.SharedString;
}
if (i == 2)
{
cell.StyleIndex = 4U;
}
cell.AppendChild(cellValue);
row.AppendChild(cell);
}
sheetData.AppendChild(row);
}
var worksheetExtensionList = new WorksheetExtensionList();
var worksheetExtension = new WorksheetExtension {
Uri = "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}"
};
worksheetExtension.AddNamespaceDeclaration("x14", X14_SCHEMA);
var dataValidations = new X14.DataValidations {
Count = 0U
};
dataValidations.AddNamespaceDeclaration("xm", XM_SCHEMA);
for (int i = 0; i < fieldDictionary.Count; i++)
{
string colId = GetColId(i);
KeyValuePair <string, object[]> pair = fieldDictionary.ElementAt(i);
var spFieldType = (SPFieldType)pair.Value[1];
if (spFieldType != SPFieldType.Boolean && spFieldType != SPFieldType.Lookup &&
spFieldType != SPFieldType.User && spFieldType != SPFieldType.Choice)
{
continue;
}
if (pair.Key.Equals("SharePointAccount"))
{
continue;
}
if ((bool)pair.Value[3])
{
continue;
}
dataValidations.Count++;
var dataValidation = new X14.DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
ShowInputMessage = true,
ShowErrorMessage = true
};
string formulaValue = string.Empty;
switch (spFieldType)
{
case SPFieldType.Boolean:
formulaValue = "BValues!$A$1:$A$2";
break;
case SPFieldType.Lookup:
case SPFieldType.Choice:
int count = ((List <string>)pair.Value[2]).Count();
formulaValue = pair.Key.Replace("_x0020_", "__") + "Values!$A$1" +
(count > 1 ? ":$A$" + count : string.Empty);
break;
case SPFieldType.User:
formulaValue = "DNValues!$D:$D";
break;
}
var dataValidationForumla = new X14.DataValidationForumla1();
var formula = new Formula {
Text = formulaValue
};
dataValidationForumla.AppendChild(formula);
var refSeq = new ReferenceSequence {
Text = string.Format("{0}3:{0}1048576", colId)
};
dataValidation.Append(dataValidationForumla, refSeq);
dataValidations.AppendChild(dataValidation);
}
worksheetExtension.AppendChild(dataValidations);
worksheetExtensionList.AppendChild(worksheetExtension);
worksheet.Append(columns, sheetData, worksheetExtensionList);
worksheetPart.Worksheet = worksheet;
}