大家好!我是未来村村长,就是那个“请你跟我这样做,我就跟你这样做!”的村长👨🌾!
👩🌾“人生苦短,你用Python”,“Java内卷,我用C#”。
文章目录
一、文件操作
1、文件操作基础
(1)Directory
//创建文件夹
public static DirectoryInfo CreateDirectory(string path);
//删除文件夹:recursive为false,则仅当文件夹为空时才能删除
public static void Delete(string path,bool recursive);
//移动文件夹
public static void Move(string sourceDirName,string destDirName);
//获取文件夹子目录
public static string[] GetDirectories(string path);
//获取当前目录下所有文件
public static string[] GetFiles(string path);
//判断文件夹是否存在
public static bool Exists(String path);
(2)File
//创建文件:指定路径下已有此文件则进行覆盖
public static FileStream Create(String path);
//删除文件:直接删除不放置回收站
public static void Delete(String path);
//复制文件:复制路径相同或文件不存在则报错
public static void Copy(String sourceFileName, String destFileName);
//判断文件是否存在
public static bool Exists(String path);
//移动文件
public static void Move(String sourceFileName, String destFileName);
//读取文件为二进制
public static byte[] ReadAllBytes(String path);
//读取文件所有行
public static String[] ReadAllLines(String path);
//读取文件内容
public static String ReadAllText(String path, Encoding encoding);
//写入字符串到文件
public static void WriteAllText(String path, String contents);
//追加字符串到文件
public static void AppendAllText(String path, String contents);
(3)FileStream
一般使用构造方法:
public FileStream(String path, FileMode mode, FileAccess access);
FileMode:
public enum FileMode
{
// Creates a new file. An exception is raised if the file already exists.
CreateNew = 1,
// Creates a new file. If the file already exists, it is overwritten.
Create = 2,
// Opens an existing file. An exception is raised if the file does not exist.
Open = 3,
// Opens the file if it exists. Otherwise, creates a new file.
OpenOrCreate = 4,
// Opens an existing file. Once opened, the file is truncated so that its
// size is zero bytes. The calling process must open the file with at least
// WRITE access. An exception is raised if the file does not exist.
Truncate = 5,
// Opens the file if it exists and seeks to the end.
//Otherwise, creates a new file.
Append = 6,
}
FileAccess:
public enum FileAccess
{
// Specifies read access to the file. Data can be read from the file and
// the file pointer can be moved. Combine with WRITE for read-write access.
Read = 1,
// Specifies write access to the file. Data can be written to the file and
// the file pointer can be moved. Combine with READ for read-write access.
Write = 2,
// Specifies read and write access to the file. Data can be written to the
// file and the file pointer can be moved. Data can also be read from the
// file.
ReadWrite = 3,//一般使用
}
FileStream常用方法:
//读取文件
public override int Read([In, Out] byte[] array, int offset, int count);
//写入文件
public override void Write(byte[] array, int offset, int count);
注意建立流后都需要手动关闭:
public virtual void Close();
(4)Path
//获取扩展类型
public static String GetExtension(String path);
//更改扩展类型
public static String ChangeExtension(String path, String extension);
//从地址字符串获取文件名
public static String GetFileName(String path);
//获取文件名不带扩展
public static String GetFileNameWithoutExtension(String path);
//连接两个地址
public static String Combine(String path1, String path2);
//获取所在文件夹路径
public static string GetDirectoryName(string path);
2、NPOI操作List
(1)读取
五层映射:读取一个Excel文件的具体单元格数据,需要经历五层映射的建立:文件→工作簿→工作表→行→单元格
//文件读取
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
//工作簿建立
IWorkbook workBook = new HSSFWorkbook(FileStream file); //xlsx:new XSSFWorkbook(file);
//获取工作表
ISheet sheet = workBook.GetSheetAt(int index);//GetSheet(sheetName);
//获取行
IRow row = sheet.GetRow(int index);
//获取单元格
ICell cell = row.GetCell(int index);
(2)写入
写入操作与读取操作流程类似,操作相反。
//文件创建
FileStream file = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
//工作簿建立
IWorkbook workBook = new XSSFWorkbook();; //xlsx:new XSSFWorkbook(file);
//创建工作表
ISheet sheet = workBook.CreateSheet(sheetName);//GetSheet(sheetName);
//创建行
IRow row = sheet.CreateRow(int index);
//创建单元格
ICell cell = row.GetCell(int index);
//写入数据到单元格中
cell.SetCellValue(string str);
//写入文件
workBook.Write(file);
(3)示例:ExcelTListHelper
namespace FileIO
{
public class ExcelTListHelper
{
public static List<List<string>> ExcelToList(string filePath,string sheetName = "Sheet1")
{
//初始化
IWorkbook wb;
ISheet sheet;
List<List<string>> ExcelList = new();
FileStream file = null;
try
{
//获取拓展名
string extension = Path.GetExtension(filePath);
file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
//获取相应工作簿
if (extension.Equals(".xls"))
{
wb = new HSSFWorkbook(file);
}
else
{
wb = new XSSFWorkbook(file);
}
//获取工作表
if(sheetName.Equals("Sheet1"))
{
sheet = wb.GetSheetAt(0);
}
else
{
sheet = wb.GetSheet(sheetName);
}
//读取到List中
for(int i = 0;i<= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
List<string> CellList = new();
for (int j =0;j< row.LastCellNum; j++)
{
string cell = row.GetCell(j).ToString();
CellList.Add(cell);
}
ExcelList.Add(CellList);
}
}
catch(Exception e)
{
if (file != null)
{
file.Close();
}
Console.WriteLine("导入报错:" + e.Message);
return null;
}
return ExcelList;
}
public static void ListToExcel(string filepath, List<List<string>> list, string sheetName = "Sheet1")
{
//初始化
FileStream file = null;
XSSFWorkbook wb;
ISheet sheet;
try
{
//创建文件流
file = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
//创建工作簿
wb = new XSSFWorkbook();
//创建工作表
sheet = wb.CreateSheet(sheetName);
//List写入EXCEL
for (int i = 0; i < list.Count; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < list[i].Count; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(list[i][j]);
}
}
wb.Write(file);
}
catch (Exception e)
{
Console.WriteLine("导出报错:" + e.Message);
if (file != null)
{
file.Close();
}
}
}
}
}
二、EF Core操作指南
官方文档:查询数据 - EF Core | Microsoft Docs
1、映射建立
假设我们建立数据库(部分)字段如下:
CREATE TABLE `knowledge` (
`knowledge_id` int NOT NULL AUTO_INCREMENT,
`question_title` char(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`knowledge_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`knowledge_status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '未上架',
PRIMARY KEY (`knowledge_id`)
) ENGINE=InnoDB
此处为MySQL数据库,.NET连接MySQL数据库需要导入两个库:
- Microsoft.EntityFrameworkCore
- Pomelo.EntityFrameworkCore.MySql
(1)表字段映射
首先,我们需要建立实体类作为表字段的映射。[Table("数据库表名")]
是建立与数据库中的表的映射,[Key]
是主键。(此处为部分字段)
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace KnowledgeSetWeb.Entities.Models
{
[Table("knowledge")]
public class Knowledge
{
[Key]
[Column("knowledge_id")]
public int KnowledgeId { get; set; }
[Column("question_title")]
public string QuestionTitle { get; set; }
[Column("knowledge_details")]
public string KnowledgeDetails { get; set; }
[Column("knowledge_status")]
public string KnowledgeStatus { get; set; }
}
}
(2)数据库映射
using Microsoft.EntityFrameworkCore;
using KnowledgeSetWeb.Entities.Models;
namespace KnowledgeSetWeb.Repositories.DbContexts
{
public class KnowledgeSetDbContext : DbContext
{
public DbSet<Knowledge> Knowledge { get; set; }
public KnowledgeSetDbContext(DbContextOptions<KnowledgeSetDbContext> options) : base(options) { }
}
}
(3).NET连接配置
在Startup.cs的ConfigureServices注册添加相应的服务配置。
public void ConfigureServices(IServiceCollection services)
{
//DbContextע
services.AddDbContext<KnowledgeSetDbContext>(
options => options.UseMySql(
Configuration.GetConnectionString("MySQL"),
MySqlServerVersion.LatestSupportedServerVersion));
}
在appsettings.json中配置连接字符串:此处的名字对应服务配置中的GetConnectionString方法传入参数。
"ConnectionStrings": {
"MySQL": "Server=localhost; Port=3306; Database=KnowledgeSet; User=root; Password=123456"
}
输出对应SQL语句的简单日志:在上下文的OnConfiguring方法中调用optionsBuilder类的LogTo方法。
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.LogTo(Console.WriteLine);
}
2、增删改查之类
(1)根据指定范围进行查询
public async Task<IEnumerable<Knowledge>> GetSpecifiedKnowledgeAsync(SpecifiedKnowledgeVO knowledgeVO)
{
var result = await _dbContext.Knowledge.Where(
k => (knowledgeVO.KnowledgeType.Length == 0 || (k.KnowledgeType == knowledgeVO.KnowledgeType))
&& (knowledgeVO.Keyword.Length == 0 || EF.Functions.Like(k.Keyword, "%" + knowledgeVO.Keyword + "%"))
&& (knowledgeVO.IsPutAway.Length == 0 || k.KnowledgeStatus == knowledgeVO.IsPutAway)
&& (knowledgeVO.SystemModule.Length == 0 || k.SystemModule == knowledgeVO.SystemModule)
&& (knowledgeVO.BelongtoSystem.Length == 0 || k.BelongtoSystem == knowledgeVO.BelongtoSystem)
&& (k.UpdatedTime.CompareTo(knowledgeVO.StartTime) >= 0)
&& (k.UpdatedTime.CompareTo(knowledgeVO.EndTime) <= 0)
).ToListAsync();
return result;
}
(2)分页查询
public async Task<IEnumerable<Knowledge>> GetPageKnowledgeAsync( int pageSize, int pageIndex)
{
return await _dbContext.Knowledge.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
}
(3)删除数据
public async Task DeleteKnowledgeAsync(int id)
{
var removingKnowledge = await _dbContext.Knowledge.Where(k => k.KnowledgeId == id).FirstOrDefaultAsync();
if(removingKnowledge is Knowledge)
{
_dbContext.Remove(removingKnowledge);
await _dbContext.SaveChangesAsync();
}
}
(4)获取分组组别信息
public async Task<KnowledgeGroupingLabelVO> GetKnowledgeGroupingLabelAsync()
{
KnowledgeGroupingLabelVO result = new KnowledgeGroupingLabelVO()
{
SystemModule = await _dbContext.Knowledge.Select(k => k.SystemModule).Distinct().ToListAsync(),
KnowledgeStatus = await _dbContext.Knowledge.Select(k => k.KnowledgeStatus).Distinct().ToListAsync(),
KnowledgeType = await _dbContext.Knowledge.Select(k => k.KnowledgeType).Distinct().ToListAsync(),
BelongtoSystem = await _dbContext.Knowledge.Select(k => k.BelongtoSystem).Distinct().ToListAsync()
};
return result;
}
(5)获取页码总数
public async Task<long> GetPageAmountAsync(int pageSize)
{
long count = (await _dbContext.Knowledge.ToListAsync()).LongCount();
long amount = (long) Math.Ceiling((count * 1.0) / pageSize);
return amount;
}
(6)数据更改
public async Task UpdateKnowledgeAsync(Knowledge newKnowledge)
{
var oldKnowledge = await _dbContext.Knowledge.Where(k => k.KnowledgeId == newKnowledge.KnowledgeId).FirstOrDefaultAsync();
if (oldKnowledge is Knowledge)
{
oldKnowledge.BelongtoSystem = newKnowledge.BelongtoSystem;
oldKnowledge.Keyword = newKnowledge.Keyword;
oldKnowledge.KnowledgeType = newKnowledge.KnowledgeType;
oldKnowledge.Mender = newKnowledge.Mender;
oldKnowledge.UpdatedTime = DateTime.Now;
oldKnowledge.SystemModule = newKnowledge.SystemModule;
}
await _dbContext.SaveChangesAsync();
}
3、LINQ常用集合类扩展方法【适用于数据库操作】
(1)数据过滤:Where
IEnumerable<Employee> list = list.Where(e => e.Salary >2500 && e.Age<35);
(2)数据条数:Count
int count = list.Count(e => e.Salary > 5000 || e.age < 30);
(3)是否存在:Any
bool has = list.Any(e => e.Salary > 5000);
(4)获取一条:Single、SingleOrDefault、First、FirstOrDefault
- Single:如果确认有且只有一条则使用,若有多条则抛出异常
- singleOrDefault:如果最多只有一条则使用,若没有满足条件的数据,返回类型的默认值。若满足条件的数据多于一条则抛出异常
- First:如果满足条件的数据有多条则返回第一条,若没有则抛出异常
- FirstOrDefault:如果满足条件的数据有多条则返回第一条,若没有则返回类型的默认值
Eployee eployee = list.Single(e => e.Id==6);
(5)排序:Order/OrderByDescending
var orderedDAta = list.OrderBy(e => e.Age);
(6)分页查询:Skip().Take()
car skipData = list.Skip(n).Take(m);
//从第n条数据开始,获取m条数据
(7)聚合函数
Max、Min、Avg、Sum、Count。
(8)字段分组:GroupBy
GroupBy返回类型为IEnumerable<IGrouping<Tkey,Tsource>>,Tsource表示数据源,可为Dbcontext.数据表。返回类型依然为IEnumerable,我们可以在返回的数据基础上继续使用聚合函数进行操作。
IEnumerable<IGrouping<int,Employee>> employees = list.GroupBy(e=>Age);
(9)提取字段:Select
Select方法返回的是匿名类型的IEnumberable,必须使用var声明变量类型。
var item = list.Select(e=>new{
e.Name,e.Age,sex = e.Gender ? "男" : "女"
});
(10)集合转换
- ToArray:将IEnumerable<T>转换为数组
- ToList:将IEnumerable<T>转换为List<T>
4、关系配置
(1)建立实体
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EntityAndRepository.Models
{
[Table("article")]
public class Article
{
[Key]
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public List<Comment> Comments{get;set;} = new List<Comment>();
}
}
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EntityAndRepository.Models
{
[Table("comment")]
public class Comment
{
[Key]
public long Id { get; set; }
public string Message { get; set; }
public Article Article { get; set; }
}
}
其对应的数据库:
CREATE TABLE `article` (
`Id` int NOT NULL AUTO_INCREMENT,
`Title` varchar(100) DEFAULT NULL,
`Content` varchar(100) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `comment` (
`Id` int NOT NULL AUTO_INCREMENT,
`Message` varchar(100) DEFAULT NULL,
`ArticleId` int DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
(2)建立关联
using Microsoft.EntityFrameworkCore;
using KnowledgeSetWeb.Entities.Models;
using System;
namespace KnowledgeSetWeb.Repositories.DbContexts
{
public class KnowledgeSetDbContext : DbContext
{
public DbSet<Article> Article { get; set; }
public DbSet<Comment> Comment { get; set; }
public KnowledgeSetDbContext(DbContextOptions<KnowledgeSetDbContext> options) : base(options) { }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Comment>()
.HasOne(c => c.Article)
.WithMany(a => a.Comments).IsRequired();
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.LogTo(Console.WriteLine);
}
}
}
(3)执行查询
public Article Get()
{
return db.Article.Include(a => a.Comments).Single(a => a.Id == 1);
}
对应的MySQL语句:
SELECT `t`.`Id`, `t`.`Content`, `t`.`Title`, `c`.`Id`, `c`.`ArticleId`, `c`.`Message`
FROM (
SELECT `a`.`Id`, `a`.`Content`, `a`.`Title`
FROM `article` AS `a`
WHERE `a`.`Id` = 1
LIMIT 2
) AS `t`
LEFT JOIN `comment` AS `c` ON `t`.`Id` = `c`.`ArticleId`
ORDER BY `t`.`Id`, `c`.`Id`
(4)执行保存
public void AddArticle()
{
Article article = new Article()
{
Id = 2,
Title = "文章标题2",
Content = "文章内容2"
};
Comment c1 = new Comment()
{
Id = 2,
Message = "评论2"
};
Comment c2 = new Comment()
{
Id = 3,
Message = "评论3"
}; ;
article.Comments.Add(c1);
article.Comments.Add(c2);
_dbContext.Article.Add(article);
_dbContext.SaveChanges();
}
对应的MySQL语句:
INSERT INTO `article` (`Id`, `Content`, `Title`)
VALUES (@p0, @p1, @p2);
INSERT INTO `comment` (`Id`, `ArticleId`, `Message`)
VALUES (@p0, @p1, @p2);
INSERT INTO `comment` (`Id`, `ArticleId`, `Message`)
VALUES (@p0, @p1, @p2);
5、建立外键
在EF Core中以注释的方式:
namespace KnowledgeSetWeb.Entities.Models
{
[Table("comment")]
public class Comment
{
[Key]
public long Id { get; set; }
public string Message { get; set; }
public long ArticleId { get; set; }
[ForeignKey("ArticleId")]
public Article Article { get; set; }
}
}
MySQL相关操作:
set foreign_key_checks=0; -- 关闭外键约束检查,1为开启
alter table 表名 drop foreign key 外键名; -- 删除外键约束
alter table 表名 add foreign key(字段名) references 外键所在的表(id); -- 增加外键约束
ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`); -- 增加组合索引
ALTER TABLE `table_name` ADD INDEX index_name (`column`); -- 增加普通索引
DROP INDEX index_name ON `table_name`; -- 删除索引
6、批量操作:Zack.EFCore.Batch.MySQL.Pomelo
中文说明文档:Zack.EFCore.Batch/README_CN.md at main · yangzhongke/Zack.EFCore.Batch (github.com)
三、日志的使用
1、logging
在Startup的ConfigureServices中进行logging组件的配置,在此之前我们还需要引入以下两个包:
- Microsoft.Extensions.Logging
- Microsoft.Extensions.Logging.Console
public void ConfigureServices(IServiceCollection services)
{
services.AddLogging(logBuilder =>{
logBuilder.AddConsole();
logBuilder.SetMinimumLevel(LogLevel.Trace);//设置日志输出级别
});
}
2、NLog
此处需要导入Nlog的NuGet包,然后除了需要在Startup的ConfigureServices中进行注册配置以外。
Nuget包:NLog.Extensions.Logging
public void ConfigureServices(IServiceCollection services)
{
services.AddLogging(logBuilder =>{
logBuilder.AddConsole();
logBuilder.AddNLog();//文件日志
logBuilder.SetMinimumLevel(LogLevel.Trace);//设置日志输出级别
});
}
还需要在根目录下,建立一个配置文件nlog.config,该文件的内容参照以下:
<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
autoReload="true"
internalLogLevel="Info"
internalLogFile="internal-nlog-AspNetCore.txt">
<!-- enable asp.net core layout renderers -->
<extensions>
<add assembly="NLog.Web.AspNetCore"/>
</extensions>
<!-- the targets to write to -->
<targets>
<!-- File Target for all log messages with basic details -->
<target xsi:type="File" name="allfile" fileName="nlog-AspNetCore-all-${shortdate}.log"
layout="${longdate}|${event-properties:item=EventId_Id:whenEmpty=0}|${level:uppercase=true}|${logger}|${message} ${exception:format=tostring}" />
<!-- File Target for own log messages with extra web details using some ASP.NET core renderers -->
<target xsi:type="File" name="ownFile-web" fileName="nlog-AspNetCore-own-${shortdate}.log"
layout="${longdate}|${event-properties:item=EventId_Id:whenEmpty=0}|${level:uppercase=true}|${logger}|${message} ${exception:format=tostring}|url: ${aspnet-request-url}|action: ${aspnet-mvc-action}|${callsite}" />
<!--Console Target for hosting lifetime messages to improve Docker / Visual Studio startup detection -->
<target xsi:type="Console" name="lifetimeConsole" layout="${MicrosoftConsoleLayout}" />
</targets>
<!-- rules to map from logger name to target -->
<rules>
<!--All logs, including from Microsoft-->
<logger name="*" minlevel="Trace" writeTo="allfile" />
<!--Output hosting lifetime messages to console target for faster startup detection -->
<logger name="Microsoft.Hosting.Lifetime" minlevel="Info" writeTo="lifetimeConsole, ownFile-web" final="true" />
<!--Skip non-critical Microsoft logs and so log only own logs (BlackHole) -->
<logger name="Microsoft.*" maxlevel="Info" final="true" />
<logger name="System.Net.Http.*" maxlevel="Info" final="true" />
<logger name="*" minlevel="Trace" writeTo="ownFile-web" />
</rules>
</nlog>
四、接口Demo示例
1、文件上传与下载
(1)文件上传
Controller:
[HttpPost]
[Route("upload")]
public async Task<URResult<string>> UploadFileAsync(IFormFile file)
{
if (file != null)
{
string filePath = await _fileManagerService.UploadFileAsync(file);
}
}
Service:
public async Task<string> UploadFileAsync(IFormFile file)
{
//建立文件夹
string fileDir = @"C:\Users\Administrator\Desktop\KSDir";
if (!Directory.Exists(fileDir))
{
Directory.CreateDirectory(fileDir);
}
//文件名:随机生成
string fileName = "fw_" + new Random().Next() + "_" + file.FileName;
//存放地址
string fileLocation = @"C:\Users\Administrator\Desktop\KSDir\" + fileName;
//文件类型
string fileType = Path.GetExtension(fileLocation);
using (FileStream newFile = System.IO.File.Create(fileLocation))
{
file.CopyTo(newFile);
newFile.Flush();
}
//文件信息存入数据库
FileManager fileManager = new FileManager()
{
FileName = fileName,
FileType = fileType,
FileLocation = fileLocation
};
await _repository.AddFileMessageAsync(fileManager);
return fileManager.FileLocation;
}
(2)文件下载
[HttpGet]
[Route("download")]
public async Task<FileContentResult> downloadFile(int fileId)
{
_logger.LogTrace("[api/controller/download] 请求数据:" + fileId);
FileManager fileManager = await _fileManagerService.GetFileMessageByIdAsync(fileId);
byte[] fileBytes = System.IO.File.ReadAllBytes(fileManager.FileLocation);
string fileName = fileManager.FileName;
_logger.LogTrace("[api/controller/download] 传输文件:" + fileManager.FileLocation);
return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);
}
(3)文件删除
public async Task<bool> DeleteFileAsync(string filePath)
{
try{
//判断文件是否存在
if (File.Exists(filePath)){
//删除文件
File.Delete(filePath);
//删除数据库中数据
await _repository.DeleteFileMessageAsync(filePath);
return true;
}
}
catch(Exception e){
Console.WriteLine("删除文件报错:" + e.Message);
}
return false;
}
2、读取Excel写入数据库
Controller:
[HttpPost]
[Route("ExcelToDb")]
public async Task<URResult<string>> UploadExcelToDbAsync(IFormFile file,string creator)
{
URResult<string> response = null;
if (file != null)
{
string fileExtensions = Path.GetExtension(file.FileName);
if (fileExtensions != ".xlsx" && fileExtensions != ".xls")
{
response = new URResult<string>()
{
Message = "文件格式错误",
Status = 400
};
return response;
}
//上传文件
string filePath = await _fileManagerService.UploadFileAsync(file);
//读取导入数据库
bool SuccessfulFlag = await _knowledgeService.ExcelToDbAsync(filePath,creator);
//删除文件
await _fileManagerService.DeleteFileAsync(filePath);
if (SuccessfulFlag)
{
response = new URResult<string>()
{
Message = "上传成功",
Status = 200
};
}
else
{
response = new URResult<string>()
{
Message = "上传失败,未使用模板文件或填写错误",
Status = 500
};
}
}
else
{
response = new URResult<string>()
{
Message = "文件为空",
Status = 400
};
}
return response;
}
Service:
public async Task<bool> ExcelToDbAsync(string filePath, string creator,string sheetName = "Sheet1")
{
//初始化
IWorkbook wb;
ISheet sheet;
FileStream file = null;
try
{
//获取拓展名
string extension = Path.GetExtension(filePath);
file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
//获取相应工作簿
if (extension.Equals(".xls"))
{
wb = new HSSFWorkbook(file);
}
else
{
wb = new XSSFWorkbook(file);
}
//获取工作表
if (sheetName.Equals("Sheet1"))
{
sheet = wb.GetSheetAt(0);
}
else
{
sheet = wb.GetSheet(sheetName);
}
//读取到knowledge数据库
for (int i = 1; i < sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
Knowledge knowledge = new Knowledge();
for (int j = 0; j < row.LastCellNum; j++)
{
string cell = row.GetCell(j)?.ToString();
//赋值
switch (j)
{
case 0: knowledge.BelongtoSystem = cell; break;
case 1: knowledge.SystemModule = cell; break;
case 2: knowledge.KnowledgeType = cell; break;
case 3: knowledge.Keyword = cell; break;
case 4: knowledge.QuestionTitle = cell; break;
case 5: knowledge.KnowledgeDetails = cell; break;
}
}
knowledge.Creator = creator;
//调用add添加
await _repository.AddKnowledgeAsync(knowledge);
}
file.Close();
}
catch (Exception e)
{
if (file != null)
{
file.Close();
}
_logger.LogError("KnowledgeService-Method报错:" + "Exception:" + e.Message);
return false;
}
return true;
}