コード例 #1
0
        private void SetW(Dictionary <string, Dictionary <string, int> > mab)
        {
            try
            {
                wbook  = exl.Workbooks.Add();
                wsheet = wbook.Sheets["Sheet1"] as Worksheet;
                wsheet.get_Range("a1").Value = "部位";
                wsheet.get_Range("b1").Value = "模板编号";
                wsheet.get_Range("c1").Value = "安装图数量-清单数量";

                int row = 2;

                foreach (string item in mab.Keys)
                {
                    foreach (string item1 in mab[item].Keys)
                    {
                        wsheet.get_Range("a" + row.ToString()).Value = item;
                        wsheet.get_Range("b" + row.ToString()).Value = item1;
                        wsheet.get_Range("c" + row.ToString()).Value = mab[item][item1];
                        row += 1;
                    }
                }
                wsheet.Name = "差异表";
                exl.Visible = true;
                //  wbook.Windows.Application.Visible = true;
                //  wbook.Save();
            }
            catch (Exception E)
            {
                MyPlugin.ExceptionWrit(E);
            }
        }
コード例 #2
0
        public void SetV(Dictionary <string, Dictionary <string, int> > codes)
        {
            try
            {
                GetEndRow("a");

                int row = h;

                foreach (string item in codes.Keys)
                {
                    foreach (var item1 in codes[item].Keys)
                    {
                        wsheet.get_Range("a1").Value = item;
                        wsheet.get_Range("b1").Value = item1;
                        wsheet.get_Range("c1").Value = codes[item][item1];
                    }
                }

                wbook.Save();
            }
            catch (Exception E)
            {
                MyPlugin.ExceptionWrit(E);
            }
        }
コード例 #3
0
        public EXLg(ref Boolean hav)
        {
            try
            {
                int l = Process.GetProcessesByName("EXCEL").Length;
                if (l > 0)
                {
                    ExlApp exl1 = (ExlApp)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

                    foreach (Workbook item in exl1.Workbooks)
                    {
                        if (Regex.IsMatch(item.Name, "^总量\\.xls"))
                        {
                            Workbook wbook1 = item;
                            foreach (Worksheet item1 in wbook1.Sheets)
                            {
                                if (item1.Name == "核对数量")
                                {
                                    hav = true;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception E)
            {
                MyPlugin.ExceptionWrit(E);
            }
        }
コード例 #4
0
 public EXL()
 {
     try
     {
         int l = Process.GetProcessesByName("EXCEL").Length;
         if (l > 0)
         {
             exl         = ( ExlApp )System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
             exl.Visible = false;
         }
         else
         {
             exl         = new Microsoft.Office.Interop.Excel.Application( );
             exl.Visible = false;
         }
         string pa = "F:\\图纸编号";
         if (!Directory.Exists(pa))
         {
             Directory.CreateDirectory(pa);
             wbook = exl.Workbooks.Add( );
             wbook.SaveAs(pa + @"\图纸编号", XlFileFormat.xlOpenXMLWorkbook);
             wsheet = wbook.Sheets ["Sheet1"] as Worksheet;
         }
         else if (!File.Exists(pa + @"\图纸编号.xlsx"))
         {
             wbook = exl.Workbooks.Add( );
             wbook.SaveAs(pa + @"\图纸编号", XlFileFormat.xlWorkbookNormal);
             wsheet = wbook.Sheets ["Sheet1"] as Worksheet;
         }
         else
         {
             foreach (Workbook item in exl.Workbooks)
             {
                 if (item.Name == "图纸编号")
                 {
                     wbook = exl.Workbooks ["图纸编号"];
                     ope   = 1;
                 }
             }
             if (wbook == null)
             {
                 wbook = exl.Workbooks.Open(pa + @"\图纸编号.xlsx");
             }
             wsheet = wbook.Sheets ["Sheet1"] as Worksheet;
         }
         wsheet.get_Range("a1").Value = "图号";
         wsheet.get_Range("b1").Value = "模板编号";
     }
     catch (Exception E)
     {
         MyPlugin.ExceptionWrit(E);
     }
 }
コード例 #5
0
        public SortedSet <string> GetDar()
        {
            SortedSet <string> sed = new SortedSet <string> ( );

            try
            {
                string cl = "J";
                int    rh = 7;
                foreach (Worksheet item in wbook.Worksheets)
                {
                    wsheet = item;
                    object v = wsheet.get_Range("j7").Value;
                    if (v != null && v.ToString() == "图纸编号")
                    {
                        rh = 9;
                    }
                    else
                    {
                        rh = 7;
                        cl = "i";
                    }
                    GetEndRow("b");

                    for (int i = rh; i < h + 1; i++)
                    {
                        object dar = wsheet.get_Range(cl + i).Value;

                        if (dar != null)
                        {
                            string th = dar.ToString( );

                            if (!Regex.IsMatch(th, pattern:"^ZW-[CQPNZKJTF]-"))
                            {
                                sed.Add(dar.ToString( ));
                            }
                        }
                    }
                }
            }
            catch (Exception E)
            {
                MyPlugin.ExceptionWrit(E);
            }
            return(sed);
        }
コード例 #6
0
 // private static int ope = 0;
 // Dictionary<string, Dictionary<string, int>> mab = new Dictionary<string, Dictionary<string, int>>();
 public EXLf()
 {
     try
     {
         int l = Process.GetProcessesByName("EXCEL").Length;
         if (l > 0)
         {
             exl = (ExlApp)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
             //exl.Visible = false;
         }
         else
         {
             exl = new Microsoft.Office.Interop.Excel.Application();
             //exl.Visible = false;
         }
     }
     catch (Exception E)
     {
         MyPlugin.ExceptionWrit(E);
     }
 }
コード例 #7
0
        public SortedSet <string> GetDar1( )
        {
            SortedSet <string> sed = new SortedSet <string> ( );

            try
            {
                int rh = 7;
                foreach (Worksheet item in wbook.Worksheets)
                {
                    wsheet = item;
                    object v = wsheet.get_Range("j7").Value;
                    if (v != null && v.ToString( ) == "图纸编号")
                    {
                        rh = 9;
                    }
                    else
                    {
                        rh = 7;
                    }
                    GetEndRow("b");

                    for (int i = rh; i < h + 1; i++)
                    {
                        object dar = wsheet.get_Range("c" + i).Value;

                        if (dar != null)
                        {
                            string th = dar.ToString( );
                            sed.Add(dar.ToString( ));
                        }
                    }
                }
            }
            catch (Exception E)
            {
                MyPlugin.ExceptionWrit(E);
            }
            return(sed);
        }
コード例 #8
0
        private void SetW(Dictionary <string, Dictionary <string, int> > mab)
        {
            try
            {
                HSSFWorkbook workbook1 = new HSSFWorkbook();

                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                SummaryInformation         si  = PropertySetFactory.CreateSummaryInformation();

                workbook1.DocumentSummaryInformation = dsi;
                workbook1.SummaryInformation         = si;

                ISheet wsheet = workbook1.CreateSheet("差异表");

                wsheet.SetColumnWidth(2, 5000);

                IRow row = wsheet.CreateRow(0);

                row.Height = 300;

                row.CreateCell(0).SetCellValue("部位");
                row.CreateCell(1).SetCellValue("模板编号");
                row.CreateCell(2).SetCellValue("安装图数量-清单数量");

                int rowv = 1;

                foreach (string item in mab.Keys)
                {
                    foreach (string item1 in mab[item].Keys)
                    {
                        row        = wsheet.CreateRow(rowv);
                        row.Height = 300;

                        row.CreateCell(0).SetCellValue(item);
                        row.CreateCell(1).SetCellValue(item1);
                        row.CreateCell(2).SetCellValue(mab[item][item1]);
                        rowv += 1;
                    }
                }

                string posi = pa + "\\差异表.xls";

                FileStream fs = new FileStream(posi, FileMode.Create);

                workbook1.Write(fs);

                fs.Close();
                workbook1.Close();

                try
                {
                    ExlApp exl;
                    int    l = Process.GetProcessesByName("EXCEL").Length;
                    if (l > 0)
                    {
                        exl = (ExlApp)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
                    }
                    else
                    {
                        exl = new Microsoft.Office.Interop.Excel.Application();
                    }

                    exl.Workbooks.Open(posi);
                    exl.Visible = true;
                }
                catch (Exception E)
                {
                    MyPlugin.ExceptionWrit(E);
                }

                //exl.Visible = true;
                //  wbook.Windows.Application.Visible = true;
                //  wbook.Save();
            }
            catch (Exception E)
            {
                MyPlugin.ExceptionWrit(E);
            }
        }
コード例 #9
0
        public bool Calv(Dictionary <string, Dictionary <string, int> > mab1, string[] dirs)
        {
            try
            {
                foreach (string dir in dirs)
                {
                    string     extension = System.IO.Path.GetExtension(dir);
                    FileStream fs        = File.OpenRead(dir);

                    if (extension.Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        //把xlsx文件中的数据写入wk中
                        workbook = new XSSFWorkbook(fs);
                    }

                    fs.Close();

                    int cou = 0;

                    for (int i = 0; i < workbook.NumberOfSheets; i++)
                    {
                        ISheet sheet = workbook.GetSheetAt(i);
                        IRow   row   = sheet.GetRow(1);
                        string mbb   = row.GetCell(1).ToString();
                        if (mbb == "模板编号" && mab1.ContainsKey(sheet.SheetName))
                        {
                            int rowNu = sheet.LastRowNum;
                            for (int j = 2; j < rowNu; j++)
                            {
                                IRow rowA = sheet.GetRow(j);

                                string mb  = rowA.GetCell(1).ToString();
                                int    mbn = Convert.ToInt32(rowA.GetCell(2).ToString());

                                if (mab1[sheet.SheetName].ContainsKey(mb))
                                {
                                    cou = mab1[sheet.SheetName][mb] - mbn;

                                    if (cou != 0)
                                    {
                                        mab1[sheet.SheetName][mb] = cou;
                                    }
                                    else
                                    {
                                        mab1[sheet.SheetName].Remove(mb);
                                        continue;
                                    }
                                }
                                else
                                {
                                    cou = -mbn;

                                    if (cou != 0)
                                    {
                                        mab1[sheet.SheetName][mb] = cou;
                                    }
                                }
                            }
                        }
                        else if (mbb == "模板编号" && !mab1.ContainsKey(sheet.SheetName))
                        {
                            Dictionary <string, int> keyVa = new Dictionary <string, int>();
                            int rowNu = sheet.LastRowNum;

                            for (int j = 2; j < rowNu; j++)
                            {
                                IRow   rowA = sheet.GetRow(j);
                                string mb   = rowA.GetCell(1).ToString();
                                int    mbn  = Convert.ToInt32("-" + rowA.GetCell(2).ToString());
                                keyVa.Add(mb, mbn);
                            }
                            if (keyVa.Count != 0)
                            {
                                mab1.Add(sheet.SheetName, keyVa);
                            }
                        }
                        else
                        {
                            continue;
                        }
                    }
                }

                Dictionary <string, Dictionary <string, int> > mab1copy = new Dictionary <string, Dictionary <string, int> >(mab1);

                foreach (string item in mab1.Keys)
                {
                    if (mab1[item].Count == 0)
                    {
                        mab1copy.Remove(item);
                    }
                }
                if (mab1copy.Count != 0)
                {
                    SetW(mab1copy);
                    return(false);
                }
                return(true);
            }
            catch (Exception E)
            {
                MyPlugin.ExceptionWrit(E);
                return(false);
            }
        }
コード例 #10
0
        public bool evW(Dictionary <string, Dictionary <string, int> > mab1, string[] dirs)
        {
            try
            {
                exl.DisplayAlerts  = false;
                exl.ScreenUpdating = false;

                foreach (string dir in dirs)
                {
                    wbook = exl.Workbooks.Open(dir);

                    int cou = 0;

                    foreach (Worksheet item in wbook.Sheets)
                    {
                        wsheet = item;
                        string mbb = wsheet.get_Range("b2").Value;

                        if (mbb == "模板编号" && mab1.ContainsKey(wsheet.Name))
                        {
                            GetEndRow("b");
                            for (int i = 3; i < h; i++)
                            {
                                string mb  = wsheet.get_Range("b" + i.ToString()).Value;
                                int    mbn = Convert.ToInt32(wsheet.get_Range("c" + i.ToString()).Value);

                                if (mab1[wsheet.Name].ContainsKey(mb))
                                {
                                    cou = mab1[wsheet.Name][mb] - mbn;
                                    // mab1[wsheet.Name].Remove(mb);
                                    if (cou != 0)
                                    {
                                        mab1[wsheet.Name][mb] = cou;
                                        //  keyVa.Add(mb, cou);
                                    }
                                    else
                                    {
                                        mab1[wsheet.Name].Remove(mb);
                                        continue;
                                    }
                                }
                                else
                                {
                                    cou = -mbn;

                                    if (cou != 0)
                                    {
                                        mab1[wsheet.Name][mb] = cou;
                                        //keyVa.Add(mb, cou);
                                    }
                                }
                            }

                            // if (mab1[wsheet.Name].Count>0)
                            //  {
                            //      foreach (string item2 in mab1[wsheet.Name].Keys)
                            //     {
                            //         keyVa.Add(item2, mab1[wsheet.Name][item2]);
                            //      }
                            //   }
                            //      if (keyVa.Count != 0)
                            // {
                            //      mab.Add(wsheet.Name, keyVa);
                            //  }
                        }
                        else if (mbb == "模板编号" && !mab1.ContainsKey(wsheet.Name))
                        {
                            Dictionary <string, int> keyVa = new Dictionary <string, int>();

                            GetEndRow("b");
                            for (int i = 3; i < h; i++)
                            {
                                string mb  = wsheet.get_Range("b" + i.ToString()).Value;
                                int    mbn = Convert.ToInt32("-" + Convert.ToString(Convert.ToInt32(wsheet.get_Range("c" + i.ToString()).Value)));
                                keyVa.Add(mb, mbn);
                            }

                            if (keyVa.Count != 0)
                            {
                                mab1.Add(wsheet.Name, keyVa);
                                //mab.Add(wsheet.Name, keyVa);
                            }
                        }
                        else
                        {
                            continue;
                        }
                    }

                    wbook.Close();
                }

                exl.DisplayAlerts  = true;
                exl.ScreenUpdating = true;

                Dictionary <string, Dictionary <string, int> > mab1copy = new Dictionary <string, Dictionary <string, int> >(mab1);

                foreach (string item in mab1.Keys)
                {
                    if (mab1[item].Count == 0)
                    {
                        mab1copy.Remove(item);
                    }
                }

                if (mab1copy.Count != 0)
                {
                    SetW(mab1copy);
                    return(false);
                }
                return(true);
            }
            catch (Exception E)
            {
                exl.ScreenUpdating = true;
                exl.DisplayAlerts  = true;
                MyPlugin.ExceptionWrit(E);
                return(false);
            }
        }
コード例 #11
0
        public void setValueRange(Dictionary <string, int> dic)
        {
            try
            {
                int l = Process.GetProcessesByName("EXCEL").Length;
                if (l > 0)
                {
                    exl = (ExlApp)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

                    foreach (Workbook item2 in exl.Workbooks)
                    {
                        for (int k = 1; k < exl.Workbooks.Count + 1; k++)
                        {
                            if (Regex.IsMatch(exl.Workbooks.Item[k].Name, "^总量\\.xls"))
                            {
                                wbook = exl.Workbooks.Item[k];

                                for (int g = 1; g < wbook.Sheets.Count + 1; g++)
                                {
                                    if (wbook.Sheets.Item[g].Name == "核对数量")
                                    {
                                        wsheet = wbook.Sheets.Item[g];

                                        h = wsheet.get_Range("a65536").get_End(XlDirection.xlUp).Row;
                                        wsheet.get_Range("b1").Value = "清单数量";
                                        wsheet.get_Range("c1").Value = "安装图数量";
                                        for (int i = 2; i < h + 1; i++)
                                        {
                                            string code = (string)wsheet.get_Range("a" + i).Value;
                                            if (dic.ContainsKey(code))
                                            {
                                                wsheet.get_Range("c" + i).Value = dic[code];
                                                if (wsheet.get_Range("c" + i).Value != wsheet.get_Range("b" + i).Value)
                                                {
                                                    wsheet.get_Range("a" + i + ":" + "c" + i).Interior.Color = 255;
                                                }
                                                dic.Remove(code);
                                            }
                                            else
                                            {
                                                wsheet.get_Range("c" + i).Value = 0;
                                                wsheet.get_Range("a" + i + ":" + "c" + i).Interior.Color = 255;
                                            }
                                        }

                                        if (dic.Keys.Count > 0)
                                        {
                                            int j = 2;
                                            wsheet.get_Range("e1").Value = "模板编码(安装图有但清单没有)";
                                            wsheet.get_Range("f1").Value = "数量";
                                            foreach (string item in dic.Keys)
                                            {
                                                wsheet.get_Range("e" + j).Value = item;
                                                wsheet.get_Range("f" + j).Value = dic[item];
                                                wsheet.get_Range("e" + j + ":" + "f" + j).Interior.Color = 255;
                                                j += 1;
                                            }
                                        }
                                        exl.Application.DisplayAlerts = false;
                                        setFormat();

                                        return;
                                    }
                                    else if (g == wbook.Sheets.Count)
                                    {
                                        MessageBox.Show("工作表名称错误,请命名为‘核对数量’!");
                                    }
                                }
                                return;
                            }
                            else if (k == exl.Workbooks.Count)
                            {
                                MessageBox.Show("不存在名称为:‘总量’的工作薄");
                            }
                        }
                    }
                }
            }
            catch (Exception E)
            {
                MyPlugin.ExceptionWrit(E);
            }
            finally
            {
                exl.Application.DisplayAlerts = true;
            }

            wbook.Save();
        }