用于将 Excel 数据解析为平面集合的 Silverlight 控件
4.31/5 (4投票s)
引言
很多时候需要将Excel文件中的数据导入数据库，或者仅仅是将Excel数据解析成内存中的一个类型化集合，以便后续使用。在这里，我将介绍一个我编写的Silverlight用户控件，它应该能简化开发者在类似场景下的工作。
这个控件的主要用途是帮助我快速构建Excel数据导入Web应用程序的功能，我将从这个角度来解释它的工作原理。
背景
在我的工作中，我经常需要在Web应用程序中提供将Excel文件中的数据上传到数据库（主要是SQL Server）的功能。其中一个常见痛点是Excel数据的标准化非常困难，特别是当Excel文件格式不受我们控制，并且数据是由其他人整理的时候。诸如列标题中的拼写错误、名字的随意缩写（例如“Option”缩写为“opt”）、列顺序不固定等问题，在解决这类问题时都非常普遍。
16世纪以来的一种做法是，在服务器上安装JET ODBC引擎和Excel软件包，将文件物理上传到服务器，然后使用JET读取文件并将其中的数据导入数据库。不用说，这是一种老旧的方法，并且不再受Microsoft支持，服务器管理员不喜欢在服务器上安装Excel这样的软件包，最重要的是它扩展性不强。
另一种方法是使用SSIS（SQL Server Integration Service）并设置导入包，这可以做一些ETL方面的事情，但这就要求用户（不一定是技术人员）知道如何设置和配置它们。他们想要的是简单易用的东西，让他们能够导入Excel文件而无需了解太多技术。
因此，我编写这个控件正是为了解决这个问题，如前所述，它能帮助我快速构建Excel数据导入功能。也可能存在其他用例，例如将Excel数据解析成类型化集合进行处理，而不是纠结于字符串。让我们看看...
工作流
假设我的Excel文件看起来像这样，我想将其导入数据库
我将上面的Excel数据复制并粘贴到如下所示的文本框中
点击“Next”，然后在右侧的下拉列表中选择一个值，它代表左侧预期的列名
最后，我点击“Import”，粘贴的数据就被解析成一个扁平化的列值集合，可以被客户端代码使用了。
注意：在这里需要注意的是，映射屏幕中的下拉列表包含了Excel列标题，其顺序与上面Excel文件中的顺序相同。因此，通过在下拉列表中选择一个值，并读取其 SelectedIndex 属性，我实际上是选择了Excel数据中列的索引。这是“复制、粘贴、映射和导入”整个概念的关键。
架构
该控件的结构如下所示
图例
TDTP：TabDelimitedTextParser（实际控件，包含两个面板，一个用于将Excel的原始数据接收到文本框中，另一个用于根据客户端代码发送的预期列生成列映射屏幕）
CCM：ComboboxToColumnMapping（一个辅助类，包含一个 Dictionary<int, string>，它将下拉列表中数字后缀（例如 cmb1, cmb2, cmb3）存储为键，并将预期的列名存储为值。这用于跟踪左侧的哪个列与右侧下拉列表中的选择相对应。这比存储整个组合框的名称要容易得多。而且由于这些下拉列表是在运行时由系统生成的，名称都在我的控制之下，所以我可以自由地修改它们，而不必担心其他人更改名称而破坏此跟踪功能）
public class ComboboxToColumnMapping
{
private Dictionary<int, string> comboBoxNumberToExpectedColumnMappings;
public ComboboxToColumnMapping()
{
comboBoxNumberToExpectedColumnMappings = new Dictionary<int, string>();
}
public void MapComboboxNumberToExpectedColumn(int comboBoxNumberSuffix, string expectedColumn)
{
if (!comboBoxNumberToExpectedColumnMappings.ContainsKey(comboBoxNumberSuffix))
{
comboBoxNumberToExpectedColumnMappings.Add(comboBoxNumberSuffix, expectedColumn);
}
}
public string GetExpectedColumnComboboxIsMappedTo(int comboBoxNumberSuffix)
{
string expectedColumn = String.Empty;
if (comboBoxNumberToExpectedColumnMappings.ContainsKey(comboBoxNumberSuffix))
{
expectedColumn = comboBoxNumberToExpectedColumnMappings[comboBoxNumberSuffix];
}
return expectedColumn;
}
}
CIM：ColumnToIndexMapping（一个辅助类，包含一个 Dictionary<string, int>，它将预期的列名存储为键，并将下拉列表中的 SelectedIndex 值存储为值。这实际上是将左侧的预期列映射到右侧下拉列表中可用的列）
public class ColumnToIndexMapping
{
private Dictionary<string, int> expectedColumnToIndexMappings;
public ColumnToIndexMapping()
{
expectedColumnToIndexMappings = new Dictionary<string, int>();
}
public void SeedColumnToIndexMappingTable(List<string> expectedColumns)
{
for (int i = 0; i < expectedColumns.Count; i++)
{
//bug fix: check to prevent duplicate keys. 5-Apr-2014
if (!expectedColumnToIndexMappings.ContainsKey(expectedColumns[i]))
{
expectedColumnToIndexMappings.Add(expectedColumns[i], -1);
}
}
}
public void MapColumnToIndex(string columnName, int selectedIndex)
{
if (expectedColumnToIndexMappings.ContainsKey(columnName))
{
expectedColumnToIndexMappings[columnName] = selectedIndex;
}
}
public int GetMappedColumnIndex(string columnName)
{
int index = -1;
if (expectedColumnToIndexMappings.ContainsKey(columnName))
{
index = expectedColumnToIndexMappings[columnName];
}
return index;
}
}
FCB：FlatCollectionBuilder（输出构建器）
public class FlatCollectionBuilder
{
public static Dictionary<string, List<string>> PrepareFlatColumnValuesCollection(string rawPastedData, List<string> expectedColumns, ColumnToIndexMapping columnToIndexMapping)
{
ImportParser importDataParser = new ImportParser(rawPastedData);
string[] allRows = importDataParser.GetRows();
Dictionary<string, List<string>> flatColumnValues = new Dictionary<string, List<string>>();
foreach (string expectedColumn in expectedColumns)
{
List<string> valuesForThisColumn = new List<string>();
int expectedColumnIndex = columnToIndexMapping.GetMappedColumnIndex(expectedColumn);
//don't include the ones that have been left on their default selections
if (expectedColumnIndex >= 0)
{
for (int i = 1; i < allRows.Length; i++)
{
string[] columnsInThisRow = importDataParser.GetColumns(allRows[i]);
valuesForThisColumn.Add(columnsInThisRow[expectedColumnIndex]);
}
flatColumnValues.Add(expectedColumn, valuesForThisColumn);
}
}
return flatColumnValues;
}
}
DDE（未显示）：DelimitedDataExtractor（辅助工具，用于使用 String 类的 Split(...) 方法将制表符和换行符分隔的Excel数据解析为字符串数组。实际的分隔功能封装在另一个名为ToolsNUtils.AgLight的DLL中，并随源代码一起提供）
public class DelimitedDataExtractor
{
public static List<string> ExtractColumnsFromPastedData(string rawPastedData)
{
ImportParser importDataParser = new ImportParser(rawPastedData);
string[] allRows = importDataParser.GetRows();
string[] allColumns = importDataParser.GetColumns(allRows[0]);
List<string> columnsInPastedData = allColumns.Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => x).ToList();
columnsInPastedData.Insert(columnsInPastedData.Count, Resource.ResourceManager.GetString("PleaseSelect"));
return columnsInPastedData;
}
}
此控件的输出是一个扁平化的1维列值表，即 Dictionary<string, List<string>>，看起来像这样
每个键代表一个列名，关联的值即 List 代表该列的值列表。不同键下的值之间存在严格的一对一对应关系，并代表Excel数据的一行，例如在上面的图片中，John的姓是Gostick，出生日期是1982年10月11日；同样，Mike的姓是Bennett，出生日期是1983年11月12日。
然后，这些数据就可以转换为类型化集合，例如 ObservableCollection<Student>，并导入数据库。
使此控件真正灵活的最重要的一点是，下拉映射屏幕是完全基于发送给它的预期列列表在运行时生成的。这些预期列形成左侧的标签（列名），而粘贴到此控件中的Excel数据的标题则填充到右侧的下拉列表中。
使用代码
此控件可以托管在Silverlight子窗口、另一个用户控件或页面等地方。客户端代码需要传递一个 List<string>（它期望在扁平化集合中看到的预期列列表）和一个 Action<Dictionary<string, List<string>>>（在输出准备好后执行），如下图所示，该控件托管在Silverlight ChildWindow 中。
宿主 ChildWindow: 的XAML标记：
<controls:ChildWindow x:Class="SimpleTabDelimitedDataImporter.Popups.ImportPopup"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:controls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"
Width="Auto" Height="Auto"
xmlns:ImportControls="clr-namespace:SimpleTabDelimitedDataImporter.Controls" Loaded="ChildWindow_Loaded">
<Grid x:Name="LayoutRoot" Margin="2">
<Grid.RowDefinitions>
<RowDefinition Height="Auto" />
</Grid.RowDefinitions>
<ImportControls:TabDelimitedTextParser x:Name="copyPasteImporter" Grid.Row="0"></ImportControls:TabDelimitedTextParser>
</Grid>
</controls:ChildWindow>
弹出窗口的代码隐藏
public partial class ImportPopup : ChildWindow
{
private List<string> expectedColumns;
private Action<Dictionary<string, List<string>>> columnValuesReadyCallback;
public ImportPopup()
{
InitializeComponent();
expectedColumns = new List<string>();
}
public ImportPopup(List<string> expectedColumns, Action<Dictionary<string, List<string>>> columnValuesReadyCallback)
{
InitializeComponent();
if (expectedColumns != null)
{
this.expectedColumns = expectedColumns;
}
else
{
this.expectedColumns = new List<string>();
}
this.columnValuesReadyCallback = columnValuesReadyCallback;
}
private void ChildWindow_Loaded(object sender, RoutedEventArgs e)
{
SetLocalisedWindowTitle();
copyPasteImporter.ExpectedColumns = this.expectedColumns;
copyPasteImporter.ColumnValuesReadyCallback = this.columnValuesReadyCallback;
}
private void SetLocalisedWindowTitle()
{
this.Title = Resource.ResourceManager.GetString("ImportPopup");
}
}
正如所见，我准备了一个我希望将Excel列映射到的列列表（即期望看到的列），定义了一个我希望在输出准备好后执行的 Action，最后将这些输入参数加载到弹出窗口中。
public void ImportData()
{
List<string> expectedcolumns = new List<string>();
expectedcolumns.Add(Helpers.Constants.FIRSTNAME);
expectedcolumns.Add(Helpers.Constants.LASTNAME);
expectedcolumns.Add(Helpers.Constants.DATEOFBIRTH);
importPopup = new Popups.ImportPopup(expectedcolumns, new Action<Dictionary<string, List<string>>>(ColumnValuesReady));
importPopup.Show();
}
private void ColumnValuesReady(Dictionary<string, List<string>> columnValues)
{
if (columnValues != null && columnValues.Count > 0)
{
importPopup.DialogResult = true;
ImportStudentData(columnValues);
}
}
当结果准备就绪时，会执行 ColumnValuesReady 回调函数。正如你所见，我选择了将此数据导入数据库。但是，如何将这种扁平化的1维数据导入数据库表呢？嗯，我首先将其转换为一个代表表结构的类型化集合，如下所示。
private ObservableCollection<Services.StudentDTO> ConvertToDTOs(Dictionary<string, List<string>> flatColumnValues)
{
ObservableCollection<Services.StudentDTO> studentsToBeImported = new ObservableCollection<Services.StudentDTO>();
//parsing logic. Since the number of values under each key would always be the same (Excel data would be x rows and y columns
//but the number of cells under each column would always be same, you cannot select jagged data. With notepad its possible
//so would need to handle that case)
//get the iteration count from under any key here i have used the first key
int numberOfStudents = flatColumnValues[Helpers.Constants.FIRSTNAME].Count;
for (int i = 0; i < numberOfStudents; i++)
{
Services.StudentDTO student = new Services.StudentDTO();
student.Id = Guid.NewGuid();
student.FirstName = flatColumnValues[Helpers.Constants.FIRSTNAME][i];
student.LastName = flatColumnValues[Helpers.Constants.LASTNAME][i];
student.DateOfBirth = DateTime.Parse(flatColumnValues[Helpers.Constants.DATEOFBIRTH][i]);
studentsToBeImported.Add(student);
}
return studentsToBeImported;
}
这里的绝妙之处在于，由于不同列下的值是一对一对应的，我可以轻松地找出需要导入多少个学生记录，并将其作为迭代限制 numberOfStudents。
因此，通过执行
int numberOfStudents = flatColumnValues[Helpers.Constants.FIRSTNAME].Count;
我可以找出学生名字的数量，这表明了学生的总数（一个学生只会有一个名字）。我也可以选择 LASTNAME 或 DATEOFBIRTH，结果会一样，都是3（看看Excel数据的截图，有3个学生我要导入数据库）。
关注点
虽然这基本上完成了我想要它做的工作，但我确信它远非完美，这就是为什么在这样的网站上分享很有帮助，因为有人可能会发现bug和/或修复它，或者告诉我。我想要进一步研究的一个案例是如何处理关联对象，例如客户-订单数据是一个二维数据结构，但这个控件只返回一维结构，并且所有都是字符串类型，所以也许客户端代码需要自己进行数据重塑，这听起来并不过分。控件只是做了它被设计来做的事情，不多也不少。
请随时在下面的评论区提问或发表评论、批评，并随意使用这些代码。希望如果您发现自己不时地遇到类似问题，您会觉得它有用。