public Form1() { InitializeComponent(); CheckedListBoxCurrency1.Items.Clear(); checkedListBox1.Items.Clear(); checkedListBoxPolCode1.Items.Clear(); dataGridView1.ColumnHeadersHeight = 22; dataGridView1.RowHeadersWidth = 70; dataGridView2.RowHeadersWidth = 70; dataGridView3.RowHeadersWidth = 70; dataGridView1.RowStateChanged += RowStateChanged; dataGridView2.RowStateChanged += RowStateChanged; dataGridView3.RowStateChanged += RowStateChanged; //根据collection office的勾选的内容来生成表(需要做出配置功能,只显示常用的几个(大约5个),其他的统统不要) string configFile = Environment.CurrentDirectory + "\\Config.xlsx"; var configChargeCurrency = ExcelOpenXml.GetSheet(configFile, "货币种类"); if (configChargeCurrency == null) { MessageBox.Show(@"列转换配置表[Config][货币种类]表数据不完整,请给出正确格式的配置文件"); return; } foreach (var item in configChargeCurrency.Columns.Cast <DataColumn>().Select(x => x.ColumnName)) { CheckedListBoxCurrency1.Items.Add(item, true); } _configCheckBoxTable = ExcelOpenXml.GetSheet(configFile, "显示collectionoffice集合"); if (_configCheckBoxTable == null) { MessageBox.Show(@"列转换配置表[Config][显示collectionoffice集合]表数据不完整,请给出正确格式的配置文件"); return; } foreach (var item in _configCheckBoxTable.Columns.Cast <DataColumn>().Select(x => x.ColumnName)) { checkedListBox1.Items.Add(item, true); for (int i = 0; i < _configCheckBoxTable.Rows.Count; i++) { var value = _configCheckBoxTable.Rows[i][item]?.ToString()?.Trim() ?? ""; if (value.Length > 0) { checkedListBoxPolCode1.Items.Add($"{item}-{value}", true); } } } //todo:event是在改变前,必须选择其他event checkedListBox1.ItemCheck += CheckedListBox1_ItemCheck; //checkedListBox1.Items.AddRange(configCheckBoxTable.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray()); }
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) { _fileName = ""; textBox1.Text = ""; _configSheet1List.Clear(); _configCurrencyCheckedList.Clear(); _configCollectionOfficeCheckedList.Clear(); _configPolCodeCheckedList.Clear(); _targetTable = null; _dataTable = null; dataGridView1.DataSource = null; dataGridView2.DataSource = null; dataGridView3.DataSource = null; for (var i = 0; i < CheckedListBoxCurrency1.Items.Count; i++) { var item = CheckedListBoxCurrency1.GetItemChecked(i); if (item) { _configCurrencyCheckedList.Add(CheckedListBoxCurrency1.GetItemText(CheckedListBoxCurrency1.Items[i])); } } if (_configCurrencyCheckedList.Count == 0) { MessageBox.Show(@"请至少选择一种[货币]"); return; } for (var i = 0; i < checkedListBox1.Items.Count; i++) { var item = checkedListBox1.GetItemChecked(i); if (item) { _configCollectionOfficeCheckedList.Add(checkedListBox1.GetItemText(checkedListBox1.Items[i])); } } for (var i = 0; i < checkedListBoxPolCode1.Items.Count; i++) { var item = checkedListBoxPolCode1.GetItemChecked(i); if (item) { _configPolCodeCheckedList.Add(checkedListBoxPolCode1.GetItemText(checkedListBoxPolCode1.Items[i])); } } var 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; } DataTable configTable = ExcelOpenXml.GetSheet(configFile, "显示列集合"); if (configTable == null || configTable.Rows.Count < 1) { MessageBox.Show(@"列转换配置表[Config][显示列集合]表数据不完整,请给出正确格式的配置文件"); return; } //读取配置文件 for (int i = 0; i < configTable.Columns.Count; i++) { _configSheet1List.Add(new ConfigSheet1 { ColumnName = configTable.Columns[i].ColumnName.Replace("\r", " ").Replace("\n", " ").Trim(), IsHaveY = configTable.Rows[0][i].ToString().Trim().ToUpper() == "Y" }); } // //_dataTable = ExcelOpenXml.GetSheet(_fileName, "Sheet0", 3); _dataTable = ExcelOpenXml.GetSheet(_fileName, "Sheet0", 3); if (_dataTable.Rows.Count < 3) { MessageBox.Show(@"未读取到[Sheet0]数据"); 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", " ").Trim(); } _dataTable.Rows.RemoveAt(0); _dataTable.Rows.RemoveAt(0); var collectionOfficeDict = _configCollectionOfficeCheckedList?.ToDictionary(x => x, x => new List <string>()); foreach (var item in _configPolCodeCheckedList) { var polCodes = item.Split('-'); if (polCodes.Length >= 2 && collectionOfficeDict.ContainsKey(polCodes[0])) { collectionOfficeDict[polCodes[0]].Add(polCodes[1]); } } //只有[Incl. OFT.]值为N才计算,其他值的行,则忽略,仅显示collection office勾选的列 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" && _configCollectionOfficeCheckedList.Contains(_dataTable.Rows[i]["Collection Office"].ToString().Trim()) && _configCurrencyCheckedList.Contains(_dataTable.Rows[i]["Charge Currency"].ToString().Trim())) { var key = _dataTable.Rows[i]["Collection Office"].ToString().Trim(); //var polCode = _dataTable.Rows[i]["POL Code"].ToString().Trim(); //修改 [POL Code] 为[Trunk POL] //修改 [POL Code] 为[B/L No.]前三位 var polCode = _dataTable.Rows[i]["B/L No."].ToString().Trim().Substring(0, 3); //if (_dataTable.Rows[i]["B/L No."].ToString().Trim() == "NG11900810") //{ // Console.WriteLine(key); //} if (!collectionOfficeDict.ContainsKey(key)) { continue; } if (collectionOfficeDict[key].Count > 0) { if (collectionOfficeDict[key].Contains(polCode)) { table.Rows.Add(_dataTable.Rows[i].ItemArray); } } else { table.Rows.Add(_dataTable.Rows[i].ItemArray); } } } _dataTable = table; if (_dataTable.Rows.Count < 1) { MessageBox.Show(@"当前条件下无数据"); return; } //表1,仅保留黄色表头部分,仅保留配置Sheet0中值为Y的列 var list = _configSheet1List.Where(x => x.IsHaveY).Select(x => x.ColumnName.ToUpper()).ToList(); var table1 = new DataTable("Sheet0"); table1 = table.Clone(); for (int i = 0; i < table.Rows.Count; i++) { table1.Rows.Add(table.Rows[i].ItemArray); } for (int i = table.Columns.Count - 1; i >= 0; i--) { if (!list.Contains(table1.Columns[i].ColumnName.ToUpper())) { table1.Columns.RemoveAt(i); } } _targetTable = table1; //"8S8Otakt" _targetTable = new DataTable("Sheet1"); //_targetTable = table1.Clone(); _targetTable.TableName = "Sheet1"; //chargeCode的内容变为列 var chargeCodeUsList = table.Select("[Charge Currency] = 'USD'").Select(x => x.Field <string>("Charge Code"))?.Distinct()?.ToList(); var chargeCodeCnList = table.Select("[Charge Currency] = 'CNY'").Select(x => x.Field <string>("Charge Code"))?.Distinct()?.ToList(); if (chargeCodeUsList?.Count > 0 && chargeCodeUsList.Contains("LCH")) { chargeCodeUsList.Remove("LCH"); } var targetTableColumnsNamesList = table1.Columns.Cast <DataColumn>().Select(x => x.ColumnName)?.ToList(); var targetOldTable = targetTableColumnsNamesList.ToArray(); if (chargeCodeUsList?.Count + chargeCodeCnList?.Count < 1 || targetTableColumnsNamesList?.Count < 1) { MessageBox.Show(@"未找到关键列[Charge Code],注:大小写,空字符敏感"); return; } //将chargeCode的内容添加至tableColumns for (int i = 0; i < targetTableColumnsNamesList.Count; i++) { if (targetTableColumnsNamesList[i] == "Charge Code") { //添加美元列、合计 if (chargeCodeUsList.Count > 0) { targetTableColumnsNamesList.InsertRange(i + 1, chargeCodeUsList); } targetTableColumnsNamesList.Insert(i + 1 + chargeCodeUsList.Count, "Us Sum"); //添加RMB列、合计 if (chargeCodeCnList.Count > 0) { targetTableColumnsNamesList.InsertRange(i + 1 + chargeCodeUsList.Count + 1, chargeCodeCnList); } targetTableColumnsNamesList.Insert(i + 1 + chargeCodeUsList.Count + 1 + chargeCodeCnList.Count, "Cn Sum"); } } //单独加2列。美金合计 人民币合计 //targetTableColumnsNamesList.AddRange(new[] { "USD Sum", "CNY Sum" }); _targetTable.Columns.AddRange(targetTableColumnsNamesList.Select(x => new DataColumn(x)).ToArray()); //按照BLNo 分组 var blNoList = table1.Rows.Cast <DataRow>().Select(x => x.Field <string>("B/L No.").Trim())?.Distinct()?.ToList(); if (blNoList?.Count < 1) { MessageBox.Show(@"未找到关键数据[B/L No.],请检查数据源!"); return; } for (int i = 0; i < blNoList.Count; i++) { //检索唯一[BL/No] var rows = table1.Select($"[B/L No.] = '{blNoList[i]}'"); var datas = rows.Select(x => new RowModel { Code = x.Field <string>("Charge Code"), Currency = x.Field <string>("Charge Currency"), Rated = x.Field <string>("Rated As"), Unit = x.Field <string>("Unit"), Amount = x.Field <string>("Charge Amount"), Us = "", Cn = "" })?.ToList(); //检索唯一[BL/No]下唯一[Unit] var units = datas.Select(x => x.Unit).Distinct().ToList(); //将BLLD移至第一行 if (units.Contains("BLLD")) { units.Remove("BLLD"); //units.Insert(0, "BLLD"); foreach (var item in datas.Where(x => x.Unit.Contains("BLLD"))) { item.Unit = units[0]; } } //按照箱型分组 if (checkBox1.Checked) { foreach (var unit in units) { var row = _targetTable.NewRow(); foreach (var columnName in targetOldTable) { row[columnName] = rows[0][columnName]; } var unitEqualsDatas = datas.Where(x => x.Unit == unit).ToList(); foreach (var data in unitEqualsDatas) { row[data.Code] = data.Amount; } row["Us Sum"] = unitEqualsDatas.Where(x => x.Currency == "USD").Sum(x => Convert.ToDecimal(x.Amount)).ToString(); row["Cn Sum"] = unitEqualsDatas.Where(x => x.Currency == "CNY").Sum(x => Convert.ToDecimal(x.Amount)).ToString(); row["Rated As"] = unitEqualsDatas.FirstOrDefault()?.Rated; row["Unit"] = unit; row["Charge Amount"] = unitEqualsDatas.Sum(x => Convert.ToDecimal(x.Amount)).ToString(); _targetTable.Rows.Add(row); } } else//不按照箱型分组 { var row = _targetTable.NewRow(); foreach (var columnName in targetOldTable) { row[columnName] = rows[0][columnName]; } //var unitEqualsDatas = datas.Where(x => x.Unit == unit).ToList(); foreach (var data in datas) { row[data.Code] = data.Amount; } row["Us Sum"] = datas.Where(x => x.Currency == "USD").Sum(x => Convert.ToDecimal(x.Amount)).ToString(); row["Cn Sum"] = datas.Where(x => x.Currency == "CNY").Sum(x => Convert.ToDecimal(x.Amount)).ToString(); row["Rated As"] = datas.FirstOrDefault()?.Rated; row["Unit"] = units.FirstOrDefault(); row["Charge Amount"] = datas.Sum(x => Convert.ToDecimal(x.Amount)).ToString(); _targetTable.Rows.Add(row); } } dataGridView1.DataSource = configTable; dataGridView2.DataSource = _dataTable; dataGridView3.DataSource = _targetTable; //1.选择excel //2.验证列,不存在则报错 //3.读取配置文件,不存在则报错 }