Issue while Exporting excel template

[maheshParticipant]

Hi ,
I am getting issue while generating excel sheet with existing template.

and after generating i am getting like this

Template formatting is not exactly preserved while generating new excel sheet.

[Eric WhiteKeymaster]

Hi,

Sorry, I’m not clear on what you are using for Excel templates.

There appears to be some issue with your markup such that you are getting two blank entries in the drop down. I would look to see if they were actually the same, or is one of those some type of invisible character, so you have some cells that are truly blank, and other cells that contain an invisible character, and this would result in getting the strange drop down list.

Cheers, Eric

[maheshParticipant]

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.

[Eric WhiteKeymaster]

I don’t have any existing samples that shows how to accomplish what you want. I do have examples that generate spreadsheets, specifically the streaming example that enables creating huge spreadsheets.

Screen-Cast: Using Open XML and LINQ to XML in a Streaming Fashion to Create Huge Spreadsheets

That example focuses on the streaming approach, with a minimalist approach to formatting. Controlling drop downs lists could be thought of as an advanced form of formatting.

I have often wanted to put far more effort into SpreadsheetML, but due to the massive demand for tools / process for WordprocessingML, to date I have not had the opportunity to do so. I know that there is demand to be able to generate spreadsheets in a far easier fashion, and controlling the drop down lists is an interesting and important scenario.

Cheers, Eric

[Author]

Posts