从c#中阅读一个巨大的Excel专栏
本文关键字:一个 巨大 专栏 Excel | 更新日期: 2023-09-27 18:07:36
我有一个四列的Excel文档(可能是2010年或2013年,不知道这是否会成为以后的问题)。前三列存储电话号码,基本上是一个包含10个或更多字符的字符串。只有四列,永远存储1 2 3或4,这是一个类别。我需要检查列A中的每个数字是否出现在列B和C中,所以我认为在读取每个列的所有Excel单元格并存储在列表中(尚未实现,因为问题我将在下面解释)。为此,我编写了以下代码:
private void btnCargarExcel_Click(object sender, EventArgs e)
{
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
if (System.IO.File.Exists(openFileDialog1.FileName))
{
filePath.Text = openFileDialog1.FileName.ToString();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(openFileDialog1.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "'t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
str = (range.Cells[rCnt, 1] as Excel.Range).Value2.ToString();
//bd.Add(cleanString(str));
bd.Add(cleanString(str, 10));
}
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
str = (range.Cells[rCnt, 2] as Excel.Range).Value2.ToString();
//bd.Add(cleanString(str));
bl.Add(cleanString(str, 10));
}
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
str = (range.Cells[rCnt, 3] as Excel.Range).Value2.ToString();
//bd.Add(cleanString(str));
cm.Add(cleanString(str, 10));
}
nrosProcesados.Text = bd.Count().ToString();
listBox1.DataSource = bd;
noProcesadosBL.Text = bl.Count().ToString();
listBox2.DataSource = bl;
noProcesadosCM.Text = cm.Count().ToString();
listBox3.DataSource = cm;
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
else
{
MessageBox.Show("No se pudo abrir el fichero!");
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
System.Windows.Forms.Application.Exit();
}
}
}
所以我在一个列中遍历单元格,并在做一些字符串更改后将每个数字存储在列表中,正如您在代码中看到的那样。这里的问题是,列A有797340个单元格,列B有91617个单元格,列C有95891个单元格,所以如果我运行应用程序,加载Excel并等待我的PC挂出(甚至有12GB的RAM和Core i3处理器),我需要打开任务管理器并结束任务。为了得到我想要的东西(只留下不重复的数字)而不挂我的电脑,最好的解决方案是什么?对于每个周期,是否可以将事情划分为单独的线程(我不太了解这一点,因为我是从c#开始的,所以任何帮助都将受到赞赏)?你对这个话题有什么看法?
EDIT: Add a new and clean method
所以在阅读和阅读并得到一些成员的帮助后,我改进了代码一点,但现在我有另一个问题(在代码下面注释)。请看下面的代码:
// this goes first when I declare vars
public static System.Array objRowAValues;
// this goes in action when I click the button (I leave only relevant part)
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range, rngARowLast;
string str;
int rCnt = 0;
long lastACell, fullRow;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(openFileDialog1.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "'t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
fullRow = xlWorkSheet.Rows.Count;
lastACell = xlWorkSheet.Cells[fullRow, 1].End(Excel.XlDirection.xlUp).Row;
rngARowLast = xlWorkSheet.get_Range("A1", "A" + lastACell);
objRowAValues = (System.Array) rngARowLast.Cells.Value;
现在因为我要用objrowavvalues的值填充ListBox, ListBox只接受列表作为数据源,然后我需要将objrowavvalues转换为字符串列表。我试过了,但对我不起作用。任何帮助吗?
不幸的是,我更喜欢VB。NET小伙-所以我已经为你转换了一些代码。我希望这是开箱即用的——我在这里不使用这种工具,所以我没有办法测试它。
public void test()
{
object[,] RaWData = null;
dynamic range = xlWorkSheet.UsedRange;
//i am unsure here about the correct order - I do not work with excel at Work, so you might have to change the following lange, if columns needs to be before rows or so
RaWData = range.value2;
//I am using a list here, because Lists are a lot easier to work with then simple arrays
List<List<string>> RealData = new List<List<string>>();
//start at 1 because the excel-delivered array do not have values at index 0 - this is the only 1-based array you will ever encounter in .net
for (x = 1; x <= Information.UBound(RaWData, 1); x++) {
List<string> templist = new List<string>();
for (y = 1; y <= Information.UBound(RaWData, 2); y++) {
templist.Add(RaWData[x, y].ToString());
}
RealData.Add(templist);
}
//you should be finished here...
}