Nrthugu

I want to get page number by using cell, Excel Interop C#

for example D,77 is on page 3... I have no idea witch property of cell it can be...

I want to

  ws.get_Range($"C{page2Start}", $"I{page2InfoEnd}").Value2 = preInfo;

Its table to start on the secound line of the secound page..

//crete excle report from resultObject

public static Workbook WriteCertificateOfComplianceExcelReport(List<CertificateOfComplianceTableObject> mainTbl,bool show)

{

    List<CSerieses> serieses = QADataBaseManager.GetCSerieses();



    GlobalVariables.xlApp.DisplayAlerts = true;

    GlobalVariables.xlApp.Visible = true;

    GlobalVariables.xlApp.ScreenUpdating = true;



    Workbook wb = GlobalVariables.xlApp.Workbooks.Open(GlobalVariables.excelReport);

    Worksheet ws = (Worksheet)wb.Worksheets[1];



    if (ws == null)

    {

        Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");

        return null;

    }

    ws.Cells.NumberFormat = "@";

    ws.Cells.Style.Font.Size = ws.Cells.Style.Font.Size - 1;

    ws.Cells.ShrinkToFit = true;



    string vals = QADataBaseManager.GetCustomerPO();

    var preInfo = new string[6, 7] {

        { vals[0], "","","Tadiran cat No:","","",vals[3]},

        { vals[1].Split(" ").First(), "","","Tadiran P/O:","","",vals[4]},

        { vals[2], "","","Description:","","",vals[5]},

        { "", "","","Customer P/N:","","",vals[6]},

        { "", "","","Test date:","","",vals[7]},

        { "", "","","Quantity:","","",vals[8]},

    };



    ws.get_Range("C7", "I12").Value2 = preInfo;



    if (Convert.ToInt32(vals[8]) != serieses.Sum(s => s.Count))

    {

        Console.WriteLine("nError!!!!n");

    }



    int j = 0;

    int i;

    for (i = 0; i < serieses.Count(); i++)

    {

        if (serieses[i].Mark)

        {

            string series = serieses[i].SeriesNumber.Trim();

            if (serieses[i].Count != 0)

            {

                series += $"({serieses[i].Count})";

            }

            ws.Cells[10 + j, "C"] = series;

            j++;

        }

    }



    ws.Cells[45, "G"] = QADataBaseManager.GetInspector();

    //ws.Cells[40, "G"] = "";//"------------";

    int page2Start = 44;

    int page2InfoEnd = page2Start + 5;

    ws.get_Range($"C{page2Start}", $"I{page2InfoEnd}").Value2 = preInfo;





    ws.get_Range($"A{page2Start}", $"I{page2InfoEnd + serieses.Count()}").Cells.HorizontalAlignment = XlHAlign.xlHAlignLeft;

    ws.get_Range($"A{page2Start}", $"I{page2InfoEnd + serieses.Count()}").Cells.Font.Name = "Times New Roman";



    var seriesObj = new string[serieses.Count(), 3];

    for (int ii = 0; ii < 999; ii++)

    {

        //int x = ws.Range[ii, ii].PageBreak;

       //wb.p();



    }

    var ranges = "";

    int seriesesLine = (page2InfoEnd - 2);

    for (i = 0; i < serieses.Count(); i++)

    {

        int k = (page2InfoEnd-2) + i;

        seriesObj[i, 0] = "Code & Quantity:";

        ranges += $"A{k}:B{k},";

        string series = serieses[i].SeriesNumber.Trim();

        if (serieses[i].Count != 0)

        {

            series += $"({serieses[i].Count})";

        }

        seriesObj[i, 2] = series;

        ranges += $"C{k}:E{k},";

    }

    ws.get_Range(ranges.TrimEnd(',')).Merge();

    ranges = "";

    ws.get_Range($"A{seriesesLine}", $"C{seriesesLine + (serieses.Count()-1)}").Value2 = seriesObj;



    int t_start = seriesesLine + serieses.Count() + 1;

    int t_end = t_start + mainTbl.Count()+ 1;





    Range tRange = ws.get_Range("A" + t_start, "K" + t_end);



    tRange.SetBorders();

    Range r = ws.get_Range($"A{t_start}", $"K{t_start}");

    r.HorizontalAlignment = XlHAlign.xlHAlignCenter;

    r.Cells.VerticalAlignment = XlVAlign.xlVAlignCenter;

    r.Cells.WrapText = true;





    ranges += $"A{ t_start}:C{ t_start},F{ t_start}:H{ t_start},I{ t_start}:J{ t_start}";

    ws.get_Range(ranges).Merge();



    //ws.get_Range("A" + t_start, "C" + t_start).Merge();

    //ws.get_Range("F" + t_start, "H" + t_start).Merge();

    //ws.get_Range("I" + t_start, "J" + t_start).Merge();



    var objData = new string[mainTbl.Count() + 2, 11];



    objData[0, 0] = "Test";

    objData[0, 3] = "Sample size";

    objData[0, 4] = "ACPT No.";

    objData[0, 5] = "Requirements";

    objData[0, 8] = "Findings";

    objData[0, 10] = "ACPT REG";



    objData[1, 0] = "Correct designation";

    objData[1, 3] = "1";

    objData[1, 4] = "---";

    objData[1, 5] = "TLP-81128/T/AR3";

    objData[1, 8] = "O.K.";

    objData[1, 10] = "A";



    int s_temp = t_start + 1;



    ws.get_Range($"D{s_temp}", $"K{s_temp}").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;



    //ranges += $"A{ s_temp}:C{ s_temp},F{ s_temp}:H{ s_temp},I{ s_temp}:J{ s_temp},";



    Range from = ws.get_Range($"A{t_start}:J{t_start}");

    Range to = ws.get_Range($"A{t_start+1}:J{t_start + mainTbl.Count() + 1}");

    from.Copy(to);



    //ws.get_Range("A" + s_temp, "C" + s_temp).Merge();//5

    //ws.get_Range("F" + s_temp, "H" + s_temp).Merge();

    //ws.get_Range("I" + s_temp, "J" + s_temp).Merge();//2

    for (i = 0; i < mainTbl.Count(); i++)

    {

        CertificateOfComplianceTableObject obj = mainTbl[i];



        try

        {

            if (obj.PropNumber == 13)

            {



                obj.Text += "    " + QADataBaseManager.GetRequiermentsByCloserDateTime(41).Min + "mA   ";

                obj.Text += QADataBaseManager.GetRequiermentsByCloserDateTime(42).Min + "mS";

            }

            if (obj.PropNumber == 45)

            {

                obj.Text += "    " + QADataBaseManager.GetRequiermentsByCloserDateTime(47).Min + "mA   ";

                obj.Text += QADataBaseManager.GetRequiermentsByCloserDateTime(48).Min + "mS";

            }

        }

        catch (Exception ex)

        {

            MilBatDataBaseManager.WriteLog(ex.StackTrace, ex.Message);

        }

        objData[i + 2, 0] = obj.Text;

        objData[i + 2, 3] = obj.Cells.ToString();

        objData[i + 2, 4] = "1";

        objData[i + 2, 5] = obj.Requirements;

        objData[i + 2, 8] = obj.Finding;

        objData[i + 2, 10] = obj.ACPT_REG;

        int mergeI = t_start + 2 + i;

        //ranges += $"A{mergeI}:C{mergeI},F{mergeI}:H{mergeI},I{mergeI}:J{mergeI},";



        //ws.get_Range("A" + (t_start + 2 + i), "C" + (t_start + 2 + i)).Merge();

        //ws.get_Range("F" + (t_start + 2 + i), "H" + (t_start + 2 + i)).Merge();

        //ws.get_Range("I" + (t_start + 2 + i), "J" + (t_start + 2 + i)).Merge();





        if (obj.ACPT_REG.Equals("R"))

        {

            ws.get_Range($"K{i + 2 + t_start}").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

        }

    }



    var range = ws.get_Range($"A{(t_start)}", $"K{mainTbl.Count() + 1 + t_start}");//.Value2 = objData;

    range.Value2 = objData;



    range.Font.Size = 13;

    //ws.get_Range($"A{(t_start)}", $"K{mainTbl.Count() + 1 + t_start}").Value2 = objData;



    ws.get_Range($"A{t_start + 1}", $"C{t_end}").Cells.HorizontalAlignment = XlHAlign.xlHAlignLeft;

    ranges = "";

    var remarks = QADataBaseManager.GetRemarks();

    var remArray = remarks["g"].Replace("rn", "n").Split("n");

    int remarkGSize = remArray.Count() + remArray.Sum(s => ((int)(s.Length / 60)))-1;

    //for (int k = 0; k < remArray.Count(); k++)

    //{



    //    ranges += $"A{ t_end + 3 +k}:K{ t_end + 3 + k},";

    //}





    switch (remarkGSize)

    {

        case 0:

        case 1:

        case 2:

        case 3:

            t_end += 2;

            break;

        case 4:

        case 5:

        case 6:

        case 7:

            t_end += 1;

            break;

        default:

            break;

    }

    ws.get_Range($"B{ t_end + 2}:K{ t_end + 2}").Merge();

    ws.Cells[t_end + 2, "B"] = remarks["g"];

    ws.get_Range($"C{t_end + 2}:I{ t_end + 2}").HorizontalAlignment = XlHAlign.xlHAlignLeft;

    //ws.get_Range($"A{ t_end + 3}:K{ t_end + 3 + remArray.Count()}").Rows.AutoFit();

    //ws.get_Range($"A{t_end + 2}:K{t_end + 2}").Rows.AutoFit();





    //ranges += $"A{ t_end + 3}:K{ t_end + 3 + remArray.Count()},";



    //ws.Range[ws.Cells[t_end + 2, "A"], ws.Cells[t_end + 2, "K"]].Merge();



    int statusStart = t_end + 2 + remarkGSize;



    switch (remarkGSize)

    {

        case 0:

        case 1:

        case 2:

        case 3:

            statusStart += 2;

            break;

        case 4:

        case 5:

        case 6:

        case 7:

            statusStart += 1;

            break;

        default:

            break;

    }



    QADataBaseManager.GetMifratFromCatalogProp();



    ws.get_Range($"A{statusStart}", $"H{statusStart + 4}").Cells.HorizontalAlignment =

         Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

    ws.get_Range($"A{statusStart}", $"H{statusStart + 4}").Cells.Font.Name = "Calibri";

    ws.get_Range($"A{statusStart}", $"H{statusStart + 4}").Cells.Font.Size = 13;



    var EndInfoObj = new string[5, 10];



    EndInfoObj[0, 0] = "Status:";

    EndInfoObj[1, 0] = "Remarks:";

    EndInfoObj[3, 0] = "Tad. Spec:";

    EndInfoObj[4, 0] = "Date:";



    for (int t = 0; t <= 4; t++)

    {

        //ws.Range[ws.Cells[statusStart + t, "A"], ws.Cells[statusStart + t, "B"]].Merge();

        ranges += $"A{statusStart + t}:B{statusStart + t},";

    }



    //ws.Cells[statusStart, "D"] = "Approved By Name:";







    bool isFalse = QADataBaseManager.GetRejectWord();



    if(isFalse)

    {

        EndInfoObj[0, 2] = "Failed";

        ws.Range["C" + statusStart, "C" + statusStart].Font.Color = System.Drawing.Color.Red;

    }

    else

    {

        EndInfoObj[0, 2] = "Success";

        ws.get_Range("C" + statusStart, "C" + statusStart).Font.Color = System.Drawing.Color.Blue;

    }





    EndInfoObj[1, 2] = remarks["f"];

    EndInfoObj[3, 2] = GlobalVariables.MifratNumber;

    EndInfoObj[4, 2] = vals[7];



    ranges += $"C{ statusStart}:E{ statusStart},C{ statusStart + 1}:E{ statusStart + 1}," +

        $"C{ statusStart + 3}:E{ statusStart + 3},C{ statusStart + 4}:E{ statusStart + 4},";



    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ i>



    EndInfoObj[3, 5] = "Rev :";

    EndInfoObj[4, 5] = "Inspector :";



    ranges += $"F{ statusStart + 3}:G{ statusStart + 3},F{ statusStart + 4}:G{ statusStart + 4},";

    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ i>



    EndInfoObj[3, 7] = GlobalVariables.Version;

    EndInfoObj[4, 7] = QADataBaseManager.GetInspector();



    ranges += $"H{ statusStart + 3}:I{ statusStart + 3},H{ statusStart + 4}:I{ statusStart + 4}";



    ws.get_Range(ranges).Merge();

    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ i>



    EndInfoObj[4, 9] = "Stamp :";

    ws.get_Range("A" + statusStart, "J" + (statusStart + 4)).Value2 = EndInfoObj;

    GlobalVariables.xlApp.Visible = show;

    GlobalVariables.xlApp.ScreenUpdating = show;

    return wb;

}