注册 登录
  • 欢迎访问开心洋葱网站,在线教程,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站,欢迎加入开心洋葱 QQ群
  • 为方便开心洋葱网用户,开心洋葱官网已经开启复制功能!
  • 欢迎访问开心洋葱网站,手机也能访问哦~欢迎加入开心洋葱多维思维学习平台 QQ群
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏开心洋葱吧~~~~~~~~~~~~~!
  • 由于近期流量激增,小站的ECS没能经的起亲们的访问,本站依然没有盈利,如果各位看如果觉着文字不错,还请看官给小站打个赏~~~~~~~~~~~~~!

C#导入导出数据到Excel的通用类代码

OC/C/C++ 水墨上仙 3038次浏览 已收录 手机上查看

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;
     }
}


开心洋葱 , 版权所有丨如未注明 , 均为原创丨未经授权请勿修改 , 转载请注明C#导入导出数据到Excel的通用类代码
喜欢 (0)
[开心洋葱]
分享 (0)
关于作者:
水墨上仙
……
加载中……