'System.AccessViolationException' occurred
本文关键字：occurred System AccessViolationException | 更新日期: 2023-09-27 18:02:13
更新:在发生错误的地方添加了完整的代码块
更新2:我发现了一个奇怪的异常。现在，当tabName变量等于"上一年的服务行"时，代码在该行上连续中断。今天早上，为了笑，我将选项卡名称更改为"test"，因此tabName变量反过来等于"test"，并且它经常工作。我真的很茫然。
我已经研究了很多，但找不到任何能解决我的代码中发生的事情的东西。不过这是随机发生的。有时它不会发生，有时它会在同一位置发生，但都在这部分代码上(在templateSheet = templateBook.Sheets[tabName];):
public void ExportToExcel(DataSet dataSet, string filePath, int i, int h, Excel.Application excelApp)
{
//create the excel definitions again.
//Excel.Application excelApp = new Excel.Application();
//excelApp.Visible = true;
FileInfo excelFileInfo = new FileInfo(filePath);
Boolean fileOpenTest = IsFileOpen(excelFileInfo);
Excel.Workbook templateBook;
Excel.Worksheet templateSheet;
//check to see if the template is already open, if its not then open it,
//if it is then bind it to work with it
if (!fileOpenTest)
{ templateBook = excelApp.Workbooks.Open(filePath); }
else
{ templateBook = (Excel.Workbook)System.Runtime.InteropServices.Marshal.BindToMoniker(filePath); }
//this grabs the name of the tab to dump the data into from the "Query Dumps" Tab
string tabName = lstQueryDumpSheet.Items[i].ToString();
templateSheet = templateBook.Sheets[tabName];
excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;
templateSheet = templateBook.Sheets[tabName];
// Copy DataTable
foreach (System.Data.DataTable dt in dataSet.Tables)
{
// Copy the DataTable to an object array
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
// Copy the values to the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{ rawData[row, col] = dt.Rows[row].ItemArray[col]; }
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{ finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1); }
finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);
//this grabs the cell address from the "Query Dump" sheet, splits it on the '=' and
//pulls out only the cell address (i.e., "address=a3" becomes "a3")
string dumpCellString = lstQueryDumpText.Items[i].ToString();
string dumpCell = dumpCellString.Split('=').Last();
//referts to the range in which we are dumping the DataSet. The upper right hand cell is
//defined by the 'dumpCell' varaible and the bottom right cell is defined by the
//final column letter and the count of rows.
string firstRef = "";
string baseRow = "";
if (char.IsLetter(dumpCell, 1))
{
char[] createCellRef = dumpCell.ToCharArray();
firstRef = createCellRef[0].ToString() + createCellRef[1].ToString();
for (int z = 2; z < createCellRef.Count(); z++)
{
baseRow = baseRow + createCellRef[z].ToString();
}
}
else
{
char[] createCellRef = dumpCell.ToCharArray();
firstRef = createCellRef[0].ToString();
for (int z = 1; z < createCellRef.Count(); z++)
{
baseRow = baseRow + createCellRef[z].ToString();
}
}
int baseRowInt = Convert.ToInt32(baseRow);
int startingCol = ColumnLetterToColumnIndex(firstRef);
int endingCol = ColumnLetterToColumnIndex(finalColLetter);
int finalCol = startingCol + endingCol;
string endCol = ColumnIndexToColumnLetter(finalCol - 1);
int endRow = (baseRowInt + (dt.Rows.Count - 1));
string cellCheck = endCol + endRow;
string excelRange;
if (dumpCell.ToUpper() == cellCheck.ToUpper())
{
excelRange = string.Format(dumpCell + ":" + dumpCell);
}
else
{
excelRange = string.Format(dumpCell + ":{0}{1}", endCol, endRow);
}
//this dumps the cells into the range on Excel as defined above
templateSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
//checks to see if all the SQL queries have been run from the "Query Dump" tab, if not, continue
//the loop, if it is the last one, then save the workbook and move on.
if (i == lstSqlAddress.Items.Count - 1)
{
excelApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
/*Run through the value save sheet array then grab the address from the corresponding list
place in the address array. If the address reads "whole sheet" then save the whole page,
else set the addresses range and value save that.*/
//for (int y = 0; y < lstSaveSheet.Items.Count; y++)
//{
// MessageBox.Show("Save Sheet: " + lstSaveSheet.Items[y] + "'n" + "Save Address: " + lstSaveRange.Items[y]);
//}
//run the macro to hide the unused columns
excelApp.Run("ReportMakerExecute");
//save excel file as hospital name and move onto the next
SaveTemplateAs(templateBook, h);
//close the open Excel App before looping back
//Marshal.ReleaseComObject(templateSheet);
//Marshal.ReleaseComObject(templateBook);
//templateSheet = null;
//templateBook = null;
//GC.Collect();
//GC.WaitForPendingFinalizers();
}
//Close excel Applications
//excelApp.Quit();
//Marshal.ReleaseComObject(templateSheet);
//Marshal.FinalReleaseComObject(excelApp);
//excelApp = null;
//templateSheet = null;
// GC.Collect();
//GC.WaitForPendingFinalizers();
}
}
try/catch块也没有用。这是错误:
"An unhandled exception of type 'System.AccessViolationException' occurred inSQUiRE (Sql QUery REtriever) v1.exe. Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
System.AccessViolationException通常会在您尝试访问本机代码(不是. net)中的未分配内存时发生。然后。net将其转换为托管世界中的此异常。
您的代码本身没有任何不安全块。所以访问冲突一定发生在Excel内部。
考虑到它有时发生，有时不发生的事实，我想说它可能是由并行Excel使用引起的(我认为Excel COM不是线程安全的)。
我建议你把你所有的代码在lock块，以防止Excel开始并行使用。像这样:
public void ExportToExcel(DataSet dataSet, string filePath, int i, int h, Excel.Application excelApp)
{
lock(this.GetType()) // You can change here to other instance to me used a mutex
{
// Your original code here
}
}
说来话长，三天的测试时间更长，这是因为一个excel文件试图打开并填充SQL结果。缓冲区被填满，导致异常…它只是在每次运行的同一点发生，因为excel文件的加载时间是它工作或失败的决定性因素。
所以在加载之后，我只是添加了一个延迟do…当检查文件是否可访问时，它停止了失败。fileOpenTest从这里取
do
{
Task.Delay(2000);
}
while(!fileOpenTest);