VSTO學習筆記（七）基于WPF的Excel分析、轉換小程序

近期因為工作的需要，要批量處理Excel文件，于是寫了一個小程序，來提升工作效率。

小程序的功能是對Excel進行一些分析、驗證，然后進行轉換。

概述

小程序主界面如下：

首先選擇一個日期和類別，從命名上對待分析的Excel文件進行過濾。點擊【瀏覽】選擇待分析的Excel文件所在的目錄，

程序中會獲取所有子目錄。然后點擊【執行分析】就會按照左邊CheckBox中的選擇進行分析，分析結果顯示在每一行中間。【修改配置】可以對分析規則進行設置，此處還未完善，后續再改進。分析結束后點擊【執行轉換】可以將Exce轉換為我們需要的格式和內容，去除只讀、重命名、轉換格式、去除保護、去除公式、顯示零設置等。

主要對Excel做如下幾種分析：

只讀分析：判斷Excel的文件屬性是否為只讀；

命名分析：判斷Excel的文件名是否符合既定規范，包含的工作表是否在有效值列表中；

格式分析：判斷Excel的格式是否符合既定規范（是哪一種Excel，2003？2007？）

保護分析：判斷Excel是否包含工作簿保護、工作表保護；

公式分析：判斷Excel的單元格中是否包含公式；

顯示零分析：判斷Excel中的工作表是否設置為零值顯示。

前五種都比較好理解，只有最后一種"顯示零分析"需要介紹一下。

"顯示零"是指什么呢？在Excel中，若單元格中包含的數值為0，則可以通過設置來決定0值是否顯示，這種設置在某些情況下是有意義的。以Excel 2010為例，我們來看一下怎么設置"零值顯示" ：

2.1、隨便打開一個Excel 2010文件，點擊【File】 -->【Options】：

2.2、在【Advanced】選項卡中，找到【Display options for this worksheet】，選中或取消【Show a zero in cells that have zero value】，點擊【OK】即可：

注意這種設置是工作表級別的，即當前設置只對所選擇工作表有效。為什么要單獨做這種分析呢？若一個工作表中有大量的區域都包含零值，此時若設置為零值顯示則會使得工作表的內容非常雜亂，影響美觀；其他情況下我們設置為零值顯示，這樣方便程序處理，即讀取或寫入單元格中的數值。

分析完成后會顯示出分析結果，并可以【查看明細】按鈕來查看具體的內容，可以精確到單元格，如XX單元格包含公式，公式是什么；XX工作表包含保護等。其中公式的分析結果會記錄日志，方便查看。

程序中有一個全局配置文件GolbalConfig.xml，來存儲分析規則，其他的一些配置信息等：

代碼

<?xml version="1.0" encoding="utf-8" ?>
<ExcelValidate>
    <SelectedDate></SelectedDate>
    <ReportKind>
        <Report name="不限"></Report>
        <Report name="儲備"></Report>
        <Report name="噸煤"></Report>
        <Report name="匯總"></Report>
    </ReportKind>
    <ReportNames>
        <ReportName IsSum="true" reg="^\d{1,2}月匯總表.xlsx?$">
            <SheetNames>
                <SheetName name="累計庫存"></SheetName>
                <SheetName name="累計消耗"></SheetName>
                <SheetName name="原煤1 "></SheetName>
                <SheetName name="各業務科平均庫存儲備資金統計 "></SheetName>
                <SheetName name="各業務科庫存儲備資金統計"></SheetName>
                <SheetName name="累計庫存資金統計報表"></SheetName>
                <SheetName name="累計消耗資金統計報表"></SheetName>
                <SheetName name="本月庫存資金統計報表"></SheetName>
                <SheetName name="本月消耗資金統計報表"></SheetName>
                <SheetName name="庫存儲備資金完成情況統計表"></SheetName>
                <SheetName name="各單位平均庫存儲備資金統計表"></SheetName>
                <SheetName name="各單位噸煤材料費"></SheetName>
                <SheetName name="各業務科噸煤材料費"></SheetName>
                <SheetName name="產量進尺表"></SheetName>
                <SheetName name="全局1表"></SheetName>
                <SheetName name="煤業1表"></SheetName>
                <SheetName name="全局2表"></SheetName>
                <SheetName name="煤業2表"></SheetName>
                <SheetName name="袁莊礦1表"></SheetName>
                <SheetName name="朱莊礦1表"></SheetName>
                <SheetName name="楊莊礦1表"></SheetName>
                <SheetName name="蘆嶺礦1表"></SheetName>
                <SheetName name="朱仙莊1表"></SheetName>
                <SheetName name="童亭礦1表"></SheetName>
                <SheetName name="桃園礦1表"></SheetName>
                <SheetName name="祁南礦1表"></SheetName>
                <SheetName name="許疃礦1表"></SheetName>
                <SheetName name="渦北礦1表"></SheetName>
                <SheetName name="孫疃礦1表"></SheetName>
                <SheetName name="淮選廠1表"></SheetName>
                <SheetName name="臨選廠1表"></SheetName>
                <SheetName name="鐵運處1表"></SheetName>
                <SheetName name="工程處1表"></SheetName>
                <SheetName name="總倉庫1表"></SheetName>
                <SheetName name="岱河礦1表"></SheetName>
                <SheetName name="朔里礦1表"></SheetName>
                <SheetName name="石臺礦1表"></SheetName>
                <SheetName name="臨渙礦1表"></SheetName>
                <SheetName name="海孜礦1表"></SheetName>
                <SheetName name="劉店礦1表"></SheetName>
                <SheetName name="袁莊礦2表"></SheetName>
                <SheetName name="朱莊礦2表"></SheetName>
                <SheetName name="楊莊礦2表"></SheetName>
                <SheetName name="蘆嶺礦2表"></SheetName>
                <SheetName name="朱仙莊2表"></SheetName>
                <SheetName name="童亭礦2表"></SheetName>
                <SheetName name="桃園礦2表"></SheetName>
                <SheetName name="祁南礦2表"></SheetName>
                <SheetName name="許疃礦2表"></SheetName>
                <SheetName name="渦北礦2表"></SheetName>
                <SheetName name="孫疃礦2表"></SheetName>
                <SheetName name="岱河礦2表"></SheetName>
                <SheetName name="朔里礦2表"></SheetName>
                <SheetName name="石臺礦2表"></SheetName>
                <SheetName name="臨渙礦2表"></SheetName>
                <SheetName name="海孜礦2表"></SheetName>
                <SheetName name="劉店礦2表"></SheetName>
            </SheetNames>
        </ReportName>
        <ReportName IsSum="false" reg="^\d{2}\w{3}(儲備|噸煤)報表.xlsx?$">
            <SheetNames>
                <SheetName reg="^\d{4}.\d{2}$"></SheetName>
            </SheetNames>
        </ReportName>
    </ReportNames>
    <MineNames>
        <MineName name="岱河礦"></MineName>
        <MineName name="工程處"></MineName>
        <MineName name="海孜礦"></MineName>
        <MineName name="淮選廠"></MineName>
        <MineName name="臨渙礦"></MineName>
        <MineName name="臨選廠"></MineName>
        <MineName name="劉店礦"></MineName>
        <MineName name="蘆嶺礦"></MineName>
        <MineName name="祁南礦"></MineName>
        <MineName name="石臺礦"></MineName>
        <MineName name="朔里礦"></MineName>
        <MineName name="孫疃礦"></MineName>
        <MineName name="桃園礦"></MineName>
        <MineName name="鐵運處"></MineName>
        <MineName name="童亭礦"></MineName>
        <MineName name="渦北礦"></MineName>
        <MineName name="許疃礦"></MineName>
        <MineName name="楊莊礦"></MineName>
        <MineName name="袁莊礦"></MineName>
        <MineName name="朱仙莊"></MineName>
        <MineName name="朱莊礦"></MineName>
        <MineName name="總倉庫"></MineName>
    </MineNames>
</ExcelValidate>

分析細則

只讀分析：用File.IsReadOnly判斷即可；
命名分析：基于GolbalConfig.xml中的配置進行分析，主要是正則表達式和有效值列表；
格式分析：也是用正則表達式進行分析

Regex excel = new Regex(@"^.xlsx?$");

保護分析：我們知道Excel中的保護功能分為兩種級別，一種是工作簿保護，一種是工作表保護，這里要區分對待。

在Excel COM API 中，工作簿保護用Workbook. ProtectWindows和Workbook. ProtectStructure進行判斷，這兩種保護有什么區別呢？還是以Excel 2010為例，在【Preview】選項卡中點擊【Protect Workbook】：

可以看到Structure默認被選中：

我們保持選中Structure不變，輸入密碼、確認密碼后保存，看看效果：

此時可以改變工作表窗口的大小，但是不能添加、刪除、重命名、復制工作表等，右鍵中相應選項不可用：

另一種Windows保護恰好相反。

工作表保護可以對行、列、單元格、區域等做更細粒度的控制：

公式分析：用Excel COM API中的Range.HasFormula來判斷；
顯示零分析：這個比較特殊，我在Excel COM API中沒有找到編程設置的方法，無奈使用OpenXML SDK 2.0中的方法進行驗證。從Office 2007開始，Word、Excel、PowerPoint全面使用OpenXML作為存儲格式，微軟也相應提供了OpenXML SDK，方便開發人員處理OpenXML文檔，關于OpenXML SDK的介紹，請參閱我的另一篇博文：

OpenXML SDK 2.0悄然發布

OpenXML SDK是個很好的東西，但是網上資源太少，OpenXML的架構也非常復雜，看著幫助文檔中數量龐大的類有些茫然，關于OpenXML SDK我研究也不多，感興趣的朋友可以一起討論。

首先需要下載OpenXML SDK 2.0（可以在這里下載），其中有一個工具：OpenXML SDK 2.0 Productivity Tool，功能非常強大，可以查看OpenXML 文檔的架構，比較兩個OpenXML 文檔架構的不同，甚至可以將OpenXML 文檔反編譯為C#代碼。

下面我們來看一下顯示零的設置在OpenXML架構中是如何顯示的。Excel 中默認是顯示零值的，先參照2.1、2.2中的步驟設置為零值不顯示，然后打開OpenXML SDK 2.0 Productivity Tool：

點擊【Open File】找到剛才設置為零值不顯示的Excel文件，點擊【Reflector Code】：

Ctrl + F，輸入：ShowZeros查找,可以看到在SheetView類的屬性里：

下面用OpenXML SDK 2.0中的方法進行分析：

代碼

                        using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(file.FullName, false))
                        {
                            var book = mySpreadsheet.WorkbookPart.Workbook;
                            foreach (WorksheetPart s in book.WorkbookPart.WorksheetParts)
                            {
                                foreach (SheetView view in s.Worksheet.SheetViews)
                                {
                                    if (view.ShowZeros != null)
                                    {
                                        if (!view.ShowZeros)
                                        {
                                            this.__int顯示零單元格個數++;
                                            this.__dic顯示零.Add(this.__int顯示零單元格個數, file.FullName);
                                        }
                                    }
                                }
                            }
                        }

執行轉換

分析結束后，就可以執行轉換了，來達到我們需要的格式、命名等要求。轉換前默認備份，防止轉換錯誤造成數據丟失，

將待分析的Excel所在的目錄整個復制到指定位置。備份目錄名取當前時間的年月日時分秒。

這里我引用了Microsoft.VisualBasic.dll進行復制目錄操作，否則需要寫一個遞歸函數，C#本身未提供復制目錄的方法：

取消保護時需要輸入工作簿密碼、工作表密碼：

運行效果

小結

本次用一個小程序的方式對Excel常見的方面進行了分析與驗證，可以避免繁瑣的手工處理。程序全部使用了WPF設計，主要運用了Excel COM API來對Excel進行操作，摻雜有少量的Linq to XML。程序中大量使用了foreach循環，效率有待提高，尤其是公式判斷時會比較慢，你可以根據需要自行修改代碼。僅供測試，在生產環境中情謹慎使用。

posted @ 2010-06-03 21:26 江蘇瑞步科技閱讀(5518) 評論(2) 收藏舉報

刷新頁面返回頂部

江蘇瑞步科技

目前主要研發檢測機構解決方案質檢、計量、疾控、環境等商務請聯系 15952187581(微信同號)

VSTO學習筆記（七）基于WPF的Excel分析、轉換小程序

公告

江蘇瑞步科技

目前主要研發檢測機構解決方案 質檢、計量、疾控、環境等 商務請聯系 15952187581(微信同號)

VSTO學習筆記（七）基于WPF的Excel分析、轉換小程序

公告

目前主要研發檢測機構解決方案質檢、計量、疾控、環境等商務請聯系 15952187581(微信同號)