通州北大青鳥學(xué)校學(xué)術(shù)部講解：C#中Excel數(shù)據(jù)的導(dǎo)入、導(dǎo)出
///
/// 導(dǎo)出到 Excel 文件
///
/// 含完整路徑
/// 含字段標(biāo)題名
public void ExpExcel(string fileName ,DataTable dataTable)
{
Excel.ApplicationClass apc =new Excel.ApplicationClass();
apc.Visible = false ;
Excel.Workbook wkbook = apc.Workbooks.Add( true ) ;
Excel.Worksheet wksheet = (Excel.Worksheet)wkbook.ActiveSheet;
int rowIndex = 2;
int colIndex = 1;
wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).NumberFormat = "@";
//取得列標(biāo)題
foreach (DataColumn dc in dataTable.Columns)
{
colIndex ++;
wksheet.Cells[1,colIndex] = dc.ColumnName;
}
//取得表格中數(shù)據(jù)（通州北大青鳥學(xué)校）
foreach (DataRow dr in dataTable.Rows)
{
colIndex = 1;
foreach (DataColumn dc in dataTable.Columns)
{
if(dc.DataType == System.Type.GetType("System.DateTime"))
{
apc.Cells[rowIndex,colIndex] = "'"+(Convert.ToDateTime(dr[dc.ColumnName].ToString())).ToString("yyyy-MM-dd");
}
else
if(dc.DataType == System.Type.GetType("System.String"))
{
apc.Cells[rowIndex,colIndex] = "'"+dr[dc.ColumnName].ToString();
}
else
{
apc.Cells[rowIndex,colIndex] = "'"+dr[dc.ColumnName].ToString();
}
wksheet.get_Range(apc.Cells[rowIndex,colIndex],apc.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
colIndex++;
}
rowIndex++;
}
//設(shè)置表格樣式（通州北大青鳥學(xué)校）
wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Interior.ColorIndex = 20;
wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Font.ColorIndex = 3;
wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Borders.Weight = Excel.XlBorderWeight.xlThin;
wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).Columns.AutoFit();
if(File.Exists(fileName))
{
File.Delete(fileName);
}
wkbook.SaveAs( fileName ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
wkbook.Close(Type.Missing,Type.Missing,Type.Missing);
apc.Quit();
wkbook = null;
apc = null;
GC.Collect();
}
///
/// 從Excel導(dǎo)入帳戶(逐單元格讀取)
///
/// 完整路徑名（通州北大青鳥學(xué)校）
public IList ImpExcel(string fileName)
{
IList alExcel = new ArrayList();
UserInfo userInfo = new UserInfo();
Excel.Application app;
Excel.Workbooks wbs;
Excel.Worksheet ws;
app = new Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws= (Excel.Worksheet)app.Worksheets.get_Item(1);
int a = ws.Rows.Count;
int b = ws.Columns.Count;
for ( int i = 2; i < 4; i++)
{
for ( int j = 1; j < 21; j++)
{
Excel.Range range = ws.get_Range(app.Cells[i,j],app.Cells[i,j]);
range.Select();
alExcel.Add( app.ActiveCell.Text.ToString() );
}
}
return alExcel;
}
///
/// 從Excel導(dǎo)入帳戶(新建oleDb連接,Excel整表讀取,適于無合并單元格時(shí))
///
/// 完整路徑名
///
public DataTable ImpExcelDt (string fileName)
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
string strCom = " SELECT * FROM [Sheet1$] " ;
myConn.Open ( ) ;
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
DataSet myDataSet = new DataSet ( ) ;
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
myConn.Close ( ) ;
DataTable dtUsers = myDataSet.Tables[0];
return dtUsers;
}
（通州北大青鳥學(xué)校）
dataGrid中顯示：
DataGrid1.DataMember= "[Sheet1$]" ;
DataGrid1.DataSource = myDataSet ;
（通州北大青鳥學(xué)校）
