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.executenonquery();
  
  
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.executenonquery();
  
  
}
  
  

  
  
con.close();
  
  
}
  
  
}