excel upload faster

참조사이트

• https://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled
• https://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable/23638843

Normal ConnectionString : (work for xls files)

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\""

Office 2007 ConnectionString : (work for xlsx files)

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\""

 

private void UseOlebForLoadExcel()
{
	string filePath = "C:\\99.download\\샘플.xlsx";

	int nOutputRow = 0;
	string sSheetName = null;
	string sConnection = null;
	DataTable dtTablesList = default(DataTable);
	OleDbCommand oleExcelCommand = default(OleDbCommand);
	OleDbDataReader oleExcelReader = default(OleDbDataReader);
	OleDbConnection oleExcelConnection = default(OleDbConnection);


	sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ filePath + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";

	oleExcelConnection = new OleDbConnection(sConnection);
	oleExcelConnection.Open();

	dtTablesList = oleExcelConnection.GetSchema("Tables");

	if (dtTablesList.Rows.Count > 0)
	{
		SheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
	}

	dtTablesList.Clear();
	dtTablesList.Dispose();

	if (!string.IsNullOrEmpty(sSheetName))
	{
		oleExcelCommand = oleExcelConnection.CreateCommand();
		oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
		oleExcelCommand.CommandType = CommandType.Text;
		oleExcelReader = oleExcelCommand.ExecuteReader();
		nOutputRow = 0;

		while (oleExcelReader.Read())
		{
			// reader.GetDouble(0);
			var index = grid.Rows.Add();
			grid.Rows[index].Cells["filePath"].Value = oleExcelReader.GetString(0);
			grid.Rows[index].Cells["fileName"].Value = oleExcelReader.GetString(0);
			grid.Rows[index].Cells["lineNo"].Value = oleExcelReader.GetString(0);
			grid.Rows[index].Cells["fileCnts"].Value = oleExcelReader.GetString(0);
		}
		oleExcelReader.Close();
	}
	oleExcelConnection.Close();
}