EXCEL导入
- 接口ExcelImport
- ExcelToDataTable
- 去除datatable中的空行RemoveEmpty
EXCEL导出 (请求方式必须为表单提交)
- 接口ExportPlantData(Void)
- 接口ExportPlantData
- 新建类 重写Npoi流方法
EXCEL导入
接口ExcelImport
/// <summary>
///导入Excel 先将文件保存到服务器本地 然后访问服务器文件地址,获取文件
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
[HttpPost]
public async Task<Result> ExcelImport()
{
var request = HttpContext.Current.Request;
var tableName=request.Params["TableName"];
var server = HttpContext.Current.Server;
HttpPostedFile postFile;
if (request.Files.Count > 0)
{
//单文件上传,多文件for
postFile = request.Files[0];
}
else
{
return Result.Failed("无文件");
}
try
{
var filePath = server.MapPath("~/UpLoad/");
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
var fileName = Path.GetFileName(postFile.FileName);
var fileExtention = Path.GetExtension(fileName);
if (fileExtention != ".xls" && fileExtention != ".xlsx")
{
return Result.Failed("文件格式非.xls 或 .xlsx");
}
var saveFileName = filePath + Guid.NewGuid() + fileExtention;
postFile.SaveAs(saveFileName);
_deleteFile = saveFileName;
var excelToList = await _service.ExcelToList(tableName, saveFileName);
if (excelToList == null || excelToList.Count<1 || excelToList[0] == null || excelToList[0].Count < 1)
return Result.Failed("文件为空");
return await _service.ExcelImport(tableName, excelToList) ? Result.Successed : Result.Failed("操作失败");
}
catch (Exception ex)
{
return Result.Failed(ex.ToString());
}
finally
{
if (!string.IsNullOrEmpty(_deleteFile))
File.Delete(_deleteFile);
}
}
ExcelToDataTable
/// </summary>
/// <param name="filePath">Excel位置</param>
/// <param name="tableName">Table名字</param>
/// <param name="sheetIndex">sheet索引</param>
/// <returns></returns>
public static Task<DataTable> ExcelToDataTable(string filePath, string tableName, int sheetIndex)
{
return Task.Run(() =>
{
DataTable fileToTable = new DataTable();
if (!string.IsNullOrEmpty(tableName))
{
fileToTable.TableName = tableName;
}
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workBook = null;
try {
workBook = new XSSFWorkbook(file);
}
catch {
workBook = new HSSFWorkbook(file);
}
ISheet sheet = workBook.GetSheetAt(sheetIndex);
//列头
foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
{
fileToTable.Columns.Add(item.ToString(), typeof(string));
}
//写入内容
System.Colle