前端代碼
<div style="float: left; margin-top: 10px; margin-right: 30px; ">
<button class="btn btn-primary" type="button" onclick="Export()">
導出
</button>
</div>
<script type="text/javascript">
function Export() {
var data = {};
data["CODE"] = $("#CODE").val();
data["FACTORYNAME"] = $("#FACTORYNAME").val();
data["CREATEDON"] = $("#CREATEDON").val();
data["CREATEDON1"] = $("#CREATEDON1").val();
data["STATE"] = $("#STATE").val();
$.ajax({
url: "/PersonnelModule/YH_SPEED_APPLY/Export?r=" + Math.round(Math.random() * 10000),
data: data,
type: "POST",
cache: false,
async: false,
dataType: "json",
success: function (result) {
if (result.url == "") {
alert("導出失敗");
}
else {
window.location.href = result.url;
}
}
});
}
</script>
後端代碼
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
//Excel導出
public ActionResult Export(string CUSTOMERNAME, string CODE, string FACTORYNAME, string CREATEDON, string CREATEDON1, string STATE, bootstrapTableParam bootstrapTableParam)
{
using (MiniPoco.Database db = new MiniPoco.Database("PDDA"))
{
try
{
string sqlWhere = " where 1 = 1 ";
if (!string.IsNullOrEmpty(CODE))
{
sqlWhere += string.Format(@" and A.CODE like '%{0}%' ", CODE);
}
if (!string.IsNullOrEmpty(CUSTOMERNAME))
{
sqlWhere += string.Format(@" and A.CUSTOMERNAME like '%{0}%' ", CUSTOMERNAME);
}
if (!string.IsNullOrEmpty(FACTORYNAME))
{
sqlWhere += string.Format(@" and A.FACTORYNAME = '{0}' ", FACTORYNAME);
}
if (!string.IsNullOrEmpty(CREATEDON))
{
sqlWhere += string.Format(@" and to_date(to_char(A.CREATEDON,'yyyy-MM-dd'),'yyyy-MM-dd') >= to_date('{0}','yyyy-MM-dd')", CREATEDON);
}
if (!string.IsNullOrEmpty(CREATEDON1))
{
sqlWhere += string.Format(@" and to_date(to_char(A.CREATEDON,'yyyy-MM-dd'),'yyyy-MM-dd') <= to_date('{0}','yyyy-MM-dd')", CREATEDON1);
}
if (!string.IsNullOrEmpty(STATE))
{
sqlWhere += string.Format(@" and A.STATE ='{0}' ", STATE);
}
//加載要導出的資料
List<Hashtable> ListData = new List<Hashtable>();
string sql = string.Format(@"select A.* from YH_SPEED_APPLY A {0} order by A.CREATEDON desc", sqlWhere);
ListData = db.GetHashtable(sql);
//設定要導出的位置
string fileName = string.Format("YH_SPEED_APPLY_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"));
string serverPath = Server.MapPath("~/Upload/DownloadExcal/YH_SPEED_APPLY/");
if (!Directory.Exists(serverPath))
{
Directory.CreateDirectory(serverPath);
}
string fileUrl = Path.Combine(serverPath, fileName);
FileInfo info = new FileInfo(fileUrl);
if (info.Exists)
{
info.Delete();
info = new FileInfo(fileUrl);
}
using (ExcelPackage package = new ExcelPackage(info))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("印花車速申請");
ListToExcel(worksheet, ListData);
package.Save();
}
string path = "/Upload/DownloadExcal/YH_SPEED_APPLY/" + fileName;
Hashtable result = new Hashtable();
result["url"] = path;
return Json(result);
}
catch (Exception ex)
{
return Content(ex.Message);
}
}
}
//清單轉為Excel
public void ListToExcel(ExcelWorksheet excelWorksheet, List<Hashtable> list)
{
//定義Excel行數
int lineNumber = 1, columnNumber = 1;
//繪制Excel總标題
excelWorksheet.Cells[lineNumber, 1, lineNumber, 14].Value = "印花車速申請";
excelWorksheet.Cells[lineNumber, 1, lineNumber, 14].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
excelWorksheet.Cells[lineNumber, 1, lineNumber, 14].Style.Font.SetFromFont(new Font("宋體", 20f, FontStyle.Regular));
excelWorksheet.Cells[lineNumber, 1, lineNumber, 14].Merge = true;
//繪制Excel清單标題
lineNumber++; columnNumber = 0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申請分廠";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申請日期";
excelWorksheet.Column(columnNumber).Width = 20.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申請人";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申請類型";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申請機台";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "狀态";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "客戶名稱";
excelWorksheet.Column(columnNumber).Width = 25.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "色号";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "品名";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "米數";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申請車速";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "磁棒";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "車速申請的原因";
excelWorksheet.Column(columnNumber).Width = 25.0;
excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "其他備注";
excelWorksheet.Column(columnNumber).Width = 15.0;
excelWorksheet.Cells[lineNumber, 1, lineNumber, columnNumber].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//繪制Excel清單
lineNumber++;
if (list.Count > 0)
{
foreach (var items in list)
{
columnNumber = 0;
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["FACTORYNAME"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["CREATEDON"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["CREATEDNAME"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["TYPE"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["MACHINE"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["STATE"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["CUSTOMERNAME"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["COLORCODE"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["PRODUCTNAME"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["METERS"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["SPEED"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["MAGNETICBAR"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["REASON"].ToString());
DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["REMARK"].ToString());
excelWorksheet.Row(lineNumber).Height = 20.0;
lineNumber++;
}
}
}
//繪制清單單元格
public void DrawExcelListCell(ExcelWorksheet excelWorksheet, int lineNumber, int columnNumber, string value)
{
using (ExcelRange range = excelWorksheet.Cells[lineNumber, columnNumber])
{
range.Value = value;
range.Style.Font.SetFromFont(new Font("宋體", 10f, FontStyle.Regular));
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
}
}