Example #1
0
        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());
        }
Example #2
0
        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(@"转换完成!");
        }
Example #3
0
        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.读取配置文件,不存在则报错
        }