x01.ExcelHelper: NPOI 操作_ZenDei技術網路在線

Excel 操作，具有十分明顯的針對性，故很難通用，但這並不妨礙參考後以解決自己的實際問題。有一彙總表如下：當然，只是示範，產品的代碼應該唯一！現在要根據此彙總表產生各個客戶的產品清單。由於客戶較多，彙總分表1，表2；客戶清單模板根據產品類別，每個清單又分為三個表。做個模板，然後手工複製粘貼，完 ...

Excel 操作，具有十分明顯的針對性，故很難通用，但這並不妨礙參考後以解決自己的實際問題。

有一彙總表如下：

當然，只是示範，產品的代碼應該唯一！現在要根據此彙總表產生各個客戶的產品清單。由於客戶較多，彙總分表1，表2；客戶清單模板根據產品類別，每個清單又分為三個表。做個模板，然後手工複製粘貼，完成需要小半天的時間。還是寫個程式來幫幫忙吧。

首先，是下載 NPOI 庫及參考手冊，花個10來分鐘粗略看看，然後開工。主要代碼如下：

<Window x:Class="x01.ExcelHelper.SplitWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    WindowStartupLocation="CenterScreen"
    Title="x01.SplitWindow" Height="310" Width="480">
        <Window.Resources>
        <Style TargetType="TextBox" x:Key="ShortBoxKey">
            <Setter Property="MinWidth" Value="20" />
            <Setter Property="Margin" Value="5" />
        </Style>
        <Style TargetType="TextBox">
            <Setter Property="Height" Value="20" />
        </Style>
        <Style TargetType="Button">
            <Setter Property="Margin" Value="5" />
            <Setter Property="Height" Value="20" />
        </Style>
        <Style TargetType="TextBlock">
            <Setter Property="Margin" Value="0 5 0 0" />
            <Setter Property="Height" Value="20" />
        </Style>
    </Window.Resources>
    <Grid Margin="5 10 5 5">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto" />
            <RowDefinition Height="Auto" />
            <RowDefinition Height="Auto" />
            <RowDefinition Height="Auto" />
            <RowDefinition Height="Auto" />
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="Auto" />
            <ColumnDefinition Width="*" />
            <ColumnDefinition Width="Auto" />
        </Grid.ColumnDefinitions>
        <TextBlock Grid.Row="0" Grid.Column="0" HorizontalAlignment="Right">原始文件:</TextBlock>
        <TextBox Grid.Row="0" Grid.Column="1" Name="tbxOriginPath" /> 
        <Button Grid.Row="0" Grid.Column="2" Width="30" Name="OpenOriginButton"
            Click="OpenOriginButton_Click">…</Button>
        <StackPanel Grid.Row="1" Grid.ColumnSpan="3">
            <StackPanel Orientation="Horizontal">
                <TextBlock>原始表1: 表名</TextBlock>
                <TextBox Name="tbxOriginSheet1Name" MinWidth="40" Margin="5 0" />
                <TextBlock>起始行</TextBlock>
                <TextBox Name="tbxOriginSheet1StartRow" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束行</TextBlock>
                <TextBox Name="tbxOriginSheet1EndRow" Style="{StaticResource ShortBoxKey}" />    
                <TextBlock>起始列</TextBlock>
                <TextBox Name="tbxOriginSheet1StartCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束列</TextBlock>
                <TextBox Name="tbxOriginSheet1EndCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>代碼列</TextBlock>
                <TextBox Name="tbxOriginSheet1CodeCol" Style="{StaticResource ShortBoxKey}" />
            </StackPanel>
        <StackPanel Orientation="Horizontal">
                <TextBlock>原始表2: 表名</TextBlock>
                <TextBox Name="tbxOriginSheet2Name" MinWidth="40" Margin="5 0" />
                <TextBlock>起始行</TextBlock>
                <TextBox Name="tbxOriginSheet2StartRow" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束行</TextBlock>
                <TextBox Name="tbxOriginSheet2EndRow" Style="{StaticResource ShortBoxKey}" />    
                <TextBlock>起始列</TextBlock>
                <TextBox Name="tbxOriginSheet2StartCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束列</TextBlock>
                <TextBox Name="tbxOriginSheet2EndCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>代碼列</TextBlock>
                <TextBox Name="tbxOriginSheet2CodeCol" Style="{StaticResource ShortBoxKey}" />
            </StackPanel>
        </StackPanel>
        <TextBlock Grid.Row="2" Grid.Column="0" HorizontalAlignment="Right">模板文件:</TextBlock>
        <TextBox Grid.Row="2" Grid.Column="1" Name="tbxTemplatePath" />
        <Button Grid.Row="2" Grid.Column="2" Width="30" Name="OpenTemplateButton" 
                Click="OpenTemplateButton_Click">…</Button>
        <StackPanel Grid.Row="3" Grid.ColumnSpan="3">
            <StackPanel Orientation="Horizontal">
                <TextBlock>模板表1: 表名</TextBlock>
                <TextBox Name="tbxTemplateSheet1Name" MinWidth="40" Margin="5 0" />
                <TextBlock>起始行</TextBlock>
                <TextBox Name="tbxTemplateSheet1StartRow" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束行</TextBlock>
                <TextBox Name="tbxTemplateSheet1EndRow" Style="{StaticResource ShortBoxKey}" />    
                <TextBlock>起始列</TextBlock>
                <TextBox Name="tbxTemplateSheet1StartCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束列</TextBlock>
                <TextBox Name="tbxTemplateSheet1EndCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>代碼列</TextBlock>
                <TextBox Name="tbxTemplateSheet1CodeCol" Style="{StaticResource ShortBoxKey}" />
            </StackPanel>
            <StackPanel Orientation="Horizontal">
                <TextBlock>模板表2: 表名</TextBlock>
                <TextBox Name="tbxTemplateSheet2Name" MinWidth="40" Margin="5 0" />
                <TextBlock>起始行</TextBlock>
                <TextBox Name="tbxTemplateSheet2StartRow" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束行</TextBlock>
                <TextBox Name="tbxTemplateSheet2EndRow" Style="{StaticResource ShortBoxKey}" />    
                <TextBlock>起始列</TextBlock>
                <TextBox Name="tbxTemplateSheet2StartCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束列</TextBlock>
                <TextBox Name="tbxTemplateSheet2EndCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>代碼列</TextBlock>
                <TextBox Name="tbxTemplateSheet2CodeCol" Style="{StaticResource ShortBoxKey}" />
            </StackPanel>
            <StackPanel Orientation="Horizontal">
                <TextBlock>模板表3: 表名</TextBlock>
                <TextBox Name="tbxTemplateSheet3Name" MinWidth="40" Margin="5 0" />
                <TextBlock>起始行</TextBlock>
                <TextBox Name="tbxTemplateSheet3StartRow" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束行</TextBlock>
                <TextBox Name="tbxTemplateSheet3EndRow" Style="{StaticResource ShortBoxKey}" />    
                <TextBlock>起始列</TextBlock>
                <TextBox Name="tbxTemplateSheet3StartCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>結束列</TextBlock>
                <TextBox Name="tbxTemplateSheet3EndCol" Style="{StaticResource ShortBoxKey}" />
                <TextBlock>代碼列</TextBlock>
                <TextBox Name="tbxTemplateSheet3CodeCol" Style="{StaticResource ShortBoxKey}" />
            </StackPanel>
        </StackPanel>
        <Button Grid.Row="4" Grid.ColumnSpan="3" HorizontalAlignment="Right" 
                Name="GenerateFilesButton" Margin="0 10 5 0" Height="32"
                Click="GenerateFilesButton_Click">_Generate Files</Button>
    </Grid>
</Window>

SplitWindow.xaml

/**
 * SplitWindow.cs (c) 2017 by x01
 */
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;

using Microsoft.Win32;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace x01.ExcelHelper
{
    /// <summary>
    /// Interaction logic for SplitWindow.xaml
    /// </summary>
    public partial class SplitWindow : Window
    {
        #region Settings
        
        public string OriginPath
        {
            get {
                if (string.IsNullOrEmpty(tbxOriginPath.Text))
                    throw new Exception("請選擇原始文件！");
                return tbxOriginPath.Text;
            }
        }
        
        public string OriginSheet1Name
        {
            get {
                return tbxOriginSheet1Name.Text;
            }
        }
        public int OriginSheet1StartRow
        {
            get {
                int row;
                if (!int.TryParse(tbxOriginSheet1StartRow.Text, out row))
                    throw new Exception("請在表1起始行中填充正確的數字！");
                return row;
            }
        }
        public int OriginSheet1EndRow
        {
            get {
                int row;
                if (!int.TryParse(tbxOriginSheet1EndRow.Text, out row))
                    throw new Exception("請在表1結束行中填入正確的數字！");
                return row;
            }
        }
        public int OriginSheet1StartCol
        {
            get {
                int col;
                if (!int.TryParse(tbxOriginSheet1StartCol.Text, out col))
                    throw new Exception("請在表1起始列中填入正確的數字！");
                return col;
            }
        }
        public int OriginSheet1EndCol
        {
            get {
                int col;
                if (!int.TryParse(tbxOriginSheet1EndCol.Text, out col))
                    throw new Exception("請在表1結束列中填入正確的數字！");
                return col;
            }
        }
        public int OriginSheet1CodeCol
        {
            get {
                int col;
                if (!int.TryParse(tbxOriginSheet1CodeCol.Text, out col))
                    throw new Exception("請在表1代碼列中填入正確的數字！");
                return col;
            }
        }
        
        public string OriginSheet2Name
        {
            get {
                return tbxOriginSheet2Name.Text;
            }
        }
        public int OriginSheet2StartRow
        {
            get {
                int row;
                if (!int.TryParse(tbxOriginSheet2StartRow.Text, out row))
                    throw new Exception("請在表2起始行中填入正確的數字！");
                return row;
            }
        }
        public int OriginSheet2EndRow
        {
            get {
                int row;
                if (!int.TryParse(tbxOriginSheet2EndRow.Text, out row))
                    throw new Exception("請在表2結束行中填入正確的數字！");
                return row;
            }
        }
        public int OriginSheet2StartCol
        {
            get {
                int col;
                if (!int.TryParse(tbxOriginSheet2StartCol.Text, out col))
                    throw new Exception("請在表2開始列中填入正確的數字！");
                return col;
            }
        }
        public int OriginSheet2EndCol
        {
            get {
                int col;
                if (!int.TryParse(tbxOriginSheet2EndCol.Text, out col))
                    throw new Exception("請在表2結束列中填入正確的數字！");
                return col;
            }
        }
        public int OriginSheet2CodeCol
        {
            get {
                int col;
                if (!int.TryParse(tbxOriginSheet2CodeCol.Text, out col))
                    throw new Exception("請在表2代碼列中填入正確的數字！");
                return col;
            }
        }
        
        public string TemplatePath
        {
            get {
                if (string.IsNullOrEmpty(tbxTemplatePath.Text))
                    throw new Exception("請選擇模板文件！");
                return tbxTemplatePath.Text;
            }
        }
        
        public string TemplateSheet1Name
        {
            get {
                return tbxTemplateSheet1Name.Text;
            }
        }
        public int TemplateSheet1StartRow
        {
            get {
                int row;
                if (!int.TryParse(tbxTemplateSheet1StartRow.Text, out row))
                    throw new Exception("請在模板表1開始行中填入正確的數字！");
                return row;
            }
        }
        public int TemplateSheet1EndRow
        {
            get {
                int row;
                if (!int.TryParse(tbxTemplateSheet1EndRow.Text, out row))
                    throw new Exception("請在模板表1結束行中填入正確的數字！");
                return row;
            }
        }
        public int TemplateSheet1StartCol
        {
            get {
                int col;
                if (!int.TryParse(tbxTemplateSheet1StartCol.Text, out col))
                    throw new Exception("請在模板表1開始列中填入正確的數字！");
                return col;
            }
        }
        public int TemplateSheet1EndCol
        {
            get {
                int col;
                if (!int.TryParse(tbxTemplateSheet1EndCol.Text, out col))
                    throw new Exception("請在模板表1結束列中填入正確的數字！");
                return col;
            }
        }
        public int TemplateSheet1CodeCol
        {
            get {
                int col;
                if (!int.TryParse(tbxTemplateSheet1CodeCol.Text, out col))
                    throw new Exception("請在模板表1代碼列中填入正確的數字！");
                return col;
            }
        }
        
        public string TemplateSheet2Name
        {
            get {
                return tbxTemplateSheet2Name.Text;
            }
        }
        public int TemplateSheet2StartRow
        {
            get {
                int row;
                if (!int.TryParse(tbxTemplateSheet2StartRow.Text, out row))
                    throw new Exception("請在模板表2開始行中填入正確的數字！");
                return row;
            }
        }
        public int TemplateSheet2EndRow
        {
            get {
                int row;
                if (!int.TryParse(tbxTemplateSheet2EndRow.Text, out row))
                    throw new Exception("請在模板表2結束行中填入正確的數字！");
                return row;
            }
        }
        public int TemplateSheet2StartCol
        {
            get {
                int col;
                if (!int.TryParse(tbxTemplateSheet2StartCol.Text, out col))
                    throw new Exception("請在模板表2開始列中填入正確的數字！");
                return col;
            }
        }
        public int TemplateSheet2EndCol
        {
            get {
                int col;
                if (!int.TryParse(tbxTemplateSheet2EndCol.Text, out col))
                    throw new Exception("請在模板表2結束列中填入正確的數字！");
                return col;
            }
        }
        public int TemplateSheet2CodeCol
        {
            get {
                int col;
                if (!int.TryParse(tbxTemplateSheet2CodeCol.Text, out col))
                    throw new Exception("請在模板表2代碼列中填入正確的數字！");
                return col;
            }
        }
        
        public string TemplateSheet3Name
        {
            get {
                return tbxTemplateSheet3Name.Text;
            }
        }
        public int