Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Software Development Software Development
Search Search
Search for:
Tech Support Guy > > >

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


(!)

geek123's Avatar
geek123 geek123 is offline
Member with 44 posts.
THREAD STARTER
 
Join Date: Dec 2011
Experience: Beginner
29-Jun-2012, 11:51 AM #1
Solved: Regarding working with excel sheets using visual c#.
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)
        {

        }
    }
}
panric's Avatar
panric panric is offline
Member with 3 posts.
 
Join Date: May 2012
Experience: Intermediate
01-Jul-2012, 10:23 PM #2
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
geek123's Avatar
geek123 geek123 is offline
Member with 44 posts.
THREAD STARTER
 
Join Date: Dec 2011
Experience: Beginner
15-Jul-2012, 09:08 AM #3
Thanks a lot panric. It worked
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
c# code

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑