C# Aspose.Cells方式导入Excel文件
读取Excel 类 我返回的是DataTable 类型 也可以返回DataSet类型
public class XlsFileHelper
{
public DataTable ImportExcel(Stream filePath)
{
DataTable dt = new DataTable();
try
{
//打开文件,参数可以是文件的路径,也可以直接传入一个文件流
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filePath);
//获取sheet表
Aspose.Cells.WorksheetCollection worksheets = workbook.Worksheets;
Aspose.Cells.Worksheet worksheet = null;
Aspose.Cells.Cells cell = null;
//默认第一行为列名 所以第一行不读,索引从第二行开始
int rowIndex = 0;
//从第一列读取
int colIndex = 0;
for (int i = 0; i < worksheets.Count; i++)
{
worksheet = worksheets[i];
//获取每个sheet表的所有单元格
cell = worksheet.Cells;
dt = cell.ExportDataTableAsString(rowIndex, colIndex, cell.MaxDataRow + 1, cell.MaxDataColumn + 1, true);
//表
dt.TableName = "table" + i.ToString();
}
worksheets.Clear();
worksheet = null;
worksheets = null;
workbook = null;
}
catch (Exception ex)
{
throw;
}
return dt;
}
}
读取excel文件的控制器方法,我传入的是一个HttpPostedFileBase
/// <summary>
/// 导入Excel文件
/// </summary>
/// <returns></returns>
[HttpPost]
public ActionResult Daoru(HttpPostedFileBase file)
{
var result = new OperationResult();
//名单类list
List<MingDan> list = new List<MingDan>();
try
{
XlsFileHelper helper = new XlsFileHelper();
//导入excel文件的方法(参数:IO流)
var data = helper.ImportExcel(file.InputStream);
//for循环获取表中列名值,长度小于表行数
for (int i = 0; i < data.Rows.Count; i++)
{
MingDan mingdan = new MingDan();
//姓名
mingdan.Name = data.Rows[i][0].ToString();
//证件类型
mingdan.CardType = data.Rows[i][1].ToString();
//证件号码
mingdan.CardCode = data.Rows[i][2].ToString();
//因为Aspose.Cells方式读取的是整个excel文件 包括它的工作簿,所以我在身份证号为空时中断循环
if (string.IsNullOrWhiteSpace(mingdan.CardCode))
{
break;
}
//出生日期
mingdan.Birth = data.Rows[i][3].ToString();
//性别
mingdan.Sex = data.Rows[i][4].ToString();
list.Add(mingdan);
}
}
catch (Exception e2)
{
return Json(result.ErrorResult("操作异常 : " + e2.Message));
}
//返回Json数据
return Json(list);
}
前端代码是这样的
<div>
<a href="javascript:void(0)" onclick="document.getElementById('txt_file').click()">导入名单</a>
<form action="@Url.Action("Daoru")" enctype="multipart/form-data" method="post" id="up" target="upiframe">
<input type="file" hidden name="file" id="txt_file" onchange="document.getElementById('up').submit()" />
<input type="hidden" name="num" id="num" value="" />
</form>
<
</div>
<iframe style="display:none" name="upiframe" id="upiframe"></iframe>
<script type="text/javascript">
window.onload = function () {
document.getElementById("upiframe").onload = function (e) {
try {
var upiframe = document.getElementById("upiframe"),
body = (upiframe.contentDocument || upiframe.contentWindow.document).body,
result = body.innerText || body.textContent || '';
//最终的Json数据data
data = JSON.parse(result);
} catch (er) {
console.log(er.message)
}
document.getElementById("up").reset();
};
}
</script>