none
Slow process RRS feed

  • Question

  • Hi,
    Further to this, as I'm using the same codes to write Excel file, one problem is, it is still slow to write to relevant Excel file. Any advice to this?

    Many Thanks & Best Regards, Hua Min

    Friday, January 27, 2017 5:11 AM

Answers

  • The foreach is used to avoid adding duplicated value. We could skip the checking.

    Please see the updated code below. You could use variable rowIndex to decide we insert the value to which row.

      SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    
                    int rowIndex = 2; //add to row 2
                    SheetData sheetData = new SheetData();
                    Row row = new Row() { RowIndex = Convert.ToUInt32(rowIndex) }; // row index RowIndex = (UInt32Value)2U
                    SharedStringTablePart shareStringPart = workbookpart.AddNewPart<SharedStringTablePart>();
                    SharedStringTable sharedStringTable =shareStringPart.SharedStringTable = new SharedStringTable();
                    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);
                        sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(line)));
                        Cell cell = new Cell() { CellReference = ConvertToLetter(counter) + rowIndex, DataType = CellValues.SharedString };  
                        cell.CellValue = new CellValue((counter-1).ToString()); // counter-1 because sharedStringTable starts from 0.
                        row.Append(cell);
                        counter++;
                    }
                        sheetData.Append(row);
    
                        worksheetPart.Worksheet = new Worksheet(sheetData);
    
                        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                            AppendChild<Sheets>(new Sheets());
                        Sheet sheet = new Sheet()
                        {
                            Id = spreadsheetDocument.WorkbookPart.
                            GetIdOfPart(worksheetPart),
                            SheetId = 1,
                            Name = sheetName
                        };
                        sheets.Append(sheet);
                        workbookpart.Workbook.Save();
                        spreadsheetDocument.Close();


    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 6, 2017 5:12 AM
    • Unmarked as answer by Jackson_1990 Tuesday, February 7, 2017 8:24 AM
    • Marked as answer by Jackson_1990 Wednesday, February 8, 2017 7:16 AM
    Wednesday, February 1, 2017 8:49 AM
    Moderator

All replies

  • Hi,

    To create and write files with large data, I think slow process could not be avoided by using Open XML library or Office Interop.



    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 30, 2017 5:49 AM
    Moderator
  • Comparing with Office Interop, use Open XML library is quicker. The problem is you want to write 1080143 cells. To speed up as possible as you could, I suggest you run the project step by step and write few cells. You could check which line is not necessary.

    Another suggestion is that the logic of the current project is: create the new spreadsheet, close it, then open the new spreadsheet, insert the cells. The following part of inserting cells in an existing file would cause slow process because it loops all existing cells and then insert it into the new location.

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

    You could try code below. It creates a new workbook with a new worksheet and a new row.

     SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    
                    SheetData sheetData = new SheetData();
                    Row row = new Row(); // row 1
                    SharedStringTablePart shareStringPart = 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);
                        Cell cell = new Cell() { CellReference = ConvertToLetter(counter) +"1", DataType = CellValues.SharedString }; // CellReference should be A1,B1,C1
                        cell.CellValue = new CellValue(index.ToString());
                        row.Append(cell);
                        counter++;
                    }
                        sheetData.Append(row);
    
                        worksheetPart.Worksheet = new Worksheet(sheetData);
    
                        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                            AppendChild<Sheets>(new Sheets());
                        Sheet sheet = new Sheet()
                        {
                            Id = spreadsheetDocument.WorkbookPart.
                            GetIdOfPart(worksheetPart),
                            SheetId = 1,
                            Name = sheetName
                        };
                        sheets.Append(sheet);
                        workbookpart.Workbook.Save();
                        spreadsheetDocument.Close();

    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, January 31, 2017 8:17 AM
    Moderator
  • Many thanks.

    In your codes, you're having this

    int index = InsertSharedStringItem(line, shareStringPart);

    while within InsertCellInWorksheet of my codes (that is having foreach line and can be reason to slowness), I do not know how I can get such index as I was passing relevant column name and row index to update!

    Many Thanks & Best Regards, Hua Min

    Tuesday, January 31, 2017 1:48 PM
  • The foreach is used to avoid adding duplicated value. We could skip the checking.

    Please see the updated code below. You could use variable rowIndex to decide we insert the value to which row.

      SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    
                    int rowIndex = 2; //add to row 2
                    SheetData sheetData = new SheetData();
                    Row row = new Row() { RowIndex = Convert.ToUInt32(rowIndex) }; // row index RowIndex = (UInt32Value)2U
                    SharedStringTablePart shareStringPart = workbookpart.AddNewPart<SharedStringTablePart>();
                    SharedStringTable sharedStringTable =shareStringPart.SharedStringTable = new SharedStringTable();
                    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);
                        sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(line)));
                        Cell cell = new Cell() { CellReference = ConvertToLetter(counter) + rowIndex, DataType = CellValues.SharedString };  
                        cell.CellValue = new CellValue((counter-1).ToString()); // counter-1 because sharedStringTable starts from 0.
                        row.Append(cell);
                        counter++;
                    }
                        sheetData.Append(row);
    
                        worksheetPart.Worksheet = new Worksheet(sheetData);
    
                        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                            AppendChild<Sheets>(new Sheets());
                        Sheet sheet = new Sheet()
                        {
                            Id = spreadsheetDocument.WorkbookPart.
                            GetIdOfPart(worksheetPart),
                            SheetId = 1,
                            Name = sheetName
                        };
                        sheets.Append(sheet);
                        workbookpart.Workbook.Save();
                        spreadsheetDocument.Close();


    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 6, 2017 5:12 AM
    • Unmarked as answer by Jackson_1990 Tuesday, February 7, 2017 8:24 AM
    • Marked as answer by Jackson_1990 Wednesday, February 8, 2017 7:16 AM
    Wednesday, February 1, 2017 8:49 AM
    Moderator
  • Within InsertCellInWorksheet, I did not read from one Text file. Can you please advise what value should be to "line" below?

    sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(line)));

    Many Thanks & Best Regards, Hua Min


    • Edited by Jackson_1990 Wednesday, February 1, 2017 9:55 AM
    Wednesday, February 1, 2017 9:48 AM
  • It seems that I didn't clarify clearly.

    My suggestion is to create the whole document at the same time instead of creating it firstly and then inserting the cell.

    InsertCellInWorksheet is more suitable for inserting cell into an existing file because we could specify the location.

    To insert cell when creating the new file, we could simply use  row.Append(cell);  instread of

      row.InsertBefore(newCell, refCell);

    So, please create a new project and test the code in my previous post. The code above creates a new workbook with data from a text file. 


    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 2, 2017 2:27 AM
    Moderator
  • Would it be feasible to use InsertCellInWorksheet to EACH TIME write to one CELL ONLY? (Please note that I DID NOT read from Text file within InsertCellInWorksheet)

    My preference is to adjust InsertCellInWorksheet to make it do the similar like your CODES (but without reading from Text file).


    Many Thanks & Best Regards, Hua Min





    Thursday, February 2, 2017 4:23 AM
  • Lets focus on original issue. If you change your requirement, I suggest you post new threads. Please check if the updated code would speed up for your file.


    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 2, 2017 5:36 AM
    Moderator
  • Sorry, in your example (to this thread):

    1.        You're reading from one Text file, and can you show your Text file?
    2.        Is one Text file line for one Excel row, or not?

    Many Thanks & Best Regards, Hua Min

    Tuesday, February 7, 2017 8:26 AM
  • The text looks like

    The result:

    rowIndex determines row. The index in the txt determines the column index and index of SharedStringItem in SharedStringTable.

    so there is no need to hard code all the index value if we read from 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.

    Wednesday, February 8, 2017 2:57 AM
    Moderator
  • You are reading from file (line by line) which is for 'cell to cell' in Excel file, right? How do you know it's the end of Excel row?

    Many Thanks & Best Regards, Hua Min

    Wednesday, February 8, 2017 3:59 AM
  • I don't understand why we need to know it is the end of excel row. And there is no need to do that.

    We append the cell node to row node, then excel recognizes the cell. So how many cells we append, how many cells excel shows. I am referring to the cells with value. We could see these empty cells when opening the sheet in Excel application, but they do not exist in the open xml format.

    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.


    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, February 8, 2017 4:26 AM
    Moderator
  • For instance, I expect to "know" also which row or column per relevant line (of Text file) and then to "directly" write to specific cell of Excel file. Can I?

    Many Thanks & Best Regards, Hua Min

    Wednesday, February 8, 2017 4:52 AM
  • Do you mean that you have written the cell reference and cell value in the txt, and then want to insert then into sheet?  If I misunderstand, please give a sample. Besides, if you have new issues, please post new threads. Do not discuss several different issues in one thread.

     


    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, February 8, 2017 5:16 AM
    Moderator