[Jakarin Chawachat]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
  using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.Windows.Forms;
  using Excel = Microsoft.Office.Interop.Excel;
  using System.IO;
  using System.Runtime.InteropServices;
  using System.Reflection;
  namespace excel
  {
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            object missing = Type.Missing;
            Excel.Application oXL = null;
            Excel.Workbooks oWBs = null;
            Excel.Workbook oWB = null;
            Excel.Worksheet oSheet = null;
            Excel.Range oCells = null;
            Excel.Range oRng1 = null;
            Excel.Range oRng2 = null;
            try{
                // เริ่ม Excel Application
                oXL = new Excel.Application();
                oXL.Visible = false; // ถ้าเป็น true จะเปิดโปรแกรมมาให้เห็น
                // สร้าง Workbook ใหม่
                oWBs = oXL.Workbooks;
                oWB = oWBs.Add();
                // เลือก Worksheet ที่ active และตั้งชื่อ
                oSheet = oWB.ActiveSheet as Excel.Worksheet;
                oSheet.Name = "Report";
                // กรอกข้อมูลใน cell 
                oCells = oSheet.Cells;
                oCells[1, 1] = "First Name";
                oCells[1, 2] = "Last Name";
                oCells[1, 3] = "Full Name";
                // สร้าง array ของ user names
                string[,] saNames = new string[,] {
                    {"John", "Smith"}, 
                    {"Tom", "Brown"}, 
                    {"Sue", "Thomas"}, 
                    {"Jane", "Jones"}, 
                    {"Adam", "Johnson"}};
                // เอา saNames ไปใส่ในช่วง A2 ถึง B6
                oRng1 = oSheet.get_Range("A2", "B6");
                oRng1.Value2 = saNames;
                // กำหนดช่วงที่สนใจให้ oRng2
                oRng2 = oSheet.get_Range("C2", "C6");
                // กำหนดสูตรโดยเอาข้อมูลในช่อง A2 และ B2 มาไว้ที่ช่อง C2 ถึง C6
                oRng2.Formula = "=A2 & \" \" & B2";
                //เพิ่ม Worksheet ใหม่ และตั้งชื่อว่า Report 2
                oSheet = oWB.Worksheets.Add();
                oSheet.Name = "Report2";
                // ระบุ Path ที่จะ save
                string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\Sample1.xlsx";
                // save
                oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
                // ปิด Excel Application
                oWB.Close(missing, missing, missing);
                oXL.UserControl = true;
                oXL.Quit();
            } catch (Exception ex){                
                Console.WriteLine("Error: {0}", ex.Message);}
            finally{
                if (oRng2 != null)
                {
                    Marshal.FinalReleaseComObject(oRng2);
                    oRng2 = null;
                }
                if (oRng1 != null)
                {
                    Marshal.FinalReleaseComObject(oRng1);
                    oRng1 = null;
                }
                if (oCells != null)
                {
                    Marshal.FinalReleaseComObject(oCells);
                    oCells = null;
                }if (oSheet != null)
                {
                    Marshal.FinalReleaseComObject(oSheet);
                    oSheet = null;
                }
                if (oWB != null)
                {
                    Marshal.FinalReleaseComObject(oWB);
                    oWB = null;
                }
                if (oWBs != null)
                {
                    Marshal.FinalReleaseComObject(oWBs);
                    oWBs = null;
                }
                if (oXL != null)
                {
                    Marshal.FinalReleaseComObject(oXL);
                    oXL = null;
                }
            }
        }
    }
  }
[Jakarin Chawachat]

User Tools

Site Tools

Page Tools
