C#导入导出EXCEL文件的代码实例

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
            {
                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();
                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;
                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;
            }
            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;
        }
    }
}