The code below will remove all special characters from a string. Allowed characters are A-Z (uppercase or lowercase), numbers (0-9), underscore (_) and the dot sign (.)
public static string RemoveSpecialCharacters(string s)
{
StringBuilder stringBuilder = new StringBuilder(s.Length);
foreach (char c in s)
{
if ((c >= '0' && c <= '9') || (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || c == '.' || c == '_')
{
stringBuilder.Append(c);
}
}
return stringBuilder.ToString();
}
Or using Regular Expressions
using System.Text.RegularExpressions;
public static string RemoveSpecialCharacters(string s)
{
return Regex.Replace(s, "[^a-zA-Z0-9_.]+", "", RegexOptions.Compiled);
}
Reset all Controls (Textbox, ComboBox, CheckBox, ListBox) in a Windows Form using C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace Common
{
public class Utilities
{
public static void ResetAllControls(Control form)
{
foreach (Control control in form.Controls)
{
if (control is TextBox)
{
TextBox textBox = (TextBox)control;
textBox.Text = null;
}
if (control is ComboBox)
{
ComboBox comboBox = (ComboBox)control;
if (comboBox.Items.Count > 0)
comboBox.SelectedIndex = 0;
}
if (control is CheckBox)
{
CheckBox checkBox = (CheckBox)control;
checkBox.Checked = false;
}
if (control is ListBox)
{
ListBox listBox = (ListBox)control;
listBox.ClearSelected();
}
}
}
}
}
Calling method from code behind form:
private void button1_Click(object sender, EventArgs e)
{
Common.Utilities.ResetAllControls(this);
}
Add a reference “Microsoft.Office.Interop.Excel” into project
Reading and Writing Data to Excel using Microsoft.Office.Interop.Excel
using Excel=Microsoft.Office.Interop.Excel;
using System.Reflection;
Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
Excel.Workbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
Excel.Worksheet worksheet;
// Opening excel file
workbook = excelApp.Workbooks.Open("D:\\test.xlsx", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
// Get first Worksheet
worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
// Setting cell values
((Excel.Range)worksheet.Cells["1", "A"]).Value2 = "5";
((Excel.Range)worksheet.Cells["2", "A"]).Value2 = "7";
workbook.Save();
workbook.Close(0, 0, 0);
excelApp.Quit();
Add a reference “Microsoft.Office.Interop.Excel” into project
Reading Excel file and Binding to DataGridView
Microsoft.Office.Interop.Excel.Application excelApp;
Microsoft.Office.Interop.Excel.Workbook workbook;
Microsoft.Office.Interop.Excel.Worksheet worksheet;
Microsoft.Office.Interop.Excel.Range range;
excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// Opening Excel file
workbook = excelApp.Workbooks.Open(txtPath.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);
range = worksheet.UsedRange;
int column = 0;
int row = 0;
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("CustomerID");
dt.Columns.Add("CompanyName");
dt.Columns.Add("ContactName");
for (row = 2; row <= range.Rows.Count; row++)
{
DataRow dr = dt.NewRow();
for (column = 1; column <= range.Columns.Count; column++)
{
dr[column - 1] = (range.Cells[row, column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
}
dt.Rows.Add(dr);
}
workbook.Close(true, null, null);
excelApp.Quit();
// Binding to DataGridView
dataGridView1.DataSource = dt;
Example
This article instructs you to get Excel Sheet Names using System.Data.OleDb in C#
using System.Data.Common;
using System.Data.OleDb;
Get Excel Sheet Names
public static List<string> GetSheetNames(string path)
{
List<string> sheets = new List<string>();
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", path);
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
connection.Open();
DataTable tbl = connection.GetSchema("Tables");
connection.Close();
foreach (DataRow row in tbl.Rows)
{
string sheetName = (string)row["TABLE_NAME"];
if (sheetName.EndsWith("$"))
{
sheetName = sheetName.Substring(0, sheetName.Length - 1);
}
sheets.Add(sheetName);
}
return sheets;
}
Loading data into GridView
private void btnLoadData_Click(object sender, EventArgs e)
{
if (System.IO.File.Exists(txtPath.Text))
{
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", txtPath.Text);
string query = String.Format("select * from [{0}$]", comboBox1.SelectedItem);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
dataGridView1.DataSource = dataSet.Tables[0];
}
else
{
MessageBox.Show("No File is Selected");
}
}
Complete Source Code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.OleDb;
namespace Get_Excel_Sheet_Names
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
DialogResult dlgResult = dlg.ShowDialog();
if (dlgResult == DialogResult.OK)
{
txtPath.Text = dlg.FileName;
}
comboBox1.DataSource = GetSheetNames(txtPath.Text);
}
public static List<string> GetSheetNames(string path)
{
List<string> sheets = new List<string>();
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", path);
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
connection.Open();
DataTable tbl = connection.GetSchema("Tables");
connection.Close();
foreach (DataRow row in tbl.Rows)
{
string sheetName = (string)row["TABLE_NAME"];
if (sheetName.EndsWith("$"))
{
sheetName = sheetName.Substring(0, sheetName.Length - 1);
}
sheets.Add(sheetName);
}
return sheets;
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (System.IO.File.Exists(txtPath.Text))
{
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", txtPath.Text);
string query = String.Format("select * from [{0}$]", comboBox1.SelectedItem);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
dataGridView1.DataSource = dataSet.Tables[0];
}
else
{
MessageBox.Show("No File is Selected");
}
}
}
}