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;
        }
}
}

[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

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);

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:

msdn.microsoft.com

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!

• Go to this link: https://github.com/bruderstein/nppPluginManager/releases/tag/v1.4.9 
• Unpack the zip file
• Copy the file PluginManager.dll into the plugin directory of Notepad++ 
• Copy the file gpup.exe (from updater) to the updater folder of Notepad++
• Restart Notepad++

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!

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!

C#: Setting up a connection with PostgreSQL in VisualStudio

In this small tutorial I gonna show you how to setup a connection to your local PostgreSQL instance and write/read data to/from a table (in the following I will call this table my_test_table).

You will need:

• Visual Studio Community www.visualstudio.com/
• PostgreSQL for Windows: www.enterprisedb.com  (be sure to select pgAdmin3 during the installation, it will make your life easier)
• The Npgsql package: www.npgsql.org 

Here is the definition of my_table (I used pgAdmin to create it)

CREATE TABLE public.my_table
(
  id integer NOT NULL,
  extref integer NOT NULL,
  val numeric NOT NULL,
  CONSTRAINT my_table_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.my_table
  OWNER TO postgres;


Install Npgsql from the Package Manager Console in VisualStudio:  

Tools -> NuGet Package Manager -> Package Manager Console 

In the command prompt type:

Install-Package Npgsql -Version 3.2.5   (or whatever new version is available here)



In VisualStudio create a simple console application and use this code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace TestDB
{
    class Program
    {

        static public void TestDB()
        {

            // Adapt for your configuration (port, Username, password etc)
            var connString = "Host=localhost;Port=5432;Username=postgres;Password=postgres;Database=postgres";

            using (var conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                // Insert some data

                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO my_table (id, extref, val) VALUES (@id, @extref, @val)";
                    cmd.Parameters.AddWithValue("id", 10);
                    cmd.Parameters.AddWithValue("extref", 10);
                    cmd.Parameters.AddWithValue("val", 150);
                    cmd.ExecuteNonQuery();
                }

                // Retrieve all rows
                Console.WriteLine("Reading from the DB table...");
                using (var cmd = new NpgsqlCommand("SELECT * FROM my_test_table", conn))
                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                    {
                        string readLine = string.Format("id={0}, extref={1}, value={2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
                        Console.WriteLine(readLine);
                    }
            }

        }

        static void Main(string[] args)
        {
            TestDB();

            Console.ReadKey();

        }
    }
}

NOTE

If you try to run this program twice, you will get an exception because you will be trying to insert the same id.. twice and this is not possible because ID is a unique key.
So remember to delete the table's content each time you runt this application.


The list of available parameters for the connection string  is available here


References:
http://www.npgsql.org/doc/index.html

 

How the I2C protocol works

I found this interesting video explaining how the I2C or two-wire interface works.
Since this is a very popular protocol, you will meet soon or later in the embedded systems, so it's extremely worth to learn it!

http://howtomechatronics.com/tutorials/arduino/how-i2c-communication-works-and-how-to-use-it-with-arduino/

Enjoy!

Appunti di Informatica Libera [pdf]

Ecco qui il link al PDF di Appunti di Informatica Libera (precedentemente noti come Appunti Linux). L'ultimo aggiornamento è del 11.11.2013.
Opera monumentale!

ftp://na.mirror.garr.it/mirrors/appuntilinux/2013.11.11/a2-2013.11.11.pdf

http://a2.pluto.it/a2/a0.pdf


Altri formati:

ftp://na.mirror.garr.it/mirrors/appuntilinux/2013.11.11/


Online

tic and toc functions in C++ (millisecond resolution)

This is a newer version of my original post about Matlab-like tic/toc functions in C++

These new versions of the TIC/TOC functions have millisecond resolution while the old ones rounded the time difference to the seconds.

Here is the the new  tic_toc.h header:


#ifndef TIC_TOC_H
#define TIC_TOC_H


#include <iostream>
#include <chrono>


typedef std::chrono::high_resolution_clock Clock;
typedef std::chrono::milliseconds milliseconds;


static Clock::time_point t0 = Clock::now();

void tic()
{
t0 = Clock::now();
}


void toc()
{
    Clock::time_point t1 = Clock::now();
    milliseconds ms = std::chrono::duration_cast<milliseconds>(t1 - t0);
    std::cout <<"Elapsed time is "<< ms.count() << " milliseconds\n";
}


#endif

// happy coding and performance testing :)

Undefined functions while using struct and optim packages in Octave

If you have encountered errors like these in octave:


error: 'cell2fields' undefined near line 939 column 11
error: called from
    __nonlin_residmin__> at line -1 column -1
    __lm_svd__ at line 191 column 9
    __nonlin_residmin__ at line 1128 column 21
    nonlin_curvefit at line 83 column 18
    nlinfit at line 169 column 18

OR these

error: '__collect_constraints__' undefined near line 152 column 7
error: called from
    __nonlin_residmin__ at line 151 column 48
    nonlin_curvefit at line 83 column 18
    nlinfit at line 169 column 18



mean that NOT all the functions in the packages struct or optim have been correctly imported into Octave.


istread of importing the single folders in the Octave's path:


addpath('/home/eddie/octave/struct-1.0.14') % NOT ENOUGH!!!
addpath('/home/eddie/octave/optim-1.5.2')   % NOT ENOUGH!!!


just use the pkg command:


pkg load struct ;
pkg load optim ;

That's it. Happy scientific programming :)