Lets Have a fun with Technology.

BTemplates.com

Follow by Email

Wednesday, October 12, 2011

Create/Format Excel File From C#.Net


Create/Format Excel File From C#.Net
Hello Friend’s
In this article I am going to share “How to create excel file from C# and How to format that Excel file”

1.)    Microsoft.Office.InterPro.Excel namespace use to create excel file.

2.)    If this name space does not exists in your solution then add it from add reference.
3.)    Create Excel Application, Worksheet and Workbook Object as given in below example.


  Microsoft.Office.Interop.Excel.Application xlApp;
  Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
  Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
         Microsoft.Office.Interop.Excel.Range chartRange;
  object misValue = System.Reflection.Missing.Value;
  xlApp = new app.Application();
  xlWorkBook = xlApp.Workbooks.Add(misValue);
  xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


Purpose of misValue object :: if we do not pass the parameter then we can use misValue object.

Now let’s play with excel.
4.)    Below statement write my name to Excel file and also Border around it so its look cool.

xlWorkSheet.Cells[6, 3] = "My Name";
xlWorkSheet.Cells[6, 4] = “Ketan
xlWorkSheet.get_Range("d6", "g6").Merge(true);
xlWorkSheet.get_Range("d6", "g6").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);


5.)    Now suppose I want to give some header in excel file. For that Look at below statement.

xlWorkSheet.get_Range("a14", "l14").Merge(false);
chartRange = xlWorkSheet.get_Range("a14", "l14");
chartRange.FormulaR1C1 = "I am Heading";
chartRange.HorizontalAlignment = 3;
chartRange.BorderAround(true);

Above statements merge Excel cells and write statement in it.


6.)    Continue in this Way.  Decide your content position by taking excel positioning in mind. When we write from C# to excel one main point to remember is  Column consider from A-Z  (For more column repeat double letter like aa,ab etc..) and Rows is consider from 1-n.

7.)    You can play with this. You can also create dynamic content in excel by using looping  concept and some logic.

8.)    Below statements set font name and size of excel file.

xlWorkSheet.Cells.Font.Size = 8;
             xlWorkSheet.Cells.Font.Name = "Arial";
     
This two property comes with all cells so if you want to set different font and size for separate cell then you can achive using this properties.

9.)    If you want to format excel cell content position, Alignment Property is there. Let’s look at below example.
                               
xlWorkSheet.Cells[6, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Row[9].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Cells.VerticalAlignment =
                Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;



       First statement make cell content to Left alignment, second statement make full row to left alignment and third statement make Center vertical Alignment.


10.)         You can also specify Width of the Excel Column.
xlWorkSheet.Columns[1].ColumnWidth = 4.0;

11.)         Below statement set wrap property of excel cell.
       xlWorkSheet.Cells[startingrow, 2].WrapText = true;

12.)         Most of property which applicable to Cell we can also apply to Row(s) of excel.

13.)         Now suppose you want to color one cell of excel.

        xlWorkSheet.Cells[1, 1].Interior.Color = Color.Red;
      
         This will fill red color in cell.

14.)         Now save excel file to physical directory.
    
xlWorkBook.SaveAs(strFilepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);

String strFilePath is full path of excel which are going to create.

15.)         Close the excel operation and release all the objects which are used.
  xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();      

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                GC.Collect();
                GC.WaitForPendingFinalizers();


27 comments:

  1. THERE IS ERROR ON YOUR CODING
    protected void btnPrintExcell_Click(object sender, EventArgs e)
    {
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    Excel.Range chartRange;
    object misValue = System.Reflection.Missing.Value;
    xlApp = new Excel.Application();
    xlWorkBook = (xlApp.Workbooks.Add(misValue));
    xlWorkSheet.Cells[6, 3] = "My Name";
    xlWorkSheet.Cells[6, 4] = "Ketan";
    xlWorkSheet.get_Range("d6", "g6").Merge(true);
    }

    ERROR MESSAGE = Error 13 Use of unassigned local variable 'xlWorkSheet'

    ReplyDelete
  2. Hello,

    I apologize for the inconvenience.

    Now error was resolved.

    the missing statement is "xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    "

    ReplyDelete
  3. hello,
    i have question,
    i don't know "app" what?
    "xlApp = new app.Application();"
    erro message in the "app"
    please help me understand! thanks

    ReplyDelete
  4. Hello,

    App is object of "Microsoft.Office.Interop.Excel.Application".

    See in the coding.

    There should be some other problem let me know exact error message.

    ReplyDelete
  5. i got this error "No overload for method 'SaveAs'" at "xlWorkBook.SaveAs(strFilepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);" my be it due to the version of the assembly used

    ReplyDelete
  6. Hello Khalid,

    you have lower version of visual studio.

    This code is developed in 4.0.

    Let me know your visual studio version.

    There should no major changes in lower version.

    ReplyDelete
  7. How to add 4th worksheet to the excel

    ReplyDelete
  8. @above
    Can u tell me details about your requirement ?

    ReplyDelete
  9. no overload for method save as it need 10 arguments i am using 3.5 framework any one help me?

    ReplyDelete
  10. myExcelWorksheet.SaveAs(filepath,Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);

    I TRIED THIS CODING BUT WHEN I OPENING THE EXCEL FILE THIS ERROR CAME "the file you are trying to open "myfilename.xls" is in a different format specified by the file extension verity your file is not corrupted and is from trusted source before opening. etc..." Thanks in advance... :)

    ReplyDelete
  11. hey bharathi,

    try to save in different extension. and also double check extension in File Dialog box.

    ReplyDelete
  12. @bhratathi

    hope you have resolve that 10 arguments error.Above code developed in 4.0 Framework. for other framwork Arguments may vary but after proper modified same code should work for other framwork.

    ReplyDelete
  13. @Ketan Patel
    Thank you so much now its working fine..
    // this is my code

    Excel.Application myExcelApp;
    Excel.Workbook myExcelWorkbook;
    Excel.Worksheet myExcelWorksheet;
    object misValue = System.Reflection.Missing.Value;
    myExcelApp = new Excel.ApplicationClass();

    myExcelWorkbook = myExcelApp.Workbooks.Add(misValue);
    myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
    myExcelApp.Visible = false;

    myExcelWorksheet.SaveAs(filepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
    myExcelApp.Quit();

    i am using this code now its working fine in 3.5 framework...

    ReplyDelete
  14. The type or namespace name 'app' could not be found (are you missing a using directive or an assembly reference?)

    how to remove this error

    ReplyDelete
  15. hey i am with 2 more error. plz tell how can i remove it.

    > No overload for method 'BorderAround' takes '1' arguments

    > No overload for method 'SaveAs' takes '2' arguments

    ReplyDelete
  16. i some how removed last two errors (> No overload for method 'BorderAround' takes '1' arguments

    > No overload for method 'SaveAs' takes '2' arguments)

    but could not remove the 1st error(The type or namespace name 'app' could not be found (are you missing a using directive or an assembly reference?) )

    Plz help

    ReplyDelete
  17. Hello @Above,


    App is object of "Microsoft.Office.Interop.Excel.Application".

    Make confirm that you have added Office reference in your application. It's seem reference miss cause problem with your application.

    If you still in problem then send me your page I will look into.

    ReplyDelete
  18. this is my code (i have added Microsoft.Office.Interop.Excel reference):

    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using Microsoft.Office.Interop.Excel;

    namespace createexcelapp
    {
    public partial class _Default : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    Microsoft.Office.Interop.Excel.Application xlApp;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
    Microsoft.Office.Interop.Excel.Range chartRange;
    object misValue = System.Reflection.Missing.Value;
    xlApp = new app.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


    xlWorkSheet.Cells[6, 3] = "My Name";
    xlWorkSheet.Cells[6, 4] = "Ketan";
    xlWorkSheet.get_Range("d6", "g6").Merge(true);
    xlWorkSheet.get_Range("d6", "g6").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);

    xlWorkSheet.get_Range("a14", "l14").Merge(false);
    chartRange = xlWorkSheet.get_Range("a14", "l14");
    chartRange.FormulaR1C1 = "I am Heading";
    chartRange.HorizontalAlignment = 3;
    //chartRange.BorderAround(true);

    // strFilepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled
    xlWorkBook.SaveAs("C:\\", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
    GC.Collect();
    GC.WaitForPendingFinalizers();
    }
    }
    }


    which reference should i add??

    Whats wrong in the above code??? i use VS2008

    ReplyDelete
  19. @above,

    It's seem some other problem.

    This has been developed in vs 2010 but well work with vs 2008 by changing some arguments.

    ReplyDelete
  20. hi Ketan Patel I have one doubt..
    i am running open dialog box in c# i want to one or more files concurrently using loops... then i want to exe.. files .. i opened any files in opendialog the exe will not running..
    i press the cancel button of open dialog box the exe will running may i know the reason ..
    any help will be greatly appreciated..
    thanks in advance here
    my coding

    TextBox txtfilelocation = new TextBox();
    int cancelcount = 0;
    OpenFileDialog ofdnew = new OpenFileDialog();
    ofdnew.Filter = "xls Files|*.xls|xlsx Files|*.xlsx|Text Files|*.txt";
    ofdnew.Title = "Select File";
    DialogResult resultnew = new DialogResult();
    //runexeDownload();
    for(int i=0;i<3;i++)
    {
    resultnew = ofdnew.ShowDialog();
    ofdnew.Filter = "All Files|*.*";
    ofdnew.FilterIndex = 2;
    ofdnew.RestoreDirectory = true;
    ofdnew.Reset();
    ofdnew.Dispose();
    ofdnew.FileName = "F:\\Documents and Settings\\Origin\\My Documents\\Visual Studio 2008\\Projects\\Test1\\Test1\\bin\\Debug\\Download.txt";
    runexeDownload();
    ////int dialog = 0;
    if (resultnew == DialogResult.OK)
    {
    txtfilelocation.Text = ofdnew.FileName;
    }
    cancelcount = 0;
    while (resultnew == DialogResult.Cancel && cancelcount != 2)
    {
    cancelcount++;
    resultnew = ofdnew.ShowDialog();
    if (resultnew == DialogResult.OK)
    {
    txtfilelocation.Text = ofdnew.FileName;
    }
    }
    if (cancelcount == 2 && resultnew == DialogResult.Cancel)
    {
    ofdnew.Dispose();
    // int filecount = 0;
    }


    }
    //runexeDownload();
    }

    // reply to my mail id bharathiit09@gmail.com thanks in advance..


    **** i think little bit diff to understand the problem... doubts on my questions free to mail @ bharathiit09@gmail.com //

    ReplyDelete
  21. Hello bharathi,

    Sorry I am not clear in your question.

    Can you send me your solution ? so I can check it.

    And also explain your Output clearly in mail.

    ReplyDelete
  22. Hello, how would i write the code if i wanted to write to next row if first row is full? For example my program displays the temperature of 3 cities. Today I use the program to write the results to excel file "Results.xls" Tomorrow i want the updated values for the new day to go into the next avalible row in the same file. How do I do this?

    ReplyDelete
  23. xlWorkSheet.Cells.Font.Size = 8;
    xlWorkSheet.Cells.Font.Name = "Arial";

    Its not working.. These statements give following error:
    Unable to set the Size property of the Font class
    Unable to set the Name property of the Font class

    ReplyDelete
  24. how to write new column in a excel sheet which is generated through a console app which pull data from db and generates excel reports?

    pls mail me if possible : sptalkshan@gmail.com

    ReplyDelete
  25. How to make width of some filled column best fit?

    ReplyDelete