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";
      ////初始化數(shù)據(jù)庫(kù)
      //DataBase.OleDbObject.InitDatabase(OriginalPath);
      //DataBase.OleDbObject.OpenDb();
      //int k=DataBase.OleDbObject.Select("select * from ElementInventory").Rows.Count;

      //初始化要寫入Excel的數(shù)據(jù)
      DataTable dt = new DataTable();
      for (int i = 0; i < 11; i++)
      {
        DataColumn dc = new DataColumn();
        dc.ColumnName = "name" + i;
        dc.DataType = typeof(string);
        dt.Columns.Add(dc);
      }
      for (int i = 0; i < 30; i++)
      {
        DataRow dr = dt.NewRow();
        if (i % 2 == 0)
        {
          dr[0] = "測(cè)試用例" + i + "號(hào)，開(kāi)始測(cè)試是否自動(dòng)換行，以及頁(yè)面高度自適應(yīng)。";
        }
        else
        {
          dr[0] = "測(cè)試用例" + i + "號(hào)";
        }
        for (int j = 1; j < 11; j++)
        {
          dr[j] = j;
        }
        dt.Rows.Add(dr);
      }
      if (!isStartPrint)
      {
        Thread th = new Thread(delegate ()
        {
          DataTabletoExcel(dt, "測(cè)試.xlsx");
        });
        th.Start();
        isStartPrint = true;
      }
      else
      {
        MessageBox.Show("打印程序已啟用，請(qǐng)稍后……", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
      }
      //DataTabletoExcel(dt, "測(cè)試.xlsx");
    }

    public void DataTabletoExcel(System.Data.DataTable dtTemp, string strFileName)
    {
      int rowNum = dtTemp.Rows.Count; //先得到dtTemp的行數(shù)
      int columnNum = dtTemp.Columns.Count; //列數(shù)
      Excel.Application ExcelApp = new Excel.Application(); //聲明一個(gè)應(yīng)用程序類實(shí)例

      //ExcelApp.DefaultFilePath = ""; //默認(rèn)文件路徑導(dǎo)出excel的路徑還是在參數(shù)strFileName里設(shè)置
      //ExcelApp.DisplayAlerts = true;
      //ExcelApp.SheetsInNewWorkbook = 1;///返回或設(shè)置 Microsoft Excel 自動(dòng)插入到新工作簿中的工作表數(shù)目。
      Excel.Workbook worksBook = ExcelApp.Workbooks.Add(); //創(chuàng)建一個(gè)新工作簿
      Excel.Worksheet workSheet = (Excel.Worksheet)worksBook.Worksheets[1]; //在工作簿中得到sheet。
      if (workSheet == null)
      {
        System.Diagnostics.Debug.WriteLine("ERROR: worksheet == null");
        return;
      }
      workSheet.Name = "測(cè)試Sheet1"; //工作表的名稱
      workSheet.Cells.WrapText = true; //設(shè)置所有列的文本自動(dòng)換行
      workSheet.Cells.EntireRow.AutoFit(); //設(shè)置所有列自動(dòng)調(diào)整行高
      #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中的數(shù)據(jù)導(dǎo)入Excel中
      for (int i = 0; i < rowNum; i++)
      {
        for (int j = 0; j < columnNum; j++)
        {
          workSheet.Cells[i + 3, j + 1] = 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;
      //------------------------打印頁(yè)面相關(guān)設(shè)置--------------------------------
      workSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;//紙張大小
      workSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//頁(yè)面橫向
                                         //workSheet.PageSetup.Zoom = 75; //打印時(shí)頁(yè)面設(shè)置,縮放比例百分之幾
      workSheet.PageSetup.Zoom = false; //打印時(shí)頁(yè)面設(shè)置,必須設(shè)置為false,頁(yè)高,頁(yè)寬才有效
      workSheet.PageSetup.FitToPagesWide = 1; //設(shè)置頁(yè)面縮放的頁(yè)寬為1頁(yè)寬
      workSheet.PageSetup.FitToPagesTall = false; //設(shè)置頁(yè)面縮放的頁(yè)高自動(dòng)
      workSheet.PageSetup.LeftHeader = "Nigel";//頁(yè)面左上邊的標(biāo)志
      workSheet.PageSetup.CenterFooter = "第 &P 頁(yè)，共 &N 頁(yè)";//頁(yè)面下標(biāo)
      workSheet.PageSetup.PrintGridlines = true; //打印單元格網(wǎng)線
      workSheet.PageSetup.TopMargin = 1.5 / 0.035; //上邊距為2cm（轉(zhuǎn)換為in）
      workSheet.PageSetup.BottomMargin = 1.5 / 0.035; //下邊距為1.5cm
      workSheet.PageSetup.LeftMargin = 2 / 0.035; //左邊距為2cm
      workSheet.PageSetup.RightMargin = 2 / 0.035; //右邊距為2cm
      workSheet.PageSetup.CenterHorizontally = true; //文字水平居中
                              //------------------------打印頁(yè)面設(shè)置結(jié)束--------------------------------
                              ///http://blog.csdn.net/wanmingtom/article/details/6125599
      worksBook.PrintPreview(); //打印預(yù)覽
                   //worksBook.PrintOutEx(); //直接打印
                   //worksBook.Close(); //關(guān)閉工作空間
                   //ExcelApp.Quit(); //退出程序
                   //workSheet.SaveAs(filePath); //另存表
      KillProcess(ExcelApp); //殺掉生成的進(jìn)程
      isStartPrint = false; //打印完畢
      GC.Collect(); //垃圾回收機(jī)制
    }
    /// <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>
    /// 殺掉生成的進(jìn)程
    /// </summary>
    /// <param name="AppObject">進(jìn)程程對(duì)象</param>
    private static void KillProcess(Excel.Application AppObject)
    {
      int Pid = 0;
      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("進(jìn)程關(guān)閉失敗！異常信息：" + ex);
      }
    }

    /// <summary>
    /// 創(chuàng)建表頭單元格，包括合并單元格
    /// </summary>
    /// <param name="workSheet">工作表</param>
    /// <param name="startCell">單元格起始格編號(hào)</param>
    /// <param name="endCell">單元格結(jié)束編號(hào)</param>
    /// <param name="strText">單元格名稱</param>
    private static bool RangeMark(Excel.Worksheet workSheet, string startCell, string endCell, string strText)
    {
      //創(chuàng)建一個(gè)區(qū)域?qū)ο?。第一個(gè)參數(shù)是開(kāi)始格子號(hào)，第二個(gè)參數(shù)是終止格子號(hào)。比如選中A1——D3這個(gè)區(qū)域。
      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); //合并方法，0的時(shí)候直接合并為一個(gè)單元格
      range.Font.Size = 16; //字體大小
      range.Font.Name = "黑體"; //字體
      range.WrapText = true; //文本自動(dòng)換行
      range.EntireRow.AutoFit(); //自動(dòng)調(diào)整行高
                    //range.RowHeight = 20; 
                    //range.EntireColumn.AutoFit(); //自動(dòng)調(diào)整列寬
      range.ColumnWidth = 15;
      range.HorizontalAlignment = XlVAlign.xlVAlignCenter; //橫向居中
      range.Value = strText; //合并單元格之后，設(shè)置其中的文本
      range.Interior.ColorIndex = 20; //填充顏色
      range.Cells.Borders.LineStyle = 1; //設(shè)置單元格邊框的粗細(xì)

      return true;
    }
  }
}