使用C#写入Excel中的新列 [英] Writing to new columns in excel using C#

问题描述

我对Microsoft的互操作库有些困惑.似乎并不是那么直观，我想如果他们只是坚持使用多维数组，那将是一个更好的库.话虽这么说，但我对如何使用C#写入列有些困惑.我将如何遍历单个列并将数据放入每个单独的列.更好的问题是，如何在Excel中遍历单个excel列.我基本上是试图遍历各列，并根据第一列中的值将数据添加到其他列中.我会拉出所有数据并将其放入某种数据结构(很可能是数组)中，然后将其重铸为Range或类似的东西吗?有点困惑.这是我用来创建新列的代码，我需要用从同一行中其他列获取的数据来填充这些列...

 公共静态无效setUpSheet(工作表wkSheet){范围rng =(Range)wkSheet.get_Range("A1"，Type.Missing);rng.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight，XlInsertFormatOrigin.xlFormatFromRightOrBelow);wkSheet.Range ["A1"，Type.Missing] .Value2 ="R_EMPIID";范围rng2 =(Range)wkSheet.get_Range("A1"，Type.Missing);rng2.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight，XlInsertFormatOrigin.xlFormatFromRightOrBelow);wkSheet.Range ["A1"，Type.Missing] .Value2 ="PopulationID";范围rng3 =(Range)wkSheet.get_Range("A1"，Type.Missing);rng3.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight，XlInsertFormatOrigin.xlFormatFromRightOrBelow);wkSheet.Range ["A1"，Type.Missing] .Value2 ="PopPatID";wkSheet.SaveAs("C:\\ CorrectDirectory \\ App_Data \\ test"，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing);} 

所以我可以添加一个新列并命名该列，现在我只需要弄清楚如何从其他列中获取正确的数据并将它们放在该行的另一列中(这些列将是我新的列刚刚在这里创建).有什么想法吗?

更新:尝试更加具体 我知道您可以在excel列中获取一系列值.但是无法知道数据集有多大.例如

 (Excel.Range)excelWorksheet.get_Range("A5:A35"，Type.Missing); 

那将为您提供一系列包含数据的列.但是，可以说您之前没有看过excel文件.如果您不知道该列中有多少行，将如何遍历该列中的全部?还是您应该全部捕获并执行.get_Range(A2:A10000，Type.Missing).然后在那之后，您将如何更改完全相同的行却在不同的列中?

我认为处理映射的最简单方法是创建一个函数，将基于0的整数列映射到Excel列名称:例如.0，1，2，... 26，27，28 => A，B，C ... AA，AB，AC.(您可能需要也可能不需要反向映射.)

然后在单元格地址和行/列索引之间创建映射函数非常容易，因为您知道任何单元格地址< alpha>< numeric>.变成< column>< row>.

它们没有提供基于索引的范围，因此需要做一些额外的工作，但是如果您开始执行高级公式，则无论如何都要进行此映射.

----根据更新的问题进行编辑-----

我相信 Worksheet.Cells.Width 和 Worksheet.Cells.Height 可能在这里有用.工作表可能包含很多空单元格，但至少应该是一个起点.

注意:我正在查看lib类型的版本12，不确定您使用的是哪个版本...

I am a little confounded with Microsoft's interop Library. It doesn't seem all that intuitive, and I think if they had just stuck with multidimensional arrays this would have been a much better library. That being said though, I am a little confused about how to write to columns using C#. How would I iterate through a single column and place data into each individual column. Better question, how do I loop through an individual excel column in excel. I am basically attempting to loop through columns and add data to other columns based on the value in the first column. Would I pull all of the data out and put it into some kind of data structure (most likely an array) and then recast it as a Range or something like that? Kind of confused. Here is the code that I use to create new columns that I need to populate with data that I get from other columns in that same row...

    public static void setUpSheet(Worksheet wkSheet)
    {
        Range rng = (Range)wkSheet.get_Range("A1", Type.Missing);
        rng.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight,
                                XlInsertFormatOrigin.xlFormatFromRightOrBelow);
        wkSheet.Range["A1", Type.Missing].Value2 = "R_EMPIID";

        Range rng2 = (Range)wkSheet.get_Range("A1", Type.Missing);
        rng2.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight,
                                XlInsertFormatOrigin.xlFormatFromRightOrBelow);
        wkSheet.Range["A1", Type.Missing].Value2 = "PopulationID";

        Range rng3 = (Range)wkSheet.get_Range("A1", Type.Missing);
        rng3.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight,
                                XlInsertFormatOrigin.xlFormatFromRightOrBelow);
        wkSheet.Range["A1", Type.Missing].Value2 = "PopPatID";

        wkSheet.SaveAs("C:\\CorrectDirectory\\App_Data\\test", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);            
    }

So I can add a new column and name the column, Now I just need to figure out how to get the right data out of other columns and place them in another column in that row (those columns would be the new columns I just created here). Any ideas?

UPDATE: trying to be more specific I know you can get a range of values in an excel column. But there is no way of knowing how large the dataset is. For example

    (Excel.Range)excelWorksheet.get_Range("A5:A35", Type.Missing);

That would get you a range of columns with data in them. But, lets say you haven't seen the excel file before hand. How would you loop through ALL of the rows in that column if you don't know how many rows are in it? Or should you just do a catch all and just do .get_Range(A2:A10000,Type.Missing). And then after that, how would you change the exact same rows but in a different column?

解决方案

I think the easiest way to deal with the mappings is to create a function to map 0 based integer column to Excel column names: eg. 0, 1, 2, ... 26, 27, 28 => A, B, C ... AA, AB, AC. (You may or may not need the reverse mapping).

It's then pretty easy to create a mapping function between Cell addresses and row/column indexes since you know for any cell address <alpha><numeric> becomes <column><row>.

It's a bit of extra work up front that they didn't provide index based ranges, but if you start doing advanced formulas you'd be doing this mapping anyways.

----edited based on updated question-----

I believe Worksheet.Cells.Width and Worksheet.Cells.Height might be of some use here. It's possible for a worksheet to contain a lot of empty cells, but it should at least be a starting point.

Note: I'm looking at version 12 of the type lib, not sure what version you're on...

这篇关于使用C#写入Excel中的新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！