描述:从数据库抓取数据后插入名为"Data" sheet中,"Data"为该excel pivotTable的数据源.执行程序如下
Excel.Application app = new Excel.ApplicationClass();
app.Visible = false;
app.DisplayInfoWindow = false;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Workbooks books = app.Workbooks;
Excel.Workbook book = books.Open(Server.MapPath("../Import/YTD AP REPORT1.xls"), Missing.Value, true, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
执行至 Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];系统报错忙碌
错误信息如下 :
The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))Source Error:
Line 63:
Line 64:
Line 65: Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
Line 66: // Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
Line 67: Response.Write("Print2!");
请高手指教谢谢!注:第一次执行就会报上述错误
Excel.Application app = new Excel.ApplicationClass();
app.Visible = false;
app.DisplayInfoWindow = false;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Workbooks books = app.Workbooks;
Excel.Workbook book = books.Open(Server.MapPath("../Import/YTD AP REPORT1.xls"), Missing.Value, true, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
执行至 Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];系统报错忙碌
错误信息如下 :
The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))Source Error:
Line 63:
Line 64:
Line 65: Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
Line 66: // Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
Line 67: Response.Write("Print2!");
请高手指教谢谢!注:第一次执行就会报上述错误
我已作过单步调试,在IDE下运行时没有问题的,只是发布之后执行books.open 需要一分钟然后跳至Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
就会报错了。而且我是在服务器重启后测试也是如此
但执行至Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
就会报错
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
结果页面无报错,但一直处在等待状态
//app.DisplayAlerts = false;
app.Visible = false;
app.DisplayInfoWindow = false;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Workbooks books = app.Workbooks;
Excel.Workbook book = books.Open(Server.MapPath("../Import/YTD AP REPORT1.xls"), Missing.Value, true, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
我忘记了,excel的sheet是从0开始还1开始
{
string _result = string.Empty;
DateTime start = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = false;
app.DisplayInfoWindow = false;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Workbooks books = app.Workbooks;
Excel.Workbook book = books.Open(Server.MapPath("../Import/YTD AP REPORT1.xls"), Missing.Value, true, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
System.Threading.Thread.Sleep(2000);
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
Response.Write("Print2!");
btn_export.Text = "2";
DateTime end = DateTime.Now;
string fileName = string.Empty; try
{
int currentRow = 1;
string[] data = new string[table.Columns.Count];
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < row.ItemArray.Length; i++)
{
data[i] = row.ItemArray[i].ToString();
}
currentRow++; sheet.Cells[currentRow, 1] = String.Join("|", data);
Excel.Range range = (Excel.Range)sheet.Cells[currentRow, 1];
range.TextToColumns(Missing.Value, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierNone,
false, false, false, false, false, true, "|", Missing.Value, Missing.Value, Missing.Value, Missing.Value);
} sheet.UsedRange.Columns.AutoFit();
Excel.Borders borders = ((Excel.Borders)sheet.UsedRange.Borders);
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = Excel.XlBorderWeight.xlThin;
app.ActiveWindow.FreezePanes = true; fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "_Report.xls";
book.SaveAs(Server.MapPath("../Import/UploadFile/" + fileName), Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception ex)
{
throw ex;
}
finally
{
app.Quit();
IntPtr t = new IntPtr(app.Hwnd);
int k = 0;
if (GetWindowThreadProcessId(t, out k) != 0)
{
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
NAL(sheet);
NAL(book);
NAL(books);
NAL(app); sheet = null;
book = null;
books = null;
System.Diagnostics.Process.GetProcessById(k);
System.Threading.Thread.Sleep(2000);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
GC.Collect();
_result = "../Import/UploadFile/" + fileName;
}
return _result;
}
Response.Write是发布后临时加的。
该程序只要用意就是:从数据库抓取数据插入excel sheet “data”(data为excel 透视表的数据源),然后导出。
--------------------------
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
是在这句的时候弹的么?让等待可能和对话框有关,对话框在等待响应
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
Response.Write("Print2!");
------------
//输出到日志
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];
//输出到日志这样看下,我觉得问题不在 Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[9];这句
谢谢阿非,谢谢各位