一、Excel数据导入
excel数据导入,前后端传输的数据是文件流,我们前端用的是Vue.js 后端用的是.Net WebApi 。前端读取到文件流之后,通过ajax等发起请求,后端接收到文件流之后,首先转换为DataTable类型的数据,然后可以做相应的处理。我这里是将DataTable转换成对应的实体集合List<T>,然后再进行的数据库写入等操作。
话不多说,上代码:
[HttpPost]
public IHttpActionResult ImportMeters()
{
//返回信息
dynamic result;
var filelist = HttpContext.Current.Request.Files;
var meterList = new List<Tb_meters_info>();
if (filelist.Count > 0)
{
for (var i = 0; i < filelist.Count; i++)
{
var file = filelist[i];
// LogHelper.WriteLog("收到" + file.ToString());
DataTable dt = ExcelHelper.ExcelToTableForXLSX(file.InputStream);//excel转成datatable
meterList = ListDatatableConvert.ToDataList<Tb_meters_info>(dt);//datatable转成list
}
string errorMsg = "";
int sucNum = meterBll.AddMeters(meterList,ref errorMsg);
if (sucNum == 0)
{
result = new
{
code = "error",
msg = errorMsg
};
}
else
{
result = new
{
code = "success",
msg = "成功导入数据" + sucNum + "条"
};
}
}
else
{
result = new
{
code = "error",
msg = "没有数据需要导入"
};
}
return Json(result);
}
Excel文件流转换为DataTable的方法见:https://blog.csdn.net/qq_34811513/article/details/109111940
DataTable转成List<T>的方式见:https://blog.csdn.net/qq_34811513/article/details/109112327
2、数据导出为Excel
数据导出,前端可以传查询条件到后台WebApi,后台接收到参数后查询出数据列表然后导出,也可以直接传数据集合到后台WebApi,后台接收到之后直接处理导出。后台处理之后,会将文件路径返回给前端,前端可直接下载该文件。
我这里是直接传List集合到后台接口,代码如下:
/// <summary>
/// 导出抄表数据
/// </summary>
/// <param name="meterList"></param>
/// HttpResponseMessage
/// <returns></returns>
[HttpPost]
public IHttpActionResult ExportExcel(IList<Tb_meters_info> meterList)
{
string basePath = System.AppDomain.CurrentDomain.BaseDirectory;
basePath += "\\FileUpload\\";
if (!Directory.Exists(basePath))
{
//不存在
Directory.CreateDirectory(basePath);
}
IList<DataExportModel> epList = new List<DataExportModel>();
DataExportModel dtModel;
foreach (Tb_meters_info meter in meterList)
{
dtModel = new DataExportModel();
dtModel.CmpName = meter.CmpName;
dtModel.DepartName = meter.DepartName;
dtModel.ZoneName = meter.ZoneName;
dtModel.Collector_no = meter.retval.Collector_no;
dtModel.MeterNo = meter.retval.Meter_No;
dtModel.Dt_collect = meter.retval.Dt_collect;
dtModel.Ret_value = meter.retval.Ret_value;
dtModel.Dt_return = meter.retval.Dt_return;
dtModel.Corrected = meter.retval.Corrected;
epList.Add(dtModel);
}
DataTable dt = ListDatatableConvert.ListToDataTable<DataExportModel>(epList);
string fileName = DateTime.Now.ToString("yyMMddHHmmssfff") + ".xls";
string filePath = basePath + fileName;
FileStream stream = ExcelHelper.ExportExcel(dt, filePath);
string downLoadFilePath = "\\FileUpload\\" + fileName;
if (stream != null)
{
return Json(downLoadFilePath);
}
else
{
return Json("导出失败");
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="FilePath"></param>
public static FileStream ExportExcel(DataTable dt, string FilePath)
{
int row_index = 0;
//创建全新的Workbook
IWorkbook workbook = new XSSFWorkbook();
//创建Sheet
workbook.CreateSheet("Sheet1");
//根据Sheet名字获得Sheet对象
ISheet sheet = workbook.GetSheet("Sheet1");
IRow row;
row = sheet.CreateRow(row_index);
//for (int i = 0; i < ds.Tables.Count; i++)
//{
//写入标题
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Columns[j].Caption.ToString());
}
row = sheet.CreateRow(++row_index);
//写入数据
foreach (DataRow r in dt.Select())
{
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(r[j].ToString());
}
row = sheet.CreateRow(++row_index);
}
// }
//保存Workbook方式一: 以文件形式保存到服务器中(每次导出都会生成一个文件,慎重使用)
FileStream sw = File.Create(FilePath);
workbook.Write(sw);
//sw.Close();
return sw;
//保存Workbook方式二: 保存到内存流中
//var stream = new MemoryStream();
//workbook.Write(stream);
}