使用 C# 和 OpenXML 创建带有预填充下拉列表的 ExcelSheet。
4.56/5 (7投票s)
创建 ExcelSheet，其中包含来自自身 excelsheet 的预填充下拉数据的下拉列表。
引言
通过代码处理 Excel 时，我们可能会遇到需要创建包含下拉列表的数据 Excel 表格的情况。在 Excel 术语中，我们称之为数据验证。在本文中，我们将看到如何借助 OpenXML 创建一个 Excel 表格，并且该 Excel 表格在任何特定单元格上都包含我们需要的下拉列表。该下拉列表的数据可以是任何硬编码数据或任何动态数据。我们将使用同一 Excel 文件的另一个工作表的动态数据填充下拉列表。
使用代码
我们将创建一个控制台应用程序来演示这个例子。
步骤
1. 使用 Visual Studio，创建一个控制台应用程序(文件 -> 新建 -> 项目 -> 控制台应用程序(来自 Visual C# 模板)) 并将其命名为 CreateDropDownInExcel.
2. 从 Nuget 包管理器安装 OpenXML。转到工具 -> Nuget 包管理器 -> 管理解决方案的 Nuget 包。
3. 在搜索栏中搜索 OpenXml。单击 DocumentFormat.OpenXml 的安装选项。
现在添加一个类 DataInSheet.cs。此类用于为创建的 Excel 文件的两个工作表创建数据。
using System;
using System.Collections.Generic;
namespace CreateDropDownInExcel
{
public class DataInSheet
{
public string firstRow { get; set; }
public string secondRow { get; set; }
public string thirdRow { get; set; }
public string fourthRow { get; set; }
public static List<DataInSheet> GetDataOfSheet1()
{
List<DataInSheet> dataForSheet = new List<DataInSheet>
{
new DataInSheet
{
firstRow = "CONDITION",
secondRow = "CONDITION",
thirdRow = "CONDITION",
fourthRow = "Assingment"
},
new DataInSheet
{
firstRow = "Num1.Char1",
secondRow = "List2",
thirdRow = "Size",
fourthRow = "BikeFrames"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "38",
fourthRow = "FR-M94S-34"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "36",
fourthRow = "FR-M94S-38"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "40",
fourthRow = "FR-M94S-31"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "38",
fourthRow = "FR-M94S-37"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "38",
fourthRow = "FR-M94S-37"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "57",
fourthRow = "FR-M94S-45"
},
};
return dataForSheet;
}
public static List<DataInSheet> GetDataOfSheet2()
{
List<DataInSheet> dataForSecondSheet = new List<DataInSheet>
{
new DataInSheet
{
firstRow = "Roshan",
secondRow = "Rahul",
thirdRow = "gautam",
fourthRow = "Sudripto"
},
new DataInSheet
{
firstRow = "Anand",
secondRow = "Gourav",
thirdRow = "Josep",
fourthRow = "Mathew"
},
new DataInSheet
{
firstRow = "Mohit",
secondRow = "Vimal",
thirdRow = "Sumitra",
fourthRow = "Hamid"
},
new DataInSheet
{
firstRow = "Uma",
secondRow = "Maity",
thirdRow = "Shubh",
fourthRow = "Raja"
},
new DataInSheet
{
firstRow = "Dinesh",
secondRow = "Narang",
thirdRow = "Jonathan",
fourthRow = "Andre"
},
new DataInSheet
{
firstRow = "Kawie",
secondRow = "Tom",
thirdRow = "Nies",
fourthRow = "FR-M94S-37"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "38",
fourthRow = "FR-M94S-37"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "57",
fourthRow = "FR-M94S-45"
},
};
return dataForSecondSheet;
}
}
}
您可以根据自己的选择创建数据。
创建另一个名为 ExcelOperations.cs 的类。此类将包含创建 Excel 和 Excel 工作表中下拉列表的所有业务逻辑。我们将简要讨论这段代码。
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CreateDropDownInExcel
{
class ExcelOprations
{
public static void CreatingExcelAndDrowownInExcel()
{
var filepath = @"D:\Projects\Testing.xlsx";
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = myWorkbook.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart2.Worksheet = new Worksheet(new SheetData());
Sheets sheets = myWorkbook.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Worksheet worksheet1 = new Worksheet()
{ MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet1.AddNamespaceDeclaration
("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet1.AddNamespaceDeclaration
("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet1.AddNamespaceDeclaration
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Worksheet worksheet2 = new Worksheet()
{ MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet2.AddNamespaceDeclaration
("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet2.AddNamespaceDeclaration
("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet2.AddNamespaceDeclaration
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Sheet sheet = new Sheet()
{
Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DropDownContainingSheet"
};
Sheet sheet1 = new Sheet()
{
Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "DropDownDataContainingSheet"
};
sheets.Append(sheet);
sheets.Append(sheet1);
SheetData sheetData = new SheetData();
SheetData sheetData1 = new SheetData();
int Counter1 = 1;
foreach (var value in DataInSheet.GetDataOfSheet1())
{
Row contentRow = CreateRowValues(Counter1, value);
Counter1++;
sheetData.AppendChild(contentRow);
}
worksheet1.Append(sheetData);
int Counter2 = 1;
foreach (var value in DataInSheet.GetDataOfSheet2())
{
Row contentRow = CreateRowValues(Counter2, value);
Counter2++;
sheetData1.AppendChild(contentRow);
}
worksheet2.Append(sheetData1);
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
Formula1 = new Formula1("'DropDownDataContainingSheet'!$A$1:$A$3")
};
DataValidations dataValidations = worksheet1.GetFirstChild<DataValidations>();
if (dataValidations != null)
{
dataValidations.Count = dataValidations.Count + 1;
dataValidations.Append(dataValidation);
}
else
{
DataValidations newdataValidations = new DataValidations();
newdataValidations.Append(dataValidation);
newdataValidations.Count = 1;
worksheet1.Append(newdataValidations);
}
worksheetPart.Worksheet = worksheet1; ;
worksheetPart2.Worksheet = worksheet2;
workbookpart.Workbook.Save();
myWorkbook.Close();
}
static string[] headerColumns = new string[] { "A", "B", "C", "D" };
private static Row CreateRowValues(int index, DataInSheet objToInsert)
{
Row row = new Row();
row.RowIndex = (UInt32)index;
int i = 0;
foreach (var property in objToInsert.GetType().GetProperties())
{
Cell cell = new Cell();
cell.CellReference = headerColumns[i].ToString() + index;
if (property.PropertyType.ToString()
.Equals("System.string", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "";
}
cell.DataType = CellValues.String;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
if (property.PropertyType.ToString()
.Equals("System.int32", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = 0;
}
CellValue cellValue = new CellValue();
cellValue.Text = result.ToString();
cell.AppendChild(cellValue);
}
if (property.PropertyType.ToString()
.Equals("System.boolean", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "False";
}
cell.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
row.AppendChild(cell);
i = i + 1;
}
return row;
}
}
}
以上是创建包含两个工作表的 Excel 文件的代码。
var filepath = @"D:\Projects\Testing.xlsx";
这是将在您的机器上创建 Excel 文件的路径。您可以根据需要更改它。
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
注意：SpreadsheetDocumnet 类需要添加 windowsBase dll 引用。否则会给出构建错误。
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = myWorkbook.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart2.Worksheet = new Worksheet(new SheetData());
Sheets sheets = myWorkbook.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Worksheet worksheet1 = new Worksheet()
{ MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet1.AddNamespaceDeclaration
("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet1.AddNamespaceDeclaration
("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet1.AddNamespaceDeclaration
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Worksheet worksheet2 = new Worksheet()
{ MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet2.AddNamespaceDeclaration
("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet2.AddNamespaceDeclaration
("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet2.AddNamespaceDeclaration
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
上面编写的代码首先是创建 Excel 工作表，向其添加 workbookpart，并将 worksheet 添加到 workbook。在这里我们将创建两个工作表，因此我们将有两个 worksheet 对象添加到 worksheetpart。
创建 Sheets 对象并将 sheets sheet 和 sheet1 都附加到它。
Sheet sheet = new Sheet()
{
Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DropDownContainingSheet"
};
Sheet sheet1 = new Sheet()
{
Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "DropDownDataContainingSheet"
};
sheets.Append(sheet);
sheets.Append(sheet1);
在这里，sheet 类的 name 属性用于指定您要分配给将要创建的工作表的名称。
到目前为止，两个工作表都已创建。是时候将数据添加到工作表中了。现在我们将调用 GetDataOfSheet1 和 GetDataOfSheet2 方法，我们之前在 DataInSheet 类中创建了这两个方法，以使用数据填充两个工作表。
int Counter1 = 1;
foreach (var value in DataInSheet.GetDataOfSheet1())
{
Row contentRow = CreateRowValues(Counter1, value);
Counter1++;
sheetData.AppendChild(contentRow);
}
worksheet1.Append(sheetData);
int Counter2 = 1;
foreach (var value in DataInSheet.GetDataOfSheet2())
{
Row contentRow = CreateRowValues(Counter2, value);
Counter2++;
sheetData1.AppendChild(contentRow);
}
worksheet2.Append(sheetData1);
通过调用 getDataOfSheet1 和 getDataOfSheet2 方法，我们只会得到将填充到工作表中的数据，但将数据绑定到工作表将通过 CreateRowValues 方法完成，该方法尚未出现。此方法如下所示
static string[] headerColumns = new string[] { "A", "B", "C", "D" };
private static Row CreateRowValues(int index, DataInSheet objToInsert)
{
Row row = new Row();
row.RowIndex = (UInt32)index;
int i = 0;
foreach (var property in objToInsert.GetType().GetProperties())
{
Cell cell = new Cell();
cell.CellReference = headerColumns[i].ToString() + index;
if (property.PropertyType.ToString()
.Equals("System.string", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "";
}
cell.DataType = CellValues.String;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
if (property.PropertyType.ToString()
.Equals("System.int32", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = 0;
}
CellValue cellValue = new CellValue();
cellValue.Text = result.ToString();
cell.AppendChild(cellValue);
}
if (property.PropertyType.ToString()
.Equals("System.boolean", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "False";
}
cell.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
row.AppendChild(cell);
i = i + 1;
}
return row;
}
此方法什么都不做，只是将数据添加到工作表的单元格中。乍一看，此方法可能看起来很复杂，但它没什么，只是对所有类型的数据进行验证，无论数据是字符串类型、int 类型还是布尔类型。我们可以根据需要向其添加更多验证以处理更多数据类型。这只是使此方法看起来很复杂。
将数据添加到单元格后，将数据附加到 Row 并返回 Row。该 Row 将附加到 sheetdata，并且该 sheetdata 将附加到 worksheet 对象。
现在，我们将看到负责在工作表中创建下拉列表的代码。
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
Formula1 = new Formula1("'DropDownDataContainingSheet'!$A$1:$A$3")
};
DataValidations dataValidations = worksheet1.GetFirstChild<DataValidations>();
if (dataValidations != null)
{
dataValidations.Count = dataValidations.Count + 1;
dataValidations.Append(dataValidation);
}
else
{
DataValidations newdataValidations = new DataValidations();
newdataValidations.Append(dataValidation);
newdataValidations.Count = 1;
worksheet1.Append(newdataValidations);
}
worksheetPart.Worksheet = worksheet1; ;
worksheetPart2.Worksheet = worksheet2;
workbookpart.Workbook.Save();
myWorkbook.Close();
在此，Datavalidation 类的 type 属性是 list 类型，它告诉它创建下拉列表。它可以是日期类型、十进制类型等。sequenceOfRefrence 属性告诉您下拉列表将出现在哪个单元格上。在这里，它将出现在 B1 单元格上。Formula type 属性告诉您需要将来自另一个工作表的数据从哪里填充到下拉列表中。在这里，它将从单元格 A1 填充到 A3。
最后，两个 worksheet 都将添加到它们各自的 worksheetpart。保存后，myWorkbook 将被关闭。
在 Program 类的 main 方法中调用 ExcelOprations.cs 类的 CreatingExcelAndDrowownInExcel 方法，这是我们应用程序的入口点。
class Program
{
static void Main(string[] args)
{
ExcelOprations.CreatingExcelAndDrowownInExcel();
}
}
因此，我们的 Excel 表格最终准备好了，如下所示。
这是 colum A1 到 A3 的数据，将从 sheet1 填充到名为 DropDownDataContainingSheet 的 Sheet2 的下拉列表中。
这是 colum B1 上的下拉列表。
关注点
请确保在引用部分中添加 windowsBase dll。