Hi shabzo,
I have created a sample that full fill your requirement. You need to modified as per your requirement.
C#
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
this.BindDataGridView();
}
private void BindDataGridView()
{
SqlConnection con;
string str = "data source=.;initial catalog=Northwind;user id=sa;password=password;";
con = new SqlConnection(str);
con.Open();
SqlDataAdapter dscmd = new SqlDataAdapter("SELECT TOP 9 CustomerID,ContactName,Address,City,Country FROM Customers", con);
DataTable dt = new DataTable();
dscmd.Fill(dt);
this.dataGridView1.DataSource = dt;
}
private void btnExportExcel_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
string sourcePath = @"C:\Users\dharmendra\Desktop\masterquote.xlsx";
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(sourcePath);
string datestr = DateTime.Now.ToShortDateString();
string destinationPath = @"C:\Users\dharmendra\Desktop\Quote_" + "Name_" + datestr.Replace('/', '_') + ".xls";
string quote = "Test Name" + "_" + datestr;
string salesman = "test@test.com".ToUpper();
string customer = "Test Name".ToUpper();
int currentSheet = 1;
int cell = 0;
int rowsToDisplay = 30;
int rowsToStart = 19;
for (int row = 0; row < dataGridView1.Rows.Count; 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(misValue, xlSheets[xlWorkBook.Sheets.Count]);
xlWorkSheet.Name = "Sheet" + currentSheet.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;
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(destinationPath))
{
System.IO.File.Delete(destinationPath);
}
xlWorkBook.SaveAs(destinationPath, 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 in " + destinationPath);
}
}
VB.Net
Public Partial Class Form1
Public Sub New()
InitializeComponent()
Me.BindDataGridView()
End Sub
Private Sub BindDataGridView()
Dim con As SqlConnection
Dim str As String = "data source=.;initial catalog=Northwind;user id=sa;password=password;"
con = New SqlConnection(str)
con.Open()
Dim dscmd As New SqlDataAdapter("SELECT TOP 9 CustomerID,ContactName,Address,City,Country FROM Customers", con)
Dim dt As New DataTable()
dscmd.Fill(dt)
Me.dataGridView1.DataSource = dt
End Sub
Private Sub btnExportExcel_Click(sender As Object, e As EventArgs)
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim sourcePath As String = "C:\Users\dharmendra\Desktop\masterquote.xlsx"
xlApp = New Excel.Application()
xlWorkBook = xlApp.Workbooks.Add(sourcePath)
Dim datestr As String = DateTime.Now.ToShortDateString()
Dim destinationPath As String = "C:\Users\dharmendra\Desktop\Quote_" + "Name_" + datestr.Replace("/"C, "_"C) + ".xls"
Dim quote As String = Convert.ToString("Test Name" + "_") & datestr
Dim salesman As String = "test@test.com".ToUpper()
Dim customer As String = "Test Name".ToUpper()
Dim currentSheet As Integer = 1
Dim cell As Integer = 0
Dim rowsToDisplay As Integer = 30
Dim rowsToStart As Integer = 19
For row As Integer = 0 To dataGridView1.Rows.Count - 1
If currentSheet <= xlWorkBook.Sheets.Count Then
xlWorkSheet = DirectCast(xlWorkBook.Worksheets.get_Item(currentSheet), Excel.Worksheet)
Else
Dim xlSheets = TryCast(xlWorkBook.Sheets, Excel.Sheets)
xlWorkSheet = DirectCast(xlSheets.Add(misValue, xlSheets(xlWorkBook.Sheets.Count)), Excel.Worksheet)
xlWorkSheet.Name = "Sheet" + currentSheet.ToString()
End If
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
For column As Integer = 0 To dataGridView1.Columns.Count - 1
If column = 2 OrElse column = 5 Then
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()
End If
Next
If (row + 1) Mod rowsToDisplay = 0 Then
currentSheet += 1
cell = 0
Else
cell += 1
End If
Next
If System.IO.File.Exists(destinationPath) Then
System.IO.File.Delete(destinationPath)
End If
xlWorkBook.SaveAs(destinationPath, 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(Convert.ToString("Excel file created , you can find the file in ") & destinationPath)
End Sub
End Class
Output
