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";
                    //
                    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 "";
        }

 

Code39    一维码