function btnExport() {
var colArr = [];
if(colArr.length == 0){
alert("请查询要导出的数据!");
return;
}
var searchdata = {
AreaNO: $('#chooseBox').val(),
StartTime: sDateResult,
EndTime: sDateResult,
ColNames: colArr
};
var postData = { QueryType: 1, FileName: "你想要导出的文件名称", QueryParms: JSON.stringify(searchdata) };
$.post("/Export/ExcelExport", postData, function (data) {
if (data) {
location.href = '/Export/GetExcelFile?download=' + data;
}
});
}
public class ExportController : Controller
{
public FileResult GetExcelFile(string download)
{
FileStream fs = (FileStream)Session[download];
Session.Remove(download);
return File(fs, "application/vnd.ms-excel", download);
}
public string ExcelExport(int queryType, string fileName, string queryParms)
{
QueryParmsObj o = new QueryParmsObj();
QueryParmsObj request = (QueryParmsObj)JsonToObject(queryParms, o);
System.IO.FileStream fs;
fileName = fileName + "_导出数据_" + DateTime.Now.ToString("yyyyMMddHHmmss");
string download = string.Format(@"{0}.xlsx", fileName);
try
{
IWorkbook workbook = new HSSFWorkbook();
if (queryType == 1)
{
var otherController = DependencyResolver.Current.GetService<FinancialAnalysisController>();
var response = otherController.FinancialAnalysisMConerlistData(request.AreaNO,request.StartTime,request.EndTime,null,null,null,null);
JObject list = (JObject)JsonConvert.DeserializeObject(response);
GetRowsOfType1(workbook, list, fileName,request.ColNames);
}
else if (queryType == 2)
{
}
string pathString = Request.ApplicationPath;
var mappath = Server.MapPath(pathString);
var domainPath = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
if (!Directory.Exists(string.Format("{0}\\Report", domainPath)))
{
Directory.CreateDirectory(string.Format("{0}\\Report", domainPath));
}
string filePath = String.Format(@"{0}\\Report\\{1}_{2}.xlsx", domainPath, fileName, DateTime.Now.ToString("yyyyMMddHHmmss"));
fs = new FileStream(filePath, FileMode.OpenOrCreate);
workbook.Write(fs);
fs.Seek(0, SeekOrigin.Begin);
}
catch (Exception)
{
throw new Exception();
}
Session[download] = fs;
return download;
}
private IWorkbook GetRowsOfType1(IWorkbook workbook, JObject list, string name,List<string> colNames)
{
ISheet sheet1 = workbook.CreateSheet("Sheet1");
int colSpan = colNames.Count-1;
SetSheetTitle(workbook, sheet1, name,colSpan);
IRow row2 = sheet1.CreateRow(2);
for (var i = 0; i < colNames.Count; i++)
{
row2.CreateCell(i).SetCellValue(colNames[i]);
}
var listData = list["data"];
for (int i = 0; i < listData.ToList().Count; i++)
{
IRow rowtemp = sheet1.CreateRow(i + 3);
var cellVal = "";
for (int j = 0; j < listData[i].ToList().Count; j++)
{
cellVal = listData[i].ToList()[j].First.ToString();
rowtemp.CreateCell(j).SetCellValue(cellVal);
}
}
return workbook;
}
private void SetSheetTitle(IWorkbook workbook, ISheet sheet1, string name,int colSpan)
{
IRow row0 = sheet1.CreateRow(0);
row0.HeightInPoints = 30;
row0.CreateCell(0).SetCellValue(name);
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 17;
font.Boldweight = short.MaxValue;
ICellStyle style = workbook.CreateCellStyle();
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
row0.Cells[0].CellStyle = style; ;
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, colSpan);
sheet1.AddMergedRegion(cellRangeAddress);
IRow row1 = sheet1.CreateRow(1);
row1.HeightInPoints = 20;
row1.CreateCell(0).SetCellValue("制表日期:" + DateTime.Now.Date.ToString("yyyy-MM-dd"));
font = workbook.CreateFont();
font.FontHeightInPoints = 13;
font.Boldweight = short.MaxValue;
style = workbook.CreateCellStyle();
style.SetFont(font);
row1.Cells[0].CellStyle = style;
cellRangeAddress = new CellRangeAddress(1, 1, 0, colSpan);
sheet1.AddMergedRegion(cellRangeAddress);
}
public static object JsonToObject(string jsonString, object obj)
{
return JsonConvert.DeserializeObject(jsonString, obj.GetType());
}
private class QueryParmsObj
{
public string AreaNO { get; set; }
public string StartTime { get; set; }
public string EndTime { get; set; }
public List<string> ColNames { get; set; }
}
}