private void btnImportFromExcel_Click(object sender, EventArgs e)
{
try
{
string fileName = string.Empty;
var openFileDialog = new OpenFileDialog();
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
fileName = openFileDialog.FileName;
}
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
string query = null;
string connString = "";
string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
string strFileType = System.IO.Path.GetExtension(openFileDialog.FileName).ToString().ToLower();
if (strFileType.Trim() == ".xls")
{
MessageBox.Show("'xls' is not supported. Please choose the 'xlsx' file extension.");
}
else
{
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed) conn.Open();
string SpreadSheetName = "";
DataTable ExcelSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
query = "SELECT * FROM [" + SpreadSheetName + "]";
cmd = new OleDbCommand(query, conn);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
}
}
catch (Exception ex)
{
MessageBox.Show("The Import file is failed. \n" + ex.ToString());
}
}
Import Excel file
最新推荐文章于 2021-12-23 19:18:26 发布