一、导出Excel文件。
这里使用了NPIO来导出xls文件，控制器接口代码如下：
/// <summary>
/// 按条件导出房屋信息
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
[HttpGet("items/export")]
[Auth()]
public ActionResult ExportRooms([FromQuery] ExportRoomsRequest request)
{
var targetDB = CurrentTenantSystemDB();
var res = request.GetResult(Context, targetDB);
return res;
}在请求类ExportRoomsRequest中，定义具体的请求参数，以及方法的实现。
/// <summary>
/// 请求导出Excel数据
/// </summary>
public class ExportRoomsRequest
{
/// <summary>
/// 名称
/// </summary>
public string? Name { get; set; }
/// <summary>
/// 楼层ID
/// </summary>
public long? FloorID { get; set; }
/// <summary>
/// 区域ID
/// </summary>
public long? AreaID { get; set; }
/// <summary>
/// 部门ID
/// </summary>
public long TargetPath { get; set; }
/// <summary>
/// 获取请求结果
/// </summary>
/// <returns></returns>
internal ActionResult GetResult(YggdrasilContext context, IFreeSql db)
{
List<DownLoadInfo> res = new();
var allDevicesInfo = RoomInforCache.GetRoomInfoByTargetPath(context, db, TargetPath);
var where = allDevicesInfo.Where(item => 1 == 1);
if (!string.IsNullOrEmpty(Name))
{
where = where.Where(item => item.Name.Contains(Name));
}
if (FloorID != null)
{
where = where.Where(item => item.FloorID == FloorID);
}
if (AreaID != null)
{
where = where.Where(item => item.AreaID == AreaID);
}
var AreaInfo = DataDicCache.GetDataDicInfoByType(context, db, 3);
var BuildInfo = DataDicCache.GetDataDicInfoByType(context, db, 4);
var FloorInfo = DataDicCache.GetDataDicInfoByType(context, db, 2);
where.ToList().ForEach(item =>
{
res.Add(new DownLoadInfo()
{
ID = item.RID,
Num = item.RoomNum,
Name = item.Name,
Area = AreaInfo.FirstOrDefault(a => a.DdId == item.AreaID)?.Title ?? "",
Build = BuildInfo.FirstOrDefault(a => a.DdId == item.Building)?.Title ?? "",
Floor = FloorInfo.FirstOrDefault(a => a.DdId == item.FloorID)?.Title ?? ""
});
});
var dt = listToDataTable(res);
string path = Bifrost.FilePath + DateTime.Now.ToString("yyyyMMdd") + ".xls";
var re = Export_ToExcel(dt, path, new string[] { "ID", "Num", "Name", "Area", "Build", "Floor" }, new string[] { "ID", "编号", "Name", "区域", "楼栋", "楼层" });
var fs = System.IO.File.OpenRead(path);
FileStreamResult fsr = new(fs, "application/octet-stream");
fsr.FileDownloadName = DateTime.Now.ToString("yyyyMMdd") + ".xls";
return fsr;
}
/// <summary>
/// List<T>转换成DataTable
/// <summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataList">泛型List集合数据</param>
/// <returns>返回DataTable</returns>
public System.Data.DataTable? listToDataTable<T>(List<T> dataList)
{
if (dataList == null || !dataList.Any() || dataList.Count == 0)
{
return null;
}
Type type = typeof(T);
using (System.Data.DataTable dt = new System.Data.DataTable(type.Name))
{
PropertyInfo[] propertyInfoArray = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (T t in dataList.Where(t => t != null))
{
DataRow row = dt.NewRow();
for (int i = 0, j = propertyInfoArray.Length; i < j; i++)
{
PropertyInfo propertyInfo = propertyInfoArray[i];
string name = propertyInfo.Name;
if (dt.Columns[name] == null)
{
System.Data.DataColumn column = new System.Data.DataColumn(name, propertyInfo.PropertyType);
dt.Columns.Add(column);
}
row[name] = propertyInfo.GetValue(t, null);
}
dt.Rows.Add(row);
}
return dt;
}
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="colname"></param>
/// <param name="coln_came"></param>
/// <returns></returns>
public static bool Export_ToExcel(DataTable? dt, string fileName, string[] colname, string[] coln_came)
{
try
{
//获取导出模板
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet();
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
IRow row = sheet.CreateRow(0);
for (int i = 0; i < coln_came.Length; i++)
{
string name = coln_came[i];
ICell cell = row.CreateCell(i); //在第一行中创建单元格
cell.CellStyle = cellStyle;//单元格添加样式
cell.SetCellValue(name);//循环往第二行的单元格中添加数据
}
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = sheet.CreateRow(i + 1);
for (int j = 0; j < colname.Length; j++)
{
string name = colname[j];
string columnval = dt.Rows[i][name].ToString();
ICell cell = rows.CreateCell(j); //在第二行中创建单元格
cell.CellStyle = cellStyle;//单元格添加样式
cell.SetCellValue(columnval);//循环往第二行的单元格中添加数据
}
}
}
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate);
hssfworkbook.Write(fs);
fs.Close();
return true;
}
catch (Exception e)
{
return false;
}
}
}用到的辅助类代码如下：
/// <summary>
/// DownLoadInfo
/// </summary>
public class DownLoadInfo
{
/// <summary>
/// ID
/// </summary>
public long ID { get; set; }
/// <summary>
/// 编号
/// </summary>
public string Num { get; set; } = string.Empty;
/// <summary>
/// 名称
/// </summary>
public string Name { get; set; } = string.Empty;
/// <summary>
/// 区域
/// </summary>
public string Area { get; set; } = string.Empty;
/// <summary>
/// 楼栋
/// </summary>
public string Build { get; set; } = string.Empty;
/// <summary>
/// 楼层
/// </summary>
public string Floor { get; set; } = string.Empty;
}二、导入Excel文件
控制器定义接口，接收文件
/// <summary>
/// 导入房间信息
/// </summary>
/// <param name="formFile"></param>
/// <param name="request"></param>
/// <returns></returns>
[HttpPost("items/import")]
[Auth()]
public DataResponse<bool> ImportRooms(IFormFile formFile, [FromQuery] ImportRoomsRequest request)
{
var file = formFile;
if (file == null)
return IllegalRequest<bool>();
string physicPath = $"{Bifrost.FilePath}{file.FileName}";
if (!Directory.Exists(Bifrost.FilePath))
Directory.CreateDirectory(Bifrost.FilePath);
using (FileStream fs = new FileStream(physicPath, FileMode.Create))
{
file.CopyTo(fs);
}
var targetDB = CurrentTenantSystemDB();
if (targetDB is null)
{
return IllegalRequest<bool>();
}
var res = request.GetResult(Context, targetDB, physicPath, CurrentUser!.UID);
return HandleData(res);
}在请求类中，具体的接收参数信息，解析文件内容：
/// <summary>
/// 请求导入文件
/// </summary>
public class ImportRoomsRequest
{
/// <summary>
/// 部门ID
/// </summary>
public long Targetid { get; set; }
/// <summary>
/// 获取请求结果
/// </summary>
/// <param name="context"></param>
/// <param name="db"></param>
/// <param name="filepath"></param>
/// <param name="uid"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
internal bool GetResult(YggdrasilContext context, IFreeSql db, string filepath, long uid)
{
DataTable? dataTable = ExcelToDataTable(filepath, true);
if (dataTable == null)
{
return false;
}
bool re = false;
db.Transaction(() =>
{
List<Room> rooms = new List<Room>();
var Areainfo = DataDicCache.GetDataDicInfoByType(context, db, 3);
var Buildinfo = DataDicCache.GetDataDicInfoByType(context, db, 4);
var floorinfo = DataDicCache.GetDataDicInfoByType(context, db, 2);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
var row = dataTable.Rows[i];
var A_id = Areainfo.FindLast(a => a.Title == row[3].ToString())?.DdId ?? 0;
var B_id = Buildinfo.FindLast(a => a.Title == row[4].ToString())?.DdId ?? 0;
var F_id = floorinfo.FindLast(a => a.Title == row[5].ToString())?.DdId ?? 0;
if (A_id == 0)
{
re = false;
throw new Exception($"没有找到区域名为:{row[2]}的区域信息，导入失败");
}
if (B_id == 0)
{
re = false;
throw new Exception($"没有找到楼栋名为:{row[3]}的楼栋信息，导入失败");
}
if (F_id == 0)
{
re = false;
throw new Exception($"没有找到楼层名为:{row[4]}的楼层信息，导入失败");
}
rooms.Add(new Room
{
RID = Asgard.Core.IDGen.Instance.NextId(),
Name = row[2].ToString(),
AreaID = A_id,
Building = B_id,
FloorID = F_id,
CreatedBy = uid,
RoomNum = row[1]?.ToString(),
TargetPath = Targetid
}); ;
}
re = db.Insert(rooms).ExecuteAffrows() == rooms.Count;
});
return re;
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="fileName">excel路径</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public static DataTable? ExcelToDataTable(string fileName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null;
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
}
return data;
}
catch (Exception ex)
{
return null;
}
}
}
615
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
