我想要从数据库中提取数据 然后生成Excel 然后提供下载,
可是有的时候点下载就可以,有的时候说是有进程在使用无法访问该文件,
请高手帮忙解决
下边是代码:
错误代码
文件“D:\mjmweb\download\试题格式档-语文.xlsx”正由另一进程使用,因此该进程无法访问该文件。这里是后台代码: private void doExport(System.Data.DataTable ds, string strExcelFileName)
{ Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); // Excel.Workbook obj=new Excel.WorkbookClass();
// obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); int rowIndex = 1;
int colIndex = 0; excel.Application.Workbooks.Add(true);
System.Data.DataTable table = ds;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
} foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
excel.Visible = false;
//excel.Sheets[0] = "ss";
//excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null,null);
excel.ActiveWorkbook.SaveAs(strExcelFileName, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //wkbNew.SaveAs strBookName
//excel.Save(strExcelFileName);
excel.Quit();
excel = null; GC.Collect();//垃圾回收
}//按钮事件
protected void Button1_Click(object sender, EventArgs e)
{
string filename="file:///D:\\mjmmm\\mjm\\mjmweb\\download\\试题格式档-"+this.ddlSub.SelectedItem.Text;
doExport(StudentScoreManager.GetStudentScoreTable(this.ddlExam.SelectedValue, this.ddlSub.SelectedValue, this.ddlSchool.SelectedValue, ddlGrade.SelectedValue, ddlClass.SelectedValue), filename);
string file = "download/试题格式档-" + this.ddlSub.SelectedItem.Text + ".xlsx";
Download(file);
}
//下载块代码
public void Download(String Filename)
{
Response.ContentType = "application/x-zip-compressed";
Response.AddHeader("Content-Disposition", "attachment;FileName=" + HttpUtility.UrlEncode("试题格式档-" + this.ddlSub.SelectedItem.Text + ".xlsx", Response.HeaderEncoding));
string filename = Server.MapPath(Filename);
Response.TransmitFile(filename);
}
可是有的时候点下载就可以,有的时候说是有进程在使用无法访问该文件,
请高手帮忙解决
下边是代码:
错误代码
文件“D:\mjmweb\download\试题格式档-语文.xlsx”正由另一进程使用,因此该进程无法访问该文件。这里是后台代码: private void doExport(System.Data.DataTable ds, string strExcelFileName)
{ Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); // Excel.Workbook obj=new Excel.WorkbookClass();
// obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); int rowIndex = 1;
int colIndex = 0; excel.Application.Workbooks.Add(true);
System.Data.DataTable table = ds;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
} foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
excel.Visible = false;
//excel.Sheets[0] = "ss";
//excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null,null);
excel.ActiveWorkbook.SaveAs(strExcelFileName, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //wkbNew.SaveAs strBookName
//excel.Save(strExcelFileName);
excel.Quit();
excel = null; GC.Collect();//垃圾回收
}//按钮事件
protected void Button1_Click(object sender, EventArgs e)
{
string filename="file:///D:\\mjmmm\\mjm\\mjmweb\\download\\试题格式档-"+this.ddlSub.SelectedItem.Text;
doExport(StudentScoreManager.GetStudentScoreTable(this.ddlExam.SelectedValue, this.ddlSub.SelectedValue, this.ddlSchool.SelectedValue, ddlGrade.SelectedValue, ddlClass.SelectedValue), filename);
string file = "download/试题格式档-" + this.ddlSub.SelectedItem.Text + ".xlsx";
Download(file);
}
//下载块代码
public void Download(String Filename)
{
Response.ContentType = "application/x-zip-compressed";
Response.AddHeader("Content-Disposition", "attachment;FileName=" + HttpUtility.UrlEncode("试题格式档-" + this.ddlSub.SelectedItem.Text + ".xlsx", Response.HeaderEncoding));
string filename = Server.MapPath(Filename);
Response.TransmitFile(filename);
}
这句是调用创建保存为本地资源的
CreateExcel(ToDataSet(list), "1", "ShengHao.xls");
下面的是成员方法,看看适合你的要求不
public static DataSet ToDataSet(IList p_List)
{
DataSet result = new DataSet();
DataTable _DataTable = new DataTable();
if (p_List.Count > 0)
{
PropertyInfo[] propertys = p_List[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
_DataTable.Columns.Add(pi.Name, pi.PropertyType);
} for (int i = 0; i < p_List.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
object obj = pi.GetValue(p_List[i], null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
_DataTable.LoadDataRow(array, true);
}
}
result.Tables.Add(_DataTable);
return result;
}public void CreateExcel(DataSet ds, string typeid, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
int i = 0; //定义表对象与行对像,同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select("");
// typeid=="1"时导出为EXCEL格式文件;typeid=="2"时导出为XML格式文件
if (typeid == "1")
{
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count - 1; i++)
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach (DataRow row in myRow)
{
//在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n
for (i = 0; i < row.Table.Columns.Count - 1; i++)
ls_item += row[i].ToString() + "\t";
ls_item += row[i].ToString() + "\n";
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item = "";
}
}
else
{
if (typeid == "2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
}
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
{
if (myDataTable.Rows.Count == 0)
{
return false;
}
Random r = new Random();
string saveFileName = "C:\\Documents and Settings\\_0052\\Desktop\\" + DateTime.Now.ToString("yyyy-MM-dd") + "" + r.Next(1000, 9999) + ".xls";
if (saveFileName.IndexOf(":") < 0)
{
return false;//被点了取消
} Excel.Application xlApp = new Excel.Application();
object missing = System.Reflection.Missing.Value; if (xlApp == null)
{
Response.Write("无法创建Excel对象,可能您的机子未安装Excel");
return false;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1 //写入字段
for (int i = 0; i < myDataTable.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = myDataTable.Columns[i].ColumnName;
} //写入数值
for (int j = 0; j < myDataTable.Rows.Count; j++)
{
for (int i = 0; i < myDataTable.Columns.Count; i++)
{
worksheet.Cells[j + 2, i + 1] = myDataTable.Rows[j][i];
} }
worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing); workbook.Close(missing, missing, missing);
xlApp.Quit();
return true;
}