save data from every loop in excel

darma teja

Hi All,

I wanted to export data to excel.
here is the code that i wrote:

Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();

Workbook vb = xla.Workbooks.Add(XlSheetType.xlWorksheet);

var Worksheet = (Worksheet)xla.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

foreach (logic)

{
//logic to get data
          // I put data to excel cells
                example: Worksheet.Cells[i + 1, 1] = bla bla;
             }

Worksheet ws = (Worksheet)xla.ActiveSheet;

ws.SaveAs(@"c:\Documents and Settings\Desktop\output1.xls");

Now my problem is: I dont want to save xls file in last step.

I want to save data in excel file in every loop. It means that If any exception comes at least I have data of the passed loops.

Many thanks,

Darma

2 Replies

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

darma teja13y ago
Hi Imtiyaz, Thanks for the reply. But It does not work in my case. You put the whole data (whole loop data) in xlworksheet and than finally you saved it. My question is: Suppose, If my loop runs for 3 times. at the end of the 1st loop I should save data got from the first in filename1(excel file name According to your code). at the end of the second loop again i should save the data in the same excel file(filename1) ..... Thanks, Darma
Imtiyaz Ansari13y ago
try this code:-
firstly folling refarence add
Microsoft.Office.Interop.Excel
Microsoft.Office.Interop.Word
this code are used gridview data create excel file

try
{
string filename1 = "Damage Product List";

filename1 = "D:\\Report\\" + filename1 + ".xls";
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;

for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[1, j + 1] = dataGridView1.Columns[j].Name;
}

for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs(filename1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

MessageBox.Show("Excel file created , you can find the file");

}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
return;
}
please Accept the Answer if slove youer problam........