本文主要解决通过webapi接口上传zip文件（文件里包含图片以及excel），解压缩并读取内部excel文件为datatable进行数据库操作
需要用到的nuget库:NPOI
主要代码
public async Task<string> A_UploadZipCommon(IFormFile file )
{
DataTable dataTable = new DataTable();
if (file != null)
{
//创建文件夹解压缩文件的文件夹，按日期来命名
var s = AppDomain.CurrentDomain.BaseDirectory;
string year = DateTime.Now.Year.ToString() + "年";
string month = DateTime.Now.Month.ToString() + "月";
string day = DateTime.Now.Day.ToString() + "日";
string path7 = System.AppDomain.CurrentDomain.BaseDirectory + "upload";
string file_path = path7 + "/" + year + month + day ;
if (!Directory.Exists(path7)) //如果“日期”的文件不存在
{
Directory.CreateDirectory(path7); //创建一个“日期”的文件夹
}
if (!Directory.Exists(file_path)) //如果“日期”的文件不存在
{
Directory.CreateDirectory(file_path); //创建一个“日期”的文件夹
}
FileStream fs = new FileStream(Path.Combine(file_path, file.FileName), FileMode.Create, FileAccess.ReadWrite);
file.CopyTo(fs);
fs.Close();
string url = Path.Combine(file_path, file.FileName);
//解压缩之后直接访问excel 路径
string asd = url.Split(".zip")[0];
string excelurl = asd + "/teacher.xlsx";
string filedir = Path.Combine(file_path, Path.GetFileNameWithoutExtension(url));
if (Directory.Exists(filedir))
{
//删除
//build an array of the unzipped files
string[] files = Directory.GetFiles(filedir);
foreach (string fi in files)
{
FileInfo f = new FileInfo(fi);
//Check if the file exists already, if so delete it and then move the new file to the extract folder
if (File.Exists(Path.Combine(filedir,f.Name)))
{
File.Delete(Path.Combine(filedir, f.Name));
}
}
//Delete the temporary directory.
Directory.Delete(filedir);
}
//文件解压缩
ZipFile.ExtractToDirectory(url, filedir);
//将zip内部文件的excel 读取为DataTable
return dataTable = NPOIHelper.ReadExcelToDataTable(excelurl,null,true);
}
}
excel转datatable辅助类
public class NPOIHelper
{
/// <summary>
/// 将excel文件内容读取到DataTable数据表中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 1;
try
{
if (!File.Exists(fileName))
{
return null;
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//根据文件流创建excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fs);
// IWorkbook workbook = new HSSFWorkbook(fs);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(4);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
cell.SetCellType(CellType.String);
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)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
/// <summary>
/// 将文件流读取到DataTable数据表中
/// </summary>
/// <param name="fileStream">文件流</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
//根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fileStream);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
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 || row.FirstCellNum < 0) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理，没有数据的单元格都默认是null
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
//判断是否日期类型
if (DateUtil.IsCellDateFormatted(cell))
{
dataRow[j] = row.GetCell(j).DateCellValue;
}
else
{
dataRow[j] = row.GetCell(j).ToString().Trim();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString().Trim();
}
}
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
throw ex;
}
}
}
1241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
