public void ListMapExcel()
{
var list = CreateTestData1List();
var oldJson = JsonConvert.SerializeObject(list);
var dt = OfficeHelper.ToDatatable(list, new Dictionary <string, Expression <Func <TestData1, object> > > {
{ "Id", it => it.Id },
{ "Name", it => it.Name },
{ "Time", it => it.Time }
});
DataTable newTable;
byte[] bs;
using (var memoryStream = new MemoryStream())
{
var ds = new DataSet();
ds.Tables.Add(dt);
NPOIHelper.DatasetToExcel(ds, memoryStream, ExcelFormat.Xlsx);
bs = memoryStream.ToArray();
}
using (var memoryStream = new MemoryStream(bs))
{
newTable = NPOIHelper.ExcelToDataset(memoryStream).Tables[0];
}
int successCount = 0;
List <string> messageList = new List <string>(newTable.Rows.Count);
List <TestData1> testData1List = new List <TestData1>(newTable.Rows.Count);
foreach (var item in OfficeHelper.ConvertFromDatatable(newTable, new Dictionary <string, Func <TestData1, object, DataRow, bool> > {
{ "Id", (it, obj, row) => OfficeHelper.TryConvertThen <int>(obj, v => it.Id = v) },
{ "Name", (it, obj, row) => OfficeHelper.TryConvertThen <string>(obj, v => it.Name = v) },
{ "Time", (it, obj, row) => OfficeHelper.TryConvertThen <DateTimeOffset>(obj, v => it.Time = v) }
}))
{
if (item.IsSuccess)
{
successCount++;
testData1List.Add(item.Data);
}
else
{
messageList.Add(item.Message);
}
}
if (successCount == testData1List.Count)
{
var newJson = JsonConvert.SerializeObject(testData1List);
//Assert.AreEqual(oldJson, newJson); 由于DateTime转换到Excel精度丢失，无法保证导入导出的数据完全一致
}
else
{
Assert.Fail();
}
}
public ActionResult ImportXlsx(HttpPostedFile import)
{
DataSet ds = null;
try
{
if (import != null && import.ContentLength > 0)
{
ds = NPOIHelper.ExcelToDataset(import.InputStream);
}
}
catch (Exception)
{
throw;
}
if (ds.Tables?.Count > 0)
{
var dt = ds.Tables[0];
int successCount = 0;
List <string> messageList = new List <string>(dt.Rows.Count);
List <TestData1> testData1List = new List <TestData1>(dt.Rows.Count);
foreach (var item in OfficeHelper.ConvertFromDatatable(dt, new Dictionary <string, Func <TestData1, object, DataRow, bool> > {
{ "Id", (it, obj, row) => OfficeHelper.TryConvertThen <int>(obj, v => it.Id = v) },
{ "Name", (it, obj, row) => OfficeHelper.TryConvertThen <string>(obj, v => it.Name = v) },
{ "Time", (it, obj, row) => OfficeHelper.TryConvertThen <DateTime>(obj, v => it.Time = v) }
}))
{
if (item.IsSuccess)
{
successCount++;
testData1List.Add(item.Data);
}
else
{
messageList.Add(item.Message);
}
}
//下面部分只是为了展示出导入的数据
ViewData["successCount"] = successCount;
ViewData["messageList"] = messageList;
ViewData["testData1List"] = testData1List;
return(View("Index", CreateTestData1List()));
}
else
{
throw new Exception("所上传文件中不包含工作表！");
}
}
public void ListMapDatatable()
{
var list = CreateTestData1List();
var oldJson = JsonConvert.SerializeObject(list);
var dt = OfficeHelper.ToDatatable(list, new Dictionary <string, Expression <Func <TestData1, object> > > {
{ "Id", it => it.Id },
{ "Name", it => it.Name },
{ "Time", it => it.Time }
});
int successCount = 0;
List <string> messageList = new List <string>(dt.Rows.Count);
List <TestData1> testData1List = new List <TestData1>(dt.Rows.Count);
foreach (var item in OfficeHelper.ConvertFromDatatable(dt, new Dictionary <string, Func <TestData1, object, DataRow, bool> > {
{ "Id", (it, obj, row) => OfficeHelper.TryConvertThen <int>(obj, v => it.Id = v) },
{ "Name", (it, obj, row) => OfficeHelper.TryConvertThen <string>(obj, v => it.Name = v) },
{ "Time", (it, obj, row) => OfficeHelper.TryConvertThen <DateTimeOffset>(obj, v => it.Time = v) }
}))
{
if (item.IsSuccess)
{
successCount++;
testData1List.Add(item.Data);
}
else
{
messageList.Add(item.Message);
}
}
if (successCount == testData1List.Count)
{
var newJson = JsonConvert.SerializeObject(testData1List);
Assert.AreEqual(oldJson, newJson);
}
else
{
Assert.Fail();
}
}