//关闭一个Excel对象,销毁对象 public void Close() { //wb.Save(); wb.Close(Type.Missing, Type.Missing, Type.Missing); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; GC.Collect(); }
public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title) { //文件存在时先删除文件后再进行下一步操作 FileInfo file = new FileInfo(strFullFilePath); if (file.Exists) { file.Delete(); } int rowIndex = 3; //开始写入数据的单元格行 int colIndex = 0; //开始写入数据的单元格列 System.Reflection.Missing miss = System.Reflection.Missing.Value; Excel.ApplicationClass mExcel = new Excel.ApplicationClass(); mExcel.Visible = false; Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks; Excel.Workbook mBook = (Excel.Workbook)(mBooks.Add(miss)); Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet; Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本 er.Value2 = title; try { foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格 { colIndex++; mSheet.Cells[3, colIndex] = col.ColumnName; } foreach (DataRow row in table.Rows) //同样方法处理数据 { rowIndex++; colIndex = 0; foreach (DataColumn col in table.Columns) { colIndex++; if (colIndex == 2) { mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失 } else { mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } } } //保存工作已写入数据的工作表 mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss); // return true; } catch (Exception ee) { throw new Exception(ee.Message); } finally //finally中的代码主要用来释放内存和中止进程() { mBook.Close(false, miss, miss); mBooks.Close(); mExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(er); System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel); GC.Collect(); } //return false; }
private void ExportUpEmployee() { string strSql = @"select GetOrgName(GetStationOrgID(b.org_Id)) UnitName, getworkshopname(b.org_id) WorkShopName, case when c.level_num=4 then c.Short_Name else null end WorkGroupName, Employee_Name, Identity_CardNo, e.Train_Plan_Name, d.Class_Name,f.Post_Name from zj_train_plan_employee t inner join Employee b on t.Employee_ID=b.Employee_ID inner join Org c on b.Org_ID=c.Org_ID inner join ZJ_Train_Plan_Post_Class d on t.Train_Plan_Post_Class_ID=d.Train_Plan_Post_Class_ID inner join ZJ_Train_Plan e on d.Train_Plan_ID=e.Train_Plan_ID inner join Post f on b.Post_ID = f.Post_ID where t.Train_Plan_Post_Class_Org_ID=" + Request.QueryString.Get("classOrgID"); OracleAccess db = new OracleAccess(); DataSet ds = db.RunSqlDataSet(strSql); if (ds.Tables[0].Rows.Count == 0) { Response.Write("<script>alert('没有已上报的员工信息!');window.close();</script>"); return; } string templateFileName = Server.MapPath("/RailExamBao/RandomExam/ProgressBar.htm"); StreamReader reader = new StreamReader(@templateFileName, System.Text.Encoding.GetEncoding("gb2312")); string html = reader.ReadToEnd(); reader.Close(); Response.Write(html); Response.Flush(); System.Threading.Thread.Sleep(200); string jsBlock; Excel.Application objApp = new Excel.ApplicationClass(); Excel.Workbooks objbooks = objApp.Workbooks; Excel.Workbook objbook = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet objSheet = (Excel.Worksheet)objbook.Worksheets[1]; //取得sheet1 string filename = ""; string strName = ""; string strFileName = ""; try { int count = ds.Tables[0].Rows.Count; strName = "Excel"; strFileName = ds.Tables[0].Rows[0]["UnitName"].ToString() + ds.Tables[0].Rows[0]["Class_Name"].ToString() + "上报人员"; filename = Server.MapPath("/RailExamBao/Excel/" + strName + ".xls"); objSheet.Cells.Font.Size = 10; objSheet.Cells.Font.Name = "宋体"; int colIndex = 1; objSheet.Cells[1, colIndex] = "序号"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[1, colIndex] = "站段"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[1, colIndex] = "车间"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[1, colIndex] = "班组"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[1, colIndex] = "姓名"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[1, colIndex] = "身份证号"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[1, colIndex] = "职名"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[1, colIndex] = "上报培训计划名"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[1, colIndex] = "上报计划培训班名"; ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; System.Threading.Thread.Sleep(10); jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(1 * 100) / (double)count + 1).ToString("0.00") + "'); </script>"; Response.Write(jsBlock); Response.Flush(); int rowIndex = 2; foreach (DataRow dr in ds.Tables[0].Rows) { colIndex = 1; objSheet.Cells[rowIndex, colIndex] = rowIndex - 1; ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[rowIndex, colIndex] = dr["UnitName"].ToString(); ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[rowIndex, colIndex] = dr["WorkShopName"].ToString(); ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[rowIndex, colIndex] = dr["WorkGroupName"].ToString(); ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[rowIndex, colIndex] = dr["Employee_Name"].ToString(); ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[rowIndex, colIndex] = "'" + dr["Identity_CardNo"]; ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[rowIndex, colIndex] = dr["Post_Name"].ToString(); ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[rowIndex, colIndex] = dr["Train_Plan_Name"].ToString(); ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; colIndex++; objSheet.Cells[rowIndex, colIndex] = dr["Class_Name"].ToString(); ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter; System.Threading.Thread.Sleep(10); jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(rowIndex * 100) / (double)count + 1).ToString("0.00") + "'); </script>"; Response.Write(jsBlock); Response.Flush(); rowIndex++; } objSheet.Cells.Columns.AutoFit(); objApp.Visible = false; objbook.Saved = true; objbook.SaveCopyAs(filename); // 处理完成 jsBlock = "<script>SetCompleted('处理完成。'); </script>"; Response.Write(jsBlock); Response.Flush(); } catch (Exception ex) { throw ex; } finally { objbook.Close(Type.Missing, filename, Type.Missing); objbooks.Close(); objApp.Application.Workbooks.Close(); objApp.Application.Quit(); objApp.Quit(); GC.Collect(); } Response.Write("<script>top.returnValue='" + strName + "|" + strFileName + "';window.close();</script>"); }