Programs Knowledge

using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Reflection;
using System.Runtime.InteropServices;
using System.ComponentModel;

namespace ExportExcel1
{
    class Program
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        private static void FormatColumn(Range excelRange, int col, string format)
        { 
            ((Range)excelRange.Cells[1, col]).EntireColumn.NumberFormat = format; 
        }

        private static void ExportExcel(System.Data.DataTable dataTable, Worksheet sheet)
        {
            object[,] columnNames;
            columnNames = new object[1, dataTable.Columns.Count];

            //write column name
            for (int i = 0; i < dataTable.Columns.Count; i++)
                columnNames[0, i] = dataTable.Columns[i].Caption;

            Range columnsNamesRange = sheet.get_Range(sheet.Cells[1,1], sheet.Cells[1, dataTable.Columns.Count]);
                columnsNamesRange.Value2 = columnNames;

            //set title Font as Bold
            columnsNamesRange.EntireRow.Font.Bold = true;

            //Release resource
            System.Runtime.InteropServices.Marshal.ReleaseComObject(columnsNamesRange);
            columnsNamesRange = null;

            object[,] rowData = new object[dataTable.Rows.Count, dataTable.Columns.Count];

            //insert data into rowData
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                for (int j = 0; j < dataTable.Columns.Count; j++)
                rowData[i, j] = dataTable.Rows[i][j];
            }

            //set 寫入範圍從[2,1]~dataTable.Rows.Count + 1, dataTable.Columns.Count]
            Range dataCells = sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);

            //set columns type
            short colIndex = 1;
            string colType = string.Empty;
            foreach (DataColumn dcol in dataTable.Columns)
            {
                colType = string.Empty;

                if (dcol.DataType.Equals(typeof(string)))
                    colType = "G/通用格式"; //純文字 = "@"
                else if (dcol.DataType.Equals(typeof(DateTime)))
                    colType = "yyyy/mm/dd hh:mm:ss";

                if (!string.IsNullOrEmpty(colType))
                    FormatColumn(dataCells, colIndex, colType);

                ++colIndex;
            }

            //assign data to worksheet
            dataCells.Value2 = rowData;

            //Release resource
            System.Runtime.InteropServices.Marshal.ReleaseComObject(dataCells);
            dataCells = null;
        }

        private void ExportSetting(object[][] data, string FilePath)
        {
            Application excelApp1 = null;
            _Workbook excelBook1 = null;
            Worksheet excelSheet1 = null;

            excelApp1 = new Application();
            excelBook1 = excelApp1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            excelSheet1 = (Worksheet)excelBook1.Worksheets[1];

            //set save as .xlsx(columns > 10000)
            excelApp1.DefaultSaveFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook;

            //To suppress the save as alert
            excelApp1.DisplayAlerts = false;
            excelApp1.AlertBeforeOverwriting = false;

            //因Excel中也有DataTable, 所以必須指定為System的
            System.Data.DataTable dt = new System.Data.DataTable("Sheet Name");

            //declare column
            DataColumn column;

            int colNum = data[0].Length;
            for (int i = 1; i <= colNum; i++)
            {
                column = new DataColumn();

                //System.Double, System.DateTime, System.Int16, System.Int32, System.Int64
                column.DataType = System.Type.GetType("System.String");

                //set column name
                column.ColumnName = "Column" + i.ToString();

                dt.Columns.Add(column);
            }

            //Fill data into table
            int rowNum = data.Length;
            for (int i = 0; i < rowNum; i++)
            {
                System.Data.DataRow dr = dt.NewRow();

                for (int j = 1; j <= colNum; j++)
                {
                    if (data[i][j - 1] != null && data[i][j - 1] != string.Empty)
                        dr["Column" + j.ToString()] = data[i][j - 1].ToString();
                }

                dt.Rows.Add(dr);
            }

            //Call Export function
            ExportExcel(dt, excelSheet1);

            //Set Columns Width
            excelSheet1.Cells.EntireColumn.AutoFit();

            //Save file
            excelSheet1.SaveAs(FilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            //Release resource
            IntPtr t = new IntPtr(excelApp1.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();
        }

        static void Main(string[] args)
        {
            Program p = new Program();

            object[][] data = new object[2][] { new object[] { "a", "b", "c" }, new object[] { 1, 2, 3 } };
            p.ExportSetting(data, @"D:\test.xls");
        }
    }
}

using System.Collections.Generic;
using System.Runtime.InteropServices;

namespace ExportExcel3
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string[][] data = new string[][] { new string[] { "a1", "b1" }, new string[] { "a2","b2" } };

                //建立DataTable並設定excel的sheet名稱
                string sheetName = "name1";
                System.Data.DataTable dt = new System.Data.DataTable(sheetName);

                int colNum = data[0].Length;

                //設定column name
                for (int i = 1; i <= colNum; i++)
                    dt.Columns.Add("Col" + i.ToString());

                int rowNum = data.Length;
                for (int i = 0; i < rowNum; i++)
                {
                    System.Data.DataRow dr = dt.NewRow();

                    for (int j = 1; j <= colNum; j++)
                        dr["Col" + j.ToString()] = data[i][j - 1].ToString();

                    dt.Rows.Add(dr);
                }

                //create excel file
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=@E:\test.xls;Extended Properties=Excel 8.0;";
                System.Data.OleDb.OleDbConnection Conn = newSystem.Data.OleDb.OleDbConnection(strConn);
                Conn.Open();

                //use SQL string to create excel
                string str = "Create table " + sheetName + "(";
                for (int i = 1; i <= colNum; i++)
                {
                    str += "Col" + i.ToString() + " VARCHAR";

                    if (i != colNum)
                        str += ", ";
                    else
                        str += ") ";
                }

                System.Data.OleDb.OleDbCommand odbcmd = new System.Data.OleDb.OleDbCommand(str, Conn);
                try 
                { 
                    odbcmd.ExecuteNonQuery();
                 }
                catch (Exception ex) 
                {
                    /*Exception Handling*/ 
                }
                finally 
                { 
                    Conn.Close();
                 }

                //insert data into excel
                string strInsert = "Insert into " + sheetName + "(";
                for (int i = 1; i <= colNum; i++)
                {
                    strInsert += "Col" + i.ToString();

                    if (i != colNum)
                        strInsert += ", ";
                    else
                        strInsert += ") VALUES( ";
                }

                for (int i = 1; i <= colNum; i++)
                {
                    strInsert += "@Col" + i.ToString();

                    if (i != colNum)
                        strInsert += ", ";
                    else
                        strInsert += ") ";
                }

                System.Data.OleDb.OleDbCommand olecmd = new System.Data.OleDb.OleDbCommand(strInsert, Conn);

                for (int i = 1; i <= colNum; i++)
                {
                    olecmd.Parameters.Add("@Col" + i.ToString(), System.Data.OleDb.OleDbType.VarChar);
                    olecmd.Parameters["@Col" + i.ToString()].SourceColumn = "Col" + i.ToString();
                }

                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
                da.InsertCommand = olecmd;
                da.Update(dt);
            }
            catch (Exception e) 
            {
                /*Exception Handling*/ 
            }
        }
    }
}

//指定已存在的excel file
Microsoft.Office.Interop.Excel._Workbook excelBook = excelApp.Workbooks.Add(@"D:\test.xls");

//指定foucs在第幾個sheet
Microsoft.Office.Interop.Excel.Worksheet excelSheet =
    (Microsoft.Office.Interop.Excel.Worksheet)(excelBook.Worksheets[1]);

excelSheet.get_Range(excelSheet.Cells[1, 1], 
    excelSheet.Cells[10, 10]).Value2 = data; //data 需為 object[,] 型態

using Microsoft.Office.Interop.Excel;

namespace ExportExcel2
{
    class Program
    {
        static void Main(string[] args)
        {
            Application excelApp = new Application();

            //指定已存在的excel file
            _Workbook excelBook = excelApp.Workbooks.Add(@"D:\test.xls");

            //指定foucs在第幾個sheet
            Worksheet excelSheet = (Worksheet)(excelBook.Worksheets[1]);

            //寫入字串, excel座標是從1開始
            excelApp.Cells[1, 1] = "a";

            //save file
            excelBook.SaveCopyAs(@"D:\test.xls");

            //不顯示儲存提示
            excelBook.Saved = true;
            excelApp.Quit();

            //release resource
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
        }
    }
}

{
    Application xlApp;
    Workbook xlWorkBook;
    Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;
    Range chartRange;

    xlApp = new ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

    //add data 
    xlWorkSheet.Cells[4, 2] = "";
    xlWorkSheet.Cells[4, 3] = "Student1";
    xlWorkSheet.Cells[4, 4] = "Student2";
    xlWorkSheet.Cells[4, 5] = "Student3";

    xlWorkSheet.Cells[5, 2] = "Term1";
    xlWorkSheet.Cells[5, 3] = "80";
    xlWorkSheet.Cells[5, 4] = "65";
    xlWorkSheet.Cells[5, 5] = "45";

    xlWorkSheet.Cells[6, 2] = "Term2";
    xlWorkSheet.Cells[6, 3] = "78";
    xlWorkSheet.Cells[6, 4] = "72";
    xlWorkSheet.Cells[6, 5] = "60";

    xlWorkSheet.Cells[7, 2] = "Term3";
    xlWorkSheet.Cells[7, 3] = "82";
    xlWorkSheet.Cells[7, 4] = "80";
    xlWorkSheet.Cells[7, 5] = "65";

    xlWorkSheet.Cells[8, 2] = "Term4";
    xlWorkSheet.Cells[8, 3] = "75";
    xlWorkSheet.Cells[8, 4] = "82";
    xlWorkSheet.Cells[8, 5] = "68";

    xlWorkSheet.Cells[9, 2] = "Total";
    xlWorkSheet.Cells[9, 3] = "315";
    xlWorkSheet.Cells[9, 4] = "299";
    xlWorkSheet.Cells[9, 5] = "238";

    xlWorkSheet.get_Range("b2", "e3").Merge(false);

    chartRange = xlWorkSheet.get_Range("b2", "e3");
    chartRange.FormulaR1C1 = "MARK LIST";
    chartRange.HorizontalAlignment = 3;
    chartRange.VerticalAlignment = 3;

    chartRange = xlWorkSheet.get_Range("b4", "e4");
    chartRange.Font.Bold = true;
    chartRange = xlWorkSheet.get_Range("b9", "e9");
    chartRange.Font.Bold = true;

    chartRange = xlWorkSheet.get_Range("b2", "e9");
    chartRange.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, XlColorIndex.xlColorIndexAutomatic);

    xlWorkBook.SaveAs("D:\\csharp.net-informations.xls", XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlApp);
    releaseObject(xlWorkBook);
    releaseObject(xlWorkSheet);

    
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
    }
    finally
    {
        GC.Collect();
    }
}