I need to open view and Edit an Excel File within a Windows form.
I tried implementing the code for opening the Excel file in Gridview and then edit it, but that becomes too much lengthy process, hence I need to find a convenient way of View and Edit an Excel File in WindowsForms.
Any help would be appreciated.
Thanks,
You might want to use OLEDB to read and query excel the below method returns a specific sheet in an excel file as a data table, which later you can set as Data source to your gridview.
public static DataTable ConvertExcelToDataTableGroupSubGroup(string FileName)
{
DataTable dtResult = null;
int totalSheet = 0; //No of sheets on excel file
using (OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
{
objConn.Open();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = string.Empty;
if (dt != null)
{
var tempDataTable = (from dataRow in dt.AsEnumerable()
where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
select dataRow).CopyToDataTable();
dt = tempDataTable;
totalSheet = dt.Rows.Count;
// Sheet from first index
sheetName = dt.Rows[1]["TABLE_NAME"].ToString();
}
cmd.Connection = objConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "excelData");
dtResult = ds.Tables["excelData"];
objConn.Close();
return dtResult; //Returning Dattable
}
}
OLEDB also gives you the flexibility to write SQL queries on excel.
First, you need to install 2 Library in NuGet
https://github.com/ExcelDataReader/ExcelDataReader
It can be possible read and write *.XLS, *.XLSX without Office or OLE Connection.
And this is source what I'm using :)
using ExcelDataReader;
private DataSet ds;
IExcelDataReader reader = null;
OpenFileDialog openFileDialog = new OpenFileDialog();
private void btnOpen_Click(object sender, EventArgs e)
{
openFileDialog.Filter = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx";
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
tbPath.Text = openFileDialog.FileName;
var file = new FileInfo(tbPath.Text);
try
{
using (var stream = new FileStream(tbPath.Text, FileMode.Open))
{
if (reader != null) { reader = null; }
// Judge it is .xls or .xlsx
if (file.Extension == ".xls") { reader = ExcelReaderFactory.CreateBinaryReader(stream); }
else if (file.Extension == ".xlsx") { reader = ExcelReaderFactory.CreateOpenXmlReader(stream); }
if (reader == null) { return; }
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false,
ReadHeaderRow = (rowReader) => {
rowReader.Read();
},
// Gets or sets a callback to determine whether to include the
// current row in the DataTable.
FilterRow = (rowReader) => {
return true;
},
}
});
var tablenames = GetTablenames(ds.Tables);
cbSheet.DataSource = tablenames;
listSheet.DataSource = tablenames;
if (cbSheet.Items.Count == 1)
{
cbSheet.SelectedIndex = 0;
}
}
cbSheet.Enabled = true;
btnOpen.Enabled = true;
}
catch (Exception ex)
{
tbPath.Text = "";
cbSheet.Enabled = false;
btnOpen.Enabled = true;
MessageBox.Show(ex.Message);
}
}
}
