C#調(diào)用OpenXml讀取excel行數(shù)據(jù)

更新時(shí)間：2024年12月23日 10:59:46 作者：lxxxxl

這篇文章主要為大家詳細(xì)介紹了C#如何調(diào)用OpenXml實(shí)現(xiàn)讀取excel行數(shù)據(jù),文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下

問題及現(xiàn)象

在OpenXML中文件不包含空白單元格的條目，這就是跳過空白單元格的原因。

所以如果當(dāng)我們打開一個(gè)excel，讀取一個(gè)表格數(shù)據(jù)，發(fā)現(xiàn)如果有空單元格，openXML會(huì)跳過導(dǎo)致讀取的數(shù)據(jù)發(fā)生錯(cuò)位。

比如這個(gè)是原始的excel表格數(shù)據(jù)。

當(dāng)有空格讀取后，第一行和最后一行的數(shù)據(jù)就會(huì)錯(cuò)位了，如下：

解決的辦法就是：

假設(shè)：

DocumentFormat.OpenXml.Spreadsheet.Row row，

Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference == $"列行");

//例如：c.CellReference =="A12"

當(dāng)cell是空的時(shí)候，表示該單元格是空值。

因此對(duì)于從行中提取單元格不能使用“foreach (Cell cell in row)”，這樣取出來的cell是非空的單元格，也就是如果你有10列，有兩列是空單元格，那么就只能取出來8列，這就導(dǎo)致了取出數(shù)據(jù)的錯(cuò)位。

而應(yīng)該使用循環(huán)，也就是知道了表格的列數(shù)了，然后使用for去循環(huán)，例如：

 for (int columnIndex = 0; columnIndex < dt.Columns.Count; columnIndex++)
  {
 
                //查找指定的行列單元格是否存在。
 
                Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference == $"{columnReference[columnIndex]}{row.RowIndex}");//例如：c.CellReference =="A12"
                string cellVal = null;  //定義獲取的單元格的值，默認(rèn)為空
                if (cell != null)
                {//不為空使用定義的GetCellValue（）函數(shù)獲取cell中的值
                    cellVal = GetCellValue(cell, workbookPart);
                }
 
             ....
    }

以下為封裝的OpenXML處理的完整代碼

調(diào)用ReadSheetWithHeader（）函數(shù)，ReadSheetWithHeader會(huì)調(diào)用封裝的OpenXml類OutExcel對(duì)象，從而把指定的excel文件的sheet表讀取到DataTable的數(shù)據(jù)集合中。

  /// <summary>
        /// 將指定的excel文件中的指定索引的sheet讀取到表對(duì)象中
        /// </summary>
        /// <param name="fileNm">excel文件路徑</param>
        /// <param name="sheetIndex">sheet索引</param>
        /// <returns>返回DataTable對(duì)象</returns>
        public DataTable ReadSheetWithHeader(string fileNm, int sheetIndex)
        {
            FileStream fs = new FileStream(fileNm, FileMode.Open, FileAccess.Read, FileShare.Read);
            DataTable dt = new OutExcel().ReadExcel(sheetIndex, fs);
 
            return dt;
        }

封裝OpenXml類OutExcel

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace OpenExcelMng
{
    public class OutExcel
    {
        /// <summary>
        /// 按照給定的Excel流組織成Datatable
        /// </summary>
        /// <param name="sheetName">須要讀取的Sheet的名稱</param>
        /// <param name="stream">Excel文件流</param>
        /// <returns>組織好的DataTable</returns>
        public DataTable ReadExcel(string sheetName, Stream stream)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
            {   //打開Stream
                WorkbookPart workbookPart = document.WorkbookPart;
                IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                {//找出合適前提的sheet,沒有則返回                     
                    return null;
                }
 
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                //獲取Excel中共享數(shù)據(jù)
                SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//獲得Excel中得數(shù)據(jù)行
                DataTable dt = new DataTable("Excel");
                //因?yàn)轫氁獙?shù)據(jù)導(dǎo)入到DataTable中,所以我們假定Excel的第一行是列名,從第二行開端是行數(shù)據(jù)
 
                foreach (Row row in rows)
                {
                    if (row.RowIndex == 1)
                    {//Excel第一行動(dòng)列名
                        GetDataColumn(row, stringTable, ref dt);
                    }
                    else
                    {
                        GetDataRow(row, stringTable, workbookPart, ref dt);//Excel第二行同時(shí)為DataTable的第一行數(shù)據(jù)
                    }
                }
                return dt;
            }
        }
 
        /// <summary>
        /// 按照給定的Excel流組織成Datatable
        /// </summary>
        /// <param name="sheetIndex">須要讀取的Sheet的索引</param>
        /// <param name="sheetIndex">Excel文件流</param>
        /// <returns>組織好的DataTable</returns>
        public DataTable ReadExcel(int sheetIndex, Stream stream)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
            {//打開Stream
                WorkbookPart workbookPart = document.WorkbookPart;
                IList<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>().ToList();
                if (sheets.Count() == 0)
                {//找出合適前提的sheet,沒有則返回                     
                    return null;
                }
 
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets[sheetIndex].Id);
                //獲取Excel中共享數(shù)據(jù)
                SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//獲得Excel中得數(shù)據(jù)行
                DataTable dt = new DataTable("Excel");
                //因?yàn)轫氁獙?shù)據(jù)導(dǎo)入到DataTable中,所以我們假定Excel的第一行是列名,從第二行開端是行數(shù)據(jù)
                foreach (Row row in rows)
                {
                    if (row.RowIndex == 1)
                    {//Excel第一行動(dòng)列名
                        GetDataColumn(row, stringTable, ref dt);
                    }
                    else
                    {
                        GetDataRow(row, stringTable, workbookPart, ref dt);//Excel第二行同時(shí)為DataTable的第一行數(shù)據(jù)
                    }
 
                }
                return dt;
            }
        }
 
 
        /// <summary>
        /// 構(gòu)建DataTable的列
        /// </summary>
        /// <param name="row">OpenXML定義的Row對(duì)象</param>
        /// <param name="stringTablePart"></param>
        /// <param name="dt">須要返回的DataTable對(duì)象</param>
        /// <returns></returns>
        public void GetDataColumn(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataColumn col = new DataColumn();
            Dictionary<string, int> columnCount = new Dictionary<string, int>();
            foreach (Cell cell in row)
            {
                string cellVal = GetValue(cell, stringTable);
                col = new DataColumn(cellVal);
                if (IsContainsColumn(dt, col.ColumnName))
                {
                    if (!columnCount.ContainsKey(col.ColumnName))
                        columnCount.Add(col.ColumnName, 0);
                    col.ColumnName = col.ColumnName + (columnCount[col.ColumnName]++);
                }
                dt.Columns.Add(col);
            }
        }
        /// <summary>
        /// 構(gòu)建DataTable的每一行數(shù)據(jù),并返回該Datatable
        /// </summary>
        /// <param name="row"></param>
        /// <param name="stringTable"></param>
        /// <param name="workbookPart">用于處理獲取Cell中的信息，如果Cell存在，不是空單元格</param>
        /// <param name="dt">把行數(shù)據(jù)寫入到datatabl中</param>
        private void GetDataRow(DocumentFormat.OpenXml.Spreadsheet.Row row,
                                DocumentFormat.OpenXml.Spreadsheet.SharedStringTable stringTable, //不再使用
                                DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart, //用于處理獲取Cell中的信息，如果Cell存在，不是空單元格
                                ref System.Data.DataTable dt)  //把行數(shù)據(jù)寫入到datatabl中。
        {
            // 讀取算法：按行一一讀取單位格,若是整行均是空數(shù)據(jù)
            // 則忽視改行(因?yàn)楸救说墓ぷ鲀?nèi)容不須要空行)-_-
            DataRow dr = dt.NewRow();
            int i = 0;
            int nullRowCount = i;
            Dictionary<int, string> columnReference = new Dictionary<int, string>();
            columnReference.Add(0, "A");
            columnReference.Add(1, "B");
            columnReference.Add(2, "C");
            columnReference.Add(3, "D");
            columnReference.Add(4, "E");
            columnReference.Add(5, "F");
            columnReference.Add(6, "G");
            columnReference.Add(7, "H");
            for (int columnIndex = 0; columnIndex < dt.Columns.Count; columnIndex++)
            {
 
                Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference ==     $"{columnReference[columnIndex]}{row.RowIndex}");//例如：c.CellReference =="A12"
                string cellVal = null;
                if (cell != null)
                {
                    cellVal = GetCellValue(cell, workbookPart);
                }
 
                if (string.IsNullOrEmpty(cellVal))
                {
                    nullRowCount++;
                }
                dr[i] = cellVal;
                i++;
            }           
 
            if (nullRowCount != i)
            {
                dt.Rows.Add(dr);
            }
        }
 
        /// <summary>
        /// 獲取單位格的值
        /// </summary>
        /// <param name="cell">單元格</param>
        /// <param name="workbookPart"></param>
        /// <param name="type">1 不去空格 2 前后空格 3 所有空格  </param>
        /// <returns></returns>
        public static string GetCellValue(Cell cell, WorkbookPart workbookPart, int type = 2)
        {
            //合并單元格不做處理
            if (cell.CellValue == null)
                return string.Empty;
 
            string cellInnerText = cell.CellValue.InnerXml;
 
            //純字符串
            if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString || cell.DataType.Value == CellValues.String || cell.DataType.Value == CellValues.Number))
            {
                //獲取spreadsheetDocument中共享的數(shù)據(jù)
                SharedStringTable stringTable = workbookPart.SharedStringTablePart.SharedStringTable;
 
                //如果共享字符串表丟失，則說明出了問題。
                if (!stringTable.Any())
                    return string.Empty;
 
                string text = stringTable.ElementAt(int.Parse(cellInnerText)).InnerText;
                if (type == 2)
                    return text.Trim();
                else if (type == 3)
                    return text.Replace(" ", "");
                else
                    return text;
            }
            //bool類型
            else if (cell.DataType != null && cell.DataType.Value == CellValues.Boolean)
            {
                return (cellInnerText != "0").ToString().ToUpper();
            }
            //數(shù)字格式代碼（numFmtId）小于164是內(nèi)置的：https://www.it1352.com/736329.html
            else
            {
                //為空為數(shù)值
                if (cell.StyleIndex == null)
                    return cellInnerText;
 
                Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
                CellFormat cellFormat = (CellFormat)styleSheet.CellFormats.ChildElements[(int)cell.StyleIndex.Value];
 
                uint formatId = cellFormat.NumberFormatId.Value;
                double doubleTime;//OLE 自動(dòng)化日期值
                DateTime dateTime;//yyyy/MM/dd HH:mm:ss
                switch (formatId)
                {
                    case 0://常規(guī)
                        return cellInnerText;
                    case 9://百分比【0%】
                    case 10://百分比【0.00%】
                    case 11://科學(xué)計(jì)數(shù)【1.00E+02】
                    case 12://分?jǐn)?shù)【1/2】
                        return cellInnerText;
                    case 14:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM/dd");
                    //case 15:
                    //case 16:
                    case 17:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM");
                    //case 18:
                    //case 19:
                    case 20:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("H:mm");
                    case 21:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("HH:mm:ss");
                    case 22:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM/dd HH:mm");
                    //case 45:
                    //case 46:
                    case 47:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM/dd");
                    case 58://【中國】11月11日
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("MM/dd");
                    case 176://【中國】2020年11月11日
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM/dd");
                    case 177://【中國】11:22:00
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("HH:mm:ss");
                    default:
                        return cellInnerText;
                }
            }
        }
        /// <summary>
        /// 獲取單位格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="stringTablePart"></param>
        /// <returns></returns>
        private string GetValue(Cell cell, SharedStringTable stringTable)
        {
            //因?yàn)镋xcel的數(shù)據(jù)存儲(chǔ)在SharedStringTable中,須要獲取數(shù)據(jù)在SharedStringTable 中的索引
            string value = string.Empty;
            try
            {
                if (cell.ChildElements.Count == 0)
                    return value;
                value = double.Parse(cell.CellValue.InnerText).ToString();
                if (cell.DataType != null)
                {
                    switch (cell.DataType.Value)
                    {
                        case CellValues.SharedString:
                            value = stringTable.ChildElements[Int32.Parse(value)].InnerText; break;
                    }
                }
 
            }
            catch (Exception ex)
            {
                value = "N/A";
            }
            return value;
        }
        /// <summary>
        /// 判斷網(wǎng)格是否存在列
        /// </summary>
        /// <param name="dt">網(wǎng)格</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public bool IsContainsColumn(DataTable dt, string columnName)
        {
            if (dt == null || columnName == null)
            {
                return false;
            }
            return dt.Columns.Contains(columnName);
        }
        public static void ConvertToDateTime(ref DataTable dt, string columnNm, string dtFormat)
        {
            int findLoca_Old = dt.Columns.IndexOf(columnNm);
 
            DataColumn newColumn = new DataColumn(System.Guid.NewGuid().ToString(), typeof(String));
            string newColumnNm = newColumn.ColumnName;
            dt.Columns.Add(newColumn);
            newColumn.SetOrdinal(findLoca_Old + 1);
 
            foreach (DataRow row in dt.Rows)
            {
                try
                {
                    double val = Convert.ToDouble(row[columnNm]);
                    row[newColumnNm] = DateTime.FromOADate(val).ToString(dtFormat);
                }
                catch (Exception ex)
                {
                    ;
                }
            }
 
            dt.Columns.RemoveAt(findLoca_Old);
            newColumn.ColumnName = columnNm;
        }
    }
}

到此這篇關(guān)于C#調(diào)用OpenXml讀取excel行數(shù)據(jù)的文章就介紹到這了,更多相關(guān)C# OpenXml讀取excel行數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: