Problem to create file
Hi,
Using these codes (in the project),
https://1drv.ms/u/s!Ai8CrEskdewXlEGrqvoPUVMD6z4T
Using these codes (in the project),
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
Row row;
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
...https://1drv.ms/u/s!Ai8CrEskdewXlEGrqvoPUVMD6z4T
Dirk Strauss
What are you using to create the excel object? Have you had a look at using EPPlus? It's an excellent dll to use and available via NuGet if I'm not mistaken. Here is a short tutorial on creating an excel document using EPPlus: http://www.c-sharpcorner.c
your code would fail if the cellReference was not found in the row.Elements:
row.InsertBefore might go wrong if the 'refCell' was not assigned with a valid cell.
Sara
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;row.InsertBefore might go wrong if the 'refCell' was not assigned with a valid cell.
Sara
ASKER
Sara, how to adjust it to avoid problem?
you should check cellReference before insert:
but i don't know whether this will solve the issue. is the calling function able to handle a null return? is your sheet able to handle a new empty row at all?
Sara
if (cellReference != null)
{
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
return null;but i don't know whether this will solve the issue. is the calling function able to handle a null return? is your sheet able to handle a new empty row at all?
Sara
ASKER
Very sorry Sara, the original question is unclear. It is actually using this event
private static Cell InsertCellInWorksheetFun(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
Row row;
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
Cell cell = new Cell() { CellReference = cellReference };
row.Append(cell);
worksheet.Save();
return cell;
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Still many thanks Sara. To this part
does it try to see if the row not existing, then it would point to first row in the list?
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}does it try to see if the row not existing, then it would point to first row in the list?
i would say that if the result set of the query 'where r.RowIndex = rowIndex' is not empty, it takes the first row which is in this result set. a query of a specific row index can't find more than one row. so, the first row actually is the row which has been searched for.
Sara
Sara