none
Problem to event RRS feed

  • Question

  • Hi,
    Further to this, how about that I DID NOT read from one Text file within InsertCellInWorksheet, by which I was to write to one cell each time?

    Many Thanks & Best Regards, Hua Min

    Monday, February 6, 2017 5:17 AM

Answers

  • Hi,

    We could not add cells like

     Cell cell = new Cell() { CellReference = "B1", DataType = CellValues.SharedString };
                    Cell cell2 = new Cell() { CellReference = "A1", DataType = CellValues.SharedString };
                    row.Append(cell);
                    row.Append(cell2);
    

    We must append A1 before B1.

    So if you want to create a separated function to add cell, we must loop all the cells and insert the cell into correct location like current function InsertCellInWorksheet.

    So if you don’t want to read the text from a txt, you must hard code like:

       SheetData sheetData = new SheetData();
                    Row row = new Row() { RowIndex = 1 }; 
                    SharedStringTablePart shareStringPart = workbookpart.AddNewPart<SharedStringTablePart>();
                    SharedStringTable sharedStringTable = shareStringPart.SharedStringTable = new SharedStringTable();
                    Cell cell = new Cell() { CellReference = "A1", DataType = CellValues.SharedString };
                    Cell cell2 = new Cell() { CellReference = "B1", DataType = CellValues.SharedString };
    
                    SharedStringItem sharedStringItem1 = new SharedStringItem();
                    sharedStringItem1.Append(new DocumentFormat.OpenXml.Spreadsheet.Text("Test1"));                  
                    shareStringPart.SharedStringTable.AppendChild(sharedStringItem1);
    
                    SharedStringItem sharedStringItem2 = new SharedStringItem();
                    sharedStringItem2.Append(new DocumentFormat.OpenXml.Spreadsheet.Text("Test2"));
                    shareStringPart.SharedStringTable.AppendChild(sharedStringItem2);
    
                    cell.CellValue = new CellValue("0");//index of SharedStringItem starts from 0
                    cell2.CellValue = new CellValue("1");
    
                    row.Append(cell);
                    row.Append(cell2);
                    sheetData.Append(row);
    



    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, February 13, 2017 9:55 AM
    Tuesday, February 7, 2017 5:14 AM
    Moderator

All replies

  • Hi,

    We could not add cells like

     Cell cell = new Cell() { CellReference = "B1", DataType = CellValues.SharedString };
                    Cell cell2 = new Cell() { CellReference = "A1", DataType = CellValues.SharedString };
                    row.Append(cell);
                    row.Append(cell2);
    

    We must append A1 before B1.

    So if you want to create a separated function to add cell, we must loop all the cells and insert the cell into correct location like current function InsertCellInWorksheet.

    So if you don’t want to read the text from a txt, you must hard code like:

       SheetData sheetData = new SheetData();
                    Row row = new Row() { RowIndex = 1 }; 
                    SharedStringTablePart shareStringPart = workbookpart.AddNewPart<SharedStringTablePart>();
                    SharedStringTable sharedStringTable = shareStringPart.SharedStringTable = new SharedStringTable();
                    Cell cell = new Cell() { CellReference = "A1", DataType = CellValues.SharedString };
                    Cell cell2 = new Cell() { CellReference = "B1", DataType = CellValues.SharedString };
    
                    SharedStringItem sharedStringItem1 = new SharedStringItem();
                    sharedStringItem1.Append(new DocumentFormat.OpenXml.Spreadsheet.Text("Test1"));                  
                    shareStringPart.SharedStringTable.AppendChild(sharedStringItem1);
    
                    SharedStringItem sharedStringItem2 = new SharedStringItem();
                    sharedStringItem2.Append(new DocumentFormat.OpenXml.Spreadsheet.Text("Test2"));
                    shareStringPart.SharedStringTable.AppendChild(sharedStringItem2);
    
                    cell.CellValue = new CellValue("0");//index of SharedStringItem starts from 0
                    cell2.CellValue = new CellValue("1");
    
                    row.Append(cell);
                    row.Append(cell2);
                    sheetData.Append(row);
    



    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, February 13, 2017 9:55 AM
    Tuesday, February 7, 2017 5:14 AM
    Moderator
  • Hi,
    As I did mention on the original thread, I do want to write to Excel file per given "specific" column name and Row ID. This is also I need to event InsertCellInWorksheet. Here is one sample file, inside which, 1st field is sheet name, 2nd field column name, 3rd field row ID and last field cell value.


    Many Thanks & Best Regards, Hua Min



    • Edited by Jackson_1990 Wednesday, February 8, 2017 8:26 AM
    Wednesday, February 8, 2017 7:23 AM
  • >>I do want to write to Excel file per given "specific" column name and Row ID.

    I think you want to create a workbook and insert data including sheet name, cell reference and cell value from a text file. You need to make sure the cells reference in your text file meets the criterion like the list are A1,B1,A2,B2. If there is no rule, we need to loop all cells to meet validation, which would cause slow process.

    I have checked your file and try to create a demo. But the issue is a little complicated, I did not finish the whole project now, so let us split the issue into several small ones. And I suggest you post threads for the part you could not finish.

    1)How to split the string in the text for further use. Meaning how to get sheet name, column name, row index and cell value.

    2)read sheet name from text file, return worksheet part according to sheet name, if it doesn't exist, create one.

    3)How to insert the new cell according to the txt.


    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, February 9, 2017 9:50 AM
    Moderator
  • >> If there is no rule, we need to loop all cells to meet validation, which would cause slow process.
    Is this a must and we HAVE TO loop all cells to have all MATCH?

    Many Thanks & Best Regards, Hua Min

    Thursday, February 9, 2017 10:06 AM
  • Yes.

    If the list are B1,A1 and we insert the cell B1 and then insert cell A1 using row.Append, the sheet is not valid.


    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, February 9, 2017 10:16 AM
    Moderator
  • From previous thread
    >> for example, I input value into cell A1, the xml of the sheetdata should have one row node with one cell node. Create a sheet and check its xml, you would understand my point.

    Does it mean we have to match for every cell by a loop, and no other way? 


    Many Thanks & Best Regards, Hua Min

    Friday, February 10, 2017 1:43 AM
  • Yes. There is no other way.


    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, February 10, 2017 1:46 AM
    Moderator
  • >> If there is no rule, we need to loop all cells to meet validation, which would cause slow process.
    What rule do you expect to have? Would it be better, if we order (by all cells) all details of change that we expect to apply to Excel/XML file?

    Many Thanks & Best Regards, Hua Min

    Friday, February 10, 2017 7:56 AM
  • The rule depends on you. But the cell reference should be in order.

    Cell reference should look like A1, B1, C1.  It is invalid if they are A1, C1, B1.


    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, February 10, 2017 8:48 AM
    Moderator
  • Hi,
    >> I have checked your file and try to create a demo. But the issue is a little complicated, I did not finish the whole project now, 

    When do you expect to make this available?


    Many Thanks & Best Regards, Hua Min

    Monday, February 13, 2017 9:03 AM
  • Hi,

    I suggest you post different threads for the part you could not finish.

    1)How to split the string in the text for further use. Meaning how to get sheet name, column name, row index and cell value.

    2)read sheet name from text file, return worksheet part according to sheet name, if it doesn't exist, create one.

    3)How to insert the new cell according to the txt.

    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, February 13, 2017 9:12 AM
    Moderator