How to modify a method to format an Excel file in a C# Windows app? 1

[barden9]

I am developing a C# .NET application with SQL Server using Visual Studio 2005 and a Windows form.

In the code below, I display 2 methods as follows.

1) A method named button4_click creates a DataTable and exports it to Excel.
2) A method named ExcelFormat to reformat some of the Excel data for presentation purposes.

Do you know how I could modify the method below named ExcelFormat so that it opens the Excel filename pased to it and formats Cells A2 through A65535 as #,##0 and then save the file automatically without asking "A file name already exists. Do you want to replace it ?".

private void button4_Click(object sender, EventArgs e)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=FSV,13;Initial Catalog=U;User ID =PWt;PWD=CM"))
{

conn.Open();
System.Data.SqlClient.SqlCommand comm = conn.CreateCommand();
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.CommandText = string.Format("dbo.procCount");
System.Data.DataSet dataSet = new System.Data.DataSet();
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(comm);
sqlDataAdapter.Fill(dataSet);

SqlCommand mySqlSelect = new SqlCommand("select count(*) from tblMailroomGim2_04032009", _conn);
mySqlSelect.CommandType = CommandType.Text;
SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlSelect);
DataSet myDataSet = new DataSet();
mySqlAdapter.Fill(myDataSet);
string finalString = "Red Flag records have been exported to Excel";
DataTable myDataTable = myDataSet.Tables[0];

string ExportedFile = @"C:\SPRGACSE_" + DateTime.Now.ToString("MMddhhmmss") + ".XLS";
MessageBox.Show(finalString);
Cursor.Current = Cursors.WaitCursor;
ExcelExport(myDataTable, ExportedFile);
ExcelFormat(ExportedFile);
Cursor.Current = Cursors.Default;
string excel = @"excel.exe";
string xlsFile = ExportedFile;
ProcessStartInfo startInfo = new ProcessStartInfo(excel, xlsFile);
Process.Start(startInfo);
conn.Close();
}
}

public static void ExcelFormat(string filename)
{
Excel.Application oXL = new Excel.Application();
oXL.Visible = false;
Excel._Workbook oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
Excel._Worksheet oSheet = (Excel._Worksheet)oWB.ActiveSheet;

try
{

oSheet.Columns.AutoFit();

}
finally
{
oWB.Close(true, filename, Missing.Value);
oXL.Quit();
}
}

 

[dinger2121]

I haven't done much work with Excel formatting in quite a while, but you should be able to do something like the following -

oSheet.Range("A1:A65535").NumberFormat = "#,##0";
oWB..SaveAs(filename, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

let me know if that works.



carl
MCSD, MCTS:MOSS