none
Restore original approach RRS feed

  • Question

  • Hi,
    Further to this thread, how to restore my original codes to only output to one Excel file (as to this thread, Fei did adjust the approach to output to several other Excel file, totally different from my original approach)?


    Many Thanks & Best Regards, Hua Min

    Thursday, June 8, 2017 9:16 AM

Answers

  • Hi,

    I have run your project, but, have you check my above code? You still use Append code instead of my above code.

    It's sad that you need a fish instead of fishing skill.

    Okey, please check below fish.

            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 refRow = null;
                    foreach (Row r in sheetData.Elements<Row>())
                    {
                        if (r.RowIndex> rowIndex)
                        {
                            refRow = r;
                            break;
                        }
                    }
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.InsertBefore(row, refRow);
    
                }
                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
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    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;
                }
    
            }
    

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Monday, July 3, 2017 7:52 AM
    Thursday, June 29, 2017 7:12 AM

All replies

  • Hi,

    After checking previous thread, I assume the reason why Fei suggests to split it into many workbook is that generating so many worksheets in the same workbook causes unexpected result which will make the workbook broken. And it is hard to identity which element causes workbook broken.

    I have some questions about your issue, could you help to identify?

    1. There are many txt files in your original post, could you share us how did you generate them? I assume you did not write them manually.

    2. It seems you want to convert these txt files into one workbook, when will you decide to convert to the second workbook? I assume your txt files will increase again and again, I am afraid one day will reach the bottleneck.

    3. How long will it cause to convert current txt files to workbook? I think it is not efficient to read and write line by line?

    Could you share us your business logic? Maybe we can try to think out any other approach for your requirement.

    In addition, what is your scenario? Did you develop in Asp.net project or just winform? Could you share us why you did not use Excel Interop?

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 9, 2017 7:06 AM
  • Hi Edward,
    1. I only want to see the way to write to Excel file, per given details, like the Text files.
    2. I need the way to be able to write to Excel file, per given files.
    3. Want to achieve point (2) in an efficient way.

    I want to be able to create the Excel file in WinForm process using OpenXML. More details to Excel Interop?

    BTW, Celeste is very good in OpenXML and did ever help a lot to such problem. Can I know if she can further help to this?


    Many Thanks & Best Regards, Hua Min



    Friday, June 9, 2017 7:39 AM
  • Hi Celeste,

    In other previous threads (related to OpenXML), you did help a lot! But now I want to know why one corrupt Excel has been created (to this thread). But for many days, I still could not get one “fine” resolution from Fei! Could you please help to such threads, if available?


    Many Thanks & Best Regards, Hua Min



    Friday, June 9, 2017 9:42 AM
  • Hi HuaMin,
    After checking your txt file, I found the issue is caused by that we define the row Index by hardcode. We defined that we must append the row one by one.
    For provided txt files, it will read the txt files by outpu1.txt, output10.txt and output2.txt. The row index in output10.txt is 10495, and the row index in output2.txt is 495. The row from output10 will be inserted before output2, and cause the row in a wrong order.
    To follow the right steps to generate excel file, I would suggest you check the shared string and then write cells one by one, and I suggest you follow How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, June 12, 2017 9:49 AM
    Moderator
  • Hello,

    For the project Fei provided, using test(filePath+ txtfilePath.Replace(txtfilePath, "Output.xlsx").Replace(@".\Files\",""), txtfilePath, ErrMsg);  could generate all the worksheets in the same workbook, but you would still get a corrupt Excel file as your previous thread.

    The cause is that we did not check the row index and insert the row to specific location when we append rows. The project reads output10.txt after output1.txt, so the rows from 10495 to 11744 are appended before row 495 to row 10494, so Excel would repair the file and remove all the rows from 495 to the end.

    If you use the project and provide these txt file from 1 to 10 one by one and check its output workbook each time, the final output workbook is fine. All the rows are inserted in sequential order. 

    So to fix the issue, we need to follow How to: Insert text into a cell in a spreadsheet document (Open XML SDK) to insert rows.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 13, 2017 7:03 AM
    Moderator
  • Hi Celeste,
    Thanks a lot to you.
    You mean the order to update the problem would lead to problem and which codes is having such ordering issue (in my current project above)?

    Many Thanks & Best Regards, Hua Min

    Tuesday, June 13, 2017 7:51 AM
  • Hello,

    The below code which appends the cells directly causes this issue.

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

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 14, 2017 8:34 AM
    Moderator
  • Hi Celeste,
    Can you please show with more details to adjust the problem?

    Many Thanks & Best Regards, Hua Min

    Wednesday, June 14, 2017 8:41 AM
  • Hi,

    Before adjusting the problem, I think you need to understand the root cause of this issue, otherwise you will fall into this issue next time and still do not know why.

    Have you know why this issue happened per to my above explanation?

    If not, please feel free to let me know, I am glad to help you understand the root cause, and guide you to find the solution.

    Do not be afraid to debug your code.

    Best Regards,

    Edward 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 15, 2017 2:00 AM
    Moderator
  • Yes, the order like, 

    1, 10, 2

    would definitely lead to the problem, right, as numerically, the order should be 

    1, 2, 3...,9, 10

    ? Can you please advise the details to adjust it?


    Many Thanks & Best Regards, Hua Min


    Thursday, June 15, 2017 4:16 AM
  • Hi,

    You have got the point, but it is a little different from root cause. The order of files which will be read should not be an problem. Right? We should not restrict the txt files be our required order.

    So, we need to change our code instead of the txt files order. 

    For a possible solution, you could follow below link to insert rows instead of append rows directly.

    #How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    https://msdn.microsoft.com/en-us/library/office/cc861607.aspx?f=255&MSPPError=-2147217396

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 15, 2017 9:45 AM
    Moderator
  • Hi Celeste,
    Good day.
    I need some (additional) advice to adjust these
                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;
                }


    per current issue you mentioned! Is it only a problem to append in above?


    Many Thanks & Best Regards, Hua Min


    Friday, June 16, 2017 8:03 AM
  • Hello,

    Have you checked the link in my above thread? 

    The link is much helpful and could resolve your issue, it shares insert text into a cell in a spreadsheet document in a detailed description, but I assume you have not read it even through I shared so many times.

    Never mind, if you have any issue with this link, please let me know. 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 16, 2017 8:56 AM
    Moderator
  • Celeste,
    To my codes now, there is no foreach loop, while it is EXISTING in your given link, like
                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
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    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;
                }
                


    Could you please point out exactly the details to adjust in my project?

    Many Thanks & Best Regards, Hua Min

    Friday, June 16, 2017 10:22 AM
  • Hi,

    Do you have any issue when using above code in your project?

    Please feel free to let us know your test and your issue.

    In addition, I will guide you how to debug and resolve your issue instead of sharing you the exactly code solution. It's better to teach fishing than to offer fish. Do you agree? 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, June 19, 2017 2:45 AM
    Moderator
  • Problem is only with the corrupt Excel file created.

    Per your current advice, to the wrong order like 1, 10, 2, that would lead to the corrupt file, I only need more details to this!

    If you are sure, this part

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

    is leading to corrupt Excel file created, can I have more details to adjust such part, to avoid the problem?


    Many Thanks & Best Regards, Hua Min


    Monday, June 19, 2017 4:28 AM
  • Hi,

    >>to the wrong order like 1, 10, 2, that would lead to the corrupt file,

    This is caused by Cells must be in sequential order according to CellReference. You could reproduce this issue by one text file.

    Below will work.

    ^&*SN^&*Sheet1^&*CO^&*A^&*RO^&*1^&*IT^&*EMAIL
    ^&*SI^&*1
    ^&*FS^&*10
    ^&*SN^&*Sheet1^&*CO^&*A^&*RO^&*2^&*IT^&*EMAIL^&*DT^&*s
    ^&*SI^&*1
    ^&*FS^&*10

    Below will fail.

    ^&*SN^&*Sheet1^&*CO^&*A^&*RO^&*2^&*IT^&*EMAIL
    ^&*SI^&*1
    ^&*FS^&*10
    ^&*SN^&*Sheet1^&*CO^&*A^&*RO^&*1^&*IT^&*EMAIL^&*DT^&*s
    ^&*SI^&*1
    ^&*FS^&*10 

    The only difference is row index.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 19, 2017 8:07 AM
    Moderator
  • How do you adjust the part of codes, to avoid this problem?

    Many Thanks & Best Regards, Hua Min

    Monday, June 19, 2017 10:09 AM
  • Have you reproduced this issue with above test data?

    Have you known the reason why you got this error?

    Before resolving this issue, I think you need to understand why this happened, and then you will not get this issue again next time.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 20, 2017 3:28 AM
    Moderator
  • >> Have you reproduced this issue with above test data?
    Yes, I do get exactly the Corrupt Excel file

    >> Have you known the reason why you got this error?
    Per your advice, you mentioned the part of codes (that are leading to the problem), right? Why can't you directly answer to my question? It is bad for you to keep on asking me questions while I showed my question clearly.

    Many Thanks & Best Regards, Hua Min


    Tuesday, June 20, 2017 3:54 AM
  • I have shared you the solution, and you could find the solution from below link.

    https://msdn.microsoft.com/en-us/library/office/cc861607.aspx?f=255&MSPPError=-2147217396

    Do you have any trouble to read this document?

    >>It is bad for you to keep on asking me questions while I showed my question clearly.

    You know your question, but you do not understand why you got this issue. I am trying my best to help you, and I will keep helping you.

    Any problem please feel free to let me know.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 21, 2017 3:07 AM
    Moderator
  • >> You know your question, but you do not understand why you got this issue. I am trying my best to help you, and I will keep helping you.
    I have difficulty in adjusting the current part of codes, to avoid creating corrupt Excel file. It is NO USE that you again and again mention such URL, as I need hints with little bit more details to adjust the part!

    How to avoid it is updating, per order like 1, 10, 2, 3 but use order like 1, 2, 3, ..., 9, 10, to the following?
                if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                ...
    
                if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
                ...
    


    Many Thanks & Best Regards, Hua Min


    Wednesday, June 21, 2017 4:15 AM
  • Hi,

    Have you tried below code to insert cells instead of appending cells directly?

    Did you get any issue with below code?

    // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
            // If the cell already exists, returns it. 
            private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
            {
                Worksheet worksheet = worksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                string cellReference = columnName + rowIndex;
    
                // If the worksheet does not contain a row with the specified row index, insert one.
                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 there is not a cell with the specified column name, insert one.  
                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
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    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;
                }
            }

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 22, 2017 2:02 AM
  • Thanks Edward for update.

    Celeste,
    Did we ever talk about that Insert would lead to slow speed (comparing with Append) in other previous threads? Do you remember this?

    Edward,

    To your current question, here is the event from 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;
                }
            }
    


    Many Thanks & Best Regards, Hua Min


    Thursday, June 22, 2017 3:24 AM
  • Hi,

    Have you resolved your issue? If not, what is wrong with above code?

    You may need to share us more information about your issue.

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 23, 2017 2:42 AM
  • I want to know where I can adjust, to this part of code

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


    to avoid the Corrupt Excel file being created, to this project, per Celeste's advice in above.


    Many Thanks & Best Regards, Hua Min


    Friday, June 23, 2017 3:10 AM
  • Hi,

    You could check your project code to find the function which you used to insert cells, and then replace this function with current code.

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 26, 2017 2:01 AM
  • The current project WAS USING such codes!

    Many Thanks & Best Regards, Hua Min

    Monday, June 26, 2017 3:38 PM
  • Is there any issue with current code?

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 27, 2017 2:23 AM
  • Corrupt Excel file was created (the current event of part of codes being used).

    Many Thanks & Best Regards, Hua Min

    Tuesday, June 27, 2017 2:37 AM
  • How did you use the code?

    I made a test with below code from the document, it works for me.

    // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
            // If the cell already exists, returns it. 
            private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
            {
                Worksheet worksheet = worksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                string cellReference = columnName + rowIndex;
    
                // If the worksheet does not contain a row with the specified row index, insert one.
                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 there is not a cell with the specified column name, insert one.  
                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
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    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;
                }
            }
    

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 27, 2017 9:38 AM
  • Edward,

    I still get Corrupt file using your codes in above.

    Celeste,

    >> You know your question, but you do not understand why you got this issue. I am trying my best to help you, and I will keep helping you.

    Did you check the current case. It is now using event "InsertCellInWorksheetFun" inside.


    Many Thanks & Best Regards, Hua Min


    Wednesday, June 28, 2017 4:49 AM
  • Hi,

    What do you mean by "InsertCellInWorksheetFun"?

    How did you try "InsertCellInWorksheet"?

    Did you get corrupt file with below data?

    ^&*SN^&*Sheet1^&*CO^&*A^&*RO^&*1^&*IT^&*EMAIL
    ^&*SI^&*1
    ^&*FS^&*10
    ^&*SN^&*Sheet1^&*CO^&*A^&*RO^&*2^&*IT^&*EMAIL^&*DT^&*s
    ^&*SI^&*1
    ^&*FS^&*10

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 28, 2017 8:45 AM
  • Hi,
    Did you ever run the project? If you run it, you need to press CreateDoc button to create Excel file and the button is further calling 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;
                }
    
            }



    Many Thanks & Best Regards, Hua Min

    Wednesday, June 28, 2017 9:37 AM
  • Hi,

    I have run your project, but, have you check my above code? You still use Append code instead of my above code.

    It's sad that you need a fish instead of fishing skill.

    Okey, please check below fish.

            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 refRow = null;
                    foreach (Row r in sheetData.Elements<Row>())
                    {
                        if (r.RowIndex> rowIndex)
                        {
                            refRow = r;
                            break;
                        }
                    }
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.InsertBefore(row, refRow);
    
                }
                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
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    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;
                }
    
            }
    

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Monday, July 3, 2017 7:52 AM
    Thursday, June 29, 2017 7:12 AM