代码如下:
Net core后台代码:
public ActionResult ExportData(string condition)
{
List<Model> models = modelBLL.GetModelList(condition);
var workBook = new HSSFWorkbook();
var sheet = workBook.CreateSheet("导出数据");
//列宽
var dic=new Dictionary<int,int>();
var headerFont = workBook.CreateFont();
headerFont.IsBold = true;
var headerStyle = workBook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.SetFont(headerFont);
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
var cellStyle = workBook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
//设置行高
row.Height=200*2;
var cell = row.CreateCell(0);
cell.SetCellValue("国家编码");
cell.CellStyle = headerStyle;
dic.Add(0,Encoding.Default.GetBytes(cell.StringCellValue).Length*256+200);
cell = row.CreateCell(1);
cell.SetCellValue("国家名称");
cell.CellStyle = headerStyle;
dic.Add(1,Encoding.Default.GetBytes(cell.StringCellValue).Length*256+200);
cell = row.CreateCell(2);
cell.SetCellValue("所在州");
cell.CellStyle = headerStyle;
dic.Add(2,Encoding.Default.GetBytes(cell.StringCellValue).Length*256+200);
foreach (Model item in models)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
row.Height=200*2;
cell = row.CreateCell(0);
cell.SetCellValue(item.country_code);
cell.CellStyle = cellStyle;
if(GetColumnWidth(cell)>dic[0])
{
dic[0]=GetColumnWidth(cell);
}
cell = row.CreateCell(1);
cell.SetCellValue(item.country_name);
cell.CellStyle = cellStyle;
if(GetColumnWidth(cell)>dic[1])
{
dic[1]=GetColumnWidth(cell);
}
cell = row.CreateCell(2);
cell.SetCellValue(item.state);
cell.CellStyle = cellStyle;
if(GetColumnWidth(cell)>dic[2])
{
dic[2]=GetColumnWidth(cell);
}
}
//按照列数设置列宽
for(int i=0;i<3;i++)
{
sheet.SetColumnWidth(i,dic[i]);
}
byte[] buffer = null;
using (MemoryStream memoryStream = new MemoryStream())
{
workBook.Write(memoryStream);
buffer = memoryStream.GetBuffer();
memoryStream.Close();
}
//这是最重要的部分,返回文件流;test.xlsx可带可不带
return File(buffer,"application/octet-stream","test.xlsx");
}
public int GetColumnWidth(ICell cell)
{
int length=Encoding.Default.GetBytes(cell.StringCellValue).Length*256+200;
length=length>15000?15000:length;
return length;
}
js通过axios请求代码:
axios.get('/Controller/ExportData?condition=condition', { responseType: 'blob' })
.then(function (response) {
if (!response) { return; }
//最重要一步 new Blob([response.data],{type:response.data.type})
var blob = new Blob([response.data],{type:response.data.type});
var url=URL.createObjectURL(blob);
var link=document.createElement('a');
link.style.display="none";
link.href=url;
link.setAttribute('download','导出数据.xls');
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}).catch(function (error) {
console.log(error);
});