将Excel文件读入txt文件

时间：2010-08-17 来源：penggang_2366

将Excel文件读入txt文件

目标：将Excel中的工作表读出来，不同的工作表写入不同的文件，文件名形式为Excel文件名+工作表名。

知识点：在Excel中，数据是高度结构化的，利用Application ，Workbook ，Worksheet，

Range四个常用对象来处理数据。

核心步骤：

首先介绍下事先定义好的相关的局部变量：

代码

#region DATA MEMBERS 
        private Excel.Application excelApplication = null;
        private Excel.Workbook excelWorkbook = null;
        private Excel.Sheets excelSheets = null;
        private Excel.Worksheet excelWorksheet = null;

        // to represent values that are missing/not applicable while passing parameters
        private static object esh_missing = System.Reflection.Missing.Value;

        private static object esh_visible = true;
        private static object esh_true = true;
        private static object esh_false = false;

        // to set the application visible or invisible
        private static object esh_app_visible = false;

        // used in the closingof the application
        private object esh_filename = null;

        #endregion

(1) 打开Excel文件

代码

 1 public void OpenFile(string filename, string password)
 2         {
 3             esh_filename = filename;
 4             if (password.Length > 0)
 5             {
 6                 esh_password = password;
 7             }
 8             try
 9             {
10                 this.excelWorkbook = this.excelApplication.Workbooks.Open(filename,
11                         esh_update_links, esh_read_only, esh_format, esh_password, esh_write_res_password,
12                         esh_ignore_read_only_recommend, esh_origin, esh_delimiter, esh_editable, esh_notify,
13                         esh_converter, esh_add_to_mru, esh_local, esh_corrupt_load);
14                
15             }            
16             catch (Exception ee)
17             {
18                 if ((ee.Message).Contains("could not be found"))
19                 {
20                     throw (new FileNotFoundException(ee.Message));
21                 }
22                 else
23                 {
24                     throw (new Exception("Unknown error while opening the file"));
25                 }
26             }
27             
28         }

(2) 获取Excel中的工作表

public void GetExcelsheets()
        {
            if (this.excelWorkbook != null)
            {
                this.excelSheets = this.excelWorkbook.Worksheets;
            }
        }

(3) 确定数据的范围

用Range.UsedRange.Rows实现

代码

 if (this.excelSheets != null)
            {
                for (int i = 1; i <= this.excelSheets.Count; ++i)
                {
                    this.excelWorksheet = (Excel.Worksheet)excelSheets.get_Item((object)i);

                    Excel.Range currentsheet=excelWorksheet.UsedRange.Rows;

                    System.Array dataArray = (System.Array)currentsheet.Cells.Value2;
                    dataArray.ToString();

                    string filename = outputFolderName+@"\"+excelWorksheet.Name + "." + excelWorksheet.Name+".txt";

                    this.ReadExceltoTxt(dataArray, filename);

                 

                }
            }

(4) 将数据转存到数组，然后写进txt文件

函数接口中的变量用的是Array，在函数实现中，将cells的内容转存到交错数组中，用循环语句逐个读单元格的内容

代码

 private void ReadExceltoTxt(System.Array dataArray,string filename)
        {

            FileStream fs = new FileStream(filename, FileMode.Create);
            StreamWriter sw = new StreamWriter(fs);

            string[][] sArray = new string[dataArray.GetUpperBound(0)][];


            for(int i=dataArray.GetLowerBound(0);i<=dataArray.GetUpperBound(0);i++)
            {
                sArray[i - 1] = new string[dataArray.GetUpperBound(1)];

                for(int j=dataArray.GetLowerBound(1);j<=dataArray.GetUpperBound(1);j++)
                {
                    if (dataArray.GetValue(i, j) == null)
                    {
                        sArray[i - 1][j - 1] = "---";
                    }
                    else
                    {
                        sArray[i - 1][j - 1] = dataArray.GetValue(i, j).ToString();
                        
                    }
                    sw.Write(sArray[i - 1][j - 1]);
                    sw.Write("\t");
                }

                sw.WriteLine();

            }
            sw.Close();
            fs.Close();

        }