public async Task <IActionResult> FileUpload(IFormFile file)
{
try
{
if (file == null || file.Length == 0)
{
throw new NullReferenceException("上传文件失败");
}
var worksheet = await EpPlusExtension.GetWorksheetAsync(file);
var rowCount = worksheet.Dimension?.Rows;
var colCount = worksheet.Dimension?.Columns;
if (rowCount.HasValue && colCount.HasValue)
{
for (var row = 2; row < rowCount.Value; row++)
{
//姓名，手机号,部门，岗位
var realName = worksheet.Cells[row, 1].Value.ToString();
var phone = worksheet.Cells[row, 2].Value.ToString();
var post = worksheet.Cells[row, 3].Value.ToString();
var branch = worksheet.Cells[row, 4].Value.ToString();
var account = _accountService.GetObject(z => z.Phone == phone && !z.Flag);
//手机号不允许重复
}
_senparcEntities.SaveChanges();
}
SetMessager(MessageType.success, "上传成功");
}
catch (Exception)
{
SetMessager(MessageType.danger, "上传失败");
}
return(RedirectToAction("Index"));
}
public async Task <ActionResult> ExportToExcel()
{
var queryString = HttpUtility.ParseQueryString(Request.UrlReferrer.Query);
var searchString = queryString["SearchString"];
var fechaDesde = queryString["FechaDesde"];
var fechaHasta = queryString["FechaHasta"];
var pq = int.Parse(queryString["pq"].ToString());
var productQuoteStatusID = queryString["ProductQuoteStatusID"];
//Recuperacion de los datos
List <ProductQuote> productoQuotes = await GetProductQuotes(searchString, fechaDesde, fechaHasta, pq, productQuoteStatusID);
if (productoQuotes.Count == 0)
{
var res = new { ok = false, data = "La lista no tiene elementos" };
return(Json(res));
}
var result = from s in productoQuotes
select new ProductQuoteReportViewModel(s);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
byte[] response;
using (var excelFile = new ExcelPackage())
{
excelFile.Workbook.Properties.Title = "Cotizaciones";
var worksheet = excelFile.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells["A1"]
.LoadFromCollection(Collection: result, PrintHeaders: true);
using (ExcelRange Rng = worksheet.Cells["A1:FZ1"])
{
Rng.Style.Font.Bold = true;
//Rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGreen);
}
//Eliminamos CustomerId
worksheet.DeleteColumn(EpPlusExtension.ColumnLetterToColumnIndex("A"));
string DateCellFormat = "dd/MM/yyyy";
//Fecha Cotizacion
using (ExcelRange Rng = worksheet.Cells["D2:D" + (result.Count() + 1).ToString()])
{
Rng.Style.Numberformat.Format = DateCellFormat;
}
//Fecha Entrega
using (ExcelRange Rng = worksheet.Cells["E2:E" + (result.Count() + 1).ToString()])
{
Rng.Style.Numberformat.Format = DateCellFormat;
}
//Validez Precio
using (ExcelRange Rng = worksheet.Cells["Q2:Q" + (result.Count() + 1).ToString()])
{
Rng.Style.Numberformat.Format = DateCellFormat;
}
//Fecha Cierra
using (ExcelRange Rng = worksheet.Cells["CI2:CI" + (result.Count() + 1).ToString()])
{
Rng.Style.Numberformat.Format = DateCellFormat;
}
//Ocultamos campos extras
for (int i = 90; i < 166; i++)
{
worksheet.Column(i).Hidden = true;
}
worksheet.Cells.AutoFitColumns();
response = excelFile.GetAsByteArray();
}
// Save dialog appears through browser for user to save file as desired.
return(File(response, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"Cotizaciones-" + DateTime.Now.ToString("yyyy'-'MM'-'dd'T'HH'-'mm") + ".xlsx"));
}
