Export data into Excell format

Hello,

I'd like to know how to export data into excell format.
that's for the windows application.

There could be two variants:

1. exporting a ready dataset/ datatable/ or other collection/storage structure (ListArray?)

2. and manually adding values to the different  cells of the spread sheet

code snippet would be much appreciated

regards
LVL 21
YurichAsked:
Who is Participating?
 
vinhnlCommented:
1. See this url: http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21043608.html

or

2. use Microsoft.Office.Interop.Excel: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_oxppias.asp

using System;
using System.Data.OleDb;
using System.Reflection; // For Missing.Value and BindingFlags
using System.Runtime.InteropServices; // For COMException
using System.Collections;
using System.ComponentModel;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
using System.IO;

namespace libExcel
{
      /// <summary>
      /// Summary description for clsExcelObject.
      /// </summary>
      public class clsExcelObject
      {
            public enum Error
            {
                  NO_ERROR = 0,
                  CANNT_CREATE_EXCEL_APP = 1,
                  CANNT_OPEN_EXCEL_FILE = 2,
                  WORKSHEETS_IS_EMPTY = 3,
                  EXCEL_APP_IS_CLOSED = 4,
                  CANNT_CLOSE_EXCEL_APP = 5,
                  CANNT_SET_COLOR = 6,
                  CANNT_CHANGE_CELL_VALUE = 7,
                  CANNT_CHANGE_RANGE = 8,
                  CANNT_GET_RANGE = 9
            }

            #region Declare Variables
            private string strFullFilePath;
            private Microsoft.Office.Interop.Excel.Application myExcel = null;
            private Microsoft.Office.Interop.Excel.Workbooks myWorkbooks = null;
            private Microsoft.Office.Interop.Excel._Workbook myWorkbook = null;
            private Microsoft.Office.Interop.Excel.Sheets myWorksheets = null;
            private Microsoft.Office.Interop.Excel._Worksheet myWorksheet = null;

            private string strMessage = "";
            private Error intCode = Error.NO_ERROR;
            private bool blnChangedFullFilePath = false;
            #endregion

            #region Properties
            public string FullFileName
            {
                  get
                  {
                        return strFullFilePath;
                  }
                  set
                  {
                        strFullFilePath = value;
                        blnChangedFullFilePath = true;
                  }
            }

            public string Message
            {
                  get
                  {
                        return strMessage;
                  }
            }

            public Error Code
            {
                  get
                  {
                        return intCode;
                  }
            }
            #endregion

            #region Constructor
            public clsExcelObject()
            {
            }
            #endregion

            #region Private Methods
            #endregion

            #region Public Interfaces
            /// <summary>  
            /// Xóa c&#7901; l&#7895;i n&#7871;u nó &#273;ang &#273;&#432;&#7907;c d&#7921;ng
            /// </summary>
            public void ClearError()
            {
                  strMessage = "";
                  intCode = Error.NO_ERROR;
            }

            /// <summary>  
            /// M&#7903; file Excel nh&#432;ng không hi&#7879;n. Tên file ch&#7913;a trong FullFileName
            /// </summary>
            public void Open()
            {
                  //Xoa loi
                  ClearError();
                  //xoa cac bien trong bo nho
                  myExcel = null;
                  myWorkbook = null;
                  myWorkbooks = null;
                  myWorksheet = null;
                  myWorksheets = null;
                  //tao doi tuong Excel
                  try
                  {
                        myExcel = new Microsoft.Office.Interop.Excel.Application();
                  }
                  catch(Exception)
                  {
                        myExcel = null;
                        strMessage = "Không th&#7875; m&#7903; &#273;&#432;&#7907;c t&#7879;p tin này!";
                        intCode = Error.CANNT_OPEN_EXCEL_FILE;
                        System.Windows.Forms.MessageBox.Show("Microsoft Excel b&#7883; l&#7895;i. N&#7871;u g&#7863;p l&#7841;i l&#7895;i này m&#7897;t \nl&#7847;n n&#7919;a, xin hãy cài l&#7841;i b&#7843;n Microsoft Excel XP","Thông báo",System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Stop);
                        return;
                  }
                  if (myExcel == null)
                  {
                        strMessage = "Không th&#7875; kh&#7903;i t&#7841;o &#273;&#432;&#7907;c &#7913;ng d&#7909;ng Excel!";
                        intCode = Error.CANNT_CREATE_EXCEL_APP;
                        return;
                  }
                  //an doi tuong Excel
                  try
                  {
                        myExcel.Visible = false;
                  }
                  catch(Exception)
                  {
                        myExcel = null;
                        strMessage = "Không th&#7875; m&#7903; &#273;&#432;&#7907;c t&#7879;p tin này!";
                        intCode = Error.CANNT_OPEN_EXCEL_FILE;
                        System.Windows.Forms.MessageBox.Show("Microsoft Excel b&#7883; l&#7895;i. N&#7871;u g&#7863;p l&#7841;i l&#7895;i này m&#7897;t \nl&#7847;n n&#7919;a, xin hãy cài l&#7841;i b&#7843;n Microsoft Excel XP","Thông báo",System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Stop);
                        return;
                  }
                  //tro den doi tuong Workbooks
                  myWorkbooks = myExcel.Workbooks;
                  //Kiem tra tep tin da duoc mo
                  if(!libServices.FileIsNoUsed(strFullFilePath))
                  {
                        myExcel = null;
                        strMessage = "Không th&#7875; m&#7903; &#273;&#432;&#7907;c t&#7879;p tin này!";
                        intCode = Error.CANNT_OPEN_EXCEL_FILE;
                        return;
                  }
                  //mo file Excel
                  try
                  {
                        myWorkbook = myWorkbooks.Open(strFullFilePath, 0, false, 5,      "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false,0, true,true,false);
                  }

                  catch(COMException)
                  {
                        myExcel.UserControl = false;
                        myExcel.Quit();
                        myExcel = null;
                        strMessage = "Không th&#7875; m&#7903; &#273;&#432;&#7907;c t&#7879;p tin này!";
                        intCode = Error.CANNT_OPEN_EXCEL_FILE;
                        return;
                  }

                  catch(Exception)
                  {
                        myExcel.UserControl = false;
                        myExcel.Quit();
                        myExcel = null;
                        strMessage = "Không th&#7875; m&#7903; &#273;&#432;&#7907;c t&#7879;p tin này!";
                        intCode = Error.CANNT_OPEN_EXCEL_FILE;
                        System.Windows.Forms.MessageBox.Show("Microsoft Excel b&#7883; l&#7895;i. N&#7871;u g&#7863;p l&#7841;i l&#7895;i này m&#7897;t \nl&#7847;n n&#7919;a, xin hãy cài l&#7841;i b&#7843;n Microsoft Excel XP","Thông báo",System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Stop);
                        return;
                  }

                  //tro den worksheets
                  myWorksheets = myWorkbook.Worksheets;

                  //tro den worksheet
                  myWorksheet = (_Worksheet) myWorksheets.get_Item(1);
                  if(myWorkbook==null)
                  {
                        myExcel.UserControl = false;
                        myExcel.Quit();
                        myExcel = null;
                        strMessage = "Không có Sheet nào trong b&#7843;ng Excel";
                        intCode = Error.WORKSHEETS_IS_EMPTY;
                        return;
                  }
                  blnChangedFullFilePath = false;
            }

            /// <summary>  
            /// &#272;óng &#7913;ng d&#7909;ng Excel &#273;ang &#273;&#432;&#7907;c m&#7903;
            /// </summary>
            public void Close()
            {
                  ClearError();
                  //Kiem tra xem Excel App co ton tai
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return;
                  }

                  try
                  {
                        // If user interacted with Excel it will not close when the app object is destroyed, so we close it explicitely                                                
                        myWorkbook.Saved = true;
                        myExcel.UserControl = false;
                        myWorkbook.Close(false,this.FullFileName,false);
                        myExcel.Quit();
                  }
                  catch (COMException)
                  {
                        strMessage = "Ng&#432;&#7901;i dùng &#273;ã &#273;óng ch&#432;&#417;ng trình Excel b&#7857;ng tay";
                        intCode = Error.CANNT_CLOSE_EXCEL_APP;
                        return;
                  }
            
                  myExcel = null;
                  myWorkbook = null;
                  myWorkbooks = null;
                  myWorksheet = null;
                  myWorksheets = null;


                  System.GC.Collect();

            }

            /// <summary>  
            /// L&#7845;y ra d&#7919; li&#7879;u c&#7911;a cell t&#7841;i &#273;&#7883;a ch&#7881; nào &#273;ó.
            /// Ví d&#7909;: GetCell(1,2); T&#432;&#417;ng &#7913;ng v&#7899;i d&#7919; li&#7879;u c&#7911;a cell "A2"
            /// </summary>
            /// <param name="RowIndex">
            /// &#272;&#7883;a ch&#7881; dòng
            /// </param>
            /// <param name="ColIndex">
            /// &#272;&#7883;a ch&#7881; c&#7897;t
            /// </param>
            public object GetCell(int RowIndex, int ColIndex)
            {
                  string strAdd1,strAdd2;
                  ClearError();
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return null;
                  }
                  Range range1;
                  if(ColIndex<=((int)'Z'-(int)'A'+1))
                  {
                        strAdd1 = (char)(ColIndex+(int)'A'-1)+RowIndex.ToString();
                        strAdd2 = (char)(ColIndex+(int)'A'-1)+((int)(RowIndex+1)).ToString();
                  }
                  else
                  {
                        strAdd1 = ((char)(ColIndex/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+RowIndex.ToString();
                        strAdd2 = ((char)(ColIndex/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((int)(RowIndex+1)).ToString();
                  }

                  Object[,] arr = null;
                  try
                  {
                        range1 = myWorksheet.get_Range(strAdd1, strAdd2);
                        arr = (Object[,])range1.Value2;
                  }
                  catch(Exception)
                  {
                        intCode = Error.CANNT_GET_RANGE;
                        strMessage = "Không th&#7875; l&#7845;y &#273;&#432;&#7907;c giá tr&#7883; range";
                  }
                  return arr.GetValue(arr.GetLowerBound(0),arr.GetLowerBound(1));
            }

            /// <summary>  
            /// Xem d&#7919; li&#7879;u t&#7841;i &#273;&#7883;a ch&#7881; này là d&#7841;ng d&#7919; li&#7879;u hay d&#7841;ng công th&#7913;c
            /// Ví d&#7909;: IsFormula(1,2)
            /// </summary>
            /// <param name="RowIndex">
            /// &#272;&#7883;a ch&#7881; dòng
            /// </param>
            /// <param name="ColIndex">
            /// &#272;&#7883;a ch&#7881; c&#7897;t
            /// </param>
            public bool IsFormula(int RowIndex, int ColIndex)
            {
                  string strAdd1,strAdd2;
                  bool blResult = false;
                  ClearError();
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return false;
                  }
                  Range range1;
                  if(ColIndex<=((int)'Z'-(int)'A'+1))
                  {
                        strAdd1 = (char)(ColIndex+(int)'A'-1)+RowIndex.ToString();
                        strAdd2 = (char)(ColIndex+(int)'A'-1)+((int)(RowIndex+1)).ToString();
                  }
                  else
                  {
                        strAdd1 = ((char)(ColIndex/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+RowIndex.ToString();
                        strAdd2 = ((char)(ColIndex/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((int)(RowIndex+1)).ToString();
                  }

                  try
                  {
                        range1 = myWorksheet.get_Range(strAdd1, strAdd2);
                        blResult = (bool)range1.get_Resize(1,1).HasFormula;
                  }
                  catch(Exception)
                  {
                        intCode = Error.CANNT_GET_RANGE;
                        strMessage = "Không th&#7875; l&#7845;y &#273;&#432;&#7907;c giá tr&#7883; range";
                  }

                  return blResult;
            }

            /// <summary>  
            /// L&#7845;y công th&#7913;c có t&#7841;i &#273;&#7883;a ch&#7881; RowIndex và ColIndex
            /// Ví d&#7909;: GetFormula(1,2)
            /// </summary>
            /// <param name="RowIndex">
            /// &#272;&#7883;a ch&#7881; dòng
            /// </param>
            /// <param name="ColIndex">
            /// &#272;&#7883;a ch&#7881; c&#7897;t
            /// </param>
            public string GetFormula(int RowIndex, int ColIndex)
            {
                  string strAdd1,strAdd2;
                  string strResult = "";
                  ClearError();
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return null;
                  }
                  Range range1;
                  if(ColIndex<=((int)'Z'-(int)'A'+1))
                  {
                        strAdd1 = (char)(ColIndex+(int)'A'-1)+RowIndex.ToString();
                        strAdd2 = (char)(ColIndex+(int)'A'-1)+((int)(RowIndex+1)).ToString();
                  }
                  else
                  {
                        strAdd1 = ((char)(ColIndex/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+RowIndex.ToString();
                        strAdd2 = ((char)(ColIndex/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((int)(RowIndex+1)).ToString();
                  }

                  try
                  {
                        range1 = myWorksheet.get_Range(strAdd1, strAdd2);
                        strResult = range1.get_Resize(1,1).Formula.ToString();
                  }

                  catch(Exception)
                  {
                        intCode = Error.CANNT_GET_RANGE;
                        strMessage = "Không th&#7875; l&#7845;y &#273;&#432;&#7907;c giá tr&#7883; range";
                  }

                  return strResult;
            }

            /// <summary>  
            /// L&#7845;y d&#7919; li&#7879;u t&#7915; m&#7897;t range ra m&#7897;t m&#7843;ng string
            /// </summary>
            /// <example>
            /// GetRange(1,2,4,5);(B1:E4)
            /// </example>
            /// <returns>
            /// Object[Rows,Cols] Array: M&#7843;ng ch&#7913;a 2 chi&#7873;u d&#7919; li&#7879;u d&#7841;ng object
            /// </returns>                  
            /// <param name="RowIndex1">
            /// &#272;&#7883;a ch&#7881; dòng c&#7911;a &#273;i&#7875;m trên trái
            /// </param>
            /// <param name="ColIndex1">
            /// &#272;&#7883;a ch&#7881; c&#7897;t c&#7911;a &#273;i&#7875;m trên trái
            /// </param>
            /// <param name="RowIndex2">
            /// &#272;&#7883;a ch&#7881; dòng c&#7911;a &#273;i&#7875;m d&#432;&#7899;i ph&#7843;i
            /// </param>
            /// <param name="ColIndex2">
            /// &#272;&#7883;a ch&#7881; c&#7897;t c&#7911;a &#273;i&#7875;m d&#432;&#7899;i ph&#7843;i
            /// </param>            
            public Object[,] GetRange(int RowIndex1, int ColIndex1, int RowIndex2, int ColIndex2)
            {
                  string strAdd1,strAdd2;
                  Object[,] array1 = null;
                  ClearError();
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return null;
                  }
                  Range range1 = null;
                  if(ColIndex1<=((int)'Z'-(int)'A'+1))
                  {
                        strAdd1 = (char)(ColIndex1+(int)'A'-1)+RowIndex1.ToString();
                  }
                  else
                  {
                        strAdd1 = ((char)(ColIndex1/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex1%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+RowIndex1.ToString();
                  }

                  if(ColIndex2<=((int)'Z'-(int)'A'+1))
                  {
                        strAdd2 = (char)(ColIndex2+(int)'A'-1)+RowIndex2.ToString();
                  }
                  else
                  {
                        strAdd2 = ((char)(ColIndex2/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex2%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+RowIndex2.ToString();
                  }

                  try
                  {
                        range1 = myWorksheet.get_Range(strAdd1, strAdd2);
                        array1 = new Object [RowIndex2-RowIndex1+1, ColIndex2-ColIndex1+1];
                        array1 = (Object[,])range1.Value2;
                  }
                  catch(Exception)
                  {
                        intCode = Error.CANNT_GET_RANGE;
                        strMessage = "Không th&#7875; l&#7845;y &#273;&#432;&#7907;c giá tr&#7883; range";
                  }

                  return array1;
            }

            /// <summary>  
            /// Gán d&#7919; li&#7879;u t&#7915; m&#7897;t array vào m&#7897;t range c&#7911;a Excel
            /// </summary>
            /// <example>
            /// SetRange(1,2,array1);(B1)
            /// </example>
            /// <param name="RowIndex">
            /// &#272;&#7883;a ch&#7881; dòng c&#7911;a &#273;i&#7875;m trên trái
            /// </param>
            /// <param name="ColIndex">
            /// &#272;&#7883;a ch&#7881; c&#7897;t c&#7911;a &#273;i&#7875;m trên trái
            /// </param>
            /// <param name="array1">
            /// M&#7843;ng ch&#7913;a d&#7919; li&#7879;u
            /// </param>
            public void SetRange(int RowIndex, int ColIndex, Object[,] array1)
            {
                  string strAdd1,strAdd2;
                  int RowIndex1, ColIndex1;
                  int RowIndex2, ColIndex2;
                  RowIndex1 = RowIndex;
                  ColIndex1 = ColIndex;
                  RowIndex2 = RowIndex + array1.GetUpperBound(0)-array1.GetLowerBound(0);
                  ColIndex2 = ColIndex + array1.GetUpperBound(1)-array1.GetLowerBound(1);
                  ClearError();
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return;
                  }
                  Range range1;
                  if(ColIndex1<=((int)'Z'-(int)'A'+1))
                  {
                        strAdd1 = (char)(ColIndex1+(int)'A'-1)+RowIndex1.ToString();
                  }
                  else
                  {
                        strAdd1 = ((char)(ColIndex1/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex1%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+RowIndex1.ToString();
                  }

                  if(ColIndex2<=((int)'Z'-(int)'A'+1))
                  {
                        strAdd2 = (char)(ColIndex2+(int)'A'-1)+RowIndex2.ToString();
                  }
                  else
                  {
                        strAdd2 = ((char)(ColIndex2/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex2%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+RowIndex2.ToString();
                  }

                  try
                  {
                        range1 = myWorksheet.get_Range(strAdd1, strAdd2);
                        range1.Value2 = array1;
                  }
                  catch(Exception)
                  {
                        intCode = Error.CANNT_CHANGE_RANGE;
                        strMessage = "Không th&#7875; thay &#273;&#7893;i range";
                  }
            }

            /// <summary>  
            /// Gán giá tr&#7883; ho&#7863;c hàm cho m&#7897;t cell
            /// </summary>
            /// <example>
            /// SetCell(1,3,"=A1*2+A2");
            /// </example>
            /// <param name="RowIndex">
            /// &#272;&#7883;a ch&#7881; dòng
            /// </param>
            /// <param name="ColIndex">
            /// &#272;&#7883;a ch&#7881; c&#7897;t
            /// </param>
            /// <param name="item">
            /// Giá tr&#7883; c&#7911;a cell
            /// </param>
            public void SetCell(int RowIndex, int ColIndex, object item)
            {
                  ClearError();
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return;
                  }

                  try
                  {
                        myWorksheet.Cells.set_Item(RowIndex,ColIndex,item);
                  }
                  catch(Exception)
                  {
                        intCode = Error.CANNT_CHANGE_CELL_VALUE;
                        strMessage = "Không th&#7875; &#273;&#7863;t l&#7841;i giá tr&#7883; cho cell này";
                  }
            }

            /// <summary>  
            /// &#272;&#7893;i m&#7847;u c&#7911;a m&#7897;t cell
            /// </summary>
            /// <example>
            /// ChangeColor(1,3,System.Drawing.Color.Red);
            /// </example>
            /// <param name="RowIndex">
            /// &#272;&#7883;a ch&#7881; dòng
            /// </param>
            /// <param name="ColIndex">
            /// &#272;&#7883;a ch&#7881; c&#7897;t
            /// </param>
            /// <param name="Color">
            /// M&#7847;u c&#7847;n &#273;&#7863;t cho Cell
            /// </param>
            public void ChangeColor(int RowIndex, int ColIndex, long Color)
            {
                  string strAdd1;
                  ClearError();
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return;
                  }
                  Range range1;
                  if(ColIndex<=((int)'Z'-(int)'A'+1))
                  {
                        strAdd1 = (char)(ColIndex+(int)'A'-1)+RowIndex.ToString();
                  }
                  else
                  {
                        strAdd1 = ((char)(ColIndex/(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+((char)(ColIndex%(((int)'Z'-(int)'A'+1))+(int)'A'-1)).ToString()+RowIndex.ToString();
                  }

                  try
                  {
                        range1 = myWorksheet.get_Range(strAdd1, strAdd1);
                        //myExcel.Visible = true;
                        range1.Font.Color = (double)Color;
                  }
                  catch(Exception)
                  {
                        strMessage = "Không th&#7875; &#273;&#7863;t l&#7841;i m&#7847;u cho cell này";
                        intCode = Error.CANNT_SET_COLOR;
                  }
            }

            /// <summary>  
            /// Ghi l&#7841;i file Excel
            /// Chú ý: N&#7871;u tên file Excel b&#7883; &#273;&#7893;i thì t&#432;&#417;ng &#273;&#432;&#417;ng v&#7899;i vi&#7879;c SaveAs.
            /// N&#7871;u &#273;ã có file Excel có tên t&#432;&#417;ng &#7913;ng thì h&#7879; th&#7889;ng s&#7869; ghi &#273;è lên file c&#361;
            /// </summary>
            public void Save()
            {
                  ClearError();
                  if(myExcel==null)
                  {
                        strMessage = "Excel App r&#7895;ng";
                        intCode = Error.EXCEL_APP_IS_CLOSED;
                        return;
                  }
                  if(blnChangedFullFilePath)
                  {
                        while(System.IO.File.Exists(strFullFilePath))
                        {
                              try
                              {
                                    System.IO.File.Delete(strFullFilePath);
                              }
                              catch(System.IO.IOException)
                              {
                                    System.Windows.Forms.DialogResult result = System.Windows.Forms.MessageBox.Show(null,"Xin ha&#771;y &#273;o&#769;ng tê&#803;p tin "
                                          +strFullFilePath+ " tr&#432;&#417;&#769;c khi nhâ&#769;n OK &#273;ê&#777; tiê&#769;p t&#432;&#803;c l&#432;u tr&#432;&#771;, ho&#259;&#803;c nhâ&#769;n Cancel &#273;ê&#777; bo&#777; qua thao ta&#769;c l&#432;u tr&#432;&#771; na&#768;y",
                                          "Thông ba&#769;o",System.Windows.Forms.MessageBoxButtons.OKCancel,System.Windows.Forms.MessageBoxIcon.Warning);
                                    if(result == System.Windows.Forms.DialogResult.Cancel)
                                          return;
                              }
                        }
                        
                        myWorkbook.SaveAs(strFullFilePath,Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,null,null,false,false,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,false,false,null,null,null);
                  }
                  else
                  {
                        
                        myWorkbook.Save();
                  }
            }

                        /// <summary>
            /// L&#7845;y kích th&#432;&#7899;c c&#7911;a m&#7897;t b&#7843;ng Excel tuân th&#7911; theo cách &#273;&#7883;nh d&#7841;ng c&#7911;a MIS-Hatinh
            /// </summary>
            /// <param name="strFileName">&#272;&#432;&#7901;ng d&#7851;n &#273;&#7847;y &#273;&#7911; &#273;&#7871;n t&#7879;p tin ch&#7913;a file Excel</param>
            /// <returns>kích th&#432;&#7899;c c&#7911;a b&#7843;ng</returns>
            public static System.Drawing.Size GetSizeOfExcel(string strFileName)
            {
                  System.Drawing.Size MySize;
                  if(File.Exists(strFileName))
                        if(libServices.FileIsNoUsed(strFileName))
                        {
                              clsExcelObject myExcel = new clsExcelObject();
                              myExcel.FullFileName = strFileName;
                              //Open Excel file but not show
                              myExcel.Open();

                              if(myExcel.Code==0)
                                    MySize = GetSizeOfExcel(myExcel);
                              else
                                    MySize = new System.Drawing.Size(0,0);
                              
                              myExcel.Close();
                        }
                        else
                              MySize = new System.Drawing.Size(0,0);
                  else
                        MySize = new System.Drawing.Size(0,0);
                  return MySize;
            }

            /// <summary>
            /// L&#7845;y kích th&#432;&#7899;c c&#7911;a m&#7897;t b&#7843;ng Excel tuân th&#7911; theo cách &#273;&#7883;nh d&#7841;ng c&#7911;a MIS-Hatinh
            /// </summary>
            /// <param name="myExcel">&#272;&#7889;i t&#432;&#7907;ng Excel &#273;ã &#273;&#432;&#7907;c m&#7903; s&#7861;n</param>
            /// <returns>kích th&#432;&#7899;c c&#7911;a b&#7843;ng</returns>
            public static System.Drawing.Size GetSizeOfExcel(clsExcelObject myExcel)
            {
                  int intError = 0;
                  System.Drawing.Size MySize = new System.Drawing.Size(0,0);
                  //Get col data array
                  object[,] arCol = myExcel.GetRange(1,1,1,1000);
                  intError = intError + (int)myExcel.Code;
                  //Get row data array
                  object[,] arRow = myExcel.GetRange(1,1,1000,1);
                  intError = intError + (int)myExcel.Code;

                  if(intError == 0)
                  {
                        //Get bound of arrays
                        int iColX0 = arCol.GetLowerBound(1), iColX1 = arCol.GetUpperBound(1),
                              iColY = arCol.GetLowerBound(0);
                        int iRowX = arRow.GetLowerBound(1), iRowY0 = arRow.GetLowerBound(0),
                              iRowY1 = arRow.GetUpperBound(0);

                        //cal actual bound
                        for(int i=iColX1 ; i>=iColX0 ; i--)
                              if( arCol[iColY,i]!=null)
                                    if(libServices.isNumeric(arCol[iColY,i].ToString()))
                                    {
                                          MySize.Width = (i - iColX0 + 1);
                                          break;
                                    }

                        for(int i=iRowY1 ; i>=iRowY0 ; i--)
                              if( arRow[ i , iRowX ]!=null)
                                    if(libServices.isNumeric(arRow[ i , iRowX ].ToString()))
                                    {
                                          MySize.Height = (i - iRowY0 + 1);
                                          break;
                                    }
                  }

                  return MySize;
            }

            /// <summary>
            /// &#272;i&#7873;n các thông tin thêm vào các m&#7851;u trong file Excel
            /// </summary>
            /// <param name="strFileName"></param>
            /// <param name="strPartern"></param>
            /// <param name="strInfo"></param>
            public static void ReplaceInfo2Excel(string strFileName, string strPartern, string strInfo)
            {
                  if(!File.Exists(strFileName))
                        return;
                  if(libServices.FileIsNoUsed(strFileName))
                        return;

                  clsExcelObject myExcel = new clsExcelObject();
                  myExcel.FullFileName = strFileName;
                  //Open Excel file but not show
                  myExcel.Open();

                  if(myExcel.Code==0)
                        ReplaceInfo2Excel(myExcel,strPartern,strInfo);

                  myExcel.Save();
                  myExcel.Close();
            }

            /// <summary>
            /// &#272;i&#7873;n s&#7889; li&#7879;u t&#7915; m&#7897;t &#273;&#7889;i Excel có s&#7861;n vào m&#7897;t &#273;&#7889;i t&#432;&#7907;ng Excel khác cùng m&#7851;u
            /// </summary>
            /// <param name="myData">&#272;&#7889;i t&#432;&#7907;ng Excel ch&#7913;a d&#7919; li&#7879;u có s&#7861;n</param>
            /// <param name="myReport">&#272;&#7889;i t&#432;&#7907;ng Excel c&#7847;n &#273;i&#7873;n d&#7919; li&#7879;u</param>
            public static void BindDataToExcel(clsExcelObject myData, clsExcelObject myReport)
            {
                  int intError =0;
                  //Lay cac thong tin ve bound cua cac bang
                  System.Drawing.Size mySize1 = GetSizeOfExcel(myData);
                  System.Drawing.Size mySize2 = GetSizeOfExcel(myReport);
                  //Kiem tra khong lay duoc
                  if(mySize1.Height==0||mySize2.Height==0||mySize2.Width!=mySize1.Width)
                        return;
                  //Lay du lieu tu bang DATA
                  object[,] arData = myData.GetRange(1,1,mySize1.Height,mySize1.Width);
                  intError += (int)myReport.Code;
                  //Lay du lieu tu bang REPORT
                  object[,] arReport = myReport.GetRange(1,1,mySize2.Height,mySize2.Width);
                  intError += (int)myReport.Code;
                  //Tao bang du lieu TEMP
                  object[,] arTemp = new object[mySize2.Height,mySize2.Width];
                  bool[,] arChanged = new bool[mySize2.Height,mySize2.Width];
                  for(int i=0;i<mySize2.Height;i++)
                        for(int j=0;j<mySize2.Width;j++)
                              arChanged[i,j] = false;
                  intError += (int)myReport.Code;
                  //Kiem tra neu gap loi
                  if(intError!=0||arReport.GetUpperBound(1)-arReport.GetLowerBound(1)!=arData.GetUpperBound(1)-arData.GetLowerBound(1))
                        return;
                  //Lay cac thong so cua cac bang
                  int iDataColX0 = arData.GetLowerBound(1), iDataColX1 = arData.GetUpperBound(1),
                        iDataRowY0 = arData.GetLowerBound(0), iDataRowY1 = arData.GetUpperBound(0);
                  int iReportColX0 = arReport.GetLowerBound(1), iReportColX1 = arReport.GetUpperBound(1),
                        iReportRowY0 = arReport.GetLowerBound(0), iReportRowY1 = arReport.GetUpperBound(0);
                  //Dien du lieu tu bang Report vao bang Temp
                  for(int i=iReportRowY0;i<=iReportRowY1;i++)
                        for(int j=iReportColX0;j<=iReportColX1;j++)
                              if(arReport[i,j]!=null)
                                    arTemp[i-iReportRowY0,j-iReportColX0] = arReport[i,j];
                  //Dinh dang lai entry
                  string strtemp = "";
                  for(int j=iReportColX0;j<=iReportColX1;j++)
                        if(libServices.ConvertObj2Str( arReport[iReportRowY0,j] ) == "1")
                    for(int i=iReportRowY0;i<=iReportRowY1;i++)
                                    if( libServices.ConvertObj2Str( arReport[i,iReportColX0] ) == ""||
                                          libServices.ConvertObj2Str( arReport[i,iReportColX0] ) == "2")
                                    {
                                          if(arReport[i,j] == null) arReport[i,j] = strtemp;
                                    }
                                    else
                                          if(libServices.ConvertObj2Str( arReport[i,j])!="")
                                                strtemp = arReport[i,j].ToString();

                  strtemp = "";
                  for(int j=iDataColX0;j<=iDataColX1;j++)
                        if(libServices.ConvertObj2Str( arData[iDataRowY0,j] ) == "1")
                              for(int i=iDataRowY0;i<=iDataRowY1;i++)
                                    if( libServices.ConvertObj2Str( arData[i,iDataColX0] ) == ""||
                                          libServices.ConvertObj2Str( arData[i,iDataColX0] ) == "2")
                                    {
                                          if(arData[i,j] == null) arData[i,j] = strtemp;
                                    }
                                    else
                                          if(libServices.ConvertObj2Str( arData[i,j] )!="")
                                                strtemp = arData[i,j].ToString();

                  //Tim du lieu tu bang data va dien vao report
                  string strHeader1,strHeader2;
                  for(int i1=iReportRowY0;i1<=iReportRowY1;i1++)
                        if( libServices.ConvertObj2Str( arReport[i1,iReportColX0] )==""
                              ||libServices.ConvertObj2Str( arReport[i1,iReportColX0] )=="2" )
                        {
                              //Lay entry cua bang report
                              strHeader1 = "";
                              for(int j1=iReportColX0;j1<=iReportColX1;j1++)
                              {
                                    if( libServices.ConvertObj2Str( arReport[iReportRowY0,j1] )== "1")
                                          strHeader1 += libServices.ConvertObj2Str( arReport[i1,j1] );
                              }
                              //Neu entry == null thi bo qua
                              if(strHeader1=="") continue;
                              //Do tiep bang data
                              for(int i2=iDataRowY0;i2<=iDataRowY1;i2++)
                                    if( libServices.ConvertObj2Str( arData[i1,iDataColX0] )==""
                                          ||libServices.ConvertObj2Str( arData[i1,iDataColX0] )=="2" )
                                    {
                                          //Lay entry cua bang data
                                          strHeader2 = "";
                                          for(int j2=iDataColX0;j2<=iDataColX1;j2++)
                                          {
                                                if( libServices.ConvertObj2Str( arData[iDataRowY0,j2] )== "1")
                                                      strHeader2 += libServices.ConvertObj2Str( arData[i2,j2] );
                                          }
                                          //Khop entry
                                          if(strHeader1==strHeader2)
                                          {
                                                for(int k=iReportColX0;k<=iReportColX1;k++)
                                                      if( libServices.ConvertObj2Str(arReport[iReportRowY0,k])==""||
                                                            libServices.ConvertObj2Str(arReport[iReportRowY0,k])=="2")
                                                            if(libServices.ConvertObj2Str( arTemp[i1-iReportRowY0,k-iReportColX0] )!=
                                                                  libServices.ConvertObj2Str( arData[i2,k-iReportColX0+iDataColX0] ))
                                                            {
                                                                  arTemp[i1-iReportRowY0,k-iReportColX0]=arData[i2,k-iReportColX0+iDataColX0];
                                                                  arChanged[i1-iReportRowY0,k-iReportColX0]=true;
                                                            }
                                                break;
                                          }
                                    }
                        }
                  //Do du lieu tu Temp vao Report
                  for(int i=0;i<mySize2.Height;i++)
                        if(libServices.ConvertObj2Str(arTemp[i,0])==""||libServices.ConvertObj2Str(arTemp[i,0])=="2")
                              for(int j=0;j<mySize2.Width;j++)
                                    if(libServices.ConvertObj2Str(arTemp[0,j])==""||libServices.ConvertObj2Str(arTemp[0,j])=="2")
                                          if(arChanged[i,j])
                                                myReport.SetCell(i+iReportRowY0,j+iReportColX0,arTemp[i,j]);
            }

            /// <summary>
            /// &#272;i&#7873;n s&#7889; li&#7879;u t&#7915; m&#7897;t &#273;&#7889;i Excel có s&#7861;n vào m&#7897;t &#273;&#7889;i t&#432;&#7907;ng Excel khác cùng m&#7851;u
            /// </summary>
            /// <param name="strDataFile">Tên &#273;&#7847;y &#273;&#7911; file ch&#7913;a d&#7919; li&#7879;u</param>
            /// <param name="strReportFile">Tên &#273;&#7847;y &#273;&#7911; file c&#7847;n &#273;i&#7873;n d&#7919; li&#7879;u</param>
            public static void BindDataToExcel(string strDataFile, string strReportFile)
            {
                  //Kiem tra loi file
                  if(!File.Exists(strDataFile))
                        return;
                  if(libServices.FileIsNoUsed(strDataFile))
                        return;
                  if(!File.Exists(strReportFile))
                        return;
                  if(libServices.FileIsNoUsed(strReportFile))
                        return;
                  //T&#7841;o &#273;&#7889;i t&#432;&#7907;ng Excel d&#7919; li&#7879;u
                  clsExcelObject myData = new clsExcelObject();
                  myData.FullFileName = strDataFile;
                  myData.Open();
                  //T&#7841;o &#273;&#7889;i t&#432;&#7907;ng Excel báo cáo
                  clsExcelObject myReport = new clsExcelObject();
                  myReport.FullFileName = strReportFile;
                  myReport.Open();
                  if(myData.Code!=0||myReport.Code!=0)
                        return;

                  //Dien du lieu
                  BindDataToExcel(myData,myReport);

                  //Dong
                  myReport.Save();
                  myReport.Close();
                  myData.Close();
            }

            /// <summary>
            /// &#272;i&#7873;n các thông tin thêm vào các m&#7851;u trong &#273;&#7889;i t&#432;&#417;ng Excel có s&#7861;n
            /// </summary>
            /// <param name="myExcel">&#272;&#7889;i t&#432;&#7907;ng Excel có s&#7861;n</param>
            /// <param name="strPartern">M&#7851;u</param>
            /// <param name="strInfo">Giá tr&#7883; s&#7869; &#273;&#432;&#7907;c th&#7871; vào m&#7851;u</param>
            public static void ReplaceInfo2Excel(clsExcelObject myExcel, string strPartern, string strInfo)
            {
                  string strTemp,strValue;
                  //Get size of Excel object
                  System.Drawing.Size mySize = GetSizeOfExcel(myExcel);
                  //Get data range of Exel Object
                  object [,] myArray = myExcel.GetRange(1,1,mySize.Height,mySize.Width);
                  //Check error
                  if(myExcel.Code==0&&myArray!=null)
                  {
                        //Get Bounds of Array
                        int intColX0 = myArray.GetLowerBound(1), intColX1 = myArray.GetUpperBound(1),
                              intRowY0 = myArray.GetLowerBound(0), intRowY1 = myArray.GetUpperBound(0);
                        for(int i=intRowY0;i<=intRowY1;i++)
                              for(int j=intColX0;j<=intColX1;j++)
                                    if(myArray[i,j]!=null)
                                    {
                                          strTemp = myArray[i,j].ToString();
                                          strValue = strTemp.Replace(strPartern,strInfo);
                                          if(strValue!=strTemp)
                                                myExcel.SetCell(i-intRowY0+1,j-intColX0+1,strValue);
                                    }
                  }
            }

            #endregion
      }
}

Good luck
VINHNL
0
 
YurichAuthor Commented:
thanks vinh, i'll check the links later specially they're mostly in VB not in C# and your code you copy-pasted here looks a bit confusing since i don't have vietnamse support and a good part (all comments and strings) of it look pretty much gibberish to me. Of course it's still doable but I'm sure there's a way to show how to do it in less than 1000 lines...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
vinhnlCommented:
Yes. I know it is diff for you see. So that you can see this url: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q316934
0
 
YurichAuthor Commented:
the last one is good but it'll take me some time to digest ;)
0
 
YurichAuthor Commented:
thanks, haven't completed the whole thing but the idea is clear.
regards
0
All Courses

From novice to tech pro — start learning today.