随笔- 567 文章- 0 评论- 0 阅读- 55万

VSTO - 使用Excel加载项生成表和图表

此示例显示如何创建Excel的加载项，使用户可以在其工作表中选择库存符号，然后生成一个新工作表，显示库存的历史性能。工作表包含数据表和图表。

介绍
Excel加载项通常不知道工作表包含什么。典型的加载项解决方案使用户在自定义UI或工作表单元格中选择的信息来为Excel中未内置的用户执行某种服务。

此示例中的加载项为通过使用工作表计划退休的人员提供服务。该加载项假设用户将在其工作表中的某处存在股票或共同基金符号列表。用户选择一个符号，然后从自定义任务窗格中生成一个包含历史股价表的新工作表，以及一段时间内显示股票表现的图表。

以下是您学到的几件事情：

1.如何创建与工作表交互的自定义任务窗格。
2.如何从服务获取数据并在解决方案中使用该数据。
3.如何生成新的工作表，列表对象（表）以及从服务中显示数据的图表
4.如何使用户可以使用控件自定义任务窗格来更改列表对象和图表的外观和内容。
5.当用户在自定义任务窗格中选择控件时，如何删除列表对象，图表和工作表。

要求
要运行此示例，您将需要Visual Studio 2013和Excel 2013或Excel 2010.有关您通常需要开发Office解决方案的更多信息，请参阅配置计算机来开发Office解决方案。

创建实例

生成表格，表格和数据图表：

1.按F5。
2.在Excel中，创建一个新的工作表。
3.工作表侧面显示一个自定义任务窗格。
4.在单元格中输入诸如“MSFT”之类的股票代码。
5.在工作表中选择另一个空白单元格，然后选择包含刚刚添加的符号的单元格。
6.在任务窗格中，选择开始日期，然后选择显示所选符号的价格历史记录复选框。

一张新工作表打开，一张历史价格表与图表一起出现。

要通过使用任务窗格中的控件修改表和图表的内容和外观：

1.在任务窗格中，选择表格中的任何一个标题（例如：high或close），以便在表格中显示和隐藏该列。
2.通过选择任何无线电彩色单选按钮更改表格的颜色主题。
3.通过使用任务窗格底部附近的任何组合框，更改图表显示的数据列，图表的样式或图表的颜色。
删除表格、图表和工作表

清除所选符号的显示价格历史记录复选框以删除工作表及其上的控件

更多信息
有关Visual Studio Tools for Office（VSTO）的更多信息：http://msdn.microsoft.com/en-us/vsto/default.aspx。

工程文件结构：

创建窗体界面（TableAndChartPane.vb）

代码（TableAndChartPane.vb）：

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

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

Imports System.Net
 
Public Class TableAndChartPane
    Public Sub New()
        InitializeComponent()
        PopulateListObjectHeaderCheckBoxList()
 
        'Populate the chart data source combo box.
        chartDataSourceComboBox.Items.Add("Open")
        chartDataSourceComboBox.Items.Add("High")
        chartDataSourceComboBox.Items.Add("Low")
        chartDataSourceComboBox.Items.Add("Close")
        chartDataSourceComboBox.Items.Add("Volume")
        chartDataSourceComboBox.Items.Add("Adj_Close")
 
        'Populate the chart style combo box.
        ChartStyleComboBox.Items.Add("line")
        ChartStyleComboBox.Items.Add("Column")
        ChartStyleComboBox.Items.Add("Area")
 
        'Populate the chart style combo box.
        ChartColorThemeComboBox.Items.Add("Gray background")
        ChartColorThemeComboBox.Items.Add("Blue background")
        ChartColorThemeComboBox.Items.Add("White background")
 
    End Sub
 
    Dim _vstoWorkSheet As Microsoft.Office.Tools.Excel.Worksheet
    Dim _worksheetInteropObject As Excel.Worksheet
    Dim _listObject As Microsoft.Office.Tools.Excel.ListObject = Nothing
    Dim _chart As Microsoft.Office.Tools.Excel.Chart = Nothing
 
 
    Public ReadOnly Property VstoWorksheet() As Microsoft.Office.Tools.Excel.Worksheet
        Get
            If _vstoWorkSheet Is Nothing Then
                If _worksheetInteropObject Is Nothing Then
                    _vstoWorkSheet = Globals.Factory.GetVstoObject(DirectCast(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1), Excel.Worksheet))
                Else
                    _vstoWorkSheet = Globals.Factory.GetVstoObject(_worksheetInteropObject)
                End If
            End If
 
            _vstoWorkSheet.Activate()
            Return _vstoWorkSheet
        End Get
    End Property
 
    Public Sub SetWorksheet(worksheetInteropObject As Excel.Worksheet)
        _worksheetInteropObject = worksheetInteropObject
        _vstoWorkSheet = Nothing
    End Sub
 
    Private Sub PopulateListObjectHeaderCheckBoxList()
        ListObjectHeaders.Items.Add("Date", True)
        ListObjectHeaders.Items.Add("Open", True)
        ListObjectHeaders.Items.Add("High", True)
        ListObjectHeaders.Items.Add("Low", True)
        ListObjectHeaders.Items.Add("Close", True)
        ListObjectHeaders.Items.Add("Volume", True)
        ListObjectHeaders.Items.Add("Adj Close", True)
    End Sub
 
    ' When user chooses the checkbox, generate a new sheet, a table of data, and a chart.
    ' If the sheet, table, and chart already exist, delete them.
    Private Sub ListObject_Check(sender As Object, e As EventArgs) Handles ListObjectCheckBox.Click
        Dim listObjectName As String = "stockHistoryListObject"
        Dim chartName As String = "stockHistoryChart"
 
        If DirectCast(sender, System.Windows.Forms.CheckBox).Checked Then
            If dateTimePicker1.Value.[Date] >= DateTime.Now.[Date] Then
 
                MessageBox.Show("Please choose a starting date before today's date")
                DirectCast(sender, System.Windows.Forms.CheckBox).Checked = False
            Else
                Dim selection As Excel.Range = SelectedRange
                Dim tickerSymbol As String = selection.Value2
 
                Dim data As List(Of HistoricalStock) = Nothing
                Try
                    data = GetDataUpdatesFoOneDataSource(tickerSymbol, dateTimePicker1.Value.[Date].ToString())
                Catch generatedExceptionName As Exception
                    MessageBox.Show("Unable to return data. Please ensure that you select a valid stock ticker symbol" & " in your worksheet and then try again")
                    DirectCast(sender, System.Windows.Forms.CheckBox).Checked = False
                    Return
                End Try
 
                CreateNewSheet()
 
                If selection IsNot Nothing Then
                    _listObject = VstoWorksheet.Controls.AddListObject(Globals.ThisAddIn.Application.Range("A1"), listObjectName)
                    groupBox1.Enabled = True
                    groupBox2.Enabled = True
                    groupBox3.Enabled = True
 
                    _listObject.DataBindings.Clear()
                    _listObject.SetDataBinding(data)
 
                    Dim counter As Integer = 0
 
                    For Each range As Excel.Range In _listObject.HeaderRowRange.Cells
                        range.Value2 = ListObjectHeaders.Items(counter)
                        counter += 1
                    Next
 
                    AddChart(chartName)
 
                End If
            End If
        Else
            VstoWorksheet.Controls.Remove(listObjectName)
            VstoWorksheet.Controls.Remove(chartName)
            VstoWorksheet.Delete()
            SetWorksheet(Globals.ThisAddIn.Application.ActiveWorkbook.Sheets(1))
 
            groupBox1.Enabled = False
            groupBox2.Enabled = False
            groupBox3.Enabled = False
        End If
    End Sub
 
    Private Sub CreateNewSheet()
 
        Dim newWorksheet As Excel.Worksheet
        newWorksheet = DirectCast(Globals.ThisAddIn.Application.Worksheets.Add(), Excel.Worksheet)
        newWorksheet.Name = "Price history"
        SetWorksheet(newWorksheet)
    End Sub
 
    Private ReadOnly Property SelectedRange() As Excel.Range
        Get
            Dim selection As Excel.Range = TryCast(VstoWorksheet.Application.Selection, Excel.Range)
 
            If selection IsNot Nothing AndAlso selection.Worksheet.Name = VstoWorksheet.Name Then
                Return selection
            End If
 
            Return Nothing
        End Get
    End Property
 
    ' Define a class to hold information from the stock service.
    Public Class HistoricalStock
        Public Property [Date]() As DateTime
            Get
                Return m_Date
            End Get
            Set(value As DateTime)
                m_Date = value
            End Set
        End Property
        Private m_Date As DateTime
        Public Property Open() As Double
            Get
                Return m_Open
            End Get
            Set(value As Double)
                m_Open = value
            End Set
        End Property
        Private m_Open As Double
        Public Property High() As Double
            Get
                Return m_High
            End Get
            Set(value As Double)
                m_High = value
            End Set
        End Property
        Private m_High As Double
        Public Property Low() As Double
            Get
                Return m_Low
            End Get
            Set(value As Double)
                m_Low = value
            End Set
        End Property
        Private m_Low As Double
        Public Property Close() As Double
            Get
                Return m_Close
            End Get
            Set(value As Double)
                m_Close = value
            End Set
        End Property
        Private m_Close As Double
        Public Property Volume() As Double
            Get
                Return m_Volume
            End Get
            Set(value As Double)
                m_Volume = value
            End Set
        End Property
        Private m_Volume As Double
        Public Property AdjClose() As Double
            Get
                Return m_AdjClose
            End Get
            Set(value As Double)
                m_AdjClose = value
            End Set
        End Property
        Private m_AdjClose As Double
    End Class
 
    ' Query the stock service.
    Public Function GetDataUpdatesFoOneDataSource(ticker As String, mostRecentDate As String) As List(Of HistoricalStock)
        Dim _startDate As DateTime = DateTime.Now.[Date]
        Dim _endDate As DateTime
        _endDate = Convert.ToDateTime(mostRecentDate)
 
        Dim retval As New List(Of HistoricalStock)()
 
        If _startDate.[Date] <> _endDate.[Date] Then
            Dim _startMonthTemp As Integer = _startDate.Month - 1
            Dim _startMonth As String = _startMonthTemp.ToString()
            Dim _startDay As String = _startDate.Day.ToString()
            Dim _startYear As String = _startDate.Year.ToString()
 
            _endDate = _endDate.AddDays(1)
            Dim _endMonthTemp As Integer = _endDate.Month - 1
            Dim _endMonth As String = _endMonthTemp.ToString()
            Dim _endDay As String = _endDate.Day.ToString()
            Dim _endYear As String = _endDate.Year.ToString()
 
            Using web As New WebClient()
                Dim _inputString As String = "http://ichart.finance.yahoo.com/table.csv?s=" & ticker & "&d=" & _startMonth & "&e=" & _startDay & "&f=" & _startYear & "&g=d&a=" & _endMonth & "&b=" & _endDay & "&c=" & _endYear & "&ignore=.csv"
 
                Dim data As String = web.DownloadString(_inputString)
 
                data = data.Replace("r", "")
                Dim rows As String() = data.Split(ControlChars.Lf)
 
                'First row is headers so Ignore it
                For i As Integer = 1 To rows.Length - 1
                    If rows(i).Replace("n", "").Trim() = "" Then
                        Continue For
                    End If
                    Dim cols As String() = rows(i).Split(","c)
                    Dim hs As New HistoricalStock()
                    hs.[Date] = Convert.ToDateTime(cols(0))
                    hs.Open = Convert.ToDouble(cols(1))
                    hs.High = Convert.ToDouble(cols(2))
                    hs.Low = Convert.ToDouble(cols(3))
                    hs.Close = Convert.ToDouble(cols(4))
                    hs.Volume = Convert.ToDouble(cols(5))
                    hs.AdjClose = Convert.ToDouble(cols(6))
                    retval.Add(hs)
                Next
 
                If retval.Count > 1 Then
                    If retval(0).[Date] = retval(1).[Date] Then
                        retval.RemoveAt(0)
                    End If
                End If
            End Using
        End If
        Return retval
    End Function
 
    Private Sub AddChart(chartName As String)
 
        Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
            Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
 
        Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
            Globals.Factory.GetVstoObject(NativeWorksheet)
 
        Dim cells As Excel.Range = worksheet.Range("I1", "O22")
        Dim chart As Microsoft.Office.Tools.Excel.Chart = worksheet.Controls.AddChart(cells, chartName)
        chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine
        chart.SetSourceData(_listObject.ListColumns(5).Range.EntireColumn)
        _chart = chart
    End Sub
 
    Private Sub ListObjectHeaders_Click(sender As Object, e As EventArgs) Handles ListObjectHeaders.Click
        Dim columnToHide As Excel.Range = Nothing
 
        Select Case ListObjectHeaders.SelectedItem.ToString()
            Case "Date"
                columnToHide = _listObject.ListColumns(1).Range.EntireColumn
                Exit Select
            Case "Open"
                columnToHide = _listObject.ListColumns(2).Range.EntireColumn
                Exit Select
            Case "High"
                columnToHide = _listObject.ListColumns(3).Range.EntireColumn
                Exit Select
            Case "Low"
                columnToHide = _listObject.ListColumns(4).Range.EntireColumn
                Exit Select
            Case "Close"
                columnToHide = _listObject.ListColumns(5).Range.EntireColumn
                Exit Select
            Case "Volume"
                columnToHide = _listObject.ListColumns(6).Range.EntireColumn
                Exit Select
            Case "Adj Close"
                columnToHide = _listObject.ListColumns(7).Range.EntireColumn
                Exit Select
        End Select
 
        If columnToHide.Hidden = False Then
            columnToHide.Hidden = True
        Else
            columnToHide.Hidden = False
        End If
 
    End Sub
    Private Sub BlackStyle_CheckedChanged(sender As Object, e As EventArgs) Handles BlackStyle.CheckedChanged
        _listObject.TableStyle = "TableStyleMedium1"
    End Sub
 
    Private Sub BlueStyle_CheckedChanged(sender As Object, e As EventArgs) Handles BlueStyle.CheckedChanged
        _listObject.TableStyle = "TableStyleMedium2"
    End Sub
 
    Private Sub OrangeStyle_CheckedChanged(sender As Object, e As EventArgs) Handles OrangeStyle.CheckedChanged
        _listObject.TableStyle = "TableStyleMedium3"
    End Sub
 
    Private Sub GrayStyle_CheckedChanged(sender As Object, e As EventArgs) Handles GrayStyle.CheckedChanged
        _listObject.TableStyle = "TableStyleMedium4"
    End Sub
 
    Private Sub GreenStyle_CheckedChanged(sender As Object, e As EventArgs) Handles GreenStyle.CheckedChanged
        _listObject.TableStyle = "TableStyleMedium7"
    End Sub
 
    Private Sub chartDataSourceComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles chartDataSourceComboBox.SelectedIndexChanged
        Select Case chartDataSourceComboBox.Text
            Case "Open"
                _chart.SetSourceData(_listObject.ListColumns(2).Range.EntireColumn)
                Exit Select
            Case "High"
                _chart.SetSourceData(_listObject.ListColumns(3).Range.EntireColumn)
                Exit Select
            Case "Low"
                _chart.SetSourceData(_listObject.ListColumns(4).Range.EntireColumn)
                Exit Select
            Case "Close"
                _chart.SetSourceData(_listObject.ListColumns(5).Range.EntireColumn)
                Exit Select
            Case "Volume"
                _chart.SetSourceData(_listObject.ListColumns(6).Range.EntireColumn)
                Exit Select
            Case "Adj_Close"
                _chart.SetSourceData(_listObject.ListColumns(7).Range.EntireColumn)
                Exit Select
            Case Else
                MessageBox.Show("Invalid Selection")
                Exit Select
        End Select
    End Sub
 
    Private Sub ChartStyleComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ChartStyleComboBox.SelectedIndexChanged
        Select Case ChartStyleComboBox.Text
            Case "Line"
                _chart.ChartType = Excel.XlChartType.xlLine
                Exit Select
            Case "Column"
                _chart.ChartType = Excel.XlChartType.xlColumnClustered
                Exit Select
            Case "Area"
                _chart.ChartType = Excel.XlChartType.xlArea
                Exit Select
            Case Else
                MessageBox.Show("Invalid Selection")
                Exit Select
        End Select
    End Sub
 
    Private Sub ChartColorThemeComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ChartColorThemeComboBox.SelectedIndexChanged
        Select Case ChartColorThemeComboBox.Text
            Case "White background"
                _chart.ChartStyle = 227
                Exit Select
            Case "Blue background"
                _chart.ChartStyle = 229
                Exit Select
            Case "Gray background"
                _chart.ChartStyle = 236
                Exit Select
            Case Else
                MessageBox.Show("Invalid Selection")
                Exit Select
        End Select
    End Sub
 
 
 
 
    Private Sub TableAndChartPane_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
    End Sub
End Class