Winsoft.GOV/Winsoft.GOV.ImportDataForm/Form1.cs

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Winsoft.GOV.Framework.Model;
using Winsoft.GOV.Framework.Provider;
using static Winsoft.GOV.ImportDataForm.ExcelHelper;

namespace Winsoft.GOV.ImportDataForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
                comboBox1.Text = Path.GetFileNameWithoutExtension(openFileDialog1.FileName);
                dataGridView1.DataSource = ExcelHelper.ExcelToDataSet(textBox1.Text).Tables[0];
                dataGridView1.Show();
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            IEnumerable<Branch> tmp = BranchProvider.Instance.FindAll();
            PowerMatterTypeProvider.Instance.CreateTable();
            PowerMettersBaseProvider.Instance.CreateTable();
            OnceRunPowerMattersProvider.Instance.CreateTable();
            foreach(Branch b in tmp)
            {
                comboBox1.Items.Add(b);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            bool find = false;
            foreach (Branch b in comboBox1.Items)
            {
                if (comboBox1.Text == b.ShortName)
                {
                    comboBox1.SelectedItem = b;
                    find = true;
                    break;
                }
            }
            if (!find)
            {
                if (MessageBox.Show("没有找到部门名为“" + comboBox1.Text + "”的部门，您是否向系统添加该部门？", "添加部门", MessageBoxButtons.OKCancel) == DialogResult.OK)
                {
                    Branch b = new Branch()
                    {
                        GUID = comboBox1.Text,
                        ShortName = comboBox1.Text
                    };
                    comboBox1.Items.Add(b);
                    comboBox1.SelectedItem = b;
                }
                else
                    return;
            }

            IList<PowerMattersBase> list = ExcelHelper.ExcelToList<PowerMattersBase>(textBox1.Text, GetApplyType);
            IList<PowerMattersBase> failslist = new List<PowerMattersBase>();
            PowerMatterTypeProvider.Instance.Insert(list, ref failslist);
            if (failslist.Count == 0)
            {
                MessageBox.Show("导入成功");
            }
            else
            {
                saveFileDialog1.FileName = String.Format("{0}（失败记录）.xls", comboBox1.Text);
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    ExcelHelper.WriteExcel<PowerMattersBase>(failslist, saveFileDialog1.FileName, WriteHeard, WriteRow);
                }
                MessageBox.Show("完成");
            }
        }

        private void WriteRow(IRow row, PowerMattersBase obj)
        {
            row.CreateCell(0, CellType.String).SetCellValue(obj.QL_DEP);
            row.CreateCell(1, CellType.String).SetCellValue(obj.OUGUID);
            row.CreateCell(2, CellType.String).SetCellValue(obj.QL_NAME);
            row.CreateCell(3, CellType.Numeric).SetCellValue(obj.ApplyCode);
            row.CreateCell(4, CellType.String).SetCellValue((obj.ApplyCode > -1 && obj.ApplyCode < ApplyType.Names.Length)? ApplyType.Names[obj.ApplyCode] : "其他");
        }
        private void WriteHeard(IRow row)
        {
            row.CreateCell(0, CellType.String).SetCellValue("部门名称");
            row.CreateCell(1, CellType.String).SetCellValue("部门编号");
            row.CreateCell(2, CellType.String).SetCellValue("权力名称");
            row.CreateCell(3, CellType.String).SetCellValue("分类编码");
            row.CreateCell(4, CellType.String).SetCellValue("分类名称");
        }

        private PowerMattersBase GetApplyType(IRow row)
        {
            if (row.Cells.Count != 7)
                return null;
            PowerMattersBase a = new PowerMattersBase();

            a.QL_DEP = (comboBox1.SelectedItem as Branch).ShortName;
            a.OUGUID = (comboBox1.SelectedItem as Branch).GUID;
            a.QL_NAME = row.GetCell(1).StringCellValue.Trim();

            int i = 0;
            for (int j = 2; j <= row.Cells.Count - 1; j++)
            {
                ICell cell = row.GetCell(j);
                if (cell == null || cell.CellType == CellType.Blank || (cell.CellType == CellType.String && String.IsNullOrWhiteSpace(cell.StringCellValue)) 
                    || (cell.CellType == CellType.Numeric && cell.NumericCellValue != 1))
                {
                    i++;
                    continue;
                }                    
                else
                    break;
            }
            a.ApplyCode = i > ApplyType.Names.Length - 1 ?  -1 : i;
            return a;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox2.Text = openFileDialog1.FileName;
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            label1.Text = "正在载入数据...";
            IList<PowerMattersBase> list = ExcelHelper.ExcelToList<PowerMattersBase>(textBox2.Text, ReadPowerMatters);
            label1.Text = "正在更新部门信息...";
            foreach (PowerMattersBase p in list)
            {
                Branch b = BranchProvider.Instance.Find(p.OUGUID);
                if (b == null)
                {
                    b = new Branch()
                    {
                        GUID = p.OUGUID,
                        ShortName = p.QL_DEP
                    };
                    //BranchProvider.Instance.Insert(b);
                }
                else
                {
                    p.QL_DEP = b.ShortName;
                }
            }
            label1.Text = "正在清除老权力清单数据...";
            //PowerMettersBaseProvider.Instance.DeleteAll();
            label1.Text = "正在插入新权力清单数据...";
            //PowerMettersBaseProvider.Instance.Insert(list);
            label1.Text = "正在清理老权力清单的详细数据...";
            PowerMettersDetailProvider.Instance.DeleteByPowerMattersBase();
            label1.Text = "正在插入新权力清单的详细数据...";
            foreach (PowerMattersBase p in list)
            {
                if (!PowerMettersDetailProvider.Instance.IsExist(p.QL_INNER_CODE))
                {
                    PowerMattersDetail pd = QLSXProvider.Instance.FindByID(p.QL_INNER_CODE);
                    if (pd != null)
                        PowerMettersDetailProvider.Instance.Insert(pd);
                }
            }
            label1.Text = "完成";
        }

        private PowerMattersBase ReadPowerMatters(IRow row)
        {
            if (row.Cells.Count < 6)
                return null;
            PowerMattersBase a = new PowerMattersBase();
            a.OUGUID = row.GetCell(1).StringCellValue;
            a.QL_INNER_CODE = row.GetCell(3).StringCellValue;
            a.QL_DEP = row.GetCell(4).StringCellValue;
            a.QL_NAME = row.GetCell(5).StringCellValue;
            return a;
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox3.Text = openFileDialog1.FileName;
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            label2.Text = "正在载入数据...";
            IList<PowerMattersBase> list = ExcelHelper.ExcelToList<PowerMattersBase>(textBox3.Text, ReadPowerMatters);
            label2.Text = "正在更新部门信息...";
            foreach (PowerMattersBase p in list)
            {
                Branch b = BranchProvider.Instance.Find(p.OUGUID);
                if (b == null)
                {
                    b = new Branch()
                    {
                        GUID = p.OUGUID,
                        ShortName = p.QL_DEP
                    };
                    BranchProvider.Instance.Insert(b);
                }
                else
                {
                    p.QL_DEP = b.ShortName;
                }
            }
            label2.Text = "正在清除老的最多跑一次数据...";
            OnceRunPowerMattersProvider.Instance.DeleteAll();
            label2.Text = "正在插入新的最多跑一次数据...";
            OnceRunPowerMattersProvider.Instance.Insert(list);
            label2.Text = "完成";
        }

        private void button7_Click(object sender, EventArgs e)
        {
            if(folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                IEnumerable<Branch> bs = BranchProvider.Instance.FindOnceRunBranch();
                foreach(Branch b in bs)
                {
                    label3.Text = "正在导出“" + b.ShortName + ".xls”";
                    IEnumerable<PowerMattersBase> ps = OnceRunPowerMattersProvider.Instance.FindOnceRunPowerMattersByBranchGUID(b.GUID);
                    foreach(PowerMattersBase p in ps)
                    {
                        p.QL_DEP = b.ShortName;
                        p.OUGUID = b.GUID;
                    }
                    ExcelHelper.WriteExcel<PowerMattersBase>(ps.ToList<PowerMattersBase>(), folderBrowserDialog1.SelectedPath + "\\" + b.ShortName + ".xls", ExportWriteHeard, ExportWriteRow);
                }
                label3.Text = "完成";
            }
        }

        private void ExportWriteRow(IRow row, PowerMattersBase obj)
        {
            row.CreateCell(0, CellType.String).SetCellValue(obj.OUGUID);
            row.CreateCell(1, CellType.String).SetCellValue(obj.QL_INNER_CODE);
            row.CreateCell(2, CellType.String).SetCellValue(obj.QL_DEP);
            row.CreateCell(3, CellType.String).SetCellValue(obj.QL_NAME);
            row.CreateCell(4, CellType.String).SetCellValue("");
            row.CreateCell(5, CellType.String).SetCellValue("");
            row.CreateCell(6, CellType.String).SetCellValue("");
            row.CreateCell(7, CellType.String).SetCellValue("");
            row.CreateCell(8, CellType.String).SetCellValue("");
            row.CreateCell(9, CellType.String).SetCellValue("");
            PowerMattersBase p = PowerMatterTypeProvider.Instance.FindById(obj.QL_INNER_CODE);
            if (p != null)
                row.CreateCell(5 + p.ApplyCode).SetCellValue("1");
            else
                row.CreateCell(4).SetCellValue("1");
            int i = 0;

            foreach (ICell c in row.Cells)
            {
                ICellStyle style = row.Sheet.Workbook.CreateCellStyle();
                style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                style.GetFont(row.Sheet.Workbook).IsBold = false;
                if (i == 3)
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                else                 
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                i++;
                if (p == null)
                    style.FillBackgroundColor = 51;
                c.CellStyle = style;
            }           
        }
        private void ExportWriteHeard(IRow row)
        {
            row.Sheet.SetColumnWidth(0, 15 * 256);
            row.Sheet.SetColumnWidth(1, 40 * 256);
            row.Sheet.SetColumnWidth(2, 15 * 256);
            row.Sheet.SetColumnWidth(3, 50 * 256);
            row.Sheet.SetColumnWidth(4, 15 * 256);
            row.Sheet.SetColumnWidth(5, 15 * 256);
            row.Sheet.SetColumnWidth(6, 15 * 256);
            row.Sheet.SetColumnWidth(7, 15 * 256);
            row.Sheet.SetColumnWidth(8, 15 * 256);
            row.Sheet.SetColumnWidth(9, 15 * 256);
            row.CreateCell(0, CellType.String).SetCellValue("部门编号");
            row.CreateCell(1, CellType.String).SetCellValue("权力编码");
            row.CreateCell(2, CellType.String).SetCellValue("部门名称");
            row.CreateCell(3, CellType.String).SetCellValue("权力名称");
            row.CreateCell(4, CellType.String).SetCellValue("其他");
            row.CreateCell(5, CellType.String).SetCellValue("即办");
            row.CreateCell(6, CellType.String).SetCellValue("全程网上申办，在线取件");
            row.CreateCell(7, CellType.String).SetCellValue("全程网上申办，快递送达");
            row.CreateCell(8, CellType.String).SetCellValue("网上申报，预审，窗口核验取件");
            row.CreateCell(9, CellType.String).SetCellValue("窗口申报，快递送达");
            foreach(ICell c in row.Cells)
            {
                ICellStyle style = row.Sheet.Workbook.CreateCellStyle();
                style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                style.WrapText = true;
                style.GetFont(row.Sheet.Workbook).IsBold = true;
                c.CellStyle = style;
            }         
        }
    }
}