none
c# code to convert txt to xls file

    Question

  • Hi

    how can i convert the following txt file to xls file. perticular data in txt should be stored on pertular xls cell.

    sample txt file:

    txt1 txt2 txt3 txt4
    txt5 txt6 txt7 txt8
    txt4 txt3 txt2 txt1

    This sample txt data (each "txt") should be stored in perticular xls cell.

    can anyone give me a C# code which does this.
    Friday, February 10, 2012 3:30 PM

Answers

  • I did put together this console app for you. Does the trick for me.

    it uses a reference to Microsoft.Office.Interop.Excel 14.0.0.0

    namespace ConsoleApplication2
    {
        using System;
        using System.IO;
    
        using Microsoft.Office.Interop.Excel;
    
        /// <summary>
        /// The program.
        /// </summary>
        internal class Program
        {
            #region Methods
    
            /// <summary>
            /// The add data from file.
            /// </summary>
            /// <param name="excellWorkSheet" />
            /// The excell work sheet. 
            /// 
            /// <param name="fileName" />
            /// The file name. 
            /// 
            private static void AddDataFromFile(_Worksheet excellWorkSheet, string fileName)
            {
                if (excellWorkSheet == null)
                {
                    throw new ArgumentNullException("excellWorkSheet");
                }
    
                var lines = File.ReadAllLines(fileName);
                var rowCounter = 1;
                foreach (var line in lines)
                {
                    var columnCounter = 1;
                    var values = line.Split(' ');
                    foreach (var value in values)
                    {
                        excellWorkSheet.Cells[rowCounter, columnCounter] = value;
                        columnCounter++;
                    }
    
                    rowCounter++;
                }
            }
    
            /// <summary>
            /// The close quit and release.
            /// </summary>
            /// <param name="excellApp" />
            /// The excell app. 
            /// 
            /// <param name="excellWorkSheet" />
            /// The excell work sheet. 
            /// 
            /// <param name="excellWorkBook" />
            /// The excell work book. 
            /// 
            /// <param name="misValue" />
            /// The mis value. 
            /// 
            private static void CloseQuitAndRelease(
                Application excellApp, Worksheet excellWorkSheet, Workbook excellWorkBook, object misValue)
            {
                excellWorkBook.Close(true, misValue, misValue);
                excellApp.Quit();
                ReleaseObject(excellApp);
                ReleaseObject(excellWorkBook);
                ReleaseObject(excellWorkSheet);
            }
    
            /// <summary>
            /// The main.
            /// </summary>
            /// <param name="args" />
            /// The args. 
            /// 
            private static void Main(string[] args)
            {
                object misValue = System.Reflection.Missing.Value;
                var excellApp = new Application();
                var excellWorkBook = excellApp.Workbooks.Add(misValue);
                var excellWorkSheet = (Worksheet)excellWorkBook.Worksheets.Item[1];
    
                /* Data(excellWorkSheet); */
                const string InputfileName = "inputFile.txt";
                AddDataFromFile(excellWorkSheet, InputfileName);
                Safe(misValue, excellWorkBook, "csharp.net-informations.xls");
    
                CloseQuitAndRelease(excellApp, excellWorkSheet, excellWorkBook, misValue);
    
                Console.WriteLine("File created !");
            }
    
            /// <summary>
            /// The release object.
            /// </summary>
            /// <param name="obj" />
            /// The obj. 
            /// 
            private static void ReleaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Unable to release the Object " + ex);
                }
                finally
                {
                    GC.Collect();
                }
            }
    
            /// <summary>
            /// The safe.
            /// </summary>
            /// <param name="misValue" />
            /// The mis value. 
            /// 
            /// <param name="excellWorkBook" />
            /// The excell work book. 
            /// 
            /// <param name="excellfilename" />The name of the excell file 
            private static void Safe(object misValue, Workbook excellWorkBook, string excellfilename)
            {
                excellWorkBook.SaveAs(
                    excellfilename, 
                    XlFileFormat.xlWorkbookNormal, 
                    misValue, 
                    misValue, 
                    misValue, 
                    misValue, 
                    XlSaveAsAccessMode.xlExclusive, 
                    misValue, 
                    misValue, 
                    misValue, 
                    misValue, 
                    misValue);
            }
    
            #endregion
        }
    }

    The base of this sample was borrowed from here :http://csharp.net-informations.com/excel/csharp-format-excel.htm

    Hope this helps and good luck


    Friday, February 10, 2012 9:13 PM
  • Hi High_fly,

    We should utilize Excel Interop library or connect to an Excel doc via the OleDb Provider to store these data in an excel sheet.
    Here I provide you with a complete sample which demonstrates auto-creating a Excel table which has a relatively complex structure:
    http://www.codeproject.com/Articles/20228/Using-C-to-Create-an-Excel-Document.

    Have a nice day,

    Leo Liu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 15, 2012 3:02 AM
  • You can use "NPOI" for the same. This is an excel library. http://npoi.codeplex.com/documentation

    The documentation has good sample examples and steps you need to perform to download dll's and add it to your solution.

    You even have "ExcelPackage" library from codeplex, but I see this works a bit slow.

    "NPOI" and "ExcelPackage" work on xls files only "EPPlus" works on xlsx files only (meant for Excel 2007 and above files).

    Hope this is informative and solves your questions


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote helpful topics and Mark answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Wednesday, February 15, 2012 3:18 AM

All replies

  • Hi,

    You should use a library to write to a xls file. Try the free excellibrary: http://code.google.com/p/excellibrary/


    Ricardo Sabino --- http://www.ricardosabino.com


    • Edited by n0n4m3 Friday, February 10, 2012 3:57 PM
    Friday, February 10, 2012 3:56 PM
  • In many cases you can just alter the file to be tab/comma delimited and then just change the extension to .xls.  Excel knows how to read such files, unless there is a compelling reason to actually convert it to a true excel file.
    Friday, February 10, 2012 4:01 PM
  • i am getting following errors: (trying to fix it but no result, any help appreciated)

    Error    20877    'Microsoft.Office.Interop.Excel.WorksheetClass' does not contain a constructor that takes '1' arguments    C:\Documents and Settings\Taurus\My Documents\Visual Studio 2008\cartracker\WebApplication4\WebApplication4\Default.aspx.cs    44    35    WebApplication4
    Error    20878    The type or namespace name 'Cell' could not be found (are you missing a using directive or an assembly reference?)    C:\Documents and Settings\Taurus\My Documents\Visual Studio 2008\cartracker\WebApplication4\WebApplication4\Default.aspx.cs    45    41    WebApplication4
    Error    20885    Cannot apply indexing with [] to an expression of type 'object'    C:\Documents and Settings\Taurus\My Documents\Visual Studio 2008\cartracker\WebApplication4\WebApplication4\Default.aspx.cs    52    13    WebApplication4

    Error    20886    No overload for method 'Add' takes '1' arguments    C:\Documents and Settings\Taurus\My Documents\Visual Studio 2008\cartracker\WebApplication4\WebApplication4\Default.aspx.cs    53    13    WebApplication4
    Error    20887    No overload for method 'Save' takes '1' arguments    C:\Documents and Settings\Taurus\My Documents\Visual Studio 2008\cartracker\WebApplication4\WebApplication4\Default.aspx.cs    54    13    WebApplication4

    You can also read my problem there:

    http://www.codeproject.com/Forums/1649/Csharp.aspx?select=4153833&tid=4153813
     

    Friday, February 10, 2012 5:25 PM
  • Hi servy42, tx for tryig to help me.

    but converting txt to csv might not help me.

    Plz read my problem here and help me: http://www.codeproject.com/Forums/1649/Csharp.aspx?select=4153833&tid=4153813

    Friday, February 10, 2012 5:28 PM
  • Hi servy42, tx for tryig to help me.

    but converting txt to csv might not help me.

    Plz read my problem here and help me: http://www.codeproject.com/Forums/1649/Csharp.aspx?select=4153833&tid=4153813

    If it doesn't work it doesn't work; that's fine. I've just found that that it's frequently acceptable to users, and when it is it is much quicker/easier to implement and run.  It's a useful thing to keep in mind, in general, even if it's not applicable in this specific instance.
    Friday, February 10, 2012 6:34 PM
  • I did put together this console app for you. Does the trick for me.

    it uses a reference to Microsoft.Office.Interop.Excel 14.0.0.0

    namespace ConsoleApplication2
    {
        using System;
        using System.IO;
    
        using Microsoft.Office.Interop.Excel;
    
        /// <summary>
        /// The program.
        /// </summary>
        internal class Program
        {
            #region Methods
    
            /// <summary>
            /// The add data from file.
            /// </summary>
            /// <param name="excellWorkSheet" />
            /// The excell work sheet. 
            /// 
            /// <param name="fileName" />
            /// The file name. 
            /// 
            private static void AddDataFromFile(_Worksheet excellWorkSheet, string fileName)
            {
                if (excellWorkSheet == null)
                {
                    throw new ArgumentNullException("excellWorkSheet");
                }
    
                var lines = File.ReadAllLines(fileName);
                var rowCounter = 1;
                foreach (var line in lines)
                {
                    var columnCounter = 1;
                    var values = line.Split(' ');
                    foreach (var value in values)
                    {
                        excellWorkSheet.Cells[rowCounter, columnCounter] = value;
                        columnCounter++;
                    }
    
                    rowCounter++;
                }
            }
    
            /// <summary>
            /// The close quit and release.
            /// </summary>
            /// <param name="excellApp" />
            /// The excell app. 
            /// 
            /// <param name="excellWorkSheet" />
            /// The excell work sheet. 
            /// 
            /// <param name="excellWorkBook" />
            /// The excell work book. 
            /// 
            /// <param name="misValue" />
            /// The mis value. 
            /// 
            private static void CloseQuitAndRelease(
                Application excellApp, Worksheet excellWorkSheet, Workbook excellWorkBook, object misValue)
            {
                excellWorkBook.Close(true, misValue, misValue);
                excellApp.Quit();
                ReleaseObject(excellApp);
                ReleaseObject(excellWorkBook);
                ReleaseObject(excellWorkSheet);
            }
    
            /// <summary>
            /// The main.
            /// </summary>
            /// <param name="args" />
            /// The args. 
            /// 
            private static void Main(string[] args)
            {
                object misValue = System.Reflection.Missing.Value;
                var excellApp = new Application();
                var excellWorkBook = excellApp.Workbooks.Add(misValue);
                var excellWorkSheet = (Worksheet)excellWorkBook.Worksheets.Item[1];
    
                /* Data(excellWorkSheet); */
                const string InputfileName = "inputFile.txt";
                AddDataFromFile(excellWorkSheet, InputfileName);
                Safe(misValue, excellWorkBook, "csharp.net-informations.xls");
    
                CloseQuitAndRelease(excellApp, excellWorkSheet, excellWorkBook, misValue);
    
                Console.WriteLine("File created !");
            }
    
            /// <summary>
            /// The release object.
            /// </summary>
            /// <param name="obj" />
            /// The obj. 
            /// 
            private static void ReleaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Unable to release the Object " + ex);
                }
                finally
                {
                    GC.Collect();
                }
            }
    
            /// <summary>
            /// The safe.
            /// </summary>
            /// <param name="misValue" />
            /// The mis value. 
            /// 
            /// <param name="excellWorkBook" />
            /// The excell work book. 
            /// 
            /// <param name="excellfilename" />The name of the excell file 
            private static void Safe(object misValue, Workbook excellWorkBook, string excellfilename)
            {
                excellWorkBook.SaveAs(
                    excellfilename, 
                    XlFileFormat.xlWorkbookNormal, 
                    misValue, 
                    misValue, 
                    misValue, 
                    misValue, 
                    XlSaveAsAccessMode.xlExclusive, 
                    misValue, 
                    misValue, 
                    misValue, 
                    misValue, 
                    misValue);
            }
    
            #endregion
        }
    }

    The base of this sample was borrowed from here :http://csharp.net-informations.com/excel/csharp-format-excel.htm

    Hope this helps and good luck


    Friday, February 10, 2012 9:13 PM
  • Hi High_fly,

    We should utilize Excel Interop library or connect to an Excel doc via the OleDb Provider to store these data in an excel sheet.
    Here I provide you with a complete sample which demonstrates auto-creating a Excel table which has a relatively complex structure:
    http://www.codeproject.com/Articles/20228/Using-C-to-Create-an-Excel-Document.

    Have a nice day,

    Leo Liu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 15, 2012 3:02 AM
  • You can use "NPOI" for the same. This is an excel library. http://npoi.codeplex.com/documentation

    The documentation has good sample examples and steps you need to perform to download dll's and add it to your solution.

    You even have "ExcelPackage" library from codeplex, but I see this works a bit slow.

    "NPOI" and "ExcelPackage" work on xls files only "EPPlus" works on xlsx files only (meant for Excel 2007 and above files).

    Hope this is informative and solves your questions


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote helpful topics and Mark answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Wednesday, February 15, 2012 3:18 AM
  • Here is one way (actually, it gives you MANY options to save as different formats):

    http://www.codeproject.com/Tips/190144/Export-Database-to-Excel-PDF-HTML-RTF-XML-etc-for


    Ryan Shuell

    Thursday, April 12, 2012 8:29 PM
  • Hi,

    your TXT file is actually value separated file with white-space separator.

    You can easily convert to XLS like this:

    // Create new CSV file.
    var csvFile = new ExcelFile();
    
    // Load data from TXT file.
    csvFile.LoadCsv(fileName + ".txt", ' ');
    
    // Save CSV file to XLS file.
    csvFile.SaveXls(fileName + ".xls");
    with this Excel C# library.

    Wednesday, June 20, 2012 8:14 AM