/// <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 ReadStuInfoXLS(ref DataSet dsStuInfoXls,string getPath,int classNumbers,ref Hashtable className) { OleDbDataAdapter myAdp = null; OleDbConnection oleConn = null; string[] sheetName = new string[classNumbers]; DataTable[] stuInfoXlsTable = new DataTable[classNumbers]; try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 getPath,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; for ( int i=0; i<classNumbers; i++ ) { m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(i+1)); //记录工作表名称 sheetName[i] = m_objSheet.Name.Trim(); className.Add(i,sheetName[i]); } m_objBooks.Close(); oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器 + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"); oleConn.Open(); for ( int i=0; i<classNumbers; i++ ) { stuInfoXlsTable[i] = new DataTable("Table"+i.ToString()); myAdp = new OleDbDataAdapter("select * from ["+sheetName[i]+"$]", oleConn); //获取第一张工作表的内容 myAdp.Fill(stuInfoXlsTable[i]); dsStuInfoXls.Tables.Add(stuInfoXlsTable[i]); } oleConn.Close(); 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 e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } finally { m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
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(); } }
public void ReadStuInfoXLS(ref DataSet dsStuInfoXls, string getPath, int classNumbers, ref Hashtable className) { OleDbDataAdapter myAdp = null; OleDbConnection oleConn = null; string[] sheetName = new string[classNumbers]; DataTable[] stuInfoXlsTable = new DataTable[classNumbers]; try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 getPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; for (int i = 0; i < classNumbers; i++) { m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(i + 1)); //记录工作表名称 sheetName[i] = m_objSheet.Name.Trim(); className.Add(i, sheetName[i]); } m_objBooks.Close(); oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器 + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"); oleConn.Open(); for (int i = 0; i < classNumbers; i++) { stuInfoXlsTable[i] = new DataTable("Table" + i.ToString()); myAdp = new OleDbDataAdapter("select * from [" + sheetName[i] + "$]", oleConn); //获取第一张工作表的内容 myAdp.Fill(stuInfoXlsTable[i]); dsStuInfoXls.Tables.Add(stuInfoXlsTable[i]); } oleConn.Close(); 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 e) { Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE); } finally { m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
private void ExportComputerRoom() { //不符合数据的数据源 DataTable dt = new DataTable(); DataColumn dcnew1 = dt.Columns.Add("ComputerRoomName"); DataColumn dcnew2 = dt.Columns.Add("SeatCount"); DataColumn dcnew5 = dt.Columns.Add("Seat"); DataColumn dcnew6 = dt.Columns.Add("MACAddress"); DataColumn dcnew7 = dt.Columns.Add("ErrorReason"); string strFileName = Server.UrlDecode(Request.QueryString.Get("FileName")); string jsBlock; string strPath = Server.MapPath("/RailExamBao/Excel/" + strFileName); bool isClose = false; string strMessage; Excel.Application objApp = null; Excel._Workbook objBook = null; Excel.Workbooks objBooks = null; Excel.Sheets objSheets = null; Excel._Worksheet objSheet = null; Excel.Range range = null; DataSet ds = new DataSet(); #region 读取Excel文件 try { //生成ExcelApp objApp = new Excel.Application(); //Excel不显示 objApp.Visible = false; //生成Books objBooks = objApp.Workbooks; //打开Excel文件 objBooks.Open(strPath, 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); //取得Book objBook = objBooks.get_Item(1); //取得Sheets objSheets = objBook.Worksheets; //取得Sheet objSheet = (Excel._Worksheet)objSheets.get_Item(1); //取得Range int rowNum = objSheet.UsedRange.Rows.Count; int colNum = objSheet.UsedRange.Columns.Count; // 根据 ProgressBar.htm 显示进度条界面 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); DataTable dtItem = new DataTable(); Hashtable htCol = new Hashtable(); for (int i = 1; i <= colNum; i++) { range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[1, i], objSheet.Cells[1, i])); DataColumn dc = dtItem.Columns.Add(range.Value2.ToString()); htCol[range.Value2.ToString()] = i; } DataRow newRow = null; for (int i = 2; i <= rowNum; i++) { newRow = dtItem.NewRow(); range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["序号"]], objSheet.Cells[i, htCol["序号"]])); newRow["序号"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["单位"]], objSheet.Cells[i, htCol["单位"]])); newRow["单位"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["微机教室名称"]], objSheet.Cells[i, htCol["微机教室名称"]])); newRow["微机教室名称"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["微机教室地址"]], objSheet.Cells[i, htCol["微机教室地址"]])); newRow["微机教室地址"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["联系人"]], objSheet.Cells[i, htCol["联系人"]])); newRow["联系人"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["联系电话"]], objSheet.Cells[i, htCol["联系电话"]])); newRow["联系电话"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["服务器名称及型号"]], objSheet.Cells[i, htCol["服务器名称及型号"]])); newRow["服务器名称及型号"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["服务器存放地点"]], objSheet.Cells[i, htCol["服务器存放地点"]])); newRow["服务器存放地点"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["服务器IP地址"]], objSheet.Cells[i, htCol["服务器IP地址"]])); newRow["服务器IP地址"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["机位数"]], objSheet.Cells[i, htCol["机位数"]])); newRow["机位数"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["机位"]], objSheet.Cells[i, htCol["机位"]])); newRow["机位"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["MAC地址"]], objSheet.Cells[i, htCol["MAC地址"]])); newRow["MAC地址"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["良好"]], objSheet.Cells[i, htCol["良好"]])); newRow["良好"] = range.Value2; range = ((Excel.Range)objSheet.get_Range(objSheet.Cells[i, htCol["损坏"]], objSheet.Cells[i, htCol["损坏"]])); newRow["损坏"] = range.Value2; dtItem.Rows.Add(newRow); System.Threading.Thread.Sleep(10); jsBlock = "<script>SetPorgressBar('正在读取Excel文件','" + ((double)((i - 1) * 100) / (double)rowNum).ToString("0.00") + "'); </script>"; Response.Write(jsBlock); Response.Flush(); } ds.Tables.Add(dtItem); // 处理完成 jsBlock = "<script>SetCompleted('Excel数据读取完毕'); </script>"; Response.Write(jsBlock); Response.Flush(); } catch { isClose = true; } finally { objBook.Close(Type.Missing, strPath, Type.Missing); objBooks.Close(); objApp.Application.Workbooks.Close(); objApp.Application.Quit(); objApp.Quit(); GC.Collect(); } if (isClose) { if (File.Exists(strPath)) { File.Delete(strPath); } Response.Write("<script>window.returnValue='请检查Excel文件格式',window.close();</script>"); return; } #endregion #region 检验数据 System.Threading.Thread.Sleep(10); jsBlock = "<script>SetPorgressBar('正准备检测Excel数据','0.00'); </script>"; Response.Write(jsBlock); Response.Flush(); if (ds.Tables[0].Rows.Count == 0) { Response.Write("<script>window.returnValue='Excel中没有任何记录,请核对',window.close();</script>"); return; } DataColumn dc1 = ds.Tables[0].Columns.Add("ItemType"); DataColumn dc2 = ds.Tables[0].Columns.Add("BookID"); int index = 1; foreach (DataRow dr in ds.Tables[0].Rows) { System.Threading.Thread.Sleep(10); jsBlock = "<script>SetPorgressBar('正在检测Excel数据','" + ((double)(index * 100) / (double)ds.Tables[0].Rows.Count).ToString("0.00") + "'); </script>"; Response.Write(jsBlock); Response.Flush(); index = index + 1; } // 处理完成 jsBlock = "<script>SetCompleted('Excel数据检测完毕'); </script>"; Response.Write(jsBlock); Response.Flush(); #endregion if (dt.Rows.Count > 0) { Session["table"] = dt; if (File.Exists(strPath)) { File.Delete(strPath); } Response.Write("<script>window.returnValue='refresh|请检查Excel文件数据',window.close();</script>"); return; } else { dt.Clear(); Session["table"] = dt; } #region 导入数据 System.Threading.Thread.Sleep(10); jsBlock = "<script>SetPorgressBar('正准备导入试题','0.00'); </script>"; Response.Write(jsBlock); Response.Flush(); try { int count = 0; int m = 1; foreach (DataRow dr in ds.Tables[0].Rows) { System.Threading.Thread.Sleep(10); jsBlock = "<script>SetPorgressBar('正在导入试题','" + ((double)(m * 100) / (double)ds.Tables[0].Rows.Count).ToString("0.00") + "'); </script>"; Response.Write(jsBlock); Response.Flush(); m = m + 1; } jsBlock = "<script>SetCompleted('试题导入完毕'); </script>"; Response.Write(jsBlock); Response.Flush(); strMessage = "导入成功!"; } catch { strMessage = "导入失败!"; } if (File.Exists(strPath)) { File.Delete(strPath); } Response.Write("<script>window.returnValue='refresh|" + strMessage + "';window.close();</script>"); #endregion }
public bool WriteExcelFile(string sReportName, string sSheetName, DateTime time1, DateTime time2, DataTable dt) { string[] date = new string[2]; date[0] = dealdate(time1); date[1] = dealdate(time2); StringBuilder sAddrModel = new StringBuilder(40); StringBuilder sAddrReport = new StringBuilder(40); string sFileModel = ""; string sFileDer = ""; string colA = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; char[] colABC = new char[26] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' }; GetPrivateProfileString("Addr", "AddrModel", "", sAddrModel, sAddrModel.Capacity, FILE_NAME); GetPrivateProfileString("Addr", "AddrReport", "", sAddrReport, sAddrReport.Capacity, FILE_NAME); sFileModel = sAddrModel.ToString() + sReportName.Trim() + ".xls"; if (date[0].CompareTo(date[1]) == 0) { sReportName = sReportName.Trim() + "(" + date[0] + ")"; } else { sReportName = sReportName.Trim() + "(" + date[0] + "-" + date[1] + ")"; } sFileDer = sAddrReport.ToString() + sReportName.Trim() + ".xls"; if (System.IO.File.Exists(sFileModel) == false) { MyLog.Log("3", "模板文件不存在"); return(false); } if (System.IO.File.Exists(sFileDer) == false) { System.IO.File.Copy(sFileModel, sFileDer, true); } else { System.IO.File.Delete(sFileDer); System.IO.File.Copy(sFileModel, sFileDer, true); } //从表中把数据导入SHEET中 #region string FileName; Excel.Workbooks eworkbooks = null; Excel._Workbook eworkbook = null; Excel.Sheets esheets = null; Excel._Worksheet esheet = null; Excel.Range range = null; excelApp.Visible = false; excelApp.UserControl = true; //用Excel应用程序创建Workbooks eworkbooks = excelApp.Workbooks; FileName = sFileDer; eworkbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //用Workbooks创建Workbook eworkbook = eworkbooks.get_Item(1); //用Workbook创建sheets esheets = eworkbook.Worksheets; int iSheetNumber = esheets.Count; int index = 0; for (int i = 1; i <= iSheetNumber; i++) { esheet = (Excel._Worksheet)esheets.get_Item(i); if (esheet.Name.Trim() == sSheetName.Trim()) { index++; break; } } if (index == 0) { esheet = null;//没有找到相应的SHEET页 } else { int _rows = dt.Rows.Count; int _cols = dt.Columns.Count; int j = 4; object[] date_write = new object[4]; date_write[0] = "日期:"; date_write[1] = date[0]; date_write[2] = "至"; date_write[3] = date[1]; range = esheet.get_Range("A" + 2, "D" + 2); range.set_Value(Missing.Value, date); date = null; for (int i = 0; i < _rows; i++) { j = i + 5;//指定从哪一行开始写入数据 object[] currentRow = new object[_cols]; currentRow = dt.Rows[i].ItemArray; range = esheet.get_Range("A" + j, colA.Substring(_cols - 1, 1) + j); range.set_Value(Missing.Value, currentRow); currentRow = null; } } eworkbook.Save(); range = null; eworkbook = null; esheet = null; esheets = null; eworkbooks.Close(); #endregion//excel写数据处理 return(true); }
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>"); }
private bool ReadToTable(string fpath)//来自日报文件 { progressBar1.Visible = true; dt = new DataTable(); DataColumn myColumn = new DataColumn(); myColumn.DataType = System.Type.GetType("System.Int16"); //该列的数据类型 myColumn.ColumnName = "序号"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.DataType = System.Type.GetType("System.String"); //该列的数据类型 myColumn.ColumnName = "井号"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "油压"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "含水"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "动液面"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "泵径"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "泵挂"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "一级杆径"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "一级杆长"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "二级杆径"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "二级杆长"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "三级杆径"; //该列的名称 dt.Columns.Add(myColumn); myColumn = new DataColumn(); myColumn.ColumnName = "三级杆长"; //该列的名称 dt.Columns.Add(myColumn); Excel.Application excel = null; Excel.Workbooks wbs = null; Excel.Workbook wb = null; Excel.Worksheet ws = null; object Nothing = System.Reflection.Missing.Value; excel = new Excel.Application(); excel.UserControl = true; excel.DisplayAlerts = false; excel.Application.Workbooks.Open(fpath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); wbs = excel.Workbooks; wb = wbs[1]; ws = (Excel.Worksheet)wb.Worksheets["油井"]; int count = 0; progressBar1.Maximum = ws.UsedRange.Rows.Count; for (int i = 0; i < ws.UsedRange.Rows.Count; i++) { if (ws.Cells[i + 4, 4].Value2 > 0) //开井时间=0或空的井不读 { DataRow myRow = dt.NewRow(); //0井号,1油压,2含水,3动液面 myRow["序号"] = ++count; myRow["井号"] = Convert.ToString(ws.Cells[i + 4, 2].Value); myRow["油压"] = Convert.ToString(ws.Cells[i + 4, 5].Value); myRow["含水"] = Convert.ToString(ws.Cells[i + 4, 8].Value); myRow["动液面"] = Convert.ToString(ws.Cells[i + 4, 9].Value); myRow["泵径"] = ""; myRow["泵挂"] = ""; myRow["一级杆径"] = ""; myRow["一级杆长"] = ""; myRow["二级杆径"] = ""; myRow["二级杆长"] = ""; myRow["三级杆径"] = ""; myRow["三级杆长"] = ""; dt.Rows.Add(myRow); if (i != 0 && i % 30 == 0) { textBox3.Text = "正在读取第" + Convert.ToString(dt.Rows.Count) + "口实开井数据!"; textBox3.Refresh(); } progressBar1.Value = i; } else { continue; } } textBox3.Text = "已读取" + Convert.ToString(dt.Rows.Count) + "口实开井数据!"; textBox3.Refresh(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); ws = null; wb.Close(false, Nothing, Nothing); System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); wb = null; wbs.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs); wbs = null; excel.Application.Workbooks.Close(); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); return(true); }