using System;

using System.Data;

using System.IO;

using System.Runtime.InteropServices;

using System.Threading;

using System.Windows.Forms;

using Microsoft.Office.Core;

using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1

{

    public partial class Form1 : Form

    {

        private bool isStartPrint = false;

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            //string OriginalPath = Path.GetDirectoryName(Application.ExecutablePath) + "\\IMS.accdb";

            ////初始化数据库

            //DataBase.OleDbObject.InitDatabase(OriginalPath);

            //DataBase.OleDbObject.OpenDb();

            //int k=DataBase.OleDbObject.Select("select * from ElementInventory").Rows.Count;

            //初始化要写入Excel的数据

            DataTable dt = new DataTable();

            for (int i = ; i < ; i++)

            {

                DataColumn dc = new DataColumn();

                dc.ColumnName = "name" + i;

                dc.DataType = typeof(string);

                dt.Columns.Add(dc);

            }

            for (int i = ; i < ; i++)

            {

                DataRow dr = dt.NewRow();

                if (i %  == )

                {

                    dr[] = "测试用例" + i + "号，开始测试是否自动换行，以及页面高度自适应。";

                }

                else

                {

                    dr[] = "测试用例" + i + "号";

                }

                for (int j = ; j < ; j++)

                {

                    dr[j] = j;

                }

                dt.Rows.Add(dr);

            }

            if (!isStartPrint)

            {

                Thread th = new Thread(delegate ()

                {

                    DataTabletoExcel(dt, "测试.xlsx");

                });

                th.Start();

                isStartPrint = true;

            }

            else

            {

                MessageBox.Show("打印程序已启用，请稍后……", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            //DataTabletoExcel(dt, "测试.xlsx");

        }

        public void DataTabletoExcel(System.Data.DataTable dtTemp, string strFileName)

        {

            int rowNum = dtTemp.Rows.Count; //先得到dtTemp的行数

            int columnNum = dtTemp.Columns.Count; //列数

            Excel.Application ExcelApp = new Excel.Application(); //声明一个应用程序类实例

            //ExcelApp.DefaultFilePath = ""; //默认文件路径导出excel的路径还是在参数strFileName里设置

            //ExcelApp.DisplayAlerts = true;

            //ExcelApp.SheetsInNewWorkbook = 1;///返回或设置 Microsoft Excel 自动插入到新工作簿中的工作表数目。

            Excel.Workbook worksBook = ExcelApp.Workbooks.Add(); //创建一个新工作簿

            Excel.Worksheet workSheet = (Excel.Worksheet)worksBook.Worksheets[]; //在工作簿中得到sheet。

            if (workSheet == null)

            {

                System.Diagnostics.Debug.WriteLine("ERROR: worksheet == null");

                return;

            }

            workSheet.Name = "测试Sheet1"; //工作表的名称

            workSheet.Cells.WrapText = true; //设置所有列的文本自动换行

            workSheet.Cells.EntireRow.AutoFit(); //设置所有列自动调整行高

            #region 绘制列

            ///自定义方法，向sheet中绘制列

            RangeMark(workSheet, "A1", "A2", "合并竖列1");

            RangeMark(workSheet, "B1", "B2", "合并竖列2");

            RangeMark(workSheet, "C1", "C2", "合并竖列3");

            RangeMark(workSheet, "D1", "D2", "合并竖列4");

            RangeMark(workSheet, "E1", "E2", "合并竖列5");

            RangeMark(workSheet, "F1", "H1", "合并横行1");

            RangeMark(workSheet, "F2", "F2", "合并横行1.1");

            RangeMark(workSheet, "G2", "G2", "合并横行1.2");

            RangeMark(workSheet, "H2", "H2", "合并横行1.3");

            RangeMark(workSheet, "I1", "K1", "合并横行2");

            RangeMark(workSheet, "I2", "J2", "合并横行2.1");

            RangeMark(workSheet, "K2", "K2", "合并横行2.2");

            #endregion

            //将DataTable中的数据导入Excel中

            for (int i = ; i < rowNum; i++)

            {

                for (int j = ; j < columnNum; j++)

                {

                    workSheet.Cells[i + , j + ] = dtTemp.Rows[i][j].ToString(); //文本

                }

            }

            ///保存路径

            string filePath = @"C:\Users\Admin\Desktop\" + strFileName;

            if (File.Exists(filePath))

            {

                try

                {

                    File.Delete(filePath);

                }

                catch (Exception)

                {

                }

            }

            ExcelApp.Visible = true;

            //------------------------打印页面相关设置--------------------------------

            workSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;//纸张大小

            workSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//页面横向

                                                                                  //workSheet.PageSetup.Zoom = 75; //打印时页面设置,缩放比例百分之几

            workSheet.PageSetup.Zoom = false; //打印时页面设置,必须设置为false,页高,页宽才有效

            workSheet.PageSetup.FitToPagesWide = ; //设置页面缩放的页宽为1页宽

            workSheet.PageSetup.FitToPagesTall = false; //设置页面缩放的页高自动

            workSheet.PageSetup.LeftHeader = "Nigel";//页面左上边的标志

            workSheet.PageSetup.CenterFooter = "第 &P 页，共 &N 页";//页面下标

            workSheet.PageSetup.PrintGridlines = true; //打印单元格网线

            workSheet.PageSetup.TopMargin = 1.5 / 0.035; //上边距为2cm（转换为in）

            workSheet.PageSetup.BottomMargin = 1.5 / 0.035; //下边距为1.5cm

            workSheet.PageSetup.LeftMargin =  / 0.035; //左边距为2cm

            workSheet.PageSetup.RightMargin =  / 0.035; //右边距为2cm

            workSheet.PageSetup.CenterHorizontally = true; //文字水平居中

                                                           //------------------------打印页面设置结束--------------------------------

                                                           ///http://blog.csdn.net/wanmingtom/article/details/6125599

            worksBook.PrintPreview(); //打印预览

                                      //worksBook.PrintOutEx(); //直接打印

                                      //worksBook.Close(); //关闭工作空间

                                      //ExcelApp.Quit(); //退出程序

                                      //workSheet.SaveAs(filePath); //另存表

            KillProcess(ExcelApp); //杀掉生成的进程

            isStartPrint = false; //打印完毕

            GC.Collect(); //垃圾回收机制

        }

        /// <summary>

        /// 引用Windows句柄，获取程序PID

        /// </summary>

        /// <param name="Hwnd"></param>

        /// <param name="PID"></param>

        /// <returns></returns>

        [DllImport("User32.dll")]

        public static extern int GetWindowThreadProcessId(IntPtr Hwnd, out int PID);

        /// <summary>

        /// 杀掉生成的进程

        /// </summary>

        /// <param name="AppObject">进程程对象</param>

        private static void KillProcess(Excel.Application AppObject)

        {

            int Pid = ;

            IntPtr Hwnd = new IntPtr(AppObject.Hwnd);

            System.Diagnostics.Process p = null;

            try

            {

                GetWindowThreadProcessId(Hwnd, out Pid);

                p = System.Diagnostics.Process.GetProcessById(Pid);

                if (p != null)

                {

                    p.Kill();

                    p.Dispose();

                }

            }

            catch (Exception ex)

            {

                System.Diagnostics.Debug.WriteLine("进程关闭失败！异常信息：" + ex);

            }

        }

        /// <summary>

        /// 创建表头单元格，包括合并单元格

        /// </summary>

        /// <param name="workSheet">工作表</param>

        /// <param name="startCell">单元格起始格编号</param>

        /// <param name="endCell">单元格结束编号</param>

        /// <param name="strText">单元格名称</param>

        private static bool RangeMark(Excel.Worksheet workSheet, string startCell, string endCell, string strText)

        {

            //创建一个区域对象。第一个参数是开始格子号，第二个参数是终止格子号。比如选中A1——D3这个区域。

            Excel.Range range = (Excel.Range)workSheet.get_Range(startCell, endCell);

            if (range == null)

            {

                System.Diagnostics.Debug.WriteLine("ERROR: range == null");

                return false;

            }

            range.Merge(); //合并方法，0的时候直接合并为一个单元格

            range.Font.Size = ; //字体大小

            range.Font.Name = "黑体"; //字体

            range.WrapText = true; //文本自动换行

            range.EntireRow.AutoFit(); //自动调整行高

                                       //range.RowHeight = 20;

                                       //range.EntireColumn.AutoFit(); //自动调整列宽

            range.ColumnWidth = ;

            range.HorizontalAlignment = XlVAlign.xlVAlignCenter; //横向居中

            range.Value = strText; //合并单元格之后，设置其中的文本

            range.Interior.ColorIndex = ; //填充颜色

            range.Cells.Borders.LineStyle = ; //设置单元格边框的粗细

            return true;

        }

    }

}