none
Better way to work with XML file RRS feed

  • Question

  • Hi,

    Further to this thread, it is slow to use InsertText to each time write to one cell with values. Must we ensure we each time do write to one whole Excel row to have better performance, for writing to an OPENXML file?



    Many Thanks & Best Regards, Hua Min

    Friday, January 20, 2017 5:10 AM

Answers

  • >>Should I write all these values into one row and then append row to Excel file.

    Yes. You need to write 20 cells and append them to one row then append this row to excel like the code in my first post.

     

    The following is the sample code to insert a row according to the txt.

    Please create a new project to test the code below to avoid cross using.

    In .txt, I write

    Then use code below, it generates:

    Its XML nodes:

       static void InsertRowFun(string docName, string sheetName,uint rowIndex)
            {
                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 counter = 1;
                    string line;
                    System.IO.StreamReader file = new System.IO.StreamReader(@"D:\row.txt");
                    while ((line = file.ReadLine()) != null)
                    {
                        int index = InsertSharedStringItem(line, shareStringPart);                             
                    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                    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);
    
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);
    
                    Cell cell = InsertCellInWorksheet(ConvertToLetter(counter), rowIndex, worksheetPart);
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    worksheetPart.Worksheet.Save();
                        counter++;
                    }
                    file.Close();
    
                }
            }
            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;
                }
            }
         public static string ConvertToLetter(int iCol)
            {
                string functionReturnValue = null;
                int iAlpha = 0;
                int iRemainder = 0;
                iAlpha = iCol / 27;
                iRemainder = iCol - (iAlpha * 26);
                if (iAlpha > 0)
                {
                    functionReturnValue =((char)(iAlpha + 64)).ToString();
                }
                if (iRemainder > 0)
                {
                    functionReturnValue = functionReturnValue + ((char)(iRemainder + 64)).ToString();
                }
                return functionReturnValue;
            }


    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, January 25, 2017 1:53 AM
    Moderator

All replies

  • Hi,

    This is by design. We need to write each cell.

    A better way might look like

     Row row1 = new Row()
                Cell cell1 = new Cell(){ CellReference = "A1" };
                CellValue cellValue1 = new CellValue();
                cellValue1.Text = "1";
                cell1.Append(cellValue1);
     
                Cell cell2 = new Cell(){ CellReference = "O1" };
                CellValue cellValue2 = new CellValue();
                cellValue2.Text = "0";
     
                cell2.Append(cellValue2);
     
                row1.Append(cell1);
                row1.Append(cell2);


    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, January 20, 2017 7:38 AM
    Moderator
  • Hi Celeste,
    Do you mean we have to write to XML file row by row, if we have filled one row wholly with all cell values inside?

    Many Thanks & Best Regards, Hua Min


    Friday, January 20, 2017 7:56 AM
  • Yes

    We have to write each cell and each row. So if you have large data, it might be slow.


    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, January 20, 2017 8:44 AM
    Moderator
  • Hi,

    Can you show also the way to each time write one row as a whole into the XML file?


    Many Thanks & Best Regards, Hua Min


    Friday, January 20, 2017 9:27 AM
  • Hi,

    Do you want to write the same value in the same row? How many columns do you want to write?

    If you don't want to write the same value, where do you want to get the value?


    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, January 23, 2017 10:46 AM
    Moderator
  • Hi,

    For instance, I have about 20 cell values on one row. Should I write all these values into one row and then append row to Excel file. Is there an example to this?


    Many Thanks & Best Regards, Hua Min

    Tuesday, January 24, 2017 9:02 AM
  • >>Should I write all these values into one row and then append row to Excel file.

    Yes. You need to write 20 cells and append them to one row then append this row to excel like the code in my first post.

     

    The following is the sample code to insert a row according to the txt.

    Please create a new project to test the code below to avoid cross using.

    In .txt, I write

    Then use code below, it generates:

    Its XML nodes:

       static void InsertRowFun(string docName, string sheetName,uint rowIndex)
            {
                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 counter = 1;
                    string line;
                    System.IO.StreamReader file = new System.IO.StreamReader(@"D:\row.txt");
                    while ((line = file.ReadLine()) != null)
                    {
                        int index = InsertSharedStringItem(line, shareStringPart);                             
                    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                    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);
    
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);
    
                    Cell cell = InsertCellInWorksheet(ConvertToLetter(counter), rowIndex, worksheetPart);
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    worksheetPart.Worksheet.Save();
                        counter++;
                    }
                    file.Close();
    
                }
            }
            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;
                }
            }
         public static string ConvertToLetter(int iCol)
            {
                string functionReturnValue = null;
                int iAlpha = 0;
                int iRemainder = 0;
                iAlpha = iCol / 27;
                iRemainder = iCol - (iAlpha * 26);
                if (iAlpha > 0)
                {
                    functionReturnValue =((char)(iAlpha + 64)).ToString();
                }
                if (iRemainder > 0)
                {
                    functionReturnValue = functionReturnValue + ((char)(iRemainder + 64)).ToString();
                }
                return functionReturnValue;
            }


    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, January 25, 2017 1:53 AM
    Moderator
  • Hi Celeste,
    In your example, do you read line from Text file, to further match with cell in the Excel file?

    Many Thanks & Best Regards, Hua Min

    Wednesday, January 25, 2017 4:13 AM
  • If you don't want to read from text file, you need to hard code like:

    int index1 = InsertSharedStringItem("text1", shareStringPart); Cell cell1 = InsertCellInWorksheet("A", rowIndex, worksheetPart); cell1.CellValue = new CellValue(index1.ToString()); cell1.DataType = new EnumValue<CellValues>(CellValues.SharedString); int index2 = InsertSharedStringItem("text2", shareStringPart); Cell cell2 = InsertCellInWorksheet("B", rowIndex, worksheetPart); cell2.CellValue = new CellValue(index2.ToString()); cell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);




    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, January 25, 2017 4:54 AM
    Moderator
  • I know you each time read one value from Text file but you do not read relevant cell position from the text file.

    Many Thanks & Best Regards, Hua Min

    Wednesday, January 25, 2017 5:17 AM
  • The line index is the column index of the cell.

    I am using function ConvertToLetter to convert the line index into column name.

    test1 is in line 1, so I convert it to column A and then use InsertCellInWorksheet function to insert the cell. test2 is in line 2, so I convert it to column B and then it is inserted into B1.

    If you want to hard code the column, you could see the code in my previous post.

    Does the code work for you? Could you get the same result showing in the picture?

    If you don't know how to adjust for your requirement or you want another method to read the cell value and cell reference, I suggest you post a new thread and clarify your requirement.


    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, January 25, 2017 5:36 AM
    Moderator
  • Can I see your sample file? One concern is, you're actually each time taking one cell value only from Text file, right?

    Many Thanks & Best Regards, Hua Min

    Wednesday, January 25, 2017 9:31 AM
  • Please download the sample project from: https://1drv.ms/u/s!AkcxDWH1nFmJpQMScnSMJlhRtKNy

    >>One concern is, you're actually each time taking one cell value only from Text file, right?

    Yes, it is the only way to insert cell value using Open XML library.

    Sheetdata is made up of rows and rows consist of cells. To write data for one row, we need to write each cell firstly.


    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 26, 2017 5:17 AM
    Moderator