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;
        }