C#导入导出数据到Excel的通用类代码详解编程语言

Excel文件导入导出,需引用Microsoft Excel 11.0 Object Library

/////////////////////////////////////////////////////////////////////////// 
//Purpose：Excel文件导入导出,需引用Microsoft Excel 11.0 Object Library 
//Author: Dangmy 
//Date: 2007-03-09 
//Version: 1.0 
/////////////////////////////////////////////////////////////////////////// 
public class ExcelIO 
{ 
private int _ReturnStatus; 
private string _ReturnMessage; 
/// <summary> 
/// 执行返回状态 
/// </summary> 
public int ReturnStatus 
{ 
get{return _ReturnStatus;} 
} 
/// <summary> 
/// 执行返回信息 
/// </summary> 
public string ReturnMessage 
{ 
get{return _ReturnMessage;} 
} 
public ExcelIO() 
{ 
} 
/// <summary> 
/// 导入EXCEL到DataSet 
/// </summary> 
/// <param name="fileName">Excel全路径文件名</param> 
/// <returns>导入成功的DataSet</returns> 
public DataSet ImportExcel(string fileName) 
{ 
//判断是否安装EXCEL 
Excel.Application xlApp=new Excel.ApplicationClass();           
if(xlApp==null) 
{ 
_ReturnStatus = -1; 
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel"; 
return null; 
}       
//判断文件是否被其他进程使用            
Excel.Workbook workbook;                
try 
{ 
workbook = xlApp.Workbooks.Open(fileName,0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0); 
} 
catch 
{ 
_ReturnStatus = -1; 
_ReturnMessage = "Excel文件处于打开状态，请保存关闭"; 
return null; 
}       
//获得所有Sheet名称 
int n = workbook.Worksheets.Count; 
string[] SheetSet = new string[n]; 
System.Collections.ArrayList al = new System.Collections.ArrayList(); 
for(int i=1; i<=n; i++) 
{ 
SheetSet[i-1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name; 
} 
//释放Excel相关对象 
workbook.Close(null,null,null);         
xlApp.Quit(); 
if(workbook != null) 
{ 
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); 
workbook = null; 
} 
if(xlApp != null) 
{ 
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); 
xlApp = null; 
}   
GC.Collect(); 
//把EXCEL导入到DataSet 
DataSet ds = new DataSet();         
string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+ fileName +";Extended Properties=Excel 8.0" ; 
using(OleDbConnection conn = new OleDbConnection (connStr)) 
{ 
conn.Open(); 
OleDbDataAdapter da; 
for(int i=1; i<=n; i++) 
{ 
string sql = "select * from ["+ SheetSet[i-1] +"$] "; 
da = new OleDbDataAdapter(sql,conn); 
da.Fill(ds,SheetSet[i-1]);  
da.Dispose(); 
}               
conn.Close(); 
conn.Dispose(); 
}               
return ds; 
} 
/// <summary> 
/// 把DataTable导出到EXCEL 
/// </summary> 
/// <param name="reportName">报表名称</param> 
/// <param name="dt">数据源表</param> 
/// <param name="saveFileName">Excel全路径文件名</param> 
/// <returns>导出是否成功</returns> 
public bool ExportExcel(string reportName,DataTable dt,string saveFileName) 
{ 
if(dt==null) 
{ 
_ReturnStatus = -1; 
_ReturnMessage = "数据集为空！"; 
return false;           
} 
bool fileSaved=false; 
Excel.Application xlApp=new Excel.ApplicationClass();   
if(xlApp==null) 
{ 
_ReturnStatus = -1; 
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel"; 
return false; 
} 
Excel.Workbooks workbooks=xlApp.Workbooks; 
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); 
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
worksheet.Cells.Font.Size = 10; 
Excel.Range range; 
long totalCount=dt.Rows.Count; 
long rowRead=0; 
float percent=0; 
worksheet.Cells[1,1]=reportName; 
((Excel.Range)worksheet.Cells[1,1]).Font.Size = 12; 
((Excel.Range)worksheet.Cells[1,1]).Font.Bold = true; 
//写入字段 
for(int i=0;i<dt.Columns.Count;i++) 
{ 
worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName; 
range=(Excel.Range)worksheet.Cells[2,i+1]; 
range.Interior.ColorIndex = 15; 
range.Font.Bold = true; 
} 
//写入数值 
for(int r=0;r<dt.Rows.Count;r++) 
{ 
for(int i=0;i<dt.Columns.Count;i++) 
{ 
worksheet.Cells[r+3,i+1]=dt.Rows[r][i].ToString(); 
} 
rowRead++; 
percent=((float)(100*rowRead))/totalCount; 
} 
range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]); 
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null); 
if( dt.Rows.Count > 0) 
{ 
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; 
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous; 
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin; 
} 
if(dt.Columns.Count>1) 
{ 
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic; 
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; 
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; 
} 
//保存文件 
if(saveFileName!="") 
{ 
try 
{ 
workbook.Saved =true; 
workbook.SaveCopyAs(saveFileName); 
fileSaved=true; 
} 
catch(Exception ex) 
{ 
fileSaved=false; 
_ReturnStatus = -1; 
_ReturnMessage = "导出文件时出错,文件可能正被打开！/n"+ex.Message; 
} 
} 
else 
{ 
fileSaved=false; 
}           
//释放Excel对应的对象 
if(range != null) 
{ 
System.Runtime.InteropServices.Marshal.ReleaseComObject(range); 
range = null; 
} 
if(worksheet != null) 
{ 
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); 
worksheet = null; 
} 
if(workbook != null) 
{ 
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); 
workbook = null; 
} 
if(workbooks != null) 
{ 
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); 
workbooks = null; 
}               
xlApp.Application.Workbooks.Close(); 
xlApp.Quit(); 
if(xlApp != null) 
{ 
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); 
xlApp = null; 
} 
GC.Collect(); 
return fileSaved; 
} 
}