首页 > 代码库 > x01.ExcelHelper: NPOI 操作
x01.ExcelHelper: NPOI 操作
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.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 TemplateSheet3StartRow { get { int row; if (!int.TryParse(tbxTemplateSheet3StartRow.Text, out row)) throw new Exception("请在模板表3开始行中填入正确的数字！"); return row; } } public int TemplateSheet3EndRow { get { int row; if (!int.TryParse(tbxTemplateSheet3EndRow.Text, out row)) throw new Exception("请在模板表3结束行中填入正确的数字！"); return row; } } public int TemplateSheet3StartCol { get { int col; if (!int.TryParse(tbxTemplateSheet3StartCol.Text, out col)) throw new Exception("请在模板表3开始列中填入正确的数字！"); return col; } } public int TemplateSheet3EndCol { get { int col; if (!int.TryParse(tbxTemplateSheet3EndCol.Text, out col)) throw new Exception("请在模板表3结束列中填入正确的数字！"); return col; } } public int TemplateSheet3CodeCol { get { int col; if (!int.TryParse(tbxTemplateSheet3CodeCol.Text, out col)) throw new Exception("请在模板表3代码列中填入正确的数字！"); return col; } } #endregion OpenFileDialog openDialog = new OpenFileDialog(); SaveFileDialog saveDialog = new SaveFileDialog(); public SplitWindow() { InitializeComponent(); openDialog.Filter = "Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"; saveDialog.Filter = "Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"; } void OpenTemplateButton_Click(object sender, RoutedEventArgs e) { if ((bool)openDialog.ShowDialog()) { tbxTemplatePath.Text = openDialog.FileName; } } void OpenOriginButton_Click(object sender, RoutedEventArgs e) { if ((bool)openDialog.ShowDialog()) { tbxOriginPath.Text = openDialog.FileName; } } void GenerateFilesButton_Click(object sender, RoutedEventArgs e) { var orgBook = CreateWorkook(OriginPath); var orgSheet1 = GetSheet(orgBook,OriginSheet1Name); var orgSheet2 = GetSheet(orgBook,OriginSheet2Name); var tempBook = CreateWorkook(TemplatePath); var tempSheet1 = GetSheet(tempBook,TemplateSheet1Name); var tempSheet2 = GetSheet(tempBook,TemplateSheet2Name); var tempSheet3 = GetSheet(tempBook, TemplateSheet3Name); if (orgSheet1 != null) { for (int j = OriginSheet1StartCol - 1; j < OriginSheet1EndCol; j++) { string name = orgSheet1.GetRow(OriginSheet1StartRow-1).GetCell(j).StringCellValue; GenerateTemplateSheet(ref orgSheet1, ref tempSheet1, j, name, OriginSheet1StartRow, OriginSheet1EndRow, OriginSheet1CodeCol, TemplateSheet1StartRow, TemplateSheet1EndRow, TemplateSheet1StartCol, TemplateSheet1EndCol, TemplateSheet1CodeCol); GenerateTemplateSheet(ref orgSheet1, ref tempSheet2, j, name, OriginSheet1StartRow, OriginSheet1EndRow, OriginSheet1CodeCol, TemplateSheet2StartRow, TemplateSheet2EndRow, TemplateSheet2StartCol, TemplateSheet2EndCol, TemplateSheet2CodeCol); GenerateTemplateSheet(ref orgSheet1, ref tempSheet3, j, name, OriginSheet1StartRow, OriginSheet1EndRow, OriginSheet1CodeCol, TemplateSheet3StartRow, TemplateSheet3EndRow, TemplateSheet3StartCol, TemplateSheet3EndCol, TemplateSheet3CodeCol); var fs = new FileStream(Path.Combine(Path.GetDirectoryName(TemplatePath),name+".xls"), FileMode.Create); tempBook.Write(fs); fs.Close(); tempBook = CreateWorkook(TemplatePath); tempSheet1 = GetSheet(tempBook, TemplateSheet1Name); tempSheet2 = GetSheet(tempBook, TemplateSheet2Name); tempSheet3 = GetSheet(tempBook, TemplateSheet3Name); } } if (orgSheet2 != null) { for (int j = OriginSheet2StartCol - 1; j < OriginSheet2EndCol; j++) { string name = orgSheet2.GetRow(OriginSheet1StartRow-1).GetCell(j).StringCellValue; GenerateTemplateSheet(ref orgSheet2, ref tempSheet1, j, name, OriginSheet2StartRow, OriginSheet2EndRow, OriginSheet2CodeCol, TemplateSheet1StartRow, TemplateSheet1EndRow, TemplateSheet1StartCol, TemplateSheet1EndCol, TemplateSheet1CodeCol); GenerateTemplateSheet(ref orgSheet2, ref tempSheet2, j, name, OriginSheet2StartRow, OriginSheet2EndRow, OriginSheet2CodeCol, TemplateSheet2StartRow, TemplateSheet2EndRow, TemplateSheet2StartCol, TemplateSheet2EndCol, TemplateSheet2CodeCol); GenerateTemplateSheet(ref orgSheet2, ref tempSheet3, j, name, OriginSheet2StartRow, OriginSheet2EndRow, OriginSheet2CodeCol, TemplateSheet3StartRow, TemplateSheet3EndRow, TemplateSheet3StartCol, TemplateSheet3EndCol, TemplateSheet3CodeCol); var fs = new FileStream(Path.Combine(Path.GetDirectoryName(TemplatePath),name+".xls"), FileMode.Create); tempBook.Write(fs); fs.Close(); tempBook = CreateWorkook(TemplatePath); tempSheet1 = GetSheet(tempBook, TemplateSheet1Name); tempSheet2 = GetSheet(tempBook, TemplateSheet2Name); tempSheet3 = GetSheet(tempBook, TemplateSheet3Name); } } MessageBox.Show("OK!"); } void GenerateTemplateSheet(ref ISheet orgSheet, ref ISheet tempSheet, int orgCol, string name, int orgStartRow, int orgEndRow, int orgCodeCol, int tempStartRow, int tempEndRow, int tempStartCol, int tempEndCol, int tempCodeCol) { for (int i = orgStartRow; i < orgEndRow; i++) { if (tempSheet != null) { for (int y = tempStartRow - 1; y < tempEndRow; y++) { for (int x = tempStartCol - 1; x < tempEndCol; x++) { if (tempSheet.GetRow(y).GetCell(tempCodeCol - 1).NumericCellValue == orgSheet.GetRow(i).GetCell(orgCodeCol - 1).NumericCellValue) { tempSheet.GetRow(y).GetCell(x).SetCellValue(orgSheet.GetRow(i).GetCell(orgCol).NumericCellValue); } } } } } for (int y = tempStartRow - 1; y < tempEndRow; y++) { if (tempSheet == null) break; tempSheet.GetRow(y).GetCell(tempCodeCol - 1).SetCellType(CellType.Blank); } } HSSFWorkbook CreateWorkook(string path) { var fs = new FileStream(path, FileMode.Open, FileAccess.Read); var book = new HSSFWorkbook(fs); fs.Close(); return book; } ISheet GetSheet(HSSFWorkbook book, string sheetName) { if (string.IsNullOrEmpty(sheetName)) return null; return book.GetSheet(sheetName); } }}
运行填充相关设置后，点击生成按钮，瞬间完成，OK！
x01.ExcelHelper: NPOI 操作
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。