Excel data import into SQL Server

There are 2 basic scenarios, all without the need to install Office

Ole DB Read + bulkcopy

Get Excel Individual SheetName

//Connection Stringstringstrconn ="provider=microsoft.jet.oledb.4.0;"+"Data source="+ Path +";"+"Extended Properties=excel 8.0;"; OleDbConnection Conn=NewOleDbConnection (strconn); Conn. Open (); //returns the schema for Excel, including the name, type, creation time, and modification time of each sheet tableDataTable dtsheetname = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables,New Object[] {NULL,NULL,NULL,"Table" }); //an array of strings that contain table names in Excelstring[] Strtablenames =New string[DtSheetName.Rows.Count]; for(intK =0; K < DtSheetName.Rows.Count; k++) {Strtablenames[k]= dtsheetname.rows[k]["table_name"]. ToString ();}returnStrtablenames;

Npoi Read + bulkcopy

Get Excel Individual SheetName

using(FileStream fs = File.openread (Txtpath.text))//Open Myxls.xls File{hssfworkbook wk=NewHssfworkbook (FS); string[] Strtablenames =New string[wk.    Numberofsheets];  for(inti =0; I < wk. Numberofsheets; i++)//Numberofsheets is the total number of tables in Myxls.xls{Strtablenames[i]=wk. Getsheetat (i).    SheetName; }    returnStrtablenames;}

SqlBulkCopy Data Write

using New System.Data.SqlClient.SqlBulkCopy (connectionString)) {    //bcp. Sqlrowscopied + = new System.Data.SqlClient.SqlRowsCopiedEventHandler (bcp_sqlrowscopied);    ; // Number of rows per transfer    ; // Number of lines for the progress prompt    Bcp. DestinationTableName = SheetName; // target table    Bcp. WriteToServer (ds. tables[0]);}

Excel data import into SQL Server