Hello all,
I have this C# code where in I am writing some datato an excel sheet and then saving it using saveas.
But when I run the program again for the next time it is asking me prompt whether or not replace the exisiting file. I want it to be yes for everytime and it should not give a prompt to save and replace the existing file again and again.
How can I do it??
Here is the code. Please help.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace DC
{
public partial class Form1 : Form
{
private Excel.Application app = null;
private Excel.Workbook workbook = null;
private Excel.Worksheet worksheet = null;
private Excel.Range workSheet_range = null;
public Form1()
{
InitializeComponent();
//createDoc();
}
public void createDoc()
{
try
{
app = new Excel.Application();
app.Visible = true;
workbook = app.Workbooks.Add(1);
worksheet = (Excel.Worksheet)workbook.Sheets[1];
}
catch (Exception e)
{
Console.Write("Error",e.Message);
}
finally
{
}
}
public void createHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns, string b, bool font, int size, string
fcolor)
{
worksheet.Cells[row, col] = htext;
workSheet_range = worksheet.get_Range(cell1, cell2);
workSheet_range.Merge(mergeColumns);
switch (b)
{
case "YELLOW":
workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
break;
case "GRAY":
workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
break;
case "GAINSBORO":
workSheet_range.Interior.Color =
System.Drawing.Color.Gainsboro.ToArgb();
break;
case "Turquoise":
workSheet_range.Interior.Color =
System.Drawing.Color.Turquoise.ToArgb();
break;
case "PeachPuff":
workSheet_range.Interior.Color =
System.Drawing.Color.PeachPuff.ToArgb();
break;
default:
// workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
break;
}
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.Font.Bold = font;
workSheet_range.ColumnWidth = size;
if (fcolor.Equals(""))
{
workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
}
else
{
workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
}
}
public void addData(int row, int col, string data,
string cell1, string cell2, string format)
{
worksheet.Cells[row, col] = data;
workSheet_range = worksheet.get_Range(cell1, cell2);
workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
workSheet_range.NumberFormat = format;
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
Form1 excell_app = new Form1();
excell_app.createDoc();
//creates the main header
excell_app.createHeaders(5, 2, "'ERROR MESSAGE", "B5", "D5", 2, "YELLOW", true, 10, "n");
//creates subheaders
excell_app.createHeaders(6, 2, "'0000343477", "B6", "B6", 0, "GRAY", true, 10, "");
excell_app.createHeaders(6, 3, "", "C6", "C6", 0, "GRAY", true, 10, "");
excell_app.createHeaders(6, 4, "Initial Total", "D6", "D6", 0, "GRAY", true, 10, "");
//add Data to cells
excell_app.addData(7, 2, "114287", "B7", "B7", "#,##0");
excell_app.addData(7, 3, "", "C7", "C7", "");
excell_app.addData(7, 4, "129121", "D7", "D7", "#,##0");
//add percentage row
excell_app.addData(8, 2, "", "B8", "B8", "");
excell_app.addData(8, 3, "=B7/D7", "C8", "C8", "0.0%");
excell_app.addData(8, 4, "", "D8", "D8", "");
//add empty divider
excell_app.createHeaders(9, 2, "", "B9", "D9", 2, "GAINSBORO", true, 10, "");
//Form1 excell_app1 = new Form1();
//app.SaveWorkspace("C:\\spec\\1111111.xls");
//excell_app.app.SaveWorkspace("C:\\spec\\1123.xls");
//excell_app.app.SaveWorkspace("C:\\spec\\aaaaaa.xls")
// workbook1 = app.Workbooks.Add(1);
// worksheet = (Excel.Worksheet)workbook1.Sheets[2];
excell_app.createHeaders(5, 2, "ERROR MESSAGE", "A5", "J5", 2, "YELLOW", true, 10, "n");
//creates subheaders
excell_app.createHeaders(6, 2, "Sold Product", "B6", "B6", 0, "GRAY", true, 10, "");
excell_app.createHeaders(6, 3, "", "C6", "C6", 0, "GRAY", true, 10, "");
excell_app.createHeaders(6, 4, "Initial Total", "D6", "D6", 0, "GRAY", true, 10, "");
//add Data to cells
excell_app.addData(7, 2, "114287", "B7", "B7", "#,##0");
excell_app.addData(7, 3, "", "C7", "C7", "");
excell_app.addData(7, 4, "129121", "D7", "D7", "#,##0");
//add percentage row
excell_app.addData(8, 2, "", "B8", "B8", "");
excell_app.addData(8, 3, "=B7/D7", "C8", "C8", "0.0%");
excell_app.addData(8, 4, "", "D8", "D8", "");
//add empty divider
excell_app.createHeaders(9, 2, "", "B9", "D9", 2, "GAINSBORO", true, 10, "");
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
excel1.DisplayAlerts = false;
excell_app.workbook.SaveAs("c:\\test\\test.xls", Excel.XlFileFormat.xlWorkbookDefault,
Type.Missing, Type.Missing, true, false,
Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
excel1.DisplayAlerts = true;
excel1.Quit();
}
private void button2_Click(object sender, EventArgs e)
{
}
}
}