Eric White's Blog

Hi,

I am not using any Kind of markup file .I want to write into excel file in which I already created List Validation. I crated List and the end of the File

and when I am exporting datat to these excel it is removing option from dropdown .

public string GetFormatedExcel(string sourceFile, string destinationFile, string fileName, DataTable table, List<string> columns, int templateId)
{
System.IO.File.Copy(sourceFile, destinationFile, true);

Package spreadsheetPackage = Package.Open(destinationFile, FileMode.Open, FileAccess.ReadWrite);

using (var document = SpreadsheetDocument.Open(spreadsheetPackage))
{
var workbookPart = document.WorkbookPart;
var workbook = workbookPart.Workbook;
var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
Worksheet ws = ((WorksheetPart)(workbookPart.GetPartById(sheet.Id))).Worksheet;
SheetData sheetData = ws.GetFirstChild<SheetData>();
if (sheet == null)
throw new Exception(“No sheed found in the template file. Please add the sheet”);

int rowIndex = 2;//default

Row row;
rowIndex =2;
uint rowInd =2;

// List<Column> cols = ws.Descendants<Column>().ToList();

foreach (System.Data.DataRow dsrow in table.Rows)
{

row = new Row();
foreach (string col in columns)
{
Cell cell = new Cell();

cell.DataType = CellValues.String;
// cell.StyleIndex = 2;
cell.CellValue = new CellValue(dsrow[col].ToString());

row.Append(cell);

}
row.RowIndex = new UInt32Value(rowInd);
sheetData.InsertAt(row, rowIndex);

rowIndex++;
rowInd++;
}
ws.Save();
workbookPart.Workbook.Save();
document.Close();

}

this is my code snipped and I am not using any other markup file.As I am not that much expertise in OPenXML If possible Please share something which will help me to add List validations through c# code.