public List <cls_sixzhuanjiagebiao_info> GetKEYnfo(string Alist)
{
List <cls_sixzhuanjiagebiao_info> MAPPINGResult = new List <cls_sixzhuanjiagebiao_info>();
try
{
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Application excelApp;
{
string path = Alist;
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook analyWK = excelApp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing,
"htc", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)analyWK.Worksheets[1];
Microsoft.Office.Interop.Excel.Range rng;
rng = WS.Range[WS.Cells[1, 1], WS.Cells[WS.UsedRange.Rows.Count, 46]];
int rowCount = WS.UsedRange.Rows.Count;
object[,] o = new object[1, 1];
o = (object[, ])rng.Value2;
int wscount = analyWK.Worksheets.Count;
clsCommHelp.CloseExcel(excelApp, analyWK);
for (int i = 2; i <= rowCount; i++)
{
cls_sixzhuanjiagebiao_info temp = new cls_sixzhuanjiagebiao_info();
#region 基础信息
//temp.touxing_B = "";
//if (o[i, 2] != null)
// temp.touxing_B = o[i, 2].ToString().Trim();
//if (temp.touxing_B == null || temp.touxing_B == "")
// continue;
//temp.zuzuangbizhong_C = "";
//if (o[i, 3] != null)
// temp.zuzuangbizhong_C = o[i, 3].ToString().Trim();
////卖场代码
//temp.shapianbizhong_D = "";
//if (o[i, 4] != null)
// temp.shapianbizhong_D = o[i, 4].ToString().Trim();
//temp.dianpiandanjia_E = "";
//if (o[i, 5] != null)
// temp.dianpiandanjia_E = o[i, 5].ToString().Trim();
//temp.handianpianqianjia_F = "";
//if (o[i, 6] != null)
// temp.handianpianqianjia_F = o[i, 6].ToString().Trim();
//temp.handianpiandunjia_G = "";
//if (o[i, 7] != null)
// temp.handianpiandunjia_G = o[i, 7].ToString().Trim();
//temp.guigexinghao_H = "";
//if (o[i, 8] != null)
// temp.guigexinghao_H = o[i, 8].ToString().Trim();
////卖场名称
//temp.guige_I = "";
//if (o[i, 9] != null)
// temp.guige_I = o[i, 9].ToString().Trim();
//temp.bizhong_J = "";
//if (o[i, 10] != null)
// temp.bizhong_J = o[i, 10].ToString().Trim();
//temp.ganjia_K = "";
//if (o[i, 11] != null)
// temp.ganjia_K = o[i, 11].ToString().Trim();
//temp.dunjia_L = "";
//if (o[i, 12] != null)
// temp.dunjia_L = o[i, 12].ToString().Trim();
//temp.yuanmei_M = "";
//if (o[i, 13] != null)
// temp.yuanmei_M = o[i, 13].ToString().Trim();
//temp.gongxu6_N = "";
//if (o[i, 14] != null)
// temp.gongxu6_N = o[i, 14].ToString().Trim();
//temp.yuanmei_O = "";
//if (o[i, 15] != null)
// temp.yuanmei_O = o[i, 15].ToString().Trim();
//temp.gongxu5_P = "";
//if (o[i, 16] != null)
// temp.gongxu5_P = o[i, 16].ToString().Trim();
//temp.shujin_Q = "";
//if (o[i, 17] != null)
// temp.shujin_Q = o[i, 17].ToString().Trim();
//temp.yunfei_R = "";
//if (o[i, 18] != null)
// temp.yunfei_R = o[i, 18].ToString().Trim();
//temp.gongxu4_S = "";
//if (o[i, 19] != null)
// temp.gongxu4_S = o[i, 19].ToString().Trim();
//temp.gongxu3_T = "";
//if (o[i, 20] != null)
// temp.gongxu3_T = o[i, 20].ToString().Trim();
//temp.gongxu2_U = "";
//if (o[i, 21] != null)
// temp.gongxu2_U = o[i, 21].ToString().Trim();
//temp.chengpinsi_V = "";
//if (o[i, 22] != null)
// temp.chengpinsi_V = o[i, 22].ToString().Trim();
//temp.shunhao_W = "";
//if (o[i, 23] != null)
// temp.shunhao_W = o[i, 23].ToString().Trim();
//temp.panyuan2_X = "";
//if (o[i, 24] != null)
// temp.panyuan2_X = o[i, 24].ToString().Trim();
//temp.gongxu1_Y = "";
//if (o[i, 25] != null)
// temp.gongxu1_Y = o[i, 25].ToString().Trim();
//temp.panyuan1_Z = "";
//if (o[i, 26] != null)
// temp.panyuan1_Z = o[i, 26].ToString().Trim();
//temp.shunhao_AA = "";
//if (o[i, 27] != null)
// temp.shunhao_AA = o[i, 27].ToString().Trim();
//temp.panyuan_AB = "";
//if (o[i, 28] != null)
// temp.panyuan_AB = o[i, 28].ToString().Trim();
#endregion
#region 2
#region 基础信息
temp.touxing_B = "";
if (o[i, 1] != null)
{
temp.touxing_B = o[i, 1].ToString().Trim();
}
temp.Order_id = "";
if (o[i, 2] != null)
{
temp.Order_id = o[i, 2].ToString().Trim();
}
temp.zuzuangbizhong_C = "";
if (o[i, 3] != null)
{
temp.zuzuangbizhong_C = o[i, 3].ToString().Trim();
}
if (temp.touxing_B == null || temp.touxing_B == "")
{
continue;
}
temp.shapianbizhong_D = "";
if (o[i, 4] != null)
{
temp.shapianbizhong_D = o[i, 4].ToString().Trim();
}
//卖场代码
temp.dianpiandanjia_E = "";
if (o[i, 5] != null)
{
temp.dianpiandanjia_E = o[i, 5].ToString().Trim();
}
temp.handianpianqianjia_F = "";
if (o[i, 6] != null)
{
temp.handianpianqianjia_F = String.Format("{0:N2}", Convert.ToDouble(Math.Round(Convert.ToDouble(o[i, 6].ToString()), 2).ToString())); //o[i, 6].ToString().Trim();
}
temp.guige_I = "";
if (o[i, 7] != null)
{
temp.guige_I = o[i, 7].ToString().Trim();
}
temp.bizhong_J = "";
if (o[i, 8] != null)
{
temp.bizhong_J = o[i, 8].ToString().Trim();
}
temp.ganjia_K = "";
if (o[i, 9] != null)
{
temp.ganjia_K = String.Format("{0:N2}", Convert.ToDouble(Math.Round(Convert.ToDouble(o[i, 9].ToString()), 2).ToString())); //o[i, 8].ToString().Trim();
}
temp.handianpiandunjia_G = "";
if (o[i, 10] != null)
{
temp.handianpiandunjia_G = String.Format("{0:N2}", Convert.ToDouble(Math.Round(Convert.ToDouble(o[i, 10].ToString()), 2).ToString())); //o[i, 8].ToString().Trim();
}
//o[i, 10].ToString().Trim();
temp.dunjia_L = "";
if (o[i, 11] != null)
{
temp.dunjia_L = o[i, 11].ToString().Trim();
}
#endregion
#endregion
MAPPINGResult.Add(temp);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: 01032" + ex);
return(null);
throw;
}
return(MAPPINGResult);
}
private void webBrowser1_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
{
try
{
#region 1
//object o = oWebBrowser;
//Object oDocument = o.GetType().InvokeMember("Document", BindingFlags.GetProperty, null, o, null);
//Object oApplication = o.GetType().InvokeMember("Application", BindingFlags.GetProperty, null, oDocument, null);
////Excel.Application eApp = (Excel.Application)oApplication;
////eApp.UserControl = true;
//Object refmissing = System.Reflection.Missing.Value;
//object[] args = new object[4];
//args[0] = SHDocVw.OLECMDID.OLECMDID_HIDETOOLBARS;
//args[1] = SHDocVw.OLECMDEXECOPT.OLECMDEXECOPT_DONTPROMPTUSER;
//args[2] = refmissing;
//args[3] = refmissing;
//object axWebBrowser = this.webBrowser1.ActiveXInstance;
//axWebBrowser.GetType().InvokeMember("ExecWB",
// BindingFlags.InvokeMethod, null, axWebBrowser, args);
//object Application = axWebBrowser.GetType().InvokeMember("Document",
// BindingFlags.GetProperty, null, axWebBrowser, null);
//Excel.Workbook wbb = (Excel.Workbook)oApplication;
//Excel.ApplicationClass excel = wbb.Application as Excel.ApplicationClass;
//Excel.Workbook wb = excel.Workbooks[1];
//Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet;
//ws.Cells.Font.Name = "Verdana";
//ws.Cells.Font.Size = 14;
//ws.Cells.Font.Bold = true;
//Excel.Range range = ws.Cells;
//Excel.Range oCell = range[10, 10] as Excel.Range;
//oCell.Value2 = "你好";
#endregion
#region is good
Microsoft.Office.Interop.Excel.Workbook wbb = null;
Object refmissing = System.Reflection.Missing.Value;
object[] args = new object[4];
args[0] = SHDocVw.OLECMDID.OLECMDID_HIDETOOLBARS;
args[1] = SHDocVw.OLECMDEXECOPT.OLECMDEXECOPT_DONTPROMPTUSER;
args[2] = refmissing;
args[3] = refmissing;
object axWebBrowser = this.webBrowser1.ActiveXInstance;
axWebBrowser.GetType().InvokeMember("ExecWB", BindingFlags.InvokeMethod, null, axWebBrowser, args);
object oApplication = axWebBrowser.GetType().InvokeMember("Document", BindingFlags.GetProperty, null, axWebBrowser, null);
wbb = (Microsoft.Office.Interop.Excel.Workbook)oApplication;
Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)wbb.Worksheets[1];
Microsoft.Office.Interop.Excel.Range rng;
rng = WS.Range[WS.Cells[1, 1], WS.Cells[WS.UsedRange.Rows.Count, 16]];
int rowCount = WS.UsedRange.Rows.Count;
object[,] o = new object[1, 1];
o = (object[, ])rng.Value2;
//Excel.Application eApp = (Excel.Application)oApplication;
// clsCommHelp.CloseExcel(eApp, wbb);
// for (int i = 2; i <= rowCount; i++)
{
cls_sixzhuanjiagebiao_info temp = new cls_sixzhuanjiagebiao_info();
//temp.touxing_B = "";
//if (o[i, 2] != null)
// temp.touxing_B = o[i, 2].ToString().Trim();// o[i, 2].ToString().Trim();
if (pass == "123")
{
Microsoft.Office.Interop.Excel.Range cloumnrng = WS.Range[WS.Cells[1, 1], WS.Cells[WS.UsedRange.Rows.Count, 1]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 2], WS.Cells[WS.UsedRange.Rows.Count, 2]];
cloumnrng.EntireColumn.Hidden = true;
cloumnrng = WS.Range[WS.Cells[1, 3], WS.Cells[WS.UsedRange.Rows.Count, 3]];
cloumnrng.EntireColumn.Hidden = true;
cloumnrng = WS.Range[WS.Cells[1, 4], WS.Cells[WS.UsedRange.Rows.Count, 4]];
cloumnrng.EntireColumn.Hidden = true;
cloumnrng = WS.Range[WS.Cells[1, 5], WS.Cells[WS.UsedRange.Rows.Count, 5]];
cloumnrng.EntireColumn.Hidden = true;
cloumnrng = WS.Range[WS.Cells[1, 6], WS.Cells[WS.UsedRange.Rows.Count, 6]];
cloumnrng.EntireColumn.Hidden = true;
cloumnrng = WS.Range[WS.Cells[1, 8], WS.Cells[WS.UsedRange.Rows.Count, 8]];
cloumnrng.EntireColumn.Hidden = true;
cloumnrng = WS.Range[WS.Cells[1, 9], WS.Cells[WS.UsedRange.Rows.Count, 9]];
cloumnrng.EntireColumn.Hidden = true;
cloumnrng = WS.Range[WS.Cells[1, 10], WS.Cells[WS.UsedRange.Rows.Count, 10]];
cloumnrng.EntireColumn.Hidden = true;
cloumnrng = WS.Range[WS.Cells[1, 11], WS.Cells[WS.UsedRange.Rows.Count, 11]];
cloumnrng.EntireColumn.Hidden = true;
}
else
{
Microsoft.Office.Interop.Excel.Range cloumnrng = WS.Range[WS.Cells[1, 1], WS.Cells[WS.UsedRange.Rows.Count, 1]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 2], WS.Cells[WS.UsedRange.Rows.Count, 2]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 3], WS.Cells[WS.UsedRange.Rows.Count, 3]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 4], WS.Cells[WS.UsedRange.Rows.Count, 4]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 5], WS.Cells[WS.UsedRange.Rows.Count, 5]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 6], WS.Cells[WS.UsedRange.Rows.Count, 6]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 8], WS.Cells[WS.UsedRange.Rows.Count, 8]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 9], WS.Cells[WS.UsedRange.Rows.Count, 9]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 10], WS.Cells[WS.UsedRange.Rows.Count, 10]];
cloumnrng.EntireColumn.Hidden = false;
cloumnrng = WS.Range[WS.Cells[1, 11], WS.Cells[WS.UsedRange.Rows.Count, 11]];
cloumnrng.EntireColumn.Hidden = false;
}
oBook = wbb;
//oApp = (Excel.Application)oApplication;
// Excel.Application eApp = (Excel.Application)oApplication;
oSheet = WS;
}
#endregion
//设置工作表保护密码，并指定不受密码保护（即允许用户编辑）的单元格区域
//WS.Protect("123", WS.Range["B2:AZ65535"], "");
//WS.Protect("123", true);
//WS.Protect("123", SheetProtectionType.All);
//Microsoft.Office.Interop.Excel.AllowEditRanges ranges = WS.Protection.AllowEditRanges;
//ranges.Add("Information", WS.Range["B2:E6"], Type.Missing);
////初始化一个工作簿并加载一个实例
//Spire.Xls.Workbook book = new Spire.Xls.Workbook();
//book.LoadFromFile("test.xlsx");
////获取工作簿中第一个工作表
//Spire.Xls.Worksheet sheet = book.Worksheets[0];
//sheet.AddAllowEditRange("AAA", sheet.Range["B2:E6"], "");
//sheet.Protect("AAA", SheetProtectionType.All);
}
catch (Exception ex)
{
toolStripLabel2.Text = "请关闭所有Excel，然后点击【强制刷新】按钮，如还出现异常再次点击【强制刷新】按钮";
MessageBox.Show("请关闭所有Excel，然后点击【强制刷新】按钮");
return;
throw;
}
}
