先提供数据源，使用aspose创建excel的单元格和各种样式，插入图片，冻结单元格，行高等
1 public string ExportDemo() 2 { 3 ViewResultModel result = ViewResultModel.Create(true, ""); 4 //获取数据源,为了能通用，转为DataTable，其他类型的数据源也是一样的 5 DataTable humanList = Power.Systems.StdSystem.HumanDO.FindAllByTable(); 6 Dictionary<string, string> header = new Dictionary<string, string>(); 7 header.Add("编号", "Code"); 8 header.Add("姓名", "Name"); 9 header.Add("部门", "DeptName"); 10 header.Add("头像", "HeadSmall"); 11 OutPutFile(header, humanList, "公司人员报表"); 12 return result.ToJson(); 13 } 14 /// <summary> 15 /// 输出文件名 16 /// </summary> 17 /// <param name="header">编号:Code,姓名:Name....</param> 18 /// <param name="dt">数据源,Code,Name.....</param> 19 /// <param name="filename">文件名</param> 20 private void OutPutFile(Dictionary<string, string> header, DataTable dt, string filename) 21 { 22 Workbook workbook = new Workbook(); //工作簿 23 Worksheet sheet = workbook.Worksheets[0]; //工作表 24 Cells cells = sheet.Cells;//单元格 25 /* 26 * 预计格式为 27 * 人员信息 28 * ------------------------------------------ 29 * |编号| |姓名| |部门| |头像| 30 * ------------------------------------------ 31 * 0001 张三 信息部 picture 32 * …… 33 */ 34 //为标题设置样式 35 Style styleTitle = workbook.Styles[workbook.Styles.Add()];//新增样式 36 styleTitle.Font.Color = System.Drawing.Color.FromArgb(255, 99, 71);//字体颜色RBG颜色 37 styleTitle.ForegroundColor = System.Drawing.Color.FromArgb(250, 240, 230);//背景颜色RBG颜色 38 styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中 39 styleTitle.Font.Name = "宋体";//文字字体 40 styleTitle.Font.Size = 18;//文字大小 41 styleTitle.Font.IsBold = true;//粗体 42 43 44 //列头样式 45 Style styleHeader = workbook.Styles[workbook.Styles.Add()];//新增样式 46 styleHeader.HorizontalAlignment = TextAlignmentType.Center;//文字居中 47 styleHeader.Font.Name = "宋体";//文字字体 48 styleHeader.Font.Size = 14;//文字大小 49 styleHeader.Font.IsBold = true;//粗体 50 styleHeader.IsTextWrapped = true;//单元格内容自动换行 51 //上下左右增加细边框线 52 styleHeader.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; 53 styleHeader.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; 54 styleHeader.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; 55 styleHeader.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; 56 57 //普通单元格样式 58 Style styleContent = workbook.Styles[workbook.Styles.Add()];//新增样式 59 styleContent.HorizontalAlignment = TextAlignmentType.Left;//文字靠左 60 styleContent.Font.Name = "宋体";//文字字体 61 styleContent.Font.Size = 12;//文字大小 62 //styleContent.IsTextWrapped = true;//单元格内容自动换行 63 styleContent.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.DashDot; 64 styleContent.Borders[BorderType.RightBorder].LineStyle = CellBorderType.DashDot; 65 styleContent.Borders[BorderType.TopBorder].LineStyle = CellBorderType.DashDot; 66 styleContent.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.DashDot; 67 68 //生成 标题行 69 cells.Merge(0, 0, 1, header.Keys.Count);//合并单元格 ,标题行有几列就合并几列 70 cells[0, 0].PutValue(filename);//填写内容 71 cells[0, 0].SetStyle(styleTitle);//使用标题样式 72 cells.SetRowHeight(0, 50);//第0行，50px高 73 74 //生成 列头行 75 int headerNum = 0;//当前表头所在列 76 foreach (string item in header.Keys) 77 { 78 cells[1, headerNum].PutValue(item); 79 cells[1, headerNum].SetStyle(styleHeader); 80 cells.SetColumnWidthPixel(headerNum, 200);//设置单元格200宽度 81 cells.SetRowHeight(1, 30);//第一行，30px高 82 headerNum++; 83 } 84 85 //生成数据行 86 for (int i = 0; i < dt.Rows.Count; i++) 87 { 88 cells.SetRowHeight(2 + i, 24); 89 int contentNum = 0;//当前内容所在列 90 foreach (string item in header.Keys) 91 { 92 string value = dt.Rows[i][header[item]] == null ? "" : dt.Rows[i][header[item]].ToString(); 93 if (item == "头像") 94 { 95 cells[2 + i, contentNum].SetStyle(styleContent);//单元格样式 96 //获取头像的文件流 97 if (dt.Rows[i][header[item]] != null && 98 dt.Rows[i][header[item]].ToString() != "00000000-0000-0000-0000-000000000000") 99 { 100 //通过Id找到附件表，此处是获取图片的 文件流，各自不同的方法得到最终的文件流即可 101 Power.Systems.Systems.DocFileDO docfile = Power.Systems.Systems.DocFileDO.FindByKey(value); 102 if (docfile != null) 103 { 104 string ServerUrl = docfile.ServerUrl; 105 string Ip = Power.Global.PowerGlobal.FTPIp; 106 string Port = Power.Global.PowerGlobal.FTPPort; 107 string UserId = Power.Global.PowerGlobal.FTPUserId; 108 string UserPwd = Power.Global.PowerGlobal.FTPUserPwd;//UserPwd 109 string filePath = "ftp://" + Ip + ":" + Port + ServerUrl; 110 byte[] fileData = Power.Global.FtpHelper.FtpfileDownLoad(filePath, UserId, UserPwd).GetBuffer(); 111 if (fileData.Length > 0) 112 { 113 System.IO.MemoryStream ms = new System.IO.MemoryStream(fileData); 114 System.Drawing.Image image = System.Drawing.Image.FromStream(ms); 115 Bitmap img = PercentImage(image);//压缩图片 116 117 int height = img.Height; 118 System.IO.MemoryStream mss = new System.IO.MemoryStream( ); 119 img.Save(mss,System.Drawing.Imaging.ImageFormat.Png); 120 sheet.Pictures.Add(2 + i, contentNum, mss, 100, 100);//加入图片,后面是宽度和高度的缩放比例 121 122 cells.SetRowHeight(2 + i, height);//设置行高 123 } 124 125 } 126 } 127 128 } 129 else 130 { 131 cells[2 + i, contentNum].PutValue(value); 132 cells[2 + i, contentNum].SetStyle(styleContent); 133 } 134 contentNum++; 135 } 136 } 137 138 sheet.FreezePanes(2, 0, 2, header.Keys.Count);//冻结标题行 139 140 System.Web.HttpResponse Response = System.Web.HttpContext.Current.Response; 141 workbook.Save(Response, HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".xlsx", Aspose.Cells.ContentDisposition.Attachment, new Aspose.Cells.XlsSaveOptions(Aspose.Cells.SaveFormat.Xlsx)); 142 //直接显示PDF 143 //Aspose.Cells.PdfSaveOptions pdfopt = new Aspose.Cells.PdfSaveOptions(Aspose.Cells.SaveFormat.Pdf); 144 //pdfopt.AllColumnsInOnePagePerSheet = true; 145 146 //workbook.Save(Response, HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".pdf", Aspose.Cells.ContentDisposition.Inline, pdfopt); 147 } 148 private Bitmap PercentImage(Image srcImage) 149 { 150 151 int newW = srcImage.Width < 150 ? srcImage.Width : 150; 152 153 int newH = int.Parse(Math.Round(srcImage.Height * (double)newW / srcImage.Width).ToString()); 154 155 try 156 { 157 158 Bitmap b = new Bitmap(newW, newH); 159 160 Graphics g = Graphics.FromImage(b); 161 162 g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.Default; 163 164 g.DrawImage(srcImage, new Rectangle(0, 0, newW, newH), new Rectangle(0, 0, srcImage.Width, srcImage.Height), GraphicsUnit.Pixel); 165 166 g.Dispose(); 167 168 return b; 169 170 } 171 172 catch (Exception) 173 { 174 175 return null; 176 177 } 178 179 }
