/// <summary> /// 功能:实现Excel应用程序的打开 /// </summary> /// <param name="TemplateFilePath">模板文件物理路径</param> public void Open(string TemplateFilePath) { //打开对象 m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objExcel.Visible = false; m_objExcel.DisplayAlerts = false; if (m_objExcel.Version != "11.0") { MessageBox.Show("您的Excel 版本不是11.0 (Office 2003),操作可能会出现问题。"); m_objExcel.Quit(); return; } m_objBooks = (Workbooks)m_objExcel.Workbooks; if (TemplateFilePath.Equals(String.Empty)) { m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt)); } else { m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); } m_objSheets = (Sheets)m_objBook.Worksheets; m_objSheet = (_Worksheet)(m_objSheets.get_Item(1)); m_objExcel.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose); }
public void newWorkbook(string excelTemplate, string fileName) { //以excelTemplate为模板新建文件fileName //excelApplication. xlsWb = xlsWbs.Add(excelTemplate); SaveFileName = ""; SaveAsExcel(false); }
public Base() { App = new Application(); App.Visible = true; App.DisplayAlerts = false; Books = App.Workbooks; Book = Books.Add(Missing.Value); Sheet = Book.ActiveSheet; }
public void Open(string FileName)//打开一个Excel文件 { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); //wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing); //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); mFilename = FileName; }
private void ExportExcel(DataTable dt) { if (dt == null) { return; } Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { return; } System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; Excel.Range range; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = transColumnName(dt.Columns[i].ColumnName); range = (Excel.Range)worksheet.Cells[1, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i]; } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; } xlApp.Visible = true; }
/// <summary> /// 新建一个Excel实例 /// </summary> /// <param name="strTitle">Excel表头上的文字</param> public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle) { m_objExcel = new Excel.ApplicationClass(); m_objExcel.Visible = true; m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; if (intSheetTotalSize <= 3) { if (this.dbTotalSize <= this.dbSheetSize) { this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle); return; } else if (this.dbTotalSize <= this.dbSheetSize * 2) { this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle); this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle); return; } else { this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle); this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle); this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle); return; } } for (int i = 3; i < intSheetTotalSize; i++) { m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt); } ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle); for (int i = 2; i <= m_objSheets.Count; i++) { ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle); } }
static void Main(string[] args) { Console.WriteLine("Interop Performance Test - 10.000 Cells."); /* * start excel and disable messageboxes and screen updating */ Excel.Application excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; excelApplication.ScreenUpdating = false; //excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate); /* * create new empty worksheet */ Excel.Workbooks books = excelApplication.Workbooks; Excel.Workbook book = books.Add(Missing.Value); Excel.Sheets sheets = book.Worksheets; Excel.Worksheet sheet = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); /* * do the test * we collect all references and release after time recording * the 2 latebind libs release the references at end automaticly and we want a fair test */ List <object> comReferenesList = new List <object>(); DateTime timeStart = DateTime.Now; for (int i = 1; i <= 10000; i++) { string rangeAdress = "$A" + i.ToString(); Range cellRange = (Range)sheet.Range[rangeAdress]; cellRange.Value = "value"; cellRange.Font.Name = "Verdana"; cellRange.NumberFormat = "@"; cellRange.WrapText = false; Comment sampleComment = cellRange.AddComment("Sample Comment"); comReferenesList.Add(cellRange); comReferenesList.Add(sampleComment); } DateTime timeEnd = DateTime.Now; TimeSpan timeElapsed = timeEnd - timeStart; foreach (var item in comReferenesList) { Marshal.ReleaseComObject(item); } /* * display for user */ string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine); Console.WriteLine(outputConsole); Console.Read(); /* * write result in logfile */ string logFile = Path.Combine(Environment.CurrentDirectory, "Interop.log"); string logFileAppend = timeElapsed.ToString() + Environment.NewLine; File.AppendAllText(logFile, logFileAppend, Encoding.UTF8); /* * release & quit */ Marshal.ReleaseComObject(sheet); Marshal.ReleaseComObject(sheets); Marshal.ReleaseComObject(book); Marshal.ReleaseComObject(books); excelApplication.Quit(); Marshal.ReleaseComObject(excelApplication); }
/// <summary> /// 将datatable导出到excel /// </summary> /// <param name="dt"></param> /// <param name="saveasfilepath"></param> /// <param name="WorkbookName"></param> public void ExportExcel(System.Data.DataTable dt, string saveasfilepath, string WorkbookName) { if (dt == null) { return; } string saveFileName = saveasfilepath; // "d:\\333.xlsx"; bool fileSaved = false; Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { throw new Exception("无法创建Excel对象,可能您的机子未安装Excel"); } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 if (!string.IsNullOrEmpty(WorkbookName)) { Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item(1); ws.Name = WorkbookName; } Excel.Range range; //string oldCaption = DateTime.Today.ToString("yy-MM-dd"); long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; // worksheet.Cells[1, 1] = DateTime.Today.ToString("yy-MM-dd"); //写入字段 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; range = (Excel.Range)worksheet.Cells[1, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i]; } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; //this.lbl_process.Text = "正在导出数据[" + percent.ToString("0.00") + "%]..."; //这里可以自己做一个label用来显示进度. System.Windows.Forms.Application.DoEvents(); } //this.lbl_process.Visible = false; range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]); range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } if (saveFileName != "") { try { //xlApp.Visible = true; //workbook.PrintPreview(false); workbook.Close(true, saveFileName, Type.Missing); workbook = null; //SaveAs方法会截断超过255个字符的字符串 //workbook.Saved = true; //workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel7, Type.Missing, // Type.Missing, Type.Missing, Type.Missing, // Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, // Type.Missing, Type.Missing, Type.Missing); //fileSaved = true; } catch (Exception ex) { xlApp.Quit(); GC.Collect(); fileSaved = false; throw new Exception("导出文件时出错,文件可能正被打开!\n" + ex.Message); } } else { fileSaved = false; } xlApp.Quit(); xlApp = null; GC.Collect();//强行销毁 if (fileSaved && File.Exists(saveFileName)) { //System.Diagnostics.Process.Start(saveFileName); } }
/// <summary> /// 把DataTable中的数据导出到Excel /// </summary> /// <param name="dt"></param> public static void DataTableToExcel(System.Data.DataTable srcDt, string savename) { System.Data.DataTable dt = new System.Data.DataTable(); dt = srcDt; if (dt == null) { return; } string saveFileName = ""; bool fileSaved = false; /*SaveFileDialog saveDialog = new SaveFileDialog(); * saveDialog.DefaultExt = "xlsx"; * saveDialog.Filter = "Excel文件|*.xlsx"; * saveDialog.FileName = "导出文件"; * saveDialog.ShowDialog(); * saveFileName = saveDialog.FileName; * if (saveFileName.IndexOf(":") < 0) return; //被点了取消 */ Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth = 40; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight = 50; //worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText = true; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); for (int i = 2; i < 12; i++) { if (dt.Rows[i - 2][3].ToString() == "E") { worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3; } if (dt.Rows[i - 2][3].ToString() == "W") { worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6; } } //写入字段 worksheet.Cells[1, 1] = "参数名称"; worksheet.Cells[1, 2] = "参数值"; worksheet.Cells[1, 3] = "推荐值"; worksheet.Cells[1, 4] = "报警值"; worksheet.Cells[1, 5] = "详情"; worksheet.Cells[1, 6] = "参数类型"; worksheet.Cells[1, 7] = "重要级别"; worksheet.Cells[1, 8] = "历史变化"; //写入数值 for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i]; } System.Windows.Forms.Application.DoEvents(); } //worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 /*string md5 = ""; * for (int r = 0; r < 5; r++) * { * for (int i = 0; i < 8; i++) * { * md5 += dt.Rows[r][i].ToString(); * worksheet.Cells[2, 9] = md5; * } * * } */ //worksheet.Cells.Width = 39; //if (saveFileName != "") //{ try { workbook.Saved = true; saveFileName = savename; workbook.SaveCopyAs(saveFileName); fileSaved = true; MessageBox.Show("导出完成!"); } catch (Exception ex) { fileSaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } //} //else //{ // fileSaved = false; //} xlApp.Quit(); GC.Collect();//强行销毁 if (fileSaved && System.IO.File.Exists(saveFileName)) { System.Diagnostics.Process.Start(saveFileName); //打开EXCEL } }
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; }
public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible) { _excelApplication = new Excel.Application(); _excelApplication.Visible = visible; _workBooks = (Excel.Workbooks)_excelApplication.Workbooks; _workBook = (Excel._Workbook)(_workBooks.Add(filePath)); _excelSheets = (Excel.Sheets)_workBook.Worksheets; _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex)); _excelSheet.Name = sheetName; }
public void OpenExcelApp(bool visible) { _excelApplication = new Excel.Application(); _excelApplication.Visible = visible; _workBooks = (Excel.Workbooks)_excelApplication.Workbooks; _workBook = (Excel._Workbook)(_workBooks.Add(_value)); _excelSheets = (Excel.Sheets)_workBook.Worksheets; _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1)); }
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>"); }
public void Create()//创建一个Excel对象 { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(true); }