Monday, December 11, 2017

C#: Import excel file as dataset using OLEDB

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace cltStammdatenImporterGUI
{
    public class ExcelFile
    {

        public static DataSet DataSetFromExcelFile(string sExcelFilename, string sExcelRange = "")
        {
            DataSet ds = new DataSet();

            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sExcelFilename + ";Extended Properties='Excel 12.0 XML;HDR=NO;IMEX=1;';";

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;

                // Get all Sheets in Excel File
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                // Loop through all Sheets to get data
                foreach (DataRow dr in dtSheet.Rows)
                {
                    string sheetName = dr["TABLE_NAME"].ToString();

                    if (!sheetName.EndsWith("$"))
                        continue;

                    // Get all rows from the Sheet. sExcelRange must be in the form A1:C10 or sim or just empty
                    cmd.CommandText = "SELECT * FROM [" + sheetName + sExcelRange + "]";

                    DataTable dt = new DataTable();
                    dt.TableName = sheetName;

                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    da.Fill(dt);

                    ds.Tables.Add(dt);
                }

                cmd = null;
                conn.Close();
            }

            return ds;
        }
}
}

Tuesday, December 5, 2017

[C#] How to fix the "Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine" error

If you are trying to read an excel file in C# and you get this error:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

You need to install "Microsoft Access Database Engine 2010 Redistributable" that is available at the Microsoft's website:


https://www.microsoft.com/en-in/download/details.aspx?id=13255

Wednesday, November 22, 2017

C# : How Convert doubles to strings ALWAYS with dots instead of commas

To convert a double to string ALWAYS with dots instead of commas (as it is used in some languages like Italian or German) you need to use an overloaded version of the ToString() method.



Example:

double dVal = 77.55;
string sVal = dVal.ToString(System.Globalization.CultureInfo.InvariantCulture);
// sVal will be  always "77.55"


or if you use System.Globalization


using System.Globalization;

double dVal = 77.55;
string sVal = dVal.ToString(CultureInfo.InvariantCulture);

Tuesday, November 21, 2017

How to implement Matlab-like Tic() and Toc() functions in C#

Tic() and Toc() functions are quite handy in Matlab and here is how you can get something similar in C#


using System;
using System.Diagnostics;

namespace MyTools
{
    class Perform
    {
        static Stopwatch stopWatch;

        public static void Tic()
        {
            stopWatch = new Stopwatch();
            stopWatch.Start();

        }

        public static void Toc()
        {
            TimeSpan ts = stopWatch.Elapsed;

            // Format and display the TimeSpan value.
            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                ts.Hours, ts.Minutes, ts.Seconds,
                ts.Milliseconds / 10);

            // elapsed time since the last Tic()
            Console.WriteLine($"Elapsed time : {elapsedTime} ");

        }
    }
}


Now you can check the performance of your code with:



Perform.Tic();

// some code that takes some time to execute

Perform.Toc();



OR if you use the using static directive:


using static MyTools.Perform;

Tic();

// some code that takes some time to execute

Toc();



Happy programming!


Source:

Monday, November 20, 2017

PluginManger for Notepad++ is missing

If you installed the 64 bit version of Notepad, it can be that the useful plugin manager is missing.
In this case, you can install it manually!

Thursday, November 16, 2017

VisualStudio: How to indent all code in file

In VisualStudio to indent all code in the file you are currently working on:

1. Select all code: CTRL+A
2. Choose CTRL+K, CTRL+D

That's it!


Wednesday, November 15, 2017

Notepad++: Remove all lines containing a certain word/sentence/regex

In Notepad++ (a wonderful Program!) you can easily delete all LINES containing a certain word/sentence or regex.

It's basically a two step process.


You need to find all lines that containing your search string. 

  • Open the search window (CTRL+F)
  • Choose the Mark tab
  • Insert the string you want to search for 
  • Select the Bookmark line
  • Press Mark All.

Now from the Notepad++ Menus, choose:
Search -> Bookmark -> Remove Bookmarked Lines

That's it!