private void tsbtnExportHigo_Click(object sender, EventArgs e)
{
if (lvwOrders.SelectedItems.Count <= 0)
{
MessageBox.Show(this, "请选择需要导出的订单先.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
Cursor.Current = Cursors.WaitCursor;
try
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Files(*.xls)|*.xls|All Files(*.*)|*.*";
sfd.FileName = string.Format("buy{0}-dd.xls", DateTime.Now.ToString("yyyyMMdd"));
sfd.OverwritePrompt = true;
if (DialogResult.OK == sfd.ShowDialog(this))
{
string templateFilename = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "template-higo.xls");
File.Copy(templateFilename, sfd.FileName);
Egode.Excel excel = new Egode.Excel(sfd.FileName, Excel.OledbVersions.OLEDB40);
foreach (DangdangOrderListViewItem item in lvwOrders.SelectedItems)
{
DangdangAddressParser ai = new DangdangAddressParser(item.Order.Address);
//Trace.WriteLine(item.Order.Address);
//Trace.WriteLine(string.Format("{0}, {1}, {2}, {3}", ai.Province, ai.City, ai.District, ai.StreetAddress));
//Trace.WriteLine(string.Empty);
//continue;
object[] args = new object[] {
"SAL06", "NBBLK", "邮政国内小包", item.Order.OrderId,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty,
"否",
string.Empty, string.Empty,
string.Empty,
item.Order.RecipientName, item.Order.Mobile,
string.Empty,
ai.Province, ai.City, ai.District, ai.StreetAddress,
ProductInfo.GetProductByDangdangCode(item.Order.UniqueProductCode).NingboId,
string.Empty, string.Empty,
item.Order.ActualCount,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty,
"否",
string.Empty, string.Empty,
string.Format("{0},{1}", item.Order.RecipientName, item.Order.IdNumber), "支付宝", string.Empty,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty
};
excel.Insert("合作代发订单导入模板", string.Empty, args);
}
excel.Close();
}
}
finally
{
Cursor.Current = Cursors.Default;
}
}
private void UpdateShipmentNumberInPackingList(string packingListExcel, List <PdfPacketInfoEx> pdfPackets)
{
Cursor.Current = Cursors.WaitCursor;
Excel excel = null;
try
{
excel = new Excel(packingListExcel, true);
}
catch
{
MessageBox.Show(
this,
"Open Excel file of packing list failed.\nMake sure the Excel file was not opened and try again.", this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
try
{
DataSet ds = excel.Get("Sheet1", string.Empty);
if (null == ds)
{
return;
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
//Trace.WriteLine(dr.ItemArray[1].ToString());
string recipientNameCn = dr.ItemArray[1].ToString();
if (string.IsNullOrEmpty(recipientNameCn))
{
continue;
}
if (recipientNameCn.Equals("张秋兰"))
{
Trace.Write("");
}
string recipientNamePinyin = HanZiToPinYin.Convert(recipientNameCn);
PdfPacketInfoEx ppi = PdfPacketInfoEx.GetItem(recipientNamePinyin, pdfPackets, true);
if (null == ppi)
{
PdfPacketInfoEx ppi1 = new PdfPacketInfoEx(string.Empty, PacketTypes.Unknown, string.Empty, string.Empty, 0);
ppi1.MatchedRecipientName = recipientNameCn;
pdfPackets.Add(ppi1);
continue;
}
ppi.MatchedRecipientName = recipientNameCn;
try
{
excel.Update(
"Sheet1",
"运单号", string.Format("{0}:{1}", ppi.RecipientName, ppi.ShipmentNumber),
"序号", dr.ItemArray[0].ToString());
//"收货人", recipientNameCn);
ppi.Updated = true;
}
catch (Exception ex)
{
Trace.WriteLine(ex);
}
//Trace.WriteLine(string.Format("Matched: {0}, {1}, {2}, {3}", recipientNameCn, ppi.RecipientName, ppi.ShipmentNumber, ppi.Weight));
}
}
catch (Exception ex)
{
MessageBox.Show(this, "Error occured during udpate shipment number into excel file.\n" + ex.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
excel.Close();
Cursor.Current = Cursors.Default;
}
}
private void tsbtnImportDangdangOrders_Click(object sender, EventArgs e)
{
if (lvwOrders.Items.Count > 0)
{
DialogResult dr = MessageBox.Show(this, "当前列表中的订单信息将会被清除.\n是否加载订单文件?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (DialogResult.No == dr)
{
return;
}
}
Cursor.Current = Cursors.WaitCursor;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Files(*.xlsx)|*.xlsx|Excel Files 97-2003(*.xls)|*.xls|All Files(*.*)|*.*";
if (DialogResult.OK == ofd.ShowDialog(this))
{
Egode.Excel excel = null;
try
{
excel = new Egode.Excel(ofd.FileName, Excel.OledbVersions.OLEDB12);
DataSet ds = excel.Get("Sheet0", string.Empty);
lvwOrders.Items.Clear();
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
string orderId = row.ItemArray[1].ToString();
string sellerId = row.ItemArray[2].ToString();
string recipientName = row.ItemArray[3].ToString();
string idNumber = row.ItemArray[4].ToString();
string mobile = row.ItemArray[5].ToString();
string address = row.ItemArray[6].ToString();
string deliveryType = row.ItemArray[7].ToString();
string deliveryTime = row.ItemArray[8].ToString();
string paymentType = row.ItemArray[9].ToString();
float totalMoney = float.Parse(row.ItemArray[10].ToString()); ///
DateTime dealTime = DateTime.Parse(row.ItemArray[15].ToString());
DateTime payTime = string.IsNullOrEmpty(row.ItemArray[16].ToString()) ? DateTime.MinValue : DateTime.Parse(row.ItemArray[16].ToString());
string invoice = row.ItemArray[20].ToString();
string status = row.ItemArray[24].ToString();
float fee = float.Parse(row.ItemArray[26].ToString());
float tax = float.Parse(row.ItemArray[28].ToString());
string productCode = row.ItemArray[29].ToString();
int count = int.Parse(row.ItemArray[30].ToString());
float price = float.Parse(row.ItemArray[31].ToString());
string paymentId = row.ItemArray[32].ToString();
string device = row.ItemArray[33].ToString();
DateTime consigningTime = string.IsNullOrEmpty(row.ItemArray[17].ToString()) ? DateTime.MinValue : DateTime.Parse(row.ItemArray[17].ToString());
string shipmentCompany = row.ItemArray[21].ToString();
string shipmentNumber = row.ItemArray[22].ToString();
DangdangOrder o = new DangdangOrder(
orderId, sellerId,
recipientName, idNumber, mobile, address,
deliveryType, deliveryTime, paymentType, totalMoney,
dealTime, payTime, invoice, status, fee, tax, productCode, count, price,
paymentId,
device,
consigningTime, shipmentCompany, shipmentNumber);
lvwOrders.Items.Add(new DangdangOrderListViewItem(o));
}
}
finally
{
if (null != excel && excel.Opened)
{
excel.Close();
}
}
}
Cursor.Current = Cursors.Default;
}
private void btnLoad_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
try
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Files(*.xls)|*.xls|All Files(*.*)|*.*";
if (DialogResult.OK == ofd.ShowDialog(this))
{
Excel excel = new Excel(ofd.FileName, true, Excel.OledbVersions.OLEDB40);
try
{
List <string> tableNames = excel.GetTableNames();
if (null == tableNames || tableNames.Count <= 0)
{
MessageBox.Show(this, "Excel文件中未找到任何表.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
Ningbo.NingboTableColumnSelectorForm colSelectorForm = new Egode.Ningbo.NingboTableColumnSelectorForm(excel);
if (DialogResult.OK == colSelectorForm.ShowDialog(this))
{
DataSet ds = excel.Get(tableNames[0], string.Empty);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
if (string.IsNullOrEmpty(row[colSelectorForm.ColumnInfo.LogisticsCompany].ToString()) && string.IsNullOrEmpty(row[colSelectorForm.ColumnInfo.MailNumber].ToString()))
{
continue;
}
int count = int.Parse(row.ItemArray[colSelectorForm.ColumnInfo.Count].ToString());
List <SoldProductInfo> products = new List <SoldProductInfo>();
ProductInfo pi = null;
SoldProductInfo spi = null;
if (colSelectorForm.ColumnInfo.ProductNingboCode >= 0)
{
pi = ProductInfo.GetProductByNingboCode(row.ItemArray[colSelectorForm.ColumnInfo.ProductNingboCode].ToString());
if (null != pi)
{
spi = new SoldProductInfo(pi);
spi.Count = count;
products.Add(spi);
}
}
//NingboOrder no = new NingboOrder(
// row.ItemArray[2].ToString().Trim(), row.ItemArray[3].ToString().Trim(),
// row.ItemArray[4].ToString().Trim(), row.ItemArray[15].ToString().Trim(),
// row.ItemArray[16].ToString().Trim(), row.ItemArray[18].ToString().Trim(), row.ItemArray[19].ToString().Trim(),
// row.ItemArray[20].ToString().Trim(), row.ItemArray[21].ToString().Trim(),
// products, row.ItemArray[35].ToString().Trim());
NingboOrder no = new NingboOrder(
row.ItemArray[colSelectorForm.ColumnInfo.LogisticsCompany].ToString().Trim(), row.ItemArray[colSelectorForm.ColumnInfo.MailNumber].ToString().Trim(),
row.ItemArray[colSelectorForm.ColumnInfo.OrderId].ToString().Trim(), row.ItemArray[colSelectorForm.ColumnInfo.RecipientName].ToString().Trim(),
row.ItemArray[colSelectorForm.ColumnInfo.Mobile].ToString().Trim(), row.ItemArray[colSelectorForm.ColumnInfo.Province].ToString().Trim(), row.ItemArray[colSelectorForm.ColumnInfo.City].ToString().Trim(),
row.ItemArray[colSelectorForm.ColumnInfo.District].ToString().Trim(), row.ItemArray[colSelectorForm.ColumnInfo.StreetAddr].ToString().Trim(),
products, string.Empty, string.Empty); // last arg is alipay number.
NingboOrderListViewItem item = new NingboOrderListViewItem(no, 0, lvwNingboOrders.BackColor);
lvwNingboOrders.Items.Add(item);
if (null == spi)
{
if (colSelectorForm.ColumnInfo.ProductNingboCode >= 0)
{
item.SubItems[9].Text = row.ItemArray[colSelectorForm.ColumnInfo.ProductNingboCode].ToString();
}
if (colSelectorForm.ColumnInfo.Count >= 0)
{
item.SubItems[10].Text = row.ItemArray[colSelectorForm.ColumnInfo.Count].ToString();
}
}
}
}
/* Removed by KK on 2016/07/15.
* // Get column index for shipment number return from higo.
* int logisticsCompanyColIndex = -1;
* int shipmentNumberColIndex = -1;
* int taobaoOrderIdColIndex = -1;
* int recipientNameColIndex = -1;
* int mobileColIndex = -1;
* int provinceColIndex = -1;
* int cityColIndex = -1;
* int districtColIndex = -1;
* int streetAddrColIndex = -1;
* int productNingboCodeColIndex = -1;
* int productCountColIndex = -1;
* for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
* {
* System.Diagnostics.Trace.WriteLine(string.Format("column {0}: {1}", i, ds.Tables[0].Rows[0].ItemArray[i]));
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("快递公司"))
* logisticsCompanyColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("运单号"))
* shipmentNumberColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("网站订单编号"))
* taobaoOrderIdColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("收货人"))
* recipientNameColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("手机"))
* mobileColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("省份"))
* provinceColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("市"))
* cityColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("区"))
* districtColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("详细信息"))
* streetAddrColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Contains("商品编码"))
* productNingboCodeColIndex = i;
* if (ds.Tables[0].Rows[0].ItemArray[i].ToString().Trim().Equals("数量"))
* productCountColIndex = i;
* }
*
* int idInfoColIndex = -1;
* if (ds.Tables[0].Rows.Count >= 2)
* {
* Regex r = new Regex(@"\d{17}[\d|x|X]");
* for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
* {
* if (r.Match(ds.Tables[0].Rows[1].ItemArray[i].ToString()).Success)
* {
* idInfoColIndex = i;
* break;
* }
* }
* }
*
* if (-1 == shipmentNumberColIndex)
* {
* //MessageBox.Show(this, "没有找到\"运单号\"列, 请检查文件数据或联系KK~", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
* MessageBox.Show(this, "没有找到\"运单号\"列, 默认使用第4列(D列)作为运单号, 请自行确认数据无误!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
* shipmentNumberColIndex = 3;
* //return;
* }
* if (-1 == taobaoOrderIdColIndex)
* {
* MessageBox.Show(this, "没有找到\"网站订单编号\"列, 默认使用第5列(E列)作为订单编号, 请自行确认数据无误!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
* taobaoOrderIdColIndex = 4;
* //return;
* }
*
* for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
* {
* DataRow row = ds.Tables[0].Rows[i];
*
* ProductInfo pi = ProductInfo.GetProductByNingboCode(row.ItemArray[productNingboCodeColIndex].ToString());
* int count = int.Parse(row.ItemArray[productCountColIndex].ToString());
* SoldProductInfo spi = new SoldProductInfo(pi);
* spi.Count = count;
* List<SoldProductInfo> products = new List<SoldProductInfo>();
* products.Add(spi);
*
* //NingboOrder no = new NingboOrder(
* // row.ItemArray[2].ToString().Trim(), row.ItemArray[3].ToString().Trim(),
* // row.ItemArray[4].ToString().Trim(), row.ItemArray[15].ToString().Trim(),
* // row.ItemArray[16].ToString().Trim(), row.ItemArray[18].ToString().Trim(), row.ItemArray[19].ToString().Trim(),
* // row.ItemArray[20].ToString().Trim(), row.ItemArray[21].ToString().Trim(),
* // products, row.ItemArray[35].ToString().Trim());
* NingboOrder no = new NingboOrder(
* row.ItemArray[logisticsCompanyColIndex].ToString().Trim(), row.ItemArray[shipmentNumberColIndex].ToString().Trim(),
* row.ItemArray[taobaoOrderIdColIndex].ToString().Trim(), row.ItemArray[recipientNameColIndex].ToString().Trim(),
* row.ItemArray[mobileColIndex].ToString().Trim(), row.ItemArray[provinceColIndex].ToString().Trim(), row.ItemArray[19].ToString().Trim(),
* row.ItemArray[cityColIndex].ToString().Trim(), row.ItemArray[districtColIndex].ToString().Trim(),
* products, row.ItemArray[idInfoColIndex].ToString().Trim(), string.Empty); // last arg is alipay number.
* NingboOrderListViewItem item = new NingboOrderListViewItem(no, 0, lvwNingboOrders.BackColor);
* lvwNingboOrders.Items.Add(item);
* }
*/
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, this.Text, MessageBoxButtons.OK);
}
finally
{
excel.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, this.Text, MessageBoxButtons.OK);
}
finally
{
Cursor.Current = Cursors.Default;
}
}
private void btnExport_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
try
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Files(*.xls)|*.xls|All Files(*.*)|*.*";
sfd.FileName = string.Format("buy{0}.xls", DateTime.Now.ToString("yyyyMMdd"));
sfd.OverwritePrompt = true;
if (DialogResult.OK == sfd.ShowDialog(this))
{
string templateFilename = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "template-higo-20151228.xls");
File.Copy(templateFilename, sfd.FileName);
Excel excel = new Excel(sfd.FileName, Excel.OledbVersions.OLEDB40);
foreach (NingboOrder o in NingboOrder.Orders)
{
for (int i = 0; i < o.SoldProducts.Count; i++)
{
//object[] args = new object[]{
// "SAL06", "NBBLK", o.LogisticsCompany, o.TaobaoOrderId,
// string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty,
// "否",
// string.Empty, string.Empty,
// string.Empty,
// o.RecipientName, o.Mobile,
// string.Empty,
// o.Province, o.City, o.District, o.StreetAddr,
// o.SoldProducts[i].NingboId,
// string.Empty, string.Empty,
// o.SoldProducts[i].Count,
// string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty,
// "否",
// string.Empty, string.Empty,
// o.IdInfo,
// string.Format("支付宝,{0}", o.AlipayNumber),
// o.SoldProducts.Count > 1 ? string.Format("合并发货:{0}", o.RecipientName) : string.Empty,
// string.Empty, string.Empty, string.Empty, string.Empty, string.Empty};
//excel.Insert("合作代发订单导入模板", string.Empty, args);
// 根据实际支付金额计算单价、运费.
Order taobaoOrder = MainForm.Instance.GetOrder(o.TaobaoOrderId);
System.Diagnostics.Trace.Assert(null != taobaoOrder);
// v20151228.
object[] args = new object[] {
"SAL06", "NBBLK", o.LogisticsCompany, o.TaobaoOrderId,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty,
"否",
string.Empty, // 运费
string.Empty,
o.RecipientName, // 客户
o.RecipientName, o.Mobile,
string.Empty, // 邮编
o.Province, o.City, o.District, o.StreetAddr,
o.SoldProducts[i].NingboId,
string.Empty, string.Empty,
o.SoldProducts[i].Count,
"盒", //单位
"0.00", //单价
"00.00", // 总价
string.Empty, string.Empty, string.Empty,
"否",
string.Empty, string.Empty,
o.IdInfo,
string.Empty, // string.Format("支付宝,{0}", o.AlipayNumber),
o.SoldProducts.Count > 1 ? string.Format("合并发货:{0}", o.RecipientName) : string.Empty,
string.Empty, string.Empty, string.Empty,
"支付宝", // 支付方式
o.AlipayNumber, // 支付流水号
taobaoOrder.TotalMoney.ToString("0.00")
}; // 支付金额
excel.Insert("订单导入模板", string.Empty, args);
}
}
excel.Close();
DialogResult dr = MessageBox.Show(this, "导出数据成功.\n确认数据无误后点击Yes同步订单状态至服务器, 否则点击No退出.", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (DialogResult.Yes == dr)
{
UpdateStatusToServer(NingboOrder.Orders);
}
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, this.Text, MessageBoxButtons.OK);
}
finally
{
Cursor.Current = Cursors.Default;
}
}
private void UpdateShipmentNumberInPackingList(string packingListExcel)
{
Cursor.Current = Cursors.WaitCursor;
Excel excel = null;
try
{
excel = new Excel(packingListExcel, true);
}
catch
{
MessageBox.Show(
this,
"Open Excel file of packing list failed.\nMake sure the Excel file was not opened and try again.", this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
try
{
DataSet ds = excel.Get("Sheet1", string.Empty);
if (null == ds)
{
return;
}
for (int i = 1; i <= ds.Tables[0].Rows.Count; i++)
{
excel.Insert("Sheet1", string.Format("G{0}:G{0}", i), string.Format("'x{0}'", Guid.NewGuid().ToString()));
}
excel.Close();
System.Threading.Thread.Sleep(500);
Application.DoEvents();
}
catch (Exception ex)
{
Trace.WriteLine(ex);
}
try
{
excel = new Excel(packingListExcel, true);
DataSet ds = excel.Get("Sheet1", string.Empty);
if (null == ds)
{
return;
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
//Trace.WriteLine(dr.ItemArray[1].ToString());
string recipientNameCn = dr.ItemArray[1].ToString();
if (string.IsNullOrEmpty(recipientNameCn))
{
continue;
}
PdfPacketInfoEx ppi = PdfPacketInfoEx.GetItemByRecipientName(recipientNameCn, _packetInfos, true);
if (null == ppi)
{
PdfPacketInfoEx ppi1 = new PdfPacketInfoEx(string.Empty, PacketTypes.Unknown, string.Empty, string.Empty, string.Empty, 0, string.Empty, string.Empty);
ppi1.MatchedRecipientName = recipientNameCn;
_packetInfos.Add(ppi1);
lvwPdfPacketInfos.Items.Add(new PdfPacketInfoListViewItem(ppi1));
continue;
}
ppi.MatchedRecipientName = recipientNameCn;
try
{
excel.Update(
"Sheet1",
"运单号", string.Format("{0}:{1}", ppi.RecipientName, ppi.ShipmentNumber),
//"序号", dr.ItemArray[0].ToString());
//"收货人", recipientNameCn);
//"序号", dr.ItemArray[0].ToString());
"reserved", dr.ItemArray[6].ToString());
ppi.Updated = true;
//Trace.WriteLine(dr.ItemArray[0].ToString());
//if (dr.ItemArray[0].ToString().Equals("28"))
// Trace.WriteLine("");
}
catch (Exception ex)
{
Trace.WriteLine(ex);
}
//Trace.WriteLine(string.Format("Matched: {0}, {1}, {2}, {3}", recipientNameCn, ppi.RecipientName, ppi.ShipmentNumber, ppi.Weight));
}
}
catch (Exception ex)
{
MessageBox.Show(this, "Error occured during udpate shipment number into excel file.\n" + ex.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
excel.Close();
Cursor.Current = Cursors.Default;
}
}
private string GeneratePackingListExcelFile(List <PacketInfo> packetInfos, string folder)
{
// Generate list for JHT.
string destPackingListJHT = CreateOutputFile(
Directory.GetParent(Application.ExecutablePath).FullName, PACKING_LIST_TEMPLATE,
folder, string.Format(PACKING_LIST_OUTPUT_JHT, DateTime.Now.ToString("yyyyMMdd")));
if (string.IsNullOrEmpty(destPackingListJHT))
{
#region error message
MessageBox.Show(
this,
"Create excel file for shipping list for JHT failed.\nMaybe the template file missed.\nMake sure the template file exists in the same folder with the executable file.",
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
#endregion
return(string.Empty);
}
Excel excelPackingListJHT = new Excel(destPackingListJHT);
try
{
for (int i = 0; i < packetInfos.Count; i++)
{
PacketInfo pi = packetInfos[i];
string[] products = pi.ProductInfo.Split(new char[] { '\r', '\n' });
for (int j = 0; j < products.Length; j++)
{
if (string.IsNullOrEmpty(products[j].Trim()))
{
continue;
}
string[] productDetails = products[j].Split(';');
object[] values = new object[] {
j == 0 ? (i + 1).ToString():" ",
j == 0 ? pi.RecipientNameCn:" ",
j == 0 ? "000000000000":" ",
productDetails[0].Trim(), productDetails[1].Trim(),
productDetails.Length >= 3 ? productDetails[2].Trim() : string.Empty
};
excelPackingListJHT.Insert("Sheet1", string.Empty, values);
}
}
}
catch (Exception ex)
{
#region error message
MessageBox.Show(
this,
"Error occured during write data into excel file for packing:" + ex.ToString(),
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
#endregion
}
finally
{
excelPackingListJHT.Close();
}
return(destPackingListJHT);
}
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);
}
}
private string GenerateSupermarketFiles(List <PacketInfo> packetInfos, string folder)
{
try
{
string destExcelFile = CreateOutputFile(
Directory.GetParent(Application.ExecutablePath).FullName, SUPERMARKET_TEMPLATE_FILENAME,
folder, SUPERMARKET_EXCEL_OUTPUT_FILENAME);
#region error message
if (string.IsNullOrEmpty(destExcelFile))
{
MessageBox.Show(
this,
"Create excel file for supermarket failed.\nMaybe supermarket 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
// Write data into excel.
Excel supermarketExcel = new Excel(destExcelFile);
try
{
for (int i = 0; i < packetInfos.Count; i++)
{
PacketInfo pi = packetInfos[i];
if (!supermarketExcel.Insert("Sheet1", string.Empty, CreatePacketInfoSupermarketValues(i + 1, pi)))
{
#region error message
MessageBox.Show(
this,
"Error occured during write data into excel file for supermarket:" + pi.RecipientNameCn,
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
#endregion
return(string.Empty);
}
}
}
catch (Exception ex)
{
#region error message
MessageBox.Show(
this,
"Error occured during write data into excel file for supermarket:" + ex.ToString(),
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
#endregion
return(string.Empty);
}
finally
{
supermarketExcel.Close();
}
return(destExcelFile);
}
catch (Exception ex)
{
MessageBox.Show(
this,
"Error occured during generating files for supermarket:" + ex.ToString(),
this.Text,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(string.Empty);
}
}