Problem to create file

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Hi,
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;
            }
        }
        ...

Open in new window

there would be corrupt Excel file generated, when I run the project against these files
https://1drv.ms/u/s!Ai8CrEskdewXlEGrqvoPUVMD6z4T
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dirk StraussSenior Full Stack Developer
Distinguished Expert 2017

Commented:
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.com/blogs/how-to-create-excel-file-development-using-epplus-net-library-c-sharp
Top Expert 2016

Commented:
your code would fail if the cellReference was not found in the row.Elements:

                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;

Open in new window


row.InsertBefore might go wrong if the 'refCell' was not assigned with a valid cell.

Sara
HuaMin ChenProblem resolver

Author

Commented:
Sara, how to adjust it to avoid problem?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
you should check cellReference before insert:

           if (cellReference != null)
           {
                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);
                worksheet.Save();
                return newCell;
          }
          return null;

Open in new window


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
HuaMin ChenProblem resolver

Author

Commented:
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;
            }

        }
        

Open in new window

and Corrupt Excel file is created. Why?
Top Expert 2016
Commented:
actually i have no expertise with Excel (including automation) .... also my c# knowledge is only basic.

so actually any statement where you make a change to the current worksheet might  go wrong. it even could be that the worksheet passed into the function already was corrupt by a previous operation or  because it is a null object ...

to find out what goes wrong you should save the worksheet after a statement that could go wrong and copy the file after save to a new name. you would begin after the first statement where a copy of the worksheet was created and probably use the File.Copy method with arguments sourceFileName, destFileName, and boolean overwrite = true. run your program and check whether the copied file is also corrupt. if yes, the issue already happened before calling the above function. if the copied file is not corrupt, you would move the save and copy statements down, probaly after 'sheetData.Append(row);'. and so on until you find the wrong statement.

another thing you could do is to add a try-catch to your functions such that you would know if some of your statements would throw an exception.

Sara
HuaMin ChenProblem resolver

Author

Commented:
Still many thanks Sara. To this part

            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }

Open in new window

           
does it try to see if the row not existing, then it would point to first row in the list?
Top Expert 2016

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial