【學習筆記】C#使用Aspose.Cells.dll處理excel表格

本文主要描述使用Aspose.cells處理excel表格的類，用於批量測試載入器的，自己寫的，不足之處還是很多的

包含三個方法

1.載入excel表格，會判斷excel是2007還是2003

2.sheet處理

3.行處理

 

    using System;    using System.Collections.Generic;    using System.Text;    using System.Drawing;    using Aspose.Cells;    using System.IO;    using System.Configuration;    class ProcessExcel    {        //初始化excel        public Workbook ImportExcel(string inputFile)        {            try            {                Workbook workBook = new Workbook();                FileStream fs = new FileStream(ConfigurationSettings.AppSettings["InputFile"], FileMode.Open);                int firstIndex=inputFile.LastIndexOf(".");                string fileType = inputFile.Substring(firstIndex);                if (fileType == ".xls")                {                    workBook.Open(fs);                    return workBook;                }                else                {                    workBook.Open(fs, FileFormatType.Excel2007Xlsx);                    return workBook;                }            }            catch (Exception ex)            {                Console.WriteLine("EXCEL初始化失敗，請檢查設定檔！");                Console.ReadKey();                return null;            }        }             //處理sheet        public void processSheet(Worksheet workSheet)        {            Cells cell = workSheet.Cells;            int rowNum = cell.MaxDataRow;            for (int i = 2; i <= rowNum; i++)            {                this.processRow(i, cell);            }        }        //處理行        public void processRow(int rowNum, Cells cell)        {            string userNO = cell[rowNum, 2].StringValue;            string userContent = cell[rowNum, 3].StringValue;            string expectReturnSMS = cell[rowNum, 4].StringValue;            //用例標記為有效            if (cell[rowNum, 1].StringValue == "1")            {                GetResult getResult = new GetResult();                if (getResult.InvokeCMCC(userNO, userContent))                {                    if (getResult.GetReturnSMS(userNO))                    {                        getResult.GetTestResult(expectReturnSMS);                        cell[rowNum, 5].PutValue(getResult.returnSMS);                        if (getResult.testResult == "FALSE")                        {                            cell[rowNum, 6].PutValue(getResult.testResult);                            cell[rowNum, 6].Style.BackgroundColor = Color.Red;                        }                        else                        {                            cell[rowNum, 6].PutValue(getResult.testResult);                            cell[rowNum, 6].Style.BackgroundColor = Color.Green;                        }                    }                    else                    {                        cell[rowNum, 6].PutValue("未擷取到返回簡訊");                        cell[rowNum, 6].Style.BackgroundColor = Color.Yellow;                    }                }                else                {                    cell[rowNum, 6].PutValue("CMCC介面調用異常");                    cell[rowNum, 6].Style.BackgroundColor = Color.Yellow;                }            }                        //用例無效            else if(cell[rowNum, 1].StringValue == "0")            {                GetResult getResult = new GetResult();                if (getResult.InvokeCMCC(userNO, userContent))                {                    Console.WriteLine("此條用例無需執行");                    Console.WriteLine();                }                else                {                    cell[rowNum, 6].PutValue("CMCC介面調用異常");                    Console.WriteLine();                }            }        }         }