On button click all items on the datgridview is sent to an excel sheet, The template starts from A19 and ends at a49. if there is more I want to copy the template below the original and continue from A88. I am trying to avoid sheet 1 and sheet 2 etc, All should be on sheet 1.
What I have tried:
<pre>private void button1_Click(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.ToString("dd.MM.yyyy");
string quote = txtName.Text.Replace(" ", " ");
string filename = @"C:\MyCedarQuotes\Quote_";
string myfile = filename + quote + "_" + datestr + ".xls";
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;
int rowsToStart2 = 188;
decimal grandTotal = 0;
decimal vatPer = 15;
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();
}
}
grandTotal += Convert.ToDecimal(dataGridView1.Rows[row].Cells[6].Value);
if ((row + 1) % rowsToDisplay == 0)
{
xlWorkSheet.get_Range("I50", "I50").Value2 = "Sub Total";
xlWorkSheet.get_Range("J50", "J50").Value2 = "=sum(J19:J49)";
currentSheet++;
cell = 0;
}
else if (row + 1 == dataGridView1.Rows.Count - 1)
{
xlWorkSheet.get_Range("I50", "I50").Value2 = "Sub Total";
xlWorkSheet.get_Range("J50", "J50").Value2 = "=sum(J19:J49)";
xlWorkSheet.get_Range("I51", "I51").Value2 = "Transport";
xlWorkSheet.get_Range("J51", "J51").Value2 = 0;
xlWorkSheet.get_Range("I52", "I52").Value2 = "Excl Vat";
xlWorkSheet.get_Range("J52", "J52").Value2 = grandTotal;
decimal vatAmount = grandTotal * (vatPer / 100);
xlWorkSheet.get_Range("I53", "I53").Value2 = "Vat @15%";
xlWorkSheet.get_Range("J53", "J53").Value2 = vatAmount;
xlWorkSheet.get_Range("I54", "I54").Value2 = "Total";
xlWorkSheet.get_Range("J54", "J54").Value2 = grandTotal + vatAmount;
}
else
{
cell++;
}
}