1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
|
Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Drawing
Imports System.Drawing.Imaging
Imports Aspose.Cells
Imports Aspose.Cells.Charts
Imports Aspose.Slides
Imports Aspose.Slides.Pptx
Namespace ExcelPowerPoint
Partial Public Class Excel_Chart_Ole_PowerPoint
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim strTemp As String = DateTime.Now.ToString("ddmmyyhhmmss")
If (Not Page.IsPostBack) Then
popListChartType()
getChartImage().Save(Server.MapPath("images") & "\output" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgExcel.ImageUrl = "~/images/output" & lstChartType.Text & strTemp & ".jpg"
RunPPT().GetSlideByPosition(1).GetThumbnail(1f, 1f).Save(Server.MapPath("images") & "\outputPres" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgPowerPoint.ImageUrl = "~/images/outputPres" & lstChartType.Text & strTemp & ".jpg"
Else
Try
Select Case GetPostBackControl(Me)
Case "btnPreviewExcel"
getChartImage().Save(Server.MapPath("images") & "\output" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgExcel.ImageUrl = "~/images/output" & lstChartType.Text & strTemp & ".jpg"
Case "lstChartType"
getChartImage().Save(Server.MapPath("images") & "\output" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgExcel.ImageUrl = "~/images/output" & lstChartType.Text & strTemp & ".jpg"
RunPPT().GetSlideByPosition(1).GetThumbnail(1f, 1f).Save(Server.MapPath("images") & "\outputPres" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgPowerPoint.ImageUrl = "~/images/outputPres" & lstChartType.Text & strTemp & ".jpg"
Case "btnPreviewPres"
If Me.lstPowerPointFmt.Text = "ppt" Then
RunPPT().GetSlideByPosition(1).GetThumbnail(1f, 1f).Save(Server.MapPath("images") & "\outputPres" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgPowerPoint.ImageUrl = "~/images/outputPres" & lstChartType.Text & strTemp & ".jpg"
Else
RunPPTX().Slides(0).GetThumbnail(1f, 1f).Save(Server.MapPath("images") & "\outputPres" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgPowerPoint.ImageUrl = "~/images/outputPres" & lstChartType.Text & strTemp & ".jpg"
End If
Case "lstPowerPointFmt"
If Me.lstPowerPointFmt.Text = "ppt" Then
RunPPT().GetSlideByPosition(1).GetThumbnail(1f, 1f).Save(Server.MapPath("images") & "\outputPres" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgPowerPoint.ImageUrl = "~/images/outputPres" & lstChartType.Text & strTemp & ".jpg"
Else
RunPPTX().Slides(0).GetThumbnail(1f, 1f).Save(Server.MapPath("images") & "\outputPres" & lstChartType.Text & strTemp & ".jpg", ImageFormat.Jpeg)
imgPowerPoint.ImageUrl = "~/images/outputPres" & lstChartType.Text & strTemp & ".jpg"
End If
End Select
Catch ex As Exception
Me.imgExcel.ImageUrl = "~/images/error.jpg"
Me.imgPowerPoint.ImageUrl = "~/images/error.jpg"
End Try
End If
End Sub
Public Shared Function GetPostBackControl(ByVal page As Page) As String
Dim control As Control = Nothing
Dim ctrlname As String = page.Request.Params.Get("__EVENTTARGET")
If ctrlname IsNot Nothing AndAlso ctrlname <> String.Empty Then
control = page.FindControl(ctrlname)
Else
For Each ctl As String In page.Request.Form
Dim c As Control = page.FindControl(ctl)
If TypeOf c Is System.Web.UI.WebControls.Button Then
control = c
Exit For
End If
Next ctl
End If
Return control.ID
End Function
Private Sub popListChartType()
For Each s As String In System.Enum.GetNames(GetType(ChartType))
Me.lstChartType.Items.Add(s)
Next s
Me.lstChartType.Text = "Column"
End Sub
Private Function getChartImage() As Bitmap
'Create a workbook
Dim wb As New Workbook()
'Add an excel chart
Dim chartRows As Integer = Integer.Parse(Me.txtRows.Text) ' 55;
Dim chartCols As Integer = Integer.Parse(Me.txtCols.Text) '25;
Dim chartSheetIndex As Integer = AddExcelChartInWorkbook(wb, chartRows, chartCols)
'Set the OLE size of the chart
wb.Worksheets.SetOleSize(0, chartRows, 0, chartCols)
'wb.Save("d:\\presex\\chk\\images\\wb1.xls");
'Get the image of the chart
'Bitmap imgChart = wb.Worksheets[chartSheetIndex].Charts[0].ToImage();
Return wb.Worksheets(chartSheetIndex).Charts(0).ToImage()
End Function
Public Function RunPPT() As Presentation
'Create a workbook
Dim wb As New Workbook()
'Add an excel chart
Dim chartRows As Integer = Integer.Parse(Me.txtRows.Text) ' 55;
Dim chartCols As Integer = Integer.Parse(Me.txtCols.Text) '25;
Dim chartSheetIndex As Integer = AddExcelChartInWorkbook(wb, chartRows, chartCols)
'Set the OLE size of the chart
wb.Worksheets.SetOleSize(0, chartRows, 0, chartCols)
'Get the image of the chart
Dim imgChart As Bitmap = wb.Worksheets(chartSheetIndex).Charts(0).ToImage()
'imgChart.Save(Server.MapPath("images") + "\\output" + this.lstChartType.Text + ".jpg", ImageFormat.Jpeg);
'Save the workbook to stream
Dim wbStream As MemoryStream = wb.SaveToStream()
'Create a presentation
Dim pres As New Presentation()
'Set the Slide Size
Dim intSldHt As Integer = CInt(Fix(Single.Parse(Me.txtHeightSld.Text) * 576))
Dim intSldWd As Integer = CInt(Fix(Single.Parse(Me.txtWidthSld.Text) * 576))
Dim sz As New Size(intSldWd, intSldHt)
pres.SlideSize = sz
Dim sld As Slide = pres.GetSlideByPosition(1)
'Add the workbook on slide
AddExcelChartInPresentation(pres, sld, wbStream, imgChart)
'Write the output presentation to the disk
Return pres
'pres.Write("d:\\presex\\output" + this.lstChartType.Text + ".ppt");
End Function
Private Function AddExcelChartInWorkbook(ByVal wb As Workbook, ByVal chartRows As Integer, ByVal chartCols As Integer) As Integer
'Add a new worksheet to populate cells with data
Dim dataSheetIdx As Integer = wb.Worksheets.Add()
Dim dataSheet As Worksheet = wb.Worksheets(dataSheetIdx)
Dim sheetName As String = "DataSheet"
dataSheet.Name = sheetName
'Populate DataSheet with data
'for (int i = 0; i < this.GrdViewExcel.RowCount; i++)
' for (int j = 0; j < this.GrdViewExcel.ColumnCount; j++)
Dim strData(3)() As String
strData(0) = New String() { Me.A1.Text, Me.B1.Text, Me.C1.Text, Me.D1.Text, Me.E1.Text }
strData(1) = New String() { Me.A2.Text, Me.B2.Text, Me.C2.Text, Me.D2.Text, Me.E2.Text }
strData(2) = New String() { Me.A3.Text, Me.B3.Text, Me.C3.Text, Me.D3.Text, Me.E3.Text }
strData(3) = New String() { Me.A4.Text, Me.B4.Text, Me.C4.Text, Me.D4.Text, Me.E4.Text }
For i As Integer = 0 To 3
For j As Integer = 0 To 4
Try
dataSheet.Cells(i, j).PutValue(Integer.Parse(strData(i)(j)))
Catch ex As Exception
'string str = ex.Message;
dataSheet.Cells(i, j).PutValue(strData(i)(j))
End Try
'Response.Write(strData[i][j] + "<br>");
Next j
Next i
'Add a chart sheet
Dim chartSheetIdx As Integer = wb.Worksheets.Add(SheetType.Chart)
Dim chartSheet As Worksheet = wb.Worksheets(chartSheetIdx)
chartSheet.Name = "ChartSheet"
'Add a chart in ChartSheet with data series from DataSheet
Dim chartIdx As Integer = 0
For Each s As String In System.Enum.GetNames(GetType(ChartType))
If s.CompareTo(Me.lstChartType.Text) = 0 Then
chartIdx = chartSheet.Charts.Add(CType(System.Enum.Parse(GetType(ChartType), s), ChartType), 0, chartRows, 0, chartCols)
End If
Next s
Dim chart As Chart = chartSheet.Charts(chartIdx)
'for (int rows = 1; rows <= this.GrdViewExcel.RowCount; rows++)
For rows As Integer = 1 To 4
chart.NSeries.Add(sheetName & "!A" & rows.ToString() & ":E" & rows.ToString(), False)
Next rows
'Set ChartSheet an active sheet
wb.Worksheets.ActiveSheetIndex = chartSheetIdx
'wb.Save("");
Return chartSheetIdx
End Function
Private Sub AddExcelChartInPresentation(ByVal pres As Presentation, ByVal sld As Slide, ByVal wbStream As Stream, ByVal imgChart As Bitmap)
Dim pic As New Aspose.Slides.Picture(pres, imgChart)
Dim picId As Integer = pres.Pictures.Add(pic)
Dim oleWidth As Integer = CInt(Fix(Single.Parse(Me.txtWidthOLE.Text) * 576))
Dim oleHeight As Integer = CInt(Fix(Single.Parse(Me.txtHeightOLE.Text) * 576))
Dim x As Integer = 0
Dim chartOleData(wbStream.Length - 1) As Byte
wbStream.Position = 0
wbStream.Read(chartOleData, 0, chartOleData.Length)
Dim oof As OleObjectFrame = sld.Shapes.AddOleObjectFrame(x, 0, oleWidth, oleHeight, "Excel.Sheet.8", chartOleData)
oof.PictureId = picId
End Sub
Public Function RunPPTX() As PresentationEx
'Create a workbook
Dim wb As New Workbook()
'Add an excel chart
Dim chartRows As Integer = Integer.Parse(Me.txtRows.Text)
Dim chartCols As Integer = Integer.Parse(Me.txtCols.Text)
Dim chartSheetIndex As Integer = AddExcelChartInWorkbookPPTX(wb, chartRows, chartCols)
'Set the OLE size of the chart
wb.Worksheets.SetOleSize(0, chartRows, 0, chartCols)
'Get the image of the chart
Dim imgChart As Bitmap = wb.Worksheets(chartSheetIndex).Charts(0).ToImage()
'Save the workbook to stream
Dim wbStream As New MemoryStream()
If Me.lstExcelFmt.Text = "XLSX" Then
wb.Save(wbStream, FileFormatType.Excel2007Xlsx)
Else
wb.Save(wbStream, FileFormatType.Excel2003)
End If
'Create a presentation
Dim pres As New PresentationEx()
'Set the Slide Size
Dim fltSldHt As Single = Single.Parse(Me.txtHeightSld.Text) * 72
Dim fltSldWd As Single = Single.Parse(Me.txtWidthSld.Text) * 72
Dim sz As New SizeF(fltSldWd, fltSldHt)
pres.SlideSize.Size = sz
Dim sld As SlideEx = pres.Slides(0)
'Add the workbook on slide
AddExcelChartInPresentationPPTX(pres, sld, wbStream, imgChart)
Return pres
'Write the output presentation to the disk
'pres.Write("d:\\output.pptx");
End Function
Private Function AddExcelChartInWorkbookPPTX(ByVal wb As Workbook, ByVal chartRows As Integer, ByVal chartCols As Integer) As Integer
'Add a new worksheet to populate cells with data
Dim dataSheetIdx As Integer = wb.Worksheets.Add()
Dim dataSheet As Worksheet = wb.Worksheets(dataSheetIdx)
Dim sheetName As String = "DataSheet"
dataSheet.Name = sheetName
Dim strData(3)() As String
strData(0) = New String() { Me.A1.Text, Me.B1.Text, Me.C1.Text, Me.D1.Text, Me.E1.Text }
strData(1) = New String() { Me.A2.Text, Me.B2.Text, Me.C2.Text, Me.D2.Text, Me.E2.Text }
strData(2) = New String() { Me.A3.Text, Me.B3.Text, Me.C3.Text, Me.D3.Text, Me.E3.Text }
strData(3) = New String() { Me.A4.Text, Me.B4.Text, Me.C4.Text, Me.D4.Text, Me.E4.Text }
For i As Integer = 0 To 3
For j As Integer = 0 To 4
Try
dataSheet.Cells(i, j).PutValue(Integer.Parse(strData(i)(j)))
Catch ex As Exception
'string str = ex.Message;
dataSheet.Cells(i, j).PutValue(strData(i)(j))
End Try
'Response.Write(strData[i][j] + "<br>");
Next j
Next i
'Add a chart sheet
Dim chartSheetIdx As Integer = wb.Worksheets.Add(SheetType.Chart)
Dim chartSheet As Worksheet = wb.Worksheets(chartSheetIdx)
chartSheet.Name = "ChartSheet"
'Add a chart in ChartSheet with data series from DataSheet
Dim chartIdx As Integer = 0
For Each s As String In System.Enum.GetNames(GetType(ChartType))
If s.CompareTo(Me.lstChartType.Text) = 0 Then
chartIdx = chartSheet.Charts.Add(CType(System.Enum.Parse(GetType(ChartType), s), ChartType), 0, chartRows, 0, chartCols)
End If
Next s
Dim chart As Chart = chartSheet.Charts(chartIdx)
For rows As Integer = 1 To 4
chart.NSeries.Add(sheetName & "!A" & rows.ToString() & ":E" & rows.ToString(), False)
Next rows
'Set ChartSheet an active sheet
wb.Worksheets.ActiveSheetIndex = chartSheetIdx
Return chartSheetIdx
End Function
Private Sub AddExcelChartInPresentationPPTX(ByVal pres As PresentationEx, ByVal sld As SlideEx, ByVal wbStream As Stream, ByVal imgChart As Bitmap)
Dim oleWidth As Single = Single.Parse(Me.txtWidthOLE.Text) * 72
Dim oleHeight As Single = Single.Parse(Me.txtHeightOLE.Text) * 72
Dim x As Integer = 0
Dim chartOleData(wbStream.Length - 1) As Byte
wbStream.Position = 0
wbStream.Read(chartOleData, 0, chartOleData.Length)
Dim oof As OleObjectFrameEx = Nothing
If Me.lstExcelFmt.Text = "XLSX" Then
oof = sld.Shapes.AddOleObjectFrame(x, 0, oleWidth, oleHeight, "Excel.Sheet.12", chartOleData)
Else
oof = sld.Shapes.AddOleObjectFrame(x, 0, oleWidth, oleHeight, "Excel.Sheet.8", chartOleData)
End If
oof.Image = pres.Images.AddImage(CType(imgChart, System.Drawing.Image))
End Sub
Protected Sub btnDownload_Click(ByVal sender As Object, ByVal e As EventArgs)
If Me.lstPowerPointFmt.Text = "ppt" Then
RunPPT().Save("ExcelPowerPoint." & Me.lstPowerPointFmt.Text,Aspose.Slides.Export.SaveFormat.Ppt,Response,False)
End If
If Me.lstPowerPointFmt.Text = "pptx" Then
RunPPTX().Save("ExcelPowerPoint." & Me.lstPowerPointFmt.Text, Aspose.Slides.Export.SaveFormat.Pptx, Response, False)
End If
Me.Response.End()
End Sub
End Class
End Namespace
|