Difficulties with C# + Excel

Question

I have a problem.

I have an Excel File where sometimes the same customer is in 2 rows. But not always.

Its like: Click

Now, i want to create a DataGrid in C# which can list this in one row like: Click

I know it would be easier to change the Excel file, but assume it wouldnt work that way(we get the file like this and we cant change it)

I know i could too just make another Excel File and make it with Excel(already did it this way, but we would need it more as C#)

Now i am at this point:

private void button2_Click(object sender, EventArgs e)
        {

            OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Arbeitsmappe |*.xlsx", ValidateNames = true };

            if (ofd.ShowDialog() == DialogResult.OK)
                textBox1.Text = ofd.SafeFileName;

            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = false;

            string filename = ofd.FileName;

            Excel.Workbook workbook = excelApp.Workbooks.Open(filename);
            Excel.Worksheet worksheet = workbook.Worksheets[1];     
            dataGridView1.ColumnCount = 2;
            dataGridView1.Columns[0].Name = "Number";
            dataGridView1.Columns[1].Name = "Street";
            int rows = worksheet.UsedRange.Rows.Count;

            for (int i = 2; i <= rows; i++)
            {
                string combinehr = worksheet.Cells[i, 150].Text + worksheet.Cells[i, 151].Text;
                dataGridView1.Rows.Add(worksheet.Cells[i,29].Text, combinehr);
            }
        }

How do i expand it that it works like i want?

I would be so grateful

(sorry for the english)

Answer 1

With a reference to ExcelDataReader, ExcelDataReader.DataSet and DataSetExtensions (.Net) you can read the Excel file pretty easy into a DataSet, then you just have to work with the logic:

Input file:

using System;
using System.Data;
using System.IO;
using System.Linq;
using ExcelDataReader;

public DataTable GetTableFromExcel(string filePath)
{
    DataSet ds = new DataSet();
    using (var stream = System.IO.File.Open(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read))
    {
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            ds = reader.AsDataSet();
        }
    }

    DataTable table = new DataTable();
    table.Columns.Add(new DataColumn("CustomerNr"));
    table.Columns.Add(new DataColumn("Address"));

    // Set column names
    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
    {
        // DataColumn.ColumnName can't be empty when DataColumn is part
        // of a DataTable (throws ArgumentException)
        string columnName = ds.Tables[0].Rows[0][i].ToString();
        if (string.IsNullOrWhiteSpace(columnName))
        {
            columnName = $"Column{i}";
        }
        ds.Tables[0].Columns[i].ColumnName = columnName;
    }

    // Remove the first row containing the headers
    ds.Tables[0].Rows.Remove(ds.Tables[0].Rows[0]);

    // I don't have the benchmarks with me right now, but I've tested
    // DataTable.Select vs DataTable.AsEnumerable.Select many times
    // and the AsEnumerable method its faster, that's why you need the
    // reference to System.Data.DataSetExtensions
    var enumerableTable = ds.Tables[0].AsEnumerable();

    // list of unique products
    var products = enumerableTable.Select(row => row.Field<string>("Product")).Distinct();

    // Add a column for each product
    foreach (string product in products)
    {
        table.Columns.Add(new DataColumn(product));
    }

    // list of unique customers
    var customerNumbers = enumerableTable.Select(row => row.Field<double>("CustomerNr")).Distinct();
    foreach (var customerNumber in customerNumbers)
    {
        DataRow record = table.NewRow();
        record["CustomerNr"] = customerNumber;
        record["Address"] = enumerableTable.First(row => row.Field<double>("CustomerNr").Equals(customerNumber))[1];

        for (int i = 2; i < table.Columns.Count; i++)
        {
            DataRow product = enumerableTable.FirstOrDefault(row => row.Field<double>("CustomerNr").Equals(customerNumber) 
                && row.Field<string>("Product").Equals(table.Columns[i].ColumnName));
            // Quantity = 0 if product is null
            record[i] = product?["Quantity"] ?? 0;
        }

        table.Rows.Add(record);
    }
    return table;
}

Result DataTable:

Answer 2

The same result as @IvanGarcíaTopete via Microsoft.Office.Interop.Excel.

Class ExcelModel for Excel data:

public class ExcelModel
{
    public string CustomerNr { get; set; }
    public string Address { get; set; }
    public string Product { get; set; }
    public string Quantity { get; set; }
}

Read Excel and fill out model:

private void OpenReadExcel()
{
    var dlg = new OpenFileDialog();
    if (dlg.ShowDialog() != DialogResult.OK) return;

    var exApp = new Microsoft.Office.Interop.Excel.Application();

    Workbook exWbk = exApp.Workbooks.Open(dlg.FileName);
    Worksheet wSh = exWbk.Sheets[1];
    int k = 2;
    Customers.Clear();
    while (wSh.Cells[k, 1].Text != "" && wSh.Cells[k, 1].Value != null)
    {
        var rowExcelModel = new ExcelModel()
        {
            CustomerNr = wSh.Cells[k, 1].Text,
            Address = wSh.Cells[k, 2].Text,
            Product = wSh.Cells[k, 3].Text,
            Quantity = wSh.Cells[k, 4].Text
        };

        Customers.Add(rowExcelModel);
        k++;
    }
    exApp.Quit();        
}

Generate Data table:

private void GenerateDataTable()
{
    // unique products and customers
    var products = Customers.Select(x => x.Product).Distinct().ToList();
    var customers = Customers.Select(x => x.CustomerNr).Distinct().ToList();

    // columns CustomerNr and Address
    var dataTable = new System.Data.DataTable();
    dataTable.Columns.Add(new DataColumn("CustomerNr"));
    dataTable.Columns.Add(new DataColumn("Address"));
    // columns for each product
    foreach (var product in products)
    {
        dataTable.Columns.Add(new DataColumn(product));
    }

    //fill rows for each customers
    foreach (var customer in customers)
    {
        var row = dataTable.NewRow();
        row["CustomerNr"] = customer;
        row["Address"] = Customers.Where(x => x.CustomerNr == customer).Select(x => x.Address).FirstOrDefault();
        foreach (var product in products)
        {
            var quantity = Customers.Where(x => x.CustomerNr == customer && x.Product == product)
                .Select(x => x.Quantity).FirstOrDefault();

            row[product] = quantity ?? "0";
        }
        dataTable.Rows.Add(row);
    }

    dataGridView1.DataSource = dataTable;
}