private string GenerateOuhuaFiles(List <PacketInfo> packetInfos, string folder)
{
try
{
string destExcelFile = CreateOutputFile(
Directory.GetParent(Application.ExecutablePath).FullName, OUHUA_TEMPLATE_FILENAME,
folder,
string.Format(OUHUA_EXCEL_OUTPUT_FILENAME, DateTime.Now.ToString("yyyyMMdd")));
#region error message
if (string.IsNullOrEmpty(destExcelFile))
{
MessageBox.Show(
this,
"Create excel file for Ohua failed.\nMaybe rainbow template file missed.\nMake sure the template file exists in the same folder with the executable file.",
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(string.Empty);
}
#endregion
Excel excel = new Excel(destExcelFile);
try
{
string packTitle = string.Empty;
string realkk = string.Empty;
string pingzhangEmail = string.Empty;
string weight = string.Empty;
string JHT = string.Empty;
string OttoBrennerStr = string.Empty;
string str4a = string.Empty;
string PLZ47877 = string.Empty;
string OrtWillich = string.Empty;
string TelNr021548839989 = string.Empty;
string deutschland = string.Empty;
string recipientName = string.Empty;
string address = string.Empty;
string address2 = string.Empty;
string address3 = string.Empty;
string postCode = string.Empty;
string provinceCity = string.Empty;
string china = string.Empty;
string phoneNumber = string.Empty;
string fullAddressCn = string.Empty;
string milkPowder = string.Empty;
string count = string.Empty;
string moneyAmount = string.Empty;
for (int i = 0; i < packetInfos.Count; i++)
{
PacketInfo pi = packetInfos[i];
packTitle += string.Format("'包裹单{0:00}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), i + 1);
//realkk += "'realkk'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
//pingzhangEmail += "'*****@*****.**'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
weight += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), ((float)pi.Weight / 1000).ToString("0.0"));
JHT += "'JHT International GmbH'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
OttoBrennerStr += "'Otto Brenner Str.'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
str4a += "'4a'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
PLZ47877 += "'47877'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
OrtWillich += "'Willich'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
TelNr021548839989 += "'02154 8839989'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
deutschland += "'Germany'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
recipientName += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.RecipientNameEn);
address += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.AddressEn);
address2 += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), " ");
address3 += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), " ");
postCode += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.PostCode);
provinceCity += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.ProvinceCityEn);
china += "'China'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
phoneNumber += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.PhoneNumber);
fullAddressCn += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.FullAddress);
milkPowder += "'Milk powder'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
count += "'10'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
moneyAmount += "'115.90'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
}
string endColumn = Excel.GetColumnIndex("D", packetInfos.Count - 1);
int row = 5;
//packTitle = "' ',' ',' '," + packTitle;
//excel.Insert("DHL申请表", string.Empty, packTitle);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), packTitle);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), JHT);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), OttoBrennerStr);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), str4a);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), PLZ47877);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), OrtWillich);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), TelNr021548839989);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), recipientName);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), address2);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), address);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), address2);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), postCode);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), address2);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), provinceCity);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), phoneNumber);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), china);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), weight);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), milkPowder);
excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), moneyAmount);
//excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), realkk);
//excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), pingzhangEmail);
////excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), deutschland);
////excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), address);
////excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), address2);
////excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), address3);
////excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), fullAddressCn);
////row++;
////row++;
////row++;
////excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), count);
////excel.Insert("DHL申请表", string.Format("D{0}:{1}{0}", row++, endColumn), weight);
}
catch (Exception ex)
{
MessageBox.Show(
this,
"Error occured during inserting information into excel for dealworthier.\n" + ex.ToString(),
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
excel.Close();
}
return(destExcelFile);
}
catch (Exception ex)
{
MessageBox.Show(
this,
"Error occured during generating files for rainbow:" + ex.ToString(),
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(string.Empty);
}
}
private string GenerateRainbowFiles(List <PacketInfo> packetInfos, string folder)
{
try
{
string destExcelFile = CreateOutputFile(
Directory.GetParent(Application.ExecutablePath).FullName, RAINBOW_TEMPLATE_FILENAME,
folder, RAINBOW_EXCEL_OUTPUT_FILENAME);
#region error message
if (string.IsNullOrEmpty(destExcelFile))
{
MessageBox.Show(
this,
"Create excel file for rainbow failed.\nMaybe rainbow template file missed.\nMake sure the template file exists in the same folder with the executable file.",
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(string.Empty);
}
#endregion
Excel rainbowExcel = new Excel(destExcelFile);
try
{
string packTitle = string.Empty;
string pingzhangEmail = string.Empty;
string realkk = string.Empty;
string JHT = string.Empty;
string OttoBrennerStr4a = string.Empty;
string PLZ47877 = string.Empty;
string OrtWillich = string.Empty;
string TelNr021548839989 = string.Empty;
string recipientName = string.Empty;
string address = string.Empty;
string postCode = string.Empty;
string provinceCity = string.Empty;
string phoneNumber = string.Empty;
string china = string.Empty;
string item = string.Empty;
string count = string.Empty;
string weight = string.Empty;
string moneyAmount = string.Empty;
string fullAddressCn = string.Empty;
for (int i = 0; i < packetInfos.Count; i++)
{
PacketInfo pi = packetInfos[i];
packTitle += string.Format("'包裹单{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), i + 1);
pingzhangEmail += "'*****@*****.**'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
realkk += "'realkk'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
JHT += "'JHT International GmbH'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
OttoBrennerStr4a += "'Otto Brenner Str.4a'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
PLZ47877 += "'47877'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
OrtWillich += "'Willich'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
TelNr021548839989 += "'02154 8839989'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
recipientName += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.RecipientNameEn);
address += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.AddressEn);
postCode += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.PostCode);
provinceCity += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.ProvinceCityEn);
phoneNumber += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.PhoneNumber);
china += "'China'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
item += "'Milk powder'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
count += "'10'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
weight += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), (pi.Weight / 1000).ToString("0"));
moneyAmount += "'115.90'" + (i >= packetInfos.Count - 1 ? string.Empty : ",");
fullAddressCn += string.Format("'{0}'" + (i >= packetInfos.Count - 1 ? string.Empty : ","), pi.FullAddress);
}
string endColumn = Excel.GetColumnIndex("C", packetInfos.Count - 1);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 1, endColumn), packTitle);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 2, endColumn), pingzhangEmail);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 3, endColumn), realkk);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 5, endColumn), JHT);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 6, endColumn), OttoBrennerStr4a);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 7, endColumn), PLZ47877);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 8, endColumn), OrtWillich);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 9, endColumn), TelNr021548839989);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 10, endColumn), recipientName);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 11, endColumn), address);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 13, endColumn), postCode);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 14, endColumn), provinceCity);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 15, endColumn), phoneNumber);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 16, endColumn), china);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 17, endColumn), item);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 18, endColumn), count);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 19, endColumn), weight);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 20, endColumn), moneyAmount);
rainbowExcel.Insert("Sheet1", string.Format("C{0}:{1}{0}", 21, endColumn), fullAddressCn);
}
finally
{
rainbowExcel.Close();
}
return(destExcelFile);
}
catch (Exception ex)
{
MessageBox.Show(
this,
"Error occured during generating files for rainbow:" + ex.ToString(),
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(string.Empty);
}
}