none
Problem to Excel file generated RRS feed

  • Question

  • Hi,
    Further to this, using these codes

            public static void createsheetFromTXT(string filepath,string txtfilePath,string ErrMsg)
            {
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
    
                SharedStringTablePart shareStringPart = workbookpart.AddNewPart<SharedStringTablePart>();
                SharedStringTable sharedStringTable = shareStringPart.SharedStringTable = new SharedStringTable();
    
                int counter = 1;
                string line;
                System.IO.StreamReader file = new System.IO.StreamReader(txtfilePath);
                while ((line = file.ReadLine()) != null)
                {
                    string[] words = line.Split(new string[] { "^&*" }, System.StringSplitOptions.RemoveEmptyEntries);
    
                    if (words.Length > 1)
                    {
                        ErrMsg = ValSheetName(words[1]);
    
                        if (ErrMsg == "" && words[1] == "SN")
                        {
                            WorksheetPart worksheetPart = InsertWorksheetCus(workbookpart, words[1]);
                            if (words.Length == 4)
                            {
                                Cell cell = InsertCellInWorksheetFun(words[3], Convert.ToUInt32(words[5]), worksheetPart);
                                int index = InsertSharedStringItemFun(words[7], shareStringPart);
                                cell.CellValue = new CellValue(index.ToString());
                                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                            }
                        }
                    }
                    counter++;
                }                     
                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();
            }
    
            private static bool isValid(String value)
            {
                var matches = Regex.Matches(value, @"^[A-Za-z_-][A-Za-z0-9_-]*$");
                return (matches.Count > 0);
            }
    
            private static string ValSheetName(string worksheetName)
            {
                if (worksheetName.Trim() == "")
                    return "Sheet name cannot be empty!";
    
                if (worksheetName.Trim().Length > 31)
                    return "Worksheet name cannot have more than 31 characters.";
    
                if (!isValid(worksheetName))
                    return "You should have only alphanumeric characters with either _ or - to the Worksheet name.";
    
                List<string> List0 = new List<string>(); List0.Add("Database"); List0.Add("Criteria"); List0.Add("Extract"); List0.Add("Print_Area"); List0.Add("Print_Titles");
    
                bool b = List0.Any(worksheetName.Contains);
                if (b)
                    return "Worksheet name cannot be one value of these: Database, Criteria, Extract, Print_Area or Print_Titles.";
    
                return "";
            }


    I still get this Excel file having problem inside.


    Many Thanks & Best Regards, Hua Min

    Thursday, April 13, 2017 7:40 AM

Answers

  • Hi HuaMin,

    Sorry for the confusion the link for the project is incorrect, please try download the project from this link. And let me know if you still have the problem.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 5, 2017 6:12 AM
    Moderator

All replies

  • Hi HuaMin  Chen,

    try to provide your txt file.

    I will try to test your code.

    I can see previous thread.

    did you able to create the file successfully without corruption after that answer?

    if yes, please mentioned what changes you had made after that?

    if you are not able to create file successfully after that answer then you should not need to create a new thread because the same issue is still exist so no need to create duplicate thread for that and try to continue the conversation on previous thread.

    Regards

    Deepak


    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, April 14, 2017 9:11 AM
    Moderator
  • Hi,

    I had small change to the above as shown. You can advise why the current codes lead to corrupt Excel file.


    Many Thanks & Best Regards, Hua Min

    Friday, April 14, 2017 1:01 PM
  • Hi HuaMin  Chen,

    you had mentioned that , you did a small change.

    you did not mentioned which change?

    also last time I request you to provide your text file.

    so that I can try to test the code.

    without that text file how can I make a test.

    Regards

    Deepak


    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, April 18, 2017 7:36 AM
    Moderator
  • Hi,
    Here is the file


    Many Thanks & Best Regards, Hua Min

    Tuesday, April 18, 2017 8:59 AM
  • Hi HuaMin  Chen,

    I try to test the code and find that you did not provide the necessary code to run the code.

    following functions are missing in the code.

    (1) InsertWorksheetCus()
    
    (2) InsertCellInWorksheetFun()
    
    (3) InsertSharedStringItemFun()
    
    provide the code of above functions.

    Regards

    Deepak


    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, April 18, 2017 9:21 AM
    Moderator
  • Hi,

    Please refer to these

            private static WorksheetPart InsertWorksheetCus(WorkbookPart workbookPart, string worksheetName)
            {
                Sheets sheet = workbookPart.Workbook.GetFirstChild<Sheets>();
                if (sheet == null)
                {
                    sheet = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                }
                IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
                if (sheets.Count() == 0)
                {
                    WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    newWorksheetPart.Worksheet = new Worksheet(new SheetData());
    
                    Sheets newsheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                    string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
                    uint sheetId = 1;
                    if (newsheets.Elements<Sheet>().Count() > 0)
                    {
                        sheetId = newsheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }
                    Sheet newsheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = worksheetName };
                    newsheets.Append(newsheet);
                    return newWorksheetPart;
                }
                WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheets.First().Id);
                return worksheetPart;
            }
            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;
                }
    
            }
            static int InsertSharedStringItemFun(string text, SharedStringTablePart shareStringPart)
            {
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
                int i = shareStringPart.SharedStringTable.Elements<SharedStringItem>().Count();
                if (i == 0)
                {
                    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                    return i;
                }
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
                return i;
            }
    


    Many Thanks & Best Regards, Hua Min

    Tuesday, April 18, 2017 9:37 AM
  • Hi HuaMin  Chen,

    I test your code and I find that you are creating workbook but you are not following the process to create the workbook successfully.

    to create the workbook successfully you need to follow the code below.

     public static void CreateSpreadsheetWorkbook(string filepath)
            {
                // Create a spreadsheet document by supplying the filepath.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                    Create(filepath, SpreadsheetDocumentType.Workbook);
    
    
                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
    
              
    
                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());
    
                // Add Sheets to the Workbook.
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<Sheets>(new Sheets());
              
                // Append a new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = "mySheet"
                };
                sheets.Append(sheet);
    
                workbookpart.Workbook.Save();
    
                // Close the document.
                spreadsheetDocument.Close();
            }
    

    you can compare your code with the code above.

    you will find that you are just creating the workbook and add a workbook part and then you try to execute your code.

    so here workbook get corrupted.

    so you first need to create workbook then add worksheet and then add data.

    if you see your code.

    after creating workbook , you try to open text file.

    then your text file contains incorrect data which are not in proper format.

    so then after it goes to the validation function and then it not create worksheet.

    I put the break point on InsertCellInWorksheetFun() but it never get executed.

    then after finish the loop execution you save and close the workbook.

    so the code for the sheet and other 2 function did not get executed.

    if it not get execute then workbook will get corrupted.

    so hope now you are clear about why your workbook get corrupted.

    you can also try to test your code and debug it by yourself to verify the fact.

    Regards

    Deepak


    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, April 19, 2017 1:43 AM
    Moderator
  • Hi,

    Why do you remove sheetName parameter to event CreateSpreadsheetWorkbook?


    Many Thanks & Best Regards, Hua Min

    Wednesday, April 19, 2017 4:20 AM
  • Hi HuaMin  Chen,

    I did not remove anything from your code.

    the code I posted above is from MSDN Website.

    Reference:

    How to: Create a spreadsheet document by providing a file name (Open XML SDK)

    I show you what is the issue here.

    compare the execution of this code with your code. you will notice what is happening there that cause the issue.

    Regards

    Deepak


    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, April 19, 2017 5:03 AM
    Moderator
  • Why is there one difference, as I have one sheetName parameter to event CreateSpreadsheetWorkbook? Here is the event I have

            public static void CreateSpreadsheetWorkbook(string filepath, string sheetName)
            {
                if (System.IO.File.Exists(filepath))
                {
                    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, true))
                    {
                        WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
    
                        //Worksheet worksheet1 = new Worksheet(new SheetData()) { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                        //worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                        //worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                        //worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                        //worksheet1.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                        //newWorksheetPart.Worksheet = worksheet1;
    
                        newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                        string relationshipId =
                            spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);
                        uint sheetId = 1;
                        if (sheets.Elements<Sheet>().Count() > 0)
                        {
                            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }
                        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                        sheets.Append(sheet);
                    }
                }
                else
                {
                    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                            
                    //Worksheet worksheet1 = new Worksheet(new SheetData()) { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                    //worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                    //worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                    //worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                    //worksheet1.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                    //worksheetPart.Worksheet = worksheet1;
    
    
                    //string columnName;
                    //uint rowIndex;
                    //columnName = "A";
                    //rowIndex = 1;
                    //string cellReference = columnName + rowIndex;
                    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");
                    //cell2.CellValue = new CellValue("1");
    
                    //row.Append(cell);
                    //row.Append(cell2);
                    //sheetData.Append(row);
    
                    //int counter = 1;
                    //string line;
                    //System.IO.StreamReader file = new System.IO.StreamReader(@"C:\Users\v-chencl\Desktop\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 }; // "2" refer to row 2, should be same as the RowIndex 
                    //    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);
    
                    int counter = 1;
                    string line;
                    System.IO.StreamReader file = new System.IO.StreamReader(@"C:\Users\v-chencl\Downloads\7c.txt");
                    while ((line = file.ReadLine()) != null)
                    {
                        //Console.WriteLine(line);
                        string[] words = line.Split(new string[] { "^" }, System.StringSplitOptions.RemoveEmptyEntries);
                        //int index = InsertSharedStringItem(line, shareStringPart);
                        if (words.Length==4)
                        {
                            Cell cell = InsertCellInWorksheetFun(words[1], Convert.ToUInt32(words[2]), worksheetPart);
                            int index = InsertSharedStringItemFun(words[3], shareStringPart);
                            cell.CellValue = new CellValue(index.ToString());
                            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                        }
                      
                        //foreach (string s in words)
                        //{
                        //    System.Console.WriteLine(s);
                        //}      
                        counter++;
                    }
    
                    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();
                }
            }
    


    Many Thanks & Best Regards, Hua Min


    Wednesday, April 19, 2017 6:20 AM
  • Hi HuaMin  Chen,

    you had mentioned that,"Why is there one difference, as I have one sheetName parameter to event CreateSpreadsheetWorkbook? Here is the event I have"

    your code is customized code as per your requirement.

    it is not an event. it is function.

    you want to call this function from other function and at that time you want to pass the sheet name as parameter that's why there is an parameter in your function for sheet name.

    you can remove that parameter if you want.

    then after you need to pass the sheet name where this parameter is used in code.

    Regards

    Deepak


    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, April 19, 2017 9:23 AM
    Moderator
  • Here is the event being used
            public static void CreateSpreadsheetWorkbook(string filepath, string sheetName)
            {
                // Create a spreadsheet document by supplying the filepath.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                    Create(filepath, SpreadsheetDocumentType.Workbook);
    
    
                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
    
    
    
                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());
    
                // Add Sheets to the Workbook.
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<Sheets>(new Sheets());
    
                // Append a new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = sheetName
                };
                sheets.Append(sheet);
    
                workbookpart.Workbook.Save();
    
                // Close the document.
                spreadsheetDocument.Close();
            }


    but I still get this corrupt file. Can Celeste also help to this problem?



    Many Thanks & Best Regards, Hua Min


    Thursday, April 20, 2017 1:58 AM
  • Hi HuaMin  Chen,

    I already give a reason why your file get corrupted.

    I don't know why you posted the above method because you did not use that in the original code.

    The answer is straight and forward. as I suggested you need to follow the approach suggested in the documentation.

    first create workbook, add workbook part, add worksheet part, add worksheet and then try to work on data.

    your code is not following this approach and that leads to corruption of workbook.

    this is the answer for your question.

    other thing you had mentioned that,"Can Celeste also help to this problem?"

    your this requests creates conflicts here. last time same thing was happened.

    each engineer have their own threads and other engineers will not interfere in the thread of any other engineers.

    that creates a so many issues here.

    so every time it is not possible that your thread get handled by particular engineer.

    Regards

    Deepak


    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, April 20, 2017 9:27 AM
    Moderator
  • Hi,
    Can you point it out clearly which events are having difference from the approach?

    Many Thanks & Best Regards, Hua Min

    Friday, April 21, 2017 2:00 AM
  • Hi HuaMin  Chen,

    you had mentioned that,"Can you point it out clearly which events are having difference from the approach?"

    I already clearly suggested you that these are functions, not event.

    let me explain again.

    to check , you need to test both the codes.

    first you can check the code which I showed you from the MSDN Link.

    you need to put the break point and debug the code line by line.

    just notice how code get executed and notice the sequence.

    now, try to debug your code line by line.

    you will notice that your code is not following the sequence and not adding the worksheet part, worksheet and cell.

    the reason is you have invalid data in your txt file.

    so each time it is executing the validation function and returns the error.

    because of this error , it skips the execution of current loop and counter get increment by 1.

    then next round of loop get executed.

    again it returns the error and it is get repeated in every round of loop.

    so at the end after completing the loop worksheet part, worksheet and cell is not get added in your workbook.

    and you save the and close the file.

    so as a result you get corrupted workbook.

    so first of all I suggest you to correct your data in txt file.

    so that when you execute the code it not return any error for worksheet name and it go inside the if condition that will add worksheet part, worksheet and cell.

    then check that your workbook get corrupted or not.

    if still it get corrupted then after you need to check this functions which add worksheet part, worksheet and cell.

    so try to test on your side and you will get clear idea about it.

    Regards

    Deepak


    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, April 21, 2017 8:28 AM
    Moderator
  • Hi,

    NO USE for you to write so many steps! It is hard to follow!

    Other threads supported by Celeste, are NOT having so many steps. If you know which codes are HAVING WRONG APPROACH, why can't you point them out?


    Many Thanks & Best Regards, Hua Min

    Friday, April 21, 2017 8:38 AM
  • Hi HuaMin  Chen,

    first you told me to clear the thing.

    so I try to make thing more and more clear.

    still you are having an issue to understand the problem.

    it is not my fault that you are not able to understand.

    I said clearly that your code is not adding the worksheet part and worksheet.

    because of this reason workbook get corrupted.

    now , I think this is the very simple and straight reply that can give you the idea.

    I point this out many and many times , still you are not able to understand.

    did you try whatever I suggested you? tell me clearly.

    is this code written by you ? if yes then you can understand the issue easily.

    Regards

    Deepak


    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, April 21, 2017 8:46 AM
    Moderator
  • To this thread and also previous thread supported by you, you always mentioned to debug the code but why can't you point out your ideas CLEARLY? You're NOT REALLY supporting to the threads!

    Each time you did advise me to debug the codes but currently Celeste did help me close one other thread quickly by giving clear advice!


    Many Thanks & Best Regards, Hua Min


    Friday, April 21, 2017 8:51 AM
  • Hello HuaMin,

    I download your file Ex0.xlsx and it shows your code only generate a workbook part and a sharedstringtable. The rest did not execute.

    So please check the code below and whether the process could step into the code which used to insert worksheets and cells.

           while ((line = file.ReadLine()) != null)
                {
                    string[] words = line.Split(new string[] { "^&*" }, System.StringSplitOptions.RemoveEmptyEntries);
    
                    if (words.Length > 1)
                    {
                        ErrMsg = ValSheetName(words[1]);
    
                        if (ErrMsg == "" && words[1] == "SN")
                        {

    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, April 24, 2017 2:38 AM
    Moderator
  • Hi Celeste,
    Yes, I check that it does get into such loop.

    Many Thanks & Best Regards, Hua Min

    Monday, April 24, 2017 3:56 AM
  • No. It is impossible.

    Please see the picture below words[1] would never be "SN" based on your text file. It should be words[0].

    And please see the following code, if words.Length == 4, there is no words[7],  so you would get an exception. I think it should be words.Length > 7

    Besides, I am not sure how you specify the ErrMsg parameter, my test results show it would never be empty. So when testing, I let ValSheetName return empty.

         if (words.Length == 4)
                            {
                                Cell cell = InsertCellInWorksheetFun(words[3], Convert.ToUInt32(words[5]), worksheetPart);
                                int index = InsertSharedStringItemFun(words[7], shareStringPart);
                                cell.CellValue = new CellValue(index.ToString());
                                cell.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.

    Monday, April 24, 2017 4:53 AM
    Moderator
  • Yes, here is what I have
                while ((line = file.ReadLine()) != null)
                {
                    string[] words = line.Split(new string[] { "^&*" }, System.StringSplitOptions.RemoveEmptyEntries);
    
                    if (words.Length > 1)
                    {
                        ErrMsg = ValSheetName(words[1]);
                        ...



    Many Thanks & Best Regards, Hua Min

    Monday, April 24, 2017 5:09 AM
  • Hello,

    Array starts from 0, so please change words[1] == "SN" into words[0] == "SN".

    words.Length should not be 4, please change into words.Length > 7 to make sure words[3], words[5], words[7] are not null.

    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, April 24, 2017 7:39 AM
    Moderator
  • Hi,
    I did the adjustment like
                while ((line = file.ReadLine()) != null)
                {
                    string[] words = line.Split(new string[] { "^&*" }, System.StringSplitOptions.RemoveEmptyEntries);
    
                    if (words.Length > 1)
                    {
                        ErrMsg = ValSheetName(words[1]);
    
                        if (ErrMsg == "" && words[0] == "SN")
                        {
                            WorksheetPart worksheetPart = InsertWorksheetCus(workbookpart, words[1]);
                            if (words.Length >7)
                            {
                                Cell cell = InsertCellInWorksheetFun(words[3], Convert.ToUInt32(words[5]), worksheetPart);
                                int index = InsertSharedStringItemFun(words[7], shareStringPart);
                                cell.CellValue = new CellValue(index.ToString());
                                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                            }
                        }
                    }
                    ...


    but I still get this corrupt file.


    Many Thanks & Best Regards, Hua Min

    Monday, April 24, 2017 10:10 AM
  • The file is invalid because some relationships are missing. workbook.xml and workbook,xml.rels are 0 KB.

    Since your text file is too large, so I test parts of rows and the code works for me.

    I suggest you test small text files.

    If the text file is large, it is hard to debug, so I suggest you split it into several.

      string filePath = @"D:\test\subfolder\1.xlsx";
                string ErrMsg = "";
                foreach (var txtfilePath in System.IO.Directory.EnumerateFiles(@"D:\test\subfolder", "*.txt"))
                {
                    test(filePath, txtfilePath, ErrMsg);           
                }    
    
            static void test(string filepath, string txtfilePath, string ErrMsg)
            {
                SpreadsheetDocument spreadsheetDocument;
                WorkbookPart workbookpart;
                SharedStringTablePart shareStringPart;
                SharedStringTable sharedStringTable;
                if (System.IO.File.Exists(filepath))
                {
                    using (spreadsheetDocument = SpreadsheetDocument.Open(filepath, true))
                    {
                        workbookpart = spreadsheetDocument.WorkbookPart;
                        if (workbookpart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                        {
                            shareStringPart = workbookpart.GetPartsOfType<SharedStringTablePart>().First();
                            sharedStringTable = shareStringPart.SharedStringTable;
                        }
                        else
                        {
                            shareStringPart = workbookpart.AddNewPart<SharedStringTablePart>();
                            sharedStringTable = shareStringPart.SharedStringTable = new SharedStringTable();
                        }
    
                        int counter = 1;
                        string line;
                        System.IO.StreamReader file = new System.IO.StreamReader(txtfilePath);
                        while ((line = file.ReadLine()) != null)
                        {
                            string[] words = line.Split(new string[] { "^&*" }, System.StringSplitOptions.RemoveEmptyEntries);
    
                            if (words.Length > 1)
                            {
                                ErrMsg = ValSheetName(words[1]);
    
                                if (ErrMsg == "" && words[0] == "SN")
                                {
                                    WorksheetPart worksheetPart = InsertWorksheetCus(workbookpart, words[1]);
                                    if (words.Length > 7)
                                    {
                                        Cell cell = InsertCellInWorksheetFun(words[3], Convert.ToUInt32(words[5]), worksheetPart);
                                        int index = InsertSharedStringItemFun(words[7], shareStringPart);
                                        cell.CellValue = new CellValue(index.ToString());
                                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                                    }
                                }
                            }
                            counter++;
                        }
                        workbookpart.Workbook.Save();
                    }
                }
                else
                {
                    createsheetFromTXT(filepath, txtfilePath, ErrMsg);
                }
            }


    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, April 25, 2017 5:53 AM
    Moderator
  • Hi Celeste,
    I did have smaller files having 5000 lines inside, while I do not see relevant error captured in the loop below, when running against the file.

            static void test(string filepath, string txtfilePath, string ErrMsg)
            {
                SpreadsheetDocument spreadsheetDocument;
                WorkbookPart workbookpart;
                SharedStringTablePart shareStringPart;
                SharedStringTable sharedStringTable;
                if (System.IO.File.Exists(filepath))
                {
                    using (spreadsheetDocument = SpreadsheetDocument.Open(filepath, true))
                    {
                        workbookpart = spreadsheetDocument.WorkbookPart;
                        if (workbookpart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                        {
                            shareStringPart = workbookpart.GetPartsOfType<SharedStringTablePart>().First();
                            sharedStringTable = shareStringPart.SharedStringTable;
                        }
                        else
                        {
                            shareStringPart = workbookpart.AddNewPart<SharedStringTablePart>();
                            sharedStringTable = shareStringPart.SharedStringTable = new SharedStringTable();
                        }
    
                        int counter = 1;
                        string line;
                        System.IO.StreamReader file = new System.IO.StreamReader(txtfilePath);
                        while ((line = file.ReadLine()) != null)
                        {
                            string[] words = line.Split(new string[] { "^&*" }, System.StringSplitOptions.RemoveEmptyEntries);
    
                            if (words.Length > 1)
                            {
                                ErrMsg = ValSheetName(words[1]);
    
                                if (ErrMsg == "" && words[0] == "SN")
                                {
                                    WorksheetPart worksheetPart = InsertWorksheetCus(workbookpart, words[1]);
                                    if (words.Length > 7)
                                    {
                                        Cell cell = InsertCellInWorksheetFun(words[3], Convert.ToUInt32(words[5]), worksheetPart);
                                        int index = InsertSharedStringItemFun(words[7], shareStringPart);
                                        cell.CellValue = new CellValue(index.ToString());
                                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                                    }
                                }
                            }
                            counter++;
                        }
                        workbookpart.Workbook.Save();
                    }
                }
                else
                {
                    createsheetFromTXT(filepath, txtfilePath, ErrMsg);
                }
            }
            
            private void CreateDoc_Click(object sender, EventArgs e)
            {
                //CreateSpreadsheetWorkbook(@"D:\test\new.xlsx", "Sheet1");
                string Msg0="";
                string ErrMsg = "";
                string filePath = @"C:\DP13\Ex0.xlsx";
                foreach (var txtfilePath in System.IO.Directory.EnumerateFiles(@"c:\dp13\files", "*.txt"))
                {
                    test(filePath, txtfilePath, ErrMsg);
                    //MessageBox.Show(ErrMsg);
                    Msg0 = ErrMsg;
                }
                if (Msg0 == "")
                    Msg0 = "Done";
                MessageBox.Show(Msg0);
            }
    and the generated Excel file is also corrupt.


    Many Thanks & Best Regards, Hua Min


    Thursday, April 27, 2017 7:37 AM
  • Hello,

    I suggest you split large text files into small ones and test each small text file. Please check the xml of the corrupt file to narrow down your issue. Make sure each file could generate correct sheets and then combine them. Please share your current small text file.

    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, May 1, 2017 8:08 AM
    Moderator
  • Many thanks Celeste. 
    I get

    to open this Excel file, once I include this file, to process.



    Many Thanks & Best Regards, Hua Min

    Tuesday, May 2, 2017 4:05 AM
  • Hello,

    After repairing the file, I got:

    Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
    Removed Records: Cell information from /xl/worksheets/sheet5.xml part

    After comparing the files, I find Excel removes lots of duplicated item in the sharedstringtable. So we need to use the following code to insert sharedstring item.

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


    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, May 2, 2017 6:23 AM
    Moderator
  • Sorry, I put your current codes but I still have got the same issue (I posted few hours ago in above).



    Many Thanks & Best Regards, Hua Min

    Tuesday, May 2, 2017 6:52 AM
  • Sorry, I put your current codes but I still have got the same issue (I posted few hours ago in above), when opening the generated Excel file.

    Many Thanks & Best Regards, Hua Min


    Tuesday, May 2, 2017 6:55 AM
  • Hi HuaMin,

    I am tried to reproduce this issue, however the code works well for me to create a workbook. I have upload the full code sample and you can download from here.

    Please feel free to let me if you still have the problem.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 4, 2017 5:39 AM
    Moderator
  • Hi,
    Thanks a lot. I do get problem by running this. How can I send you relevant Key to extract this file?


    Many Thanks & Best Regards, Hua Min

    Thursday, May 4, 2017 8:15 AM
  • Hi HuaMin,

    To get more effective response, I suggest that you remove the sensitive information and share the code sample public so that others who are interested at this issue can look into it.

    By the way, is there any problem with the code sample in my preview post?

    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 5, 2017 10:20 AM
    Moderator
  • Hi Fei,

    I want to know why problem happened to my codes and can you please advise one way for me to send you key to extract the file?


    Many Thanks & Best Regards, Hua Min

    Friday, May 5, 2017 3:35 PM
  • Hi HuaMin,

    The project you uploading doesn't contain the txt files.

    To understand the issue exactly, would you minding let me know whether the code sample in the preview post is helpful. So that we can identify whether the issue is relative to the Office version or other reason. Or the issue is only occur when you moving the code to the new Windows form project?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 11, 2017 10:12 AM
    Moderator
  • Hi,

    I do not know why this file would lead to failure and would lead to corrupt Excel file produced.

     

    >> I am tried to reproduce this issue, however the code works well for me to create a workbook. I have upload the full code sample and you can download from here.

    Did you upload the whole project into that? Or could you please upload your whole project into Onedrive.com?


    Many Thanks & Best Regards, Hua Min


    Thursday, May 11, 2017 1:16 PM
  • Any other help?

    Many Thanks & Best Regards, Hua Min

    Monday, May 15, 2017 7:35 AM
  • Hi HuaMin,

    The code sample(WinFormsApp 9)  with the output10.txt you provided still works well for me. And I have upload it to OneDrive, you can download it from this link.

    Please feel free to let me know if you still have the problem.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 16, 2017 9:15 AM
    Moderator
  • Hi,
    Can you please try to see if you get the same issue, by running against all files as attached?


    Many Thanks & Best Regards, Hua Min

    Tuesday, May 16, 2017 9:58 AM
  • Hi HuaMin,

    Yes, the code still works. I just modify the code below to make it to generate the spreadsheets:

    private void CreateDoc_Click(object sender, EventArgs e)
    {
                //CreateSpreadsheetWorkbook(@"D:\test\new.xlsx", "Sheet1");
                string Msg0 = "";
                string ErrMsg = "";
                string filePath = @"output\";
                foreach (var txtfilePath in System.IO.Directory.EnumerateFiles(@".\Files", "*.txt"))
                {
                    test(filePath+ txtfilePath.Replace("txt","xlsx").Replace(@".\Files\",""), txtfilePath, ErrMsg);
                    using (StreamWriter sw = new StreamWriter(@"err05.txt", true, Encoding.Unicode))
                    {
                        sw.WriteLine("apr27---: " + ErrMsg);
                        sw.Close();
                    }
                    //MessageBox.Show(ErrMsg);
                    Msg0 = ErrMsg;
                }
                if (Msg0 == "")
                    Msg0 = "Done";
                MessageBox.Show(Msg0);
    }

    And you need to create Files folder under the WinFormsApp project and copy the files in the previews post to the File folder. Then change the File property to Copy if newer so that when we run the project it will copy the files to bin folder. Then we can create an output folder under bin/debug.

    After that you can run the project successfully to generate 11 workbooks.

    Regards & Fei

     

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 18, 2017 2:56 AM
    Moderator
  • Hi Fei,

    Did you use my current project to run against all given files? Is it no corrupt Excel file got, in your side? If yes, can you give me the Excel file you've got?

    Did you choose "CreateDoc" button when running that?


    Many Thanks & Best Regards, Hua Min

    Thursday, May 18, 2017 8:44 AM
  • Hi HuaMin,

    Yes, I used the project you provided and click the button CreateDoc button. And all the file generated could be opened successfully.

    Have you tried the code above? If yes, and the code doesn't work would you mind sharing the error message if there is so than we can help you to narrow down this issue?

    I also upload all the files generate you can download them from here.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 19, 2017 5:37 AM
    Moderator
  • Hi HuaMin,

    Yes, I used the code you upload and only change a little code to make it fit to read the txt files like below:


    I am testing using the Office 2013. Would you let me know whether you can open the Excel files I have uploaded? If the files corrupt which version of Office you are using? 

    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 22, 2017 6:22 AM
    Moderator
  • Hi,
    Where is the difference in the codes (can you show it clearly in above)? I used Excel 2007 to open file and cannot, while Celeste did previously also confirm corrupt Excel file, to other cases.

    Many Thanks & Best Regards, Hua Min



    Monday, May 22, 2017 8:13 AM
  • Hi HuaMin,

    The difference is show clearly in the figure in the different color.  If you were using Chrome you can check the clear picture via right click the picture->Open image in new tab. And if you were using the IE, you can get the URL of the image via the property. Then copy the URL and paste it into the location of ID.

    After I tested in the Excel 2007(12.0.6766.5000) SP3 MSO(12.0.6768.5000), the workbooks still able to opened successfully. Here is a figure for your reference:

    Are you testing using the code I mentioned in the previews post? If yes, what's exact version of Excel you were using? Is it helpful to update the Excel to the latest version?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, May 23, 2017 7:07 AM
    Moderator
  • Here is file I get from the codes

    https://1drv.ms/x/s!Ai8CrEskdewXk2qCl5SIN-ayitQR

    while Celeste last time did mention it was corrupt!


    Many Thanks & Best Regards, Hua Min

    Tuesday, May 23, 2017 7:55 AM
  • Hi HuaMin,

    The root cause for the file corrupt is that you were referencing the shared string doesn't exit. The count of items of sharedStrings are 4757, however there are lots of incorrect reference in the sheet5.xml(sheet7d). After removing the incorrect reference the file could opened successfully. The last reference of sharedStrings should be 4756 since the index starts from 0. It should be like below:

        ...
        <x:row r="11743" spans="1:1">
          <x:c r="A11743" t="s">
            <x:v>4755</x:v>
          </x:c>
        </x:row>
        <x:row r="11744" spans="1:1">
          <x:c r="A11744" t="s">
            <x:v>4756</x:v>
          </x:c>
        </x:row>
      </x:sheetData>
      <x:pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
    </x:worksheet>
    

    Please ensure that the code you were developing refer the sharedStrings correctly to fix this issue.

    In addition, this file is not generated by the code sample you uploaded with the conversation with me. Please use the exact code which you upload to generate the files which should work.

    And please feel free to let me know if you still have the problem.

    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, May 24, 2017 6:59 AM
    Moderator
  • Sorry, I did get the corrupt file, using the same project file, I also sent to you currently.

    Many Thanks & Best Regards, Hua Min


    Wednesday, May 24, 2017 10:13 AM
  • Hi HuaMin,

    I used the exact code and files you uploaded and it works well for me. Please check the code again and let me know whether you still have the problem.

    Download the code from here. You can just unzip and run the project, then click the CreateDoc button then check files under the bin/Debug/output.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jackson_1990 Saturday, May 27, 2017 7:11 AM
    • Unmarked as answer by Jackson_1990 Monday, May 29, 2017 5:17 AM
    Friday, May 26, 2017 7:40 AM
    Moderator
  • Hi Fei,

    When I run against your project, I did get

    System.IO.IOException was unhandled
      HResult=-2147024864
      Message=The process cannot access the file 'C:\dp13\General Application\WinFormsApp 2_2_14\bin\Debug\err05.txt' because it is being used by another process.
      Source=mscorlib
      StackTrace:
           at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
           at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
           at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
           at System.IO.StreamWriter.CreateFile(String path, Boolean append, Boolean checkHost)
           at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize, Boolean checkHost)
           at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding)
           at WinFormApp.Form1.CreateDoc_Click(Object sender, EventArgs e) in c:\dp13\General Application\WinFormsApp 2_2_14\Form1.cs:line 90
           at System.Windows.Forms.Control.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(Form mainForm)
           at WinFormsApp.Program.Main() in c:\dp13\General Application\WinFormsApp 2_2_14\Program.cs:line 20
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 
    

    please advise.


    Many Thanks & Best Regards, Hua Min

    Monday, May 29, 2017 5:18 AM
  • Hi,

    Any help?


    Many Thanks & Best Regards, Hua Min

    Wednesday, May 31, 2017 6:11 AM
  • Hi Huamin,

    Based on the error message, the file C:\dp13\General Application\WinFormsApp 2_2_14\bin\Debug\err05.txt' is being used by another process. To fix this issue, please ensure that there is no program is accessing this file.

    There are lots software can check the file was occupied by which program, you can try to research it and close the program which is accessing that file. Also the issue should be fixed by rebooting the computer.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 31, 2017 9:48 AM
    Moderator
  • Hi,
    I did reboot but I still have the problem. Is there a way to avoid the problem?

    Many Thanks & Best Regards, Hua Min

    Wednesday, May 31, 2017 10:05 AM
  • Hi HuaMin,

    Did you modify any piece of code? Or can you check which program is accessing the file in the error message? Based on my understanding, the code which I provided shouldn't cause this issue. 

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 1, 2017 8:20 AM
    Moderator
  • Hi,

    I did not change anything of your project. 

    I also did not understand that, you said no problem, to my project, to which I got the error. Is Celeste there to support?

    Many Thanks & Best Regards, Hua Min


    Thursday, June 1, 2017 9:02 AM
  • Hi HuaMin,

    Sorry for the confusion the link for the project is incorrect, please try download the project from this link. And let me know if you still have the problem.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 5, 2017 6:12 AM
    Moderator
  • Hi,
    I take your project but I get such error below, when compiling it.

    Error	1	Unexpected character '$'	C:\App\WinFormsApp 2_2_20 Fei\Form1.cs	91	44	WinFormsApp
    Error	2	Unexpected character '$'	C:\App\WinFormsApp 2_2_20 Fei\Form1.cs	92	53	WinFormsApp
    Error	3	Unexpected character '$'	C:\App\WinFormsApp 2_2_20 Fei\Form1.cs	99	53	WinFormsApp
    Error	4	Invalid expression term ''	C:\App\WinFormsApp 2_2_20 Fei\Form1.cs	91	44	WinFormsApp



    Many Thanks & Best Regards, Hua Min

    Tuesday, June 6, 2017 2:43 AM
  • Hi HuaMin,

    The string interpolation($) in introduced from C# 6.0.  It seems that you were not using the latest version of MS build. To upgrade Ms-build to support C# 6, you can refer this thread.

    And if you don't want to upgrade the Ms-build we can just replace it using the String.Format method since it is a syntactic sugar.

    Here is the code for your reference:

     
            private void CreateDoc_Click(object sender, EventArgs e)
            {          
                string Msg0 = "";
                string ErrMsg = "";
                string filePath = @"output\";
    
                try
                {
    System.IO.Directory.Delete(String.Format(".\\{0}", filePath), true);
                    System.IO.Directory.CreateDirectory(String.Format(".\\{0}", filePath));
                }
                catch (Exception ex)
                {
    
                }
                finally {
      System.IO.Directory.CreateDirectory(String.Format(".\\{0}", filePath));
                }
                
                foreach (var txtfilePath in System.IO.Directory.EnumerateFiles(@".\Files", "*.txt"))
                {
                    test(filePath+ txtfilePath.Replace("txt","xlsx").Replace(@".\Files\",""), txtfilePath, ErrMsg);
                    using (StreamWriter sw = new StreamWriter(@"err05.txt", true, Encoding.Unicode))
                    {
                        sw.WriteLine("apr27---: " + ErrMsg);
                    }
                    //MessageBox.Show(ErrMsg);
                    Msg0 = ErrMsg;
                }
                if (Msg0 == "")
                    Msg0 = "Done";
                MessageBox.Show(Msg0);
            }

     Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 6, 2017 6:28 AM
    Moderator
  • Hi Fei,
    I can now run your project but you did output to different Excel file per given each Text file, and this is totally different from what I did, as I did output all details of all Text files into one Excel file ONLY.

    And I also see no details to many of Excel files within output folder, after having run your project.


    Many Thanks & Best Regards, Hua Min


    Thursday, June 8, 2017 4:54 AM
  • Hi HuaMin,

    The code sample is only help you to get start to generate the workbook and fix the corruption file issue as you mentioned in the post early.

    You need to modify the code based on the business logic. If you still have the problem I suggest that you reopen a new thread since the orignal issue( file corruption)  is fixed and there two many post in this thread so that you can get more effective response.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 8, 2017 9:00 AM
    Moderator
  • Hi Fei,
    I did compare your latest project against my original one, and I see that your current class is NOT inherited from a Form. Can you please point it out what you did exactly change, to my new thread raised?

    Many Thanks & Best Regards, Hua Min

    Friday, June 9, 2017 2:33 AM