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<string> gongDuans =(List<string>)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<DataTable> dts = new List<DataTable>();
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<DataTable> dtPays = e.UserState as List<DataTable>;
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<DataTable> dtNewPays = new List<DataTable>();
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);
}
}
/// <summary>
/// 清除零值
/// </summary>
/// <returns></returns>
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;
}