DocumentFormat.OpenXml read excel file

  这里向大家介绍一种读取excel 数据的方法，用的是DoucmentFormat.OpenXml.dll

  废话不多说，向大家展示一下在项目中处理过的方法，如果有任何疑问，随时联系我。

 

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace EArchivePermissionTool
{
    public class ExcelDataReader
    {
        private bool mIsCheck { get; set; }
        public ExcelDataReader(bool mIsCheck)
        {
            this.mIsCheck = mIsCheck;
        }
        public Dictionary<string, List<List<string>>> GetWholeSheets(Stream stream)
        {
            Dictionary<string, List<List<string>>> result = null;
            try
            {
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
                {
                    result = GetWholeSheets(spreadsheetDocument);
                }
            }
            catch
            {

            }
            finally
            {
                if (!mIsCheck && stream != null)
                {
                    stream.Dispose();
                }
            }
            return result;
        }
        private Dictionary<string, List<List<string>>> GetWholeSheets(SpreadsheetDocument spreadsheetDocument)
        {
            var data = new Dictionary<string, List<List<string>>>();
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            foreach (var worksheetInfo in workbookPart.Workbook.Descendants<Sheet>())
            {
                if (worksheetInfo.State != null && worksheetInfo.State == SheetStateValues.Hidden)
                {
                    continue;
                }
                string workSheetName = worksheetInfo.Name;
                var sheetData = GetSheetData(workbookPart, (WorksheetPart)workbookPart.GetPartById(worksheetInfo.Id));
                data.Add(workSheetName, sheetData);
            }
            return data;
        }
        private List<List<string>> GetSheetData(WorkbookPart workbookPart, WorksheetPart worksheetPart)
        {
            if (worksheetPart == null)
            {
                throw new Exception("Out of range.");
            }
            List<List<string>> result = new List<List<string>>();
            OpenXmlReader reader = OpenXmlReader.Create(worksheetPart, true);
            var rows = worksheetPart.Worksheet.Descendants<Row>();
            uint rowIndex = 1;
            int rowIndexForCheck = 0;
            foreach (var row in rows)
            {
                if (row.HasChildren)
                {
                    var currentRowIndex = row.RowIndex.Value;
                    while (currentRowIndex > rowIndex)
                    {
                        result.Add(new List<string>());
                        ++rowIndex;

                        if (mIsCheck)
                        {
                            ++rowIndexForCheck;
                            if (rowIndexForCheck == 2)
                            {
                                rowIndexForCheck = 0;
                                break;
                            }
                        }
                    }

                    int columnIndex = 0;
                    List<string> l = new List<string>();
                    foreach (Cell cell in row.Descendants<Cell>())
                    {
                        if (cell.CellReference != null)
                        {
                            // Gets the column index of the cell with data
                            int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));

                            if (columnIndex < cellColumnIndex)
                            {
                                do
                                {
                                    l.Add(string.Empty);//Insert blank data here;
                                    columnIndex++;
                                }
                                while (columnIndex < cellColumnIndex);
                            }
                        }
                        l.Add(GetCellValue(workbookPart, cell));
                        columnIndex++;
                    }
                    //Changed by EArchive
                    if (!string.IsNullOrEmpty(l[0]))
                    {
                        result.Add(l);
                    }
                    ++rowIndex;
                    ++rowIndexForCheck;
                    if (mIsCheck && rowIndexForCheck == 2)
                    {
                        break;
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// Given a cell name, parses the specified cell to get the column name.
        /// </summary>
        /// <param name="cellReference">Address of the cell (ie. B2)</param>
        /// <returns>Column Name (ie. B)</returns>
        public static string GetColumnName(string cellReference)
        {
            // Create a regular expression to match the column name portion of the cell name.
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellReference);

            return match.Value;
        }
        /// <summary>
        /// Given just the column name (no row index), it will return the zero based column index.
        /// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ). 
        /// A length of three can be implemented when needed.
        /// </summary>
        /// <param name="columnName">Column Name (ie. A or AB)</param>
        /// <returns>Zero based index if the conversion was successful; otherwise null</returns>
        public static int? GetColumnIndexFromName(string columnName)
        {
            Regex alpha = new Regex("^[A-Z]+$");
            if (!alpha.IsMatch(columnName)) throw new ArgumentException();

            char[] colLetters = columnName.ToCharArray();
            Array.Reverse(colLetters);

            int convertedValue = 0;
            for (int i = 0; i < colLetters.Length; i++)
            {
                char letter = colLetters[i];
                int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
                convertedValue += current * (int)Math.Pow(26, i);
            }
            return convertedValue;
        }

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

    }
}

 

Note: mIsCheck是一个bool值，初始化为true，只会返回每个sheet的header，初始化为false，返回header及body。