在上篇《万能报表之数据篇》中我们己经展示了数据是如何写入Excel生成报表的，在本篇中，我们再来看一下，如何在报表中指定位置，插入图片。
效果如下图所示：
插入图片的方法写到了一个类中，代码如下：
View Code
1 public class InsertImage : IDisposable
2 {
3 SpreadsheetDocument spreadSheet;
4 public WorksheetPart CurrentWorksheetPart { get; set; }
5 SharedStringTablePart shareStringPart;
6 public InsertImage(Stream file, string SheetName1)
7 {
8 #region
9 spreadSheet = SpreadsheetDocument.Open(file, true);
10 WorksheetPart worksheetPart = DocumentFormat.OpenXml.Extensions.SpreadsheetReader.GetWorksheetPartByName(spreadSheet, SheetName1);
11 if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
12 {
13 shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
14 }
15 else
16 {
17 shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
18 shareStringPart.SharedStringTable = new SharedStringTable();
19 }
20 CurrentWorksheetPart = worksheetPart;
21 #endregion
22 }
23 public void Dispose()
24 {
25 spreadSheet.Close();
26 spreadSheet.Dispose();
27 }
28
29 public void InsertExcelImage(long x, long y, long? width, long? height, string sImagePath)
30 {
31
32
33 try
34 {
35 WorksheetPart wsp = CurrentWorksheetPart;
36 DrawingsPart dp;
37 ImagePart imgp;
38 WorksheetDrawing wsd;
39
40 ImagePartType ipt;
41 switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower())
42 {
43 case "png":
44 ipt = ImagePartType.Png;
45 break;
46 case "jpg":
47 case "jpeg":
48 ipt = ImagePartType.Jpeg;
49 break;
50 case "gif":
51 ipt = ImagePartType.Gif;
52 break;
53 default:
54 return;
55 }
56
57 if (wsp.DrawingsPart == null)
58 {
59 //----- no drawing part exists, add a new one
60
61 dp = wsp.AddNewPart<DrawingsPart>();
62 imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp));
63 wsd = new WorksheetDrawing();
64 }
65 else
66 {
67 //----- use existing drawing part
68
69 dp = wsp.DrawingsPart;
70 imgp = dp.AddImagePart(ipt);
71 dp.CreateRelationshipToPart(imgp);
72 wsd = dp.WorksheetDrawing;
73 }
74 using (FileStream ImgStream = new FileStream(sImagePath, FileMode.Open))
75 {
76 imgp.FeedData(ImgStream);
77 }
78 int imageNumber = dp.ImageParts.Count();
79 if (imageNumber == 1)
80 {
81 Drawing drawing = new Drawing();
82 drawing.Id = dp.GetIdOfPart(imgp);
83 CurrentWorksheetPart.Worksheet.Append(drawing);
84 }
85
86 NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
87 nvdp.Id = new UInt32Value((uint)(1024 + imageNumber));
88 nvdp.Name = "Picture " + imageNumber.ToString();
89 nvdp.Description = "";
90 DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
91 picLocks.NoChangeAspect = true;
92 picLocks.NoChangeArrowheads = true;
93 NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
94 nvpdp.PictureLocks = picLocks;
95 NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
96 nvpp.NonVisualDrawingProperties = nvdp;
97 nvpp.NonVisualPictureDrawingProperties = nvpdp;
98
99 DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
100 stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
101
102 BlipFill blipFill = new BlipFill();
103 DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
104 blip.Embed = dp.GetIdOfPart(imgp);
105 blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
106 blipFill.Blip = blip;
107 blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
108 blipFill.Append(stretch);
109
110 DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
111 DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
112 offset.X = 0;
113 offset.Y = 0;
114 t2d.Offset = offset;
115 Bitmap bm = new Bitmap(sImagePath);
116
117 DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
118
119 if (width == null)
120 extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
121 else
122 extents.Cx = width * (long)((float)914400 / bm.HorizontalResolution);
123
124 if (height == null)
125 extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
126 else
127 extents.Cy = height * (long)((float)914400 / bm.VerticalResolution);
128
129 bm.Dispose();
130 t2d.Extents = extents;
131 ShapeProperties sp = new ShapeProperties();
132 sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
133 sp.Transform2D = t2d;
134 DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
135 prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
136 prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
137 sp.Append(prstGeom);
138 sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
139
140 DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
141 picture.NonVisualPictureProperties = nvpp;
142 picture.BlipFill = blipFill;
143 picture.ShapeProperties = sp;
144
145 Position pos = new Position();
146 pos.X = x * 914400 / 72;
147 pos.Y = y * 914400 / 72;
148 Extent ext = new Extent();
149 ext.Cx = extents.Cx;
150 ext.Cy = extents.Cy;
151 AbsoluteAnchor anchor = new AbsoluteAnchor();
152 anchor.Position = pos;
153 anchor.Extent = ext;
154 anchor.Append(picture);
155 anchor.Append(new ClientData());
156 wsd.Append(anchor);
157 wsd.Save(dp);
158
159 }
160 catch (Exception ex)
161 {
162 throw ex; // or do something more interesting if you want
163 }
164 }
165
166 public string CreateImageSteam(string ImagePath)
167 {
168 String url = ImagePath;
169 String fileName = url.Substring(url.LastIndexOf("/") + 1);
170 String refer = url.Substring(0, url.LastIndexOf("/") + 1);
171 System.Net.HttpWebRequest req = System.Net.HttpWebRequest.Create(url) as System.Net.HttpWebRequest;
172 req.AllowAutoRedirect = true;
173 req.Referer = refer;
174 //添加认证
175 //req.Credentials = new NetworkCredential("UserName","PassWord");
176 System.Net.HttpWebResponse res = req.GetResponse() as System.Net.HttpWebResponse;
177 System.IO.Stream stream = res.GetResponseStream();
178 System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
179 byte[] buffer = new byte[1024];
180 int bytes;
181 while ((bytes = stream.Read(buffer, 0, buffer.Length)) > 0)
182 {
183 memoryStream.Write(buffer, 0, bytes);
184 }
185
186 Bitmap bmt = new Bitmap(memoryStream);
187 string ImageType = "jpg";
188 System.Drawing.Imaging.ImageFormat imgfat = new System.Drawing.Imaging.ImageFormat(bmt.RawFormat.Guid);
189
190 if(bmt.RawFormat.Guid ==System.Drawing.Imaging.ImageFormat.Jpeg.Guid)
191 {
192 ImageType = "jpg";
193 }
194 else if (bmt.RawFormat.Guid == System.Drawing.Imaging.ImageFormat.Png.Guid)
195 {
196 ImageType = "png";
197 }
198 else if (bmt.RawFormat.Guid == System.Drawing.Imaging.ImageFormat.Bmp.Guid)
199 {
200 ImageType = "bmp";
201 }
202 else if (bmt.RawFormat.Guid == System.Drawing.Imaging.ImageFormat.Gif.Guid)
203 {
204 ImageType = "gif";
205 }
206 bmt.Dispose();
207 string Iamgeurl = HttpContext.Current.Server.MapPath(string.Format("..\\ExcelModel\\{0}.{1}", Guid.NewGuid(),ImageType));
208 FileStream s = new FileStream(Iamgeurl, FileMode.Create);
209 memoryStream.WriteTo(s);
210 res.Close();
211 memoryStream.Close();
212 s.Close();
213 return Iamgeurl;
214 }
215 }
将图片以流的形式写入到Excel中代码如下：
View Code
1 //获得图片
2 public string[] GetImgURl(int ProjectId)
3 {
4 StringBuilder builder = new StringBuilder();
5 builder.Append("select PicURL from Project_picture ");
6 builder.AppendFormat("where ProjectID ={0} order by Memo", ProjectId);
7 DataSet ds= DbHelper.RunSqlReturnDS(builder.ToString(), this.GetConnectionName());
8 //遍历行
9 List<string> imgurl =new List<string>();
10 foreach(DataRow dr in ds.Tables[0].Rows)
11 {
12 string url = "http://10.0.8.92/" + dr["PicURL"].ToString();
13 imgurl.Add(url);
14 }
15 return imgurl.ToArray();
16 }
17
18
19 public byte[] WriteToExcel(Project info, string ModelUrl)
20 {
21 using (MemoryStream stream = SpreadsheetReader.StreamFromFile(ModelUrl))
22 {
23 DataSet ds = this.GetProjectInfo(info.Project_ID);
24 #region
25 SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
26 string projectName = ds.Tables[0].Rows[0][33].ToString();
27 //计算车位比
28 decimal Car_Count = Convert.ToInt32(ds.Tables[0].Rows[0]["总停车位"]);
29 decimal Suite = Convert.ToInt32(ds.Tables[1].Rows[0]["项目总户数"]);
30 string Car_percent;
31 if (Suite != 0)
32 {
33 Car_percent = string.Format("{0:#0.00}", (Car_Count / Suite));
34
35 }
36 else
37 {
38 Car_percent = "--";
39 }
40 //判断是否为政策性住房
41 int Number = int.Parse(ds.Tables[0].Rows[0]["是否包括政策性住房"].ToString());
42 string status = string.Empty;
43 if (Number == 1)
44 {
45 status = "是";
46 }
47 else if (Number == 0)
48 {
49 status = "否";
50 }
51 else
52 {
53 status = "--";
54 }
55
56
57 //综合设计指标工作表
58 WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "综合设计指标");
59 WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
60 writer.PasteText("E4", ds.Tables[0].Rows[0]["获取方式"].ToString());
61 writer.PasteText("E5", string.Format("{0:yyyy-MM-dd}", ds.Tables[0].Rows[0]["获取时间"]));
62 writer.PasteNumber("E6", ds.Tables[0].Rows[0]["总用地面积"].ToString());
63 writer.PasteText("F6", ds.Tables[0].Rows[0]["Use_Terra_Area_Ex"].ToString());
64 writer.PasteNumber("E7", ds.Tables[0].Rows[0]["总建筑面积"].ToString());
65 writer.PasteText("F7", ds.Tables[0].Rows[0]["Structure_Area_Ex"].ToString());
66 writer.PasteNumber("E8", ds.Tables[0].Rows[0]["计容积率面积"].ToString());
67 writer.PasteText("F8", ds.Tables[0].Rows[0]["Cubage_Structure_Area_Ex"].ToString());
68 writer.PasteNumber("E9", ds.Tables[0].Rows[0]["预计地下建面"].ToString());
69 writer.PasteNumber("E10", ds.Tables[0].Rows[0]["容积率"].ToString());
70 writer.PasteText("F10", ds.Tables[0].Rows[0]["Cubage_rate_Ex"].ToString());
71 writer.PasteNumber("E11", ds.Tables[0].Rows[0]["可租售面积"].ToString());
72 writer.PasteText("F11", ds.Tables[0].Rows[0]["Sell_Area_Ex"].ToString());
73 writer.PasteNumber("E12", ds.Tables[0].Rows[0]["可租售面积占总建筑面积比例"].ToString());
74 writer.PasteText("F12", ds.Tables[0].Rows[0]["Sell_Area_Percent_Ex"].ToString());
75 writer.PasteNumber("E13", ds.Tables[0].Rows[0]["建筑覆盖率"].ToString());
76 writer.PasteText("F13", ds.Tables[0].Rows[0]["Structure_overlay_rate_Ex"].ToString());
77 writer.PasteNumber("E14", ds.Tables[0].Rows[0]["绿化率"].ToString());
78 writer.PasteText("F14", ds.Tables[0].Rows[0]["Virescence_rate_Ex"].ToString());
79 writer.PasteNumber("E15", ds.Tables[0].Rows[0]["道路占有率"].ToString());
80 writer.PasteText("F15", ds.Tables[0].Rows[0]["Road_Hold_rate_Ex"].ToString());
81 writer.PasteNumber("E16", ds.Tables[1].Rows[0]["项目总户数"].ToString());
82 writer.PasteText("F16", ds.Tables[0].Rows[0]["Suite_Ex"].ToString());
83 writer.PasteNumber("E17", ds.Tables[0].Rows[0]["总停车位"].ToString());
84 writer.PasteText("F17", ds.Tables[0].Rows[0]["Car_Count_Ex"].ToString());
85 writer.PasteNumber("E18", ds.Tables[0].Rows[0]["地上停车位"].ToString());
86 writer.PasteText("F18", ds.Tables[0].Rows[0]["Up_Car_Count_Ex"].ToString());
87 writer.PasteNumber("E19", ds.Tables[0].Rows[0]["地下停车位"].ToString());
88 writer.PasteText("F19", ds.Tables[0].Rows[0]["Down_Car_Count_Ex"].ToString());
89 writer.PasteNumber("E20", ds.Tables[0].Rows[0]["可售停车位"].ToString());
90 writer.PasteText("E21", string.Format("{0:#0.00%}", ds.Tables[0].Rows[0]["车位销售比"]));
91 writer.PasteText("E22", Car_percent);//车位比
92 writer.PasteText("F22", ds.Tables[0].Rows[0]["Car_Percent_Ex"].ToString());
93 writer.PasteText("I2", ds.Tables[0].Rows[0]["Project_Name"].ToString());
94 writer.PasteText("K2", status);//是否为政策性住房
95 writer.PasteText("I3", ds.Tables[0].Rows[0]["AreaName"].ToString());
96 writer.PasteText("K3", ds.Tables[0].Rows[0]["Company_Name"].ToString());
97 writer.PasteText("I4", ds.Tables[0].Rows[0]["Start_Year"].ToString());
98 writer.PasteText("K4", ds.Tables[0].Rows[0]["End_Year"].ToString());
99 writer.Save();
100 //建筑类型面积套数明细工作表
101 WorksheetPart worksheetPart2 = SpreadsheetReader.GetWorksheetPartByName(doc, "Data1");
102 WorksheetWriter writer2 = new WorksheetWriter(doc, worksheetPart2);
103 writer2.PasteDataTable(ds.Tables[3], "A2");
104 writer2.Save();
105
106
107 //公建配套Á面积工作表
108 WorksheetPart worksheetPart3 = SpreadsheetReader.GetWorksheetPartByName(doc, "Data2");
109 WorksheetWriter writer3 = new WorksheetWriter(doc, worksheetPart3);
110 writer3.PasteDataTable(ds.Tables[2], "A2");
111 writer3.Save();
112
113 //楼栋规划指标明细工作表
114 WorksheetPart worksheetPart4 = SpreadsheetReader.GetWorksheetPartByName(doc, "Data3");
115 WorksheetWriter writer4 = new WorksheetWriter(doc, worksheetPart4);
116 writer4.PasteDataTable(ds.Tables[5], "A2");
117 writer4.Save();
118
119 #endregion
120 byte[] bytes= null;
121 //获取图片路径集合
122 string[] strlist = GetImgURl(info.Project_ID);
123
124 #region 有图片则添加图片
125 if (strlist.Length > 0)
126 {
127
128 string SaveModel = HttpContext.Current.Server.MapPath(string.Format("..\\ExcelModel\\{0}.xlsx", Guid.NewGuid()));
129 //保存当前文件
130 FileStream file = new FileStream(SaveModel, FileMode.Create);
131 stream.WriteTo(file);
132 //在工作表下添加图片
133 InsertImage iimage = new InsertImage(file, "综合设计指标");
134 int y = 0;
135 int cy = 65;
136 y = cy;
137 int i = 1;
138 foreach (string s in strlist)
139 {
140 string imgurl = iimage.CreateImageSteam(s);
141 if (i > 1)
142 y -= cy;
143 iimage.InsertExcelImage(600, y, null, null, imgurl);
144 Bitmap bm = new Bitmap(imgurl);
145 y += bm.Height - (10 * i);
146 bm.Dispose();
147 i++;
148 //删除文件
149 FileInfo fi1 = new FileInfo(imgurl);
150 fi1.Delete();
151 }
152 iimage.Dispose();
153 file.Close();
154 //读取文件流转换成二进制
155 MemoryStream stream1 = SpreadsheetReader.StreamFromFile(SaveModel);
156 bytes = stream1.ToArray();
157 stream1.Close();
158 //删除文件
159 FileInfo fi = new FileInfo(SaveModel);
160 fi.Delete();
161 }
162 #endregion
163 #region 直接输出文件
164 else
165 {
166 bytes = stream.ToArray();
167 }
168 #endregion
169
170 return bytes;
171 }
172 }
总结：
OPenXML有很多的优点，它比较小巧，但是它的功能非常强大。但是它有一点不足之处就 是OpenXML不支持Excel 2003或者更早的文件格式。只支持Excel 2007及Excel 2010的文件格式。因为从Excel 2007起，这些文件都用xml表示了。我只所这么说因为xlsx其实是一个zip文件，解开此zip文件，我们可以看到很多描述excel内容的xml文件。提到这里，在项目中我遇到一个错误说是有单元格的数据要修复，破坏了xxx.XMl.最后排查是模板做的有问题，像单元格的数据格式与数据不对应所导致的。遇到此问题不要慌，改一下模板就好了。很早就想写这篇文章了，最近几日不忙，就一气呵成写下了《万能报表之超始篇 》、《万能报表之数据篇》、《万能报表之图片篇》和大家一起分享。