[SOLVED] Excel report problem in c#

See more:

C#

C#4.0

Hi,

am generating reports using interop, from Mysql database ,

My requirement is:

create a excel file when not exists, if exists delete that and create new one on that same name (or) overwrite the existing file.

here is the code:

<pre lang="c#">  using(MySqlConnection con=new MySqlConnection(ConnectionString))
               {
              String query;

                StringBuilder queryBuilder = new StringBuilder();
                MySqlCommand command = new MySqlCommand();
                System.Data.DataTable dt = new System.Data.DataTable();
               
                string fromDate = dateTimePicker1.Value.ToString("yyyy-MM-dd");
                string toDate = dateTimePicker2.Value.ToString("yyyy-MM-dd");
                if ((reportsmachine.Text == "") && (reportscard.Text == "") &&(reportscustomer.Text==""))
                {
                    query = "select * from transaction where DATE(paid_date)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";
                    da = new MySqlDataAdapter(query, con);
                    ds = new DataSet();
                  datasetvalue= da.Fill(ds);
                 
                   datatablevalue=da.Fill(dt);


                }

 Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                        object misValue = System.Reflection.Missing.Value;

                        Microsoft.Office.Interop.Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);

                        Microsoft.Office.Interop.Excel.Worksheet worksheet;
                        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

                        try
                        {
                          
                            worksheet.Cells[1, 1] = "Machine ID";
                            worksheet.Cells[1, 2] = "Customer ID";
                            worksheet.Cells[1, 3] = "Card ID";
                            worksheet.Cells[1, 4] = "Name";
                            worksheet.Cells[1, 5] = "Address";
                            worksheet.Cells[1, 6] = "Phone No";
                            worksheet.Cells[1, 7] = "Item1 Name";
                            worksheet.Cells[1, 8] = "Item1 Rate";
                            worksheet.Cells[1, 9] = "Item1 Quantity";
                            worksheet.Cells[1, 10] = "Item1 Total";
                            worksheet.Cells[1, 11] = "Item2 Name";
                            worksheet.Cells[1, 12] = "Item2 Rate";
                            worksheet.Cells[1, 13] = "Item2 Quantity";
                            worksheet.Cells[1, 14] = "Item2 Total";
                            worksheet.Cells[1, 15] = "Item3 Name";
                            worksheet.Cells[1, 16] = "Item3 Rate";
                            worksheet.Cells[1, 17] = "Item3 Quantity";
                            worksheet.Cells[1, 18] = "Item3 Total";
                            worksheet.Cells[1, 19] = "Handling Cost";
                            worksheet.Cells[1, 20] = "Billed Amount";
                            worksheet.Cells[1, 21] = "Received Amount";
                            worksheet.Cells[1, 22] = "Paid Date";
                            worksheet.Cells[1, 23] = "Due Amount";

                            int row = 1;
                            for (int k = 1; k <= 23; k++)
                            {
                                worksheet.Cells[row, k].Interior.ColorIndex = 39;
                            }


                            string data = null;

                            int i = 0;

                            int j = 0;


                            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                            {

                                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                                {

                                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();

                                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + 2, j + 1]).Value2 = data;

                                }

                            }

                            excelApp.Visible = true;
                            //xlWorkBook.SaveAs(fName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                            //workbook.Close(true, misValue, misValue);
                            //excelApp.Quit();
                            //releaseObject(worksheet);
                            //releaseObject(workbook);
                            //releaseObject(excelApp);
                        }
                        catch (Exception p)
                        {
                            MessageBox.Show(p.StackTrace);
                        }

                        finally
                        {
                            if (excelApp != null)
                                releaseObject(excelApp);
                            if (workbook != null)
                                releaseObject(workbook);
                            if (worksheet != null)
                                releaseObject(worksheet);
                        }