C#之WPF 的Excel导入导出
我们开发企业的管理系统通常都有导入和表单导入和导出的功能,用来方便把数据输出和打印。而C#中不同的框架导入和导出的实现方法也有一些不同。
下面是我在编写WPF程序时用到的导出方法。
1、 首先我们在先创建一个WPF程序,创建一个页面。页面写个DataGrid控件。表格代码如下
<UserControl x:Class="HospitalWHandPhManagement.View.PharmacyManagement.UC_PharmacyNeckDrugFromWareHouse"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:local="clr-namespace:HospitalWHandPhManagement.View.PharmacyManagement"
mc:Ignorable="d"
d:DesignHeight="560" d:DesignWidth="1360">
<DataGrid x:Name="dgWareHouseDrug" MaxHeight="300" Height="250" MinHeight="180" IsReadOnly="False"
AutoGenerateColumns="False" Background="White"
CanUserAddRows="False"
CanUserResizeColumns="False" CanUserResizeRows="False"
SelectionMode="Extended" CanUserReorderColumns="False"
ScrollViewer.VerticalScrollBarVisibility="Visible"
ScrollViewer.HorizontalScrollBarVisibility="Visible"
AlternationCount="2"
Margin="5.6,25,7.2,0" VerticalAlignment="Top" >
<DataGrid.Columns>
<DataGridTemplateColumn Width="55" Header="序号" >
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<TextBlock Text="{Binding RelativeSource={RelativeSource Mode=FindAncestor,AncestorType={x:Type DataGridRow}}, Path=Header}" HorizontalAlignment="Center" VerticalAlignment="Center"></TextBlock>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
<DataGridTextColumn Header="药品ID" Visibility="Collapsed" Binding="{Binding DrugID}" IsReadOnly="True" />
<DataGridTextColumn Header="药品名" Binding="{Binding DrugName}" IsReadOnly="True" />
<DataGridTextColumn Header="数量" Binding="{Binding count}" Width="50" IsReadOnly="False"/>
<DataGridTextColumn Header="单位" Binding="{Binding DrugUnitName}" IsReadOnly="True"/>
<DataGridTextColumn Header="零售价" Binding="{Binding RetailPrice}" Width="60" IsReadOnly="True"></DataGridTextColumn>
<DataGridTextColumn Header="零售金额" Binding="{Binding RetailMoney}" IsReadOnly="True"/>
<DataGridTextColumn Header="药品编号" Binding="{Binding DrugCode}" IsReadOnly="True"/>
<DataGridTextColumn Header="规格" Binding="{Binding Specification}" IsReadOnly="True" />
<DataGridTextColumn Header="库存数" Binding="{Binding InventoryCount}" Width="80" IsReadOnly="True"/>
<DataGridTextColumn Header="批号" Binding="{Binding Manufacturers}" Width="80" IsReadOnly="False" />
</DataGrid.Columns>
</DataGrid>
<!--导出按钮->
<Button Style="{StaticResource btnToolBar}" x:Name="btn_DaoChu" Width="80" Height="30" FontSize="15" Content="导出" FontFamily="华文新魏" FontWeight="Black" Click=" DaoChu()" ></Button>
2、 在后台定义一个导出的方法,创建一个Excel工作簿和工作表以及创建行和列,绑定数据。在此之前还要引入一个叫NOPI的插件,网上有下载。
代码如下:
public void DaoChu()
{
List<NeckDrugDetail> list = dgWareHouseDrug.ItemsSource as List<NeckDrugDetail>;
if (list.Count > 0)
{
//Excel表格的创建步骤
//第一步:创建Excel对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//第二步:创建Excel对象的工作簿
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
//第三步:Excel表头设置
//给sheet添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);//创建行
row1.CreateCell(0).SetCellValue("序号");
row1.CreateCell(1).SetCellValue("药品名");
row1.CreateCell(2).SetCellValue("数量");
row1.CreateCell(3).SetCellValue("单位");
row1.CreateCell(4).SetCellValue("零售价");
row1.CreateCell(5).SetCellValue("零售金额");
row1.CreateCell(6).SetCellValue("药品编号");
row1.CreateCell(7).SetCellValue("规格");
row1.CreateCell(8).SetCellValue("库存数");
row1.CreateCell(9).SetCellValue("批号");
//第四步:for循环给sheet的每行添加数据
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].DrugName);
row.CreateCell(2).SetCellValue(list[i].count);
row.CreateCell(3).SetCellValue(list[i].DrugUnitName);
row.CreateCell(4).SetCellValue(list[i].RetailPrice.ToString());
row.CreateCell(5).SetCellValue((double)list[i].RetailMoney);
row.CreateCell(6).SetCellValue(list[i].DrugCode);
row.CreateCell(7).SetCellValue(list[i].Specification);
row.CreateCell(8).SetCellValue(list[i].InventoryCount);
row.CreateCell(9).SetCellValue(list[i].PiHao);
}
//把Excel转化为文件流,输出
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "选择要保存的路径";
saveFileDialog.Filter = "Excel文件|*.xls|所有文件|*.*";
saveFileDialog.FileName = string.Empty;
saveFileDialog.FilterIndex = 1;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.DefaultExt = "xls";
saveFileDialog.CreatePrompt = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
FileStream BookStream = new FileStream(saveFileDialog.FileName.ToString() + ".xls", FileMode.Create, FileAccess.Write);//定义文件流
book.Write(BookStream);//将工作薄写入文件流
BookStream.Seek(0, SeekOrigin.Begin); //输出之前调用Seek(偏移量,游标位置)方法:获取文件流的长度
BookStream.Close();
}
else
{
MessageBox.Show("导出保存失败!", "提示", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
代码完成了,具体效果如下: