Web Api 将DataTable装换成Excel，并通过文件流将其下载
不废话，直接上代码
前端代码
<input type="button" class="layui-btn" value="Test-GetFileFromWebApi" onclick="GetFileFromWebApi(this)" /> <script> function GetFileFromWebApi() { location.href = '/api/WorkOrderAPI/GetFileFromWebApi'; } </script>
接口代码
/// <summary> /// 从WebAPI下载文件 /// </summary> /// <returns></returns> [HttpGet] [AllowAnonymous] public IHttpActionResult GetFileFromWebApi() { string filePath = string.Empty; var browser = String.Empty; if (System.Web.HttpContext.Current.Request.UserAgent != null) { browser = System.Web.HttpContext.Current.Request.UserAgent.ToUpper(); }
string excelFile = string.Empty, DownloadExportPath = "DownloadExport\\"; DataTable dt = new DataTable();//这里根据实际逻辑赋值 KP.Commom.OperationExcel _excel = new KP.Commom.OperationExcel(); string s = _excel.DownloadExport(dt, System.Web.HttpContext.Current.Request.PhysicalApplicationPath + DownloadExportPath + "系统客户.xls", "系统客户", out excelFile); filePath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, DownloadExportPath, excelFile);
HttpResponseMessage httpResponseMessage = new HttpResponseMessage(HttpStatusCode.OK); System.IO.FileStream fileStream = System.IO.File.OpenRead(filePath); httpResponseMessage.Content = new StreamContent(fileStream); httpResponseMessage.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream"); httpResponseMessage.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment") { FileName = browser.Contains("FIREFOX") ? System.IO.Path.GetFileName(filePath) : System.Web.HttpUtility.UrlEncode(System.IO.Path.GetFileName(filePath)) }; return ResponseMessage(httpResponseMessage); }
公用方法
/// <summary> /// DataTable转换Excel并下载到本地“下载”文件夹里 /// </summary> /// <param name="data">DataTable</param> /// <param name="sheetName">工作簿名称</param> /// <returns></returns> public string DownloadExport(DataTable data, string filePath, string sheetName, out string excelFile) { string rMsg = string.Empty; string _excelFile = string.Empty; try { _excelFile = Path.GetFileName(filePath); //文件名 string strExtenName = string.Empty; //检测是否存在文件夹，若不存在就建立个文件夹 string directoryName = Path.GetDirectoryName(filePath); if (!Directory.Exists(directoryName)) { Directory.CreateDirectory(directoryName); } //判断文件是否存在 if (File.Exists(filePath)) { string strFilePath = Path.GetDirectoryName(filePath); strExtenName = Path.GetExtension(filePath); string fullFileName = Path.GetFileName(filePath); string strSubName = fullFileName.Replace(strExtenName, ""); //获取当前目录与当前文件同类的所有文件集 string[] hasFileList = Directory.GetFiles(strFilePath, strSubName + "*" + strExtenName, SearchOption.AllDirectories); if (hasFileList.LongLength > 0) { int fileSort = 1; string strFileName = string.Empty; foreach (string item in hasFileList) { string loopFullFileName = Path.GetFileName(item); strFileName = loopFullFileName.Replace(strExtenName, ""); int strLen = strFileName.Length; string strleft = strFileName.Substring(strLen - 3, 1); string strright = strFileName.Substring(strLen - 1, 1); int leftIndex = strFileName.IndexOf(strleft); int rightIndex = strFileName.IndexOf(strright); //是否包含“()” if (strleft.IndexOf("(") == 0 && strright.IndexOf(")") == 0) { //是否为文件序号 if (rightIndex - leftIndex == 2) { int sratrSort = 0; //取出序号值 string strSort = strFileName.Substring(leftIndex + 1, 1); int.TryParse(strSort, out sratrSort); //起始序号从1开始 if (sratrSort > 1) { if (sratrSort >= fileSort) { fileSort = sratrSort + 1; } } else { fileSort = sratrSort + 1; } } } } _excelFile = strSubName + "(" + fileSort + ")" + strExtenName; filePath = strFilePath + "\\" + _excelFile; } } //创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); int i = 0, j = 0, count = 0; #region 设置Excel表格标题 IRow titleInfo = sheet.CreateRow(count); ICell cellTitle = titleInfo.CreateCell(0); cellTitle.SetCellValue(sheetName); ICellStyle titleStyle = workbook.CreateCellStyle(); titleStyle.Alignment = HorizontalAlignment.Center;//水平对齐 IFont titleFont = workbook.CreateFont(); titleFont.FontHeightInPoints = 15; titleFont.Boldweight = short.MaxValue;//字体加粗 titleStyle.SetFont(titleFont); cellTitle.CellStyle = titleStyle; #endregion count = count + 1; #region 表头 IRow headRow = sheet.CreateRow(count); ICell cellHead = null; ICellStyle styleHead = workbook.CreateCellStyle();//创建样式对象 styleHead.Alignment = HorizontalAlignment.Center;//水平对齐 styleHead.VerticalAlignment = VerticalAlignment.Center;//垂直对齐 IFont font = workbook.CreateFont(); //创建一个字体样式对象 font.FontName = "宋体"; //和excel里面的字体对应 font.Color = new NPOI.HSSF.Util.HSSFColor.Red().Indexed;//颜色参考NPOI的颜色对照表(替换掉PINK()) font.FontHeightInPoints = 10;//字体大小 font.Boldweight = short.MaxValue;//字体加粗 styleHead.SetFont(font); //将字体样式赋给样式对象 sheet.SetColumnWidth(0, 20 * 256);//设置列宽 for (j = 0; j < data.Columns.Count; ++j) { cellHead = headRow.CreateCell(j); cellHead.CellStyle = styleHead; cellHead.SetCellValue(data.Columns[j].ColumnName); sheet.SetColumnWidth(i, 17 * 256); } #endregion count = count + 1; #region 填充Excel内容 for (i = 0; i < data.Rows.Count; ++i) { IRow rowBody = sheet.CreateRow(count); ICell CellBody = null; ICellStyle bodyStyle = workbook.CreateCellStyle();//创建样式对象 bodyStyle.Alignment = HorizontalAlignment.Center;//水平对齐 bodyStyle.VerticalAlignment = VerticalAlignment.Center;//垂直对齐 IFont fontBody = workbook.CreateFont(); //创建一个字体样式对象 fontBody.FontName = "宋体"; //和excel里面的字体对应 fontBody.Color = new NPOI.HSSF.Util.HSSFColor.Black().Indexed;//颜色参考NPOI的颜色对照表(替换掉PINK()) fontBody.FontHeightInPoints = 10;//字体大小 fontBody.Boldweight = short.MinValue;//字体加粗 bodyStyle.SetFont(fontBody); //将字体样式赋给样式对象 for (j = 0; j < data.Columns.Count; ++j) { CellBody = rowBody.CreateCell(j); CellBody.CellStyle = bodyStyle; CellBody.SetCellValue(data.Rows[i][j].ToString()); sheet.SetColumnWidth(i, 17 * 256); } ++count; } #endregion //合并单元格 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, data.Columns.Count)); //生成文件 FileStream file = new FileStream(filePath, FileMode.Create); workbook.Write(file); file.Close(); System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + (!string.IsNullOrEmpty(_excelFile) ? _excelFile : "DownloadData." + strExtenName)); System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel"; System.Web.HttpContext.Current.Response.WriteFile(filePath); System.Web.HttpContext.Current.Response.Flush(); System.Web.HttpContext.Current.Response.End(); } catch (Exception ex) { rMsg = "异常：" + ex.Message + " Detail:" + (ex.InnerException != null ? ex.InnerException.ToString() : ""); } excelFile = _excelFile; return rMsg; }
