OpenXML_导入Excel到数据库
作者:网络转载 发布时间:[ 2015/11/18 11:03:00 ] 推荐标签:测试开发技术 数据库
(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;
}
}
相关推荐
更新发布
功能测试和接口测试的区别
2023/3/23 14:23:39如何写好测试用例文档
2023/3/22 16:17:39常用的选择回归测试的方式有哪些?
2022/6/14 16:14:27测试流程中需要重点把关几个过程?
2021/10/18 15:37:44性能测试的七种方法
2021/9/17 15:19:29全链路压测优化思路
2021/9/14 15:42:25性能测试流程浅谈
2021/5/28 17:25:47常见的APP性能测试指标
2021/5/8 17:01:11