일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 |
- Kakao API Address
- 나만의 상점
- 초딩수학
- Request.Form
- MYSQL
- 한번에 업데이트 인서트 하기
- 썸머노트
- chart.js
- swift 화면전환
- MSSQL
- 일본여행
- 보유중스킨
- python
- Banker's
- CSV
- league of legends
- upbit
- Banker's rounding
- rounding
- Oracle
- 한글깨짐
- .csv
- 업비트
- Excel
- MS-SQL
- Android
- LEFT JOIN
- merge match
- update / insert
- Aspose.cells
- Today
- Total
DBA
MVC ASP.NET 에서 Ajax를 이용한 파일 생성 및 다운로드 본문
DevExpress를 가지고 Excel파일을 생성해서 다운 로드 함.
[Script]
<script type="text/javascript">
$(document).ready(function () {
$("#btnExcelExport").click(function () {
var jsExportOption = $('#ddlExportOption').val();
$.ajax({
type: "POST",
url: "@Url.Action("CreateDataExportFiles", "Home", null)",
data: { viewname: "Established", fromCountKey: "00100", toCountKey: "00200", filter02: ",21,22,25", filter04: ",1,2,3", filter05: ",1,2,7", filter06: "", exportOption: jsExportOption },
contentType: "application/x-www-form-urlencoded; charset=UTF-8",
datatype: "json",
@*beforeSend: function () {
$('#resultTable01').html("<div style='background:url(@Url.Content("~/Images/ajax-loader.gif")) no-repeat center center;width:100%;height:150px;'></div>");
},*@
success: function (data) {
//alert(data);
if (data.fileName != "")
{
window.location.href = "@Url.RouteUrl(new { Controller = "Home", Action = "Download"})/?file=" + data.fileName + "&exportOption=" + jsExportOption;
}
//$('#resultTable01').html(data);
},
error: function (xhr, status, error) {
//alert("code: " + xhr.status);
//alert(error);
$('#resultTable01').html("No Result");
}
});
});
});
</script>
[Html]
<body>
<div class="wrapper">
<div class="panel panel-danger">
<div class="panel-body">
<div class="row">
<div class="col-lg-12" id="resultTable01">
</div>
</div>
<div class="row">
<div class="col-lg-12">
<select name="ddlExportOption" id="ddlExportOption">
<option value="1" selected="selected">Excel Workbook (*.xlsx)</option>
<option value="2">Excel 97-2003 Workbook (*.xls)</option>
@*<option value="3">Comma delimited (*.csv)</option>*@
@*<option value="4">PDF (*.pdf)</option>*@
</select>
<input type="button" id="btnExcelExport" class="btn-primary" value="Excel" />
</div>
</div>
</div>
</div>
</div>
</body>
[MVC ASP.NET]
using DevExpress.Spreadsheet;
using System.IO;
using System.Drawing;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Globalization;
using System.Net;
using DevExpressDemo.Models;
namespace DevExpressDemo.Controllers
{
public class HomeController : Controller
{
WRBrandDataContext BDC = new WRBrandDataContext();
// GET: Home
public ActionResult Index(int id)
{
string viewName = "ExcelExport";
switch (id)
{
case 1:
viewName = "ExcelExport";
break;
default:
viewName = "ExcelExport";
break;
}
return View(viewName);
}
private DocumentFormat ConvertIndexToFormat(int exportOption)
{
switch (exportOption)
{
case 1:
return DocumentFormat.OpenXml;
case 2:
return DocumentFormat.Xls;
case 3:
return DocumentFormat.Csv;
}
return DocumentFormat.Undefined;
}
private string ConvertIndexToContentType(int exportOption)
{
switch (exportOption)
{
case 1:
return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
case 2:
return "application/vnd.ms-excel";
case 3:
return "text/comma-separated-values";
case 4:
return "application/pdf";
}
return String.Empty;
}
private string ConvertIndexToFileExtension(int exportOption)
{
switch (exportOption)
{
case 1:
return ".xlsx";
case 2:
return ".xls";
case 3:
return ".csv";
case 4:
return ".pdf";
}
return String.Empty;
}
[HttpPost]
public JsonResult CreateDataExportFiles(string viewname, string fromCountKey, string toCountKey, string filter02, string filter04, string filter05, string filter06, int exportOption)
{
int fromCountKeySequence = int.Parse(BDC.T_StandardCalendars.Where(x => x.countKey == fromCountKey).Select(x => x.countKeySequence).FirstOrDefault().ToString());
int toCountKeySequence = int.Parse(BDC.T_StandardCalendars.Where(x => x.countKey == toCountKey).Select(x => x.countKeySequence).FirstOrDefault().ToString());
//////매개변수로 넘어온 값들에 대한 처리
ViewBag.currentView = viewname;
if (filter04 == String.Empty)
{
filter04 = null;
}
if (filter05 == String.Empty)
{
filter05 = null;
}
if (filter06 == String.Empty)
{
filter06 = null;
}
int countKeyInterval = 0;
int filter02Count = 0;
int filter04Count = 1;
int filter05Count = 1;
int filter06Count = 7;
if (fromCountKey != null && toCountKey != null)
countKeyInterval = toCountKeySequence - fromCountKeySequence + 1;
if (filter02 != null)
{
string[] filter02Split = filter02.Substring(1).Split(',');
filter02Count = filter02Split.Count();
}
if (filter04 != null)
{
string[] filter04Split = filter04.Substring(1).Split(',');
filter04Count = filter04Split.Count();
}
if (filter05 != null)
{
string[] filter05Split = filter05.Substring(1).Split(',');
filter05Count = filter05Split.Count();
}
if (filter06 != null)
{
string[] filter06Split = filter06.Substring(1).Split(',');
filter06Count = filter06Split.Count();
}
string fileName = "WRBrand" + DateTime.Now.ToString("yyyyMMddHHmm") + ConvertIndexToFileExtension(exportOption);
string fullPath = Path.Combine(Server.MapPath("~/App_Data"), fileName);
Workbook wb = new Workbook();
int wsNumber = 0;
Worksheet ws1 = wb.Worksheets[wsNumber];
//=================================================================================================================================================================================//
//////일단 BrandOnly는 무조건
ws1.Name = "BrandOnly";
int currentRow = 0;
int dataColumnStart = 2;
int dataColumnPointer = dataColumnStart;
List<WR_DataBrandOnly_RResult> result = BDC.WR_DataBrandOnly_R(fromCountKey, toCountKey, viewname, filter02, filter04, filter05, filter06, null).Select(x => x).ToList();
if (result.Count > 0)
{
foreach (var r in result)
{
////Header
if (currentRow == 0)
{
ws1.Cells[currentRow, 0].Value = "Brand";
ws1.Cells[currentRow, 0].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
ws1.Cells[currentRow, 0].Font.Color = Color.AliceBlue;
ws1.Cells[currentRow, 0].Font.Bold = true;
ws1.Cells[currentRow, 0].Font.Size = 10;
ws1.Cells[currentRow, 0].FillColor = Color.CornflowerBlue;
ws1.Cells[currentRow, 0].ColumnWidth = 500;
ws1.Cells[currentRow, 0].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, 0].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, 0].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, 0].Borders.BottomBorder.LineStyle = BorderLineStyle.Double;
ws1.Cells[currentRow, 1].Value = "Degree";
ws1.Cells[currentRow, 1].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
ws1.Cells[currentRow, 1].Font.Color = Color.AliceBlue;
ws1.Cells[currentRow, 1].Font.Bold = true;
ws1.Cells[currentRow, 1].Font.Size = 10;
ws1.Cells[currentRow, 1].FillColor = Color.CornflowerBlue;
ws1.Cells[currentRow, 1].ColumnWidth = 300;
ws1.Cells[currentRow, 1].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, 1].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, 1].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, 1].Borders.BottomBorder.LineStyle = BorderLineStyle.Double;
ws1.Cells[currentRow, dataColumnPointer].Value = r.qhName;
ws1.Cells[currentRow, dataColumnPointer].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
ws1.Cells[currentRow, dataColumnPointer].Font.Color = Color.AliceBlue;
ws1.Cells[currentRow, dataColumnPointer].Font.Bold = true;
ws1.Cells[currentRow, dataColumnPointer].Font.Size = 10;
ws1.Cells[currentRow, dataColumnPointer].FillColor = Color.CornflowerBlue;
ws1.Cells[currentRow, dataColumnPointer].ColumnWidth = 300;
ws1.Cells[currentRow, dataColumnPointer].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, dataColumnPointer].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, dataColumnPointer].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow, dataColumnPointer].Borders.BottomBorder.LineStyle = BorderLineStyle.Double;
}
////Data
ws1.Cells[currentRow + 1, 0].Value = r.productName;
ws1.Cells[currentRow + 1, 0].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
ws1.Cells[currentRow + 1, 0].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
ws1.Cells[currentRow + 1, 0].Font.Color = Color.SteelBlue;
ws1.Cells[currentRow + 1, 0].Font.Bold = true;
ws1.Cells[currentRow + 1, 0].Font.Size = 10;
ws1.Cells[currentRow + 1, 0].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, 0].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, 0].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, 0].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, 1].Value = r.degree;
ws1.Cells[currentRow + 1, 1].Font.Size = 10;
ws1.Cells[currentRow + 1, 1].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, 1].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, 1].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, 1].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
//ws1.Cells[currentRow + 1, dataColumnPointer].Value = Math.Round(decimal.Parse(r.rWeightPercentage.ToString()), 2, MidpointRounding.AwayFromZero).ToString();
ws1.Cells[currentRow + 1, dataColumnPointer].SetValue(Math.Round(decimal.Parse(r.rWeightPercentage.ToString()), 2, MidpointRounding.AwayFromZero));
//ws1.Cells[currentRow + 1, dataColumnPointer].NumberFormat = "#.#";
ws1.Cells[currentRow + 1, dataColumnPointer].Font.Size = 10;
ws1.Cells[currentRow + 1, dataColumnPointer].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, dataColumnPointer].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, dataColumnPointer].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws1.Cells[currentRow + 1, dataColumnPointer].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
dataColumnPointer++;
//ws1.MergeCells(ws1.Range.FromLTRB(0,currentRow, 5, currentRow));
//ws1.MergeCells(ws1.Range["G1:J6"]);
if (dataColumnPointer == dataColumnStart + filter06Count)
{
dataColumnPointer = dataColumnStart;
currentRow++;
}
}
Range usedRange = ws1.GetUsedRange();
for (int i = 0; i < usedRange.RowCount; i++)
{
if (i % countKeyInterval == 1)
{
ws1.MergeCells(ws1.Range.FromLTRB(0, i, 0, i + countKeyInterval - 1));
}
}
wsNumber++;
}
//=================================================================================================================================================================================//
//=================================================================================================================================================================================//
//////Brand X Filter04(Age)
if (filter04 != null)
{
wb.Worksheets.Add().Name = "Brand_Age";
Worksheet ws2 = wb.Worksheets[wsNumber];
currentRow = 0;
dataColumnStart = 3;
dataColumnPointer = dataColumnStart;
List<WR_DataBrandAge_RResult> resultBrandAge = BDC.WR_DataBrandAge_R(fromCountKey, toCountKey, viewname, filter02, filter04, filter05, filter06, 1).Select(x => x).ToList();
if (resultBrandAge.Count > 0)
{
foreach (var r in resultBrandAge)
{
if (currentRow == 0)
{
ws2.Cells[currentRow, 0].Value = "Brand";
ws2.Cells[currentRow, 0].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
ws2.Cells[currentRow, 0].Font.Color = Color.AliceBlue;
ws2.Cells[currentRow, 0].Font.Bold = true;
ws2.Cells[currentRow, 0].Font.Size = 10;
ws2.Cells[currentRow, 0].FillColor = Color.CornflowerBlue;
ws2.Cells[currentRow, 0].ColumnWidth = 500;
ws2.Cells[currentRow, 0].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 0].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 0].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 0].Borders.BottomBorder.LineStyle = BorderLineStyle.Double;
ws2.Cells[currentRow, 1].Value = "Question";
ws2.Cells[currentRow, 1].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
ws2.Cells[currentRow, 1].Font.Color = Color.AliceBlue;
ws2.Cells[currentRow, 1].Font.Bold = true;
ws2.Cells[currentRow, 1].Font.Size = 10;
ws2.Cells[currentRow, 1].FillColor = Color.CornflowerBlue;
ws2.Cells[currentRow, 1].ColumnWidth = 500;
ws2.Cells[currentRow, 1].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 1].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 1].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 1].Borders.BottomBorder.LineStyle = BorderLineStyle.Double;
ws2.Cells[currentRow, 2].Value = "Degree";
ws2.Cells[currentRow, 2].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
ws2.Cells[currentRow, 2].Font.Color = Color.AliceBlue;
ws2.Cells[currentRow, 2].Font.Bold = true;
ws2.Cells[currentRow, 2].Font.Size = 10;
ws2.Cells[currentRow, 2].FillColor = Color.CornflowerBlue;
ws2.Cells[currentRow, 2].ColumnWidth = 300;
ws2.Cells[currentRow, 2].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 2].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 2].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, 2].Borders.BottomBorder.LineStyle = BorderLineStyle.Double;
ws2.Cells[currentRow, dataColumnPointer].Value = r.condition;
ws2.Cells[currentRow, dataColumnPointer].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
ws2.Cells[currentRow, dataColumnPointer].Font.Color = Color.AliceBlue;
ws2.Cells[currentRow, dataColumnPointer].Font.Bold = true;
ws2.Cells[currentRow, dataColumnPointer].Font.Size = 10;
ws2.Cells[currentRow, dataColumnPointer].FillColor = Color.CornflowerBlue;
ws2.Cells[currentRow, dataColumnPointer].ColumnWidth = 300;
ws2.Cells[currentRow, dataColumnPointer].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, dataColumnPointer].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, dataColumnPointer].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow, dataColumnPointer].Borders.BottomBorder.LineStyle = BorderLineStyle.Double;
} //-> if (currentRow == 0) End
////Data
ws2.Cells[currentRow + 1, 0].Value = r.productName;
ws2.Cells[currentRow + 1, 0].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
ws2.Cells[currentRow + 1, 0].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
ws2.Cells[currentRow + 1, 0].Font.Color = Color.SteelBlue;
ws2.Cells[currentRow + 1, 0].Font.Bold = true;
ws2.Cells[currentRow + 1, 0].Font.Size = 10;
ws2.Cells[currentRow + 1, 0].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 0].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 0].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 0].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 1].Value = r.qhName;
ws2.Cells[currentRow + 1, 1].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
ws2.Cells[currentRow + 1, 1].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
ws2.Cells[currentRow + 1, 1].Font.Color = Color.SteelBlue;
ws2.Cells[currentRow + 1, 1].Font.Bold = true;
ws2.Cells[currentRow + 1, 1].Font.Size = 10;
ws2.Cells[currentRow + 1, 1].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 1].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 1].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 1].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 2].Value = r.degree;
ws2.Cells[currentRow + 1, 2].Font.Size = 10;
ws2.Cells[currentRow + 1, 2].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 2].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 2].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, 2].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, dataColumnPointer].SetValue(Math.Round(decimal.Parse(r.rWeightPercentage.ToString()), 2, MidpointRounding.AwayFromZero));
ws2.Cells[currentRow + 1, dataColumnPointer].Font.Size = 10;
ws2.Cells[currentRow + 1, dataColumnPointer].Borders.LeftBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, dataColumnPointer].Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, dataColumnPointer].Borders.TopBorder.LineStyle = BorderLineStyle.Thin;
ws2.Cells[currentRow + 1, dataColumnPointer].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
dataColumnPointer++;
if (dataColumnPointer == dataColumnStart + filter04Count)
{
dataColumnPointer = dataColumnStart;
currentRow++;
}
} //-> foreach (var r in resultBrandAge) End
Range usedRange = ws2.GetUsedRange();
for (int i = 0; i < usedRange.RowCount; i++)
{
if (i % countKeyInterval == 1)
{
ws2.MergeCells(ws2.Range.FromLTRB(1, i, 1, i + countKeyInterval - 1));
}
if (i % (filter06Count * countKeyInterval) == 1)
{
ws2.MergeCells(ws2.Range.FromLTRB(0, i, 0, i + (filter06Count * countKeyInterval) - 1));
}
}
//////WrokSheets Number를 증가시켜준다.
wsNumber++;
} //-> if (resultBrandAge.Count > 0) End
} //-> if (filter04 != null) End
//=================================================================================================================================================================================//
//////첫번째 Sheet를 활성화 시킴
wb.Worksheets.ActiveWorksheet = wb.Worksheets[0];
//////파일을 MemoryStream으로 생성해서 파일로 생성함
using (MemoryStream stream = new MemoryStream())
{
if (exportOption != 4)
{
wb.SaveDocument(stream, ConvertIndexToFormat(exportOption));
}
else
{
wb.ExportToPdf(stream);
}
FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
stream.WriteTo(file);
file.Close();
} //-> Using End
return Json(new { fileName = fileName, errorMessage = "" });
} //-> CreateDataExportFiles Function End
[HttpGet]
[DeleteFileAttribute] //Action Filter, it will auto delete the file after download,
//I will explain it later
public ActionResult Download(string file, int exportOption)
{
//get the temp folder and file path in server
string fullPath = Path.Combine(Server.MapPath("~/App_Data"), file);
//return the file for download, this is an Excel
//so I set the file content type to "application/vnd.ms-excel"
//////Xslx
return File(fullPath, ConvertIndexToContentType(exportOption), file);
//return File(fullPath, "application/pdf", file);
}
}
/// <summary>
/// 임시로 생성된 파일을 삭제하는 Class
/// </summary>
public class DeleteFileAttribute : ActionFilterAttribute
{
public override void OnResultExecuted(ResultExecutedContext filterContext)
{
filterContext.HttpContext.Response.Flush();
//convert the current filter context to file and get the file path
string filePath = (filterContext.Result as FilePathResult).FileName;
//delete the file after download
System.IO.File.Delete(filePath);
}
}
}
참고 : https://www.codeproject.com/Tips/1156485/How-to-Create-and-Download-File-with-Ajax-in-ASP-N