C# Excel to txt convert and format the output WPF

Question

I have a WPF application with 2 buttons currently, one for choosing the file and one to convert the chosen file to .txt format. Now I need to make the other button read the excel file and format the data and make a .txt file.

My code looks like this:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Serialization;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace WpfApplication1
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : System.Windows.Window
{
    public MainWindow()
    {
        InitializeComponent();
    }

    private void BtnFileOpen_Click(object sender, RoutedEventArgs e)
    {
        var fileDialog = new System.Windows.Forms.OpenFileDialog();
        var result = fileDialog.ShowDialog();
        switch (result)
        {
            case System.Windows.Forms.DialogResult.OK:
                var excelFilePath = fileDialog.FileName;
                TxtFile.Text = excelFilePath;
                TxtFile.ToolTip = excelFilePath;
                break;
            case System.Windows.Forms.DialogResult.Cancel:
            default:
                TxtFile.Text = null;
                TxtFile.ToolTip = null;
                break;
        }

    }

    private void convert_Click(object sender, RoutedEventArgs e)
    {
         exportExcelToTxt;
    }
    static void exportExcelToTxt(string excelFilePath, string outputTxtPath)
    {
        Dictionary<string, List<long>> values = new Dictionary<string, List<long>>();
        using (OleDbConnection excelConnection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0;HDR=YES", excelFilePath)))
        {
            excelConnection.Open();
            string firstSheet = getFirstSheetName(excelConnection);
            using (OleDbCommand cmd = excelConnection.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT * FROM [{0}]", firstSheet);
                using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                {
                    using (DataTable dt = new DataTable())
                    {
                        da.Fill(dt); // Getting all the data in the sheet
                        foreach (DataRow item in dt.Rows)
                        {
                            List<long> toAdd = new List<long>();
                            string key = item[0] as string;
                            for (int i = 1; i < dt.Columns.Count; i++)
                            {
                                toAdd.Add(Convert.ToInt64(item[i]));
                            }
                            values.Add(key, toAdd); // Associating all the "numbers" to the "Name"
                        }
                    }
                }
            }
        }
        StringBuilder toWriteToTxt = new StringBuilder();
        foreach (KeyValuePair<string, List<long>> item in values)
        {
            // Formatting the output
            toWriteToTxt.Append(string.Format("{0}:", item.Key));
            foreach (long val in item.Value.Distinct())
            {
                toWriteToTxt.AppendFormat("\t{0} * {1}\r\n", item.Value.Where(f => f == val).Count(),  // Amount of occurrencies of each number
                    val);
            }
        }
        // Writing the TXT
        using (FileStream fs = new FileStream(outputTxtPath, FileMode.Create))
        {
            using (StreamWriter sw = new StreamWriter(fs))
            {
                sw.Write(toWriteToTxt.ToString());
            }
        }
    }


    static string getFirstSheetName(OleDbConnection excelConnection)
    {
        using (DataTable ExcelTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" }))
        {
            return ExcelTables.Rows[0]["TABLE_NAME"].ToString();
        }
    }
}
}

Excel files I will be using this on will look like this:

|  A  |  B  |  C  |  D  |...
| Name|  1  |  2  |  3  |...
|  X  | 898 | 896 | 898 |...

And the .txt I would like to look like this:

 X:   4 * 898
      6 * 896

Basically so it takes the name ranging from A2 to An and then count all the identical instances from B2 to AF2. And in the end I would have a txt file with all the names having lists like above. I also have referenced Microsoft.Office.Interop.Excel, as I read it is required and found this but as I am new to excel related code I have no idea what I can use from there as the purpose of the application there is very different from mine.

How can I make the button do a function as described above?

I added the code given by codroipo and now I have these libraries:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Serialization;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

I also changed "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelFilePath

to "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0;", excelFilePath but I still get debug errors here:

// Writing the TXT
    using (FileStream fs = new FileStream(outputTxtPath, FileMode.Create))
    {
        using (StreamWriter sw = new StreamWriter(fs))
        {
            sw.Write(toWriteToTxt.ToString());
        }
    }
}

static string getFirstSheetName(OleDbConnection excelConnection)
{
    using (DataTable ExcelTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" }))
    {
        return ExcelTables.Rows[0]["TABLE_NAME"].ToString();
    }
}

The type or namespace name 'FileStream' could not be found(are you missing a using directive or an assembly reference?)

static string getFirstSheetName(OleDbConnection excelConnection) the string has an error:

"Expected class, delegate, enum, interface, or struct"

as does the Object[] on the next row.

Am I missing a library?

"4 * " and "6 * " are the number of occurrences of 898 and 896? — Matteo Umili
– Matteo Umili, Commented May 25, 2015 at 9:56
Yes, just an example of how it could show the number of certain places, the numbers correspond a place where the person did a shift so it could be 4 days at restaurant x and 6 days at restaurant y. — Slyre
– Slyre, Commented May 25, 2015 at 9:59
Sorry, here are the needed using directives: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.OleDb; using System.Data; using System.IO; The fullname of FileStream is System.IO.FileStream — Matteo Umili
– Matteo Umili, Commented May 25, 2015 at 11:42
Can you post the entire code you are trying to compile? I think that you could have some parenthesis mistake — Matteo Umili
– Matteo Umili, Commented May 25, 2015 at 11:45
Updated my code, adding System.IO solved the debug errors. does this look like it should work? — Slyre
– Slyre, Commented May 25, 2015 at 11:55

Community · Accepted Answer · 2017-05-23 12:30:08Z

I suggest you to use Microsoft.Jet.OLEDB in this case(if you want a comparison please check Which One is Best OLEDB Or Excel Object Or Database), here is a some code that you can use. I wrote this code assuming that you want to export just the first sheet in the Excel:

    static void exportExcelToTxt(string excelFilePath, string outputTxtPath)
    {
        Dictionary<string, List<long?>> values = new Dictionary<string, List<long?>>();
        using (OleDbConnection excelConnection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelFilePath)))
        {
            excelConnection.Open();
            string firstSheet = getFirstSheetName(excelConnection);
            using (OleDbCommand cmd = excelConnection.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT * FROM [{0}]", firstSheet);
                using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                {
                    using (DataTable dt = new DataTable())
                    {
                        da.Fill(dt); // Getting all the data in the sheet
                        foreach (DataRow item in dt.Rows)
                        {
                            List<long?> toAdd = new List<long?>();
                            string key = item[0] as string;
                            for (int i = 1; i < dt.Columns.Count; i++)
                            {
                                toAdd.Add(item[i] != DBNull.Value ? (long?)Convert.ToInt64(item[i]) : null);
                            }
                            values.Add(key, toAdd); // Associating all the "numbers" to the "Name"
                        }
                    }
                }
            }
        }
        StringBuilder toWriteToTxt = new StringBuilder();
        foreach (KeyValuePair<string, List<long?>> item in values)
        {
            // Formatting the output
            toWriteToTxt.Append(string.Format("{0}:", item.Key));
            foreach (long val in item.Value.Where(f => f != null).Distinct())
            {
                toWriteToTxt.AppendFormat("\t{0} * {1}\r\n", item.Value.Where(f => f == val).Count(),  // Amount of occurrencies of each number
                    val);
            }
        }
        // Writing the TXT
        using (FileStream fs = new FileStream(outputTxtPath, FileMode.Create))
        {
            using (StreamWriter sw = new StreamWriter(fs))
            {
                sw.Write(toWriteToTxt.ToString());
            }
        }
    }

    static string getFirstSheetName(OleDbConnection excelConnection)
    {
        using (DataTable ExcelTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" }))
        {
            return ExcelTables.Rows[0]["TABLE_NAME"].ToString();
        }
    }

Quickly reading up on OLEDB I came to the conclusion I should probably use Microsoft.ACE.OLEDB.12.0 as I am running a 64 bit system and apparently Microsoft.Jet.oledb.4.0 is only for 32 bit.

Collectives™ on Stack Overflow

C# Excel to txt convert and format the output WPF

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related