using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

#region 设置excel样式并保存到PC
        /// <summary>
        /// 设置excel表格格式
        /// </summary>
        /// <param name="ksCell">开始列索引</param>
        /// <param name="jsCell">结束列索引</param>
        /// <param name="ksRow">开始行索引</param>
        /// <param name="jsRow">结束行索引</param>
        /// <param name="xls">Excel.Application对象</param>
        /// <param name="filepath">文件保存完整路径</param>
        public static Excel.Application TableCss(int ksCell, int jsCell, int ksRow, int jsRow, Excel.Application xls)
        {
            //设置报表表格为最适应宽度
            //
            xls.Range[xls.Cells[ksRow, ksCell], xls.Cells[jsRow, jsCell]].Select();
            xls.Range[xls.Cells[ksRow, ksCell], xls.Cells[jsRow, jsCell]].Columns.AutoFit();
            //xls.Range[xls.Cells[ksRow, ksCell], xls.Cells[jsRow, jsCell]].Columns.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
            
            //
            //设置整个报表的标题为跨列居中
            //
            xls.Range[xls.Cells[ksRow, ksCell], xls.Cells[jsRow, jsCell]].Select();
            xls.Range[xls.Cells[ksRow, ksCell], xls.Cells[jsRow, jsCell]].HorizontalAlignment = XlHAlign.xlHAlignCenter;
            //
            //绘制边框
            //xSt.Range[xls.Cells[rowIndex, 1], xls.Cells[1, rowIndex]].Borders.LineStyle = 1;
            xls.Range[xls.Cells[jsRow, ksCell], xls.Cells[ksRow, ksCell]].Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
            xls.Range[xls.Cells[ksRow, ksCell], xls.Cells[ksRow, jsCell]].Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
            xls.Range[xls.Cells[ksRow, jsCell], xls.Cells[jsRow, jsCell]].Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
            xls.Range[xls.Cells[jsRow, ksCell], xls.Cells[jsRow, jsCell]].Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗

            xls.Visible = false;
            return xls;
        }
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int/// <summary>
        /// 保存excel文件并结束当前EXCEL进程
        /// </summary>
        /// <param name="xls"></param>
        /// <param name="filepath"></param>
        public static void SaveExcel(Excel.Application xls, string filepath)
        {
            xls.ActiveWorkbook.SaveAs(filepath, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            xls.Workbooks.Close();
            xls.Quit();
            IntPtr t = new IntPtr(xls.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
            int k = 0;
            GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
            p.Kill(); //关闭进程k 
            GC.Collect();
        }

#endregion

        #region 导出excel文件      /// <summary>
        /// 导出数据
        /// </summary>
        protected void btnPagedata_Click(object sender, EventArgs e)
        {
            try
            {
                string ddidlist = " 0,1,2,3,4 ";
                System.Data.DataTable ds = new BLL().GetList(" id in (" + ddidlist + ")").Tables[0];
                DoExport(ds, @"D:\" + DateTime.Now.ToString("yy-MM-dd") + "Excel.xls");
                ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script>alert('数据导出成功！');</script>");
            }
            catch
            {
                ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script>alert('数据导出失败！');</script>");
            }
        }
        /// <summary>
        /// 导出excel文件
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="FilePath"></param>
        private void DoExport(DataTable ds, string FilePath)
        {
            Excel.Application xls = new Excel.Application();
            int rowIndex = 1;
            //int ceelindex = 4;
            xls.Application.Workbooks.Add(true);
            if (ds.Rows.Count > 0)
            {
                xls.Cells[1] = "ID";
                xls.Cells[2] = "价格";
                xls.Cells[3] = "产品";
                xls.Cells[4] = "简称";
                xls.Cells[5] = "时间";
                xls.Cells.NumberFormat = "@";//防止存在超过11位的全数字的列在导入后会被用科学计数法显示
                foreach (DataRow row in ds.Rows)
                {
                    rowIndex++;
                    xls.Cells[rowIndex, 1] = row["id"].ToString();
                    xls.Cells[rowIndex, 2] = row["ji"].ToString() + "RMB";
                    xls.Cells[rowIndex, 3] = row["cm"].ToString();
                    xls.Cells[rowIndex, 4] = row["ss"].ToString();
                    xls.Cells[rowIndex, 5] = row["time"].ToString();
                }
                Share.TableCss(1, 5, 1, rowIndex, xls);//设置excel边框格式
                Share.SaveExcel(xls, FilePath);//保存excel文件，并且结束当前excel的进程
            }
        }
#endregion