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();


Posted in |

27 comments:

  1. Anonymous Says:

    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'

  2. Ketan Patel Says:

    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);
    "

  3. Trương Ngọc Tư Says:

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

  4. Ketan Patel Says:

    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.

  5. khalid Says:

    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

  6. ketan patel Says:

    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.

  7. Anonymous Says:

    How to add 4th worksheet to the excel

  8. Ketan Patel Says:

    @above
    Can u tell me details about your requirement ?

  9. bharathi Says:

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

  10. bharathi Says:

    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... :)

  11. Ketan Patel Says:

    hey bharathi,

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

  12. Ketan Patel Says:

    @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.

  13. bharathi Says:

    @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...

  14. Ketan Patel Says:

    you most welcome bharathi.....

  15. Trương Ngọc Tư Says:

    Thanks you Ketan Patel!

  16. Anonymous Says:

    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

  17. Anonymous Says:

    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

  18. Anonymous Says:

    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

  19. Ketan Patel Says:

    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.

  20. Anonymous Says:

    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

  21. Ketan Patel Says:

    @above,

    It's seem some other problem.

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

  22. bharathi Says:

    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 //

  23. Ketan Patel Says:

    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.

  24. Speclind Capital Management LLC Says:

    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?

  25. Anonymous Says:

    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

  26. Anonymous Says:

    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

  27. Anonymous Says:

    How to make width of some filled column best fit?

Post a Comment