Click here to Skip to main content
15,891,607 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

am generating reports in winforms application,Mysql database ,excel sheet is created properly and every thing is fine, but when i shutdown the system it's asking to save book1,book2.....

as how many times we generate report, it is asking to save at system shutdown.

am using following code:

C#
<pre lang="c#">using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Runtime.InteropServices;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using System.Diagnostics;
using iTextSharp.text.html.simpleparser;
using Microsoft.Office.Interop.Excel;
using System.Configuration;
using System.IO;
using System.Reflection;

namespace Billling_Machine
{
    public partial class Reports : Form
    {

        String ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
        MySqlDataAdapter da;
        DataSet ds;
        
        public Reports()
        {
            InitializeComponent();
        }

        private void generate_Click(object sender, EventArgs e)
        {
            int datasetvalue;
            int datatablevalue;

         try
            {
               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);


                }

                else
                {
                    query = "select * from transaction where DATE(paid_date)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";
                    if (reportsmachine.Text != "")
                    {
                        query = query + " AND " + "machine_id=" + "'" + reportsmachine.Text + "' ";
                    }
                    if (reportscustomer.Text != "")
                    {
                        query = query + " AND " + "customer_id=" + "'" + reportscustomer.Text + "' ";

                    }
                    if (reportscard.Text != "")
                    {
                        query = query + " AND " + "card_id=" + "'" + reportscard.Text + "' ";
                    }
                    
                    da = new MySqlDataAdapter(query, con);
                    ds = new DataSet();
                   datasetvalue = da.Fill(ds);

                    datatablevalue = da.Fill(dt);
                }
                if (datasetvalue != 0 && datatablevalue != 0)
                {
                    if (Convert.ToString(comboBox1.SelectedItem) == "EXCEL")
                    {
                        Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();


                        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];

                        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;



                    }//if}}
Posted

1 solution

You should save your report in your code by using: "worksheet.SaveAs(...)" into a default location on your computer.
 
Share this answer
 
Comments
Member 10263519 25-Feb-14 4:50am    
i don't want to save , just i want to watch it out,
Raul Iloc 25-Feb-14 7:00am    
Then you should close it (workbook.Close()) and release the used com resources (excelApp.Quit()).
Member 10263519 26-Feb-14 0:42am    
i have used following code:
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);
}


now , its asking as,Do you want to save in messagebox , if i press no, immediatly EXcel sheet also closing.


but my requirement is just open the excel file ,no need to save it. if i want i will take it print that's it. how to solve this...
Raul Iloc 26-Feb-14 1:00am    
In that case you have to apply my first solution, save the file into a temp folder, because the file must exist to can remain opened!
Member 10263519 26-Feb-14 1:35am    
ok thaq for you reply

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900