C#在窗体中嵌入Excel 有关字体，对齐 加边框 自动设置列 等操作

有关格式的问题，可以先打开excel ,录制宏，生成VB代码，再转换成C#语言，

1 设置字体大小

oSheet.get_Range("A" + (nFillRow).ToString(), strCol + "1").Font.Size =18;

2 根据列的大小（int型 ）得到对应的列名

//// 取得列名
// 通常字段数不会太多，所以到 26*5 目前已经够了。
        private string GetColName(int iColNum)
        {
              string  strColNames = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,"
                  +"AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ,"
                  +"BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ,"
                  +"CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO,CP,CQ,CR,CS,CT,CU,CV,CW,CX,CY,CZ," 
                 +"DA,DB,DC,DD,DE,DF,DG,DH,DI,DJ,DK,DL,DM,DN,DO,DP,DQ,DR,DS,DT,DU,DV,DW,DX,DY,DZ";
            string []ss=strColNames .Split(new char []{','});
            return ss[iColNum - 1];

  }

3、根据每一列的内容，设置合适的列宽（列宽比每一列的最大值大一点点就可以了）

                for(int i=0;i<nRow ;i++)
                    for(int j=0;j<nCol ;j++)
                        iWeights[i, j] = ss[i, j].Length;//得到各单元格的长度
               iColoumn[0] = 0;

                for (int m = 0; m < nCol; m++)
                {
                   for (int k = 0; k < nRow ; k++)
                   if (iWeights[k , m] >  iColoumn[m] )
                    iColoumn[m] = iWeights[k , m];//得到各列最大值，
                }
                  if (bSetColoumnWeight == true)
                  {
                      for (int c = 0; c < nCol; c++)
                      {
                        string strColoumn = GetColName(c + 1);
                        oSheet.get_Range(strColoumn + "1", strColoumn + "2").ColumnWidth = iColoumn[c] + 3;  //设置每列宽度比它本身的数字大3个位置噢

                      }
                  }

 

4、给选中的范围加边框 、样式 
     oRange = oSheet.get_Range("A1", "e6");
     oRange.Borders.Weight = 2;
      oRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle  =  Excel.XlLineStyle.xlContinuous;       

 // range.Interior.Pattern = Excel.XlPattern.xlPatternCrissCross;  加样式

5、内容居中对齐

    StringRange1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
   // StringRange1.HorizontalAlignment = HorizontalAlignment.Center; //开始用此句，不行啊

 

  1using System;
  2using System.Collections.Generic;
  3using System.ComponentModel;
  4using System.Data;
  5using System.Drawing;
  6using System.Text;
  7using System.Windows.Forms;
  8using System.Threading;
  9using System.Diagnostics;
 10using SuperMapLib;
 11using System.Reflection;
 12using System.Collections;
 13
 14using Excel = Microsoft.Office.Interop.Excel;
 15using ExcelApplication = Microsoft.Office.Interop.Excel.Application;
 16using XlBordersIndex = Microsoft.Office.Interop.Excel.XlBordersIndex;
 17namespace soFillExcel
 18{
 19    /**//// <summary>
 20    /// 
 21    /// </summary>
 22    public partial class frmExcel : Form
 23    {
 24      
 25        soRecordset objRecordset;// = Common.objRecordSet;
 26        soFieldInfos objFieldInfos=new soFieldInfos();
 27        int nRows, nCols;//,i,j;
 28        bool bSave;
 29        string[ ,] strs;
 30   
 31        
 32        //Excel.Application oExcel= new Excel.Application();
 33        ExcelApplication oExcel = new Excel.Application();
 34        Excel.Workbook oBook ;
 35        Excel.Worksheet oSheet;
 36        Excel.Range oRange;
 37        Excel.Range range;
 38       string strPath="";//初始时默认打开的文件路径名
 39       string strSavePath="";
 40        int nFillRow, nFillCol;
 41        /**//// <summary>
 42        /// 
 43        /// </summary>
 44        /// <param name="objRs"></param>
 45        public frmExcel(soRecordset objRs)
 46        {
 47            objRecordset = objRs;
 48            InitializeComponent();
 49        }
 50        /**//// <summary>
 51        /// 
 52        /// </summary>
 53        /// <param name="ss"></param>
 54        public frmExcel(string [,]ss)
 55        {
 56            strs = ss;
 57            InitializeComponent();
 58        }
 59        private void frmExcel_Load(object sender, EventArgs e)
 60        {
 61
 62        }
 63
 64        private void btnPrint_Click(object sender, EventArgs e)
 65        {
 66            this.axOA1.Print();
 67        }
 68
 69       
 70        private void btnSaveAsFile_Click(object sender, EventArgs e)
 71        {
 72            this.saveFileDialog1.Filter = "Excel( *.xls)|*.xls";
 73            this.saveFileDialog1.ShowDialog();
 74            this.axOA1.Save(this.saveFileDialog1.FileName);
 75         
 76
 77        }
 78
 79        private void btnSaveFile_Click(object sender, EventArgs e)
 80        {
 81            save();
 82            bSave = true;
 83        }
 84        private void save()
 85        {
 86          try
 87            {
 88      
 89            DateTime now = DateTime.Now;
 90            string strTime = now.Year.ToString() + "-" + now.Month.ToString() + "-" + now.Day.ToString() + "-" + now.Hour.ToString() + "-" + now.Minute.ToString() + "-" + now.Second.ToString();
 91            strPath =strSavePath +strTime + ".xls";
 92            this.axOA1.Save(strPath);
 93            MessageBox.Show("保存成功！");
 94        }
 95        catch 
 96        {
 97
 98        }
 99        finally
        {
        }
      }
     private void frmExcel_FormClosed(object sender, FormClosedEventArgs e)
        {

              try
              {
                this.Hide();
              
                oBook = null;
                oSheet = null;
                oRange = null;
                Common.bfrmShowed = false;
                if (oExcel != null)
                {
                    oExcel.Quit();
                    oExcel = null;
                }
            }
            finally
              {
                  this.Dispose();
                  GC.Collect();
                  GC.WaitForPendingFinalizers(); 
            
              }
        }

        private void btnOpenFile_Click(object sender, EventArgs e)
        {
            try
            {
          
            if (bSave==false)
            {
              DialogResult ds=   MessageBox.Show("当前文档尚未保存，点击确定保存！", "系统提示", MessageBoxButtons.OKCancel);
              if (ds == DialogResult.OK)
              {
                  save();
                  bSave = true;
              }
              else return;
            }
                this.openFileDialog1.Filter = "Excel( *.xls)|*.xls";
                this.openFileDialog1 .ShowDialog();
                this.axOA1.Open(this .openFileDialog1.FileName);
           
        }
        catch 
        {

        }
        finally
        {
        }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Hide();
            this.axOA1.Close();

            this.Close();
        }

        


        /**//// <summary>
        /// 数据集填充excel
        /// </summary>
        /// <param name="objRs">数据集</param>
        /// <param name="nType"></param>
        /// <param name="strStart"></param>
        /// <param name="strEnd"></param>
        /// <param name="bAutoSetColoumn">自动设置列宽</param>
        public void frmLoaded(soRecordset objRs, int nType, string strStart, string strEnd,bool bAutoSetColoumn)
        {
            int [,] iWeights;//用于得到每个单元格的宽度
            int[] iColoumn;//得到每列最大宽度
            try
            {
                if (Common.bfrmShowed == true)
                    this.axOA1.Close();
                switch (nType)
                {
                    case 1: //普通表
                        nFillRow = 2;
                        nFillCol = 1;
                        strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\sample.xls";
                        strSavePath = System.Windows.Forms.Application.StartupPath + "\\其它表\\";
                        break;
                    case 2:  //统计表
                        nFillRow = 14;
                        nFillCol = 1;
                        strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\Template.xls";
                        strSavePath = System.Windows.Forms.Application.StartupPath + "\\字段统计表\\";
                        break;
                    case 3:
                        nFillRow = 9;
                        nFillCol = 1;
                        strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\SampleGjx.xls";
                        strSavePath = System.Windows.Forms.Application.StartupPath + "\\检修表\\";
                        break;
                    case 4:
                        nFillRow = 11;
                        nFillCol = 1;
                        strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\SampleGwx.xls";
                        strSavePath = System.Windows.Forms.Application.StartupPath + "\\维修表\\";
                        break;
                    default:
                        break;
                }

                Common.bfrmShowed = true;
                axOA1.Open(strPath);
                oBook = (Excel.Workbook)axOA1.GetIDispatch();
                oExcel = oBook.Application;
                oSheet = (Excel.Worksheet)oBook.Worksheets[1];
                if (objRs == null)
                {
                    MessageBox.Show("选择集为空,请先选择对象！");
                    return;
                }
                nRows = objRs.RecordCount;
                nCols = objRs.FieldCount;
                iWeights=new int [nRows,nCols+1];
                iColoumn = new int[nCols ];
                string strCol = GetColName(nCols); //调用自定义函数得到列名
                //加粗边框
                Excel.Range StringRange = (Excel.Range)oSheet.Cells[nFillRow, nFillCol];
                StringRange = StringRange.get_Resize(nRows, nCols);
                StringRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                StringRange.Borders.Weight = 2;
                StringRange.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;

                string[,] strValues = new string[nRows, nCols];
                string a = "";
                string[] s = new string[nCols];
                for (int j = 0; j < objRs.FieldCount; j++)
                    s[j] = objRs.GetFieldInfo(j + 1).Name.Trim();
                objRs.MoveFirst();
                for (int i = 0; i < nRows; i++)
                {
                   for (int j = 0; j < nCols; j++)
                    {
                  
                       iWeights[i,j]  = 0;
                        a = s[j];
                        strValues[i, j] = objRs.GetFieldValue(a).ToString();
                        iWeights[i,j] = strValues[i, j].Length;//得到各单元格的长度
                  
                    }
                    if (objRs.IsEOF() != true)
                    {

                        objRs.MoveNext();
                    }
                }
                   iColoumn[0] = 0;
                 
                     for (int m = 0; m < nCols; m++)
                     {
                         for (int k = 0; k < nRows-1; k++)
                             if (iWeights[k + 1, m] >= iWeights[k, m])
                                 iColoumn[m] = iWeights[k + 1, m];
                     }
                     if (bAutoSetColoumn == true)
                     {
                         for (int c = 0; c < nCols; c++)
                         {
                             string strColoumn = GetColName(c + 1);
                             oSheet.get_Range(strColoumn + "1", strColoumn + "2").ColumnWidth = iColoumn[c] + 3;

                         }
                     }
                Excel.Range StringRange1 = (Excel.Range)oSheet.Cells[nFillRow, nFillCol];

                StringRange1 = StringRange.get_Resize(nRows, nCols);
                StringRange1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                StringRange1.Value2 = strValues;
                oSheet.get_Range("A" + (nFillRow - 1).ToString(), strCol + "1").Merge(false);
                oSheet.get_Range("A" + (nFillRow - 1).ToString(), strCol + "1").Font.Size = 15;
                oSheet.get_Range("A" + (nRows + nFillRow), strCol.ToString() + (nRows + nFillRow).ToString()).Merge(false);
                oSheet.Cells[nFillRow - 1, nFillCol] = strStart;
                oSheet.Cells[nFillRow + nRows, nFillCol] = strEnd;

            }
            catch (System.Exception strExcel)
            {
                MessageBox.Show(strExcel.ToString());
            }
            finally
            {
            }

        }

        public void frmLoaded(string[,] ss, int nType, string strStart, string strEnd,bool bSetColoumnWeight)
        {
            int[,] iWeights;//用于得到每个单元格的宽度
            int[] iColoumn;//得到每列最大宽度
            try
            {
                if (Common.bfrmShowed == true)
                    this.axOA1.Close();

                switch (nType)
                {
                    case 1: //普通表
                        nFillRow = 2;
                        nFillCol = 1;
                        strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\sample.xls";
                        strSavePath = System.Windows.Forms.Application.StartupPath + "\\其它表\\";
                        break;
                    case 2:  //统计表
                        nFillRow = 14;
                        nFillCol = 1;
                        strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\Template.xls";
                        strSavePath = System.Windows.Forms.Application.StartupPath + "\\字段统计表\\";
                        break;
                    case 3:
                        nFillRow = 9;
                        nFillCol = 1;
                        strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\SampleGjx.xls";
                        strSavePath = System.Windows.Forms.Application.StartupPath + "\\检修表\\";
                        break;
                    case 4:
                        nFillRow = 11;
                        nFillCol = 1;
                        strPath = System.Windows.Forms.Application.StartupPath + "\\Template\\SampleGwx.xls";
                        strSavePath = System.Windows.Forms.Application.StartupPath + "\\维修表\\";
                        break;
                    default:
                        break;
                }

                if (ss == null)
                {
                    MessageBox.Show("选择集为空,请重新选择对象！");
                    return;
                }
                Common.bfrmShowed = true;
                axOA1.Open(strPath);
                oBook = (Excel.Workbook)axOA1.GetIDispatch();
                oExcel = oBook.Application;
                oSheet = (Excel.Worksheet)oBook.Worksheets[1];

                int nRow = ss.GetLength(0);
                int nCol = ss.GetLength(1);
                iWeights = new int[nRow, nCol];
                iColoumn = new int[nCol];

                string strCol = GetColName(nCol); //调用自定义函数得到列名
                //合并第一行，到有多少列
                oSheet.get_Range("A" + (nFillRow - 1).ToString(), strCol + "1").Merge(false);//设置从第填充行的前一行 到列合并    比如合并（"A1","E1")
                //oSheet.get_Range("A" + (nFillRow).ToString(), strCol + "1").Borders.Weight = 15;
                // oSheet.get_Range("A" + (nFillRow).ToString(), strCol + "1").Cells.RowHeight = 5;
                oSheet.get_Range("A" + (nFillRow - 1).ToString(), strCol + "1").Font.Size = 15;
                //加粗边框
                Excel.Range StringRange = (Excel.Range)oSheet.Cells[nFillRow, nFillCol];//得到要开始加粗的单元格  从第二行每一列开始
                StringRange = StringRange.get_Resize(ss.GetLength(0), ss.GetLength(1)); //得到要加粗的区域    记录条数，列的范围
                StringRange.Borders.Weight = 2;
                StringRange.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;


                //填充单元格的内容
                Excel.Range StringRange1 = (Excel.Range)oSheet.Cells[nFillRow, nFillCol];

                StringRange1 = StringRange1.get_Resize(ss.GetLength(0), ss.GetLength(1));

                StringRange1.Value2 = ss;
                //合并最后一行 到指定列
                oSheet.get_Range("A" + (nRow + nFillRow), strCol.ToString() + (nRow + nFillRow).ToString()).Merge(false);
                //设置第一行和最后一行的内容
                oSheet.Cells[nFillRow - 1, nFillCol] = strStart;
                oSheet.Cells[nFillRow + nRow, nFillCol] = strEnd;

                for(int i=0;i<nRow ;i++)
                    for(int j=0;j<nCol ;j++)
                    iWeights[i, j] = ss[i, j].Length;//得到各单元格的长度
               iColoumn[0] = 0;

                for (int m = 0; m < nCol; m++)
                {
                   for (int k = 0; k < nRow - 1; k++)
                   if (iWeights[k + 1, m] >= iWeights[k, m])
                    iColoumn[m] = iWeights[k + 1, m];
                }
                  if (bSetColoumnWeight == true)
                  {
                      for (int c = 0; c < nCol; c++)
                      {
                          string strColoumn = GetColName(c + 1);
                          oSheet.get_Range(strColoumn + "1", strColoumn + "2").ColumnWidth = iColoumn[c] + 3;

                      }
                  }

            }
            catch (System.Exception strExcel)
            {
                MessageBox.Show(strExcel.ToString());
            }
            finally
            {
            }

        }

/**///// 取得列名
//Private Function GetColName(ByVal intNum As Integer) As String
//   Dim strColNames As String
//   Dim strReturn() As String
   // 通常字段数不会太多，所以到 26*5 目前已经够了。
        private string GetColName(int iColNum)
        {
              string  strColNames = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,"
                  +"AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ,"
                  +"BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ,"
                  +"CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO,CP,CQ,CR,CS,CT,CU,CV,CW,CX,CY,CZ," 
                  +"DA,DB,DC,DD,DE,DF,DG,DH,DI,DJ,DK,DL,DM,DN,DO,DP,DQ,DR,DS,DT,DU,DV,DW,DX,DY,DZ";
   //strReturn = Split(strColNames, ",", 130, 0);
           // string strReturn= strColNames .Split(
            string []ss=strColNames .Split(new char []{','});
            return ss[iColNum - 1];
                   
        }

    
    }
}