aspose导出Excel(webapi、多sheet、自定义日期样式)
[HttpGet] [Route("exportfoodapplydetail")] public HttpResponseMessage Export_Foodapplydetail(string id, string title) { var list = _fooddetailRepository.GetFoodSupplyDatail(id); string tableTitle = "序号, 食品名称,计量单位,单价,数量,小计"; try { Workbook wb = new Workbook(); wb.Worksheets.Clear(); #region 为标题设置样式 Style styleTitle = wb.CreateStyle();//新增样式 styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中 styleTitle.Font.Name = "宋体";//文字字体 styleTitle.Font.Size = 18;//文字大小 styleTitle.Font.IsBold = false;//粗体 //表头、数据行样式 Style style2 = wb.CreateStyle();//新增样式 style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中 style2.Font.Name = "宋体";//文字字体 style2.Font.Size = 12;//文字大小 style2.Font.IsBold = false;//粗体 style2.IsTextWrapped = true;//单元格内容自动换行 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; #endregion string[] Tile = tableTitle.Split(','); int Colnum = Tile.Count();//表格列数 List<string> sheetName = new List<string>(); if (list.Where(a => a.type_name != "水果").Count() > 0) { sheetName.Add("食品"); } if (list.Where(a => a.type_name == "水果").Count() > 0) { sheetName.Add("水果"); } //生成sheet的名字 for (int i = 0; i < sheetName.Count; i++) { wb.Worksheets.Add(sheetName[i]); } for (int j = 0; j < sheetName.Count; j++) { style2.Custom = ""; Worksheet sheet = wb.Worksheets[j]; Cells cells = sheet.Cells;//单元格 List<FoodApplyDetailModel> food_list = null; if (sheetName[j] == "食品") { food_list = list.Where(a => a.type_name != "水果").ToList(); } else { food_list = list.Where(a => a.type_name == "水果").ToList(); } int Rownum = food_list.Count();//表格行数 #region 生成表头 标题列 //生成标题 cells.Merge(0, 0, 1, Colnum);//合并单元格 cells[0, 0].PutValue(title + "备用食品（夜餐）选购清单");//填写内容 cells[0, 0].SetStyle(styleTitle); cells.SetRowHeight(0, 45); //生成行2 列名行 for (int i = 0; i < Colnum; i++) { cells[1, i].PutValue(Tile[i]); cells.SetColumnWidth(i, 10); cells[1, i].SetStyle(style2); cells.SetRowHeight(1, 25); } #endregion decimal? money = 0; int? total = 0; for (int i = 0; i < Rownum; i++) { food_list[i].price = (food_list[i].price != null) ? food_list[i].price : 0; cells[2 + i, 0].PutValue(i + 1); cells[2 + i, 1].PutValue(food_list[i].name); cells[2 + i, 2].PutValue(food_list[i].unit); cells[2 + i, 3].PutValue(food_list[i].price); cells[2 + i, 4].PutValue(food_list[i].quantity); cells[2 + i, 5].PutValue(food_list[i].quantity * food_list[i].price); cells[2 + i, 0].SetStyle(style2); cells[2 + i, 1].SetStyle(style2); cells[2 + i, 2].SetStyle(style2); cells[2 + i, 3].SetStyle(style2); cells[2 + i, 4].SetStyle(style2); cells[2 + i, 5].SetStyle(style2); cells.SetRowHeight(2 + i, 25); cells.SetColumnWidth(1, 30); money += food_list[i].price * food_list[i].quantity; total += food_list[i].quantity; } cells.Merge(2 + Rownum, 0, 1, Colnum - 1); cells[2 + Rownum, 0].PutValue("合计"); cells[2 + Rownum, 5].PutValue(money); //cells[2 + Rownum, 4].PutValue(total); cells[2 + Rownum, 0].SetStyle(style2); cells[2 + Rownum, 1].SetStyle(style2); cells[2 + Rownum, 2].SetStyle(style2); cells[2 + Rownum, 3].SetStyle(style2); cells[2 + Rownum, 4].SetStyle(style2); cells[2 + Rownum, 5].SetStyle(style2); cells.SetRowHeight(2 + Rownum, 25); cells[3 + Rownum, 0].PutValue("签名"); cells.Merge(3 + Rownum, 1, 1, 2); cells[3 + Rownum, 3].PutValue("申请日期"); cells[3 + Rownum, 4].PutValue(food_list[0].create_date); cells.Merge(3 + Rownum, 4, 1, 2); cells[3 + Rownum, 0].SetStyle(style2); cells[3 + Rownum, 1].SetStyle(style2); cells[3 + Rownum, 2].SetStyle(style2); cells[3 + Rownum, 3].SetStyle(style2); style2.Custom = "yyyy-MM-dd"; cells[3 + Rownum, 4].SetStyle(style2); cells[3 + Rownum, 5].SetStyle(style2); cells.SetRowHeight(3 + Rownum, 25); } string sPath = AppDomain.CurrentDomain.RelativeSearchPath + "\\" + "UploadFile"; sPath = sPath.Replace("\\bin\\", "\\"); var fileName = "食品申请单_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; fileName = HttpUtility.UrlEncode(fileName); string filePath = sPath + "\\" + fileName; wb.Save(filePath); var stream = FileToStream(filePath); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); result.Content = new StreamContent(stream); result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); result.Content.Headers.ContentDisposition.FileName = fileName; //return ResponseMessage(result); return result; } catch (Exception e) { return new HttpResponseMessage(HttpStatusCode.NoContent); //return Json(new { success = false, message = "导出有误，请联系管理员！" }); } }
//不保存文件直接导出 。。。。(表格制作略) var stream = new MemoryStream(); wb.Save(stream, Aspose.Cells.SaveFormat.Xlsx); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); result.Content = new StreamContent(stream); result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); result.Content.Headers.ContentDisposition.FileName = fileName; return result;
//注：aspose未破解版导出时会多生成一个默认sheet 所以要使用破解版
