WPF中对Excel文件的导入导出操作详解


        一、引言

        对Excel文件的导入和导出是软件一个常用模块,本文主要谈谈在WPF中对Excel文件的导入导出操作,在WPF中的操作方式和在Asp.net,Winform等.net相关技术对Excel的操作是大同小异的。导入Excel文件的思路是使用打开文件对话框,选择本地Excel文件得到文件路径,使用Excel相关类对此Excel文件进行读取,使Excel文件中的数据成为WPF某个数据展示控件(如DataGrid)的数据源;导出Excel文件的思路是将WPF某个数据展示控件(如DataGrid)写入内存建立的Excel文件里面,然后通过保存文件对话框选择保存文件的路径,将内存中Excel的文件保存到选择的路径;

        在wpf中打开文件对话框的类为Microsoft.Win32.OpenFileDialog,保存文件对话框的类为Microsoft.Win32.SaveFileDialog,注意,它们都是在Microsoft.Win32的命名空间里。在WPF中的Microsoft.Win32.OpenFileDialog,Microsoft.Win32.SaveFileDialog和在WinForm中的System.Windows.Forms.OpenFileDialog,System.Windows.Forms.SaveFileDialog在用法上基本一致,OpenFileDialog里面有很多和打开文件相关属性和方法事件,主要用到的属性有Filter(获取或设置筛选器字符串,用来确定在打开文件对话框显示的文件类型),FileName(打开文件对话框中选取的单个文件名,包含完整路径),SaveFileDialog用到比较多也是Filter和FileName,更多的成员信息可以参考MSDN上的说明。

       在VS2010中名为Microsoft.Office.Interop.Excel的程序集提供了对Excel操作的相关类,主要相关类有Application,Workbooks,Sheets,_Worksheet,Range 等。Application 对象表示 Excel 应用程序本身。Application 对象公开了大量有关正在运行的应用程序、应用于该实例的选项以及在该实例中打开的当前用户的对象的信息。Microsoft.Office.Interop.Excel.Workbook 类表示 Excel 应用程序中的单个工作簿。Microsoft.Office.Interop.Excel.Worksheet 对象是 Worksheets 集合的成员。Microsoft.Office.Interop.Excel.Worksheet 的许多属性、方法和事件与 Application 或 Microsoft.Office.Interop.Excel.Workbook 类提供的成员完全相同或相似。Sheets 集合作为 Microsoft.Office.Interop.Excel.Workbook 对象的属性,但是 Excel 中没有 Sheet 类。 Range类可以表示Excel中的单元格,行,列,含有一个或多个连续的单元格块。

        二、如何导入Excel中的数据

      导入Excel时,我们先要读取Excel,我们可以将Excel文件看成是一个数据库,连接Excel文件,我们可以像查询数据库一样查询Excel中的数据。

View Code
 /// <summary>
        
/// 读取Excel文件
        
/// </summary>
        
/// <param name="pPath"></param>
        
/// <returns></returns>
        public DataTable LoadExcel(string pPath)
        {

            
string connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2024;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;";
            connString 
+= "DBQ=" + pPath;
            OdbcConnection conn 
= new OdbcConnection(connString);
            OdbcCommand cmd 
= new OdbcCommand();
            cmd.Connection 
= conn;
            
//获取Excel中第一个Sheet名称,作为查询时的表名
            string sheetName = this.GetExcelSheetName(pPath);
            
string sql = "select * from [" + sheetName.Replace('.''#'+ "$]";
            cmd.CommandText 
= sql;
            OdbcDataAdapter da 
= new OdbcDataAdapter(cmd);
            DataSet ds 
= new DataSet();
            
try
            {
                da.Fill(ds);
                
return ds.Tables[0];
            }
            
catch (Exception x)
            {
                ds 
= null;
                
throw new Exception("从Excel文件中获取数据时发生错误!");
            }
            
finally
            {
                cmd.Dispose();
                cmd 
= null;
                da.Dispose();
                da 
= null;
                
if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn 
= null;
            }
        }

 

View Code
 private string GetExcelSheetName(string pPath)
        {
            
//打开一个Excel应用

            _excelApp 
= new Excel.Application();
            
if (_excelApp == null)
            {
                
throw new Exception("打开Excel应用时发生错误!");
            }
            _books 
= _excelApp.Workbooks;
            
//打开一个现有的工作薄
            _book = _books.Add(pPath);
            _sheets 
= _book.Sheets;
            
//选择第一个Sheet页
            _sheet  = (Excel._Worksheet)_sheets.get_Item(1);
            
string sheetName = _sheet.Name;

            ReleaseCOM(_sheet);
            ReleaseCOM(_sheets);
            ReleaseCOM(_book);
            ReleaseCOM(_books);
            _excelApp.Quit();
            ReleaseCOM(_excelApp);
            
return sheetName;
        }

 

 

View Code
 /// <summary>
        
/// 释放COM对象
        
/// </summary>
        
/// <param name="pObj"></param>
        private void ReleaseCOM(object pObj)
        {
            
try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
            }
            
catch
            {
                
throw new Exception("释放资源时发生错误!");
            }
            
finally
            {
                pObj 
= null;
            }
        }

       简单解释下这个连续字符串,Driver={Driver do Microsoft Excel(*.xls)} 这种连接写法不需要创建一个数据源DSN,DRIVERID表示驱动ID,Excel2003后都使用790,FIL表示Excel文件类型,Excel2007用excel 8.0,MaxBufferSize表示缓存大小,DBQ表示读取Excel的文件名(全路径)

        string sql = "select * f rom [" + sheetName.Replace('.', '#') + "$]";    查询某张工作薄中的某张工作表;

        System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj),这是在.NET环境里面释放COM对象的方法, 像Excel.Application这些对象都是经过包装使之可以在.NET环境中使用的COM对象,因为非托管代码释放内存的方式和托管代码不一样,所以在使用完COM对象后必须去通知COM对象运用它们的机制去释放内存。COM对象与.NET的交互是牵扯到多方面的知识,是一个复杂的庞大话题。有兴趣的同学可以翻阅MSDN参考。

      至此将Excel中某张工作表转换为DataTable后就很容易使之作为WPF控件的数据源了。

       三、导出数据到Excel

       将DataTable对象导出到Excel的工作表中,其方式是在内存中建立一个Excel对象实例,然后将DataTable中的数据填充到内存Excel工作表中,然后保存这个内存Excel对象到本地路径。

 

View Code
  /// <summary>
       
/// 保存到Excel
       
/// </summary>
       
/// <param name="excelName"></param>
        public void SaveToExcel(string excelName,DataTable dataTable)
        {
            
try
            {
                
if (dataTable != null)
                {
                    
if (dataTable.Rows.Count != 0)
                    {
                        Mouse.SetCursor(Cursors.Wait);
                        CreateExcelRef();
                        FillSheet(dataTable);
                        SaveExcel(excelName);
                        Mouse.SetCursor(Cursors.Arrow);
                    }
                }
            }
            
catch (Exception e)
            {
                MessageBox.Show(
"Error while generating Excel report");
            }
            
finally
            {
                ReleaseCOM(_sheet);
                ReleaseCOM(_sheets);
                ReleaseCOM(_book);
                ReleaseCOM(_books);
                ReleaseCOM(_excelApp);
            }
        }

 

View Code
 /// <summary>
        
/// 创建一个Excel程序实例
        
/// </summary>
        private void CreateExcelRef()
        {
            _excelApp 
= new Excel.Application();
            _books 
= (Excel.Workbooks)_excelApp.Workbooks;
            _book 
= (Excel._Workbook)(_books.Add(_optionalValue));
            _sheets 
= (Excel.Sheets)_book.Worksheets;
            _sheet 
= (Excel._Worksheet)(_sheets.get_Item(1));
        }
View Code
 /// <summary>
        
/// 将数据填充到内存Excel的工作表
        
/// </summary>
        
/// <param name="dataTable"></param>
        private void FillSheet(DataTable dataTable)
        {
            
object[] header = CreateHeader(dataTable);
            WriteData(header,dataTable);
        }
       

        
private void WriteData(object[] header,DataTable dataTable)
        {
            
object[,] objData = new object[dataTable.Rows.Count, header.Length];

            
for (int j = 0; j < dataTable.Rows.Count; j++)
            {
                var item 
= dataTable.Rows[j];
                
for (int i = 0; i < header.Length; i++)
                {
                    var y 
= dataTable.Rows[j][i];
                    objData[j, i] 
= (y == null? "" : y.ToString();
                }
            }
            AddExcelRows(
"A2", dataTable.Rows.Count, header.Length, objData);
            AutoFitColumns(
"A1", dataTable.Rows.Count + 1, header.Length);
        }

        
private object[] CreateHeader(DataTable dataTable)
        {

            List
<object> objHeaders = new List<object>();
            
for (int n = 0; n < dataTable.Columns.Count; n++)
            {
                objHeaders.Add(dataTable.Columns[n].ColumnName);
            }

            var headerToAdd 
= objHeaders.ToArray();
            
//工作表的单元是从“A1”开始
            AddExcelRows("A1"1, headerToAdd.Length, headerToAdd);
            SetHeaderStyle();

            
return headerToAdd;
        }

        
private void AutoFitColumns(string startRange, int rowCount, int colCount)
        {
            _range 
= _sheet.get_Range(startRange, _optionalValue);
            _range 
= _range.get_Resize(rowCount, colCount);
            _range.Columns.AutoFit();
        }
        

      

       
/// <summary>
       
/// 将表头加粗显示
       
/// </summary>
        private void SetHeaderStyle()
        {
            _font 
= _range.Font;
            _font.Bold 
= true;
        }
       
        
/// <summary>
        
/// 将数据填充到Excel工作表的单元格中
        
/// </summary>
        
/// <param name="startRange"></param>
        
/// <param name="rowCount"></param>
        
/// <param name="colCount"></param>
        
/// <param name="values"></param>
        private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
        {
            _range 
= _sheet.get_Range(startRange, _optionalValue);
            _range 
= _range.get_Resize(rowCount, colCount);
            _range.set_Value(_optionalValue, values);
        }

 

 

View Code
 /// <summary>
        
/// 将数据填充到Excel工作表的单元格中
        
/// </summary>
        
/// <param name="startRange"></param>
        
/// <param name="rowCount"></param>
        
/// <param name="colCount"></param>
        
/// <param name="values"></param>
        private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
        {
            _range 
= _sheet.get_Range(startRange, _optionalValue);
            _range 
= _range.get_Resize(rowCount, colCount);
            _range.set_Value(_optionalValue, values);
        }

         注意  _range = _sheet.get_Range(startRange, _optionalValue)里面的参数optionalValue,这个参数定义   private object _optionalValue = Missing.Value;
Missing.Value简单的说就是在COM调用时进行占位用的,如果某个参数不会被使用,则使用这个进行占位,比如CallA("A",Missing.Value,"B");表示传入的第二个参数没有用。

 

View Code
  /// <summary>
        
/// 将内存中Excel保存到本地路径
        
/// </summary>
        
/// <param name="excelName"></param>
        private void SaveExcel(string excelName)
        {
            _excelApp.Visible 
= false;
            
//保存为Office2003和Office2007都兼容的格式
            _book.SaveAs(excelName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            _excelApp.Quit();

        }

 

         将内存中Excel保存到本地,如果调用_book.SaveAs(excelName),这样保存的Excel文件格式用Excel2003打开会报错,保存为Office2003和Office2007都兼容的格式 的方法如下: _book.SaveAs(excelName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

         四、总结

        要更好地在.NET中对Excel进行操作,需要对Microsoft.Office.Interop.Excel程序集有熟悉的了解,在导出Excel的时候要注意文件格式的兼容性。本文提供了源码下载供有需要的同学参考。下载地址:/Files/zoupeiyang/ExcelExportImport.rar

 

 

 

 

作者:边写边唱

文章出处:http://www.cnblogs.com/zoupeiyang

专注于用自助终端技术实现互联网+,有兴趣朋友欢迎关注   捷思科技

posted on 2011-05-07 15:42  边写边唱  阅读(10460)  评论(11编辑  收藏  举报

导航