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