[C#]_[マイクロソフトOpenXmlSDK(OpenXmlReader)を使用してxlsxテーブルを読み込む]


1.OpenXmlSDKは良いクラスライブラリですが、残念ながらC#で呼び出すしかありません.C#の子供靴はまた幸せです.
2.サービス側プログラムはofficeをインストールできないため、これはasp.Netサイトにとって最も理想的なライブラリです.必要です.Net 4.0以降.
3.ストリーム形式でsaxモデルが大きなファイルを読み込む.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ConsoleApplication1
{
    class ProductObject
    {

        public String xinghao;//  
        public String changjia;//  
        public String pihao;//  
        public String fengzhuang;//  
        public String shuliang;//  

        public void init() 
        {
            //       
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            String fileName = @"E:\software\TestData\xlsx\test.xlsx";
            Program pro = new Program();
            pro.ReadAllCellValues(fileName);
        }

        void CallSqlInsert(ProductObject po)
        {
            //call bl interface method to insert data to database.
            //         
            Console.Out.Write("{0}:{1}:{2}:{3}:{4} ", po.xinghao, po.changjia, po.pihao, po.fengzhuang, po.shuliang);
            Console.Out.WriteLine();
        }

        String GetCellValue(WorkbookPart workbookPart,Cell c) 
        {
            string cellValue;
            if (c.DataType != null && c.DataType == CellValues.SharedString)
            {
                SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements().ElementAt(int.Parse(c.CellValue.InnerText));
                cellValue = ssi.Text.Text;
            }
            else
            {
                cellValue = c.CellValue.InnerText;
            }
            return cellValue;
        }

        Boolean StoreProductObject(OpenXmlReader reader, WorkbookPart workbookPart, ProductObject po) 
        {
            reader.ReadFirstChild();
            if (reader.ElementType != typeof(Cell))
            {
                return false;
            }

            Cell c = (Cell)reader.LoadCurrentElement();
            po.xinghao = GetCellValue(workbookPart,c);

            reader.ReadNextSibling();
            c = (Cell)reader.LoadCurrentElement();
            po.changjia = GetCellValue(workbookPart,c);

            reader.ReadNextSibling();
            c = (Cell)reader.LoadCurrentElement();
            po.pihao = GetCellValue(workbookPart,c);

            reader.ReadNextSibling();
            c = (Cell)reader.LoadCurrentElement();
            po.fengzhuang = GetCellValue(workbookPart,c);

            reader.ReadNextSibling();
            c = (Cell)reader.LoadCurrentElement();
            po.shuliang = GetCellValue(workbookPart,c);
            return true;
        }

        //  SAX        ,              100    100      .
        //  SAX        xlsx                .
        void ReadAllCellValues(string fileName)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                ProductObject po = new ProductObject();

                foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
                {
                    OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
                    while (reader.Read())
                    {
                        if (reader.ElementType == typeof(Worksheet))
                        {
                            if (reader.ReadFirstChild())
                            {
                                SheetProperties properties = (SheetProperties)reader.LoadCurrentElement();
                                //      Sheet1 ,     
                                if (properties.CodeName != "Sheet1")
                                {
                                    break;
                                }
                            }
                        }
                        if (reader.ElementType == typeof(Row))
                        {
                            //            init,   .
                            po.init();
                            if (StoreProductObject(reader, workbookPart, po)) 
                            {
                                CallSqlInsert(po);
                            }
                            
                        }
                    }
                }
            }
        }

    }
}