Silverlight学习笔记十五关于Sliverlight导出Excel(浏览器外运行)
关于Sliverlight导出Excel(浏览器外运行)比较复杂
下面是效果
一、导出Excel步骤
1.新建Silverlight应用程序ExcelDemo
2.添加引用：添加对Microsoft.CSharp程序集的 引用
3.项目属性设置
在“属性”面板中，勾选“允许在浏览器外运行应用程序”
点击“浏览器外设置”
设置应用程序显示名称，宽度、高度。勾选“显示安装菜单”和“在浏览器外运行时需要提示的信任”.
这样Silverlight就可以在浏览器外运行了
4.实体Customer
public class Customer : INotifyPropertyChanged
{
private string m_name;
public string Name
{
get { return m_name; }
set
{
m_name = value;
OnPropertyChanged("Name");
}
}
public int ID { get; set; }
public short Age { get; set; }
public event PropertyChangedEventHandler PropertyChanged;
protected void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
}
5.数据源DataSource
public class DataSource
{
public static IList<Customer> LoadData()
{
IList<Customer> list = new List<Customer>();
for (int i = 0; i < 10000; i++)
{
list.Add(new Customer(){Age=23,ID=1, Name="李秀婷"});
list.Add(new Customer(){Age=24,ID=2, Name="吴建强"});
list.Add(new Customer(){Age=24,ID=3, Name="张美静"});
list.Add(new Customer(){Age=25,ID=4, Name="马超"});
list.Add(new Customer(){Age=25,ID=5, Name="吴美音"});
list.Add(new Customer(){Age=33,ID=6, Name="王菲"});
list.Add(new Customer(){Age=48,ID=7, Name="刘德华"});
list.Add(new Customer(){Age=48,ID=8, Name="李连杰"});
list.Add(new Customer(){Age=36,ID=9, Name="权相宇"});
list.Add(new Customer(){Age=52,ID=10, Name="成龙"});
list.Add(new Customer(){Age=22,ID=11, Name="丁魁"});
list.Add(new Customer(){Age=35,ID=12, Name="金喜善"});
list.Add(new Customer(){Age=38,ID=13, Name="宋承宪"});
list.Add(new Customer(){Age=24,ID=14, Name="赵建"});
list.Add(new Customer(){Age=33,ID=15, Name="徐工"});
list.Add(new Customer(){Age=21,ID=16, Name="尹恩惠"});
list.Add(new Customer(){Age=23,ID=17, Name="金泰熙"});
list.Add(new Customer(){Age=12,ID=18, Name="马缙波"});
}
return list;
}
}
6.界面MainPage
<UserControl x:Name="userControl" xmlns:dataGrid="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="ExcelDemo.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"
mc:Ignorable="d"
d:DesignHeight="300" d:DesignWidth="400">
<Grid x:Name="LayoutRoot" Background="Transparent">
<TextBlock x:Name="text" Text="加载中，请等待..." TextAlignment="Center" FontSize="18.667" FontWeight="Bold" Visibility="Collapsed"/>
<Rectangle Height="51" HorizontalAlignment="Left" Margin="1,325,0,0"
x:Name="rectangle2"
Stroke="Black"
StrokeThickness="1"
VerticalAlignment="Top"
Width="576">
<Rectangle.Fill>
<LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
<GradientStop Color="Black" Offset="1"></GradientStop>
<GradientStop Color="#6FFFFFFF" Offset="0"></GradientStop>
</LinearGradientBrush>
</Rectangle.Fill>
</Rectangle>
<dataGrid:DataGrid AutoGenerateColumns="True"
Height="295"
IsReadOnly="True"
HorizontalAlignment="Left"
VerticalScrollBarVisibility="Auto"
Margin="0,30,0,0"
x:Name="customerDataGrid" VerticalAlignment="Top"
Width="576" ItemsSource="{Binding CustomerCollection, ElementName=userControl, Mode=TwoWay}" />
<Border BorderBrush="Black"
BorderThickness="1"
Height="31"
HorizontalAlignment="Left"
Margin="0,0,0,0"
x:Name="border1"
VerticalAlignment="Top"
Width="576"
CornerRadius="5,5,0,0"
MouseLeftButtonDown="border1_MouseLeftButtonDown">
<Border.Background>
<LinearGradientBrush EndPoint="0.5,1"
StartPoint="0.5,0">
<GradientStop Color="Black"
Offset="0" />
<GradientStop Color="Black"
Offset="0" />
</LinearGradientBrush>
</Border.Background>
<Border BorderBrush="{x:Null}"
BorderThickness="0"
CornerRadius="5,5,0,0"
Height="28"
x:Name="border2"
Width="570"
Margin="0,2,0,0"
HorizontalAlignment="Center"
VerticalAlignment="Top">
<Border.Background>
<LinearGradientBrush EndPoint="0.5,1"
StartPoint="0.5,0">
<GradientStop Color="White"
Offset="0" />
<GradientStop Color="#00000000"
Offset="0.616" />
</LinearGradientBrush>
</Border.Background>
<TextBlock Height="19"
IsHitTestVisible="False"
x:Name="textBlock1"
Text="Silverlight 导出 Excel "
Foreground="White"
FontSize="14"
Width="558" />
</Border>
</Border>
<Button Height="28"
HorizontalAlignment="Left" Click="exportToExcelButton_Click"
Margin="244,333,0,0"
x:Name="exportToExcelButton"
Content="Export To Excel"
Width="109" />
<Button Height="28"
HorizontalAlignment="Left" Click="installButton_Click"
Margin="244,333,0,0"
x:Name="installButton"
Content="Install"
VerticalAlignment="Top"
Width="109" />
<Rectangle Height="18"
Cursor="Hand"
HorizontalAlignment="Left"
Margin="549,6,0,0"
x:Name="rectangle1"
Stroke="Black"
StrokeThickness="1"
VerticalAlignment="Top"
Width="19"
Fill="#FF990000"
MouseLeftButtonDown="rectangle1_MouseLeftButtonDown"/>
<TextBlock Height="12"
HorizontalAlignment="Left"
Margin="555,7,0,0"
x:Name="textBlock2"
Text="X"
Foreground="White"
VerticalAlignment="Top" />
</Grid>
</UserControl>
7.后台代码
using System.Windows;
using System.Windows.Controls;
using System.Collections.ObjectModel;
using System.Runtime.InteropServices.Automation;
using System.Windows.Input;
using System;
using System.Threading;
using System.Collections.Generic;
namespace ExcelDemo
{
public partial class MainPage : UserControl
{
//建立dynamic的excel对象
dynamic excel = null;
bool newInstance = true;
delegate void SheetChangedDelegate(dynamic excelSheet, dynamic rangeArgs);
/// <summary>
/// 获取或设置集合
/// </summary>
public IList<Customer> CustomerCollection
{
get { return (IList<Customer>)GetValue(CustomerCollectionProperty); }
set { SetValue(CustomerCollectionProperty, value); }
}
public static readonly DependencyProperty CustomerCollectionProperty =
DependencyProperty.Register("CustomerCollection", typeof(IList<Customer>), typeof(MainPage), new PropertyMetadata(null));
public MainPage()
{
InitializeComponent();
//判断应用程序是否已安装（浏览器外）
if (App.Current.InstallState == InstallState.Installed)
{
if (App.Current.IsRunningOutOfBrowser)
{
installButton.Visibility = Visibility.Collapsed;
rectangle1.Visibility = Visibility.Visible;
textBlock2.Visibility = Visibility.Visible;
App.Current.MainWindow.TopMost = true;
}
else
{
installButton.IsEnabled = false;
rectangle1.Visibility = Visibility.Collapsed;
textBlock2.Visibility = Visibility.Collapsed;
}
}
else
{
installButton.Visibility = Visibility.Visible;
rectangle1.Visibility = Visibility.Collapsed;
textBlock2.Visibility = Visibility.Collapsed;
}
CustomerCollection = DataSource.LoadData();
//异步执行
ThreadPool.QueueUserWorkItem(t =>
{
UIInvoke(() =>
{
text.Visibility = Visibility.Visible;
});
UIInvoke(() =>
{
CustomerCollection = DataSource.LoadData();
});
UIInvoke(() =>
{
text.Visibility = Visibility.Collapsed;
});
});
}
/// <summary>
/// 异步方法
/// </summary>
/// <param name="action"></param>
private void UIInvoke(Action action)
{
System.Windows.Threading.Dispatcher dispatcher = Deployment.Current.Dispatcher;
if (dispatcher.CheckAccess())
{
action();
}
else
{
ManualResetEvent resetEvent = new ManualResetEvent(false);
resetEvent.Reset();
dispatcher.BeginInvoke(() =>
{
action();
resetEvent.Set();
});
resetEvent.WaitOne();
}
}
private void rectangle1_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
App.Current.MainWindow.Close();
}
private void installButton_Click(object sender, RoutedEventArgs e)
{
App.Current.InstallStateChanged += (Current_InstallStateChanged);
//开始安装
App.Current.Install();
}
void Current_InstallStateChanged(object sender, EventArgs e)
{
if (App.Current.InstallState == InstallState.Installed)
{
installButton.IsEnabled = false;
}
}
private void exportToExcelButton_Click(object sender, RoutedEventArgs e)
{
//执行导出
LaunchExcel(sender, e);
}
/// <summary>
/// 导出数据到Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void LaunchExcel(object sender, RoutedEventArgs e)
{
excel = AutomationFactory.CreateObject("Excel.Application");
excel.Visible = true;
dynamic workbook = excel.workbooks;
workbook.Add();
dynamic sheet = excel.ActiveSheet;
dynamic cell = null;
int i = 1;
foreach (Customer item in customerDataGrid.ItemsSource)
{
cell = sheet.Cells[i, 1];
cell.Value = item.Name;
cell.ColumnWidth = 50;
cell = sheet.Cells[i, 2];
cell.Value = item.ID;
cell = sheet.Cells[i, 3];
cell.Value = item.Age;
i++;
}
if (newInstance)
{
App.Current.MainWindow.Closing += (MainWindow_Closing);
excel.SheetChange += new SheetChangedDelegate(SheetChangedEventHandler);
newInstance = false;
}
}
void MainWindow_Closing(object sender, System.ComponentModel.ClosingEventArgs e)
{
if (excel != null)
{
excel.SheetChange -= new SheetChangedDelegate(SheetChangedEventHandler);
}
}
private void border1_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
App.Current.MainWindow.DragMove();
}
private void SheetChangedEventHandler(dynamic excelSheet, dynamic rangeArgs)
{
dynamic sheet = excelSheet;
dynamic col2range = sheet.Range("A1:A" + CustomerCollection.Count);
for (int i = 0; i < CustomerCollection.Count; i++)
{
CustomerCollection[i].Name = col2range.Item(i + 1).Value.ToString();
}
}
}
}
注：1.源代码下载地址：https://files.cnblogs.com/salam/ExcelDemo.rar
2.关于类型dynamic：
在Visual C# 2010中引入了一种新的dynamic类型，该类型是一个静态的（static）类型，但是一个dynamic类型的对象会绕过静态类型检查。在大多数情况下dynamic和object类型有些相似，但是在编译时，dynamic类型被假定为支持任何操作，也就是说dynamic类型的对象可以是一个Office对象，可以是一个COM对象或者是DOM对象，而如果在运行时发现该对象不是期望的对象则会抛出一个运行时异常。
由于把一个对象定义为dynamic类型，因此在代码中编写任意调用在编译时都是合法的。
使用了通过动态类型参数的方法会在运行时解析而不是在编译时解析，在.NET Framework 4 Beta1中引入了新的dynamic language runtime（DLR），它为C#中的dynamic类型提供了支持，而且提供了对诸如IronPython和IronRuby等动态语言的实现。
Visual C# 2010使用dynamic类型和命名和可选参数为与COM API交互提供了便利。许多COM方法接收各种类型参数并且通常会返回object类型值，开发人员需要进行类型转换以进行进一步操作。在.NET Framework 4中，如果使用/link开关编译程序，dynamic类型允许在COM调用时将object类型作为dynamic类型对待，这样可以避免类型转换
