//关闭一个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>");
}
