使用OpenXML将Excel内容读取到DataTable中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
/// <summary>
/// 按照给定的Excel流组织成Datatable
/// </summary>
/// <param name="stream">Excel文件流</param>
/// <param name="sheetName">须要读取的Sheet</param>
/// <returns>组织好的DataTable</returns>
private DataTable ReadExcel(string sheetName, Stream stream)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
    {//打开Stream
        IEnumerable<Sheet> sheets = document.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中共享数据
        SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
        IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
        DataTable dt = new DataTable("Excel");
        //因为须要将数据导入到DataTable中,所以我们假定Excel的第一行是列名,从第二行开端是行数据
        foreach (Row row in rows)
        {
            if (row.RowIndex == 1)
            {//Excel第一行动列名
                GetDataColumn(row, stringTable, ref dt);
            }
            else
                GetDataRow(row, stringTable, ref dt);//Excel第二行同时为DataTable的第一行数据
        }
        return dt;
    }
}
/// <summary>
/// 构建DataTable的列
/// </summary>
/// <param name="row">OpenXML定义的Row对象</param>
/// <param name="stringTablePart"></param>
/// <param name="dt">须要返回的DataTable对象</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>
/// 构建DataTable的每一行数据,并返回该Datatable
/// </summary>
/// <param name="row">OpenXML的行</param>
/// <param name="stringTablePart"></param>
/// <param name="dt">DataTable</param>
private void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt)
{
    // 读取算法:按行一一读取单位格,若是整行均是空数据
    // 则忽视改行(因为本人的工作内容不须要空行)-_-
    DataRow dr = dt.NewRow();
    int i = 0;
    int nullRowCount = i;
    foreach (Cell cell in row)
    {
        string cellVal = GetValue(cell, stringTable);
        if (cellVal == string.Empty)
        {
            nullRowCount++;
        }
        dr[i] = cellVal;
        i++;
    }
    if (nullRowCount != i)
    {
        dt.Rows.Add(dr);
    }
}
/// <summary>
/// 获取单位格的值
/// </summary>
/// <param name="cell"></param>
/// <param name="stringTablePart"></param>
/// <returns></returns>
private string GetValue(Cell cell, SharedStringTable stringTable)
{
    //因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引
    string value = string.Empty;
    try
    {
        if (cell.ChildElements.Count == 0)
            return value;
        value = double.Parse(cell.CellValue.InnerText).ToString();
        if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
        {
            value = stringTable.ChildElements[Int32.Parse(value)].InnerText;
        }
    }
    catch (Exception)
    {
        value = "N/A";
    }
    return value;
}
/// <summary>
/// 判断网格是否存在列
/// </summary>
/// <param name="dt">网格</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);
}       

  

前言:前面的几篇文章简单的介绍了如何使用OpenXML创建Excel文档。由于在平时的工作中需要经常使用到Excel的读写操作,简单的介绍下使用OpenXML读取Excel中得数据。当然使用OpenXML将数据读取成什么格式并不重要,本文仅仅介绍如何读取到DataTable中。

准备工作:

      1. Excel2007文档一个;

      2. OpenXML库:DocumentFormat.OpenXml.dll;

      3. Console项目一个,添加对OpenXML库和WindowsBase.dll的引用。

废话不多说,进入正题.

本文介绍的读取Excel的思路如下:

1. 将Excel加载到流Stream;

2. 使用OpenXML操作Stream,并写入DataTable中。

将文件加载到Stream中有很多种方式,这里就不赘述,本文主要介绍第二步。


使用:

 

 

View Code
1       FileStream fs = new FileStream(@"D:\工作簿1.xlsx", FileMode.Open, FileAccess.Read, FileShare.Read);
2         DataTable dt=ReadExcel("Sheet1",fs);
posted @   特务小强  阅读(4651)  评论(5编辑  收藏  举报
点击右上角即可分享
微信分享提示