Sam's Blog

 

在換公司之前一直收到人事部門的"請求"協助處理一支程式。

但是這個程式要產出一份報表。

所以我把順便把產出Excel範例給大家參考，包捨自動欄寬及分類

 

   private void Prient(System.Data.DataTable dt)
        {
            #region

            //Export Execl

            //沒值~
            if (dt.Rows.Count == 0)
                return;

            //Create Application
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

                   excel.Application.Workbooks.Add(true);

                 excel.Visible = true;
            //欄位表頭
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                excel.Cells[1, i + 1] = dt.Columns[i].Caption;
            }

                   if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++) {
                    for (int j = 0; j < dt.Columns.Count; j++) {

                        string str = dt.Rows[i][j].ToString();
                        excel.Cells[i + 2, j + 1] =  str;
                    }
                }
            }


            Microsoft.Office.Interop.Excel.Range oRng = excel.get_Range("A1", "A1");// From

            //自動篩選
            oRng.EntireColumn.AutoFit();

            Microsoft.Office.Interop.Excel.Range iRng = excel.get_Range("A1", "A1");// From
            //自動欄寬
            iRng.AutoFilter(1, Type.Missing, XlAutoFilterOperator.xlAnd, Type.Missing, true);


            excel.DisplayAlerts = false;
            excel.AlertBeforeOverwriting = false;


            #endregion
        }