一.前言項(xiàng)目中使用到比較多的關(guān)于Excel的前端上傳與下載，整理出來(lái)，以便后續(xù)使用或分析他人。
成都創(chuàng)新互聯(lián)公司網(wǎng)站建設(shè)公司，提供網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站建設(shè)，網(wǎng)頁(yè)設(shè)計(jì)，建網(wǎng)站，PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);可快速的進(jìn)行網(wǎng)站開(kāi)發(fā)網(wǎng)頁(yè)制作和功能擴(kuò)展；專業(yè)做搜索引擎喜愛(ài)的網(wǎng)站，是專業(yè)的做網(wǎng)站團(tuán)隊(duì)，希望更多企業(yè)前來(lái)合作!
1.前端vue:模板下載與導(dǎo)入Excel
導(dǎo)入Excel封裝了子組件，點(diǎn)擊導(dǎo)入按鈕可調(diào)用子組件，打開(kāi)文件上傳的對(duì)話框，上傳成功后返回結(jié)果
批量導(dǎo)入 模板下載 新增 importFile() { this.$refs.refFileUpload.open(); }
向后臺(tái)提交文件的方法
重慶分公司
重慶分公司
submitFile() {
const _this = this;
if (!_this.files.name) {
_this.$message.warning("請(qǐng)選擇要上傳的文件！");
return false;
}
let fileFormData = new FormData();
//filename是鍵，file是值，就是要傳的文件
fileFormData.append("file", _this.files, _this.files.name);
if(_this.OtherParams){
const keys=Object.keys(_this.OtherParams);
keys.forEach(e=>{
fileFormData.append(e, _this.OtherParams[e]);
})
}
let requestConfig = {
headers: {
"Content-Type": "multipart/form-data"
}
};
AjaxHelper.post(_this.apiURL, fileFormData, requestConfig)
.then(res => {
console.log(res);
if (res.success) {
const result = res.result;
if (result.errorCount == 0 && result.successCount > 0) {
_this.$message({
message: `導(dǎo)入成功,成功${result.successCount}條`,
type: "success"
});
_this.closeFileUpload();
_this.Refresh();
} else if (result.errorCount > 0 && result.successCount >= 0) {
_this.Refresh();
_this.tableData = result.uploadErrors;
_this.successCount = result.successCount;
_this.innerVisible = true;
} else if (result.errorCount == 0 && result.successCount == 0) {
_this.$message({
message: `上傳文件中數(shù)據(jù)為空`,
type: "error"
});
}
}
})
.catch(function(error) {
console.log(error);
});
},這是上傳文件的調(diào)用方法。
2.模板下載
關(guān)于模板下載，之前沒(méi)有考慮到IE10瀏覽器的兼容問(wèn)題，導(dǎo)致在IE10下文件沒(méi)法下載，后來(lái)百度后找到了解決辦法。
downloadFile(name) {
let requestConfig = {
headers: {
"Content-Type": "application/json;application/octet-stream"
}
};
AjaxHelper.post(this.downLoadUrl, requestConfig, {
responseType: "blob"
}).then(res => {
// 處理返回的文件流
const content = res.data;
const blob = new Blob([content]);
var date =
new Date().getFullYear() +
"" +
(new Date().getMonth() + 1) +
"" +
new Date().getDate();
const fileName = date + name + ".xlsx";
if ("download" in document.createElement("a")) {
// 非IE下載
const elink = document.createElement("a");
elink.download = fileName;
elink.style.display = "none";
elink.href = URL.createObjectURL(blob);
document.body.appendChild(elink);
elink.click();
URL.revokeObjectURL(elink.href); // 釋放URL 對(duì)象
document.body.removeChild(elink);
} else {
// IE10+下載
navigator.msSaveBlob(blob, fileName);
}
});
},前端的處理就結(jié)束了。
3.后端對(duì)于文件上傳和下載的處理
文件上傳
public UploadResult UploadFiles(IFormFile file, Guid brandId)
{
try
{
UploadResult uploadResult = new UploadResult();
if (file == null)
{
throw new UserFriendlyException(501, "上傳的文件為空，請(qǐng)重新上傳");
}
string filename = Path.GetFileName(file.FileName);
string fileEx = Path.GetExtension(filename);//獲取上傳文件的擴(kuò)展名
string NoFileName = Path.GetFileNameWithoutExtension(filename);//獲取無(wú)擴(kuò)展名的文件名
string FileType = ".xls,.xlsx";//定義上傳文件的類型字符串
if (!FileType.Contains(fileEx))
{
throw new UserFriendlyException(501, "無(wú)效的文件類型，只支持.xls和.xlsx文件");
}
//源數(shù)據(jù)
MemoryStream msSource = new MemoryStream();
file.CopyTo(msSource);
msSource.Seek(0, SeekOrigin.Begin);
DataTable sourceExcel = ReadStreamToDataTable(msSource, "", true);
//模板數(shù)據(jù)
string dataDir = _hosting.WebRootPath;//獲得當(dāng)前服務(wù)器程序的運(yùn)行目錄
dataDir = Path.Combine(dataDir, "ExcelTemplate");
var path = dataDir + "http://檔案模版.xlsx";
MemoryStream msModel = new MemoryStream();
FileStream stream = new FileStream(path, FileMode.Open);
stream.CopyTo(msModel);
msModel.Seek(0, SeekOrigin.Begin);
DataTable templateExcel = ReadStreamToDataTable(stream, "", true);
//驗(yàn)證是否同模板相同
string columnName = templateExcel.Columns[0].ColumnName;
if (columnName != sourceExcel.Columns[0].ColumnName)
{
throw new UserFriendlyException(501, "上傳的模板文件不正確");
}
int sucessCount = 0;
int errorCount = 0;
// 處理后臺(tái)邏輯 執(zhí)行 插入操作
uploadResult.SuccessCount = sucessCount;
uploadResult.ErrorCount = errorCount;
uploadResult.uploadErrors = errorList;
return uploadResult;
}
catch (Exception ex)
{
throw new UserFriendlyException(501, "上傳的模板文件不正確");
}
}將文件流轉(zhuǎn)化為Datable
public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
{
//定義要返回的datatable對(duì)象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//數(shù)據(jù)開(kāi)始行(排除標(biāo)題行)
int startRow = 0;
try
{
//根據(jù)文件流創(chuàng)建excel數(shù)據(jù)結(jié)構(gòu),NPOI的工廠類WorkbookFactory會(huì)自動(dòng)識(shí)別excel版本，創(chuàng)建出不同的excel數(shù)據(jù)結(jié)構(gòu)
IWorkbook workbook = WorkbookFactory.Create(fileStream);
//如果有指定工作表名稱
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果沒(méi)有找到指定的sheetName對(duì)應(yīng)的sheet，則嘗試獲取第一個(gè)sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果沒(méi)有指定的sheetName，則嘗試獲取第一個(gè)sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//一行最后一個(gè)cell的編號(hào) 即總的列數(shù)
int cellCount = firstRow.LastCellNum;
//如果第一行是標(biāo)題列名
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;
}
//最后一列的標(biāo)號(hào)
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.FirstCellNum < 0) continue; //沒(méi)有數(shù)據(jù)的行默認(rèn)是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理，沒(méi)有數(shù)據(jù)的單元格都默認(rèn)是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;
}
}
文件下載比較簡(jiǎn)單
public async Task DownloadFiles()
{
string dataDir = _hosting.WebRootPath;//獲得當(dāng)前服務(wù)器程序的運(yùn)行目錄
dataDir = Path.Combine(dataDir, "ExcelTemplate");
var path = dataDir + "http://檔案模版.xlsx";
var memoryStream = new MemoryStream();
using (var stream = new FileStream(path, FileMode.Open))
{
await stream.CopyToAsync(memoryStream);
}
memoryStream.Seek(0, SeekOrigin.Begin);
return new FileStreamResult(memoryStream, "application/octet-stream");//文件流方式，指定文件流對(duì)應(yīng)的ContenType。
} 文件下載比較簡(jiǎn)單
public async TaskDownloadFiles() { string dataDir = _hosting.WebRootPath;//獲得當(dāng)前服務(wù)器程序的運(yùn)行目錄 dataDir = Path.Combine(dataDir, "ExcelTemplate"); var path = dataDir + "http://檔案模版.xlsx"; var memoryStream = new MemoryStream(); using (var stream = new FileStream(path, FileMode.Open)) { await stream.CopyToAsync(memoryStream); } memoryStream.Seek(0, SeekOrigin.Begin); return new FileStreamResult(memoryStream, "application/octet-stream");//文件流方式，指定文件流對(duì)應(yīng)的ContenType。 }
文件上傳結(jié)果通知類
public class UploadResult
{
public int RepeatCount { get; set; }
public int SuccessCount { get; set; }
public int FileRepeatCount { get; set; }
public int ErrorCount { get; set; }
public List uploadErrors { get; set; }
}
public class UploadErrorDto
{
public string RowIndex { get; set; }
public string ErrorCol { get; set; }
public string ErrorData { get; set; }
} 通過(guò)以上處理后，我們就可以在前端實(shí)現(xiàn)文件的上傳了，若上傳失敗則會(huì)返回失敗結(jié)果
以上就是整個(gè)前后端關(guān)于文件上傳與下載的實(shí)現(xiàn)，想通過(guò)日常記錄這種方式，來(lái)幫助自己更好的掌握基礎(chǔ)，穩(wěn)固自己的技能
總結(jié)
以上所述是小編給大家介紹的vue實(shí)現(xiàn)Excel文件的上傳與下載功能的兩種方式，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)歡迎給我留言，小編會(huì)及時(shí)回復(fù)大家的！
