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