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;
}
}
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;
}
}
}
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;
}
}
