Subtotal an excel sheet if sheet 2 available

Last Reply 3 hours ago By dharmendr

Posted 13 days ago

How do i force a subtotal only  on column j50 .vat onj51 and total j52.

If there is a continuation to sheet 2 then only subtotal on sheet 1 and on sheet 2 subtotal +vat = total  

 

private void button1_Click_1(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            string filePath = @"C:\MyCedarData\masterquote.xltx";
            xlWorkBook = xlApp.Workbooks.Add(filePath);
            
            string datestr = DateTime.Now.ToShortDateString();
            string filename = @"C:\MyCedarQuotes\Quote_" + txtName.Text.Replace(" ", "") + "_" + datestr + ".xls";
            string quote = txtName.Text.Replace(" ", "") + "_" + datestr;
            string salesman = txtEmail.Text.ToUpper();
            string customer = txtName.Text.ToUpper();
            string custname = txtCustName.Text.ToUpper();
            int currentSheet = 1;
            int cell = 0;
            int rowsToDisplay = 30;
            int rowsToStart = 19;
            for (int row = 0; row < dataGridView1.Rows.Count -1; row++)
            {
                if (currentSheet <= xlWorkBook.Sheets.Count)
                {
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet);
                }
                else
                {
                    var xlSheets = xlWorkBook.Sheets as Excel.Sheets;
                    xlWorkSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[xlWorkBook.Sheets.Count], misValue, misValue, misValue);
                    xlWorkSheet.Name = "Sheet" + currentSheet.ToString();
                }
                    xlWorkSheet.get_Range("C10", "E10").Value2 = salesman;
                    xlWorkSheet.get_Range("C12", "E12").Value2 = customer;
                    xlWorkSheet.get_Range("C14", "E14").Value2 = custname;
                    xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
                    xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
                    xlWorkSheet.get_Range("H12", "I12").Value2 = DateTime.Now;
                    xlWorkSheet.get_Range("H10", "I10").Value2 = quote;
                    

                    for (int column = 0; column < dataGridView1.Columns.Count; column++)
                    {
                        if (column == 2 || column == 5)
                        {
                            xlWorkSheet.Cells[cell + rowsToStart, column + 1] = "'" + dataGridView1.Rows[row].Cells[column].Value.ToString();
                        }
                        else
                        {
                            xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString();
                        }
                    }
                    if ((row + 1) % rowsToDisplay == 0)
                    {
                        currentSheet++;
                        cell = 0;
                    }
                    else
                    {
                        cell++;
                    }
            }
            if (System.IO.File.Exists(filename))
            {
                System.IO.File.Delete(filename);
            }
            
             
            xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            //releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file c:\\quote.xls");
        }
            

 

You are viewing reply posted by: AnandM 13 days ago.
Posted 13 days ago

HI shabzo,

Can you please explain your requirement in more detail what exactly you are trying to achieve?

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html