JS导出Excel （Base64）

 

点击导出按钮  导出excel  base64位

 

前端调用

$.ajax({
url: "/HEDSExportExcel/HEDSExportExcel", // 请求路径
type: "POST", //请求方式
data: { "username": "jack", "age": 23 },
success: function (data) {
ToExcel("选配参数.xls", data.ReturnValue);
alert(data);
},//响应成功后的回调函数
error: function () {
alert("出错啦...")
}//表示如果请求响应出现错误，会执行的回调函数
});

//JS导出Excel

function ToExcel(fileName, str) {
var raw = window.atob(str);
var uInt8Array = new Uint8Array(raw.length);
for (var i = 0; i < raw.length; i++) {
uInt8Array[i] = raw.charCodeAt(i);
}

const link = document.createElement("a");
const blob = new Blob([uInt8Array], {
type: 'application/vnd.ms-excel'
})

link.style.display = 'none';
link.href = URL.createObjectURL(blob);
link.setAttribute('download', fileName);

document.body.appendChild(link);
link.click();

document.body.removeChild(link);
}

 

后端代码（大数据Excel导出）

 

public class HEDSExportExcelController : ApiController
{
[HttpPost]
[LogFilterAttribute]
[Route("HEDSExportExcel")]
public ApiResult HEDSExportExcel([FromBody] HEDSExportDto input)
{
try
{
LoggerUtil.Info($"HEDSExportExcel:excel导出测试开始");
HEDSExcelService ope = new HEDSExcelService();

bool result = false;
string con = GetResult(ref result);
return new ApiResult { IsSucceed = result, ReturnValue = con };
}
catch (Exception e)
{
return new ApiResult { IsSucceed = false, ReturnValue = e.Message };
}
}

public string GetResult(ref bool res)
{
List<PriceDto> list = new List<PriceDto>();
string str = ToEx(list);
res = true;
return str;

}

/// <summary>
/// 大数据量多个sheet导出
/// </summary>
/// <typeparam name="T">数据源实体类</typeparam>
/// <param name="objList">数据源</param>
/// <param name="fileName">文件名称</param>
/// <param name="btyBytes">导出数据流</param>
/// <param name="columnInfo">显示列对应数据字典</param>
/// <param name="listCount">每个sheet包含数据条数</param>
/// <returns></returns>
public string ToEx(List<PriceDto> listM)
{
int listCount = 65000;//每页最大数量值
Dictionary<string, string> columnInfo = new Dictionary<string, string>();
columnInfo.Add("devNo", "设备编号");
columnInfo.Add("jobNo", "工号");
columnInfo.Add("grouprow", "行组");
columnInfo.Add("versionnum", "版本号");

HSSFWorkbook book = new HSSFWorkbook();
if (listM != null && listM.Count > 0)
{
double sheetCount = Math.Ceiling((double)listM.Count / listCount);
for (int i = 0; i < sheetCount; i++)
{
ISheet sheet = null;
sheet = book.CreateSheet("sheet" + i);
sheet.DefaultRowHeight = 20 * 10;
List<PriceDto> list = new List<PriceDto>();
list = listM.Skip<PriceDto>(listCount * i).Take<PriceDto>(listCount).ToList();

int rowIndex = 0;
int StartColIndex = 0;
int colIndex = StartColIndex;

//创建表头样式
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true;
IFont font = book.CreateFont();

 

IFont font1 = book.CreateFont();
font1.FontName = "微软雅黑";
ICellStyle cellStyledefalut = getDefaultStyle(book);// 默认样式
cellStyledefalut.SetFont(font1);
ICellStyle cellStyletitle = getDefaultStyle(book);// 标题样式
cellStyletitle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
cellStyletitle.FillPattern = FillPattern.SolidForeground;
// 字体加粗
IFont font2 = book.CreateFont();
font2.FontName = "微软雅黑";
font2.Boldweight = (short)FontBoldWeight.Bold;
cellStyletitle.SetFont(font2);
ICellStyle cellStylehigh = getDefaultStyle(book);// 风险高内容样式
cellStylehigh.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index;
cellStylehigh.FillPattern = FillPattern.SolidForeground;
cellStylehigh.SetFont(font2);

IRow frowO = sheet.CreateRow(0);//表头
frowO.Height = 30 * 20;// 默认高度

 

Type myType = null;
myType = listM[0].GetType();
//根据反射从传递进来的属性名信息得到要显示的属性
List<PropertyInfo> myPro = new List<PropertyInfo>();
PropertyInfo[] properties = myType.GetProperties();

#region 定义表头

ICellStyle style1 = book.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;

int m = 0;

sheet.SetColumnWidth(0, 256 * 20);//设置设备编号宽度
sheet.SetColumnWidth(1, 256 * 15);//设置工号版本宽度
sheet.SetColumnWidth(2, 256 * 30);//设置参数名称宽度
sheet.SetColumnWidth(3, 256 * 20);//设置参数id宽度

if (columnInfo != null)
{
var rowheader = sheet.CreateRow(0);
rowheader.Height = rowheader.Height = 30 * 20;
foreach (string cName in columnInfo.Keys)
{

PropertyInfo p = myType.GetProperty(cName);
if (p != null)
{
myPro.Add(p);
rowheader.CreateCell(m).SetCellValue(columnInfo[cName]);
rowheader.GetCell(m).CellStyle = cellStyletitle;//设置表格样式
m++;
}
}

//rowheader.GetCell(0).CellStyle = cellStyletitle;
}
#endregion
#region 定义表体并赋值
//如果没有找到可用的属性则结束
if (myPro.Count == 0) { return ""; }
foreach (var obj in list)
{
int n = 0;
if (sheet != null)
{
rowIndex++;
var sheetrow = sheet.CreateRow(rowIndex);
sheetrow.Height = sheetrow.Height = 20 * 20;
foreach (PropertyInfo p in myPro)
{
dynamic val = p.GetValue(obj, null) ?? "";
sheetrow.CreateCell(n).SetCellValue(val);
n++;
}
}

}
#endregion
}
}
else
{
//在工作薄中建立工作表
HSSFSheet sheet = book.CreateSheet() as HSSFSheet;
sheet.SetColumnWidth(0, 30 * 256);
if (sheet != null) sheet.CreateRow(0).CreateCell(0).SetCellValue("暂无数据！");
}

try
{
MemoryStream ms = new MemoryStream();
book.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
string str = Convert.ToBase64String(bytes);
return str;
}
catch (SystemException ex)
{
return ex.Message;
}
}

/// <summary>
/// 设置默认样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public ICellStyle getDefaultStyle(HSSFWorkbook workbook)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//是否换行
cellStyle.WrapText = true;
//缩小字体填充
//cellStyle.ShrinkToFit = true;
//// 设置单元格背景色
//cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
//cellStyle.FillPattern = FillPattern.SolidForeground;

//// 字体加粗
//IFont font = workbook.CreateFont();
//font.Boldweight = (short)FontBoldWeight.Bold;
//cellStyle.SetFont(font);
return cellStyle;
}
}