110,528
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 按照给定的Excel流组织成Datatable
/// </summary>
/// <param name="stream">Excel文件流</param>
/// <param name="sheetName">须要读取的Sheet</param>
/// <returns>组织好的DataTable</returns>
public static System.Data.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);
string sheet_id = "";
if (sheets.Count() == 0)
{//找出合适前提的sheet,没有则返回
//return null;
sheet_id = "rId1";
}
else
{
sheet_id = sheets.First().Id;
}
// WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet_id);
//获取Excel中共享数据
SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
System.Data.DataTable dt = new System.Data.DataTable("Excel");
//因为须要将数据导入到DataTable中,所以我们假定Excel的第一行是列名,从第二行开端是行数据
foreach (Row row in rows)
{
if (row.RowIndex == 1)
{//Excel第一行动列名
GetDataColumn(row, stringTable, ref dt);
}
GetDataRow(row, stringTable, ref dt);//Excel第二行同时为DataTable的第一行数据
}
document.Close();
return dt;
}
}
/// <summary>
/// 构建DataTable的列
/// </summary>
/// <param name="row">OpenXML定义的Row对象</param>
/// <param name="stringTablePart"></param>
/// <param name="dt">须要返回的DataTable对象</param>
/// <returns></returns>
public static void GetDataColumn(Row row, SharedStringTable stringTable, ref System.Data.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>
public static void GetDataRow(Row row, SharedStringTable stringTable, ref System.Data.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>
public static 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 static bool IsContainsColumn(System.Data.DataTable dt, string columnName)
{
if (dt == null || columnName == null)
{
return false;
}
return dt.Columns.Contains(columnName);
}