public async Task <IActionResult> OnPostExport([FromBody] Report report)
{
string sFileName = report.FileName;
ExcelImportExport excelExport = new ExcelImportExport(sFileName,
_hostingEnvironment, _context);
MemoryStream memory = new MemoryStream();
if (report.ReportName == "VwInvoiceWeightSummary")
{
var ReportList = _context.VwInvoiceWeightSummary.Where
(d => d.InvoiceDate >= report.StartDate && d.InvoiceDate <= report.EndDate &&
d.StatusID != 8 && d.StatusID != 10)
.ToList();
memory = await excelExport.CreateExcelFileAsync(ReportList);
}
if (report.ReportName == "VwInvoiceWeightsDetail")
{
var ReportList = _context.VwInvoiceWeightsDetail.Where
(d => d.InvoiceDate >= report.StartDate && d.InvoiceDate <= report.EndDate &&
d.StatusID != 8 && d.StatusID != 10)
.ToList();
memory = await excelExport.CreateExcelFileAsync(ReportList);
}
return(new JsonResult("File Created Successfully"));
}
public async Task <IActionResult> DownloadAsync(string filename)
{
//string virtualpath = _virtualPathFolder + filename;
string _sWebRootFolder = _hostingEnvironment.WebRootPath;
string _newPath = Path.Combine(_sWebRootFolder, filename);
//var NewFile = File(_newPath, System.Net.Mime.MediaTypeNames.Application.Octet, filename);
//return NewFile;
ExcelImportExport excelExport = new ExcelImportExport(filename,
_hostingEnvironment, _context);
MemoryStream memory = new MemoryStream();
try
{
memory = await excelExport.DownloadAsync(filename);
}
catch (Exception e)
{
// string url = "/Loads/LoadReport";
// Redirect(url);
}
var NewFile = File(memory, System.Net.Mime.MediaTypeNames.Application.Octet, filename);
System.IO.File.Delete(_newPath);
return(NewFile);
}
private void BtnImport_Click(object sender, EventArgs e)
{
var ds = default(DataSet);
dgWorkbook.DataSource = ds;
try
{
if (ofdExcel.ShowDialog() == DialogResult.OK)
{
switch (ofdExcel.SafeFileName.Substring(ofdExcel.SafeFileName.IndexOf(".") + 1).ToLower())
{
case "xls": //ds = DataSetHelper.CreateDataSet(ofdExcel.FileName); break;
case "xlsx": ds = ExcelImportExport.ImportExcelODBC(ofdExcel.FileName); break;
default:
Stream ss = File.OpenRead(ofdExcel.FileName);
ds = ExcelImportExport.ImportExcelXML(ss, true, false); break;
}
foreach (DataTable tabela in ds.Tables)
{
foreach (DataRow red in tabela.Rows)
{
string sred = string.Empty;
foreach (object polje in red.ItemArray)
{
sred = sred + polje.ToString().Trim();
}
foreach (DataColumn kolona in tabela.Columns)
{
if (red[kolona].ToString() == string.Empty)
{
switch (kolona.DataType.Name)
{
case "String": red[kolona] = ""; break;
case "DateTime": red[kolona] = DateTime.Today; break;
case "Boolean": red[kolona] = false; break;
default: red[kolona] = 0; break;
}
}
}
if (sred.Trim() == string.Empty)
{
red.Delete();
}
}
}
ds.AcceptChanges();
dgWorkbook.DataSource = ds;
ofdExcel.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//public async Task<IActionResult> OnPostUpload([FromBody] Report report)
public async Task <IActionResult> OnPostUpload(IFormFile reportfile)
{
ExcelImportExport excelImport = new ExcelImportExport(
_hostingEnvironment, _context);
excelImport.ImportUpload(reportfile);
return(new JsonResult("Price File Uploaded Successfully"));
}
public async Task <IActionResult> OnPostExport()
{
// string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = @"employeeDetails.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
// FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
// var memory = new MemoryStream();
ExcelImportExport excelExport = new ExcelImportExport(sFileName, _hostingEnvironment, _context);
List <VwEmployeeViewSalesRepCode> EmployeeList = _context.VwEmployeeViewSalesRepCode.ToList();
MemoryStream memory = await excelExport.CreateExcelFileAsync(EmployeeList);
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}
private void BtnExportLO_Click(object sender, EventArgs e)
{
try
{
using (var sd = new SaveFileDialog())
{
var ds = (DataSet)dgWorkbook.DataSource;
sd.FileName = ds.DataSetName;
sd.Filter = "Excel files|*.xls";
if (sd.ShowDialog() == DialogResult.OK)
{
ExcelImportExport.ExportExcelLO(sd.FileName, ds);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void brnLoadExcel_Click(object sender, EventArgs e)
{
using (var ofd = new OpenFileDialog())
{
try
{
if (ofd.ShowDialog() == DialogResult.OK)
{
ds = ExcelImportExport.ImportExcelODBC(ofd.FileName);
ds.DataSetName = ofd.SafeFileName.Split('.')[0];
ds = ObradiDataSet(ds);
dgImportExcel.DataSource = ds;
rtbJSON.Text = JsonConvert.SerializeObject(ds, Formatting.Indented);
rtbJSON.Text = rtbJSON.Text.Replace("\\n", "<br/>");
rtbJSON.Text = "var " + ds.DataSetName + "_data=" + rtbJSON.Text + ";";
}
}
catch (Exception ex)
{
throw ex;
}
}
}
public async Task <IActionResult> OnPostExport(Report report)
{
string sFileName = report.ReportName;
ExcelImportExport excelExport = new ExcelImportExport(sFileName,
_hostingEnvironment, _context);
MemoryStream memory = new MemoryStream();
if (report.ReportName == "VwLoadSummary")
{
var ReportList = _context.VwLoadSummary.Where
(d => d.LoadDate >= report.StartDate && d.LoadDate <= report.EndDate)
.ToList();
memory = await excelExport.CreateExcelFileAsync(ReportList);
}
if (report.ReportName == "VwLoadDetail")
{
var ReportList = _context.VwLoadDetail.Where
(d => d.LoadDate >= report.StartDate && d.LoadDate <= report.EndDate)
.ToList();
memory = await excelExport.CreateExcelFileAsync(ReportList);
}
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}
