Why does the best model in the training set have the worst test result? I have trained eight models using 10-fold cross-validation, and evaluated the models by using resampling technique as described here. The result shows that SVM with sigmoid kernel (SVM-s) and random forest (RF) are the best models, while penalised regressions had the lowest performance:

However, when I predicted the target variable by using the test set, the ROC curves showed very different ranking of model performance. The SVM model turned out to have the lowest AUC, and the penalised regressions had outperformed naive Bayes, neural network and CART.
AUC:
Lasso, RF, Elastic net, Ridge: 0.75;
Naive Bayes, neural network: 0.70;
Classification tree, SVM: 0.65.
Are there any good explanations for almost opposite model performance?

Yes, this is a classic example of "overfitting." At a high level, this means that your model is "learning" the training set so well that it's "memorized" features that don't work as well in the testing set. In fact, powerful classifiers like deep neural networks will often get 100% accuracy on the training set, and nowhere near that figure on the validation set.
Ideally, when selecting a model, we want our model to 1) learn during training and 2) generalize to unseen data (validation set). We'd like to see a model that has good training performance, and a small gap between train and validation performance. For your metric (AUC-ROC), I'd calculate the area between curves as discussed here to quantify how much your models are overfitting so you can make a better decision.
I've left some intuitive (hopefully) explanations for this effect below as well for your reference.
Some Intuition
Intuition with model accuracy. I'm going to talk about this in the context of accuracy. I think the below image from Wikipedia demonstrates the general idea quite well: suppose you're doing a binary classification task (separate blue and red data points). We see the training set here. The green classifier has 100% accuracy: it draws a (very twisty) boundary between the two classes.
Green classifier overfits. However, we might intuit instead that the separation between red and blue data points is better explained by the black boundary. Based on this intuition, it seems like the green classifier "overcomplicated" the problem: there's going to be some regions, which may appear in our test set, where the simpler explanation (black boundary) works better! The green classifier is very sensitive to small changes in the data.

Bias-Variance Tradeoff
That "almost-opposite" effect that you notice here is the bias-variance tradeoff, which can be derived by decomposing the mean squared error of a model. The bias is the error resulting from inherent limitations/assumptions in the model; variance is the sensitivity of the model to small data changes.
Intuition: why is there a tradeoff? A "simple" model may not be able to capture the underlying differences between data classes well (high bias), but isn't as sensitive to small changes in the data (low variance), while a complex model might be able to separate data classes well (low bias) but runs the risk of memorizing noise in training data, which is quite brittle (high variance).
Some of the models you've posted are overfitting, demonstrating low bias and high variance. Regularized regression methods like ridge or LASSO are specifically designed to combat overfitting, so I'm not too surprised by your results.
