111,097
社区成员




/// <summary>
/// 根据列编号获取列别名
/// </summary>
/// <param name="num">列编号(从0开始)</param>
/// <returns></returns>
public static String getExcelColumnLabel(int num)
{
String temp = "";
double i = Math.Floor(Math.Log(25.0 * (num) / 26.0 + 1) / Math.Log(26)) + 1;
if (i > 1)
{
double sub = num - 26 * (Math.Pow(26, i - 1) - 1) / 25;
for (double j = i; j > 0; j--)
{
temp = temp + (char)(sub / Math.Pow(26, j - 1) + 65);
sub = sub % Math.Pow(26, j - 1);
}
}
else
{
temp = temp + (char)(num + 65);
}
return temp;
}
//(计划总产出总数)和(差异数 量总数)的公式,填充 (装配 计划 工时)值
int p0 = 18;
int ExcelRowCount = 7 + dtExcel.Rows.Count;
for (DateTime DT = dtMin; DT <= dtMax; DT = DT.AddDays(1))
{
range = workSheet.get_Range(workSheet.Cells[2, p0], workSheet.Cells[2, p0]);
string sColumnLabel = getExcelColumnLabel(p0 - 1);
range.Formula = String.Concat("=SUM(", sColumnLabel + "8", ":", sColumnLabel + ExcelRowCount, ")");
range = workSheet.get_Range(workSheet.Cells[4, p0], workSheet.Cells[4, p0]);
range.Formula = String.Concat("=", sColumnLabel + "3", "-", sColumnLabel + "2");
range = workSheet.get_Range(workSheet.Cells[5, p0], workSheet.Cells[5, p0]);
double dAleradyWorkHour = 0.0d;
foreach (DataRow dr in dtExcel.Rows)
{
string strdtColumnName = "SELFDate" + DT.ToShortDateString();
string strHourQTY = dr["PerHourQTY"].ToString();//产能数
string strQTY = dr[strdtColumnName].ToString();//排产数
if (string.IsNullOrEmpty(strHourQTY) || string.IsNullOrEmpty(strQTY))
{
continue;
}
else
{
dAleradyWorkHour = dAleradyWorkHour + double.Parse(strQTY) * 1.00 / double.Parse(strHourQTY);
}
}
if (dAleradyWorkHour > 0.1d)
{
range.Cells[1, 1] = dAleradyWorkHour.ToString("N2");
}
p0++;
}