I have an excel template with 30rows starting from A19:J19 TO A49:J49
From a50 to a65 i have clauses etc.
How do i get my datagridview with 55 items to export to the template and then open another page 2 for the balance of the items on the datgridview.
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:\CedarData\masterquote.xltx";
xlWorkBook = xlApp.Workbooks.Add(filePath);
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++)
{
xlWorkSheet.Cells[i + 19, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
// xlWorkSheet.HPageBreaks.Add(xlWorkSheet.get_Range("A49", "J49"));
}
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");
}