/// <summary> /// 读取Excel文件 /// </summary> /// <param name="pPath"></param> /// <returns></returns> private string GetExcelSheetName(string pPath) { //打开一个Excel应用 _excelApp = new Excel.Application(); if (_excelApp == null) { throw new Exception("打开Excel应用时发生错误!"); } _books = _excelApp.Workbooks; //打开一个现有的工作薄 _book = _books.Add(pPath); _sheets = _book.Sheets; //选择第一个Sheet页 _sheet = (Excel._Worksheet)_sheets.get_Item(1); string sheetName = _sheet.Name; ReleaseCOM(_sheet); ReleaseCOM(_sheets); ReleaseCOM(_book); ReleaseCOM(_books); _excelApp.Quit(); ReleaseCOM(_excelApp); return sheetName; }
/// <summary> /// 读取Excel文件 /// </summary> /// <param name="pPath"></param> /// <returns></returns> private string GetExcelSheetName(string pPath) { //打开一个Excel应用 _excelApp = new Excel.Application(); if (_excelApp == null) { throw new Exception("打开Excel应用时发生错误!"); } _books = _excelApp.Workbooks; //打开一个现有的工作薄 _book = _books.Add(pPath); _sheets = _book.Sheets; //选择第一个Sheet页 _sheet = (Excel._Worksheet)_sheets.get_Item(1); string sheetName = _sheet.Name; ReleaseCOM(_sheet); ReleaseCOM(_sheets); ReleaseCOM(_book); ReleaseCOM(_books); _excelApp.Quit(); ReleaseCOM(_excelApp); return(sheetName); }
/// <summary> /// Create Excel application parameters instances /// </summary> private void CreateExcelRef() { _excelApp = new Excel.Application(); _books = (Excel.Workbooks)_excelApp.Workbooks; _book = (Excel._Workbook)(_books.Add(_optionalValue)); _sheets = (Excel.Sheets)_book.Worksheets; _sheet = (Excel._Worksheet)(_sheets.get_Item(1)); }
public void CreateExcelFile() { UserControl(false); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel.Workbook)(m_objBooks.Add(miss)); //m_objSheets = (Excel.Worksheets)m_objBook.Sheets; m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet; }
protected virtual void ActivateExcel() { _excelApplication = new Excel.Application(); _workBooks = (Excel.Workbooks)_excelApplication.Workbooks; _workBook = (Excel._Workbook)(_workBooks.Add(_value)); _excelSheets = (Excel.Sheets)_workBook.Worksheets; _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1)); }
/// <summary> /// 返回模板文件中的指定的Sheet表 /// </summary> /// <param name="templateFilePath">模板文件路径</param> /// <param name="sheetIndex">Sheet索引</param> /// <param name="setDataToSheetHandler">赋值数据给Excel</param> public static Excel.Worksheet CreateXlsSheet(string templateFilePath, int sheetIndex, ref Excel.Application xlsApp, ref Excel.Workbook xlsWorkbook, bool isvisible = false) { xlsApp = new Excel.Application(); Excel.Workbooks workbook = xlsApp.Workbooks; xlsWorkbook = workbook.Add(templateFilePath); Excel.Worksheet xst = xlsWorkbook.Worksheets[sheetIndex] as Excel.Worksheet; xlsApp.Visible = isvisible; xlsApp.DisplayAlerts = false; return(xst); }
public void ExcelOp(string szPath) { string ExcelFilePath = szPath.Trim(); //set columns Dictionary <string, string> dic = new Dictionary <string, string>(); dic.Add("物品男", "A");// dic.Add("id男", "B"); Excel.Application excel = new Excel.Application(); Excel.Workbooks wb = excel.Workbooks; excel.Visible = false;//设置调用引用的 Excel文件是否可见 excel.Application.DisplayAlerts = false; //wb = excel.Workbooks.Open(ExcelFilePath); Excel.Workbook rWbk = wb.Add(szPath); Excel.Worksheet ws = (Excel.Worksheet)rWbk.Worksheets[1]; //索引从1开始 //(Excel.Worksheet)wb.Worksheets["SheetName"]; int rowCount = 0; //有效行,索引从1开始 try { rowCount = ws.UsedRange.Rows.Count;//赋值有效行 int columns = ws.UsedRange.Columns.Count; string ordernum = string.Empty; string count = string.Empty; //循环行 for (int i = 1; i <= 10; i++)// { if (ws.Rows[i] != null) { for (int j = 1; j <= 10; j++) { ws.Cells[i, j].value = "aaaa"; string szPos = "$"; szPos += i; string szCol = "$"; szCol += j; string cellStr = szPos + ":" + szCol; string szId = ws.Cells.get_Address(i, j); //ws.get_Range(cellStr, cellStr).Interior.Color = Color.FromArgb(0,111, 111, 255); // ((Range)ws.Cells[i, j]).Interior.Color = Color.FromArgb(0, 255, 255, 255); ((Range)ws.Cells[i, j]).Interior.ColorIndex = 0; } } } } catch (Exception ex) { } finally { ExcelClose(szPath, excel, rWbk); } }
//导出Excel的方法 public void getListView(ListView lsv, string title) { Excel.Application app = null; try { app = new Excel.Application(); app.Visible = true; object obj = System.Reflection.Missing.Value; Excel.Workbooks wb = app.Workbooks; Excel._Workbook iwk = wb.Add(obj); Excel._Worksheet sheet = (Excel._Worksheet)(iwk.ActiveSheet); int colCount = lsv.Columns.Count; int rowCount = lsv.Items.Count; app.Caption = title; string[,] dataArray = new string[rowCount + 1, colCount]; //添加列头标题 for (int i = 0; i < colCount; i++) { dataArray[0, i] = lsv.Columns[i].Text.ToString(); } //添加每一行的数据 for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[i + 1, j] = lsv.Items[i].SubItems[j].Text.ToString(); } } sheet.get_Range("A1", sheet.Cells[rowCount + 1, colCount]).Value2 = dataArray; } catch (Exception e) { MessageBox.Show(e.Message); } finally { GC.Collect(); app.Quit(); } }
public static bool comReaderNSaveData(string szPath, SaveData rSaveFunc, ref System.Windows.Forms.RichTextBox rInfoOutput) { string szExcelFilePath = szPath.Trim(); Excel.Application excel = new Excel.Application(); Excel.Workbooks wb = excel.Workbooks; excel.Visible = false;//设置调用引用的 Excel文件是否可见 excel.Application.DisplayAlerts = false; //wb = excel.Workbooks.Open(ExcelFilePath); Excel.Workbook rWbk = wb.Add(szExcelFilePath); Excel.Sheets rWorkSheets = rWbk.Worksheets; try {//每个工作表都查 索引从1开始 for (int index = 1; index <= rWorkSheets.Count; ++index) { Excel.Worksheet ws = (Excel.Worksheet)rWorkSheets[index]; int rowCount = 0; //有效行,索引从1开始 rowCount = ws.UsedRange.Rows.Count; //赋值有效行 string ordernum = string.Empty; string count = string.Empty; //循环行 for (int i = 1; i <= rowCount; i++) // { //将行中数据交给 代理处理 string szIDwithPre = ws.Cells[i, 1].Value.ToString(); string szName = ws.Cells[i, 2].Value.ToString(); rSaveFunc(szIDwithPre, szName); } } } catch (Exception ex) { rInfoOutput.SelectionColor = Color.Red; rInfoOutput.AppendText(ex.ToString()); } finally { /*ExcelClose(szPath, excel, rWbk);*/ } return(true); }
/// <summary> /// Spread와 Chart의 내용을 엑셀로 Export함.시작위치는 내부에서 자동 계산. /// </summary> /// <param name="oSpread"> Spread컨트롤 명 </param> /// <param name="oChartFx"> MSChart컨트롤 명 명 </param> /// <param name="sFileTitle"> 화면 명 </param> /// <param name="sHeadL"> 엑셀 머릿말(왼쪽) </param> /// <param name="sHeadR"> 엑셀 머릿말(오른쪽) </param> /// <param name="autofit">오토피트(자동너비계산)</param> public void subMakeMsChartExcel(FpSpread oSpread, System.Windows.Forms.DataVisualization.Charting.Chart oMSChart, string sFileTitle, string sHeadL, string sHeadR, bool bAutoFit) { DialogResult dlg; bool IsMerge = true; bool bResult = true; int iSCol = 1; int iSRow = 0; int iMergeColSize = 0; int iChartRow = 0; int iTmp = 0; int jTmp = 0; try { if (oSpread.ActiveSheet.Rows.Count < 1) { MessageBox.Show("저장할 Data가 없습니다.", "Excel"); } if (oSpread.ActiveSheet.Rows.Count > 600) { dlg = MessageBox.Show("데이타 건수가 많아 셀병합 작업시 속도가 느려집니다. 셀병합후 엑셀로 저장하시겠습니까?", "Excel Export", MessageBoxButtons.YesNo); if (dlg == DialogResult.No) { IsMerge = false; } } int iECol = 0; int iERow = 0; int iGridHeadCnt = 0; if (sFileTitle == null) { sFileTitle = ""; } if (sHeadL == null) { sHeadL = ""; } if (sHeadR == null) { sHeadR = ""; } xlApp = new Excel.Application(); xlBooks = xlApp.Workbooks; xlBook = xlBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); xlSheet = (Excel.Worksheet)xlBook.ActiveSheet; xlApp.Visible = false; //xlApp.Visible = true; xlApp.Cells.ClearContents(); xlApp.Cells.ClearFormats(); iGridHeadCnt = oSpread.ActiveSheet.ColumnHeaderRowCount; DataTable dt = null; String strSql = null; strSql = " SELECT USER_DESC FROM RWEBUSRDEF WHERE USER_ID = '" + GlobalVariable.gsUserID + "' "; dt = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", strSql); if (sHeadR == "") { sHeadR = "사 용 자 : " + dt.Rows[0][0].ToString() + " (" + GlobalVariable.gsUserID + ")"; } else { sHeadR = sHeadR + "^사 용 자 : " + dt.Rows[0][0].ToString() + " (" + GlobalVariable.gsUserID + ")"; } if (oMSChart == null) { //iSRow값을 내부에서 계산하게 수정. if (sHeadL != "") { iTmp = sHeadL.Split('^').Length; } if (sHeadR != "") { jTmp = sHeadR.Split('^').Length; } iSRow = (iTmp > jTmp ? iTmp : jTmp) + 5 + 1; //페이지 여백설정 subPageSetup(iSRow + iGridHeadCnt - 1, true); } else { //iSRow값을 내부에서 계산하게 수정. if (sHeadL != "") { iTmp = sHeadL.Split('^').Length; } if (sHeadR != "") { jTmp = sHeadR.Split('^').Length; } // 머릿말 Row수 + 타이틀이 차지하는 Row수(5) iChartRow = (iTmp > jTmp ? iTmp : jTmp) + 5 + 1; //Chart복사 iSRow = CopyMSChart(oMSChart, iChartRow); //페이지 여백설정 subPageSetup(iSRow + iGridHeadCnt - 1, false); } //Header항목이 2라인일 경우 iECol = CopySpView(oSpread, iGridHeadCnt, iSCol, iSRow, ref iMergeColSize); //Excel에서 마지막 Col의 위치값 ColSize = iECol; iERow = oSpread.ActiveSheet.Rows.Count + iSRow + iGridHeadCnt - 1; //Excel에서 마지막 Row의 위치값 //Header의 라인 작성(Head부분의 색적용, Data라인 작성) HeaderLine(iGridHeadCnt, iSCol, iSRow, iECol, iERow, iMergeColSize); ////각각의 머리글을 작성 setHeader(iSCol, iECol, sHeadL, sHeadR); //파일의 타이틀을 작성 setTitle(iSCol, iECol, sFileTitle); //Data부분 셀 Merge (600건 넘어가면 속도 작살임..) if (IsMerge == true) { string[] tmpData = null; int iRepeatRow = 0; if (oSpread.ActiveSheet.Tag != null) { tmpData = oSpread.ActiveSheet.Tag.ToString().Split('^'); iRepeatRow = Convert.ToInt16(tmpData[1].ToString()) - 1; } DataMerge(iSCol, iSRow + iGridHeadCnt, iMrgECol, iERow, iMergeColSize, iRepeatRow); } xlSheet.get_Range(xlSheet.Cells[iSRow, iSCol], xlSheet.Cells[iSRow, iSCol]).Select(); oSpread.ActiveSheet.SetActiveCell(iSRow, iSCol); if (bAutoFit == true) { //왼쪽 머릿말과 오른쪽 머리말 부분을 뺀 나머지만 AutoFit을 한다. if (sHeadL == "" && sHeadR == "") { xlSheet.get_Range(xlSheet.Cells[iSRow, 1], xlSheet.Cells[iSRow, iECol]).EntireColumn.AutoFit(); } else if (sHeadL == "" && sHeadR != "") { xlSheet.get_Range(xlSheet.Cells[iSRow, 1], xlSheet.Cells[iSRow, iECol - 1]).EntireColumn.AutoFit(); } else if (sHeadL != "" && sHeadR == "") { xlSheet.get_Range(xlSheet.Cells[iSRow, 2], xlSheet.Cells[iSRow, iECol]).EntireColumn.AutoFit(); } else { xlSheet.get_Range(xlSheet.Cells[iSRow, 2], xlSheet.Cells[iSRow, iECol - 1]).EntireColumn.AutoFit(); } } bResult = true; } catch (Exception ex) { String errorMessage = ""; errorMessage = String.Concat(errorMessage, ex.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, ex.Source); CmnFunction.ShowMsgBox(errorMessage, "Error [" + ex.Source + "]", MessageBoxButtons.OK, 1); bResult = false; } finally { // 사용자에게 저장 여부를 묻는다. xlBook.Saved = false; System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks); if (bResult == false) { //Make Excel application close. xlApp.DisplayAlerts = false; //xlBook.Saved = true; xlBooks.Close(); // ***** 이 함수를 호출 하지 않으면 작업프로세스에 EXCEL.EXE가 죽지 않고 계속 남아 있음 ***** xlApp.Quit(); } else { // 모든 엑셀의 경고메시지가 나타나도록 한다. //xlApp.DisplayAlerts = true; //Make Excel visible and give the user control. xlApp.Visible = true; xlApp.UserControl = true; } System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); GC.Collect(); System.Windows.Forms.Cursor.Current = Cursors.Default; } } //public void subMakeExcel(FpSpread oSpread, Chart oChartFx, string sFileTitle, string sHeadL, string sHeadR)
public void ExportToExcel(System.Data.DataTable dt) { if (dt == null) { 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 Excel.Range range = null; 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] = dt.Columns[i].ColumnName; range = (Excel.Range)worksheet.Cells[1, i + 1]; //range.Interior.ColorIndex = 15;//背景颜色 range.Font.Bold = true; //粗体 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中 //加边框 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //range.ColumnWidth = 4.63;//设置列宽 //range.EntireColumn.AutoFit();//自动调整列宽 //r1.EntireRow.AutoFit();//自动调整行高 } //写入内容 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]; range = (Excel.Range)worksheet.Cells[r + 2, i + 1]; range.Font.Size = 9;//字体大小 //加边框 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); range.EntireColumn.AutoFit();//自动调整列宽 } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } try { workbook.Saved = true; workbook.SaveCopyAs("C:\\datatable" + "" + ".xls"); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } xlApp.Quit(); GC.Collect();//强行销毁 //这个是从服务器中下载文件,(请参考我另外一个文章) //参考网址http://www.cnblogs.com/ghostljj/archive/2007/01/24/629293.html //BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.xls" // , System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name + ".xls", 1024000); }
/// <summary> /// 将DataTable数据导出到Excel表 /// </summary> /// <param name="tmpDataTable">要导出的DataTable</param> /// <param name="strFileName">Excel的保存路径及名称</param> public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName) { if (tmpDataTable == null) { return; } long rowNum = tmpDataTable.Rows.Count; //行数 int columnNum = tmpDataTable.Columns.Count; //列数 Excel.Application m_xlApp = new Excel.Application(); m_xlApp.DisplayAlerts = true; //不显示更改提示 m_xlApp.Visible = true; //false;// Excel.Workbooks workbooks = m_xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 //worksheet.SetBackgroundPicture("d:\\26.jpg"); try { //查询条件 string rq = "日期:" + this.dateTimePicker1.Value.ToShortDateString() + " 到 " + this.dateTimePicker2.Value.ToShortDateString(); string ks = ""; string swhere = rq + ks; int SumColCount = tmpDataTable.Columns.Count; //for (int j = 0; j < tmpDataTable.Columns.Count; j++) //{ // if (this.dataGridView1.Columns[j].Visible) // { // SumColCount = SumColCount + 1; // m_xlApp.Cells[5, SumColCount] = "" + tmpDataTable.Columns[j].Caption; // } //} //报表名称 string ss = TrasenFrame.Classes.Constant.HospitalName + "已打印检验单表"; m_xlApp.Cells[1, 1] = ss; m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Font.Bold = true; m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Select(); m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 m_xlApp.Cells[3, 1] = swhere.Trim(); m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[3, SumColCount]).Font.Size = 10; m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[3, SumColCount]).Select(); m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; if (rowNum > 65536) //单张Excel表格最大行数 { long pageRows = 65535; //定义每页显示的行数,行数必须小于65536 int scount = (int)(rowNum / pageRows); //导出数据生成的表单数 if (scount * pageRows < rowNum) //当总行数不被pageRows整除时,经过四舍五入可能页数不准 { scount = scount + 1; } for (int sc = 1; sc <= scount; sc++) { if (sc > 1) { object missing = System.Reflection.Missing.Value; worksheet = (Excel.Worksheet)workbook.Worksheets.Add( missing, missing, missing, missing); //添加一个sheet } else { worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1 } string[,] datas = new string[pageRows + 1, columnNum]; for (int i = 0; i < columnNum; i++) //写入字段 { datas[0, i] = tmpDataTable.Columns[i].Caption; //表头信息 } Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, columnNum]); range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Bold = true; range.Font.Size = 9; int init = int.Parse(((sc - 1) * pageRows).ToString()); int r = 0; int index = 0; int result; if (pageRows * sc >= rowNum) { result = (int)rowNum; } else { result = int.Parse((pageRows * sc).ToString()); } for (r = init; r < result; r++) { index = index + 1; for (int i = 0; i < columnNum; i++) { object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()]; datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } System.Windows.Forms.Application.DoEvents(); //添加进度条 } Excel.Range fchR = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[index + 5, columnNum]); fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; //Sheet表最大化 range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[index + 5, columnNum]); //range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Size = 9; range.RowHeight = 14.25; range.Borders.LineStyle = 1; range.HorizontalAlignment = 1; } } else { string[,] datas = new string[rowNum + 1, columnNum]; for (int i = 0; i < columnNum; i++) //写入字段 { datas[0, i] = tmpDataTable.Columns[i].Caption; } Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, columnNum]); range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Bold = true; range.Font.Size = 9; int r = 0; for (r = 0; r < rowNum; r++) { for (int i = 0; i < columnNum; i++) { object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()]; datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } System.Windows.Forms.Application.DoEvents(); //添加进度条 } Excel.Range fchR = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowNum + 5, columnNum]); fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowNum + 5, columnNum]); //range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Size = 9; range.RowHeight = 14.25; range.Borders.LineStyle = 1; range.HorizontalAlignment = 1; } //workbook.Saved = true; // workbook.SaveCopyAs(strFileName); } catch (Exception ex) { MessageBox.Show("导出异常:" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { if (workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if (m_xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp); m_xlApp = null; //xlApp.Quit(); } GC.Collect(); //EndReport(); } }
private void OutPut() { 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 Excel.Range range; string filename = ""; try { //生成.xls文件完整路径名 filename = Server.MapPath("/RailExamBao/Excel/CompuerServerCount.xls"); if (File.Exists(filename.ToString())) { File.Delete(filename.ToString()); } //将所得到的表的列名,赋值给单元格 objSheet.Cells[1, 1] = "站段名称"; objSheet.Cells[1, 2] = "服务器名称"; range = objSheet.get_Range(objSheet.Cells[1, 2], objSheet.Cells[1, 4]); range.Merge(0); objSheet.Cells[1, 5] = "使用人次"; objSheet.Cells[1, 6] = "其他单位使用人次"; objSheet.Cells[1, 7] = "其他单位使用天数"; DataSet ds = (DataSet)ViewState["Grid"]; int i = 0; //同样方法处理数据 foreach (DataRow dr in ds.Tables[0].Rows) { objSheet.Cells[2 + i, 1] = dr["Short_Name"].ToString(); objSheet.Cells[2 + i, 2] = dr["Computer_Server_Name"].ToString(); range = objSheet.get_Range(objSheet.Cells[2 + i, 2], objSheet.Cells[2 + i, 4]); range.Merge(0); objSheet.Cells[2 + i, 5] = dr["使用人次"].ToString(); objSheet.Cells[2 + i, 6] = dr["其他单位使用人次"].ToString(); objSheet.Cells[2 + i, 7] = dr["其他单位使用天数"].ToString(); i++; } //不可见,即后台处理 objApp.Visible = false; objbook.Saved = true; objbook.SaveCopyAs(filename); } catch { SessionSet.PageMessage = "系统错误,导出Excel文件失败!"; } finally { objbook.Close(Type.Missing, filename, Type.Missing); objbooks.Close(); objApp.Application.Workbooks.Close(); objApp.Application.Quit(); objApp.Quit(); GC.Collect(); } }
/// <summary> /// 创建一个Excel程序实例 /// </summary> private void CreateExcelRef() { _excelApp = new Excel.Application(); _books = (Excel.Workbooks)_excelApp.Workbooks; _book = (Excel._Workbook)(_books.Add(_optionalValue)); _sheets = (Excel.Sheets)_book.Worksheets; _sheet = (Excel._Worksheet)(_sheets.get_Item(1)); }
// public void WriteCardInfoExcel(DataSet dsCardInfo,string savePath) // { // try // { // System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value; // // object[,] objData; // // if ( dsCardInfo.Tables[0] != null ) // { // objData = new object[dsCardInfo.Tables[0].Rows.Count+1,1]; // objData[0,0] = "导出的卡号"; // // for( int i=1; i<=dsCardInfo.Tables[0].Rows.Count; i++ ) // objData[i,0] = dsCardInfo.Tables[0].Rows[i-1]["info_stuCardNumber"]; // // m_objExcel = new Excel.Application(); // m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; // m_objBook = (Excel._Workbook)m_objBooks.Add(true); // // m_objSheets = (Excel.Sheets)m_objBook.Sheets; // m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); // // m_objRange = m_objSheet.get_Range("A1",m_objOpt); // m_objRange = m_objRange.get_Resize(dsCardInfo.Tables[0].Rows.Count+1,1); // m_objRange.Value = objData; // // m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, // m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, // m_objOpt, m_objOpt, m_objOpt, m_objOpt); // m_objBook.Close(false, m_objOpt, m_objOpt); // m_objExcel.Quit(); // // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); // } // } // catch(Exception ex) // { // Util.WriteLog(ex.Message,Util.EXCEPTION_LOG_TITLE); // } // finally // { // m_objFont = null; // m_objRange = null; // m_objSheet = null; // m_objSheets = null; // m_objBook = null; // m_objBooks = null; // m_objExcel = null; // // GC.Collect(); // } // // } public void WriteCardInfoExcel(DataSet dsCardInfo,string savePath) { try { m_objExcel = new Excel.Application(); m_objExcel.DisplayAlerts = false; m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Add(true); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); object[,] objData = null; int index = 0; if (dsCardInfo.Tables[0].Rows.Count > 0) { objData = new object[dsCardInfo.Tables[0].Rows.Count + 1, dsCardInfo.Tables[0].Columns.Count + 2]; objData[0,0] = "年级"; objData[0,1] = "班级"; objData[0,2] = "学号"; objData[0,3] = "卡号"; objData[0,4] = "操作"; objData[0,5] = "统计"; for (int row = 0; row < dsCardInfo.Tables[0].Rows.Count; row++) { objData[row + 1, 0] = dsCardInfo.Tables[0].Rows[row][0]; objData[row + 1, 1] = dsCardInfo.Tables[0].Rows[row][1]; objData[row + 1, 2] = dsCardInfo.Tables[0].Rows[row][2]; objData[row + 1, 3] = dsCardInfo.Tables[0].Rows[row][3]; } foreach(DataRow row in dsCardInfo.Tables[2].Rows) { objData[index + 1, 5] = row[1]; index += Convert.ToInt32(row[1]); } m_objRange = m_objSheet.get_Range("A1",m_objOpt); m_objRange = m_objRange.get_Resize(dsCardInfo.Tables[0].Rows.Count + 4,6); m_objRange.Value = objData; m_objRange.Font.Size = 8; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; RowMerge("A", dsCardInfo.Tables[1]); RowMerge("B", dsCardInfo.Tables[2]); RowMerge("C", dsCardInfo.Tables[3]); RowMerge("F", dsCardInfo.Tables[2]); m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()); m_objRange.Value = "空卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()); m_objRange.Value = GetCardEncryptedNumber() - Convert.ToInt32(dsCardInfo.Tables[4].Rows[0][0]) - Convert.ToInt32(dsCardInfo.Tables[5].Rows[0][0]); m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString()); m_objRange.Value = "学生卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString()); m_objRange.Value = dsCardInfo.Tables[4].Rows[0][0]; m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString()); m_objRange.Value = "教师卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString()); m_objRange.Value = dsCardInfo.Tables[5].Rows[0][0]; m_objRange.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "F" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()).Merge(m_objOpt); m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } } catch(Exception ex) { throw ex; } finally { if (m_objRange != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); m_objRange = null; } if (m_objSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); m_objSheet = null; } if (m_objSheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); m_objSheets = null; } if (m_objBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); m_objBook = null; } if (m_objBooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); m_objBooks = null; } if(m_objExcel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); m_objExcel = null; } GC.Collect(); KillProcess(); } }
public static void excelReadNCheck(string szPath, OperatorCheckFunc checkFunc, ref System.Windows.Forms.RichTextBox rInfoOutput) { string szExcelFilePath = szPath.Trim(); Excel.Application excel = new Excel.Application(); Excel.Workbooks wb = excel.Workbooks; excel.Visible = false;//设置调用引用的 Excel文件是否可见 excel.Application.DisplayAlerts = false; //wb = excel.Workbooks.Open(ExcelFilePath); Excel.Workbook rWbk = wb.Add(szExcelFilePath); Excel.Sheets rWorkSheets = rWbk.Worksheets; try {//每个工作表都查 索引从1开始 int[] rColNum = new int[(int)OperatorHead.HeadMax]; for (int index = 1; index <= rWorkSheets.Count; ++index) { Excel.Worksheet ws = (Excel.Worksheet)rWorkSheets[index]; int rowCount = 0; //有效行,索引从1开始 rowCount = ws.UsedRange.Rows.Count; //赋值有效行 bool bIsHead = false; bool bIsEnd = true; for (int i = 1; i <= rowCount; i++) // { //将行中数据交给 代理处理 string[] rUseFulContent = new string[(int)OperatorHead.HeadMax]; int nColCount = ws.UsedRange.Columns.Count; for (int nLoopCount = ws.UsedRange.Column; nLoopCount <= nColCount; ++nLoopCount) {//循环一行中的每一列 if (ws.Cells[i, nLoopCount].Value == null) { continue; } string szContent = ws.Cells[i, nLoopCount].Value.ToString().Trim(); bIsEnd = bIsEnd && szContent == ""; if (szContent == "") { continue; } if (checkIsHead(szContent)) {//代表当前行中存在表头 if (!bIsHead) { bIsHead = true; clearHeadindexArr(ref rColNum); } //记录行列标记 setHeadindex(szContent, nLoopCount, ref rColNum); } string szAddress = ws.Cells[i, nLoopCount].Address; } if (!bIsHead && headValidaion(rColNum[0], rColNum[1], rColNum[2], rColNum[3])) { if (bIsEnd) { clearHeadindexArr(ref rColNum); continue; } for (int nUsefulIndex = 0; nUsefulIndex < rColNum.Length; nUsefulIndex += 2) { int nColName = rColNum[nUsefulIndex]; int nColID = rColNum[nUsefulIndex + 1]; if (0 != nColID && 0 != nColName) { string szName; string szID; if (ws.Cells[i, nColName].Value == null) { szName = ""; } else { szName = ws.Cells[i, nColName].Value.ToString().Trim(); } if (ws.Cells[i, nColID].Value == null) { szID = ""; } else { szID = ws.Cells[i, nColID].Value.ToString().Trim(); } if ((szID == "" && szName == "")) {//都是空都不需要填写 break; } string szAddress = getAddressStr(ws.Cells[i, nColName].Address); if (checkFunc(ref szID, ref szName, nUsefulIndex > 0 ? false : true, szAddress, ref rInfoOutput)) { ws.Cells[i, nColName].Interior.ColorIndex = 0; ws.Cells[i, nColID].Interior.ColorIndex = 0; } else {//标黄 ws.Cells[i, nColName].Interior.Color = Color.FromArgb(255, 255, 0); ws.Cells[i, nColID].Interior.Color = Color.FromArgb(255, 255, 0); } } } } bIsHead = false; } } } catch (Exception ex) { rInfoOutput.SelectionColor = Color.Red; rInfoOutput.AppendText(ex.ToString() + '\n'); } finally { excelCoverClose(szPath, excel, rWbk); } }
/// <summary> /// 将datatable导出excel文件 /// </summary> /// <param name="dt">需要导出的datatable</param> /// <param name="AbosultedFilePath">导出文件的绝对路径</param> /// <returns></returns> public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath) { //检查数据表是否为空,如果为空,则退出 if (dt == null) { return(false); } //创建Excel应用程序对象,如果未创建成功则退出 Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { System.Web.HttpContext.Current.Response.Write("无法创建Excel对象,可能你的电脑未装Excel"); return(false); } //创建Excel的工作簿 Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1 Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; range = (Excel.Range)worksheet.get_Range("A1", "O1"); //获取表格中第一行 range.Merge(0); //合并第一行 worksheet.Cells[1, 1] = this.selectyear.SelectedValue + "年度报销费用-部门报销费用按月度汇总"; //大标题 range.Font.Size = 22; range.Font.Bold = true; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.EntireColumn.AutoFit(); range.EntireRow.AutoFit(); //写入标题 for (int i = 0; i < dt.Columns.Count + 1; i++) { //写入标题名称 if (i == 0) { worksheet.Cells[2, i + 1] = "序号"; } else { worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];//从第二行的第一格开始写数据 } //设置标题的样式 range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Font.Bold = true; //粗体 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中 range.Interior.ColorIndex = 15; range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色 if (i == 0) //序号列宽度自动 { range.EntireColumn.AutoFit(); } else { //设置单元格的宽度,如果小于9就设置为9,如果大于。则设置为自动 if (range.EntireColumn.ColumnWidth <= 8.5) { range.EntireColumn.ColumnWidth = 8.5; } else { range.EntireColumn.AutoFit();//自动设置列宽 } } } //写入DataTable中数据的内容 for (int r = 0; r < dt.Rows.Count; r++) { for (int c = 0; c < dt.Columns.Count + 1; c++) { range = (Excel.Range)worksheet.Cells[r + 3, c + 1]; //写入内容 if (c == 0) { if (r == dt.Rows.Count - 1) { worksheet.Cells[r + 3, c + 1] = ""; } else { worksheet.Cells[r + 3, c + 1] = (r + 1).ToString(); //得到序号 } range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中 } else if (c == 1) { worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString(); } else { worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString().Split(',')[0]; range.NumberFormat = "#,##0.00"; } //设置样式 range.Font.Size = 9; //字体大小 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框 //设置单元格的宽度,如果小于8.5就设置为8.5,如果大于。则设置为自动 if (c == 0) //序号列宽度设为自动 { range.EntireColumn.AutoFit(); } else { if (range.EntireColumn.ColumnWidth <= 8.5) { range.EntireColumn.ColumnWidth = 8.5; } else { range.EntireColumn.AutoFit();//自动设置列宽 } } } if (r == dt.Rows.Count - 1) { range = (Excel.Range)worksheet.get_Range("A" + (r + 3).ToString(), "B" + (r + 3).ToString()); range.Merge(0); range.Value = "合计"; range.Font.Size = 9; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中 range.EntireColumn.AutoFit(); } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } try { workbook.Saved = true; workbook.SaveCopyAs(AbosultedFilePath); } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write("导出文件时出错,文件可能正被打开!\n" + ex.ToString()); return(false); } workbook.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = System.GC.GetGeneration(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.GC.Collect(generation); } GC.Collect();//强行销毁 #region 强行杀死最近打开的Excel进程 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killID = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killID = m; } } if (excelProc[killID].HasExited == false) { excelProc[killID].Kill(); } #endregion return(true); }
protected void ExportExcel(DataTable dt, string table) { if (dt == null || dt.Rows.Count == 0) { 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.Workbook workbook = xlApp.Workbooks.Open(table, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //Excel.Worksheet worksheet = workbook.Sheets[1] as Excel.Worksheet; //第一个sheet页 worksheet.Name = "武汉市公费"; //这里修改sheet名称 try { 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] = dt.Columns[i].ColumnName; range = (Excel.Range)worksheet.Cells[1, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; //range.NumberFormat = "0.00"; } DataTable dtDec = DecCol(); for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { string strValue = dt.Rows[r][i].ToString(); decimal dm = 0M; if (dtDec.Columns.Contains(dt.Columns[i].ColumnName)) { ((Excel.Range)worksheet.Cells[r + 2, i + 1]).NumberFormat = "0.00"; worksheet.Cells[r + 2, i + 1] = strValue; } else { worksheet.Cells[r + 2, i + 1] = "'" + strValue; //worksheet.Cells[r + 2, i + 1] = strValue; } } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; } xlApp.Visible = true; } catch (Exception ex) { } finally { workbook.Saved = true; if (System.IO.File.Exists(table)) { System.IO.File.Delete(table); } workbook.SaveCopyAs(table); workbook.Close(true, Type.Missing, Type.Missing); workbook = null; xlApp.Quit(); xlApp = null; } }
private void OutPut() { RandomExamCountStatisticBLL objBll = new RandomExamCountStatisticBLL(); //I当前登录人ID int _OrgId = PrjPub.CurrentLoginUser.StationOrgID; //等1:路局 等于0:站段 int _SuitRangeId = PrjPub.CurrentLoginUser.SuitRange; //考试开始时间 DateTime _DateFrom = Convert.ToDateTime(dateStartDateTime.DateValue); //考试结束时间 DateTime _DateTo = Convert.ToDateTime(dateEndDateTime.DateValue); int style = Convert.ToInt32(ddlStyle.SelectedValue); IList <RailExam.Model.RandomExamCountStatistic> objList = objBll.GetCountWithOrg(_SuitRangeId, _OrgId, _DateFrom, _DateTo, PrjPub.GetRailSystemId(), style); 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 Excel.Range range; string filename = ""; try { //生成.xls文件完整路径名 filename = Server.MapPath("/RailExamBao/Excel/Count.xls"); if (File.Exists(filename.ToString())) { File.Delete(filename.ToString()); } //将所得到的表的列名,赋值给单元格 objSheet.Cells[1, 1] = "序号"; objSheet.Cells[1, 2] = "站段单位"; range = objSheet.get_Range(objSheet.Cells[1, 2], objSheet.Cells[1, 4]); range.Merge(0); objSheet.Cells[1, 5] = "考试次数"; objSheet.Cells[1, 6] = "参考人次"; //同样方法处理数据 for (int i = 0; i < objList.Count; i++) { objSheet.Cells[2 + i, 1] = i + 1; objSheet.Cells[2 + i, 2] = objList[i].OrgName; range = objSheet.get_Range(objSheet.Cells[2 + i, 2], objSheet.Cells[2 + i, 4]); range.Merge(0); objSheet.Cells[2 + i, 5] = objList[i].ExamCount; objSheet.Cells[2 + i, 6] = objList[i].EmployeeCount; } //不可见,即后台处理 objApp.Visible = false; objbook.Saved = true; objbook.SaveCopyAs(filename); } catch { SessionSet.PageMessage = "系统错误,导出Excel文件失败!"; } finally { objbook.Close(Type.Missing, filename, Type.Missing); objbooks.Close(); objApp.Application.Workbooks.Close(); objApp.Application.Quit(); objApp.Quit(); GC.Collect(); } }
private void ExportArrange() { // 根据 ProgressBar.htm 显示进度条界面 string templateFileName = Path.Combine(Server.MapPath("."), "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 strExamId = Request.QueryString.Get("id"); RandomExamArrangeBLL eaBll = new RandomExamArrangeBLL(); IList <RailExam.Model.RandomExamArrange> ExamArranges = eaBll.GetRandomExamArranges(int.Parse(strExamId)); RandomExamBLL objBll = new RandomExamBLL(); RailExam.Model.RandomExam objRandomExam = objBll.GetExam(Convert.ToInt32(Request.QueryString.Get("id"))); EmployeeBLL psBLL = new EmployeeBLL(); DataSet ds = new DataSet(); string[] str = ExamArranges[0].UserIds.Split(','); IList <Employee> objList = new List <Employee>(); if (str[0] != "") { OracleAccess db = new OracleAccess(); string strSql; OrganizationBLL orgBll = new OrganizationBLL(); for (int i = 0; i < str.Length; i++) { Employee obj = psBLL.GetChooseEmployeeInfo(str[i]); obj.RowNum = i + 1; if (PrjPub.CurrentLoginUser.RoleID != 1) { if (orgBll.GetStationOrgID(obj.OrgID) == PrjPub.CurrentLoginUser.StationOrgID) { objList.Add(obj); } } else { objList.Add(obj); } } if (objList.Count > 0) { ds.Tables.Add(ConvertToDataTable((IList)objList)); if (ViewState["Sort"] != null) { ds.Tables[0].DefaultView.Sort = ViewState["Sort"].ToString(); } DataColumn dc = ds.Tables[0].Columns.Add("ComputeRoom"); strSql = "select a.*,c.Short_Name||'-'||b.Computer_Room_Name as ComputeRoom " + " from Random_Exam_Arrange_Detail a " + " inner join Computer_Room b on a.Computer_Room_ID=b.Computer_Room_ID" + " inner join Org c on b.Org_ID=c.Org_ID" + " where Random_Exam_ID='" + strExamId + "'"; DataSet dsDetail = db.RunSqlDataSet(strSql); foreach (DataRow dr in ds.Tables[0].Rows) { string strUser = "******" + dr["EmployeeID"] + ","; DataRow[] drs = dsDetail.Tables[0].Select("','+User_Ids+',' like '%" + strUser + "%'"); if (drs.Length > 0) { dr["ComputeRoom"] = drs[0]["ComputeRoom"].ToString(); } else { dr["ComputeRoom"] = string.Empty; } } } System.Threading.Thread.Sleep(10); string jsBlock = "<script>SetPorgressBar('导出考生信息','" + ((1 * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>"; Response.Write(jsBlock); Response.Flush(); #region OWC11 /* * SpreadsheetClass xlsheet = new SpreadsheetClass(); * Worksheet ws = (Worksheet)xlsheet.Worksheets[1]; * ws.Cells.Font.set_Size(10); * ws.Cells.Font.set_Name("宋体"); * * ws.Cells[1, 1] = objRandomExam.ExamName + " 参加考试学员名单"; * Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 7]); * range.set_MergeCells(true); * range.set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * range.Font.set_Name("宋体"); * * * //write headertext * ws.Cells[2, 1] = "序号"; * ((Range)ws.Cells[2, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * * * ws.Cells[2, 2] = "姓名"; * ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_MergeCells(true); * ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * * if (PrjPub.IsWuhan()) * { * ws.Cells[2, 3] = "员工编码"; * } * else * { * ws.Cells[2, 3] = "工资编号"; * } * ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_MergeCells(true); * ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * * ws.Cells[2, 4] = "职名"; * ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_MergeCells(true); * ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * * ws.Cells[2, 5] = "组织机构"; * ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_MergeCells(true); * ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * * ws.Cells[2, 8] = "考试地点"; * ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 10]).set_MergeCells(true); * ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * * int j = 0; * foreach(DataRow dr in ds.Tables[0].Rows) * { * ws.Cells[3 + j, 1] = j + 1; * * ws.Cells[3 + j, 2] = dr["EmployeeName"].ToString(); * ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_MergeCells(true); * ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft); * * ws.Cells[3 + j, 3] = "'" + dr["StrWorkNo"].ToString(); * ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_MergeCells(true); * ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft); * * * ws.Cells[3 + j, 4] = dr["PostName"].ToString(); * ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_MergeCells(true); * ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft); * * ws.Cells[3 + j, 5] = dr["OrgName"].ToString(); * ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_MergeCells(true); * ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * * ws.Cells[3 + j, 8] = dr["ComputeRoom"].ToString(); * ws.get_Range(ws.Cells[3 + j, 8], ws.Cells[3 + j, 10]).set_MergeCells(true); * ws.get_Range(ws.Cells[3 + j, 8], ws.Cells[3 + j, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); * * j++; * * System.Threading.Thread.Sleep(10); * jsBlock = "<script>SetPorgressBar('导出考生信息','" + (((j + 1) * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>"; * Response.Write(jsBlock); * Response.Flush(); * } * * ws.Name = "1-1"; * ws.Cells.Columns.AutoFit(); * * * ((Worksheet)xlsheet.Worksheets[1]).Activate(); * * string path = Server.MapPath("../Excel/Excel.xls"); * if (File.Exists(path)) * File.Delete(path); * xlsheet.Export(path, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportAsAppropriate); */ #endregion 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 Excel.Range rang1; string filename = ""; try { //生成.xls文件完整路径名 filename = Server.MapPath("/RailExamBao/Excel/Excel.xls"); if (File.Exists(filename.ToString())) { File.Delete(filename.ToString()); } objSheet.Cells.Font.Size = 10; objSheet.Cells.Font.Name = "宋体"; objSheet.Cells[1, 1] = objRandomExam.ExamName + " 参加考试学员名单"; rang1 = objSheet.get_Range(objSheet.Cells[1, 1], objSheet.Cells[1, 6]); rang1.Merge(0); rang1.HorizontalAlignment = XlHAlign.xlHAlignCenter; rang1.Font.Bold = true; objSheet.Cells.Font.Size = 17; objSheet.Cells.Font.Name = "宋体"; objSheet.Cells.Font.Size = 12; objSheet.Cells.Font.Name = "宋体"; //write headertext objSheet.Cells[2, 1] = "序号"; ((Excel.Range)objSheet.Cells[2, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 2] = "姓名"; objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 3] = "员工编码"; objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 4] = "职名"; objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 5] = "组织机构(车间)"; objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 6] = "考试地点"; objSheet.get_Range(objSheet.Cells[2, 6], objSheet.Cells[2, 6]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 6], objSheet.Cells[2, 6]).HorizontalAlignment = XlHAlign.xlHAlignCenter; int j = 0; foreach (DataRow dr in ds.Tables[0].Rows) { objSheet.Cells[3 + j, 1] = j + 1; objSheet.Cells[3 + j, 2] = dr["EmployeeName"].ToString(); objSheet.get_Range(objSheet.Cells[6 + j, 2], objSheet.Cells[6 + j, 2]).Merge(0); objSheet.get_Range(objSheet.Cells[6 + j, 2], objSheet.Cells[6 + j, 2]).HorizontalAlignment = XlHAlign.xlHAlignLeft; objSheet.Cells[3 + j, 3] = "'" + dr["StrWorkNo"].ToString(); objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).Merge(0); objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft; objSheet.Cells[3 + j, 4] = dr["PostName"].ToString(); objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).Merge(0); objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).HorizontalAlignment = XlHAlign.xlHAlignLeft; objSheet.Cells[3 + j, 5] = dr["OrgName"].ToString(); objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).Merge(0); objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).HorizontalAlignment = XlHAlign.xlHAlignLeft; objSheet.Cells[3 + j, 6] = dr["ComputeRoom"].ToString(); objSheet.get_Range(objSheet.Cells[3 + j, 6], objSheet.Cells[3 + j, 6]).Merge(0); objSheet.get_Range(objSheet.Cells[3 + j, 6], objSheet.Cells[3 + j, 6]).HorizontalAlignment = XlHAlign.xlHAlignLeft; j++; System.Threading.Thread.Sleep(10); jsBlock = "<script>SetPorgressBar('导出考生信息','" + (((j + 1) * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>"; Response.Write(jsBlock); Response.Flush(); } objSheet.Cells.Columns.AutoFit(); objApp.Visible = false; objbook.Saved = true; objbook.SaveCopyAs(filename); } 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='true';window.close();</script>"); }
protected void btnOutPut_Click(object sender, EventArgs e) { try { RandomExamBLL objBll = new RandomExamBLL(); RailExam.Model.RandomExam objRandomExam = objBll.GetExam(Convert.ToInt32(Request.QueryString.Get("eid"))); IList <Employee> objList = BindGrid(); #region 原导出Excel方法 //SpreadsheetClass xlsheet = new SpreadsheetClass(); //Worksheet ws = (Worksheet)xlsheet.Worksheets[1]; //ws.Cells.Font.set_Size(10); //ws.Cells.Font.set_Name("宋体"); //ws.Cells[1, 1] = objRandomExam.ExamName + " 未参加考试学员名单"; //Range rang1 = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 7]); //rang1.set_MergeCells(true); //rang1.set_HorizontalAlignment(XlHAlign.xlHAlignCenter); //rang1.Font.set_Name("宋体"); ////write headertext //ws.Cells[2, 1] = "序号"; //((Range)ws.Cells[2, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); //ws.Cells[2, 2] = "姓名"; //ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_MergeCells(true); //ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); //if (PrjPub.IsWuhan()) //{ // ws.Cells[2, 3] = "员工编码(身份证号码)"; //} //else //{ // ws.Cells[2, 3] = "工资编号"; //} //ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_MergeCells(true); //ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); //ws.Cells[2, 4] = "职名"; //ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_MergeCells(true); //ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); //ws.Cells[2, 5] = "组织机构"; //ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_MergeCells(true); //ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); //for (int j = 0; j < objList.Count; j++) //{ // ws.Cells[3 + j, 1] = j + 1; // ws.Cells[3 + j, 2] = objList[j].EmployeeName; // ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_MergeCells(true); // ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft); // ws.Cells[3 + j, 3] = "'" + objList[j].StrWorkNo; // ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_MergeCells(true); // ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft); // ws.Cells[3 + j, 4] = objList[j].PostName; // ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_MergeCells(true); // ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft); // ws.Cells[3 + j, 5] = objList[j].OrgName; // ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_MergeCells(true); // ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter); //} //ws.Name = "1-1"; //ws.Cells.Columns.AutoFit(); //((Worksheet)xlsheet.Worksheets[1]).Activate(); //string path = Server.MapPath("../Excel/Excel.xls"); //if (File.Exists(path)) // File.Delete(path); //xlsheet.Export(path, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportAsAppropriate); #endregion 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 Excel.Range rang1; string filename = ""; //生成.xls文件完整路径名 filename = Server.MapPath("/RailExamBao/Excel/Excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"); if (File.Exists(filename.ToString())) { File.Delete(filename.ToString()); } objSheet.Cells.Font.Size = 10; objSheet.Cells.Font.Name = "宋体"; objSheet.Cells[1, 1] = objRandomExam.ExamName + " 未参加考试学员名单"; rang1 = objSheet.get_Range(objSheet.Cells[1, 1], objSheet.Cells[1, 7]); rang1.Merge(0); rang1.HorizontalAlignment = XlHAlign.xlHAlignCenter; rang1.Font.Bold = true; objSheet.Cells.Font.Size = 17; objSheet.Cells.Font.Name = "宋体"; //write headertext objSheet.Cells[2, 1] = "序号"; ((Excel.Range)objSheet.Cells[2, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 2] = "姓名"; objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 3] = "员工编码(身份证号码)"; objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 4] = "职名"; objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[2, 5] = "组织机构"; objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).Merge(0); objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter; for (int j = 0; j < objList.Count; j++) { objSheet.Cells[3 + j, 1] = j + 1; objSheet.Cells[3 + j, 2] = objList[j].EmployeeName; objSheet.get_Range(objSheet.Cells[3 + j, 2], objSheet.Cells[3 + j, 2]).Merge(0); objSheet.get_Range(objSheet.Cells[3 + j, 2], objSheet.Cells[3 + j, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[3 + j, 3] = "'" + objList[j].StrWorkNo; objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).Merge(0); objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[3 + j, 4] = objList[j].PostName; objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).Merge(0); objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter; objSheet.Cells[3 + j, 5] = objList[j].OrgName; objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).Merge(0); objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter; } objSheet.Columns.AutoFit(); objApp.Visible = false; objbook.Saved = true; objbook.SaveCopyAs(filename); FileInfo file = new FileInfo(filename); this.Response.Clear(); this.Response.Buffer = true; this.Response.Charset = "utf-7"; this.Response.ContentEncoding = Encoding.UTF7; // 添加头信息,为"文件下载/另存为"对话框指定默认文件名 this.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(objRandomExam.ExamName + "未参加考试学员名单") + ".xls"); // 添加头信息,指定文件大小,让浏览器能够显示下载进度 this.Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流,必须被下载 this.Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 this.Response.WriteFile(file.FullName); } catch { SessionSet.PageMessage = "系统错误,导出Excel文件失败!"; } }
/// <summary> /// 将datatable导出excel文件 /// </summary> /// <param name="dt">需要导出的datatable</param> /// <param name="AbosultedFilePath">导出文件的绝对路径</param> /// <returns></returns> public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath) { dt.Columns.Remove("id"); dt.Columns.Remove("year"); dt.Columns.Remove("month"); dt.Columns.Remove("jobflowid"); dt.AcceptChanges(); //检查数据表是否为空,如果为空,则退出 if (dt == null) { return(false); } //创建Excel应用程序对象,如果未创建成功则退出 Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { System.Web.HttpContext.Current.Response.Write("无法创建Excel对象,可能你的电脑未装Excel"); return(false); } //创建Excel的工作簿 Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1 Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; range = (Excel.Range)worksheet.get_Range("A1", "I1");//获取表格第一行 range.Merge(0); worksheet.Cells[1, 1] = this.biaoti.InnerText; range.Font.Size = 22; range.Font.Bold = true; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.EntireColumn.AutoFit(); range.EntireRow.AutoFit(); //写入标题 for (int i = 0; i < dt.Columns.Count + 1; i++) { //写入标题名称 if (i == 0) { worksheet.Cells[2, i + 1] = "序号"; //加入序号列 } else { worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName]; } range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Font.Bold = true;//粗体 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.Interior.ColorIndex = 15; range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); if (i == 0)//序号列宽度设为自动调整 { range.EntireColumn.AutoFit(); } else { if (range.EntireColumn.ColumnWidth <= 8.5) { range.EntireColumn.ColumnWidth = 8.5; } else { range.EntireColumn.AutoFit(); } } } //写入DataTable中数据的内容 for (int r = 0; r < dt.Rows.Count; r++) { for (int c = 0; c < dt.Columns.Count + 1; c++) { range = (Excel.Range)worksheet.Cells[r + 3, c + 1]; //写入内容 if (c == 0) //增加序号 { worksheet.Cells[r + 3, c + 1] = (r + 1).ToString(); } else if (dt.Columns[c - 1].ColumnName == "happendate") //时间列 { worksheet.Cells[r + 3, c + 1] = ((DateTime)dt.Rows[r][c - 1]).ToString("yyyy年MM月dd日"); } else if (dt.Columns[c - 1].ColumnName == "ausmoney") //金额列 { worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString(); range.NumberFormat = "#,##0.00"; } else if (dt.Columns[c - 1].ColumnName == "payStatus") //支付列 { if (dt.Rows[r][c - 1].ToString() == "1") { worksheet.Cells[r + 3, c + 1] = "已支付"; } else { worksheet.Cells[r + 3, c + 1] = "未支付"; } } else { worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString(); } //设置样式 range.Font.Size = 9; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框 //设置单元格的宽度,如果小于8.5就设置为8.5,如果大于。则设置为自动 if (c == 0) //序号列宽度设为自动 { range.EntireColumn.AutoFit(); } else { if (range.EntireColumn.ColumnWidth <= 8.5) { range.EntireColumn.ColumnWidth = 8.5; } else { range.EntireColumn.AutoFit();//自动设置列宽 } } } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } //设置合计那一行 range = (Excel.Range)worksheet.get_Range("A" + (dt.Rows.Count + 3).ToString(), "I" + (dt.Rows.Count + 3).ToString()); range.Font.ColorIndex = 41; //range.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone; range.Font.Size = 10; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框 range.EntireColumn.AutoFit(); //自动调整列宽 worksheet.Cells[3 + dt.Rows.Count, 1] = "合计:"; //合计那一行的第一列 if (dt.Rows.Count == 0) { worksheet.Cells[3 + dt.Rows.Count, 7] = 0; } else { worksheet.Cells[3 + dt.Rows.Count, 7] = "=SUM(G3:G" + (dt.Rows.Count + 2).ToString() + ")"; } range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; System.Windows.Forms.Application.DoEvents(); try { workbook.Saved = true; workbook.SaveCopyAs(AbosultedFilePath); } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write("导出文件时出错,文件可能正被打开!\n" + ex.ToString()); return(false); } workbook.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = System.GC.GetGeneration(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.GC.Collect(generation); } GC.Collect();//强行销毁 #region 强行杀死最近打开的Excel进程 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killID = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killID = m; } } if (excelProc[killID].HasExited == false) { excelProc[killID].Kill(); } #endregion return(true); }
// public void WriteCardInfoExcel(DataSet dsCardInfo,string savePath) // { // try // { // System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value; // // object[,] objData; // // if ( dsCardInfo.Tables[0] != null ) // { // objData = new object[dsCardInfo.Tables[0].Rows.Count+1,1]; // objData[0,0] = "导出的卡号"; // // for( int i=1; i<=dsCardInfo.Tables[0].Rows.Count; i++ ) // objData[i,0] = dsCardInfo.Tables[0].Rows[i-1]["info_stuCardNumber"]; // // m_objExcel = new Excel.Application(); // m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; // m_objBook = (Excel._Workbook)m_objBooks.Add(true); // // m_objSheets = (Excel.Sheets)m_objBook.Sheets; // m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); // // m_objRange = m_objSheet.get_Range("A1",m_objOpt); // m_objRange = m_objRange.get_Resize(dsCardInfo.Tables[0].Rows.Count+1,1); // m_objRange.Value = objData; // // m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, // m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, // m_objOpt, m_objOpt, m_objOpt, m_objOpt); // m_objBook.Close(false, m_objOpt, m_objOpt); // m_objExcel.Quit(); // // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); // System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); // } // } // catch(Exception ex) // { // Util.WriteLog(ex.Message,Util.EXCEPTION_LOG_TITLE); // } // finally // { // m_objFont = null; // m_objRange = null; // m_objSheet = null; // m_objSheets = null; // m_objBook = null; // m_objBooks = null; // m_objExcel = null; // // GC.Collect(); // } // // } public void WriteCardInfoExcel(DataSet dsCardInfo, string savePath) { try { m_objExcel = new Excel.Application(); m_objExcel.DisplayAlerts = false; m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Add(true); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); object[,] objData = null; int index = 0; if (dsCardInfo.Tables[0].Rows.Count > 0) { objData = new object[dsCardInfo.Tables[0].Rows.Count + 1, dsCardInfo.Tables[0].Columns.Count + 2]; objData[0, 0] = "年级"; objData[0, 1] = "班级"; objData[0, 2] = "学号"; objData[0, 3] = "卡号"; objData[0, 4] = "操作"; objData[0, 5] = "统计"; for (int row = 0; row < dsCardInfo.Tables[0].Rows.Count; row++) { objData[row + 1, 0] = dsCardInfo.Tables[0].Rows[row][0]; objData[row + 1, 1] = dsCardInfo.Tables[0].Rows[row][1]; objData[row + 1, 2] = dsCardInfo.Tables[0].Rows[row][2]; objData[row + 1, 3] = dsCardInfo.Tables[0].Rows[row][3]; } foreach (DataRow row in dsCardInfo.Tables[2].Rows) { objData[index + 1, 5] = row[1]; index += Convert.ToInt32(row[1]); } m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange = m_objRange.get_Resize(dsCardInfo.Tables[0].Rows.Count + 4, 6); m_objRange.Value = objData; m_objRange.Font.Size = 8; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; RowMerge("A", dsCardInfo.Tables[1]); RowMerge("B", dsCardInfo.Tables[2]); RowMerge("C", dsCardInfo.Tables[3]); RowMerge("F", dsCardInfo.Tables[2]); m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()); m_objRange.Value = "空卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()); m_objRange.Value = GetCardEncryptedNumber() - Convert.ToInt32(dsCardInfo.Tables[4].Rows[0][0]) - Convert.ToInt32(dsCardInfo.Tables[5].Rows[0][0]); m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString()); m_objRange.Value = "学生卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString()); m_objRange.Value = dsCardInfo.Tables[4].Rows[0][0]; m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString()); m_objRange.Value = "教师卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString()); m_objRange.Value = dsCardInfo.Tables[5].Rows[0][0]; m_objRange.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "F" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()).Merge(m_objOpt); m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } } catch (Exception ex) { throw ex; } finally { if (m_objRange != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); m_objRange = null; } if (m_objSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); m_objSheet = null; } if (m_objSheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); m_objSheets = null; } if (m_objBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); m_objBook = null; } if (m_objBooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); m_objBooks = null; } if (m_objExcel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); m_objExcel = null; } GC.Collect(); KillProcess(); } }