excel开发&数据筛选

最近有这么一个需求。是精准扶贫的项目。我这里有两张表数据。第一张表中有若干条记录，有县、乡、村，证件号码等字段。如下图所示，暂且叫A表。

、而另外一张表有如下图所示的字段，我们把下图叫B表。我们来看一下，有户编号，证件编号等字段。

现在要求是用第一个图（A数据）的证件号码与第二个图(B数据)的证件编号来挂接。想取出这样的结果，两个字段中对应值相同的数据，将相同部分分证件编号的A表数据放到一个数据表里，而A表除去相同部分的数据放在一个表里，B表除去相同数据的部分放在另外一个表里中。说得有点绕，可以类似这么理解，就是我们现在有相同两个矢量量面A、B，叠加，求出共同部分，A中除去共同部分，B中除去共同部分。好了，我们来看一下代码是怎么实现的。

首先需要将两个表的数据分别读取，然后放到字典中，以证件编号，证件号码作为字典的关键字。而字典的键值对象就是我们定义好一个人口信息类。这样我们就可以将两份数据组织起来，使用字典的关键字key来判断，具体的实现可以参考代码。每次需要创建一个excel文件，我们这里使用的是Apose.cell来创建、下面我们来看一下实现的代码。

namespace ExportData
{
    public partial class Form1 : Form
    {
        private String fileName = "";
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
             OpenFileDialog file = new OpenFileDialog();
            file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            file.Multiselect = false;

            if (file.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
 
            String path = file.FileName;
            string fileSuffix = System.IO.Path.GetExtension(path);
 
            if (string.IsNullOrEmpty(fileSuffix))
            {
                System.Windows.Forms.MessageBox.Show("文件没有后缀");
                return;
            }

            fileName = path;
            this.tb_filename.Text = fileName;

        }

        //public void isExsit(List<Dictionary<string, int>> dics,string strHBH) {
        private void isExsit(Dictionary<string, PopInfo> dics, string strHBH,PopInfo pop)
        {

            /*
            for (int i = 0; i < dics.Count; i++)
            {
                if (dics[i].ContainsKey(strHBH))
                {
                   
                    int cnt = dics[i][strHBH];
                    cnt = cnt + 1;


                }
            }*/

            if (dics.ContainsKey(strHBH))
            {
                return;
            } if (!dics.ContainsKey(strHBH))
            {
                dics.Add(strHBH, pop);
            }
        }

        public void record(Dictionary<string, int> dics,string strHBH,int cnt)
        {
            if (dics.ContainsKey(strHBH))
            {
                return;
            } if (!dics.ContainsKey(strHBH))
            {
                dics.Add(strHBH, cnt);
            }
        }

        public string getBeforEightStr(string strParam) {

            string res = "";
            try
            {
                if (strParam.Length<18)
                {
                    return strParam;
                }
                res = strParam.Substring(0, 18);

                return res;
            }
            catch (Exception)
            {
                System.Windows.Forms.MessageBox.Show("转换身份证出现错误");
                return res;
            }
        
        }

        public int getNUM(string str_num) {
 
            int res = 0;
            try
            {
               res= int.Parse(str_num);
               return res;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.ToString());
                
            }
            return res;
        }

         private string GetAssemblyPath()
        {
            string CodeBasePath = System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
            CodeBasePath = CodeBasePath.Substring(8, CodeBasePath.Length - 8);
            string[] arrSection = CodeBasePath.Split(new char[] { '/' });
            string FolderPath = "";
            for (int i = 0; i < arrSection.Length - 1; i++)
            {
                FolderPath += arrSection[i] + "\\";
            }
 
            return FolderPath;
        }



        private void RUN_Click(object sender, EventArgs e)
        {
             if (string.IsNullOrEmpty(fileName))
            {
                System.Windows.Forms.MessageBox.Show("文件为空");
                return;
            }

            Workbook workbook = new Workbook();
            workbook.Open(fileName);
            Worksheets worksheets = workbook.Worksheets;
 
            String myfileName = System.IO.Path.GetFileNameWithoutExtension(fileName);

            int CNT = worksheets.Count;

            List<Dictionary<string, int>> Asheet = new List<Dictionary<string, int>>();
            List<Dictionary<string, int>> Bsheet = new List<Dictionary<string, int>>();

            Dictionary<string, PopInfo> dicsA = new Dictionary<string, PopInfo>();

            Dictionary<string, PopInfo> dicsB = new Dictionary<string, PopInfo>();
            for (int i = 0; i < CNT; i++)
            {
                if(i==0){
                    Cells cells = workbook.Worksheets[i].Cells;
                    int maxColum = cells.MaxColumn;
                    int maxRow = cells.MaxRow;

                    for (int row = 1; row < maxRow; row++) {
                        /*户编号*/
                        Cell cell_hbh = cells[row, 5];
                        string StrHBH = cell_hbh.StringValue.Trim();
                        /*人口数*/
                        Cell cell_rks = cells[row, 9];
                        int rks = getNUM(cell_rks.StringValue.Trim());

                        /*身份证前八位*/
                        Cell cell_sfz = cells[row, 8];
                        string StrSFZ = cell_sfz.StringValue.Trim();
                        //StrSFZ=getBeforEightStr(StrSFZ);

                        PopInfo popInfo = new PopInfo();
                        popInfo.setHBH(StrHBH);
                        popInfo.setHKS(rks);

                        Cell cell_fz = cells[row, 10];
                        string str_hz = cell_fz.StringValue.Trim();
                        if (str_hz.Contains("户主"))
                        {
                            isExsit(dicsA, StrSFZ, popInfo);
                        }

                        
                    }

                }
                if (i==1)
                {
                    Cells cells = workbook.Worksheets[i].Cells;
                    int maxColum = cells.MaxColumn;
                    int maxRow = cells.MaxRow;

                    for (int row = 1; row < maxRow; row++)
                    {
                        /*户编号*/
                        Cell cell_hbh = cells[row, 6];
                        string StrHBH = cell_hbh.StringValue.Trim();

                        /*人口数*/
                        Cell cell_rs = cells[row, 10];
                        string str_cnt = cell_rs.StringValue.Trim();
                        int cnt = getNUM(str_cnt);

                        /*身份证前八位*/
                        Cell cell_sfz = cells[row, 9];
                        string StrSFZ = cell_sfz.StringValue.Trim();
                        //StrSFZ = getBeforEightStr(StrSFZ);

                        PopInfo popInfo = new PopInfo();
                        popInfo.setHBH(StrHBH);
                        popInfo.setHKS(cnt);

                        isExsit(dicsB, StrSFZ, popInfo);
                    }
                }
            }

            if (compareAndExport(dicsA, dicsB))
            {
                System.Windows.Forms.MessageBox.Show("处理成功");
            }
            else {
                System.Windows.Forms.MessageBox.Show("处理失败");
            }

        }

        private bool compareAndExport(Dictionary<string, PopInfo> Asheet, Dictionary<string, PopInfo> Bsheet)
        {
            
            try
            {
                Dictionary<string, PopInfo> commonSheet = new Dictionary<string, PopInfo>();
                Dictionary<string, PopInfo> onlyASheet = new Dictionary<string, PopInfo>();
                Dictionary<string, PopInfo> onlyBSheet = new Dictionary<string, PopInfo>();
                foreach (KeyValuePair<string, PopInfo> kvA in Asheet)
                {
                    
                    bool isExistA = false;

                    foreach (KeyValuePair<string, PopInfo> kvB in Bsheet)
                    {
                        string strSFZA = getBeforEightStr(kvA.Key);
                        string strSFZB = getBeforEightStr(kvB.Key);
                        if ((strSFZA).Equals(strSFZB))
                        {
                            commonSheet.Add(kvA.Key, kvA.Value);
                            isExistA = true;
                            break;
                        }
                    }
                    /*如果b中不存在*/
                    if (!isExistA) {
                        onlyASheet.Add(kvA.Key, kvA.Value);
                    }
                }
                /*第二次遍历*/
                foreach (KeyValuePair<string, PopInfo> kvB in Bsheet)
                {
                    bool isExistB = false;
                    foreach (KeyValuePair<string, PopInfo> kvA in Asheet)
                    {
                        string strSFZA = getBeforEightStr(kvA.Key);
                        string strSFZB = getBeforEightStr(kvB.Key);

                        if ((strSFZA).Equals(strSFZB))
                        {
                           
                            isExistB = true;
                            break;
                        }
                    }
                    if (!isExistB)
                    {
                        onlyBSheet.Add(kvB.Key, kvB.Value);
                    }
                }

                exportDATA(commonSheet, "commonSheet.xls");
                exportDATA(onlyASheet, "onlyASheet.xls");
                exportDATA(onlyBSheet, "onlyBSheet.xls");
                return true;
            }
            catch (Exception)
            {
                
                return false;
            }
        
        }

        private void exportDATA(Dictionary<string, PopInfo> dics, string fileName)
        { 
        
                 /*创建工作薄*/
                Workbook wb = new Workbook();
                /*创建样式*/
                Style style = wb.Styles[wb.Styles.Add()];
                /*设置单元格水平居中对齐和垂直居中对齐*/
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                /*新建工作表*/
                Worksheet ws = wb.Worksheets[0];
                int row = 0;
                foreach (KeyValuePair<string, PopInfo> kv in dics)
                {

                    string key = kv.Key;
                    //string num = Convert.ToString(kv.Value);
                    PopInfo pop = kv.Value;

                    ws.Cells[row, 0].PutValue(key);
                    ws.Cells[row, 1].PutValue(pop.getHBH());
                    ws.Cells[row, 2].PutValue(pop.getHKS());
                    row = row + 1;
                    
                }
             /*设置所有列为自适应列宽*/
                ws.AutoFitColumns();
                string path = GetAssemblyPath();
                string filePath = System.IO.Path.Combine(path, fileName);
                if (System.IO.File.Exists(filePath))
                {
                    System.IO.File.Delete(filePath);
                }
                FileStream fs = System.IO.File.Create(filePath);
                fs.Close();
                wb.Save(filePath);
        }
    }
}

这里实现的人口信息类如下所示。非常简单。

    class PopInfo
    {

        public string hbh;
        public int hks;

        public PopInfo() { }

        public PopInfo(string hbh, int hks)
        {
            this.hbh = hbh;
            this.hks = hks;
        }

        public string getHBH() {
            return this.hbh;
        }
        public int getHKS() {
            return this.hks;
        }
        public void setHKS(int strHKS)
        {
            this.hks = strHKS;
        }

        public void setHBH(string strHBH) {
            this.hbh = strHBH;
        }
    }

注意，这里使用的是c#编写的。后面的结果不好展示，这里给大家展示实现的窗口界面。