中级-得心应手
前言
本章节中，您将学习了解 GcExcel 在相对复杂的场景中，需要掌握的功能。通过学习这些功能，您可以完成典型场景中常见需求。
数据处理
批量数据导出
报表生成
公式计算
对于基础内容，您可以参考初级-初窥门径学习 GcExcel 中，最基础的基本功能。
中级计划
下面我们将围绕上面提到的几个常见场景需求，介绍相关的GcExcel功能。
区域操作
在 Excel 中，单元格是通过 R1C1 的方式去定位。在 GcExcel 中您可以通过 IRange 获取特定的单元格。
在常见的场景中，难免需要对一片区域进行遍历，查找特定的单元格，设置特定的样式，公式等等操作。IRange 对于一片连续的矩形区域（如：A1:H5，操作一片区域）操作会很方便，但是对于遍历，你可能会产生一些疑问。
遍历区域
下面是如何对区域进行遍历的示例：
下面的代码，会打开一个名为 rangeDemo.xlsx 的 Excel 文件，之后获取"A3:Z20"的区域，并且以二维数组的方式遍历它。
在代码中，我们使用 IRange 的 getRowCount 和 getColumnCount 获取总的行数与列数，然后您可以通过 IRange 的 get 方法获取特定的单元格。
public void FetchRange() {
Workbook wb = new Workbook();
wb.open("resources/rangeDemo.xlsx");
IWorksheet sheet = wb.getWorksheets().get(0);
IRange range = sheet.getRange("A3:Z20");
for (int r = 0; r < range.getRowCount(); r++) {
for (int c = 0; c < range.getColumnCount(); c++) {
var cell = range.get(r,c);
// 基于 cell 处理单元格...
System.out.println(cell);
}
}
}遍历多个区域
对于区域（Range），在正常使用中，有时会出现一个 IRange 对象包含多个区域的情况，例如("A1:B2, C3:D4")。在这种情况下，就需要先获取Range 中的每一个区域，之后在对区域中的单元格进行遍历。
代码如下：
在下面的代码中，通过 range.getAreas()，把每一个区域获取到，之后再进行单元格的遍历。
public void FetchMultipleAreaRange() {
Workbook wb = new Workbook();
wb.open("resources/rangeDemo.xlsx");
IWorksheet sheet = wb.getWorksheets().get(0);
IRange range = sheet.getRange("A1:B3, C4:D6");
for (IRange area : range.getAreas()) {
System.out.println(area);
for (int r = 0; r < area.getRowCount(); r++) {
for (int c = 0; c < area.getColumnCount(); c++) {
var cell = area.get(r, c);
// 基于 cell 处理单元格...
}
}
}
}通过二维数组设值取值
可能你已经注意到，在GcExcel 中，遍历单元格显得有些笨拙，需要二重循环。其实，我们并不推荐您使用二重循环遍历一个区域。反复遍历多个单元格，会使代码的执行次数增多，性能降低。通常，在其他组件中，遍历单元格往往是需要取值或者设值。因此 GcExcel 提供了一种更为优雅的方式解决取值和设值的需求。
参考如下代码：
当您把数据组织成二维数组之后，就可以通过 IRange 的 setValue 直接将二维数组设置倒一片区域的每个单元格上。
public void SetGetArrary() {
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("A:F").setColumnWidth(15);
Object data = new Object[][]{
{"姓名", "城市", "生日", "眼睛颜色", "体重", "身高"},
{"李明", "北京", new GregorianCalendar(1968, 5, 8), "蓝色", 67, 165},
{"王丽", "上海", new GregorianCalendar(1972, 6, 3), "棕色", 62, 134},
{"张强", "广州", new GregorianCalendar(1964, 2, 2), "淡褐色", 72, 180},
{"刘娜", "深圳", new GregorianCalendar(1972, 7, 8), "蓝色", 66, 163},
{"陈伟", "成都", new GregorianCalendar(1986, 1, 2), "淡褐色", 76, 176},
{"杨洋", "重庆", new GregorianCalendar(1993, 1, 15), "棕色", 68, 145}
};
//将二维数组设置到 A1:F7
worksheet.getRange("A1:F7").setValue(data);
//以二维数组的形式从 A1:F7 上获取数据
Object result = worksheet.getRange("A1:B7").getValue();
//保存 Excel
workbook.save("output/SetRangeValue.xlsx");
}type=warning
因为 GcExcel 内部维护数据的模型也于二维数组相似，因此通过二维数组设值取值的方法，性能上优于遍历。
更多关于区域操作的例子，您可以参考区域操作。
数据处理
在数据处理的场景中，通常会有批量替换，删除空行，删除多余行（例如汇总行），结构化数据等数据处理的需求。为了满足这类需求，GcExcel提供了 Excel 的查询及替换功能。
查找及替换
替换
基础用法，您可以通过使用 IRange 上的 replace 来替换特定的字符串，replace 会把区域中所有符合的字符串进行替换。
代码如下：
public void ReplaceText(){
Workbook wb = new Workbook();
wb.open("resources/ReplaceExample.xlsx");
ReplaceOptions replaceOption = new ReplaceOptions();
replaceOption.setMatchCase(true);
replaceOption.setLookAt(LookAt.Whole);
IRange searchRange = wb.getActiveSheet().getRange("D3:G20");
searchRange.replace("java", "Java", replaceOption);
searchRange.replace("gcexcel", "GcExcel", replaceOption);
searchRange.replace("c", "C", replaceOption);
searchRange.replace("c++", "C++", replaceOption);
searchRange.replace("c#", "C#", replaceOption);
searchRange.replace("python", "Python", replaceOption);
wb.save("output/ReplaceText.xlsx");
}查找
对于查找则可以使用 IRange 上的 find 方法。与 Replace 不同 find 会返回找到的第一个区域，因此需要迭代遍历所有的结果进行处理。
下面的代码将给 A1:D5 中设置一些值，并且将所有的 marco 标记为红色：
public void FindAndColorText(){
// Initialize workbook Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
final String CorrectWord = "Macro";
worksheet.getRange("A1:D5").setValue(CorrectWord);
final String MisspelledWord = "marco";
worksheet.getRange("A2,C3,D1").setValue(MisspelledWord);
IRange range = null;
IRange searchRange = worksheet.getRange("A1:D5");
do {
range = searchRange.find("marco", range);
if (range != null)
{
range.getFont().setColor(Color.GetRed());
}
} while (range != null);
workbook.save("output/FindAndColorText.xlsx");
}以上为查找替换的基本用法，GcExcel 还支持按格式查找，以及查找选项。更多查找和替换的用法，请参考查找和替换Demo。
数据验证
除了区域操作以及查找替换，数据验证也是常见的Excel数据处理功能。通过对数据进行验证，可以将数据修正提前至数据输入时，避免数据的错误。
数据验证的详细用法，可以参考数据验证，以及数据检验Demo。
使用设置导入导出CSV
在初级学习计划中，我们了解到如何轻松的通过 GcExcel 把数据导出为 CSV。但是，很多情况下，导入导出 CSV 时附带有一些特定的需求。
GcExcel 提供了 CsvOpenOptions 和 CsvSaveOptions，分别用来在打开和保存时，来指定以下三个选项：
列分割符
行分隔符
转义字符
以打开为例，代码如下（保存与打开相似，但注意使用 CsvSaveOptions）：
// 创建 workbook
Workbook workbook = new Workbook(); //Open csv with more settings.
// 配置options
CsvOpenOptions options = new CsvOpenOptions();
// 列分割符
options.setColumnSeparator(",");
// 换行符
options.setRowSeparator("\r\n");
// 转义符
options.setCellSeparator('"');
// 应用options, 并打开csv
workbook.open("source.csv", options); 此外，GcExcel 还支持在导入时，对数据进行解析。这种在对数据进行格式转换或者数据处理时很有用。
具体示例可以参考读取Csv文件时自定义转换规则。
报表生成
图表
下面让我们看看报表生成的场景，在报表生成的场景中，数据可视化是一项非常常见的需求。通过使用表格、图表和透视表等元素，可以有效提升报告的可读性，并为报告的各个部分增添了丰富的内容。
在GcExcel中，拥有12大类，60种图表类型，供您使用。下面是一个饼图的例子：
public void PieCharts() {
// 创建 workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
// 准备图表数据
worksheet.getRange("A1:D4")
.setValue(new Object[][]{
{null, "Q1", "Q2", "Q3"},
{"手机", 1330, 2345, 3493},
{"电脑", 2032, 3632, 2197},
{"平板", 6233, 3270, 2030}});
worksheet.getRange("A:D").getColumns().autoFit();
// 添加饼图
IShape areaChartShape = worksheet.getShapes().
addChart(ChartType.Pie3D, 250, 20, 360, 230);
// 添加系列
areaChartShape.getChart().getSeriesCollection().
add(worksheet.getRange("A1:D4"), RowCol.Columns, true, true);
// 修改图表标题
areaChartShape.getChart().getChartTitle().
getTextFrame().getTextRange().getParagraphs()
.add("年销售记录");
// Saving workbook to Xlsx
workbook.save("output/PieChart.xlsx");
}
其他图表的示例，请参考：图表Demo。
条件格式
GcExcel 提供了功能强大的条件格式化功能，可以根据单元格的值对工作表中的行或列进行突出显示。
通过设置条件格式规则，当单元格的值符合设定的条件时，可以改变单元格的格式。例如，我们可以创建一个条件格式规则，当单元格的值低于90时，将该单元格或一组单元格设置为斜体字体样式。其他单元格将保持默认的通用格式。
在设计动态的报表模板时，条件格式非常有效。
公式进阶
在初级学习计划中，已经学习过公式的基础用法，但是在实际的使用中，公式在使用时更为复杂，也需要使用到一些更高级的公式方法。
这节将为您介绍一些进阶的公式用法。
数组公式
数组公式是一种公式，可以对单个单元格或单元格范围执行多次计算以显示一列或一列小计。
请参考以下示例代码，了解如何设置和获取数组公式，并且根据数组公式设置背景颜色。
public void ArrayFormula(){
// 创建 workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("E4:J5").setValue(new Object[][]{
{1, 2, 3},
{4, 5, 6}
});
worksheet.getRange("I6:J8").setValue(new Object[][]{
{2, 2},
{3, 3},
{4, 4}
});
//O P Q
//2 4 #N/A
//12 15 #N/A
//#N/A #N/A #N/A
worksheet.getRange("O9:Q11").setFormulaArray("=E4:G5*I6:J8");
// 判断 O9 是否有数组公式
if (worksheet.getRange("O9").getHasArray()) {
// 设置 O9 整个数组的内部颜色.
IRange currentarray = worksheet.getRange("O9").getCurrentArray();
currentarray.getInterior().setColor(Color.GetGreen());
}
// 保存为 Excel
workbook.save("output/UseArrayFormula.xlsx");
}表格公式
表格公式是对于表格的列头，以及表格区域的一种引用方式。与 Excel 相同，GcExcel 允许您通过列名及表格名引用表格中的数据。
请参考以下示例代码，了解如何在表格中使用表格公式，及在表格外使用表格公式。
public void TableFormula() {
// 创建工作薄
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("A:E").setColumnWidth(15);
worksheet.getRange("A1:E3").setValue(new Object[][]{
{"销售人员", "区域", "销售目标", "完成比例", "完成数量"},
{"小乔", "背部", 260, 0.10, null},
{"小明", "南部", 660, 0.15, null},
});
worksheet.getTables().add(worksheet.getRange("A1:E3"), true);
worksheet.getTables().get(0).setName("销售部门");
worksheet.getTables().get(0).getColumns().get("完成比例").getDataBodyRange().setNumberFormat("0%");
// 在表格中使用表格公式
worksheet.getTables().get(0).getColumns().get("完成数量").getDataBodyRange().setFormula("=[@完成比例]*[@销售目标]");
// 在表格外使用表格公式
worksheet.getRange("F2").setFormula("=SUM(销售部门[@销售目标])");
worksheet.getRange("G2").setFormula("=SUM(销售部门[[#Data],[销售目标]])");
worksheet.getRange("H2").setFormula("=SUM(销售部门[销售目标])");
worksheet.getRange("I2").setFormula("=SUM(销售部门[@完成比例], 销售部门[@完成数量])");
// 判断 F2:I2 是否有公式
for (int i = 5; i <= 8; i++) {
if (worksheet.getRange(1, i).getHasFormula()) {
worksheet.getRange(1, i).getInterior().setColor(Color.GetLightBlue());
}
}
// 保存为Excel
workbook.save("output/UseTableFormula.xlsx");
}动态数组
动态数组是将多个值（在一个数组中）返回到工作表上某个单元格区域的公式。因此，相邻单元格将根据单个输入的公式填充结果（计算数据）。这种行为称为 Spilling（溢出）,公式结果填充的范围被称之为 Spill Range（溢出范围）。溢出范围的操作符（#）可用于引用整个溢出范围。
GcExcel 对于需要在工作表中使用动态数组公式的用户，可以通过 IRange.setFormula2 方法在工作表中定义动态数组公式。它允许用户在不自动添加隐式交集运算符的情况下指定公式。要启用动态数组公式的使用，需要通过 setFormula2 方法设置指定 IRange 区域的公式。
下面是 filter 的一个示例，可以看到公式仅设置在 I4，但是返回的一组数据会在溢出到其他的格子中：
public void DynamicArray(){
// 创建 workbook
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);
sheet.setName("动态数组_过滤");
sheet.getRange("A1").setValue("过滤器功能根据您指定的标准过滤范围或数组。语法：过滤器（数组，include，[if_empty]）");
sheet.getRange("B3:E19").setValue(new Object[][] {
{ "地区", "销售代表", "产品", "销售量" },
{ "东部", "小明", "苹果", 6380 },
{ "西部", "李雷", "葡萄", 5619 },
{ "北部", "高阳", "梨子", 4565 },
{ "南部", "郑丹", "香蕉", 5323 },
{ "东部", "黄嘉琪", "苹果", 4394 },
{ "西部", "刘涛", "葡萄", 7195 },
{ "北部", "林小虎", "梨子", 5231 },
{ "南部", "杨光", "香蕉", 2427 },
{ "东部", "小明", "香蕉", 4213 },
{ "西部", "李雷", "梨子", 3239 },
{ "北部", "高阳", "葡萄", 6420 },
{ "南部", "郑丹", "苹果", 1310 },
{ "东部", "黄嘉琪", "香蕉", 6274 },
{ "西部", "刘涛", "梨子", 4894 },
{ "北部", "林小虎", "葡萄", 7580 },
{ "南部", "杨光", "苹果", 9814 }
});
sheet.getRange("G3:L4").setValue(new Object[][] { { "标准", "", "产品", "销售量", "", "总数:" }, { 5000, null, null, null, null, null } });
//给 I4 设置动态数组 FILTER
sheet.getRange("I4").setFormula2("=FILTER(D4:E19,E4:E19>G4,\"\")");
sheet.getRange("L4").setFormula2("=SUM(IF(E4:E19>G4,1,0))");
sheet.getRange("E4:E19,G4,J4:J12").setNumberFormat("#,##0");
//保存为 Excel
workbook.save("output/FilterFunction.xlsx");
}
type=warning
setFormula2 也支持普通的公式，如 sum，因此当您确定需要使用动态数组时，您可以使用 setFormula2 替代所有 setFormula。
相反，如果您确定在项目中不实用动态数组，则可以仅使用 setFormula。
更多的动态数组公式以及用法请参考动态数组Demo。
以流的方式导出文件
在大多数情况，GcExcel 导出 CSV 和 PDF 时，可能文件并不大，可以直接以字符流的方式读写文件。但在操作大文件的时候，文件流的读写则更有优势。此外，GcExcel 除了支持保存Excel，CSV 和 PDF，还支持导出 HTML 和图片。
下面是以文件流的方式导出HTML，您可以参考该示例导入导出其他文件。
public void ExportToHtml(){
// 创建一个 Zip 输出文件流
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream("output/SaveWorkbookToHtml.zip");
} catch (FileNotFoundException e) {
e.printStackTrace();
}
// 创建 workbook
Workbook workbook = new Workbook();
// 以文件流的方式打开 Excel文件
InputStream fileStream = Main.class.getClassLoader()
.getResourceAsStream(("resources/AreaInformation.xlsx"));
workbook.open(fileStream);
// 通过文件流保存文件
workbook.save(outputStream, SaveFileFormat.Html);
// 关闭输出文件流
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}