先见excel加入到文件流：
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
DataTable table = ImportExcel.ReadExcel("Sheet1", fs);
fs.Dispose();
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
namespace Web.Manage.Models
{
public class ImportExcel
{
public ImportExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
static OleDbConnection GetConnection(string path)
{
var strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0;", path);
var conn = new OleDbConnection(strConn);
conn.Open();
return conn;
}
///
/// 获取Excel的表名[Sheet1$]
/// ///
/// /// Excel的路径
/// DataTable
public static System.Data.DataTable GetExcelTableName(string path)
{
var conn = GetConnection(path);
var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
conn.Close();
return dt;
}
/// <summary>
/// 根据excel工作表名获取内容
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="path">文件路径</param>
/// <returns>DataTable</returns>
public static System.Data.DataTable GetExcelTable(string tableName, string path)
{
var sql = string.Format("SELECT * FROM [{0}]", tableName);
var conn = GetConnection(path);
var myCommand = new OleDbDataAdapter(sql, conn);
var myDataSet = new DataSet();
myCommand.Fill(myDataSet);
conn.Close();
return myDataSet.Tables[0];
}
/// <summary>
/// 删除文件
/// </summary>
/// <param name="path"></param>
public static void DeleteFile(string path)
{
try
{
File.Delete(path);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 流式下载文件
/// </summary>
public static void DownLoadFile()
{
string fileName = "Parameter_Alias_Import_Template.xlsx";//客户端保存的文件名
string filePath = HttpContext.Current.Server.MapPath("../File/Temp/Parameter_Alias_Import_Template.xlsx");//路径 //以字符流的形式下载文件
FileStream fs = new FileStream(filePath, FileMode.Open);
byte[] bytes = new byte[(int)fs.Length]; fs.Read(bytes, 0, bytes.Length);
fs.Close();
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));//通知浏览器下载文件而不是打开
HttpContext.Current.Response.BinaryWrite(bytes);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
#region 采用openxml方式把excel转换成dataTable
/// <summary>
/// Excel流组织成Datatable
/// </summary>
/// <param name="stream">Excel文件流</param>
/// <param name="sheetName">须要读取的Sheet</param>
/// <returns>DataTable</returns>
public 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");
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);
}
#endregion 采用openxml方式把excel转换成dataTable
}
}
