[C#] 使用Microsoft.Interop.Excel將Excel文件列印在同一頁(PageSetup)

說明：
將欄位較多的Excel文件自動列印在同一頁(將所有欄放入單一頁面)。


作法：
using Excel = Microsoft.Office.Interop.Excel;

Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(excelPath);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;

//Fit all columns on one page
excel.PrintCommunication = false;
Excel.PageSetup pageSetup = worksheet.PageSetup;
//FitToPagesTall property設為false，則以FitToPagesWide來縮放Worksheet
pageSetup.FitToPagesTall = false;
pageSetup.FitToPagesWide = 1;
excel.PrintCommunication = true;

workbook.Save();
worksheet.PrintOutEx();
workbook.Close();
excel.Quit();


參考來源：
https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pagesetup.fittopagestall(v=office.15).aspx
http://stackoverflow.com/questions/25741049/how-to-set-fit-all-columns-on-one-page-in-print-tab

[C#/Excel PIA] Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 8000401a The server process could not be started because the configured identity is incorrect. Check the username and password.

說明：
使用Excel PIA寫schedule job產出Excel，程式放在64 bits server上，執行出現該錯誤。


解法：
出現此錯誤，主要是COM元件的存取帳戶被設為Interactive，
因此程式執行時，需要有帳戶登入該server。看過網路上的解法，
打開Administrative Tools下的component service -> Computers -> DCOM Config，
找到Microsoft Excel Application或{00024500-0000-0000-C000-000000000046}，
右鍵Properties，找到Security tab或identity tab，將帳戶加進去，
但我找不到Excel Application，試過在command line下mmc comexp.msc /32等方法，還是沒顯示，最後是在登錄檔，HKEY_CLASSES_ROOT\AppID\{00020812-0000-0000-C000-000000000046}，將某個屬性值的Interactive改掉即可。


參考資料：
https://ovaismehboob.wordpress.com/2013/07/06/production-deployment-issues-when-asp-net-web-application-access-com-component/
http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2012/11/12/microsoft-excel-does-not-appear-in-dcom-configuration-snap-in.aspx
https://msdn.microsoft.com/en-us/library/ms678426(VS.85).aspx
http://www.cnblogs.com/fengjunkuan/

[.NET] 使用Microsoft.Office.Interop.Excel元件將資料匯出Excel

說明：有許多元件可將資料匯成Excel，這裡使用Excel 2010 PIA來實作，加入參考 -> 選.NET tab -> Microsoft.Office.Interop.Excel 12.0.0.0，namespace為Microsoft.Office.Interop.Excel。


做法：
//避免與Windows.Form.Application衝突
using Excel =  Microsoft.Office.Interop.Excel;

Excel.Application _Excel = null; //起一個Excel.exe
Excel.Workbook _Workbook = null; //活頁簿
Excel.Worksheet _Worksheet1 = null;
Excel.Sheets _Sheets = null;

string strTemplateFilePath = Application.StartupPath + @"\EXCEL\Template.xlsx"; //範本路徑
string strRptPath = Application.StartupPath + @"\EXCEL\Rpt\";
string strFilePath = string.Empty;
string strFileName = string.Empty;
           
try
{
    _Excel = new Excel.Application();
    _Excel.Visible = false;
    _Workbook = _Excel.Workbooks.Open(strTemplateFilePath);
    _Sheets = _Workbook.Worksheets;
    string[,] strTable;
 
    if (有資料)
    {
        _Worksheet1 = _Sheets.get_Item(1);
        _Worksheet1.Name = DateTime.Now.ToString("yyyyMM");

        int iInitialRow = 0; //起始列
        int iInitialCol = 0; //起始欄
        int iRowCnt = 資料來源筆數;
        int iColCnt = 資料來源欄位數;

        for (int x = 0; x < iRowCnt; x++)
        {
            for (int y = 0; y < iColCnt; y++)
            {
                strTable[x, y] = dt.Rows[x][y].ToString();
            }
        }

         Excel.Range _RangeStart = _Worksheet1.Cells[iInitialRow, iInitialCol];
         Excel.Range _RangeEnd = _Worksheet1.Cells[iInitialRow+iRowCnt, iInitialCol+iColCnt];
         Excel.Range _Range = (Excel.Range)_Worksheet1.get_Range(_RangeStart, _RangeEnd);
       
         _Range.Value2 = strTable;
         _Range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
         _Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
         _Range.Font.Name = "Times New Roman";
         _Range.Font.Size = 10;
         _Range.EntireColumn.AutoFit();
         _Range.Borders.Weight = Excel.XlBorderWeight.xlHairline;

         _Workbook.Application.DisplayAlerts = false;
         _Workbook.Application.AlertBeforeOverwriting = false;
         _Workbook.Saved = true;

         strFileName = "Rpt.xlsx";
         strFilePath = strRptPath + strFileName;
         _Workbook.SaveCopyAs(strFilePath);
    }
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    if (_Range != null)
   {
        Marshal.FinalReleaseComObject(_Range );
    }
    if (_Sheets != null)
    {
        Marshal.FinalReleaseComObject(_Sheets);
    }
    if (_Workbook != null)
    {
        _Workbook.Close(false);
        Marshal.FinalReleaseComObject(_Workbook);
     }
     if (_Excel != null)
     {
         _Excel.Workbooks.Close();
         _Excel.Quit();
         Marshal.FinalReleaseComObject(_Excel);
     }
}

 _Range.Cells.Text; //取得範本上資料
 //合併儲存格
 Excel.Range _RangeStart = _Worksheet1.Cells[int, int];
 Excel.Range _RangeEnd = _Worksheet1.Cells[int, int];
 _Worksheet1.get_Range(_RangeStart, _RangeEnd).Merge(0);
//新增工作表
_Worksheet1 = (Excel.Worksheet)_Workbook.Worksheets.Add(After: (Excel.Worksheet)_Workbook.Sheets[test], Count: 1);
_Worksheet1.Name = test+"_"+k;


參考資料：
http://www.dotblogs.com.tw/yc421206/archive/2012/03/09/70624.aspx
http://www.dotblogs.com.tw/yc421206/archive/2008/12/20/6470.aspx //fill in data
http://blog.darkthread.net/post-2013-05-14-excel-interop-notes.aspx //release
http://www.dotblogs.com.tw/chou/archive/2013/03/26/99016.aspx //Excel.Range.Cells.Text
http://fecbob.pixnet.net/blog/post/38189181-c%23-excel-%E8%A1%8C%E9%AB%98%E3%80%81%E5%88%97%E5%AF%AC%E3%80%81%E5%90%88%E4%BD%B5%E5%84%B2%E5%AD%98%E6%A0%BC%E3%80%81%E5%84%B2%E5%AD%98%E6%A0%BC%E9%82%8A%E6%A1%86 //Excel語法彙集
http://ww0o0ww.pixnet.net/blog/post/64084894-%E3%80%90c%23%E3%80%91%E3%80%8Amicrosoft.office.interop.excel%E3%80%8B%E5%AF%AB%E5%85%A5excel%E6%AA%94