DataTable ---匯出Excel

標籤：

//mscorlib.dll, v4.0.0.0&&Microsoft.Office.Interop.Excel.dll, v14.0.0.0

/// <summary>
/// 匯出Excel
/// </summary>
/// <param name="dt_data">要匯出的資料表</param>

public void ExcelPort(DataTable dt_data)        {            try            {                                //表格控制項對象（方法見後面）                string fileName = ShowSaveFileDialog("Microsoft Excel Document", "Microsoft Excel|*.xls");                            if (string.IsNullOrEmpty(fileName)) return;                //建立Excel對象                System.Reflection.Missing miss = System.Reflection.Missing.Value;                //此處引用Microsoft.Office.Interop.Excel.dll, v14.0.0.0且必須與電腦安裝office達到一致（否則不能正常建立對象）                Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
                               xlapp.Visible = false; //是否顯示匯出過程 ，建議關閉，否則在匯出過程中滑鼠點擊Excel檔案時會出錯。                xlapp.UserControl = true;                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.Workbooks;                //載入模板（必須先製作好匯出模版，在此處引用）  worKbooks.Add(url)   url 為模版路徑                Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Application.StartupPath.ToString() + ("\\Model\\modelNm.xls"));                 Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                int row = 0, column = 0;                if (fileName == "") return;                try                {                    //選中第1個工作薄。並給活頁簿新增內容內容
                   //①表格頭設定
                    worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); 
                    // eg. 2015年9月30日 xxx                    worksheet.Cells[1, 1] = DateTime.Now.Year + "年" + DateTime.Now.Month + "月" + DateTime.Now.Day +"日"+"xxx ";                    worksheet.Name = "TableNm";
                    //*xxx*為將xxx轉換為code條碼匯出                    worksheet.Cells[2, 3] = "*" + xxx + "*";                    worksheet.Cells[3, 3] = xxx ;                    worksheet.Cells[4, 3] = xxx;                    worksheet.Cells[4, 6] = xxxx;                    row = dt_data.Rows.Count;                    column = dt_data.Columns.Count;
                   ②表格主體內容設定                    for (int i = 1; i <= row; i++)                    {                        int lint_row = i + 5;                        int dt_row = i - 1;                        worksheet.Cells[lint_row, 2] = dt_data.Rows[dt_row][columNm].ToString();                        worksheet.Cells[lint_row, 3] = dt_data.Rows[dt_row][column].ToString();                        worksheet.Cells[lint_row, 4] = dt_data.Rows[dt_row][column].ToString();                        worksheet.Cells[lint_row, 5] = dt_data.Rows[dt_row][column].ToString();                        worksheet.Cells[lint_row, 6] = dt_data.Rows[dt_row][column].ToString();                        worksheet.Cells[lint_row, 7] = dt_data.Rows[dt_row][column].ToString();                    }                     //儲存資料                    workbook.RefreshAll();                    //儲存資料                    workbook.SaveCopyAs(fileName);                    //退出excel                    workbook.Close(false, miss, miss);                    DevExpress.XtraEditors.XtraMessageBox.Show("表格式資料已成功匯出!" + "\r\n檔案名稱：" + fileName, "", MessageBoxButtons.OK, MessageBoxIcon.Information);                }                catch (Exception ex)                {                    MessageBox.Show(ex.Message + ":" + ex.ToString());                }                finally                {                    xlapp.Quit();                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);                    xlapp = null;                    GC.Collect();                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message + ":" + ex.ToString());            }        }

//儲存對話方塊   System.Windows.Forms.dll, v4.0.0.0

/// <summary>
/// 儲存檔案對話方塊（儲存則返迴文件名，取消返回為“”）
/// </summary>
/// <param name="title">儲存對話方塊表單Caption</param>
/// <param name="filter">檔案檔案類型</param>
/// <returns>檔案名稱</returns>

///http://www.cnblogs.com/zlytu/archive/2013/04/10/3013254.html(詳)

public static string ShowSaveFileDialog(string title, string filter)        {            SaveFileDialog dlg = new SaveFileDialog();            string name = "Name" + System.DateTime.Now.ToString("yyyyMMddHHmmss", System.Globalization.DateTimeFormatInfo.InvariantInfo);            int n = name.LastIndexOf(".") + 1;            if (n > 0) name = name.Substring(n, name.Length - n);            //"Export To "            dlg.Title = "檔案儲存";
            //檔案名稱            dlg.FileName = name;
            //檔案檔案類型（eg .Microsoft Excel|*.xls）            dlg.Filter = filter;            if (dlg.ShowDialog() == DialogResult.OK) return dlg.FileName;            return "";        }

 

Code39r

DataTable ---匯出Excel