62,046
社区成员
发帖
与我相关
我的任务
分享
try
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
using (XXEntities db = new XXEntities())
{
XXBLL cobll = new XXBLL();
List<X> list = cobll.X.OrderByDescending(c => c.CreationDate).Take(100).ToList();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("订单编号");
row1.CreateCell(1).SetCellValue("订单用户");
row1.CreateCell(2).SetCellValue("购买用户");
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(3).SetCellValue(list[i].CarName);
rowtemp.CreateCell(10).SetCellValue(list[i].CreationDate.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "COExport_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
}
}
catch (Exception)
{
throw;
}
这是我很久以前的代码了
前端你只需要一个a标签就可以用了
<a class="layui-btn layui-btn-normal btn_add" href="/X/ExportExcel">订单导出</a>
<a class="layui-btn layui-btn-normal" style="cursor:pointer;position:relative;width:80px;background-color:#5FB878;" id="btn_excel" name="btn_excel"><img style="margin-top:-6px;" src="../../images/load.png" />保险报表</a>
<script>
layui.config({
base: '../../layuiadmin/' //静态资源所在路径
, version: true
}).extend({
index: 'lib/index' //主入口模块
}).use(['index', 'sample']);
layui.use(['jquery', 'element', 'table', 'form', 'laydate'], function () {
var table = layui.table, form = layui.form, laydate = layui.laydate, $ = layui.jquery;
$("#btn_excel").on("click", function () {
$("#btn_excel").attr("lay-text", "导出报表");
$("#btn_excel").attr("lay-href", "/DH/ExportBX");
});</script>
/// <summary>
/// 报表
/// </summary>
/// <returns></returns>
public ActionResult ExportBX()
{
string saveUrl;
bll.ExportBX(out saveUrl);
return Redirect(saveUrl);
}
/// <summary>
/// 导出报表
/// </summary>
/// <param name="status"></param>
/// <param name="saveUrl"></param>
/// <param name="rList"></param>
/// <returns></returns>
public bool ExportBX(out string saveUrl)
{
saveUrl = "";
string contentPath = HttpContext.Current.Server.MapPath("~/Content/");
string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlBooks = xlApp.Workbooks;
object missing = System.Reflection.Missing.Value;
List<BX> entity = this.Get_bx();
try
{
Excel.Workbook xlBook = xlBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//让后台执行设置为不可见,为true的话会看到打开一个Excel
xlApp.Visible = false;
object[,] arData = new object[entity.Count + 1, 20]; //行,列
arData[0, 0] = "业务部";
arData[0, 1] = "分公司";
arData[0, 2] = "业务员";
arData[0, 3] = "立项编号";
arData[0, 4] = "车架号";
arData[0, 5] = "客户";
arData[0, 6] = "联系方式";
arData[0, 7] = "首次上牌日";
arData[0, 8] = "行驶证户主";
arData[0, 9] = "使用性质";
arData[0, 10] = "荷载人数";
arData[0, 11] = "车牌";
arData[0, 12] = "发动机号";
arData[0, 13] = "交强险到期日";
arData[0, 14] = "交强险录入人员";
arData[0, 15] = "交强险录入时间";
arData[0, 16] = "商业险到期日";
arData[0, 17] = "商业险录入人员";
arData[0, 18] = "商业险录入时间";
arData[0, 19] = "案件状态";
for (var i = 0; i < entity.Count; i++)
{
var n = entity[i];
arData[i + 1, 0] = n.业务部;
arData[i + 1, 1] = n.分公司;
arData[i + 1, 2] = n.业务员;
arData[i + 1, 3] = n.立项编号;
arData[i + 1, 4] = n.车架号;
arData[i + 1, 5] = n.客户;
arData[i + 1, 6] = n.联系方式;
arData[i + 1, 7] = n.首次上牌日;
arData[i + 1, 8] = n.行驶证户主;
arData[i + 1, 9] = n.使用性质;
arData[i + 1, 10] = n.荷载人数;
arData[i + 1, 11] = n.车牌;
arData[i + 1, 12] = n.发动机号;
arData[i + 1, 13] = n.交强险到期日;
arData[i + 1, 14] = n.交强险录入人员;
arData[i + 1, 15] = n.交强险录入时间;
arData[i + 1, 16] = n.商业险到期日;
arData[i + 1, 17] = n.商业险录入人员;
arData[i + 1, 18] = n.商业险录入时间;
arData[i + 1, 19] = n.案件状态;
}
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
xlSheet.Name = "保-" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
int colCount = 20;
int rowCount = entity.Count;
string startCol = "A";
int iCnt = (colCount / 32);
string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
string endCol = endColSignal + ((char)('A' + colCount - iCnt * 32 - 1)).ToString();
Excel.Range range = xlSheet.get_Range(startCol + "1", endCol + "1"); //取得表头
range.Font.Bold = 1;//Excel文件列名 字体设定为Bold
range.Font.Size = 14;
range.Font.ColorIndex = 2; //白色
range.Interior.ColorIndex = 1; //黑底
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //置中
range = xlSheet.Columns[3];
range.NumberFormatLocal = "@";
range = xlSheet.Columns[4];
range.NumberFormatLocal = "@";
range = xlSheet.Columns[5];
range.NumberFormatLocal = "@";
range = xlSheet.Columns[6];
range.NumberFormatLocal = "@";
range = xlSheet.Columns[11];
range.NumberFormatLocal = "@";
range = xlSheet.Columns[12];
range.NumberFormatLocal = "@";
range = xlSheet.Columns[13];
range.NumberFormatLocal = "@";
Excel.Range totalrange = xlSheet.get_Range(startCol + "1", endCol + (rowCount - iCnt * 32 + 1).ToString());
totalrange.Value = arData; //给Exccel中的Range整体赋值
//totalrange.Columns.AutoFit(); //设定Excel列宽度自适应
totalrange.Font.Name = "微软雅黑";
totalrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //框线
range = xlSheet.Columns;
range.Columns.AutoFit();
xlApp.DisplayAlerts = false;
xlApp.AlertBeforeOverwriting = false;
xlApp.Workbooks[1].RefreshAll();
string savePath = contentPath + "Report";
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
xlBook.SaveAs(contentPath + "Report/" + fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
xlBooks.Close();
xlBooks = null;
xlApp.Quit();
xlApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
saveUrl = HttpContext.Current.Request.ApplicationPath + "Content/Report/" + fileName;
return true;
}