Reading data from Excel to C #

Previously I opened a topic for reading excel data from within Datatable. (My OLD Topic) I resolved that operation. But there is a problem when you need to read 10 records in Excel. There are 245 records in Datatable.

MY C# CODE

      baglan.Close();
            linkLabel5.Text= ConvertDataTableToString(DTexcel, "\n").Count().ToString().Replace(',', '.') + "Kullanıcı Bulunamadı";
            baglanti.Close();
        }
    }

    /*--- Diğer Fonsiyonlar*/
     /*Excelden Oku*/
    public static string ConvertDataTableToString(DataTable dataTable,string Karakter)
    {
        var output = new StringBuilder();

        var columnsWidths = new int[dataTable.Columns.Count];
        output.Append(Karakter);

        foreach (DataRow row in dataTable.Rows)
        {
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                var length = row[i].ToString().Length;
                if (columnsWidths[i] < length)
                    columnsWidths[i] = length;
            }
        }

        for (int i = 0; i < dataTable.Columns.Count; i++)
        {
            var text = dataTable.Columns[i].ColumnName;
            output.Append(text); 
        }
        output.Append(Karakter);

        foreach (DataRow row in dataTable.Rows)
        {
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                var text = row[i].ToString();
                output.Append(","+ Karakter + text);
            }
            output.Append(Karakter);
        }
        return output.ToString().Replace('#', '.');
    }

    private static string PadCenter(string text, int maxLength)
    {
        int diff = maxLength - text.Length;
        return new string(' ', diff / 1) + text + new string(' ', (int)(diff / 1));
    }

my excel file

Datatable read 245

1 answer

  • answered 2018-01-11 21:20 Christopher

    Reading any office format from C#, has the following options:

    • If you only need the new formats(xlsx): OpenXML. You can use the OpenXML SDK, various wrappers around it. And even the ZipArchive and XML related classes.
    • If you need the old formars (xls): You ahve to use the (t)rusty office COM interop. That requries a office to be installed, requries a interactive session (not for Services or Webpages) and has all the usual issues of COM interop on top of it.
    • For any specific Display Technology there may be a specific solution for a specific case. But as we always had the COM Interop to fall back on, there was never a need to develop a full solution for .NET.

    If you have the option at all, just limit it to XSLX and the other new Office Formats. It saves you a ton of headaches.