Reading Text or CSV File and Saving It as Excel File (.xls or xlsx) using Microsoft Excel

Introduction

Sometimes, we need to read huge amounts of data from text or CSV files and convert or put the data into Excel. To do that, we need to create an Excel file first. We create the xlexcel application for opening Excel. And then, we create xlWorkbook and xlWorksheet. When we open a particular Excel workbook and worksheet, we need to set the range of where exactly the data needs to be posted and in which sheet the data needs to be presented, like sheet1 or sheet2 or so on.

After pasting the data, we would save the file with the required extension. We then close the particular file. If you want to watch the file, then we have to comment xlexcel.Quit() and uncomment xlexcel.Visible = true.

Then we can see the Excel file too.

Creating the Form

Creating xlsConvert

private void btnXlsConvert_Click(object sender, EventArgs e)
{
    // Reading the text file - StreamReader include System.IO namespace
    StreamReader objReader = new StreamReader(@"received.txt");// Please give the file path
    string sLine = "";
    ArrayList arrText = new ArrayList();// Include System.Collections.Generic namespace
    while (sLine != null)
    {
        sLine = objReader.ReadLine();
        if (sLine != null)
            arrText.Add(sLine);
    }
    callExcel(arrText, true);
}

Creating xlsxConvert

private void btnXlsxConvert_Click(object sender, EventArgs e)
{
    try
    {
        StreamReader objReader = new StreamReader(@"received.txt");// Please give the file path
        string sLine = "";
        ArrayList arrText = new ArrayList();
        while (sLine != null)
        {
            sLine = objReader.ReadLine();
            if (sLine != null)
                arrText.Add(sLine);
        }
        callExcel(arrText, false);
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.ToString());
     }
}
private void callExcel(ArrayList arrText, bool value)
{
    try
    {
        // Change Your String here
        String textString = null;
        foreach (var item in arrText)
        {
            textString = textString + item + Environment.NewLine;
        }
        Clipboard.SetText(textString);
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        // for excel visibility
        //xlexcel.Visible = true;
        // Creating a new workbook
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        // Putting Sheet 1 as the sheet you want to put the data within
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        // creating the range
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.Paste(CR, false);
        if (value == true)
        {
            try
            {
                // saving the file as .xls
                xlWorkSheet.SaveAs(@"C:\Users\U0153056\Desktop\receivedNew.xls");
            }
            catch (Exception)
            {
                MessageBox.Show("File already exist");
            }
        }
        else
        {
            try
            {
                // saving the file as .xlsx
                xlWorkSheet.SaveAs(@"C:\Users\U0153056\Desktop\receivedNew.xlsx");
            }
            catch (Exception)
            {
                MessageBox.Show("File already exist");
            }
        }
        xlexcel.Quit();
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Before you execute this application, you are supposed to include using the Excel= Microsoft.Office.Interop.Excel namespace. For this, we need to add the namespace in the references (right click on References and click on Add Reference and then select Microsoft.Office.Interop.Excel).