C#导出Excel

————————————————————————————————————————————————————

一个简单的mvc导出Excel..........................

准备工作：引用dll

HTNL

<a class="btn btn-primary dropdown-text" onclick="btn_Export()"><i class="fa fa-plus"></i>导出提单</a>
           

JS

//导出
    function btn_Export() {
        $('#export').attr('action', "@Url.Action("方法（BillFile）", "控制器", "所在文件夹")").submit();
    }
           

后台

/// <summary>
        ///  导出
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult BillFile(string OrgIds, string start_Time, string end_Time)//前台传过来的参数/可有可无
        {
            System.IO.MemoryStream ms = new System.IO.MemoryStream();//数据流
            // 获取导出的数据
            var data = _jointSqlService.GetBillList(OrgIds, start_Time, end_Time);
            if (data.Count != 0)
            {
                var book = WriteBillExcel(data);
                book.Write(ms);
                ms.Seek(0, SeekOrigin.Begin);
                return File(ms, "application/vnd.ms-excel", string.Format("导出Excel的名称-{0}/条.xls",data.Count));
            }
            return File(ms, "application/vnd.ms-excel", string.Format("未找到数据-{0}/条.xls", data.Count));
        }

        /// <summary>
        ///  向Excel中写信息
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        private HSSFWorkbook WriteBillExcel(List<BillMonthlyStatisticsModel> list)
        {
            var filePath = Server.MapPath(@"~\Documents\XXXX.xls");//物理路径,前提所在文件夹里必须有该文件所在
            FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);//创建工作簿
            HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;//创建页码

            if (null != sheet)
            {
                //这里是创建新的行，如果createRow 为null时重启一下
                var createRow = sheet.CreateRow(0);//创建行
                var createStyle = hssfworkbook.CreateCellStyle();//创建样式
                var createFont = hssfworkbook.CreateFont();//创建字体
                createFont.FontName = "宋体";
                createFont.Boldweight = (short)FontBoldWeight.Bold;//粗体
                createFont.FontHeightInPoints = 12;//字体大小
                createRow.Height = 50 * 10;//行高
                sheet.SetColumnWidth(0, 30 * 120);//行宽
                createRow.CreateCell(0).SetCellValue("XXXX");
                sheet.SetColumnWidth(1, 30 * 250);
                createRow.CreateCell(1).SetCellValue("XXXX");
                sheet.SetColumnWidth(2, 30 * 190);
                createRow.CreateCell(2).SetCellValue("XXXX");
                sheet.SetColumnWidth(3, 30 * 190);
                createRow.CreateCell(3).SetCellValue("XXXX");

                //字体居中
                createStyle.Alignment = HorizontalAlignment.Center;
                createStyle.SetFont(createFont);
                createRow.GetCell(0).CellStyle = createStyle;
                createRow.GetCell(1).CellStyle = createStyle;
                createRow.GetCell(2).CellStyle = createStyle;
                createRow.GetCell(3).CellStyle = createStyle;
                for (int i = 0; i < list.Count; i++)
                {
                    var row = sheet.CreateRow(i+1);
                    row.CreateCell(0).SetCellValue(string.IsNullOrEmpty(list[i].TRANSPORTCONTRACTDOCUMENT_ID) ? " " : list[i].TRANSPORTCONTRACTDOCUMENT_ID);
                    row.CreateCell(1).SetCellValue(string.IsNullOrEmpty(list[i].ASSOCIATEDTRANSPORTDOCUMENT_ID) ? " " : list[i].ASSOCIATEDTRANSPORTDOCUMENT_ID);
                    row.CreateCell(2).SetCellValue(string.IsNullOrEmpty((list[i].DECLARE_DATE).ToString())?"":Convert.ToDateTime(list[i].DECLARE_DATE).ToString("yyyy-MM-dd HH:mm:ss"));
                    row.CreateCell(3).SetCellValue(string.IsNullOrEmpty((list[i].REC_SENTTIME).ToString()) ? "" : Convert.ToDateTime(list[i].REC_SENTTIME).ToString("yyyy-MM-dd HH:mm:ss"));
                }
            }
            sheet.ForceFormulaRecalculation = true;
            return hssfworkbook;
        }