How do i limit the lines to 30 that needs to be exported to excel from datagridview and then force balance on page two of excel.
The template has a logo and other stuff on top from Row A19 TO A49 I can export the datagridview from form2. The datagridview can vary from sometimes having only 10 items and at times 100 items.
It works perfectly if there is only 30 items. If there is more then it overwrites important lines from A50 TO A70.
If it is more than 30. Then the balance from 31 to 60 to go on workbook2 of template and so on. Like a continuation of workbook1.
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();
xlWorkBook = xlApp.Workbooks.Open(@"C:\CedarData\masterquote.xlsx", misValue, false, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//int i = 1;
//int j = 1;
string datestr = DateTime.Now.ToShortDateString();
//string colName = dataGridView1.Columns[j].HeaderText;
string filename = @"C:\CedarData\CedarQuotes\Quote_" + txtName.Text.Replace(" ", "") + "_" + datestr + ".xls";
string quote = txtName.Text.Replace(" ", "") + "_" + datestr;
string salesman = txtEmail.Text.ToUpper();
string customer = txtName.Text.ToUpper();
// Storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
//string value3 = dataGridView1.Rows[i].Cells[j].Value.ToString();
if (j == 2 || j == 5)
{
xlWorkSheet.Cells[i + 19, j + 1] = "'" + dataGridView1.Rows[i].Cells[j].Value.ToString();
}
else
{
xlWorkSheet.Cells[i + 19, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
xlWorkSheet.get_Range("C10", "E10").Value2 = salesman;
xlWorkSheet.get_Range("C12", "E12").Value2 = customer;
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;
}
}
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");
}