/// <summary>
/// 導出到Excel類，項目需引用Microsodt.Office.Interop.Excel,
/// 類文件需using System.Data與System.Windows.Forms命名空間
/// </summary>
public class CToExcel
{
  /// <summary>
  /// 導出到Excel
  /// </summary>
  /// <param name="fileName">默認文件名</param>
  /// <param name="listView">數(shù)據(jù)源，一個頁面上的ListView控件</param>
  /// <param name="titleRowCount">標題占據(jù)的行數(shù)，為0表示無標題</param>
  public void ExportExcel(string fileName, System.Windows.Forms.ListView listView,int titleRowCount)
  {
   string saveFileName = "";
   //bool fileSaved = false;
   SaveFileDialog saveDialog = new SaveFileDialog();
   saveDialog.DefaultExt = "xls";
   saveDialog.Filter = "Excel文件|*.xls";
   saveDialog.FileName = fileName;
   saveDialog.ShowDialog();
   saveFileName = saveDialog.FileName;
   if (saveFileName.IndexOf(":") < 0) return; //被點了取消 
   Microsoft.Office.Interop.Excel.Application xlApp;
   try
   {
    xlApp = new Microsoft.Office.Interop.Excel.Application();
   }
   catch (Exception)
   {
    MessageBox.Show("無法創(chuàng)建Excel對象，可能您的機子未安裝Excel");
    return;
   }
   finally
   {
   }
   Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
   Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
   Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
   //寫Title
   if(titleRowCount!=0)
    MergeCells(worksheet, 1, 1, titleRowCount, listView.Columns.Count, listView.Tag.ToString());
   //寫入列標題
   for (int i = 0; i <= listView.Columns.Count - 1; i++)
   {
    worksheet.Cells[titleRowCount+1, i + 1] = listView.Columns[i].Text;
   }
   //寫入數(shù)值
   for (int r = 0; r <= listView.Items.Count - 1; r++)
   {
    for (int i = 0; i <= listView.Columns.Count - 1; i++)
    {
     worksheet.Cells[r + titleRowCount+2, i + 1] = listView.Items[r].SubItems[i].Text;
    }
    System.Windows.Forms.Application.DoEvents();
   }
   worksheet.Columns.EntireColumn.AutoFit();//列寬自適應
   //if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
   //{
   // Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
   // rg.NumberFormat = "00000000";
   //}
   if (saveFileName != "")
   {
    try
    {
     workbook.Saved = true;
     workbook.SaveCopyAs(saveFileName);
     //fileSaved = true;
    }
    catch (Exception ex)
    {
     //fileSaved = false;
     MessageBox.Show("導出文件時出錯,文件可能正被打開！n" + ex.Message);
    }
   }
   //else
   //{
   // fileSaved = false;
   //}
   xlApp.Quit();
   GC.Collect();//強行銷毀 
   // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打開EXCEL
   MessageBox.Show(fileName + "導出到Excel成功", "提示", MessageBoxButtons.OK);
  }
  /// <summary>
  /// DataTable導出到Excel
  /// </summary>
  /// <param name="fileName">默認的文件名</param>
  /// <param name="dataTable">數(shù)據(jù)源,一個DataTable數(shù)據(jù)表</param>
  /// <param name="titleRowCount">標題占據(jù)的行數(shù)，為0則表示無標題</param>
  public void ExportExcel(string fileName,System.Data.DataTable dataTable,int titleRowCount)
  {
   string saveFileName = "";
   //bool fileSaved = false;
   SaveFileDialog saveDialog = new SaveFileDialog();
   saveDialog.DefaultExt = "xls";
   saveDialog.Filter = "Excel文件|*.xls";
   saveDialog.FileName = fileName;
   saveDialog.ShowDialog();
   saveFileName = saveDialog.FileName;
   if (saveFileName.IndexOf(":") < 0) return; //被點了取消 
   Microsoft.Office.Interop.Excel.Application xlApp;
   try
   {
    xlApp = new Microsoft.Office.Interop.Excel.Application();
   }
   catch (Exception)
   {
    MessageBox.Show("無法創(chuàng)建Excel對象，可能您的機子未安裝Excel");
    return;
   }
   finally
   {
   }
   Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
   Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
   Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
   //寫Title
   if(titleRowCount!=0)
    MergeCells(worksheet, 1, 1, titleRowCount, dataTable.Columns.Count, dataTable.TableName);
   //寫入列標題
   for (int i = 0; i <= dataTable.Columns.Count - 1; i++)
   {
    worksheet.Cells[titleRowCount+1, i + 1] = dataTable.Columns[i].ColumnName;
   }
   //寫入數(shù)值
   for (int r = 0; r <= dataTable.Rows.Count - 1; r++)
   {
    for (int i = 0; i <= dataTable.Columns.Count - 1; i++)
    {
     worksheet.Cells[r +titleRowCount+ 2, i + 1] = dataTable.Rows[r][i].ToString();
    }
    System.Windows.Forms.Application.DoEvents();
   }
   worksheet.Columns.EntireColumn.AutoFit();//列寬自適應
   //if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
   //{
   // Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
   // rg.NumberFormat = "00000000";
   //}
   if (saveFileName != "")
   {
    try
    {
     workbook.Saved = true;
     workbook.SaveCopyAs(saveFileName);
     //fileSaved = true;
    }
    catch (Exception ex)
    {
     //fileSaved = false;
     MessageBox.Show("導出文件時出錯,文件可能正被打開！n" + ex.Message);
    }
   }
   //else
   //{
   // fileSaved = false;
   //}
   xlApp.Quit();
   GC.Collect();//強行銷毀 
   // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打開EXCEL
   MessageBox.Show(fileName + "導出到Excel成功", "提示", MessageBoxButtons.OK);
  }
  /// <summary> 
  /// 合并單元格，并賦值，對指定WorkSheet操作 
  /// </summary> 
  /// <param name="sheetIndex">WorkSheet索引</param> 
  /// <param name="beginRowIndex">開始行索引</param> 
  /// <param name="beginColumnIndex">開始列索引</param> 
  /// <param name="endRowIndex">結(jié)束行索引</param> 
  /// <param name="endColumnIndex">結(jié)束列索引</param> 
  /// <param name="text">合并后Range的值</param> 
  public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
  {
   Microsoft.Office.Interop.Excel.Range range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
   range.ClearContents(); //先把Range內(nèi)容清除，合并才不會出錯 
   range.MergeCells = true;
   range.Value2 = text;
   range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
   range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
  } 
}