嘿,我在处理Excel.Worksheet.Cells数组时遇到了转置行为.
我的第一个单元格必须位于[行= 10,列= 3]
我的第二个单元格必须位于[行= 11,列= 17]
然后使用这两个单元格作为边界,我创建一个范围并将其合并.
从上面提到的值可以看出,该范围应该基本上是水平的.
因此,为了帮助我,我创建了一个简单的辅助函数来合并单元格:
public static void MergeRange(Excel.Worksheet worksheet, int startRowIdx,
int startColIdx, int endRowIdx, int endColIdx, bool across = false)
{ //Get range boundaries.
Excel.Range cells = worksheet.Cells;
//commented out, resolved code is directly below - N. Miller, 9-24-2013
//Excel.Range topleftCell = cells[startColIdx][startRowIdx];
//Excel.Range bottomrightCell = cells[endColIdx][endRowIdx];
Excel.Range topleftCell = cells[startRowIdx, startColIdx];
Excel.Range bottomrightCell = cells[endRowIdx, endColIdx];
//Merge range.
Debug.WriteLine(String.Format("({0}, {1}) to ({2}, {3}).", startRowIdx, startColIdx, endRowIdx, endColIdx));
Excel.Range range = worksheet.get_Range(topleftCell, bottomrightCell);
Excel.Interior interior = range.Interior;
interior.ColorIndex = XLColor.PERIWINKLE; //JUST HIGHLIGHTS RANGE FOR NOW.
//range.Merge(across);
//Cleanup
Marshal.ReleaseComObject(interior);
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(bottomrightCell);
Marshal.ReleaseComObject(topleftCell);
Marshal.ReleaseComObject(cells);
}
在上面的代码中,我通过交换列和行来选择所需的单元格.与我的预期相反,这导致了预期的行为,但是与MSDN文档相矛盾:
在MSDN文档中,他们给出了一个示例,在该示例中首先列出该行,然后列出该列.
所以我的问题是…这是正确的吗?
行应该在第一位还是列在第一位?
当我修改我的代码以使其与MSDN文档一致时,突出显示的单元格将被转置.
解决方法:
如果我对您的理解正确,那么当您使用cells [1] [2]时,则表示A2.在这种情况下,列首先出现,然后是行.如果将其写为单元格[1,2],则将得到B2.在这种情况下,Row首先出现,然后是Column.
在Excel-VBA中相同.立即窗口中的?Cells(1)(2).address给出$A $2和?Cells(1,2).address给出$B $1如果您觉得我不理解您的查询,请为我重新措辞. ..
这是测试它的完整代码.
注意:已使用VS 2010 Ultimate Excel 2010测试
using System;
using System.Windows.Forms;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
Namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
Public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlexcel;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = Missing.Value;
xlexcel = new Excel.Application();
xlexcel.Visible = true;
//~~> Open a File (Change filename as applicable)
xlWorkBook = xlexcel.Workbooks.Open("C:\\SomeFile.xlsx",
0, true, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t", false, false, 0, true, 1, 0);
//~~> Set Sheet 1 as the sheet you want to work with
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range cells = xlWorkSheet.Cells;
Excel.Range topleftCell = cells[1][2];
MessageBox.Show(topleftCell.Address);
topleftCell = cells[1,2];
MessageBox.Show(topleftCell.Address);
//~~> Once done close and quit Excel
xlWorkBook.Close(false, misValue, misValue);
xlexcel.Quit();
//~~> CleanUp
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
屏幕截图