C# Excel to .TXT

Question

I'm starting to play with C# / .NET. My work background is Python.

Im having difficulty solving the conversion from Excel to TXT.

What i need the txt file to look like is:

ROW1COLUM1;ROW1COLUM2;ROW1COLUM3    //Note the lack of ";" on line break.
ROW2COLUM1;ROW2COLUM2;ROW3COLUM3

I am trying to at least save 1 cell in the txt but i cant. Code below:

private void button1_Click(object sender, EventArgs e) // Go
{
    File.Create(@"C: \Users\AG\.PyCharmCE2017.2\config\scratches\testnet.txt").Close();
    TextWriter tw = new StreamWriter(@"C: \Users\AG\.PyCharmCE2017.2\config\scratches\testnet.txt");

    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    Excel.Range xlRange = xlWorksheet.UsedRange;

    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;
    string[] dataRow = new string[15]; //fixed number for now

    for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
            dataRow[j - 1] = xlWorksheet.Cells[i, j].Value.ToString();
        }
        tw.WriteLine(dataRow[1]); // Just checking if i can write something
    }
    MessageBox.Show("OK");
    tw.Close();

    GC.Collect();
    GC.WaitForPendingFinalizers();

    Marshal.ReleaseComObject(xlRange);
    Marshal.ReleaseComObject(xlWorksheet);

    xlWorkbook.Close();
    Marshal.ReleaseComObject(xlWorkbook);

    xlApp.Quit();
    Marshal.ReleaseComObject(xlApp);
}

I also need to save the .txt file as unicode, in case that matters. This is probably very basic, i just couldn't find an answer. Thanks

EDIT:

I managed to make it work. I also added a backgroundWorker. The problem i have is performance. Can someone point me in the right direction?

Code:

if (goNoGo)
{
    string sourceDirectory = Path.GetDirectoryName(fileName);
    string filenameWithoutExtension = Path.GetFileNameWithoutExtension(fileName);

    File.Create(sourceDirectory + filenameWithoutExtension).Close();
    TextWriter tw = new StreamWriter(sourceDirectory + "\\" + filenameWithoutExtension + ".txt", true, Encoding.Unicode);

    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    Excel.Range xlRange = xlWorksheet.UsedRange;

    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;
    string dataRow = "";
    int z = 1;

    if (checkBox1.Checked)
    {
        z = 2;
    }

    int cont = 0;
    for (float i = z; i <= rowCount; i++)
    {
        if (i % 250 == 0) // every 250 rows, check BW updates
        {
            cont = ((int)((i / rowCount) * 100));
            backgroundWorker1.ReportProgress(cont);
            //MessageBox.Show(cont.ToString());
            if (backgroundWorker1.CancellationPending)
            {
                e.Cancel = true;
                backgroundWorker1.ReportProgress(0);
                return;
            }
        }
        for (int j = 1; j <= colCount; j++)
        {
            try
            {
                if (j == 1)
                {
                    dataRow = xlWorksheet.Cells[i, j].Value.ToString();
                }
                else
                {
                    dataRow += ";";
                    dataRow += xlWorksheet.Cells[i, j].Value.ToString();
                }
            }
            catch (Exception ex) // catches empty cells
            {
                if (j == 1)
                {
                    dataRpw = "";
                }
                else
                {
                    dataRow += ";";
                    dataRow += "";
                }
                continue;
            }
        }
        tw.WriteLine(dataRow);
    }

    tw.Close();
    //cleanup
    GC.Collect();
    GC.WaitForPendingFinalizers();

    Marshal.ReleaseComObject(xlRange);
    Marshal.ReleaseComObject(xlWorksheet);

    xlWorkbook.Close();
    Marshal.ReleaseComObject(xlWorkbook);

    xlApp.Quit();
    Marshal.ReleaseComObject(xlApp);
    backgroundWorker1.ReportProgress(100);
}
else if (extensionWrong)
{
    MessageBox.Show("File must be .xls");
}
else
{
    MessageBox.Show("Load a file");
}

A 30k row file can take up to an hour. Any ideas?

Answer 1

Please try this and feedback.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Excel;

namespace test
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        DataSet result = new DataSet();

        private void button1_Click(object sender, EventArgs e)
        {
            string fileName = "";
            fileName = textBox3.Text;

            if (fileName == "")
            {
                MessageBox.Show("Enter Valid file name");
                return;
            }

            converToCSV(comboBox1.SelectedIndex);

        }

        private void button2_Click(object sender, EventArgs e)
        {
            string Chosen_File = "";

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                Chosen_File = openFileDialog1.FileName;
            }
            if (Chosen_File == String.Empty)
            {
                return;
            }
            textBox1.Text = Chosen_File;

            getExcelData(textBox1.Text);

        }

        private void button3_Click(object sender, EventArgs e)
        {
            DialogResult result = this.folderBrowserDialog1.ShowDialog();
            string foldername = "";
            if (result == DialogResult.OK)
            {
                foldername = this.folderBrowserDialog1.SelectedPath;
            }

            textBox2.Text = foldername;
        }

        private void getExcelData(string file)
        {

            if (file.EndsWith(".xlsx"))
            {
                // Reading from a binary Excel file (format; *.xlsx)
                FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                result = excelReader.AsDataSet();
                excelReader.Close();
            }

            if (file.EndsWith(".xls"))
            {
                // Reading from a binary Excel file ('97-2003 format; *.xls)
                FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                result = excelReader.AsDataSet();
                excelReader.Close();
            }

            List<string> items = new List<string>();
            for (int i = 0; i < result.Tables.Count; i++)
                items.Add(result.Tables[i].TableName.ToString());
            comboBox1.DataSource = items;

        }

        private void converToCSV(int ind)
        {
            // sheets in excel file becomes tables in dataset
            //result.Tables[0].TableName.ToString(); // to get sheet name (table name)

            string a = "";
            int row_no = 0;

            while (row_no < result.Tables[ind].Rows.Count)
            {
                for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
                {
                    a += result.Tables[ind].Rows[row_no][i].ToString() + ",";
                }
                row_no++;
                a += "\n";
            }
            string output = textBox2.Text + "\\" + textBox3.Text + ".csv";
            StreamWriter csv = new StreamWriter(@output, false);
            csv.Write(a);
            csv.Close();

            MessageBox.Show("File converted succussfully");

            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            comboBox1.DataSource = null;
            return;
        }

    }
}