The great spirit consult Winform Excel2007 export data connection can customize the title of that kind of code

CodePudding user response:

NPOI, can set the font, color, size

CodePudding user response:

Use Microsoft. ACE. The OLEDB. 12.0 drivers

CodePudding user response:

Need to download and install Microsoft. ACE. The OLEDB.
12.0 driver

 
///
 

Excel spreadsheet data read///"' 

///"' & lt;/summary> 

///"' & lt; Param name="strExcelFilePath & gt;" Excel file & lt;/param> 

///"' & lt; Param name="SheetName & gt;" The workspace table name & lt;/param> 

///"' & lt; Param name="SqlString & gt;" SQL query & lt;/param> 

///"' & lt; Param name="lpDataTable & gt;" Return a list of (DataTable) data & lt;/param> 

///"' & lt; Param name="bRowTitle & gt;" Whether the first line of the title field name & lt;/param> 

///"' & lt; returns>  
Public bool GetExcelTable (string strExcelFilePath, string SheetName, string SqlString, ref DataTable lpDataTable, bool bRowTitle) 
{
Bool bFlag=true; 
Try 
{
//note: due to Microsoft. ACE. The OLEDB. 12.0 driver is 32 bit, so the compiler options must check the "preferred 32", otherwise an error: not registered on the local computer "Microsoft. ACE. The OLEDB. 12.0" provider 
//String strConnString="Provider=Microsoft. ACE. The OLEDB. 12.0; Data Source="& amp; StrExcelFilePath & amp; "; Extended Properties='Excel 8.0; HDR=YES '"; 
String strConnString=""; 
If (bRowTitle) 
//the first behavior title field name 
StrConnString="Provider=Microsoft. ACE. The OLEDB. 12.0. Data Source="+ strExcelFilePath +"; Extended Properties='Excel 12.0; HDR=YES '"; 
The else 
//the field name to: F1, F2, F3... 
StrConnString="Provider=Microsoft. ACE. The OLEDB. 12.0. Data Source="+ strExcelFilePath +"; Extended Properties='Excel 12.0; HDR=No '"; 
Using (OleDbConnection connection=new OleDbConnection (strConnString)) 
{
Connection. The Open (); 
The DataSet ds=null/* TODO Change to default (_) if this is not a reference type */. 
String mSQL=SqlString; 
OleDbDataAdapter odda=new OleDbDataAdapter (mSQL, connection); 
Ds=new DataSet (); 
Odda. The Fill (ds, SheetName); 
LpDataTable=ds. Tables (0); 

Connection. The Close (); 
Odda. The Dispose (); 
Odda=null; 
Ds. The Dispose (); 
Ds=null; 
}
}
The catch (Exception ex) 
{
BFlag=false; 
MessageBox. Show (ex. The Message, "Error", MessageBoxButtons. OK, MessageBoxIcon. Error); 
}
Return bFlag; 
}

Private void button1_Click (object sender, EventArgs e)
{
The DataTable dt.
String mSql="select * from work [table name $]".
Bool bVal=GetExcelTable (" D: \ Text XSL, "" work table name," mSql, ref dt, true);
}