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 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 - ; j < OriginSheet1EndCol; j++) {
string name = orgSheet1.GetRow(OriginSheet1StartRow-).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 - ; j < OriginSheet2EndCol; j++) {
string name = orgSheet2.GetRow(OriginSheet1StartRow-).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 - ; y < tempEndRow; y++) {
for (int x = tempStartCol - ; x < tempEndCol; x++) {
if (tempSheet.GetRow(y).GetCell(tempCodeCol - ).NumericCellValue
== orgSheet.GetRow(i).GetCell(orgCodeCol - ).NumericCellValue)
{
tempSheet.GetRow(y).GetCell(x).SetCellValue(orgSheet.GetRow(i).GetCell(orgCol).NumericCellValue);
}
}
}
}
}
for (int y = tempStartRow - ; y < tempEndRow; y++) {
if (tempSheet == null)
break;
tempSheet.GetRow(y).GetCell(tempCodeCol - ).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);
} }
}
SplitWindow.xaml.cs
运行填充相关设置后，点击生成按钮，瞬间完成，OK！
源代码下载：x01.ExcelHelper
'========================================================================
' FillAll (c) 2015 by x01
'------------------------
' 1.将系统销售汇总 sh1 各学校数字填入相应汇总表 sh2.
' 2.参数说明：
' sh1_r1,sh1_r2: 系统销售汇总开始行，结束行
' sh1_colCode: 征订代码列
' sh1_colID: 客户编号列
' sh1_colNumber: 销售数量列
' sh2_r1,sh2_r2,sh2_c1,sh2_c2: 汇总表开始行，结束行，开始列，结束列
' sh2_colCode: 征订代码列
' sh2_rowID: 汇总表客户编号所在行
' example: FillAll Sheet1,3,122,3,1,10,Sheet33,5,34,6,42,4,3
'========================================================================
Public Sub FillAll(sh1, sh1_r1, sh1_r2, sh1_colCode, sh1_colID, sh1_colNumber, sh2, sh2_r1, sh2_r2, sh2_c1, sh2_c2, sh2_colCode, sh2_rowID)
For i = sh1_r1 To sh1_r2
For j = sh2_r1 To sh2_r2
For k = sh2_c1 To sh2_c2
If sh1.Cells(i, sh1_colID) = sh2.Cells(sh2_rowID, k) Then
If sh1.Cells(i, sh1_colCode) = sh2.Cells(j, sh2_colCode) Then
sh2.Cells(j, k) = sh1.Cells(i, sh1_colNumber) + sh2.Cells(j, k)
End If
End If
Next
Next
Next Debug.Print "OK!" End Sub '========================================================================
' FillCol (c) 2015 by x01
'------------------------
' 1.如满足条件，则将 sh1 中相应项填充到 sh2 中，汇总专用。
' 2.条件及操作可根据实际作相应调整。
' 3.取消注释可进行累加。
' FillCol Sheet1,1,300,8,12,Sheet2,2,73,2,4
'========================================================================
Public Sub FillCol(sh1, sh1_r1, sh1_r2, sh1_c1, sh1_c2, sh2, sh2_r1, sh2_r2, sh2_c1, sh2_c2)
For i = sh1_r1 To sh1_r2
For m = sh2_r1 To sh2_r2
If Application.WorksheetFunction.IsNumber(sh2.Cells(m, sh2_c1)) Then
If sh1.Cells(i, sh1_c1) = sh2.Cells(m, sh2_c1) And sh1.Cells(i, sh1_c1) > Then
sh2.Cells(m, sh2_c2) = sh1.Cells(i, sh1_c2) + sh2.Cells(m, sh2_c2)
End If
End If
Next
Next Debug.Print "OK!" End Sub '=================================================================
' SumCol (c) 2014 by x01
'-----------------------
' 计算指定列的合计数，如数量合计，码洋合计等，用于手工表。
'
' 参数：
' sheet: 所选的表。
' col: 指定的列。t1为第7列
' startRow: 开始的行数。
' endRow: 结束的行数。
'=================================================================
Public Sub SumCol(sheet, col, startRow, endRow)
Dim result As Double
result = #
For i = startRow To endRow
If Application.WorksheetFunction.IsNumber(sheet.Cells(i, col)) Then
If Trim(sheet.Cells(i, )) = "合计" Then
'If 23# = sheet.Cells(i, 3) Then
result = result + sheet.Cells(i, col)
'End If
End If
End If
Next Debug.Print CStr(result)
End Sub '=====================================================================
' SubTotal (c) 2014 by x01
'-------------------------
' 求 (单价 * 册数) 的累加小合计
'=====================================================================
Public Sub SubTotal(sh, startRow, endRow, startCol, endCol, priceCol)
Dim s As Double outRow = endRow +
For c = startCol To endCol
sh.Cells(outRow, c) =
Next For c = startCol To endCol
s =
For r = startRow To endRow
If Application.WorksheetFunction.IsNumber(sh.Cells(r, c)) Then
s = s + sh.Cells(r, c) * sh.Cells(r, priceCol)
End If
Next
sh.Cells(outRow, c) = s
Next
Debug.Print "OK!"
End Sub '================================================================
' DelRow (c) 2014 by x01
'-----------------------
' 根据条件删除行。
' sheet: 所选的表。
' col: 指定的条件列，默认为 0 时删除整行。t1 为第7列
' startRow: 开始行。
' endRow: 结束行。
'================================================================
Public Sub DelRow(sheet, col, startRow, endRow)
For i = endRow To startRow Step -
If Application.WorksheetFunction.IsNumber(sheet.Cells(i, col)) And Trim(sheet.Cells(i, )) <> "合计" Then
'此条件可更改
If # = sheet.Cells(i, col) + # Then
Range("a" & CStr(i) & ":a" & CStr(i)).EntireRow.Delete
End If
End If
Next Debug.Print "OK!"
End Sub
备用宏