- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 报存储空间不足 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=99333)
-- 发布时间：2017/4/17 16:00:00
-- 报存储空间不足
下列代码执行一个EXCEL文件中有50个多SHEET时报错“存储空间不足”的错误，检查硬盘及内容都有富余并未不足，请老师帮助看看是哪里的代码有问题？
Dim st As Date = Date.Now
Dim lbl As WinForm.Label = Forms("窗口1").Controls("Label6")
Dim lbl8 As WinForm.Label = Forms("窗口1").Controls("Label8")
Dim lbl9 As WinForm.Label = Forms("窗口1").Controls("Label9")
Dim val As WinForm.NumericComboBox = Forms("窗口1").Controls("NumericComboBox1")
Dim path As String = args(0)
Dim file As Object
Dim app As new MSExcel.Application
\'app.visible = True
\'//准备字典信息
\'try
Dim dic As new Dictionary(of String,String)
Dim dicfile As String = Forms("窗口1").Controls("TextBox2").value
Dim Book As New XLS.Book(dicfile) \'定义一个Excel工作簿
Dim Sheet As XLS.Sheet = Book.Sheets(0) \'引用工作簿的第一个工作表
For i As Integer = 0 To Sheet.Rows.Count-1
If dic.ContainsKey(Sheet(i, 0).Text.ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), "")) = False Then
dic.add(Sheet(i, 0).Text.ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""),Sheet(i, 1).Text)
End If
Next
\'--------------------
\'//开始翻译
For Each file In FileSys.GetFiles(path)
\'try
If file.EndsWith(".xls") OrElse file.EndsWith(".xlsx") Then
Dim wb=app.WorkBooks.open(file)
For k As Integer=1 To wb.worksheets.Count
Dim Ws As MSExcel.WorkSheet=Wb.WorkSheets(k)
Dim rg As MSExcel.Range=Ws.UsedRange
If rg.Count = 1 And rg(1).Value = "" Then
Continue For
End If
Dim ary = rg.value
For i As Integer=1 To rg.Rows.count
\'If rg.Rows(i).height<> 0 Then
\'For i As Integer = 0 To Sheet1.Rows.Count-1
\' If sheet1.Rows(i).height <> 0=True Then
For j As Integer = 1 To rg.Columns.Count
\'If rg.Columns(j).width <> 0 Then
If ary(i, j) <> Nothing AndAlso Typeof ary(i,j) Is String AndAlso dic.ContainsKey(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), "")) Then
If Forms("窗口1").Controls("RBt5").checked Then
\'纯译文=真
\'Sheet1(i, j).Value = dic(Sheet1(i, j).Text.ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
rg(i, j).Value= dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
lbl8.text=dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
Else If Forms("窗口1").Controls("RBt6").checked Then \'对照=真
If Forms("窗口1").Controls("RBt1").checked Then
\'对照+竖线
\'msgbox(rg(i, j).Value)
output.show(rg(i, j).Value)
rg(i, j).Value= ary(i,j) & "|" & dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
\'lbl8.text=ary(i,j) & "|" & dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
Else If Forms("窗口1").Controls("RBt2").checked=True Then
\'对照+回车
rg(i, j).Value= ary(i,j) & vbcrlf & dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
lbl8.text=ary(i,j) & vbcrlf & dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
Else If Forms("窗口1").Controls("RBt4").checked=True Then
\'对照+右列
rg(i, j).Value = ary(i,j)
rg(i,j+1).value = dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
\'错误ws.Cells(i,j+1).value = dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
lbl8.text=ary(i,j) & "+" & dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
End If
End If
End If
\' End If
Next
\'End If
Next
Next
output.show(3)
Dim txt1 As WinForm.TextBox = Forms("窗口1").Controls("TextBox3")
txt1.text = file & vbcrlf & txt1.text & vbcrlf
Application.DoEvents()
FileCount=FileCount+1
lbl.Text="共处理" & FileCount & "个文件"
wb.Save
app.quit
End If
Next
For Each p As String In FileSys.GetDirectories(path)
Functions.Execute("函数1", p)
Next
lbl9.Text="计算结束, 耗时: " & (Date.Now - st).TotalSeconds & "秒"
-- 发布时间：2017/4/17 17:11:00
--
上传个实例上来测试下吧，调试才知道问题所在。
-- 发布时间：2017/4/18 10:17:00
--
我测试了一下EXCEL表某个SHEET的列达到了上限，我把列删除一部分就就报错了，可是即使达到上限也不该报存储空间不足啊。
-- 发布时间：2017/4/18 10:44:00
--
这里的存储空间应该是指内存分配的空间。
-- 发布时间：2017/4/18 11:19:00
--
老师帮忙看看，例子已经上传
[此贴子已经被作者于2017/4/18 11:22:22编辑过]
-- 发布时间：2017/4/18 12:02:00
--
实际可用行的问题：http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=92748&authorid=0&page=0&star=1
这样处理一下：
......
For Each file In FileSys.GetFiles(path)
\'try
If file.EndsWith(".xls") OrElse file.EndsWith(".xlsx") Then
Dim wb=app.WorkBooks.open(file)
For k As Integer=1 To wb.worksheets.Count
Dim Ws As MSExcel.WorkSheet=Wb.WorkSheets(k)
Dim rowsmax As Integer = 0
Dim Colsmax As Integer = ws.UsedRange.columns.count
Output.Show("ws.UsedRange.columns.count=" & Colsmax)
For i As Integer = 1 To Colsmax
Dim r = ws.cells(65535,i).End(MsExcel.XlDirection.xlUp).Row
If r > rowsmax Then
rowsmax = r
End If
Next
Output.Show("rowsmax =" & rowsmax )
Dim rg As MSExcel.Range = Ws.Range(Ws.Cells(1,1), Ws.Cells(RowsMax,Colsmax))
\'Dim rg As MSExcel.Range=Ws.UsedRange
If rg.Count = 1 And rg(1).Value = "" Then
Continue For
End If
Dim ary = rg.value
Output.Show("ary ok")
For i As Integer=1 To rg.Rows.count
......
-- 发布时间：2017/4/18 17:12:00
--
实际可用行的问题解决了，那实际可用列的问题是不是也这样套用？
-- 发布时间：2017/4/18 17:46:00
--
可以，看6楼论坛链接例子
-- 发布时间：2017/4/19 11:07:00
--
按论坛以前的例子改了一下，原例子中的EXCEL不报错，我又换了一个EXCEL，其中有一个SHEET的最大列值为：16384，处理中还是报内存不够的错误，我重新上传了例子和测试文件有劳老师再帮忙看看
-- 发布时间：2017/4/19 11:39:00
--
1、我这边设备可能好一点，没有报错。
2、当打开excel，特别是给数组赋值的时候，行数列数越多，需要的内存就越多，当你电脑支撑不住的时候，就会报错。
3、你给数组赋值的时候，可以每次1000行，这样可以减少内存溢出的问题。