请问一下，我的代码需要将附件模板内的药品原料导入到数据表内，导入时需要将各原料的批号（有7位、8位，大于8位的）内的数字，分别解析为年、月和顺序号，以便后面用先进先出的原则，对入库早的原料优先出库，相关的导入代码如下，在运行中总提示导入字符的格式错误，要导入的模板表格，和数据表的设计如下图，请帮分析一下是哪里的问题；
```c#
//导入引用的数据库插入语句
private void SaveBatch(SqlConnection conn, SqlTransaction transaction, string batchNumber, int materialId, string materialName, decimal quantity)
{
//解析批号；
int year, month, sequence;
if (batchNumber.Length == 6)
{
year = int.Parse(batchNumber.Substring(0, 2));
month = int.Parse(batchNumber.Substring(2, 2));
sequence = int.Parse(batchNumber.Substring(4, 2));
}
else if (batchNumber.Length == 7)
{
year = int.Parse(batchNumber.Substring(0, 2));
month = int.Parse(batchNumber.Substring(2, 2));
sequence = int.Parse(batchNumber.Substring(4, 3));
}
else if (batchNumber.Length == 8)
{
year = int.Parse(batchNumber.Substring(2, 2));
month = int.Parse(batchNumber.Substring(4, 2));
sequence = int.Parse(batchNumber.Substring(6, 2));
}
//else if (batchNumber.Length > 8)
//{
// year = int.Parse(batchNumber.Substring(5, 2));
// month = int.Parse(batchNumber.Substring(7, 2));
// sequence = int.Parse(batchNumber.Substring(9, 2));
//}
else if (batchNumber.Length > 8)
{
year = int.Parse(batchNumber.Substring(5, 4));
month = int.Parse(batchNumber.Substring(9, 2));
sequence = int.Parse(batchNumber.Substring(11, 2));
}
else
{
// 这里添加一个默认情况，虽然前面条件应该覆盖了所有情况，但为了保险起见;
throw new ArgumentException("批号格式不符合标准");
}
// 插入数据库
using (var cmd = new SqlCommand(
@"INSERT INTO MaterialStock
(MaterialID,MaterialName, BatchNumber, Year, Month, Sequence, TotalQuantity,Remaining, EntryDate)
VALUES (@matID,@MaterialName, @batchNo, @year, @month, @seq, @qty,@qty, GETDATE())",
conn, transaction))
{
cmd.Parameters.AddWithValue("@matID", materialId);
cmd.Parameters.AddWithValue("@MaterialName", materialName);
cmd.Parameters.AddWithValue("@batchNo", batchNumber);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@seq", sequence);
cmd.Parameters.AddWithValue("@qty", quantity);
cmd.ExecuteNonQuery();
}
}
// 以下是总体的导入方法；
private void ImportFromExcel(string filePath)
{
FileInfo excelFile = new FileInfo(filePath);
using (var package = new ExcelPackage(excelFile))
{
var worksheet = package.Workbook.Worksheets[0];
int rowCount = worksheet.Dimension.Rows;
// 获取原料名称到ID的映射
var materialNameToIdMap = GetMaterialNameToIdMap();
// 用于记录重复的批号
var duplicateBatchNumbers = new List<string>();
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
try
{
for (int row = 2; row <= rowCount; row++) // 假设第1行是标题
{
string batchNumber = worksheet.Cells[row, 1].Text.Trim();
string materialName = worksheet.Cells[row, 2].Text.Trim(); // 现在第2列是原料名称
decimal quantity = decimal.Parse(worksheet.Cells[row, 3].Text.Trim()); // 现在第3列是数量
// 通过原料名称查找ID
if (!materialNameToIdMap.TryGetValue(materialName, out int materialId))
{
throw new ArgumentException($"找不到原料名称对应的ID: {materialName}，行号: {row}");
}
// 检查批号是否已存在
if (IsBatchNumberExists(conn, transaction, batchNumber))
{
duplicateBatchNumbers.Add(batchNumber);
continue; // 跳过当前记录
}
SaveBatch(conn, transaction, batchNumber, materialId, materialName, quantity);
}
transaction.Commit();
// 提示用户重复的批号
if (duplicateBatchNumbers.Count > 0)
{
string duplicateMessage = "以下批号已存在，已自动跳过：\n" +
string.Join("\n", duplicateBatchNumbers);
MessageBox.Show(duplicateMessage, "导入提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
}
//导入库存按钮事件
private void btnInstock_Click(object sender, EventArgs e)
{
// 创建文件选择对话框
OpenFileDialog openFileDialog = new OpenFileDialog
{
Filter = "Excel文件|*.xlsx;*.xls|CSV文件|*.csv",
Title = "选择入库数据文件"
};
// 用户选择文件后执行
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
string filePath = openFileDialog.FileName;
try
{
ImportFromExcel(filePath);
MessageBox.Show("导入成功！");
}
catch (Exception ex)
{
MessageBox.Show($"导入失败：{ex.Message}");
}
}
}
```
