static void Main(string[] args)
{
ExcelOperations.CreatingExcelAndDrowownInExcel();
}
public static void UpdateCell(string docName, string text,
uint rowIndex, string columnName)
{
//Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
//worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
//worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
//worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
//Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
//worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
//worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
//worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Worksheet worksheet1 = new Worksheet();
ExcelOperations ex = new ExcelOperations();
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
{
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "DropDownContainingSheet");
if (worksheetPart != null)
{
worksheet1 = worksheetPart.Worksheet;
SheetData sheetData = new SheetData();
SheetData sheetData1 = new SheetData();
int Counter1 = 1;
foreach (var value in DataInSheet.GetDataOfSheet1())
{
Row contentRow = ExcelOperations.CreateRowValues(Counter1, value);
Counter1++;
sheetData.AppendChild(contentRow);
}
worksheet1.Append(sheetData);
int Counter2 = 1;
//foreach (var value in DataInSheet.GetDataOfSheet2())
//{
// Row contentRow = ExcelOprations.CreateRowValues(Counter2, value);
// Counter2++;
// sheetData1.AppendChild(contentRow);
//}
//worksheet2.Append(sheetData1);
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = "A1"
},
Formula1 = new Formula1("'DropDownDataContainingSheet'!$B$1:$B$5")
};
DataValidations dataValidations = worksheet1.GetFirstChild <DataValidations>();
if (dataValidations != null)
{
dataValidations.Count = dataValidations.Count + 1;
dataValidations.Append(dataValidation);
}
else
{
DataValidations newdataValidations = new DataValidations();
newdataValidations.Append(dataValidation);
newdataValidations.Count = 1;
worksheet1.Append(newdataValidations);
}
Cell cell = GetCell(worksheetPart.Worksheet,
columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType =
new EnumValue <CellValues>(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.Append(worksheet1);
// worksheetPart.Worksheet=(worksheet2);
worksheetPart.Worksheet.Save();
}
}
}
