c# export to excel with a Specific sheet name

james james

The below code when exporting to excel gives the sheet name Book1. How can i edit it to where export the sheet name as "Sams Report"?

private void btn_Export_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
XcelApp.Application.Workbooks.Add(Type.Missing);
// Storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
XcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
XcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
XcelApp.Columns.AutoFit();
XcelApp.Visible = true;
}
}

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

ram kumar7y ago
workSheet = (Microsoft.Office.Interop.Excel.Worksheet) workBook.ActiveSheet;
workSheet.Name = "Sheet name";
0
7y ago
ZetExcel is a great SDK product is a flexible platform all in C# and super well suited for .NET applications
0
SriHari VP9y ago
You will need to get reference to the active sheet and set a name to it.

Microsoft.Office.Interop.Excel.Workbook workBook;
Microsoft.Office.Interop.Excel.Worksheet workSheet;

workBook = XcelApp.Application.Workbooks.Add(Type.Missing);

workSheet = (Microsoft.Office.Interop.Excel.Worksheet) workBook.ActiveSheet;
workSheet.Name = "Sams Report";
0
Leon D9y ago
I'd suggest you use a free third party library free Spire.XLS to export data to excel, it's easy and doesn't rely on Excel. You can get free Spire.XLS dll from NuGet Package Manager and refer to the following code:
DataTable dt = dataGridView1.DataSource as DataTable;
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sams Report";
sheet.InsertDataTable(dt, true, 1, 1);
workbook.SaveToFile("Report.xlsx", ExcelVersion.Version2013);
0
Amit Gupta9y ago
Then you have to set the title of the excel application, this will gives you the expected filename when saving
0
james james9y ago
I spoke wrong this is what i need to do without having to save the file.

The below code when exporting to excel gives the Book name Book1. How can i edit it to export the Book name as "Sams Report"?
0
Amit Gupta9y ago
You can write above the for loop
0
james james9y ago
Amit

where do I place that in my code or what do I replace?
getting build error.

Thanks
0
Amit Gupta9y ago
1. Microsoft.Office.Interop.Excel.Worksheet worksheet = (Worksheet)xlApp.Worksheets["Sheet1"];
2. worksheet.Name = “NewTabName”
0