public void generateExcelToCheck(List <ReceiveOrderStruct> StockCheckList, string startTime, string endTime) { List <string> titleList = new List <string>(); List <Object> contentList = new List <object>(); titleList.Add("订单号"); titleList.Add("跟踪条码"); titleList.Add("客户料号"); titleList.Add("8S码"); titleList.Add("DPK类型"); titleList.Add("MAC"); titleList.Add("MPN"); titleList.Add("MB描述"); foreach (ReceiveOrderStruct stockcheck in StockCheckList) { ExportExcelContent ctest1 = new ExportExcelContent(); List <string> ct1 = new List <string>(); ct1.Add(stockcheck.orderNo); ct1.Add(stockcheck.trackno); ct1.Add(stockcheck.customMaterialNo); ct1.Add(stockcheck.custom_serial_no); ct1.Add(stockcheck.dpktype); ct1.Add(stockcheck.mac); ct1.Add(stockcheck.mpn); ct1.Add(stockcheck.mbdescribe); ctest1.contentArray = ct1; contentList.Add(ctest1); } Untils.createExcel("D:\\收货单信息" + startTime.Replace('/', '-') + "-" + endTime.Replace('/', '-') + ".xlsx", titleList, contentList); }
public static void generateExcelToCheck(List <StockCheck> StockCheckList) { List <string> titleList = new List <string>(); List <Object> contentList = new List <object>(); titleList.Add("料号"); titleList.Add("数量"); titleList.Add("库房"); titleList.Add("储位"); titleList.Add("描述"); titleList.Add("真实数量"); foreach (StockCheck stockcheck in StockCheckList) { ExportExcelContent ctest1 = new ExportExcelContent(); List <string> ct1 = new List <string>(); ct1.Add(stockcheck.material_no); ct1.Add(stockcheck.num); ct1.Add(stockcheck.house); ct1.Add(stockcheck.place); ct1.Add(stockcheck.describe); ct1.Add(""); ctest1.contentArray = ct1; contentList.Add(ctest1); } Untils.createExcel("D:\\test.xlsx", titleList, contentList); }
public void generateExcelToCheck(List <BgaOutStruct> StockCheckList, string startTime, string endTime) { List <string> titleList = new List <string>(); List <Object> contentList = new List <object>(); titleList.Add("MPN"); titleList.Add("BGA简称"); titleList.Add("BGA描述"); titleList.Add("库位"); titleList.Add("出库数量"); titleList.Add("备注"); titleList.Add("领用人"); titleList.Add("日期"); foreach (BgaOutStruct stockcheck in StockCheckList) { ExportExcelContent ctest1 = new ExportExcelContent(); List <string> ct1 = new List <string>(); ct1.Add(stockcheck.mpn); ct1.Add(stockcheck.bga_brief); ct1.Add(stockcheck.bga_describe); ct1.Add(stockcheck.stock_place); ct1.Add(stockcheck.out_number); ct1.Add(stockcheck.note); ct1.Add(stockcheck.taker); ct1.Add(stockcheck.input_date); ctest1.contentArray = ct1; contentList.Add(ctest1); } Untils.createExcel("D:\\BGA出库信息" + startTime.Replace('/', '-') + "-" + endTime.Replace('/', '-') + ".xlsx", titleList, contentList); }
public void generateExcelToCheck(List <MaterialConsumeStruct> StockCheckList, string startTime, string endTime) { List <string> titleList = new List <string>(); List <Object> contentList = new List <object>(); titleList.Add("MPN"); titleList.Add("描述"); titleList.Add("存储位置"); titleList.Add("消耗数量"); titleList.Add("当前库存数量"); foreach (MaterialConsumeStruct stockcheck in StockCheckList) { ExportExcelContent ctest1 = new ExportExcelContent(); List <string> ct1 = new List <string>(); ct1.Add(stockcheck.mpn); ct1.Add(stockcheck.brief); ct1.Add(stockcheck.stock_place); ct1.Add(stockcheck.out_number); ct1.Add(stockcheck.left_number); ctest1.contentArray = ct1; contentList.Add(ctest1); } Untils.createExcel("D:\\材料消耗信息" + startTime.Replace('/', '-') + "-" + endTime.Replace('/', '-') + ".xlsx", titleList, contentList); }
public void generateExcelToCheck(List <DpkStruct> StockCheckList, string startTime, string endTime) { List <string> titleList = new List <string>(); List <Object> contentList = new List <object>(); titleList.Add("DPK类别"); titleList.Add("KEYPN"); titleList.Add("KEYID"); titleList.Add("KEYSERIAL"); titleList.Add("状态"); titleList.Add("烧录日期"); titleList.Add("客户序号"); // dpk_type NVARCHAR(128) NOT NULL, /*DPK类别*/ //KEYPN NVARCHAR(128) NOT NULL, /*KEYPN*/ //KEYID NVARCHAR(128) NOT NULL, /*KEYID*/ //KEYSERIAL NVARCHAR(128) NOT NULL, /*KEYSERIAL*/ //_status NVARCHAR(128) NOT NULL, /*状态*/ //burn_date date,/*烧录日期*/ //custom_serial_no NVARCHAR(128) /*客户序号*/ foreach (DpkStruct stockcheck in StockCheckList) { ExportExcelContent ctest1 = new ExportExcelContent(); List <string> ct1 = new List <string>(); ct1.Add(stockcheck.dpk_type); ct1.Add(stockcheck.KEYPN); ct1.Add(stockcheck.KEYID); ct1.Add(stockcheck.KEYSERIAL); ct1.Add(stockcheck._status); ct1.Add(stockcheck.burn_date); ct1.Add(stockcheck.custom_serial_no); ctest1.contentArray = ct1; contentList.Add(ctest1); } Untils.createExcel("D:\\DPK信息" + startTime.Replace('/', '-') + "-" + endTime.Replace('/', '-') + ".xlsx", titleList, contentList); }
public void generateExcelToCheck(List <RepairRecordStruct> repairRecordList, string startTime, string endTime) { List <string> titleList = new List <string>(); List <Object> contentList = new List <object>(); titleList.Add("跟踪条码"); titleList.Add("厂商"); titleList.Add("客户别"); titleList.Add("收货日期"); titleList.Add("MB描述"); titleList.Add("MB简称"); titleList.Add("客户序号"); titleList.Add("厂商序号"); titleList.Add("MPN"); titleList.Add("维修人"); titleList.Add("维修次数"); int repairLenght = 5; int bgaLength = 3; int smtLength = 10; for (int i = 1; i <= repairLenght; i++) { titleList.Add("维修原因" + i); titleList.Add("维修动作" + i); titleList.Add("维修结果" + i); titleList.Add("维修日期" + i); } for (int i = 1; i <= bgaLength; i++) { titleList.Add("BGA MPN" + i); titleList.Add("BGA类型" + i); titleList.Add("BGA简称" + i); titleList.Add("BGA 位置" + i); titleList.Add("BGA_mbfa1" + i); titleList.Add("BGAShortCut" + i); } for (int i = 1; i <= smtLength; i++) { titleList.Add("SMT MPN" + i); titleList.Add("SMT位置" + i); titleList.Add("SMT数量" + i); } foreach (RepairRecordStruct repaircheck in repairRecordList) { ExportExcelContent ctest1 = new ExportExcelContent(); List <string> ct1 = new List <string>(); ct1.Add(repaircheck.track_serial_no); ct1.Add(repaircheck.vendor); ct1.Add(repaircheck.product); ct1.Add(repaircheck.receivedate); ct1.Add(repaircheck.mb_describe); ct1.Add(repaircheck.mb_brief); ct1.Add(repaircheck.custom_serial_no); ct1.Add(repaircheck.vendor_serail_no); ct1.Add(repaircheck.mpn); ct1.Add(repaircheck.repairer); ct1.Add(repaircheck.repair_Num); for (int i = 0; i < repairLenght; i++) { if (i < repaircheck.subRecords.Count) { ct1.Add(repaircheck.subRecords[i].fault_describe); ct1.Add(repaircheck.subRecords[i]._action); ct1.Add(repaircheck.subRecords[i].repair_result); ct1.Add(repaircheck.subRecords[i].repair_date); } else { ct1.Add(""); ct1.Add(""); ct1.Add(""); ct1.Add(""); } } for (int i = 0; i < bgaLength; i++) { if (i < repaircheck.bgaRecords.Count) { ct1.Add(repaircheck.bgaRecords[i].bgampn); ct1.Add(repaircheck.bgaRecords[i].bgatype); ct1.Add(repaircheck.bgaRecords[i].bgabrief); ct1.Add(repaircheck.bgaRecords[i].bga_place); ct1.Add(repaircheck.bgaRecords[i].bgambfa1); ct1.Add(repaircheck.bgaRecords[i].bgashort_cut); } else { ct1.Add(""); ct1.Add(""); ct1.Add(""); ct1.Add(""); ct1.Add(""); ct1.Add(""); } } for (int i = 0; i < smtLength; i++) { if (i < repaircheck.smtRecords.Count) { ct1.Add(repaircheck.smtRecords[i].smtMpn); ct1.Add(repaircheck.smtRecords[i].smtplace); ct1.Add(repaircheck.smtRecords[i].smtNum); } else { ct1.Add(""); ct1.Add(""); ct1.Add(""); } } ctest1.contentArray = ct1; contentList.Add(ctest1); } Untils.createExcel("D:\\维修记录" + startTime.Replace('/', '-') + "-" + endTime.Replace('/', '-') + ".xlsx", titleList, contentList); }
private void exportToExcel_Click(object sender, EventArgs e) { if (this.pathTextBox.Text == "") { MessageBox.Show("目的文件夹为空"); return; } if (this.datasourceComboBox.Text == "") { MessageBox.Show("源数据库为空!"); return; } DateTime start = Convert.ToDateTime(this.dateTimePickerstart.Value.Date.ToString("yyyy/MM/dd")); DateTime end = Convert.ToDateTime(this.dateTimePickerend.Value.Date.ToString("yyyy/MM/dd")); if (DateTime.Compare(start, end) > 0) //判断日期大小 { MessageBox.Show("开始日期大于结束"); return; } this.Cursor = Cursors.WaitCursor; string startTime = this.dateTimePickerstart.Value.ToString("yyyy/MM/dd"); string endTime = this.dateTimePickerend.Value.ToString("yyyy/MM/dd"); List <string> titleList = new List <string>(); List <Object> contentList = new List <object>(); string sqlCmd = "select * from " + currentTableName; if (currentTableName == "receiveOrder") { sqlCmd += " where receivedate between '" + start + "' and '" + end + "'"; titleList.Add("ID"); titleList.Add("厂商"); titleList.Add("客户别"); titleList.Add("订单编号"); titleList.Add("客户料号"); titleList.Add("客户物料描述"); titleList.Add("订单数量"); titleList.Add("MB简称"); titleList.Add("厂商料号"); titleList.Add("制单人"); titleList.Add("制单时间"); titleList.Add("收货数量"); titleList.Add("收货日期"); titleList.Add("订单状态"); titleList.Add("仓库别"); titleList.Add("还货数量"); titleList.Add("申报单位"); titleList.Add("报关单号"); titleList.Add("申请单号"); titleList.Add("Cid数量"); } else if (currentTableName == "cidRecord") { sqlCmd += " where inputdate between '" + start + "' and '" + end + "'"; titleList.Add("ID"); titleList.Add("跟踪条码"); titleList.Add("厂商"); titleList.Add("客户别"); titleList.Add("订单编号"); titleList.Add("客户料号"); titleList.Add("客户序号"); titleList.Add("MB简称"); titleList.Add("厂商料号"); titleList.Add("收货日期"); titleList.Add("客户故障"); titleList.Add("客责类别"); titleList.Add("客责描述"); titleList.Add("短路电压"); titleList.Add("录入人"); titleList.Add("录入日期"); } else if (currentTableName == "LCFC_MBBOM_table") { //sqlCmd = "select top 1000 * from " + currentTableName; titleList.Add("ID"); titleList.Add("日期"); titleList.Add("厂商"); titleList.Add("客户别"); titleList.Add("MB简称"); titleList.Add("MPN"); titleList.Add("材料MPN"); titleList.Add("料盒位置"); titleList.Add("物料描述"); titleList.Add("用料数量"); titleList.Add("L1"); titleList.Add("L2"); titleList.Add("L3"); titleList.Add("L4"); titleList.Add("L5"); titleList.Add("L6"); titleList.Add("L7"); titleList.Add("L8"); } else { MessageBox.Show("报表还没有制作!"); this.Cursor = Cursors.Default; return; } try { SqlConnection conn = new SqlConnection(Constlist.ConStr); conn.Open(); if (conn.State == ConnectionState.Open) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = sqlCmd; SqlDataReader querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { ExportExcelContent ctest1 = new ExportExcelContent(); List <string> ct1 = new List <string>(); if (currentTableName == "receiveOrder") { ct1.Add(querySdr[0].ToString()); ct1.Add(querySdr[1].ToString()); ct1.Add(querySdr[2].ToString()); ct1.Add(querySdr[3].ToString()); ct1.Add(querySdr[4].ToString()); ct1.Add(querySdr[5].ToString()); ct1.Add(querySdr[6].ToString()); ct1.Add(querySdr[7].ToString()); ct1.Add(querySdr[8].ToString()); ct1.Add(querySdr[9].ToString()); ct1.Add(querySdr[10].ToString()); ct1.Add(querySdr[11].ToString()); ct1.Add(querySdr[12].ToString()); ct1.Add(querySdr[13].ToString()); ct1.Add(querySdr[14].ToString()); ct1.Add(querySdr[15].ToString()); ct1.Add(querySdr[16].ToString()); ct1.Add(querySdr[17].ToString()); ct1.Add(querySdr[18].ToString()); ct1.Add(querySdr[19].ToString()); } else if (currentTableName == "cidRecord") { ct1.Add(querySdr[0].ToString()); ct1.Add(querySdr[1].ToString()); ct1.Add(querySdr[2].ToString()); ct1.Add(querySdr[3].ToString()); ct1.Add(querySdr[4].ToString()); ct1.Add(querySdr[5].ToString()); ct1.Add(querySdr[6].ToString()); ct1.Add(querySdr[7].ToString()); ct1.Add(querySdr[8].ToString()); ct1.Add(querySdr[9].ToString()); ct1.Add(querySdr[10].ToString()); ct1.Add(querySdr[11].ToString()); ct1.Add(querySdr[12].ToString()); ct1.Add(querySdr[13].ToString()); ct1.Add(querySdr[14].ToString()); ct1.Add(querySdr[15].ToString()); } else if (currentTableName == "LCFC_MBBOM_table") { ct1.Add(querySdr[0].ToString()); ct1.Add(querySdr[1].ToString()); ct1.Add(querySdr[2].ToString()); ct1.Add(querySdr[3].ToString()); ct1.Add(querySdr[4].ToString()); ct1.Add(querySdr[5].ToString()); ct1.Add(querySdr[6].ToString()); ct1.Add(querySdr[7].ToString()); ct1.Add(querySdr[8].ToString()); ct1.Add(querySdr[9].ToString()); ct1.Add(querySdr[10].ToString()); ct1.Add(querySdr[11].ToString()); ct1.Add(querySdr[12].ToString()); ct1.Add(querySdr[13].ToString()); ct1.Add(querySdr[14].ToString()); ct1.Add(querySdr[15].ToString()); ct1.Add(querySdr[16].ToString()); ct1.Add(querySdr[17].ToString()); } ctest1.contentArray = ct1; contentList.Add(ctest1); } querySdr.Close(); } else { MessageBox.Show("SaledService is not opened"); } conn.Close(); string path = this.pathTextBox.Text; if (path.EndsWith("\\") == false) { path += "\\"; } Untils.createExcel(path + this.datasourceComboBox.Text + ".xlsx", titleList, contentList); this.Cursor = Cursors.Default; } catch (Exception ex) { this.Cursor = Cursors.Default; MessageBox.Show(ex.ToString()); } }