????(1).???????????????.xlsx?????Excel?????????????DataTable??List????
????(2).??????????Window7????+vs2013+Mvc4.0
????(3).??????????????????dll
????1.??NuGet????OpenXML?????????DocumentFormat.OpenXml??=?????????????Excel????.xlsx??β??Excel???????.xls??β??????????????????????????????
????2.WindowsBase.dll
????3.MVC?????file??????????submit????Controller???????￡?
????3.1????????
????<form action="Home/FileUpload" method="post" enctype="multipart/form-data">
????<div style="width:;height:auto;background-color:#808080">
????<input id="uploadfile" type="file" name="file" />
????<input type="submit" value="???Excel" />
????</div>
????</form>
????3.2??Controller????
/// <summary>
/// form?????Action
/// </summary>
/// <returns></returns>
public ActionResult FileUpload()
{
//1.??????????Excel???  ????????Excel???
var stream = Request.Files[0].InputStream;
//2.?????????????DataTable
var rst = new StreamToDataTable().ReadExcel(stream);
//3.??DataTable????List????
var list = this.TableToLists(rst);
return View();
}
/// <summary>
/// ????Excel????
/// </summary>
public List<ExcelImport> TableToLists(System.Data.DataTable table)
{
TBToList<ExcelImport> tables = new TBToList<ExcelImport>();
var lists = tables.ToList(table);
return lists;
}
????(4).Excel???????Datatable???????
public class StreamToDataTable
{
/// <summary>
/// Excel???????Datatable
/// </summary>
/// <param name="stream">Excel?????</param>
/// <returns>DataTable</returns>
public DataTable ReadExcel(Stream stream)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream?? false))     //?????.xls?????Excel????????????????????????????
{
//??Stream
IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
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);
}
GetDataRow(row?? stringTable?? ref dt);//Excel????????DataTable??????????
}
return dt;
}
}
/// <summary>
/// ?????????Excel???????Datatable
/// </summary>
/// <param name="stream">Excel?????</param>
/// <param name="sheetName">????????Sheet</param>
/// <returns>??????DataTable</returns>
public DataTable ReadExcelBySheetName(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);
}
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();
foreach (Cell cell in row)
{
string cellVal = GetValue(cell?? stringTable);
col = new DataColumn(cellVal);
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;
}
}