C#之WPF 的Excel导入导出

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);
                }
                 }
        }

代码完成了，具体效果如下：