none
to create Excel file and to store content from data table RRS feed

  • Question

  • Hi

    How to create the excel and to add a sheet on it to write  some content. It is working fine if the file already exist.

    How to create an excel with a unique name and I need to write from data table 

      //Creae an Excel application instance
                
                
                Excel.Application excelApp = new Excel.Application();

                //Create an Excel workbook instance and open it from the predefined location


                var myUniqueFileName = string.Format(@"{0}.xlsx", DateTime.Now.Ticks);
                myUniqueFileName = @"E:\NotNeed\" + myUniqueFileName ;

                excelApp.Workbooks.Add(System.Reflection.Missing.Value);
                excelApp.Worksheets.Add();
                excelApp.GetSaveAsFilename(myUniqueFileName);
                Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(myUniqueFileName);

                 

                foreach (DataTable table in ds.Tables)
                {
                    //Add a new worksheet to workbook with the Datatable name
                    Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                    excelWorkSheet.Name = table.TableName;

                    for (int i = 1; i < table.Columns.Count + 1; i++)
                    {
                        excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                    }

                    for (int j = 0; j < table.Rows.Count; j++)
                    {
                        for (int k = 0; k < table.Columns.Count; k++)
                        {
                            excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                        }
                    }
                }

                excelWorkBook.Save();
                excelWorkBook.Close();
                excelApp.Quit();

    Please help and many thanks in advance


    polachan


    Friday, December 2, 2016 4:30 PM

All replies

  • Hi polachan,

    Thank you for posting here.

    Since your problem is more related to Excel, I will move it to Excel for Developers for suitable support.

    The Visual C# forum discuss and ask question about the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar and code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 5, 2016 1:47 AM
  • Hi,

    >>How to create an excel with a unique name

    According to Application.GetSaveAsFilename Method (Excel):

    Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.

    So you did not save the file. You could use SaveAs method to save it. Besides, there is no need to open it again after saving to edit. You could import the data after Workbooks.Add method and then save with specific name.

            

     Excel.Application excelApp = new Excel.Application();
                var myUniqueFileName = string.Format(@"{0}.xlsx", DateTime.Now.Ticks);
                myUniqueFileName = @"D:\" + myUniqueFileName;
    
                Excel.Workbook xlWorkbook=excelApp.Workbooks.Add(System.Reflection.Missing.Value);
                // import data
    
                xlWorkbook.SaveAs(myUniqueFileName);
                xlWorkbook.Close();
                excelApp.Quit();
    

    Regards,

    Celeste



      


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 6, 2016 4:46 AM
    Moderator
  • So, export DataGridView to excel, right.  You can do it this way.

             private void button2_Click(object sender, EventArgs e)
                {
    
                    // creating Excel Application
                    Microsoft.Office.Interop.Excel._Application app  = new Microsoft.Office.Interop.Excel.Application();
                    // creating new WorkBook within Excel application
                    Microsoft.Office.Interop.Excel._Workbook workbook =  app.Workbooks.Add(Type.Missing);
                    // creating new Excelsheet in workbook
                    Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                   
    
                    // see the excel sheet behind the program
                    app.Visible = true;
    
    
                   // get the reference of first sheet. By default its name is Sheet1.
                   // store its reference to worksheet
                   worksheet = workbook.Sheets["Sheet1"];
                   worksheet = workbook.ActiveSheet;
    
                   // changing the name of active sheet
                   worksheet.Name = "Exported from gridview";
    
    
                   // storing header part in Excel
                   for(int i=1;i<dataGridView1.Columns.Count+1;i++)
                    {
                        worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
                    }
    
    
                    // storing Each row and column value to excel sheet
                    for (int i=0; i < dataGridView1.Rows.Count-1 ; i++)
                    {
                        for(int j=0;j<dataGridView1.Columns.Count;j++)
                        {
                            worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        }
                    }
    
    
                    // save the application
                    workbook.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    // Exit from the application
    
                  app.Quit();
                }

    OR

            private void button4_Click(object sender, EventArgs e)
            {
                Excel.Application xlApp ;
                Excel.Workbook xlWorkBook ;
                Excel.Worksheet xlWorkSheet ;
                object misValue = System.Reflection.Missing.Value;
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i = 0;
                int j = 0; 
    
                for (i = 0; i <= dataGridView1.RowCount  - 1; i++)
                {
                    for (j = 0; j <= dataGridView1.ColumnCount  - 1; j++)
                    {
                        DataGridViewCell cell = dataGridView1[j, i];
                        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                    }
                }
    
                xlWorkBook.SaveAs("c:\\test.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                MessageBox.Show("Excel file created , you can find the file c:\\test.xls");
            }


    If you want to export WITH FORMATTING, try it this way.

            private void button5_Click(object sender, EventArgs e)
            {
                const int WORKSHEETSTARTROW = 1;
                const int WORKSHEETSTARTCOL = 1;
    
                var excelApp = new Excel.Application();
                excelApp.Visible = true;
                Excel.Workbook excelbk = excelApp.Workbooks.Add(Type.Missing);
                Excel.Worksheet xlWorkSheet1 = (Excel.Worksheet)excelbk.Worksheets["Sheet1"];
                int worksheetRow = WORKSHEETSTARTROW;
                for (int rowCount = 0; rowCount < dataGridView1.Rows.Count - 1; rowCount++)
                {
                    int worksheetcol = WORKSHEETSTARTCOL;
                for (int colCount = 0; colCount < dataGridView1.Columns.Count - 1; colCount++)
                {
                    Excel.Range xlRange = (Excel.Range)xlWorkSheet1.Cells[WORKSHEETSTARTROW, worksheetcol];
                    xlRange.Value2 = dataGridView1.Columns[colCount].Name;
                    worksheetcol += 1;
                
    
                        if (dataGridView1.Rows[rowCount].Cells[colCount].Style.Font != null)
                        {
                            xlRange.Font.Bold = dataGridView1.Rows[rowCount].Cells[colCount].Style.Font.Bold;
                            xlRange.Font.Italic = dataGridView1.Rows[rowCount].Cells[colCount].Style.Font.Italic;
                            xlRange.Font.Underline = dataGridView1.Rows[rowCount].Cells[colCount].Style.Font.Underline;
                            xlRange.Font.FontStyle = dataGridView1.Rows[rowCount].Cells[colCount].Style.Font.FontFamily;
                        }
                        worksheetcol += 1;
                    }
                    worksheetRow += 1;
    
                }
            }

    Of course, make sure you have the correct references.....

    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 System.Data.OleDb;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Data.SqlClient;
    
    
    namespace WindowsFormsApplication6
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            ////  Your code goes here
    
         }
    }


    MY BOOK

    Tuesday, December 6, 2016 2:07 PM
  • Hi, you could use a .NET Excel component to create a new Workbook from datatable and save it with unique name.

    //connect database
    OleDbConnection connection = new OleDbConnection();
    connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
    OleDbCommand command = new OleDbCommand();
    command.CommandText = "select * from parts";
    DataSet dataSet = new System.Data.DataSet();
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
    dataAdapter.Fill(dataSet);
    DataTable t = dataSet.Tables[0];
    
    //create a new workbook
    Workbook book = new Workbook();
    Worksheet sheet = book.Worksheets[0];
    //export datatable to excel
    sheet.InsertDataTable(t, true, 1, 1);
    //save the excel file with unique name
    string fileName="myUniqueFileName.xlsx"
    book.SaveToFile(fileName);

    Wednesday, December 7, 2016 5:51 AM