当前位置:编程学习 > C#> 正文

C#如何读取Excel

时间:2013-12-22类别:编程学习

C#如何读取Excel

C#如何读取Excel

方法一:这种直接读取单元格的方法释放很重要。

  •  
  • C# 代码   复制
  • 
       Excel.Application excel = null;
       Excel.Workbooks wbs = null;
       Excel.Workbook wb = null;
       Excel.Worksheet ws = null;
       Excel.Range range1 = null;
       object Nothing = System.Reflection.Missing.Value;
         
       try
       {
        excel = new Excel.Application();
        excel.UserControl = true;
        excel.DisplayAlerts = false;
                         
        excel.Application.Workbooks.Open(this.FilePath,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing ) ;
         
        wbs = excel.Workbooks;
        wb = wbs[1];
        ws = (Excel.Worksheet)wb.Worksheets["Sheet2"];
         
              
        int rowCount = ws.UsedRange.Rows.Count;
        int colCount = ws.UsedRange.Columns.Count;
        if (rowCount <= 0)
         throw new InvalidFormatException("文件中没有数据记录");
        if (colCount < 4 ) 
         throw new InvalidFormatException("字段个数不对");
         
        for (int i = 0;i    {
    
         this.rowNo = i + 1;
         object[] row = new object[4];
         for (int j = 0;j<4;j++)
         {
          range1 = ws.get_Range(ws.Cells[i+2,j+1],ws.Cells[i+2,j+1]);
          row[j] = range1.Value;
    
          if (row[0] == null)
          {
           this.isNullRecord++;
           break;
          }
         }
                        
         if (this.isNullRecord > 0)
          continue;
    
         DataRow dataRow = this.readExcel(row);
    
         if (this.isNullRecord == 1)
          continue;
       
         if (this.verifyData(dataRow) == false)
          errFlag++;
       
         this.updateTableCurr(dataRow);
        }
         
       }
       finally
       {
        if (excel != null)
        {
         if (wbs != null)
         {
          if (wb != null)
          {
           if (ws != null)
           {
            if (range1 != null)
            {
             System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
             range1 = null;
            }
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
            ws = null;
           }
           wb.Close(false,Nothing,Nothing);  
           System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
           wb = null;
          }
          wbs.Close();
          System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
          wbs = null;
         }
         excel.Application.Workbooks.Close();
         excel.Quit();
         System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
         excel = null;
         GC.Collect();
        }
       }
    
    		
  • 方法二:这里是fill进dataset,也可以返回OleDbDataReader来逐行读,数据较快


    注:这种方法容易把混合型的字段作为null值读取进来,解决办法是改造连接字符串


    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Erp1912.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";


    通过Imex=1来把混合型作为文本型读取,避免null值

  • C# 代码   复制
  • 
    private DataSet importExcelToDataSet(string FilePath)
      {
       string strConn;
       strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
       OleDbConnection conn = new OleDbConnection(strConn);
       OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",strConn);
       DataSet myDataSet = new DataSet();
       try
       {
        myCommand.Fill(myDataSet);
       }
       catch(Exception ex)
       {
        throw new InvalidFormatException("该Excel文件的工作表的名字不正确," + ex.Message);
       }
       return myDataSet;
      }
    
    		
  • C#读写Excel文件并打印输出的Demo

    1、 创建一个叫DemoExcel的项目

    2、 引用COM,包括:Microsoft.Excel.x.0.Object.Library,Microsoft.Office.x.0.Object.Library

    建议安装正版OFFICE,而且版本在11.0以上(Office2003以上),引用以上两个Com后,在项目引用栏发现多了Excel、Microsoft.Office.Core,VBIDE三个 Library.

  •  
  • C# 代码   复制
  • 
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Core;
    using System.IO;
    using System.Reflection;
    
    namespace DemoExcel
    {
        public partial class Form1 : Form
        {
            private  object missing = Missing.Value;
            private Microsoft.Office.Interop.Excel.Application ExcelRS;
            private Microsoft.Office.Interop.Excel.Workbook RSbook;
            private Microsoft.Office.Interop.Excel.Worksheet RSsheet;
    
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                // TODO: 这行代码将数据加载到表“dataSet1.STREET”中。您可以根据需要移动或移除它。
                this.sTREETTableAdapter.Fill(this.dataSet1.STREET);
    
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                string OutFilePath = System.Windows.Forms.Application.StartupPath + @" emp.xls";
               
                string TemplateFilePath = System.Windows.Forms.Application.StartupPath + @"模版.xls";
                PrintInit(TemplateFilePath,OutFilePath);
            }
            Excle输出前初始化
  • 上一篇下一篇

    猜您喜欢

    热门推荐