Hi
Hi Dhamendr
My excel template worksheet starts from A121 : K121
column A WILL always have an item no.
datagridview can have 1 item to 100 items. so if i have <= 30 items on the datagridview that will be sent to the excel sheet , then i want to delete all empty rows UPWARDS from A121 TO A52.
Excel.Range range = xlWorkSheet.get_Range("A52", "A121");
But if there is more than 30 items on datagrid (upto 100) then delete empty rows upwards from a121 uo to rowtodelete from. like below .
example if there was 55items then (55items from A21 = A76). DELETE from A121 TO A77
if ((row + 1) % rowsToDisplay == 0)
{
int rowToDeleteFrom = rowsToStart + have;//have is datagridview .rows .count
Excel.Range range = xlWorkSheet.get_Range("A" + rowToDeleteFrom, "A121");
Excel.Range entireRow = range.EntireRow;
entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
cell++;
}
else
{
cell++;
}
private void button1_Click(object sender, EventArgs e)
{
int currentSheet = 1;
Excel.Application xlApp = new Excel.Application();
string filePath = @"C:\MyProdecData\testquote1.xltx";
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(filePath);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet); ;
object misValue = System.Reflection.Missing.Value;
string datestr = DateTime.Now.ToString("dd.MM.yyyy");
string quote = txtName.Text.Replace(" ", " ");
string filename = @"C:\MyProdecQuotes\";
string myfile = filename + quote + "_" + datestr + ".xlsx";
string salesman = txtEmail.Text.ToUpper();
string customer = txtName.Text.ToUpper();
string custname = txtCustName.Text.ToUpper();
string delivery = txtDel.Text;
int cell = 0;
int rowsToDisplay = 30;
int rowsToStart = 21;
for (int row = 0; row < dataGridView1.Rows.Count - 1; row++)
{
if (currentSheet <= xlWorkBook.Sheets.Count)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet);
}
xlWorkSheet.get_Range("F18", "H18").Value2 = salesman;
xlWorkSheet.get_Range("B13", "D14").Value2 = customer;
xlWorkSheet.get_Range("B15", "D15").Value2 = custname;
xlWorkSheet.get_Range("H13", "K15").Value2 = delivery;
xlWorkSheet.get_Range("A13", "D14").Font.Bold = true;
//xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
xlWorkSheet.get_Range("I3", "J3").Value2 = DateTime.Now;
xlWorkSheet.get_Range("I5", "K5").Value2 = quote + "_" + datestr;
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().ToUpper();
}
else
{
xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString().ToUpper();
}
}
int dgvcount = dataGridView1.Rows.Count;
int rowToDeleteFrom = dgvcount + rowsToStart;
if ((row + 1) % rowsToDisplay == 0)//if dgvcount more then 50
{
Excel.Range range = xlWorkSheet.get_Range("A" + rowToDeleteFrom, "A121");
Excel.Range entireRow = range.EntireRow;
entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
cell++;
}
if (dgvcount < 50)
{
Excel.Range range = xlWorkSheet.get_Range("A52", "A121");
Excel.Range entireRow = range.EntireRow;
entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
cell++;
}
}
if (System.IO.File.Exists(myfile))
{
System.IO.File.Delete(myfile);
}
//xlWorkBook.SaveAs(myfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.SaveAs(myfile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
xlWorkBook.Saved = true;
xlWorkBook.Close(true, misValue, misValue);
if (xlApp != null)
{
xlApp.Quit();
}
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , CLICK on the Open quote button");
{
btn2.Show();
}
}