none
How to convert a txt file to xls using C#? RRS feed

  • Question

  • Hello everyone. Im trying to build a simple program to convert a txt file into xls using C#.

    I can manage to do that with only one column in the txt file, but not with "dimensional" information.

    Basically i want to convert the information under this into a xls file.

    NAME    AGE    NATIONALITY 
    Razvan    22    Romanian
    Pedro    21    Portuguese
    Alphonse    32    Spanish

    This is what i have so far, but since im stucked is not working.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using excel = Microsoft.Office.Interop.Excel;
    using System.Threading;
    
    namespace ConsoleApp3
    {
        class Program
        {
            static void Main(string[] args)
            {
                string[] inputLines = System.IO.File.ReadAllLines(@"C:\Users\rcbalaci\Desktop\Açores\RORA_FONTES.txt");
    
                excel.Application oXL;
                excel._Workbook oWB;
                excel._Worksheet oSheet;
                excel.Range oRng;
    
                object misvalue = System.Reflection.Missing.Value;
    
                try
                {
                    //Ligar o excell e apanhar o objeto da app
                    oXL = new excel.Application();
                    oXL.Visible = true;
    
                    //Novo workbook
                    oWB = (excel._Workbook)(oXL.Workbooks.Add(""));
                    oSheet = (excel._Worksheet)oWB.ActiveSheet;
    
                    //Adicionar o cabeçalho
                    oSheet.Cells[1, 1] = "NAME";
                    oSheet.Cells[1, 2] = "AGE";
                    oSheet.Cells[1, 3] = "NATIONALITY";
    
                    //IM STUCK HERE
                    for(int i = 1; i<=inputLines.Length; i++)
                    {
                        oSheet.Cells[1][i + 1] = inputLines[i - 1];
                        oSheet.Cells[2][i + 1] = inputLines[];
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Exception" + e);
                    throw;
                }
            }
        }
    }
    

    I apreaciatte some help. Thank you.


    Friday, January 3, 2020 3:51 PM

All replies

  • Firstly I'd ask the question if it is needs to be an XSLX file or just a simple CSV file instead. If you aren't using any XSLX specific functionality then CSV is easier.

    As for your stuck part, isn't your indice backwards. The first value is the row and the second value is the column so your for loop should be enumerating the lines of the input file and incrementing the first index each time. The second index is fixed (either 1, 2 or 3). I don't know the Excel model that well but do indice start at 1? You should probably verify.

    Also your `inputLines` are the lines of the file. So if you have 3 lines then you'd have 3 values in this array. However in your for loop you're referencing the lines themselves but each line is a separate row right? Within the text file how are you distinguishing between name, age and nationality? Is this is a CSV file and therefore comma separated? You need to parse each line to get the column values. The most simplistic parsing might look something like this for a CSV file.

    var row = 2;
    foreach (var line in inputLines)
    {
       //Get the fields from the line - assuming CSV here
       var fields = line.Split(',');
    
       //Should probably verify that the line had the min fields we need...
       if (fields.Length < 3)
          continue;
    
       oSheet.Cells[row, 1] = fields[0];
       oSheet.Cells[row, 2] = fields[1];
       oSheet.Cells[row, 3] = fields[2];
    };
    Note that this is ignoring the complexities of CSV parsing such as quotes and whatnot but you get the idea.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, January 3, 2020 4:12 PM
    Moderator
  • Unfortunartely it must be a xlsx file. Im distinguishing "name, age and nationality" by a TAB.


    Friday, January 3, 2020 4:37 PM
  • Change the comma to a tab character (\t) in the `Split` call and it should work then.

    Michael Taylor http://www.michaeltaylorp3.net

    Friday, January 3, 2020 5:04 PM
    Moderator
  • Alternate solution for creating .xlsx not .xls

    Use SpreadSheetLight free, install from NuGet.

    Simple example using tab delimited file with the data you provided. If you need to style anything see this page. As requirements become complete you will also need Microsoft DocumentFormat.OpenXml version 2.5 as a reference in this project which is also free.

    using System;
    using SpreadsheetLight;
    
    namespace SpreadSheetLightImportTextFilesConsoleApp
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var sl = new SLDocument())
                {
                    sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Delimited");
                    var tio = new SLTextImportOptions();
                    sl.ImportText("ImportTextDelimited.txt", "A1", tio);
                    sl.SaveAs("ImportText.xlsx");
                }
    
                Console.WriteLine("End of program");
                Console.ReadLine();
            }
        }
    }


    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


    Friday, January 3, 2020 5:40 PM
    Moderator
  • I've already tryied to do that. Im able to put the information cell by cell but the information is only going to the second row. It doesnt convert every line.

    Friday, January 3, 2020 5:56 PM
  • Sorry I missed a critical line when converting your foreach loop. The last line of your for loop should be `++row`.

    foreach (var line in inputLines)
    {
       …
    
       ++row;
    };


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, January 3, 2020 6:03 PM
    Moderator
  • Hi,
    You can create a delimited text file that excel can parse into rows and columns.
    First, you can add the Microsoft.Office.Interop.Excle to the project by following the steps.
    1.Right click the project> Add> Reference>Choose the Microsoft.Office.Interop.Excle
    2.Click OK.
    Then, you can use OpenText() method to open the text file and use SaveAs() method to save it as xls file.
    Here is a code example you can refer to.

    private Microsoft.Office.Interop.Excel.Application m_objExcel = null;
    private Microsoft.Office.Interop.Excel.Workbooks m_objBooks = null;
    private Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
    private object m_objOpt = System.Reflection.Missing.Value;      
    private object m_strSampleFolder = "C:\\Users\\danielzh\\Desktop\\";
        private void button1_Click(object sender, EventArgs e)
    {
        // Open the text file in Excel.
        m_objExcel = new Microsoft.Office.Interop.Excel.Application();
        m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
        m_objBooks.OpenText(m_strSampleFolder + "Book5.txt", Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1,
        Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
        false, true, false, false, false, false, m_objOpt, m_objOpt,
        m_objOpt, m_objOpt, m_objOpt);
        m_objBook = m_objExcel.ActiveWorkbook;
        // Save the text file in the typical workbook format and quit Excel.
        m_objBook.SaveAs(m_strSampleFolder + "Book5.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
        m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
        m_objOpt, m_objOpt);
        m_objBook.Close(false, m_objOpt, m_objOpt);
        m_objExcel.Quit();
    }

    More details you can refer to this document.
    [How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET]

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    Best Regards,
    Daniel Zhang


    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 10, 2020 3:26 AM