none
Write with position RRS feed

  • Question

  • Hi,

    Further to this thread, is there any demonstration to also put RowID, ColumnID, to InsertText event?


    Many Thanks & Best Regards, Hua Min

    Tuesday, January 17, 2017 4:12 AM

Answers

  • Hi,

    I think you want to specify the cell reference when insetting text in a sheet.

    Usage:  InsertText(@"D:\test\new.xlsx", "Sheet1", "B", 2,  "test");

       public static void InsertText(string docName, string sheetName, string columnName, uint rowIndex, string text)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
                    SharedStringTablePart shareStringPart;
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
                    int index = InsertSharedStringItem(text, shareStringPart);
                    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                    if (sheets.Count() == 0)
                    {
                        return;
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);
                    Cell cell = InsertCellInWorksheet(columnName, rowIndex, worksheetPart);
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    worksheetPart.Worksheet.Save();
                }
            }
         private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
    
                int i = 0;
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }
    
                    i++;
                }
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
                return i;
            }
    
       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;
                }
            }
    


    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 Thursday, January 19, 2017 10:10 AM
    Wednesday, January 18, 2017 4:48 AM
    Moderator

All replies

  • Hi,

    I think you want to specify the cell reference when insetting text in a sheet.

    Usage:  InsertText(@"D:\test\new.xlsx", "Sheet1", "B", 2,  "test");

       public static void InsertText(string docName, string sheetName, string columnName, uint rowIndex, string text)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
                    SharedStringTablePart shareStringPart;
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
                    int index = InsertSharedStringItem(text, shareStringPart);
                    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                    if (sheets.Count() == 0)
                    {
                        return;
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);
                    Cell cell = InsertCellInWorksheet(columnName, rowIndex, worksheetPart);
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    worksheetPart.Worksheet.Save();
                }
            }
         private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
    
                int i = 0;
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }
    
                    i++;
                }
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
                return i;
            }
    
       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;
                }
            }
    


    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 Thursday, January 19, 2017 10:10 AM
    Wednesday, January 18, 2017 4:48 AM
    Moderator
  • Hi,

    I suggest you manually create a new workbook and a new project and test the code above.

    If it works for the new workbook, I suggest you post a new thread and share your current code and your file with us.

    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, January 19, 2017 8:09 AM
    Moderator
  • Hi Celeste,
    We pass sheetName to InsertText event right? If such Worksheet does not exist within the Excel file, can the Worksheet be created? Can you please adjust your way?

    Many Thanks & Best Regards, Hua Min

    Thursday, January 19, 2017 9:37 AM
  • Hi,

    Change

        if (sheets.Count() == 0)
                    {
                        return;
                    }
    

    Into 

       if (sheets.Count() == 0)
                    {
                        WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                        newWorksheetPart.Worksheet = new Worksheet(new SheetData());
    
                        Sheets newsheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                        string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);
                        uint sheetId = 1;
                        if (newsheets.Elements<Sheet>().Count() > 0)
                        {
                            sheetId = newsheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }
                        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                        newsheets.Append(sheet);
    
                    }
    Then it would insert the text in to specific cell of existent or nonexistent sheet. 


    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 Thursday, January 19, 2017 10:10 AM
    • Unmarked as answer by Jackson_1990 Thursday, January 19, 2017 10:10 AM
    Thursday, January 19, 2017 9:50 AM
    Moderator