用程式去調整EXCEL檔的格式,例如自動對齊,欄位大小,字型顏色等.這個功能平時寫程式很少用到,但有時要用,還真的不是很好找,所以把它給整理一下放上來,說不定那天用到時,就不用再去東翻西找,或許也有別人需要這個功能.

　　廢話不多提,直接進入主題,要使用這個功能時,要先去加入一個COM元件的參考,Microsoft.Excel 11.0 Object.Library,將它加入參考後,就可以開始進行Coding的動作了,為了做基本功能的展示,所以做了一個簡易的UI.

加上一個OpenFileDialog即可,在Filter設定*.xls.其它就都是Code的部份.

接下來,就把調整格式的動作放入button_Click的事件內去做了.

        private void button1_Click(object sender, EventArgs e) 
        { 
            if (openFileDialog1.ShowDialog() == DialogResult.OK) 
            { 
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); 
                Microsoft.Office.Interop.Excel.Worksheet excelWs; 
                Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(openFileDialog1.FileName, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing); 
 
                excelWs = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Worksheets.get_Item(1);//取得第一個sheet 
 
                #region 設定小大位置 
                excelWs.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop; //垂直調準 
                excelWs.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;//水平調準 
                excelWs.Cells.EntireRow.AutoFit(); //自動調整列高 
                excelWs.Cells.EntireColumn.AutoFit(); //自動調整欄寬 
                #endregion 
 
                #region 設定顏色 
                excelWs.get_Range("B:B", Type.Missing).Font.Color = 255; 
                excelWs.get_Range("D:D", Type.Missing).Font.Color = 255; 
                excelWs.get_Range("E:J", Type.Missing).Font.Color = 255; 
                #endregion 
 
                #region 調整儲存格格式 
                excelWs.get_Range("B:B", Type.Missing).NumberFormatLocal = "@"; 
                excelWs.get_Range("E:F", Type.Missing).NumberFormatLocal = "@"; 
                excelWs.get_Range("I:J", Type.Missing).NumberFormatLocal = "@"; 
                #endregion 
 
                ClearCom(excelWs); 
                excelWorkbook.Close(true, Type.Missing, Type.Missing); 
 
                ClearCom(excelWorkbook); 
                excelApp.Workbooks.Close(); 
                excelApp.Quit(); 
                ClearCom(excelApp); 
 
                excelWs = null; 
                excelWorkbook = null; 
                excelApp = null; 
                MessageBox.Show("設定完成"); 
            } 
        } 
 
        static void ClearCom(object o) 
        { 
            try 
            { 
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o); 
            } 
            catch { } 
            finally 
            { 
                o = null; 
            } 
        }

 

這樣就完成Excel檔的格式調整了,當然還有其它未提到的參數屬性可以設,大家可以依自己的需求去找一下.

參考 :

MSDN : WorkSheet

MSDN : WorkBook

原始碼 : ExcelFormat.zip

 

arrow
arrow
    全站熱搜

    狼翔月影 發表在 痞客邦 留言(0) 人氣()