Write data to Excel - C#

Question

I need to write data to an excel sheet and need to open it after writing it. This is the code I am using..

object misValue = System.Reflection.Missing.Value;
 Excel.Application xlAppEnv = new Excel.ApplicationClass();
 Excel.Workbook xlForEnv = xlAppEnv.Workbooks.Add(misValue);
 Excel.Worksheet xlForEnv_View = (Excel.Worksheet)xlForEnv.Worksheets.get_Item(1);
 xlForEnv_View.Name = "PF Keys";

 xlForEnv_View.Cells[row, column] = "data";

I could write data using the above code and when I am done, I could save the file to a predefined location using the below code..

envSaveLoc = envSaveLoc + "\\PF Keys.xlsx";
xlForEnv.SaveAs(envSaveLoc, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlAppEnv.DisplayAlerts = true;
xlForEnv.Close(true, misValue, misValue);
xlAppEnv.Quit();

The above code is working fine but now the requirement is that the program shouldn't save it but once the data is written to the excel sheet, open the file in excel and present to the user. User can then review and save it himself by using File->Save as option. How can I achieve it? The data can be stored in some temporary location for presenting to the user.

What kind of application is this WinForms or Web App (ASP.NET)? Could you be more precise - do you want the file to open for the user automatically? — AnalystCave.com
– AnalystCave.com, Commented Aug 18, 2014 at 10:37
This is WinForms. Yes, the file has to be open automatically after the data is captured into excel. User doesn't want the program to save the excel to his computer but just open it with the captured data — Shef
– Shef, Commented Aug 18, 2014 at 14:21

MartinStettner · Accepted Answer · 2014-08-18 11:31:15Z

1

What about simply showing the Excel application window using

xlAppEnv.Visible = true;

This should display the Excel window with all the data you've written and let the user decide how to proceed.

answered Aug 18, 2014 at 11:31

MartinStettner

29.3k15 gold badges84 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shef Over a year ago

Ok..This will do but anyway I would need to save the document to temp folder and then name it by a random name..thanks

MartinStettner Over a year ago

You can still call SaveAs (before or after setting Visible to true).

MasterOfStupidQuestions · Accepted Answer · 2014-08-18 13:27:17Z

This is what I use to run a SQL query and house the returned results in a data set then write the dataset to Excel. From there you can do whatever you wish with it, save/print/convertoPDF etc etc

int i = 0;
int j = 0;
_Workbook workbooksExcel;
Worksheet worksheet;
Excel._Application docExcel;

//This will run a SQL Query and write results to DataSet
SqlConnection conn;
string sql = null;
object misValue = System.Reflection.Missing.Value;
docExcel = null;
string ConnectionString = "Data Source=DS;Initial Catalog=TestDB;User ID = sa;Integrated Security=True;MultipleActiveResultSets=True";
conn = new System.Data.SqlClient.SqlConnection(ConnectionString);
conn.Open();
sql = "Insert SQL Statement Here";
System.Data.SqlClient.SqlDataAdapter dscmd = new System.Data.SqlClient.SqlDataAdapter(sql, conn);
ds = new DataSet();
dscmd.Fill(ds);
conn.Close();
//Here is where the above SQL Statement will write to Excel
for (i = 0; i <= SQLConnection.ds.Tables[0].Rows.Count - 1; i++)
{
  for (j = 0; j <= SQLConnection.ds.Tables[0].Columns.Count - 1; j++)
  {
    try
    {
        data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
        _Workbook workbooksExcel = docExcel.ActiveWorkbook;
        Worksheet sheetExcel = (Worksheet)workbooksExcel.ActiveSheet;
        sheetExcel.Cells[i + 2, j + 1].Value = data;
    }
    catch (Exception ex) { } 
    }
}

Collectives™ on Stack Overflow

Write data to Excel - C#

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related