예제 #1
0
        private void OpenExcelButton_Click(object sender, EventArgs e)
        {
            if (this.openExcelFileDialog.ShowDialog() == DialogResult.OK)
            {
                int count = 0;
                xmlProgressBar.Visible = true;
                string fullFileName = openExcelFileDialog.FileName;
                string fileName     = fullFileName.Substring(fullFileName.LastIndexOf("\\") + 1);
                fileName = fileName.Substring(0, fileName.LastIndexOf("."));
                Excel.Application xlApp;
                Excel.Workbook    xlWorkBook;
                Excel.Worksheet   xlWorkSheet;
                Excel.Range       range;
                int           rCnt    = 0;
                FileIDHandler handler = new FileIDHandler();
                fileID     = handler.getFileID(fullFileName);
                xlApp      = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(fullFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetIndex);

                range = xlWorkSheet.UsedRange;
                XmlWriterSettings setting = new XmlWriterSettings();
                setting.Indent      = true;
                setting.IndentChars = "\t";
                setting.Encoding    = Encoding.UTF8;
                using (XmlWriter writer = XmlWriter.Create(outputFileFullName, setting))
                {
                    writer.WriteStartElement("InputData");
                    writer.WriteAttributeString("FileID", fileID.ToString());
                    writer.WriteStartElement("Units");
                    writer.WriteStartElement("Unit");
                    writer.WriteAttributeString("Name", fileName);
                    writer.WriteAttributeString("Type", "Job");
                    writer.WriteAttributeString("Priority", "1");
                    writer.WriteStartElement("Comment");
                    writer.WriteEndElement();// end element for Comment
                    writer.WriteStartElement("Product");
                    writer.WriteRaw("Sorting");
                    writer.WriteEndElement();// end element for Product
                    count = range.Rows.Count;
                    xmlProgressBar.Maximum = count;
                    for (rCnt = 2; rCnt <= count; rCnt++)
                    {
                        //progessValue = ((rCnt - 1) / count) * 100;
                        //Console.Write("progessValue: " + progessValue + Environment.NewLine);
                        xmlProgressBar.Value = (rCnt - 1);
                        var area       = (range.Cells[rCnt, 1] as Excel.Range).Value2.ToString();
                        var branch     = (range.Cells[rCnt, 2] as Excel.Range).Value2.ToString();
                        var cardNumber = (range.Cells[rCnt, 3] as Excel.Range).Value2.ToString();
                        var noOfBranch = (range.Cells[rCnt, 4] as Excel.Range).Value2.ToString();
                        writer.WriteStartElement("Unit");
                        string name = "Card" + (rCnt - 1).ToString("0000");
                        writer.WriteAttributeString("Name", name);
                        writer.WriteAttributeString("Type", "Card");
                        writer.WriteStartElement("DataFields");
                        //data field Area
                        writer.WriteStartElement("DataField");
                        writer.WriteAttributeString("Name", "Area");
                        writer.WriteStartElement("Value");
                        writer.WriteAttributeString("InputFormat", "Text");
                        writer.WriteRaw(area);
                        writer.WriteEndElement(); // end element for Value
                        writer.WriteEndElement(); // end element for DataField
                        //data field Branch
                        writer.WriteStartElement("DataField");
                        writer.WriteAttributeString("Name", "Branch");
                        writer.WriteStartElement("Value");
                        writer.WriteAttributeString("InputFormat", "Text");
                        writer.WriteRaw(branch);
                        writer.WriteEndElement(); // end element for Value
                        writer.WriteEndElement(); // end element for DataField
                        //data field CardNumber
                        writer.WriteStartElement("DataField");
                        writer.WriteAttributeString("Name", "CardNumber");
                        writer.WriteStartElement("Value");
                        writer.WriteAttributeString("InputFormat", "Text");
                        writer.WriteRaw(cardNumber);
                        writer.WriteEndElement(); // end element for Value
                        writer.WriteEndElement(); // end element for DataField
                        //data field No.ofBranch
                        writer.WriteStartElement("DataField");
                        writer.WriteAttributeString("Name", "No.ofBranch");
                        writer.WriteStartElement("Value");
                        writer.WriteAttributeString("InputFormat", "Text");
                        writer.WriteRaw(noOfBranch);
                        writer.WriteEndElement(); // end element for Value
                        writer.WriteEndElement(); // end element for DataField

                        writer.WriteEndElement(); // end element for DataFields
                        writer.WriteEndElement(); // end element for Unit
                    }
                    writer.WriteEndElement();     // end element for Unit at the top
                    writer.WriteEndElement();     // end element for Units
                    writer.WriteEndElement();     // end element for InputData
                    writer.WriteEndDocument();
                    writer.Flush();
                }
                xlWorkBook.Close(true, null, null);
                xlApp.Quit();
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                MessageBox.Show("读写完成!");
                xmlProgressBar.Value   = 0;
                xmlProgressBar.Visible = false;
            }
        }
예제 #2
0
        private void OpenExcelButton_Click(object sender, EventArgs e)
        {
            if (_colUPC <= 0 || _colStartRFIDNO <= 0 || _colQuantity <= 0)
            {
                MessageBox.Show("Please set UPC column or Start RFID number column or quantity column");
                return;
            }

            if (this.openExcelFileDialog.ShowDialog() == DialogResult.OK)
            {
                int rowCount = 0;
                int colCount = 0;
                xmlProgressBar.Visible = true;
                string fullFileName   = openExcelFileDialog.FileName;
                string fileName       = fullFileName.Substring(fullFileName.LastIndexOf("\\") + 1);
                string outputfileName = "";
                string filePath       = "";
                string folderPath     = "";

                fileName = fileName.Substring(0, fileName.LastIndexOf("."));
                Excel.Application xlApp;
                Excel.Workbook    xlWorkBook;
                Excel.Worksheet   xlWorkSheet;
                Excel.Range       range;
                int           rCnt    = 0;
                int           cCnt    = 0;
                string        EPC     = "";
                FileIDHandler handler = new FileIDHandler();
                fileID = handler.getFileID(fullFileName);


                xlApp       = new Excel.Application();
                xlWorkBook  = xlApp.Workbooks.Open(fullFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetIndex);
                try
                {
                    range = xlWorkSheet.UsedRange;

                    rowCount = range.Rows.Count;
                    colCount = range.Columns.Count;
                    xmlProgressBar.Maximum = rowCount;
                    headers = new string[colCount + 1];
                    values  = new string[colCount + 1];
                    for (cCnt = 1; cCnt <= colCount; cCnt++)
                    {
                        headers[cCnt - 1] = (range.Cells[1, cCnt] as Excel.Range).Value2.ToString();
                    }
                    headers[colCount] = "EPC";


                    for (rCnt = 2; rCnt <= rowCount; rCnt++)
                    {
                        //progessValue = ((rCnt - 1) / count) * 100;
                        //Console.Write("progessValue: " + progessValue + Environment.NewLine);
                        xmlProgressBar.Value = (rCnt - 1);

                        for (cCnt = 1; cCnt <= colCount; cCnt++)
                        {
                            var value = (range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
                            values[cCnt - 1] = value;
                        }


                        UPC            = values[_colUPC - 1];
                        serialNumber   = values[_colStartRFIDNO - 1];
                        quantity       = int.Parse(values[_colQuantity - 1]);
                        outputfileName = productName + "_Job_" + (rCnt - 1).ToString().PadLeft(2, '0') + "_" + quantity;
                        if (quantity <= 0)
                        {
                            MessageBox.Show("quantity is zero from excel file. it shouldn't happen");
                            return;
                        }
                        if (_colUPC16 > 0)
                        {
                            UPCCompanyPrefix = values[_colUPC16 - 1];
                        }
                        else
                        {
                            UPCCompanyPrefix = UPC.ToString().Substring(0, 6);
                        }
                        if (_colUPC711 > 0)
                        {
                            ItemReferenceNumber = values[_colUPC711 - 1];
                        }
                        else
                        {
                            ItemReferenceNumber = UPC.ToString().Substring(6, 5);
                        }

                        if (UPCCompanyPrefix.Length != 6 || ItemReferenceNumber.Length != 5)
                        {
                            MessageBox.Show("UPCCompanyPrefix or ItemReferenceNumber is not correct", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }

                        EPC = convertToEPC(UPCCompanyPrefix, ItemReferenceNumber, serialNumber);
                        if (String.IsNullOrWhiteSpace(EPC))
                        {
                            MessageBox.Show("EPC length is 0", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        values[colCount] = EPC;
                        folderPath       = outputFolderPath + "\\CSV";
                        if (!Directory.Exists(folderPath))
                        {
                            Directory.CreateDirectory(folderPath);
                        }
                        filePath = folderPath + "\\" + outputfileName + ".csv";
                        createAndSaveToCSV(filePath, values, quantity, headers);
                    }

                    MessageBox.Show("读写完成!");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    xlWorkBook.Close(true, null, null);
                    xlApp.Quit();
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
                    xmlProgressBar.Value   = 0;
                    xmlProgressBar.Visible = false;
                }
            }
        }
예제 #3
0
        private void OpenCsvButton_Click(object sender, EventArgs e)
        {
            if (this.openCsvFileDialog.ShowDialog() == DialogResult.OK)
            {
                string fullFileName = openCsvFileDialog.FileName;
                string fileName     = fullFileName.Substring(fullFileName.LastIndexOf("\\") + 1);
                fileName = fileName.Substring(0, fileName.LastIndexOf("."));
                StreamReader  sr = new StreamReader(fullFileName, csvEncoding);
                string        lineText;
                int           i       = 0;
                FileIDHandler handler = new FileIDHandler();
                fileID = handler.getFileID(fullFileName);
                xmlProgressBar.Visible = true;
                xmlProgressBar.Maximum = 100;
                xmlProgressBar.Value   = 50;
                XmlWriterSettings setting = new XmlWriterSettings();
                setting.Indent      = true;
                setting.IndentChars = "\t";
                setting.Encoding    = Encoding.UTF8;
                using (XmlWriter writer = XmlWriter.Create(outputFileFullName, setting))
                {
                    writer.WriteStartElement("InputData");
                    writer.WriteAttributeString("FileID", fileID.ToString());
                    writer.WriteStartElement("Units");
                    writer.WriteStartElement("Unit");
                    writer.WriteAttributeString("Name", fileName);
                    writer.WriteAttributeString("Type", "Job");
                    writer.WriteAttributeString("Priority", "1");
                    writer.WriteStartElement("Comment");
                    writer.WriteEndElement();// end element for Comment
                    writer.WriteStartElement("Product");
                    writer.WriteRaw("Sorting");
                    writer.WriteEndElement();// end element for Product

                    while ((lineText = sr.ReadLine()) != null)
                    {
                        i++;
                        if (i == 1)
                        {
                            continue;
                        }
                        if (string.IsNullOrWhiteSpace(lineText))
                        {
                            //the line is white space or empty line, just keep it

                            continue;
                        }
                        string[] segments   = lineText.Split(delimiter);
                        var      area       = segments[0];
                        var      branch     = segments[1];
                        var      cardNumber = segments[2];
                        var      noOfBranch = segments[3];
                        writer.WriteStartElement("Unit");
                        string name = "Card" + (i - 1).ToString("0000");
                        writer.WriteAttributeString("Name", name);
                        writer.WriteAttributeString("Type", "Card");
                        writer.WriteStartElement("DataFields");
                        //data field Area
                        writer.WriteStartElement("DataField");
                        writer.WriteAttributeString("Name", "Area");
                        writer.WriteStartElement("Value");
                        writer.WriteAttributeString("InputFormat", "Text");
                        writer.WriteRaw(area);
                        writer.WriteEndElement(); // end element for Value
                        writer.WriteEndElement(); // end element for DataField
                        //data field Branch
                        writer.WriteStartElement("DataField");
                        writer.WriteAttributeString("Name", "Branch");
                        writer.WriteStartElement("Value");
                        writer.WriteAttributeString("InputFormat", "Text");
                        writer.WriteRaw(branch);
                        writer.WriteEndElement(); // end element for Value
                        writer.WriteEndElement(); // end element for DataField
                        //data field CardNumber
                        writer.WriteStartElement("DataField");
                        writer.WriteAttributeString("Name", "CardNumber");
                        writer.WriteStartElement("Value");
                        writer.WriteAttributeString("InputFormat", "Text");
                        writer.WriteRaw(cardNumber);
                        writer.WriteEndElement(); // end element for Value
                        writer.WriteEndElement(); // end element for DataField
                        //data field No.ofBranch
                        writer.WriteStartElement("DataField");
                        writer.WriteAttributeString("Name", "No.ofBranch");
                        writer.WriteStartElement("Value");
                        writer.WriteAttributeString("InputFormat", "Text");
                        writer.WriteRaw(noOfBranch);
                        writer.WriteEndElement(); // end element for Value
                        writer.WriteEndElement(); // end element for DataField

                        writer.WriteEndElement(); // end element for DataFields
                        writer.WriteEndElement(); // end element for Unit
                    }
                    writer.WriteEndElement();     // end element for Unit at the top
                    writer.WriteEndElement();     // end element for Units
                    writer.WriteEndElement();     // end element for InputData
                    writer.WriteEndDocument();
                    writer.Flush();
                }
                sr.Close();
                xmlProgressBar.Value = 100;
                MessageBox.Show("读写完成!");
                xmlProgressBar.Value   = 0;
                xmlProgressBar.Visible = false;
            }
        }