You must Sign In to post a response.
  • Category: Windows 8

    Convert .txt file to .xls file in c# 2012

    I am generating a report of 2 dimensional data in notepad. Now i want to convert this .txt file to .xls file and display it.How to do the necessary??
  • #768520
    .txt file will never directly convert to .xls, you need to read the contents from .txt file and write them I excel file
    see below snippet, you need to use EXCEL interop object for it

    Microsoft.Office.Interop.Excel.Application oXL;
    Microsoft.Office.Interop.Excel._Workbook oWB;
    Microsoft.Office.Interop.Excel._Worksheet oSheet;
    Microsoft.Office.Interop.Excel.Range oRng;
    object misvalue = System.Reflection.Missing.Value;
    try
    {
    //Start Excel and get Application object.
    oXL = new Microsoft.Office.Interop.Excel.Application();
    oXL.Visible = true;

    //Get a new workbook.
    oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
    oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

    //Add table headers going cell by cell.
    oSheet.Cells[1, 1] = "First Name";
    oSheet.Cells[1, 2] = "Last Name";
    oSheet.Cells[1, 3] = "Full Name";
    oSheet.Cells[1, 4] = "Salary";

    //Format A1:D1 as bold, vertical alignment = center.
    oSheet.get_Range("A1", "D1").Font.Bold = true;
    oSheet.get_Range("A1", "D1").VerticalAlignment =
    Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

    // Create an array to multiple values at once.
    string[,] saNames = new string[5, 2];

    saNames[0, 0] = "John";
    saNames[0, 1] = "Smith";
    saNames[1, 0] = "Tom";

    saNames[4, 1] = "Johnson";

    //Fill A2:B6 with an array of values (First and Last Names).
    oSheet.get_Range("A2", "B6").Value2 = saNames;

    //Fill C2:C6 with a relative formula (=A2 & " " & B2).
    oRng = oSheet.get_Range("C2", "C6");
    oRng.Formula = "=A2 & \" \" & B2";

    //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
    oRng = oSheet.get_Range("D2", "D6");
    oRng.Formula = "=RAND()*100000";
    oRng.NumberFormat = "$0.00";

    //AutoFit columns A:D.
    oRng = oSheet.get_Range("A1", "D1");
    oRng.EntireColumn.AutoFit();

    oXL.Visible = false;
    oXL.UserControl = false;
    oWB.SaveAs("c:\\test\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
    false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    oWB.Close();

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #768524
    Hi,

    First you have to convert txt file into any other format like string array or datarow and then read that datarow and write it into excel sheet like below.


    public static void WriteExcel(DataRow drRow, string outputFilePath, string sheetName)
    {
    try
    {
    #region Write Headers to Excel file

    objApp.Visible = true;
    objApp.DisplayAlerts = false;

    if (!string.IsNullOrEmpty(outputFilePath) && !File.Exists(outputFilePath))
    {
    //create workbook with sheet

    #region create excel sheet
    objWrkbook = objApp.Workbooks.Add(1);

    objWrkSheet = (Excel.Worksheet)objWrkbook.ActiveSheet;
    objWrkSheet.Name = "Sheet1";

    #endregion
    }
    else
    {
    //open workbook with sheet
    #region open excel sheet
    objWrkbook = objApp.Workbooks.Open(outputFilePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

    #endregion
    }

    objSheets = (Excel.Sheets)objWrkbook.Worksheets;

    //open the specific sheet in the workbook

    objWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(sheetName);

    int colcnt = objWrkSheet.UsedRange.Columns.Count;
    int rowcnt = objWrkSheet.UsedRange.Rows.Count;

    // write columns to the excel sheet
    #region create headers to excel
    if (colcnt == 1 && rowcnt == 1)
    {
    for (int i = 0; i < drRow.Table.Columns.Count; i++)
    {
    objWrkSheet.Cells[1, i + 1] = drRow.Table.Columns[i].ColumnName;
    }
    colcnt = drRow.Table.Columns.Count;
    }
    #endregion
    //write rows to the excel sheet
    #region insert rows to excel
    for (int i = 1; i <= colcnt; i++)
    {
    objWrkSheet.Cells[rowcnt + 1, i] = drRow[i - 1];
    }
    #endregion
    //save the workbook
    #region save workbook
    objWrkbook.SaveAs(outputFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    #endregion

    #endregion
    }
    catch (Exception ex)
    {
    throw;
    }
    finally
    {
    //clear objects
    #region clear workbook objects
    if (objWrkSheet != null)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkSheet);
    if (objWrkbook != null)
    objWrkbook.Close(true, outputFilePath, true);
    if (objApp != null)
    objApp.Quit();
    #endregion
    }
    }




    Hope this helps you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768527
    Hi,

    You need to read data from the txt file first and then write the data into Excel. If you don't want to install MS Excel, you can use 3rd party libraries, such as this one: https://www.nuget.org/packages/FreeSpire.XLS/.
    Add a reference to Spire.Xls.dll and then use below code:
    using System.IO;
    using Spire.Xls;

    namespace Convert_TXT_to_Excel
    {
    class Program
    {
    static void Main(string[] args)
    {
    Workbook workbook = new Workbook();
    Worksheet sheet = workbook.Worksheets[0];
    string filepath = "test.txt";
    string text = File.ReadAllText(filepath);
    string[] cells = text.Split(' ');
    for (int i = 0; i < cells.Length; i++)
    {
    //insert into the first column
    sheet.Range[i+1,1].Text = cells[i];
    }
    workbook.SaveToFile("Result.xlsx",FileFormat.Version2010);
    }
    }
    }

  • #768542
    Here's a sample cose snippet of how you could convert a txt file to excel by using in c# 2012
    static void Main(string[] args)
    {

    string line;
    using (StreamReader reader = new StreamReader(@"yourFilePath.txt"))
    {
    while ((line = reader.ReadLine()) != null)
    {
    using (StreamWriter writer = new StreamWriter(@"yourFilePath.csv", true))
    {
    writer.WriteLine(line.Replace(",",";"));
    }
    }
    }

    }

    Reference : http://www.geekinterview.com/talk/19527-convert-the-text-file-excel-format.html

    https://www.codeproject.com/questions/651343/converting-text-file-to-excel-file-using-csharp


Sign In to post your comments