Rest api to read and save data from excel
Excel uploaded by VUE js interface.
Build excel file, and save on server and download when download button clicked.
<!-- Browse and upload -->
<input type="file" @change="updateImage" />
<button class="btn btn-info" @click="submitFile">Submit</button>
<!-------download file--------->
<button class="btn btn-info" @click="downloadFile">Download</button>
<!---------------->
Below is the java script code
<script>
import axios from "axios";
export default {
name: "GroupUsers",
components: {
//GroupUsers
},
data: function() {
return {
fileName: "",
fileData: null
};
},
methods: {
downloadFile: async function() {
await axios({
method: "post",
url: "Part/GetFile",
headers: {
"Content-Type":
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
},
responseType: "blob"
}).then(response => {
const url = window.URL.createObjectURL(new Blob([response.data]));
const link = document.createElement("a");
link.href = url;
link.setAttribute("download", "file.xlsx"); //or any other extension
document.body.appendChild(link);
link.click();
});
},
updateImage: function(event) {
this.fileData = event.target.files[0];
this.fileName = this.fileData.name;
console.log(this.fileData);
},
submitFile: function() {
const fData = new FormData();
fData.append("file", this.fileData);
fData.append("name", this.fileName);
axios({
method: "post",
url: "Part/myfileupload",
data: fData,
headers: {
"Content-Type": "multipart/form-data",
fileName: this.fileName
}
})
.then(response => {
console.log(response.data);
})
.catch(function(err) {
console.log(err);
});
}
}
};
</script>
WEB API C# code
[HttpPost]
[Route("api/Part/GetFile")]
public HttpResponseMessage GetFile()
{
var dataBytes = File.ReadAllBytes(HttpContext.Current.Server.MapPath("~/EO_Fcst_Adjustment_Admin (1).xlsx"));
var dataStream = new MemoryStream(dataBytes);
HttpResponseMessage httpResponseMessage = Request.CreateResponse(HttpStatusCode.OK);
httpResponseMessage.Content = new StreamContent(dataStream);
httpResponseMessage.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
httpResponseMessage.Content.Headers.ContentDisposition.FileName = "EO_Fcst_Adjustment_Admin(1).xlsx";
httpResponseMessage.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");
return httpResponseMessage;
}
[Route("api/Part/myfileupload")]
[HttpPost]
public string MyFileUpload()
{
string message = "";
var httpRequest = HttpContext.Current.Request;
foreach (string file in httpRequest.Files)
{
var postedFile = httpRequest.Files[file];
var filePath = HttpContext.Current.Server.MapPath("~/" + postedFile.FileName);
postedFile.SaveAs(filePath);
// NOTE: To store in memory use postedFile.InputStream
DataTable dtExcel = new DataTable();
AMAT.Utilities.ExcelComponent.ExcelReader oExcelReader = new AMAT.Utilities.ExcelComponent.ExcelReader(postedFile.InputStream, "EO Fcst Adjustment");
if (oExcelReader.CurrentSheet == null)
{
message = "failed";
}
int cols = 5;
try { oExcelReader.ReadDocument(6, ref dtExcel); cols = 6; }
catch (Exception ex) { oExcelReader.ReadDocument(5, ref dtExcel); cols = 5; }
dtExcel = new DataTable();
if (oExcelReader.ReadDocument(cols, ref dtExcel) && dtExcel.Rows.Count > 0)
{
message = "success";
}
}
string star = HttpContext.Current.Request.LogonUserIdentity.Name;
return message;
}
================updated one==========
[HttpPost]
[Route("api/Part/ExportCmntFile")]
public HttpResponseMessage GetCmntFileExport(Comment cmnt)
{
DataSet dsResult = new DataSet();
try
{
dsResult = new PartViewerBAL().GetCommonResult(cmnt.p1, cmnt.p2, cmnt.p3, cmnt.key, cmnt.user);
}
catch (Exception ex)
{
Common.WriteToLog("AIMS_API", "GetCmntFileExport()", ex);
}
using (ExcelPackage p = new ExcelPackage())
{
ExcelWorksheet ws1 = p.Workbook.Worksheets.Add("Material Comments");
ws1.Cells["A1"].LoadFromDataTable(dsResult.Tables[0], true, OfficeOpenXml.Table.TableStyles.None);
Byte[] bin = p.GetAsByteArray();
var dataStream = new MemoryStream(bin);
HttpResponseMessage httpResponseMessage = Request.CreateResponse(HttpStatusCode.OK);
httpResponseMessage.Content = new StreamContent(dataStream);
httpResponseMessage.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
httpResponseMessage.Content.Headers.ContentDisposition.FileName = "Material_Comments.xlsx";
httpResponseMessage.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");
return httpResponseMessage;
}
}
[HttpPost]
[Route("api/Part/GetTemplateExport")]
public HttpResponseMessage GetTemplateExport(Comment cmnt)
{
DataSet dsResult = new DataSet();
try
{
dsResult = new PartViewerBAL().GetCommonResult(cmnt.p1, cmnt.p2, cmnt.p3, cmnt.key, cmnt.user);
}
catch (Exception ex)
{
Common.WriteToLog("AIMS_API", "GetTemplateExport()", ex);
}
using (ExcelPackage p = new ExcelPackage())
{
ExcelWorksheet ws1 = p.Workbook.Worksheets.Add("Material Comments");
ws1.Cells["A1"].LoadFromDataTable(dsResult.Tables[0], true, OfficeOpenXml.Table.TableStyles.None);
ws1.Cells.AutoFitColumns();
Byte[] bin = p.GetAsByteArray();
var dataStream = new MemoryStream(bin);
HttpResponseMessage httpResponseMessage = Request.CreateResponse(HttpStatusCode.OK);
httpResponseMessage.Content = new StreamContent(dataStream);
httpResponseMessage.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
httpResponseMessage.Content.Headers.ContentDisposition.FileName = "Material_Comments_Template.xlsx";
httpResponseMessage.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");
return httpResponseMessage;
}
}
[Route("api/Part/cmntFileUpload")]
[HttpPost]
public Result CmntFileUpload()
{
Result er = new Result();
try
{
var httpRequest = HttpContext.Current.Request;
foreach (string file in httpRequest.Files)
{
var postedFile = httpRequest.Files[file];
DataTable dtExcel = new DataTable();
AMAT.Utilities.ExcelComponent.ExcelReader oExcelReader = new AMAT.Utilities.ExcelComponent.ExcelReader(postedFile.InputStream, "Material Comments");
if (oExcelReader.CurrentSheet == null)
{
er.IsSuccess = "failed";
er.Message = "Incorrect File.";
}
oExcelReader.ReadDocument(8, ref dtExcel);
string user = httpRequest.Params["user"];
if (dtExcel.Columns[0].ColumnName == "Part Number" && dtExcel.Columns[1].ColumnName == "Comment Type" && dtExcel.Columns[2].ColumnName == "Comments" && dtExcel.Columns[3].ColumnName == "Reason Code 1"
&& dtExcel.Columns[4].ColumnName == "Reason Code 2" && dtExcel.Columns[5].ColumnName == "Follow Up Date" && dtExcel.Columns[6].ColumnName == "Mitigation Plan" && dtExcel.Columns[7].ColumnName == "Quality Of Comment") { }
else
{
er.IsSuccess = "failed";
er.Message = "Incorrect File.";
}
DataSet dsConfigRes = new PartViewerBAL().GetCommonResult("", "", "", httpRequest.Params["configKey"], user);
string msg = "";
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
string part = Convert.ToString(dtExcel.Rows[i]["Part Number"]).Trim();
string type = Convert.ToString(dtExcel.Rows[i]["Comment Type"]).Trim();
string comments = Convert.ToString(dtExcel.Rows[i]["Comments"]).Trim();
string loaddate = Convert.ToString(dtExcel.Rows[i]["Follow Up Date"]).Trim();
string reason1 = Convert.ToString(dtExcel.Rows[i]["Reason Code 1"]).Trim();
string reason2 = Convert.ToString(dtExcel.Rows[i]["Reason Code 2"]).Trim();
string mitigation = Convert.ToString(dtExcel.Rows[i]["Mitigation Plan"]).Trim();
string quality = Convert.ToString(dtExcel.Rows[i]["Quality Of Comment"]).Trim();
DataRow[] drType = dsConfigRes.Tables[1].Select("CommentType='" + type + "'");
DataRow[] drDdlRsn1 = dsConfigRes.Tables[2].Select("CommentType='" + type + "' AND Field='1' AND Value='" + reason1 + "'");
DataRow[] drDdlRsn2 = dsConfigRes.Tables[2].Select("CommentType='" + type + "' AND Field='2' AND Value='" + reason2 + "'");
DataRow[] drDdlQual = dsConfigRes.Tables[2].Select("CommentType='" + type + "' AND Field='Q' AND Value='" + quality + "'");
if (part == "" || type == "" || comments == "" || loaddate == "")
{
msg += " " + (i + 2) + ", ";
}
if (loaddate != "")
{
try
{
DateTime date;
if (DateTime.TryParse(loaddate, out date)) { }
else
{
date = DateTime.FromOADate(Convert.ToDouble(loaddate));
}
if (date < DateTime.Now.Date)
{
msg += " " + (i + 2) + ", ";
}
}
catch (Exception e1)
{
msg += " " + (i + 2) + ", ";
}
}
if (drType.Length < 1)
{
msg += " CTypeErr:" + (i + 2) + ", ";
}
else if (drType.Length > 0 && (
((Convert.ToString(drType[0]["MitigationPlan"]).ToLower() == "true" || Convert.ToString(drType[0]["MitigationPlan"]).ToLower() == "1") && mitigation == "") ||
((Convert.ToString(drType[0]["RevisitDate"]).ToLower() == "true" || Convert.ToString(drType[0]["RevisitDate"]).ToLower() == "1") && loaddate == "") ||
//((Convert.ToString(drType[0]["QualityOfComment"]).ToLower() == "true" || Convert.ToString(drType[0]["QualityOfComment"]).ToLower() == "1") && quality == "") ||
(Convert.ToString(drType[0]["ReasonCode1"]) != "" && reason1 == "") ||
(Convert.ToString(drType[0]["ReasonCode2"]) != "" && reason2 == "")
)
)
{ msg += " " + (i + 2) + ", "; }
else if (drType.Length > 0 && (
Convert.ToString(drType[0]["QualityOfComment"]).ToLower() == "true" || Convert.ToString(drType[0]["QualityOfComment"]).ToLower() == "1"
) && drDdlQual.Length < 1 && quality != "")
{
msg += " " + (i + 2) + ", ";
}
else if (drType.Length > 0 && Convert.ToString(drType[0]["ReasonCode1"]).Trim() != "" && drDdlRsn1.Length < 1)
{
msg += " " + (i + 2) + ", ";
}
else if (drType.Length > 0 && Convert.ToString(drType[0]["ReasonCode2"]).Trim() != "" && drDdlRsn2.Length < 1)
{
msg += " " + (i + 2) + ", ";
}
}
if (er.IsSuccess != "failed")
{
if (msg != "")
{
er.IsSuccess = "failed";
er.Message = "Input Validation Failed – Upload Rejected. Row details:" + msg;
}
else
{
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
string part = Convert.ToString(dtExcel.Rows[i]["Part Number"]).Trim();
string type = Convert.ToString(dtExcel.Rows[i]["Comment Type"]).Trim();
string comments = Convert.ToString(dtExcel.Rows[i]["Comments"]).Trim();
string loaddate = Convert.ToString(dtExcel.Rows[i]["Follow Up Date"]).Trim();
string reason1 = Convert.ToString(dtExcel.Rows[i]["Reason Code 1"]).Trim();
string reason2 = Convert.ToString(dtExcel.Rows[i]["Reason Code 2"]).Trim();
string mitigation = Convert.ToString(dtExcel.Rows[i]["Mitigation Plan"]).Trim();
string quality = Convert.ToString(dtExcel.Rows[i]["Quality Of Comment"]).Trim();
DataSet dsRes = new PartViewerBAL().UpdateComments("", part, type, loaddate, comments, reason1, reason2, mitigation, loaddate, quality, user);
if (dsRes != null && dsRes.Tables.Count > 0 && dsRes.Tables[0].Rows.Count > 0)
{
er.IsSuccess = "success";
er.Message += Convert.ToString(i + 2) + ", ";
}
}
er.Message = "Success rows: " + er.Message;
}
}
}
}
catch (Exception ex)
{
er.IsSuccess = "failed";
er.Message = ex.Message;
}
return er;
}
---------Client scripts -------
searchExport: async function() {
this.$store.dispatch("showLoader");
let objP1 = {};
objP1.PartNumber = this.mdlPartNumber.trim();
objP1.CommentType = this.mdlCommentType.trim();
objP1.Pim = this.srchPim + "";
objP1.PimOwner = this.srchPimOnr + "";
objP1.Pbg = this.srchPbg + "";
objP1.GppOwner = this.srchGppOnr + "";
let obj = {};
obj.p1 = JSON.stringify(objP1);
obj.p2 = "";
obj.p3 = "";
obj.key = "cmnt_part_list_export";
obj.user = this.$store.state.LUser;
if (this.$store.state.LUser === "")
await this.$store.dispatch("loginUser");
await axios({
url: "Part/ExportCmntFile",
method: "POST",
data: obj,
responseType: "blob" // important
}).then(response => {
const url = window.URL.createObjectURL(new Blob([response.data]));
const link = document.createElement("a");
link.href = url;
link.setAttribute("download", "Material_Comments.xlsx"); //or any other extension
document.body.appendChild(link);
link.click();
});
this.$store.dispatch("hideLoader");
},
templateExport: async function() {
this.$store.dispatch("showLoader");
let obj = {};
obj.p1 = "";
obj.p2 = "";
obj.p3 = "";
obj.key = "cmnt_template_export";
obj.user = this.$store.state.LUser;
if (this.$store.state.LUser === "")
await this.$store.dispatch("loginUser");
await axios({
url: "Part/GetTemplateExport",
method: "POST",
data: obj,
responseType: "blob" // important
}).then(response => {
const url = window.URL.createObjectURL(new Blob([response.data]));
const link = document.createElement("a");
link.href = url;
link.setAttribute("download", "Material_Comments_Template.xlsx"); //or any other extension
document.body.appendChild(link);
link.click();
});
this.$store.dispatch("hideLoader");
},
uploadExport: async function() {
if(this.cmntFileData===null){
alert('Attachment required!');
return;
}
this.$store.dispatch("showLoader");
const fData = new FormData();
fData.append("file", this.cmntFileData);
fData.append("user", this.$store.state.LUser);
fData.append("configKey", "cmnt_load");
await axios({
method: "post",
url: "Part/cmntFileUpload",
data: fData,
headers: {
"Content-Type": "multipart/form-data",
fileName: this.fileName
}
})
.then(response => {
let res = response.data;
alert(res.Message);
this.cmntFileData = null;
this.$refs.cmntFileInput.value = null;
this.search();
})
.catch(function(err) {
console.log(err);
});
this.$store.dispatch("hideLoader");
}
