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