/// <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);
}
