C#實現向excel中插入行列，以及設定儲存格合并置中效果

標籤：cep   單元   get_range   [1]   type   direct   cal   excel   etl   

插入空行：

Microsoft.Office.Interop.Excel.Workbook xlsWorkbook;

Microsoft.Office.Interop.Excel.Worksheet xlsSheet = xlsWorkbook.Worksheets[1];

Microsoft.Office.Interop.Excel.Range xlsRow=(Microsoft.Office.Interop.Excel.Range)xlsSheet.Rows[3,MisValue];

xlsRow.Insert(Microsoft.Office.Interop.Excel.xlShiftDown,MisValue);

 

插入空列：

Excel.Range xlsColumns = (Excel.Range)ws.Columns[index, Type.Missing];

            xlsColumns.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Type.Missing);

            string columnLetter = GetLetter(index - 1);
            ws.Cells[2, index] = txtBoxExpenseType.Text.Trim();
            Excel.Range newExpenseTypeRange = ws.get_Range(string.Format("{0}{1}", columnLetter, 2), string.Format("{0}{1}", columnLetter, 3));
            newExpenseTypeRange.MergeCells = true;
            newExpenseTypeRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            newExpenseTypeRange.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

 

 

 

public static string GetLetter(int index)
        {
            if (index < 0) { throw new Exception("invalid parameter"); }

            List chars = new List();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)‘A‘)).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);

            return String.Join(string.Empty, chars.ToArray());
        }

C#實現向excel中插入行列，以及設定儲存格合并置中效果