private void Button2_Click(object sender, EventArgs e) { string path = Environment.CurrentDirectory + "\\转换结果"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } ExcelOpenXml.Create($@"{path}\{Path.GetFileNameWithoutExtension(_fileName)}.{DateTime.Now:yyyy.MM.dd.HH.mm.ss}.xlsx", new DataSet() { Tables = { _targetTable } }); MessageBox.Show(@"转换完成!"); }
private void Button1_Click(object sender, EventArgs e) { _configList.Clear(); _configColumnsList.Clear(); _fileName = ""; textBox1.Text = ""; _targetTable = null; _dataTable = null; dataGridView1.DataSource = null; dataGridView2.DataSource = null; dataGridView3.DataSource = null; OpenFileDialog openFileDialog = new OpenFileDialog { Filter = @"All files (*.*)|*.*|xlsx(*.xlsx)|*.xlsx", FilterIndex = 2, RestoreDirectory = false }; if (openFileDialog.ShowDialog() == DialogResult.OK) { _fileName = openFileDialog.FileName; } if (_fileName.Length == 0) { return; } textBox1.Text = _fileName; string configFile = Environment.CurrentDirectory + "\\Config.xlsx"; if (!File.Exists(configFile)) { MessageBox.Show(configFile + @"出现配置文件不存在的致命错误,请恢复配置文件后再操作!\r\n"); return; } //try //{ DataTable configTable = ExcelOpenXml.GetSheet(configFile, "Sheet1"); if (configTable == null || configTable.Rows.Count < 1) { MessageBox.Show(@"列转换配置表[Config][Sheet1]表数据不完整,请给出正确格式的配置文件"); return; } for (int i = 0; i < configTable.Columns.Count; i++) { _configList.Add(new Config { Dt1 = configTable.Columns[i].ColumnName.Trim(), Dt2 = configTable.Rows[0][i].ToString().Trim() }); } var configMappingTable = ExcelOpenXml.GetSheet(configFile, "Sheet2"); var configMappingPlaceOfPaymentTable = ExcelOpenXml.GetSheet(configFile, "Sheet3").Rows.Cast <DataRow>() .Select(x => new ConvertPlaceOfPaymentMapping { CollectionOffice = x[0].ToString().Trim().ToLower(), PlaceOfPayment = x[1].ToString().Trim() }).ToList(); var configClientTable = ExcelOpenXml.GetSheet(configFile, "Sheet4"); if (configClientTable == null || configClientTable.Rows.Count < 1) { MessageBox.Show(@"列转换配置表[Config][Sheet4]表数据不完整,请给出正确格式的配置文件"); return; } int.TryParse(configClientTable.Rows[0][0].ToString(), out int configClientLength); var isHaveExtraLongCliengLenght = true; //配置文件Sheet2 数据 _configColumnsList.AddRange(configMappingTable.Rows.Cast <DataRow>().Select(x => new ConvertColumnToRow { ChargeCurrency = x[0].ToString().Trim(), ChargeCode = x[1].ToString().Trim(), Columns = x[2].ToString().Trim() })); var cofingListToLower = _configList.Select(x => x.Dt1.ToLower()); var errConfig = _configColumnsList.Where(x => !cofingListToLower.Contains(x.Columns.ToLower())).Select(x => x.Columns); if (errConfig.Any()) { MessageBox.Show($"配置文件出现致命错误!!! \r\n 以下表[Sheet2].列[Columns]中的数据未在表[Sheet1]中查找到 \r\n {string.Join("\r\n", errConfig)}"); return; } _dataTable = ExcelOpenXml.GetSheet(_fileName, "Sheet0", 3); if (_dataTable.Rows.Count < 3) { MessageBox.Show(@"未读取到数据"); return; } //Incl. OFT. for (int i = 0; i < _dataTable.Columns.Count; i++) { string row0 = _dataTable.Rows[0][i].ToString().Trim(); string row1 = _dataTable.Rows[1][i].ToString().Trim(); if (row0.Length > 0 && row1.Length == 0) { _dataTable.Columns[i].ColumnName = row0; } if (row0.Length > 0 && row1.Length > 0) { _dataTable.Columns[i].ColumnName = row1; } if (row0.Length == 0 && row1.Length > 0) { _dataTable.Columns[i].ColumnName = row1; } _dataTable.Columns[i].ColumnName = _dataTable.Columns[i].ColumnName.Replace("\r", " ").Replace("\n", " "); } _dataTable.Rows.RemoveAt(0); _dataTable.Rows.RemoveAt(0); _targetTable = new DataTable("Sheet1"); _targetTable = configTable.Clone(); _targetTable.TableName = "Sheet1"; var bl1blmanr = 1000000; if (!cofingListToLower.Contains(@"blvposno") || !cofingListToLower.Contains("bl nr.") || !cofingListToLower.Contains("bl1blmanr")) { MessageBox.Show($"未在数据源中找到列 [blvposno],[bl nr.],[bl1blmanr],请检查数据格式!"); return; } //新需求.只有[Incl. OFT.]值为N才计算,其他值的行,则忽略 var table = new DataTable("Sheet0"); table = _dataTable.Clone(); for (int i = 0; i < _dataTable.Rows.Count; i++) { if (_dataTable.Rows[i]["Incl. OFT."].ToString().Trim().ToUpper() == "N") { table.Rows.Add(_dataTable.Rows[i].ItemArray); } } _dataTable = table; for (int i = 0; i < _dataTable.Rows.Count; i++) { DataRow row = _targetTable.NewRow(); for (int j = 0; j < configTable.Columns.Count; j++) { for (int k = 0; k < _dataTable.Columns.Count; k++) { if (_configList[j].Dt2.ToLower() == _dataTable.Columns[k].ColumnName.Trim().ToLower()) { //Console.WriteLine(dataTable.Columns[k].ColumnName.Trim().ToLower()); row[j] = _dataTable.Rows[i][k]; } } } // Client列超过长度则直接替换为空 configClientLength if ((row["Client"]?.ToString() ?? "").Length > configClientLength) { if (isHaveExtraLongCliengLenght) { MessageBox.Show($@"第{i}行,列[Client]出现超过长度.已替换[{row["Client"]}]为空字符"); isHaveExtraLongCliengLenght = false; } row["Client"] = ""; } // Charge Currency Charge Code 行列转换 var targetColumnName = _configColumnsList.FirstOrDefault(x => x.ChargeCurrency.ToLower() == _dataTable.Rows[i][_ChargeCurrency].ToString().Trim().ToLower() && x.ChargeCode.ToLower() == _dataTable.Rows[i]["Charge Code"].ToString().ToLower())?.Columns; if (_configList.Select(x => x.Dt1.ToLower()).Count(x => x.Contains(targetColumnName?.ToLower() ?? "abcdefghigk123")) > 0) { decimal.TryParse(_dataTable.Rows[i]["Charge Amount"]?.ToString() ?? "0", out decimal amount); decimal.TryParse(row[targetColumnName ?? ""]?.ToString() ?? "0", out decimal value); row[targetColumnName ?? ""] = value + amount; } _targetTable.Rows.Add(row); //通过BL nr. 列判断 blvposno 的计数,从1开始计数,每行++1 直到[BL nr. 列]和上一行不一致后,重新从1开始计数 //通过BL nr. 列判断 bl1blmanr 的计数,从1开始计数,每行++1 直到[BL nr. 列]和上一行不一致后,重新从1开始计数 int blvposno = 1; if (i > 0 && i < _dataTable.Rows.Count) { bl1blmanr = _targetTable.Rows[i]["BL nr."].ToString().ToLower() == _targetTable.Rows[i - 1]["BL nr."].ToString().ToLower() ? bl1blmanr : bl1blmanr + 1; blvposno = _targetTable.Rows[i]["BL nr."].ToString().ToLower() == _targetTable.Rows[i - 1]["BL nr."].ToString().ToLower() ? int.Parse(_targetTable.Rows[i - 1]["blvposno"].ToString()) + 1 : 1; } //新需求:为防止重复,直接用[BL nr.]列 替换 [bl1blmanr]列 //_targetTable.Rows[i]["bl1blmanr"] = bl1blmanr; _targetTable.Rows[i]["bl1blmanr"] = _targetTable.Rows[i]["BL nr."]; _targetTable.Rows[i]["blvposno"] = blvposno; //数据源[Booking Office]转换为[PlaceOfPayment],转换关系在Config.xlsx的Sheet3中 var oldPlaceOfPayment = _targetTable.Rows[i]["PlaceOfPayment"].ToString().ToLower(); var newPlaceOfPayment = configMappingPlaceOfPaymentTable.FirstOrDefault(x => x.CollectionOffice == oldPlaceOfPayment)?.PlaceOfPayment ?? ""; if (newPlaceOfPayment.Length > 0) { _targetTable.Rows[i]["PlaceOfPayment"] = newPlaceOfPayment; } } dataGridView1.DataSource = configTable; dataGridView2.DataSource = _dataTable; dataGridView3.DataSource = _targetTable; //} //catch (Exception err) //{ // MessageBox.Show(err.Message); //} }