ASP. NET Excel file read/write code

1. Read the data connection string of the excel file. You can set the connection string to provider = microsoft. jet. oledb.4.0; data source = d: myexcel.xls; extended properties = excel 8.0 ;. To read the excel files in .xlsxand .xls format, set the connection character to "provider = microsoft. ace. oledb.12.0; data source = d: myexcel.xls; extended properties = excel 12.0.

2. Read the name of the sheet in the excel table.

   

    

     

      Public static string [] getexcelsheetnames (string filepath)
      
{
      
String constring = "provider = microsoft. ace. oledb.12.0; data source =" + filepath + "; extended properties =" excel 12.0; hdr = yes "";
      
Oledbconnection con = new oledbconnection (constring );
      
Con. open ();
      
Datatable dt = con. getoledbschematable (oledbschemaguid. tables, null );
      
Con. close ();
      
If (dt = null)
      
{
      
Return null;
      
}
      
String [] excelsheetnames = new string [dt. rows. count];
      
Int I = 0;
      
Foreach (datarow dr in dt. rows)
      
{
      
Excelsheetnames [I ++] = dr ["table_name"]. tostring ();
      
}
      
Return excelsheetnames;
      
}
     
    
3. Read the excel file.
   

   

    

     

      Public static datatable readexcel (string filepath, string constr)
      
{
      
String constring = "provider = microsoft. ace. oledb.12.0; data source =" + filepath + "; extended properties =" excel 12.0; hdr = yes "";
      
Oledbconnection con = new oledbconnection (constring );
      
Oledbdataadapter oda = new oledbdataadapter (constr, con );
      
Datatable dt = new datatable ();
      
Con. open ();
      
Oda. fill (dt );
      
Con. close ();
      
Return dt;
      
}
     
    
4. Write data into an excel file.
   

   

    

     

      Public static void writeexcel (string filepath, datatable dt)
      
{
      
If (file. exists (filepath ))
      
{
      
File. delete (filepath );
      
}
      
Else
      
{
      
String constring = "provider = microsoft. ace. oledb.12.0; data source =" + filepath + "; extended properties =" excel 12.0; hdr = yes "";
      
Oledbconnection con = new oledbconnection (constring );
      
String createsql = "create table sheet1 (";
      
Foreach (datacolumn dc in dt. columns)
      
{
      
Createsql + = dc. columnname + "varchar ,";
      
}
      
Createsql = createsql. substring (0, createsql. length-1) + ")";
      

      
Oledbcommand cmd = new oledbcommand (createsql, con );
      
Con. open ();
      
Cmd.exe cutenonquery ();
      
Foreach (datarow dr in dt. rows)
      
{
      
String insertsql = "insert into sheet1 values (";
      
Foreach (datacolumn dc in dt. columns)
      
{
      
Insertsql + = "'" + dr [dc]. tostring () + "',";
      
}
      
Insertsql = insertsql. substring (0, insertsql. length-1) + ")";
      
Cmd = new oledbcommand (insertsql, con );
      
Cmd.exe cutenonquery ();
      
}
      

      
Con. close ();
      
}
      
}