vba 在 C# 中使用 Excel 将单元格值传输到二维字符串数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14367077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Excel in C# to transfer Cell Values to a 2D string array
提问by Ramie
I need someone to create a method that will get me all the cell values in an excel spreadsheet into a 2D array.
我需要有人来创建一种方法，将 Excel 电子表格中的所有单元格值转换为二维数组。
I'm making it using ribbons in C# to work with Excel but i just can't get it to work.
我正在使用 C# 中的功能区来处理 Excel，但我无法让它工作。
private string[,] GetSpreadsheetData ()
{
try
{
Excel.Application exApp =
Globals.TSExcelAddIn.Application as Excel.Application;
Excel.Worksheet ExWorksheet = exApp.ActiveSheet as Excel.Worksheet;
Excel.Range xlRange = ExWorksheet.get_Range("A1","F188000");
object[,] values = (object[,])xlRange.Value2;
string[,] tsReqs = new string[xlRange.Rows.Count, 7];
char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
for (int i = 0; i < tsReqs.GetLength(0); i++)
{
for (int z = 0; z < tsReqs.GetLength(1); z++)
{
if(values[i+1,z+1] != null)
tsReqs[i, z] = values[i + 1, z + 1].ToString();
}
}
return tsReqs;
}
catch
{
MessageBox.Show
("Excel has encountered an error. \nSaving work and exitting");
return null;
}
}
Also if anyone has a more efficient way of doing this I would greatly appreciate it.
另外，如果有人有更有效的方法来做到这一点，我将不胜感激。
Excel.Range xlRange = ExWorksheet.get_Range("A1","F188000");
Reads all the way till F188000 cell from A1, I just want it to keep reading until it reaches a row with absolutely no data.
从 A1 一直读取到 F188000 单元格，我只希望它继续读取，直到到达绝对没有数据的行。
- Caught: "Index was outside the bounds of the array." (System.IndexOutOfRangeException) Exception Message = "Index was outside the bounds of the array.", Exception Type = "System.IndexOutOfRangeException"
回答by Porkbutts
You could consider using ExWorksheet.UsedRangeinstead of ExWorksheet.get_Range("A1","F188000")
你可以考虑使用ExWorksheet.UsedRange代替ExWorksheet.get_Range("A1","F188000")
EDIT:Also, I think if you use the .Text field of a Range, the value is automatically casted to a string, so no need to use .Value2 here
编辑：另外，我认为如果您使用范围的 .Text 字段，该值会自动转换为字符串，因此无需在此处使用 .Value2
Excel.Range rng = ExWorksheet.UsedRange;
int rowCount = rng.Rows.Count;
int colCount = rng.Columns.Count;
string[,] tsReqs = new string[rowCount, colCount];
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
string str = rng.Cells[i, j].Text;
tsReqs[i - 1, j - 1] = str;
}
}
回答by Bobby Tables
Try this
尝试这个
private static void printExcelValues(Worksheet xSheet)
{
Range xRng =xSheet.Cells.SpecialCells(
XlCellType.xlCellTypeConstants);
var arr = new string[xRng.Rows.Count,xRng.Columns.Count];
foreach (Range item in xRng)
{
arr[item.Row-1,item.Column-1]=item.Value.ToString();
}
}
You can use the | to specify more cell types like XlCellType.xlCellTypeFormulas or any other type you'd like.
您可以使用 | 指定更多单元格类型，如 XlCellType.xlCellTypeFormulas 或您喜欢的任何其他类型。
回答by D Stanley
Your arrays are different sizes. Your values come from A1:F188000which is 6 columns, but you're looping through the columns of tsReqswhich is 7 columns. It looks like you're trying to account for the fact that valueswill be a 1-based array but aren't accounting for that properly.
您的数组大小不同。您的值来自A1:F1880006 列，但您正在循环遍历tsReqs7 列的列。看起来您正试图解释values一个基于 1 的数组但没有正确解释这一事实。
Change the declaration of tsReqsto:
将声明更改tsReqs为：
string[,] tsReqs = new string[xlRange.Rows.Count, 6];
and you should be fine.
你应该没事的。
回答by Herman Vercuiel
I had to do something very similar. Reading an excel file takes a while... I took a different route but I got the job done with instant results.
我不得不做一些非常相似的事情。读取 excel 文件需要一段时间......我采取了不同的路线，但我立即完成了工作。
Firstly I gave each column its own array. I saved each column from the spreadsheet as a CSV(Comma delimited) file. After opening that file with notePad I was able to copy the 942 values separated with a comma to the initialization of the array
首先，我给了每一列它自己的数组。我将电子表格中的每一列保存为 CSV（逗号分隔）文件。使用记事本打开该文件后，我能够将用逗号分隔的 942 个值复制到数组的初始化中
int[] Column1 = {300, 305, 310, ..., 5000};
[0] [1] [2] ... [941]
Now... If you do this for each Column, the position will resemble the 'row' of each 'column' of your 'spreadsheet' .
现在......如果你对每一列都这样做，位置将类似于你的“电子表格”的每一“列”的“行”。
This odd method worked PERFECT for me, as I needed to compare user input sizes to the values in the "Width" column in the spreadsheet to get information regarding each respective size found in the spreadsheet.
这种奇怪的方法对我来说非常有效，因为我需要将用户输入的大小与电子表格中“宽度”列中的值进行比较，以获取有关电子表格中每个相应大小的信息。
NOTE: If your cells contain strings and not integers like mine, you can use enumeration method:
注意：如果您的单元格包含字符串而不是像我这样的整数，您可以使用枚举方法：
enum column1 { thing_1, thing_2, ..., thing_n,}
[0] [1] [n]
Here's my code If you want to have a look: (This is NOT the code converting a spreadsheet into some arrays --that is only a few lines of code, depending on your amount of columns-- this is the whole LookUp method I wrote)
这是我的代码如果你想看看：（这不是将电子表格转换成一些数组的代码——只有几行代码，取决于你的列数——这是我写的整个 LookUp 方法)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PalisadeWorld
{
//struct to store all the positions of each matched panel size in an array 'Identities'
struct ID
{
public int[] Identities;
public ID(int[] widths, int rows)
{
//Column1 from spreadsheet
int[] allWidths = { 300, 305, 310, 315, 320, 325, 330, ..., 5000 };
int i,j;
int[] Ids = new int[rows];
for (i = 0; i < rows; i++)
{
for (j = 0; j < 941; j++)
{
if (widths[i] == allWidths[j])
{
Ids[i] = j;
break;
}
}
}
this.Identities = Ids;
}
public override string ToString()
{
string data = String.Format("{0}", this.Identities);
return data;
}
}
class LookUpSheet
{
//retrieve user input from another class
public int[] lookUp_Widths {get; set;}
public int lookUp_Rows { get; set; }
//Method returning desired values from Column2
public int[] GetNumPales1()
{
//column2
int[] all_numPales = { 2, 2, 2, 2, 2, 2, 2, 2, 2, ..."goes on till [941]"...};
int[] numPales = new int[lookUp_Rows];
ID select = new ID(lookUp_Widths, lookUp_Rows);
for (int i = 0; i < lookUp_Rows; i++)
{
numPales[i] = all_numPales[select.Identities[i]];
}
return numPales;
}
//Method returning desired values from Column3
public int[] GetBlocks1()
{
//column3
int[] all_blocks = { 56, 59, 61, 64, 66, 69, 71, 74, "goes on till [941]"...};
int[] blocks = new int[lookUp_Rows];
ID select = new ID(lookUp_Widths, lookUp_Rows);
for (int i = 0; i < lookUp_Rows; i++)
{
blocks[i] = all_blocks[select.Identities[i]];
}
return blocks;
}
...
...
Goes on through each column of my spreadsheet
继续浏览电子表格的每一列
Really hope this helps someone. Cheers
真的希望这可以帮助某人。干杯
