none
How to extract part of text file and write it in existing Excel file RRS feed

  • Question

  • Hi,

    I am trying to write a console app that extract specific lines from text file and write them in the second column of the excel sheet

    my file look like this:

    ----------

    Other text

    a,text1,text,text

    b,text2,text,text

    c,text3,text,text

    other text

    ------------

    Once the words in bold are extracted, they need to be written in existing workbook.

    So far I am able to read the file and extract the required part but not able to write this to the excel file. Can you please help me on this?

    string path = @"C:\Test.txt";
                StreamReader file = new System.IO.StreamReader(path);
                bool fn = false;
                while ((line = file.ReadLine()) != null)
                {

                    if (line.StartsWith("a"))
                    {
                        fn = true;
                    }

                    if (fn)
                    {
                        line = line.Replace(",", "");

                        var parts = Array.ConvertAll(line.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries), lineItem => lineItem.Trim());
                        
                            string myTxt = parts[1];
                           
                                         
                        if (parts[0].Equals("c"))
                        {
                            Console.ReadLine();
                            break;

    Wednesday, November 27, 2019 10:40 AM

All replies

  • You can use NPOI.

    There are many examples on the web.

    Wednesday, November 27, 2019 11:50 AM
  • I would recommend SpreadSheetLight (it's free) and installed via NuGet. The following page has lots of examples. Note that Excel does not even need to be installed to write, only to read via Excel itself.

    Here is a conceptual example to append data to an existing sheet.

    var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo2.xlsx");
    const string sheetName = "Names";
    
    
    var names1 = new List<string>() {"Karen","Mary" };
    AppendData(fileName, names1, sheetName);
    
    names1 = new List<string>() { "Jean", "Sue" };
    AppendData(fileName, names1, sheetName);

    Excel operations

    public void AppendData(string pFileName, List<string> pNameList, string pSheetName)
    {
        using (var doc = new SLDocument(pFileName, pSheetName))
        {
            
            var lastRow = doc.GetWorksheetStatistics().EndRowIndex;
            lastRow = lastRow == -1 ? 1 : lastRow +1;
    
            foreach (var name in pNameList)
            {
                doc.SetCellValue($"A{lastRow}", name);
                lastRow += 1;
            }
            doc.Save();
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, November 27, 2019 11:50 AM
    Moderator
  • Thanks for your reply,

    Can you add some comments to your example code as I am not able to follow properly as I am am new to C#.

    Regards. 

    Thursday, November 28, 2019 12:10 PM
  • This

    using (var doc = new SLDocument(pFileName, pSheetName))

    Initializes a new document, opens the file to the sheet name passed in.

    This

    var lastRow = doc.GetWorksheetStatistics().EndRowIndex;
    lastRow = lastRow == -1 ? 1 : lastRow +1;

    Gets the last used row, increments it by one for appending.

    This

    foreach (var name in pNameList)
    {
    doc.SetCellValue($"A{lastRow}", name);
    lastRow += 1;
    }

    iterates the list passed in, writes it to column A:lastRow were lastRow is the next row to write too.

    Suggestions:

    - Download the help file on the main page of SpreadSheetLight to learn from.

    - In the help file look at the method and overloads for SLConvert.ToCellReference

    - Look for samples on the site e.g. https://spreadsheetlight.com/downloads/samplecode/HelloWorld.cs


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, November 28, 2019 1:29 PM
    Moderator