代码如下:
usingSystem;
usingSystem.Data;
usingSystem.Data.OleDb;
namespaceZFSoft.Joint
{
publicclassExcelIO
{
privateint_ReturnStatus;
privatestring_ReturnMessage;
///<summary>
///执行返回状态
///</summary>
publicintReturnStatus
{
get
{
return_ReturnStatus;
}
}
///<summary>
///执行返回信息
///</summary>
publicstringReturnMessage
{
get
{
return_ReturnMessage;
}
}
publicExcelIO()
{
}
///<summary>
///导入EXCEL到DataSet
///</summary>
///<paramname=”fileName”>Excel全路径文件名</param>
///<returns>导入成功的DataSet</returns>
publicDataTableImportExcel(stringfileName)
{
//判断是否安装EXCEL
Microsoft.Office.Interop.Excel.ApplicationxlApp=newMicrosoft.Office.Interop.Excel.Application();
if(xlApp==null)
{
_ReturnStatus=-1;
_ReturnMessage=”无法创建Excel对象,可能您的计算机未安装Excel”;
returnnull;
}
//判断文件是否被其他进程使用
Microsoft.Office.Interop.Excel.Workbookworkbook;
try
{
&nb sp; workbook=xlApp.Workbooks.Open(fileName,0,false,5,””,””,false,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,””,true,false,0,true,1,0);
}
catch
{
_ReturnStatus=-1;
_ReturnMessage=”Excel文件处于打开状态,请保存关闭”;
returnnull;
}
//获得所有Sheet名称
intn=workbook.Worksheets.Count;
string[]SheetSet=newstring[n];
System.Collections.ArrayListal=newSystem.Collections.ArrayList();
for(inti=1;i<=n;i++)
{
SheetSet[i-1]=((Microsoft.Office.Interop.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
DataSetds=newDataSet();
DataTabletable=newDataTable();
stringconnStr=”Provider=Microsoft.Jet.OLEDB.4.0;DataSource=”+fileName+”;ExtendedProperties=Excel8.0″;
using(OleDbConnectionconn=newOleDbConnection(connStr))
{
conn.Open();
& nbsp; OleDbDataAdapterda;
stringsql=”select*from[“+SheetSet[0]+”$]”;
da=newOleDbDataAdapter(sql,conn);
da.Fill(ds,SheetSet[0]);
da.Dispose();
table=ds.Tables[0];
conn.Close();
conn.Dispose();
}
returntable;
}
///<summary>
///把DataTable导出到EXCEL
///</summary>
///<paramname=”reportName”>报表名称</param>
///<paramname=”dt”>数据源表</param>
///<paramname=”saveFileName”>Excel全路径文件名</param>
///<returns>导出是否成功</returns>
publicboolExportExcel(stringreportName,System.Data.DataTabledt,stringsaveFileName)
{
if(dt==null)
{
_ReturnStatus=-1;
_ReturnMessage=”数据集为空!”;
returnfalse;
}
boolfileSaved=false;
Microsoft.Office.Interop.Excel.ApplicationxlApp=newMicrosoft.Office.Interop.Excel.Application();
if(xlApp==null)
{
_ReturnStatus=-1;
_ReturnMessage=”无法创建Excel对象,可能您的计算机未安装Excel”;
returnfalse;
}
Microsoft.Office.Interop.Excel.Workbooksworkbooks=xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbookworkbook=workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size=10;
Microsoft.Office.Interop.Excel.Rangerange;
longtotalCount=dt.Rows.Count;
longrowRead=0;
floatpercent=0;
worksheet.Cells[1,1]=reportName;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1,1]).Font.Size=12;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1,1]).Font.Bold=true;
//写入字段
for(inti=0;i<dt.Columns.Count;i++)
{
worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName;
range=(Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2,i+1];
range.Interior.ColorIndex=15;
range.Font.Bold=true;
}
//写入数值
for(intr=0;r<dt.Rows.Count;r++)
{
for(inti=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(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,null);
if(dt.Rows.Count>0)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex=Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
& nbsp; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight=Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
if(dt.Columns.Count>1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight=Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
//保存文件
if(saveFileName!=””)
{
try
{
workbook.Saved=true;
workbook.SaveCopyAs(saveFileName);
fileSaved=true;
}
catch(Exceptionex)
{
fileSaved=false;
_ReturnStatus=-1;
_ReturnMessage=”导出文件时出错,文件可能正被打开!n”+ex.Message;
}
}
else
{
fileSaved=false;
}
//释放Excel对应的对象
if(range!=null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range=null;
& nbsp; }
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();
returnfileSaved;
}
}
}
上述就是C#学习教程:C#导入导出EXCEL文件的代码实例分享的全部内容,如果对大家有所用处且需要了解更多关于C#学习教程,希望大家多多关注—计算机技术网(www.ctvol.com)! 您可能感兴趣的文章:C#数据导入/导出Excel文件及winForm导出Execl
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/cdevelopment/905464.html