/**/ /// <summary>/// 导出到 Excel 文件/// </summary>/// <param name="fileName">含完整路径</param>/// <param name="dataTable">含字段标题名</param> 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 = "@"; //取得列标题 foreach (DataColumn dc in dataTable.Columns) { colIndex ++; wksheet.Cells[1,colIndex] = dc.ColumnName; } //取得表格中数据 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++; } //设置表格样式 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();} /**/ /// <summary>/// 从Excel导入帐户(逐单元格读取)/// </summary>/// <param name="fileName">完整路径名</param> 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;} /**/ /// <summary>/// 从Excel导入帐户(新建oleDb连接,Excel整表读取,适于无合并单元格时)/// </summary>/// <param name="fileName">完整路径名</param>/// <returns></returns> 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;} dataGrid中显示:DataGrid1.DataMember = " [Sheet1$] " ;DataGrid1.DataSource = myDataSet ;