1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Regarding working with excel sheets using visual c#.

Discussion in 'Software Development' started by geek123, Jun 29, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. geek123

    geek123 Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    44
    Hello all,

    I have this visual c# code when I run this firstly it will open a dummy excel sheet with no data in it along with a form having two buttons. If i click on first button then it opens another excel sheet and writes some data to it.

    What I want is to save that second excel sheet with data written to it but this code is not doing that instead it is saving that dummy excel sheet which it opens first. And also if i try to save the second sheet then it is asking prompt to save it which I don't want. I want it to save the second excel sheet automatically without a prompt.

    Please help me as I just want to open only one excel sheet so that the required data what I want can be written to it and it is automatically saved without prompting me to save it. And if I run the program again with some more data then it should be able to do it with only that one excel sheet and save it without a prompt to save.

    Here is the code:-

    Code:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace DC
    {
        public partial class Form1 : Form
        {
            private Excel.Application app = null;
            private Excel.Workbook workbook = null;
            private Excel.Workbook workbook1 = null;
            private Excel.Worksheet worksheet = null;
            private Excel.Range workSheet_range = null;
    
            public Form1()
            {
                InitializeComponent();
                //createDoc();
    
            }
    
            public void createDoc()
            {
                try
                {
                    app = new Excel.Application();
                    app.Visible = true;
                    workbook = app.Workbooks.Add(1);
                    worksheet = (Excel.Worksheet)workbook.Sheets[1];
                }
                catch (Exception e)
                {
                    Console.Write("Error");
                }
                finally
                {
                }
            }
    
            public void createHeaders(int row, int col, string htext, string cell1,
            string cell2, int mergeColumns, string b, bool font, int size, string
            fcolor)
            {
                worksheet.Cells[row, col] = htext;
                workSheet_range = worksheet.get_Range(cell1, cell2);
                workSheet_range.Merge(mergeColumns);
                switch (b)
                {
                    case "YELLOW":
                        workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
                        break;
                    case "GRAY":
                        workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
                        break;
                    case "GAINSBORO":
                        workSheet_range.Interior.Color =
                System.Drawing.Color.Gainsboro.ToArgb();
                        break;
                    case "Turquoise":
                        workSheet_range.Interior.Color =
                System.Drawing.Color.Turquoise.ToArgb();
                        break;
                    case "PeachPuff":
                        workSheet_range.Interior.Color =
                System.Drawing.Color.PeachPuff.ToArgb();
                        break;
                    default:
                        //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                        break;
                }
    
                workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
                workSheet_range.Font.Bold = font;
                workSheet_range.ColumnWidth = size;
                if (fcolor.Equals(""))
                {
                    workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
                }
                else
                {
                    workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
                }
            }
    
            public void addData(int row, int col, string data,
                string cell1, string cell2, string format)
            {
                worksheet.Cells[row, col] = data;
                workSheet_range = worksheet.get_Range(cell1, cell2);
                workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
                workSheet_range.NumberFormat = format;
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
    
    
                Form1 excell_app = new Form1();
                excell_app.createDoc();
                //creates the main header
                excell_app.createHeaders(5, 2, "'ERROR MESSAGE", "B5", "D5", 2, "YELLOW", true, 10, "n");
                //creates subheaders
                excell_app.createHeaders(6, 2, "'0000343477", "B6", "B6", 0, "GRAY", true, 10, "");
                excell_app.createHeaders(6, 3, "", "C6", "C6", 0, "GRAY", true, 10, "");
                excell_app.createHeaders(6, 4, "Initial Total", "D6", "D6", 0, "GRAY", true, 10, "");
                //add Data to cells
                excell_app.addData(7, 2, "114287", "B7", "B7", "#,##0");
                excell_app.addData(7, 3, "", "C7", "C7", "");
                excell_app.addData(7, 4, "129121", "D7", "D7", "#,##0");
                //add percentage row
                excell_app.addData(8, 2, "", "B8", "B8", "");
                excell_app.addData(8, 3, "=B7/D7", "C8", "C8", "0.0%");
                excell_app.addData(8, 4, "", "D8", "D8", "");
                //add empty divider
                excell_app.createHeaders(9, 2, "", "B9", "D9", 2, "GAINSBORO", true, 10, "");
    
                //Form1 excell_app1 = new Form1();
                //app.SaveWorkspace("C:\\spec\\1111111.xls");
                excell_app.app.SaveWorkspace("C:\\spec\\1123.xls");
                
                //excell_app.app.SaveWorkspace("C:\\spec\\aaaaaa.xls")
                
                
               // workbook1 = app.Workbooks.Add(1);
               // worksheet = (Excel.Worksheet)workbook1.Sheets[2];
    
                excell_app.createHeaders(5, 2, "ERROR MESSAGE", "A5", "J5", 2, "YELLOW", true, 10, "n");
                //creates subheaders
                excell_app.createHeaders(6, 2, "Sold Product", "B6", "B6", 0, "GRAY", true, 10, "");
                excell_app.createHeaders(6, 3, "", "C6", "C6", 0, "GRAY", true, 10, "");
                excell_app.createHeaders(6, 4, "Initial Total", "D6", "D6", 0, "GRAY", true, 10, "");
                //add Data to cells
                excell_app.addData(7, 2, "114287", "B7", "B7", "#,##0");
                excell_app.addData(7, 3, "", "C7", "C7", "");
                excell_app.addData(7, 4, "129121", "D7", "D7", "#,##0");
                //add percentage row
                excell_app.addData(8, 2, "", "B8", "B8", "");
                excell_app.addData(8, 3, "=B7/D7", "C8", "C8", "0.0%");
                excell_app.addData(8, 4, "", "D8", "D8", "");
                //add empty divider
                excell_app.createHeaders(9, 2, "", "B9", "D9", 2, "GAINSBORO", true, 10, "");
    
    
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
    
            }
        }
    }
    
     
  2. panric

    panric

    Joined:
    May 7, 2012
    Messages:
    3
    Using System.IO;// Import namespace,
    and then check file and delete it if it exists,
    try {
    if (File.Exists("your target file"))
    File.Delete("your target file");
    }
    catch{
    }
    Best Regards
     
  3. geek123

    geek123 Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    44
    Thanks a lot panric. It worked
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1059036