private void btnQuery_Click(object sender, EventArgs e)

{

if (cboxFactory.Text=="")

{

MessageBox.Show("请选择厂别！","系统提示");

return;

}

fname = cboxFactory.Text;

//gongyi = cmbGongYi.Text.Trim();

List gongDuans = new List();//工段数组

gongyi = new List();

for (int i = 0; i < checkedListBox1.Items.Count; i++)        {            if (checkedListBox1.GetItemChecked(i))            {                gongDuans.Add(checkedListBox1.Items[i].ToString());            }        }        for (int i = 0; i < checkedListBox2.Items.Count; i++)        {            if (checkedListBox2.GetItemChecked(i))            {                gongyi.Add(checkedListBox2.Items[i].ToString());            }        }        backgroundWorker1.RunWorkerAsync(gongDuans);        ChangState();    }    Thread myThread;     private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)    {        myThread = Thread.CurrentThread;        List
       
         gongDuans =(List
        
         )e.Argument;        String gongyis = "";        if (gongyi.Count > 0)        {             for (int j = 0; j < gongyi.Count; j++)            {                gongyis = gongyis + gongyi[j];                if (j != gongyi.Count-1) gongyis = gongyis + "-";            }        }        if (gongDuans.Count>0)        {            List
         
           dts = new List
          
           ();            for (int i = 0; i < gongDuans.Count; i++)            {                if (!backgroundWorker1.CancellationPending)                {                                        dts.Add(GetDuiZhi(gongDuans[i],fname,gongyis));                                    }            }                        backgroundWorker1.ReportProgress(100, dts);        }    }    private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)    {        try        {            List
           
             dtPays = e.UserState as List
            
             ; Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Open(Application.StartupPath + @"\Templates\在制品堆积表1.xlt"); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];//第一个工作表 sheet.Cells["B2"].PutValue(cboxFactory.Text); quTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm"); sheet.Cells["J2"].PutValue(quTime); int lastIndex = 3; #region 构造带汇总的新集合 List
             
               dtNewPays = new List
              
               (); if (dtPays != null && dtPays.Count > 0) { foreach (DataTable item in dtPays) { DataRow newRow = item.NewRow(); int orderPaiNum=0 ,Lnum=0 ,Tnum=0 ,duiZhiNum=0 ,rong=0; decimal LAh = 0 , TAh = 0; //newRow["gongDuan"] = item.Rows[0]["gongDuan"].ToString()+"合计"; orderPaiNum = Convert.ToInt32(item.Compute("sum(orderPaiNum)", "true") is DBNull ? 0 : item.Compute("sum(orderPaiNum)", "true")); newRow["orderPaiNum"] = orderPaiNum; Lnum = Convert.ToInt32(item.Compute("sum(Lnum)", "true") is DBNull ? 0 : item.Compute("sum(Lnum)", "true")); newRow["Lnum"] = Lnum; Tnum = Convert.ToInt32(item.Compute("sum(Tnum)", "true") is DBNull ? 0 : item.Compute("sum(Tnum)", "true")); newRow["Tnum"] = Tnum; duiZhiNum = Convert.ToInt32(item.Compute("sum(duiZhiNum)", "true") is DBNull ? 0 : item.Compute("sum(duiZhiNum)", "true")); //if (newRow["gongDuan"].ToString()=="制片合计") // newRow["duiZhiNum"] = Convert.ToInt32(duiZhiNum/2); //else newRow["duiZhiNum"] =duiZhiNum; rong = Convert.ToInt32(item.Compute("sum(rong)", "true") is DBNull ? 0 : item.Compute("sum(rong)", "true")); newRow["rong"] = rong; LAh = Convert.ToDecimal(item.Compute("sum(LAh)", "true") is DBNull ? 0 : item.Compute("sum(LAh)", "true")); newRow["LAh"] = LAh; TAh = Convert.ToDecimal(item.Compute("sum(TAh)", "true") is DBNull ? 0 : item.Compute("sum(TAh)", "true")); newRow["TAh"] = TAh; item.Rows.Add(newRow); //新增列(该列隐藏) //item.Columns.Add(new DataColumn("duiZhiNum1", typeof(Int32))); //foreach (DataRow row in item.Rows) //{ // //制片堆积个数除以2 // if (row["gongDuan"].ToString() == "制片") // row["duiZhiNum1"] = Convert.ToDouble(row["duiZhiNum"]) / 2; // else // row["duiZhiNum1"] = row["duiZhiNum"]; //} //清除0 DataTable dtWithOutZrro = ClearZero(item); dtNewPays.Add(dtWithOutZrro); //foreach (DataRow row in item.Rows) //{ // oderPaiNum += Convert.ToInt32(row["oderPaiNum"].ToString() == "" ? "0" : row["oderPaiNum"].ToString()); // Lnum += Convert.ToInt32(row["Lnum"].ToString() == "" ? "0" : row["Lnum"].ToString()); // Tnum += Convert.ToInt32(row["Tnum"].ToString() == "" ? "0" : row["Tnum"].ToString()); // duiZhiNum += Convert.ToInt32(row["duiZhiNum"].ToString() == "" ? "0" : row["duiZhiNum"].ToString()); // rong += Convert.ToInt32(row["rong"].ToString() == "" ? "0" : row["rong"].ToString()); // LAh += Convert.ToDecimal(row["LAh"].ToString() == "" ? "0" : row["LAh"].ToString()); // TAh+=Convert.ToDecimal(row["TAh"].ToString() == "" ? "0" : row["TAh"].ToString()) //} } } #endregion if (dtNewPays != null && dtNewPays.Count > 0) { for (int i = 0; i < dtNewPays.Count; i++) { DataTable dt = dtNewPays[i]; if (dt.Rows.Count > 0) { sheet.Cells.ImportDataTable(dt, false, "A" + (lastIndex + 1)); lastIndex += dt.Rows.Count; } } } sheet.Cells.DeleteRows(lastIndex, 2); MemoryStream ms = workbook.SaveToStream(); ms.Seek(0, SeekOrigin.Begin); fpSpread1.OpenExcel(ms); ms.Close(); fpSpread1.Sheets[0].RowCount = lastIndex + 3; fpSpread1.Sheets[0].ColumnCount = 16; //fpSpread1.Sheets[0].Columns[13].Visible = false; fpSpread1.Sheets[0].OperationMode = FarPoint.Win.Spread.OperationMode.ReadOnly; } catch(Exception ex) { MessageBox.Show(ex.Message); } } /// 
               
 /// 清除零值 /// 
 /// 
                public DataTable ClearZero(DataTable dt) { DataTable result = dt.Clone(); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = result.NewRow(); for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Rows[i][j].ToString() == "0" || dt.Rows[i][j].ToString() == "0.00%") { dr[j] = DBNull.Value; } else { dr[j] = dt.Rows[i][j]; } } result.Rows.Add(dr); } return result; }