//private void bt_Query3_Click(object sender, EventArgs e)
//{
// this.dgvStockReport.DataSource = FrmBLL.ReleaseData.arrByteToDataTable(RefWebService_BLL.refWebtWarehouseWipTracking.Instance.StockQuery(txtMth.Text.Trim(), "NA", "NA", 3, 0));
//}
//private void bt_refresh_Click(object sender, EventArgs e)
//{
// bt_Query3_Click(null, null);
//}
//private void bt_toexcel_Click(object sender, EventArgs e)
//{
// if (this.dgvStockReport.RowCount == 0)
// {
// MessageBox.Show("没有数据可以导出到Excel"); return;
// }
// else
// {
// DataToExcel(dgvStockReport);
// }
//}
#endregion
#region 将datagridview 中的数据导出到excel
private void DataToExcel(DataGridView m_DataView)
{
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
oBook = (Excel._Workbook)(oExcel.Workbooks.Add(true));// 引用excel工作薄
//xSheet.Columns("A:G").Selection.NumberFormatLocal = ":";//设置A-G列为文本格式
for (int i = 0; i < m_DataView.Columns.Count; i++)
{
if (m_DataView.Columns[i].Visible == true)
{
oExcel.Cells[2, i + 1] = m_DataView.Columns[i].HeaderText.ToString();
}
}
for (int i = 0; i < m_DataView.Rows.Count; i++)
{
for (int j = 0; j < m_DataView.Columns.Count; j++)
{
if (m_DataView.Columns[j].Visible == true)
{
oExcel.Cells[i + 3, j + 1] = m_DataView.Rows[i].Cells[j].Value.ToString();
}
}
}
oExcel.Visible = true;
object Missing = System.Reflection.Missing.Value;
// oExcel.Run("Sheet1.printdoc", Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);
oBook.Application.DisplayAlerts = false;
}
public void CreateStudentBaseSimpleTable()
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件
excelPath + @"\report\ImportBaseTableForStudent(simple).xls", 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_objExcel.Visible = true;
}
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();
}
}
public void CreateStudentBaseSimpleTable()
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件
excelPath+@"\report\ImportBaseTableForStudent(simple).xls",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_objExcel.Visible = true;
}
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();
}
}
/// <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;
}
public void ExportTeacherAllReports(string path, DateTime startDate, DateTime endDate)
{
string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.Font m_objFont = null;
System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value;
try
{
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\TeacherDutyAll.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
ExportAllNormal(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportStat(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportSingleStat(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportSingle(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
m_objBook.SaveAs(path, 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_objFont);
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();
KillProcess();
}
}
/// <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));
}
public void AllStuInfoPrint(DataSet dsExportData, string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\AllStuInfo.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
dv = dsExportData.Tables[0].DefaultView;
for (int row = 1; row < dsExportData.Tables[1].Rows.Count; row++)
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Copy(m_objOpt, m_objSheet);
}
for (int row = 1; row <= dsExportData.Tables[1].Rows.Count; row++)
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(row);
m_objSheet.Name = Convert.ToString(dsExportData.Tables[1].Rows[row - 1][0]);
BindingData(m_objSheet.Name);
}
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 e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public ExcelFileWriter(string file)
{
_excelApplication = new Excel.Application();
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBooks.Open(file);
_workBook = (Excel._Workbook)(_workBooks.get_Item(1)); //(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);
}
protected virtual int ActivateExcelReturnRowCount(string file)
{
_excelApplication = new Excel.Application();
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBooks.Open(file);
_workBook = (Excel._Workbook)(_workBooks.get_Item(1)); //(Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
return(_excelSheet.Rows.Count);
}
public void WriteStuCardInfoXLS(DataSet dsStuInfo, string getPath)
{
KillProcess();
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;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = null;
if (dsStuInfo.Tables[0].Rows.Count > 0)
{
objData = new object[dsStuInfo.Tables[0].Rows.Count, 2];
for (int i = 0; i < dsStuInfo.Tables[0].Rows.Count; i++)
{
objData[i, 0] = dsStuInfo.Tables[0].Rows[i][0].ToString();
objData[i, 1] = dsStuInfo.Tables[0].Rows[i][1].ToString();
}
}
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange = m_objRange.get_Resize(dsStuInfo.Tables[0].Rows.Count, 2);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
m_objExcel.Visible = true;
}
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();
}
}
/// <summary>
/// 打开Excel文件
/// </summary>
/// <param name="filename">路径</param>
public void OpenExcelFile(string filename)
{
UserControl(false);
m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss,
miss, miss, miss, miss, miss, miss, miss);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel.Workbook)m_objExcel.ActiveWorkbook;
//m_objSheets = (Excel.Worksheets)m_objBook.Sheets;
m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
public void NutritionPrint(string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\nutrition.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
int number = m_objSheets.Count;
writeCover();
writeStuAmount();
writeStuConvert();
writeACC1();
writeACC2();
writeElement();
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_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void NutritionPrint(string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\nutrition.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
int number = m_objSheets.Count;
writeCover();
writeStuAmount();
writeStuConvert();
writeACC1();
writeACC2();
writeElement();
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_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
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);
}
}
public void ReleaseExcel()
{
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook);
//System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objSheet);
m_objExcel = null;
m_objBooks = null;
m_objBook = null;
m_objSheet = null;
GC.Collect();
}
public void ReadUpdateGradeInfo(ref DataTable dtUpdateGradeInfo, string getPath)
{
OleDbDataAdapter myAdp = null;
OleDbConnection oleConn = null;
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;
oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器
+ ";Extended Properties=Excel 8.0");
oleConn.Open();
myAdp = new OleDbDataAdapter("select * from [年班升级$]", oleConn); //获取第一张工作表的内容
myAdp.Fill(dtUpdateGradeInfo);
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();
}
}
// Show explorer form
public void showExplorerForm(Excel.Workbooks workkbooks)
{
try
{
if (explorerForm == null)
{
explorerForm = new ExplorerForm(workkbooks, OKMDocumentType.TYPE_EXCEL, configXML, docXML);
}
explorerForm.Show();
explorerForm.startUp();
}
catch (Exception e)
{
throw e;
}
}
//导出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);
}
public void WriteStuCardInfoXLS(DataSet dsStuInfo,string getPath)
{
KillProcess();
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;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = null;
if ( dsStuInfo.Tables[0].Rows.Count > 0 )
{
objData = new object[dsStuInfo.Tables[0].Rows.Count,2];
for ( int i=0; i<dsStuInfo.Tables[0].Rows.Count; i++ )
{
objData[i,0] = dsStuInfo.Tables[0].Rows[i][0].ToString();
objData[i,1] = dsStuInfo.Tables[0].Rows[i][1].ToString();
}
}
m_objRange = m_objSheet.get_Range("A2",m_objOpt);
m_objRange = m_objRange.get_Resize(dsStuInfo.Tables[0].Rows.Count,2);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
m_objExcel.Visible = true;
}
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();
}
}
public void VisitInfoPrint(DataSet dsAbsDetailInfo,DateTime getBegDate,DateTime getEndDate,string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\VisitInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for ( int row = 0; row < dsAbsDetailInfo.Tables[0].Rows.Count; row ++ )
{
for( int column = 1 ;column < dsAbsDetailInfo.Tables[0].Columns.Count-2; column ++)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 6, column + 1], (Excel.Range)m_objSheet.Cells[row + 6, column + 1]);
m_objRange.Value = dsAbsDetailInfo.Tables[0].Rows[row][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
if ( row == dsAbsDetailInfo.Tables[0].Rows.Count - 1 )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 8, 2], (Excel.Range)m_objSheet.Cells[row + 8, 3]);
m_objRange.MergeCells = true;
m_objRange.Value = "园所信息：";
m_objRange.Font.Bold = true;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 8, 4], (Excel.Range)m_objSheet.Cells[row + 8, 4]);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 2], (Excel.Range)m_objSheet.Cells[row + 9, 3]);
m_objRange.MergeCells = true;
m_objRange.Value = "统计启始时间：";
m_objRange.Font.Bold = true;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 4], (Excel.Range)m_objSheet.Cells[row + 9, 4]);
m_objRange.Value = getBegDate;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 2], (Excel.Range)m_objSheet.Cells[row + 10, 3]);
m_objRange.MergeCells = true;
m_objRange.Value = "统计截止时间：";
m_objRange.Font.Bold = true;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 4], (Excel.Range)m_objSheet.Cells[row + 10, 4]);
m_objRange.Value = getEndDate;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
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;
}
}
public void RealtimeMorningPrint(DataSet dsRealtimeMorning,string savePath)
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\RealtimeMorningInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for ( int row = 0; row < dsRealtimeMorning.Tables[0].Rows.Count; row ++ )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, 1], (Excel.Range)m_objSheet.Cells[row + 7, 1]);
m_objRange.Value = dsRealtimeMorning.Tables[0].Rows[row][1].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
for ( int column = 3;column < dsRealtimeMorning.Tables[0].Columns.Count; column ++)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, column - 1], (Excel.Range)m_objSheet.Cells[row + 7, column - 1]);
m_objRange.set_Item(1,1,dsRealtimeMorning.Tables[0].Rows[row][column].ToString());
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
if ( row == dsRealtimeMorning.Tables[0].Rows.Count - 1 )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 1], (Excel.Range)m_objSheet.Cells[row + 9, 1]);
m_objRange.Value = "园所信息：";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 2], (Excel.Range)m_objSheet.Cells[row + 9, 2]);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 1], (Excel.Range)m_objSheet.Cells[row + 10, 1]);
m_objRange.Value = "统计日期：";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 2], (Excel.Range)m_objSheet.Cells[row + 10, 2]);
m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd");
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void PrintKidDetailInfo(string getGrade,string getClass,string getName,string getNumber,string savePath)
{
try
{
DataSet dsStuList_Stat = null;
DateTime getBegDate = BegDate;
DateTime getEndDate = EndDate;
int mbTwoDates = (getEndDate.Year-getBegDate.Year)*12 + (getEndDate.Month-getBegDate.Month);
int monthes = (getEndDate.Year-getBegDate.Year)*12 + (getEndDate.Month-getBegDate.Month);
string classInfo = string.Empty;
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
if ( getName.Equals("") && getNumber.Equals("") )
{
dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_GradeClass(getGrade,getClass);
classInfo = new ClassesDataAccess().GetClassInfo(Convert.ToInt32(getClass),Convert.ToInt32(getGrade)).Tables[0].Rows[0][1].ToString();
}
else
{
dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_NameNumber(getName,getNumber);
classInfo = dsStuList_Stat.Tables[0].Rows[0][2].ToString();
}
}
if ( dsStuList_Stat.Tables[0].Rows.Count > 0 )
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\MorningCheckInfoKidDetail.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
for ( int i=0; i<=monthes; i++ )
{
GetExcelEndDate(getBegDate,ref getEndDate);
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(i+1));
if ( mbTwoDates >= 1 )
m_objSheet.Copy(m_objOpt,m_objSheet);
m_objRange = m_objSheet.get_Range("AE2",m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange = m_objSheet.get_Range("AE3",m_objOpt);
m_objRange.Value = classInfo;
m_objRange = m_objSheet.get_Range("AE4",m_objOpt);
m_objRange.Value = "'" + getBegDate.ToString("yyyy.MM");
WriteMonth(getBegDate);
for ( int nameRow=0; nameRow<dsStuList_Stat.Tables[0].Rows.Count; nameRow++ )
{
DataSet dsStuCheckInfo_Detail = null;
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
dsStuCheckInfo_Detail = stuCheckInfoDataAccess.GetStuCheckInfo_Detail(dsStuList_Stat.Tables[0].Rows[nameRow][0].ToString(),getBegDate,getEndDate);
WriteExcelForDetail(getBegDate,getEndDate,dsStuCheckInfo_Detail,nameRow,
dsStuList_Stat.Tables[0].Rows[nameRow][1].ToString());
}
}
mbTwoDates--;
getBegDate = getEndDate.AddDays(1);
getEndDate = EndDate;
}
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
private void butprint_pos_Click(object sender, EventArgs e)
{
Save();
String ExcelFile = Application.StartupPath + "\\门诊预交金收支情况统计.xls";
//Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//xlApp.Visible = true;
//object oMissing = System.Reflection.Missing.Value;
//Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(ExcelFile, 0, true, 5, oMissing, oMissing, true, 1, oMissing, false, false, oMissing, false, oMissing, oMissing);
//Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[1];
//xlWorksheet.PrintPreview(null);
//xlApp.Visible = false;
//xlWorksheet = null;
//xlWorksheet.PrintOut(1, 1, 1, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value);
//return;
Excel.Application xlsApp = new Excel.Application();
//xlsApp.Visible = true;
Excel.Workbooks xlsWbs = xlsApp.Workbooks;
Excel.Workbook xlsWb = xlsWbs.Open(
ExcelFile, Missing.Value, true, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
Excel.Worksheet xlsWs = (Excel.Worksheet)xlsWb.Worksheets[1];
//if (checkBox1.Checked)
// xlsWs.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//页面方向横向
//else
xlsWs.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; //页面方向横向
//objsheet.PageSetup.Zoom = 75;//打印时页面设置,缩放比例
//objsheet.PageSetup.TopMargin = 0; //上边距为0
//objsheet.PageSetup.BottomMargin = 0; //下边距为0
//objsheet.PageSetup.LeftMargin = 0; //左边距为0
//objsheet.PageSetup.RightMargin = 0; //右边距为0
//objsheet.PageSetup.CenterHorizontally = true;//水平居中
//PaperSize p = null;
//PrintDialog pd = new PrintDialog();
//foreach (PaperSize ps in pd.PrinterSettings.PaperSizes)
//{
// if (ps.PaperName.Equals("A4 Plus"))
// p = ps;
//}
//xlsWs.PageSetup.PaperSize = (Excel.XlPaperSize)p.RawKind;
//使excel可见
xlsApp.Visible = true;
//预览
xlsWb.PrintPreview(false);
//保存后退出，并释放资源
xlsApp.DisplayAlerts = false;
// xlsWb.Save();
// xlsWb.SaveAs(ExcelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWb);
xlsWs = null;
xlsWb = null;
xlsApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
xlsApp = null;
//xlsApp.Visible = true;
//打印
//xlsWb.PrintOut(1, 1, 1, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value);
//保存后退出，并释放资源
// xlsApp.DisplayAlerts = false;
// xlsWb.Save();
//xlsWb.SaveAs(ExcelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlsWs = null;
xlsWb = null;
//xlsApp.Quit();
xlsApp = null;
GC.Collect();
}
public void ImportTeaOutDetailsReports(DataSet teaOutInfo, string savePath, DateTime getBegDate, DateTime getEndDate)
{
object[,] objData;
string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.Font m_objFont = null;
System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value;
try
{
if (teaOutInfo.Tables[0].Rows.Count > 0)
{
objData = new Object[teaOutInfo.Tables[0].Rows.Count, 8];
for (int i = 0; i < teaOutInfo.Tables[0].Rows.Count; i++)
{
objData[i, 0] = teaOutInfo.Tables[0].Rows[i][0].ToString();
objData[i, 1] = teaOutInfo.Tables[0].Rows[i][1].ToString();
objData[i, 2] = teaOutInfo.Tables[0].Rows[i][2].ToString();
objData[i, 3] = teaOutInfo.Tables[0].Rows[i][3].ToString();
objData[i, 4] = teaOutInfo.Tables[0].Rows[i][4].ToString();
objData[i, 5] = teaOutInfo.Tables[0].Rows[i][5].ToString();
objData[i, 6] = teaOutInfo.Tables[0].Rows[i][6].ToString();
objData[i, 7] = teaOutInfo.Tables[0].Rows[i][7].ToString();
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\TeacherOutDetails.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3", m_objOpt);
m_objRange = m_objRange.get_Resize(teaOutInfo.Tables[0].Rows.Count, 8);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A" + (teaOutInfo.Tables[0].Rows.Count + 4).ToString(), m_objOpt);
m_objRange.Value = "园所: " + new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A" + (teaOutInfo.Tables[0].Rows.Count + 5).ToString(), m_objOpt);
m_objRange.Value = "统计日期: " + getBegDate.ToString("yyyy.MM.dd") + "-" + getEndDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
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_objFont);
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();
KillProcess();
}
}
public void PrintTeaBaseInfo(TeacherBase tBase, string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\TeacherBaseInfo.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
if (NPrintPicture())
{
m_objPictures = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
m_objPictures.Insert(@"c:\temp.jpg", m_objOpt);
m_objPicture = (Excel.Picture)m_objPictures.Item(1);
m_objRange = m_objSheet.get_Range("H8", "I15");
m_objPicture.Left = (double)m_objRange.Left;
m_objPicture.Top = (double)m_objRange.Top;
m_objPicture.Width = (double)m_objRange.Width;
m_objPicture.Height = (double)m_objRange.Height;
}
//园所名
m_objRange = m_objSheet.get_Range("C6", m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
//打印日期
m_objRange = m_objSheet.get_Range("H32", m_objOpt);
m_objRange.Value = DateTime.Now.Date.ToString("yyyy.MM.dd");
//姓名
m_objRange = m_objSheet.get_Range("C8", m_objOpt);
m_objRange.Value = tBase.TName;
//性别
m_objRange = m_objSheet.get_Range("E8", m_objOpt);
m_objRange.Value = tBase.TSex;
//学历
m_objRange = m_objSheet.get_Range("G8", m_objOpt);
m_objRange.Value = tBase.TCareer;
//家庭电话
m_objRange = m_objSheet.get_Range("C12", m_objOpt);
m_objRange.Value = tBase.THomeTel;
//手机号码
m_objRange = m_objSheet.get_Range("E12", m_objOpt);
m_objRange.Value = tBase.TPhone;
//办公电话
m_objRange = m_objSheet.get_Range("G12", m_objOpt);
m_objRange.Value = tBase.TWorkTel;
//婚否
m_objRange = m_objSheet.get_Range("C16", m_objOpt);
m_objRange.Value = tBase.TMerrige;
//家庭住址
m_objRange = m_objSheet.get_Range("F16", m_objOpt);
m_objRange.Value = tBase.TAddr;
//所属部门
m_objRange = m_objSheet.get_Range("C20", m_objOpt);
m_objRange.Value = tBase.TDepart;
//职务
m_objRange = m_objSheet.get_Range("F20", m_objOpt);
m_objRange.Value = tBase.TDuty;
//职称
m_objRange = m_objSheet.get_Range("H20", m_objOpt);
m_objRange.Value = tBase.TTechnicalPost;
//教师等级
m_objRange = m_objSheet.get_Range("C24", m_objOpt);
m_objRange.Value = tBase.TLevel;
//参加工作时间
m_objRange = m_objSheet.get_Range("G24", m_objOpt);
m_objRange.Value = tBase.TWorkTime.ToString("yyyy-MM-dd");
//入园时间
m_objRange = m_objSheet.get_Range("C28", m_objOpt);
m_objRange.Value = tBase.TEnterTime.ToString("yyyy-MM-dd");
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 e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void PrintNchsHealthPersonal(string getOutputClass,string getOutputName,string getOutputNumber,DataSet dsHealthOutput,
string getBegDate,string getEndDate,string savePath,string getTeacher)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\NchsHealthOutputPersonal",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
for ( int sheetNumbers=1; sheetNumbers<dsHealthOutput.Tables[0].Rows.Count; sheetNumbers++ )
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Copy(Type.Missing,m_objSheet);
}
for ( int row=0; row<dsHealthOutput.Tables[0].Rows.Count; row++)
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(row+1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[row]["info_stuName"].ToString()+
"("+Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_checktime"]).Year.ToString()+
"."+Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_checktime"]).Month.ToString()+")";
m_objRange = m_objSheet.get_Range("E2",m_objOpt);
m_objRange.Value = " "+dsHealthOutput.Tables[0].Rows[row]["info_stuName"].ToString();
m_objRange = m_objSheet.get_Range("G2",m_objOpt);
m_objRange.Value = " "+dsHealthOutput.Tables[0].Rows[row]["info_stuGender"].ToString();
m_objRange = m_objSheet.get_Range("I2",m_objOpt);
m_objRange.Value = " "+Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[row]["info_stuBirthday"]).ToString("yyyy-MM-dd");
m_objRange = m_objSheet.get_Range("B6",m_objOpt);
string realAge = dsHealthOutput.Tables[0].Rows[row]["nchsanaly_realage"].ToString();
if ( realAge.IndexOf(".") < 0 )
{
m_objRange.Value = realAge;
m_objRange = m_objSheet.get_Range("C6",m_objOpt);
m_objRange.Value = 0;
}
else
{
m_objRange.Value = realAge.Substring(0,realAge.IndexOf("."));
m_objRange = m_objSheet.get_Range("C6",m_objOpt);
m_objRange.Value = realAge.Substring(realAge.IndexOf(".")+1);
}
double getHeight = Convert.ToDouble(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_height"]);
m_objRange = m_objSheet.get_Range("B7",m_objOpt);
m_objRange.Value = getHeight;
if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("上") )
{
m_objRange = m_objSheet.get_Range("D7",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("中上") )
{
m_objRange = m_objSheet.get_Range("E7",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("中+") )
{
m_objRange = m_objSheet.get_Range("F7",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("中-") )
{
m_objRange = m_objSheet.get_Range("H7",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("中下") )
{
m_objRange = m_objSheet.get_Range("J7",m_objOpt);
m_objRange.Value = "√";
}
else
{
m_objRange = m_objSheet.get_Range("L7",m_objOpt);
m_objRange.Value = "√";
}
double getWeight = Convert.ToDouble(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weight"]);
m_objRange = m_objSheet.get_Range("B8",m_objOpt);
m_objRange.Value = getWeight;
if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("上") )
{
m_objRange = m_objSheet.get_Range("D8",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("中上") )
{
m_objRange = m_objSheet.get_Range("E8",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("中+") )
{
m_objRange = m_objSheet.get_Range("F8",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("中-") )
{
m_objRange = m_objSheet.get_Range("H8",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("中下") )
{
m_objRange = m_objSheet.get_Range("J8",m_objOpt);
m_objRange.Value = "√";
}
else
{
m_objRange = m_objSheet.get_Range("L8",m_objOpt);
m_objRange.Value = "√";
}
int getGender = dsHealthOutput.Tables[0].Rows[row]["info_stuGender"].ToString().Equals("男")?0:1;
double ageHeightMiddleValue = Convert.ToDouble(new HealthManagementDataAccess().GetNchsAgeHeightMiddleValue(realAge,getGender).Rows[0]["nchs_middle"]);
double ageWeightMiddleValue = Convert.ToDouble(new HealthManagementDataAccess().GetNchsAgeWeightMiddleValue(realAge,getGender).Rows[0]["nchs_middle"]);
string rangeValue = string.Empty;
if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_underweight"].Equals("中度体重低下") || dsHealthOutput.Tables[0].Rows[row]["nchsanaly_underweight"].Equals("重度体重低下") )
{
m_objRange = m_objSheet.get_Range("C12",m_objOpt);
rangeValue = Math.Abs((Convert.ToDouble(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weight"])-ageWeightMiddleValue)/ageWeightMiddleValue).ToString("0.0%")+",";
m_objRange.Value = rangeValue;
m_objRange = m_objSheet.get_Range("A14",m_objOpt);
m_objRange.Value = "√";
}
if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_stunting"].Equals("中度生长迟缓") || dsHealthOutput.Tables[0].Rows[row]["nchsanaly_stunting"].Equals("重度生长迟缓") )
{
m_objRange = m_objSheet.get_Range("C12",m_objOpt);
rangeValue += Math.Abs((Convert.ToDouble(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_height"])-ageHeightMiddleValue)/ageHeightMiddleValue).ToString("0.0%");
m_objRange.Value = rangeValue;
m_objRange = m_objSheet.get_Range("C14",m_objOpt);
m_objRange.Value = "√";
}
else
{
if (rangeValue == string.Empty)
{
m_objRange = m_objSheet.get_Range("F11", m_objOpt);
m_objRange.Value = "等";
m_objRange = m_objSheet.get_Range("C12", m_objOpt);
m_objRange.Value = ageWeightMiddleValue.ToString() + "," + ageHeightMiddleValue.ToString();
m_objRange = m_objSheet.get_Range("E14",m_objOpt);
m_objRange.Value = "√";
}
else
{
m_objRange = m_objSheet.get_Range("C12",m_objOpt);
rangeValue.Replace(",","");
m_objRange.Value = rangeValue;
}
}
double heightWeightMiddleValue = Convert.ToDouble(new HealthManagementDataAccess().GetNchsHeightWeightMiddleValue(getHeight,getGender).Rows[0]["nchs_middle"]);
if ( !dsHealthOutput.Tables[0].Rows[row]["nchsanaly_wasting"].ToString().Equals("正常") )
{
m_objRange = m_objSheet.get_Range("K11", m_objOpt);
m_objRange.Value = "低于";
m_objRange = m_objSheet.get_Range("H13",m_objOpt);
m_objRange.Value = Math.Abs((getWeight - heightWeightMiddleValue)/heightWeightMiddleValue).ToString("0.0%");
if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_wasting"].ToString().Equals("中度消瘦") )
{
m_objRange = m_objSheet.get_Range("H15",m_objOpt);
m_objRange.Value = "√";
}
else
{
m_objRange = m_objSheet.get_Range("G15",m_objOpt);
m_objRange.Value = "√";
}
}
else
{
if ( !dsHealthOutput.Tables[0].Rows[row]["nchsanaly_obesity"].ToString().Equals("正常") )
{
m_objRange = m_objSheet.get_Range("K11", m_objOpt);
m_objRange.Value = "高于";
m_objRange = m_objSheet.get_Range("H13",m_objOpt);
m_objRange.Value = Math.Abs((getWeight - heightWeightMiddleValue)/heightWeightMiddleValue).ToString("0.0%");
if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_obesity"].ToString().Equals("轻度肥胖") )
{
m_objRange = m_objSheet.get_Range("I15",m_objOpt);
m_objRange.Value = "√";
}
else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_obesity"].ToString().Equals("中度肥胖") )
{
m_objRange = m_objSheet.get_Range("J15",m_objOpt);
m_objRange.Value = "√";
}
else
{
m_objRange = m_objSheet.get_Range("K15",m_objOpt);
m_objRange.Value = "√";
}
}
else
{
m_objRange = m_objSheet.get_Range("H13", m_objOpt);
m_objRange.Value = heightWeightMiddleValue.ToString();
m_objRange = m_objSheet.get_Range("L15", m_objOpt);
m_objRange.Value = "√";
}
}
m_objRange = m_objSheet.get_Range("H17",m_objOpt);
m_objRange.Value = getTeacher;
m_objRange = m_objSheet.get_Range("H19",m_objOpt);
m_objRange.Value = DateTime.Now;
m_objRange = m_objSheet.get_Range("H21", m_objOpt);
m_objRange.Value = dsHealthOutput.Tables[0].Rows[row]["nchsanaly_checktime"].ToString();
}
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void VisitInfoPrint(DataSet dsAbsDetailInfo, DateTime getBegDate, DateTime getEndDate, string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\VisitInfo.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for (int row = 0; row < dsAbsDetailInfo.Tables[0].Rows.Count; row++)
{
for (int column = 1; column < dsAbsDetailInfo.Tables[0].Columns.Count - 2; column++)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 6, column + 1], (Excel.Range)m_objSheet.Cells[row + 6, column + 1]);
m_objRange.Value = dsAbsDetailInfo.Tables[0].Rows[row][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
if (row == dsAbsDetailInfo.Tables[0].Rows.Count - 1)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 8, 2], (Excel.Range)m_objSheet.Cells[row + 8, 3]);
m_objRange.MergeCells = true;
m_objRange.Value = "园所信息：";
m_objRange.Font.Bold = true;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 8, 4], (Excel.Range)m_objSheet.Cells[row + 8, 4]);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 2], (Excel.Range)m_objSheet.Cells[row + 9, 3]);
m_objRange.MergeCells = true;
m_objRange.Value = "统计启始时间：";
m_objRange.Font.Bold = true;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 4], (Excel.Range)m_objSheet.Cells[row + 9, 4]);
m_objRange.Value = getBegDate;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 2], (Excel.Range)m_objSheet.Cells[row + 10, 3]);
m_objRange.MergeCells = true;
m_objRange.Value = "统计截止时间：";
m_objRange.Font.Bold = true;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 4], (Excel.Range)m_objSheet.Cells[row + 10, 4]);
m_objRange.Value = getEndDate;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
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 e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void AllStuInfoPrint(DataSet dsExportData,string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\AllStuInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
dv = dsExportData.Tables[0].DefaultView;
for (int row = 1; row < dsExportData.Tables[1].Rows.Count; row++)
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Copy(m_objOpt,m_objSheet);
}
for (int row = 1; row <= dsExportData.Tables[1].Rows.Count; row++)
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(row);
m_objSheet.Name = Convert.ToString(dsExportData.Tables[1].Rows[row-1][0]);
BindingData(m_objSheet.Name);
}
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void StuBaseInfoPrint(Students students,string savePath)
{
try
{
//创建打印副本
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\StudentBaseInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
if ( NPrintPicture() )
{
m_objPictures = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
m_objPictures.Insert(@"c:\temp.jpg",m_objOpt);
m_objPicture = (Excel.Picture)m_objPictures.Item(1);
m_objRange = m_objSheet.get_Range("G8","H15");
m_objPicture.Left = (double)m_objRange.Left;
m_objPicture.Top = (double)m_objRange.Top;
m_objPicture.Width = (double)m_objRange.Width;
m_objPicture.Height = (double)m_objRange.Height;
}
//打印园所名字
m_objRange = m_objSheet.get_Range("B4",m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
//打印日期
m_objRange = m_objSheet.get_Range("G40",m_objOpt);
m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd");
//打印学号
m_objRange = m_objSheet.get_Range("B7",m_objOpt);
m_objRange.Value = students.Number;
//打印姓名
m_objRange = m_objSheet.get_Range("B8",m_objOpt);
m_objRange.Value = students.Name;
//打印性别
m_objRange = m_objSheet.get_Range("D8",m_objOpt);
m_objRange.Value = students.Gender;
//打印出生日
m_objRange = m_objSheet.get_Range("F8",m_objOpt);
m_objRange.Value = students.EntryDate;
//打印生源
m_objRange = m_objSheet.get_Range("B10",m_objOpt);
m_objRange.Value = students.Origin;
//打印入托方式
m_objRange = m_objSheet.get_Range("D10",m_objOpt);
m_objRange.Value = students.EntryStatus;
//打印入园日期
m_objRange = m_objSheet.get_Range("F10",m_objOpt);
m_objRange.Value = students.EntryDate;
//打印离园日期
m_objRange = m_objSheet.get_Range("B12",m_objOpt);
if ( students.LeaveDate == DateTime.MinValue )
m_objRange.Value = "";
else
m_objRange.Value = students.LeaveDate;
//打印国籍
m_objRange = m_objSheet.get_Range("B18",m_objOpt);
m_objRange.Value = students.Nationality;
//打印邮编
m_objRange = m_objSheet.get_Range("D18",m_objOpt);
m_objRange.Value = students.ZipCode;
//打印街道
m_objRange = m_objSheet.get_Range("F18",m_objOpt);
m_objRange.Value = students.JieDao;
//打印里委
m_objRange = m_objSheet.get_Range("F20",m_objOpt);
m_objRange.Value = students.LiWei;
//打印籍贯
m_objRange = m_objSheet.get_Range("B20",m_objOpt);
m_objRange.Value = students.Native;
//打印家庭住址
m_objRange = m_objSheet.get_Range("B22",m_objOpt);
m_objRange.Value = students.FamilyAddr;
//打印户口地址
m_objRange = m_objSheet.get_Range("B24",m_objOpt);
m_objRange.Value = students.HuKouAddr;
//打印病史记录
m_objRange = m_objSheet.get_Range("B26",m_objOpt);
m_objRange.Value = students.SickHistory;
//打印父亲姓名
m_objRange = m_objSheet.get_Range("B31",m_objOpt);
m_objRange.Value = students.FatherName;
//打印父亲联系电话
m_objRange = m_objSheet.get_Range("F31",m_objOpt);
m_objRange.Value = students.FatherPhone;
//打印父亲工作地址
m_objRange = m_objSheet.get_Range("B33",m_objOpt);
m_objRange.Value = students.FatherWorkPlace;
//打印母亲姓名
m_objRange = m_objSheet.get_Range("B35",m_objOpt);
m_objRange.Value = students.MotherName;
//打印母亲联系电话
m_objRange = m_objSheet.get_Range("F35",m_objOpt);
m_objRange.Value = students.MotherPhone;
//打印母亲工作地址
m_objRange = m_objSheet.get_Range("B37",m_objOpt);
m_objRange.Value = students.MotherWorkPlace;
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
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();
}
}
public void PrintTeaBaseInfo(TeacherBase tBase,string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\TeacherBaseInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
if ( NPrintPicture() )
{
m_objPictures = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
m_objPictures.Insert(@"c:\temp.jpg",m_objOpt);
m_objPicture = (Excel.Picture)m_objPictures.Item(1);
m_objRange = m_objSheet.get_Range("H8","I15");
m_objPicture.Left = (double)m_objRange.Left;
m_objPicture.Top = (double)m_objRange.Top;
m_objPicture.Width = (double)m_objRange.Width;
m_objPicture.Height = (double)m_objRange.Height;
}
//园所名
m_objRange = m_objSheet.get_Range("C6",m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
//打印日期
m_objRange = m_objSheet.get_Range("H32",m_objOpt);
m_objRange.Value = DateTime.Now.Date.ToString("yyyy.MM.dd");
//姓名
m_objRange = m_objSheet.get_Range("C8",m_objOpt);
m_objRange.Value = tBase.TName;
//性别
m_objRange = m_objSheet.get_Range("E8",m_objOpt);
m_objRange.Value = tBase.TSex;
//学历
m_objRange = m_objSheet.get_Range("G8",m_objOpt);
m_objRange.Value = tBase.TCareer;
//家庭电话
m_objRange = m_objSheet.get_Range("C12",m_objOpt);
m_objRange.Value = tBase.THomeTel;
//手机号码
m_objRange = m_objSheet.get_Range("E12",m_objOpt);
m_objRange.Value = tBase.TPhone;
//办公电话
m_objRange = m_objSheet.get_Range("G12",m_objOpt);
m_objRange.Value = tBase.TWorkTel;
//婚否
m_objRange = m_objSheet.get_Range("C16",m_objOpt);
m_objRange.Value = tBase.TMerrige;
//家庭住址
m_objRange = m_objSheet.get_Range("F16",m_objOpt);
m_objRange.Value = tBase.TAddr;
//所属部门
m_objRange = m_objSheet.get_Range("C20",m_objOpt);
m_objRange.Value = tBase.TDepart;
//职务
m_objRange = m_objSheet.get_Range("F20",m_objOpt);
m_objRange.Value = tBase.TDuty;
//职称
m_objRange = m_objSheet.get_Range("H20",m_objOpt);
m_objRange.Value = tBase.TTechnicalPost;
//教师等级
m_objRange = m_objSheet.get_Range("C24",m_objOpt);
m_objRange.Value = tBase.TLevel;
//参加工作时间
m_objRange = m_objSheet.get_Range("G24",m_objOpt);
m_objRange.Value = tBase.TWorkTime.ToString("yyyy-MM-dd");
//入园时间
m_objRange = m_objSheet.get_Range("C28",m_objOpt);
m_objRange.Value = tBase.TEnterTime.ToString("yyyy-MM-dd");
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
// 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 void PrintHealth(bool printType1st,bool printType2nd,bool printType3rd,string getOutputGrade,
string getOutputClass,string getOutputName,string getOutputNumber,DataSet dsHealthOutput,
string getBegDate,string getEndDate,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.Open(excelPath+@"report\health.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
if ( printType1st )
{
if ( !getOutputName.Equals("") || !getOutputNumber.Equals(""))
{
if ( dsHealthOutput.Tables[0].Rows.Count > 0 )
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[0][1].ToString();
setTitle(printType1st);
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ )
{
if (j <= 12)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j==2 )
m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+
dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
{
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
}
}
else
{
if (j == 14)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
if (j == 17)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
}
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
setJumpBoard(dsHealthOutput.Tables[0].Rows.Count,dsHealthOutput,dsHealthOutput.Tables[0].Rows.Count-1);
}
}
else
{
if ( !getOutputGrade.Equals("") )
{
if ( getOutputClass.Equals("") )
{
int rowBeg = 4;
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
if ( !hasSheet(dsHealthOutput.Tables[0].Rows[i][1].ToString()) )
{
rowBeg = 4;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[i][1].ToString();
setTitle(printType1st);
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ )
{
if (j <= 12)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j==2 )
m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+
dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
{
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
}
}
else
{
if (j == 14)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,12],m_objSheet.Cells[rowBeg,12]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
if (j == 17)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,13],m_objSheet.Cells[rowBeg,13]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
}
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
rowBeg++;
}
else
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ )
{
if (j <= 12)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j==2 )
m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+
dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
{
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
}
}
else
{
if (j == 14)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,12],m_objSheet.Cells[rowBeg,12]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
if (j == 17)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,13],m_objSheet.Cells[rowBeg,13]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
}
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
rowBeg++;
}
if ( i < dsHealthOutput.Tables[0].Rows.Count - 1 )
{
if ( !dsHealthOutput.Tables[0].Rows[i][1].ToString().Equals(dsHealthOutput.Tables[0].Rows[i+1][1].ToString()))
setJumpBoard(rowBeg-4,dsHealthOutput,i);
}
else
setJumpBoard(rowBeg-4,dsHealthOutput,i);
}
}
else
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[0][1].ToString();
setTitle(printType1st);
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ )
{
if (j <= 12)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j==2 )
m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+
dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
{
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
}
}
else
{
if (j == 14)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
if (j == 17)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
}
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
setJumpBoard(dsHealthOutput.Tables[0].Rows.Count,dsHealthOutput,dsHealthOutput.Tables[0].Rows.Count-1);
}
}
else
{
int rowBeg = 4;
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
if ( !hasSheet(dsHealthOutput.Tables[0].Rows[i][1].ToString()) )
{
rowBeg = 4;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[i][1].ToString();
setTitle(printType1st);
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ )
{
if (j <= 12)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j==2 )
m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+
dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
{
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
}
}
else
{
if (j == 14)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,12],m_objSheet.Cells[rowBeg,12]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
if (j == 17)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,13],m_objSheet.Cells[rowBeg,13]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
}
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
rowBeg++;
}
else
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ )
{
if (j <= 12)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j==2 )
m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+
dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
{
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
}
}
else
{
if (j == 14)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,12],m_objSheet.Cells[rowBeg,12]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
if (j == 17)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,13],m_objSheet.Cells[rowBeg,13]);
//m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
}
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
rowBeg++;
}
if ( i < dsHealthOutput.Tables[0].Rows.Count - 1 )
{
if ( !dsHealthOutput.Tables[0].Rows[i][1].ToString().Equals(dsHealthOutput.Tables[0].Rows[i+1][1].ToString()))
setJumpBoard(rowBeg-4,dsHealthOutput,i);
}
else
setJumpBoard(rowBeg-4,dsHealthOutput,i);
}
}
}
}
else
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = "未划分班级";
setTitle(printType1st);
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ )
{
if (j <= 12)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j==2 )
m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-1].ToString()+")"+
dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
{
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
}
}
else
{
if (j == 14)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
if (j == 17)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
}
}
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
}
DataView dv = dsHealthOutput.Tables[0].DefaultView;
dv.RowFilter = "HealthAnaly_isFat = 1";
if (dv.Count > 0)
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheets.Add(Type.Missing, m_objSheet, 1, Type.Missing);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = "肥胖儿情况汇总表";
m_objRange = m_objSheet.get_Range("D1",m_objOpt);
m_objRange.Value = " 肥胖儿情况汇总表";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 11;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange.Value = "班级";
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.RowHeight = 21.75;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("B3",m_objOpt);
m_objRange.Value = "姓名";
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("C3",m_objOpt);
m_objRange.Value = "性别";
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.ColumnWidth = 5;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("D3",m_objOpt);
m_objRange.Value = "出生日期";
m_objRange.ColumnWidth = 12;
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("E3",m_objOpt);
m_objRange.Value = "体检日期";
m_objRange.ColumnWidth = 12;
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("F3",m_objOpt);
m_objRange.Value = "年龄";
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("G3",m_objOpt);
m_objRange.Value = "身高";
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("H3",m_objOpt);
m_objRange.Value = "评价";
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("I3",m_objOpt);
m_objRange.Value = "体重";
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("J3",m_objOpt);
m_objRange.Value = "评价";
m_objRange.Font.Bold = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("K3",m_objOpt);
m_objRange.Value = "身高测体重评价";
m_objRange.Font.Bold = true;
m_objRange.ColumnWidth = 12;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("L3",m_objOpt);
m_objRange.Value = "WHO肥胖 X值";
m_objRange.Font.Bold = true;
m_objRange.ColumnWidth = 12;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("M3",m_objOpt);
m_objRange.Font.Bold = true;
m_objRange.Value = "肥胖度";
m_objRange.ColumnWidth = 13;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("N3",m_objOpt);
m_objRange.Font.Bold = true;
m_objRange.Value = "超重10-19.9%";
m_objRange.ColumnWidth = 13;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("O3",m_objOpt);
m_objRange.Font.Bold = true;
m_objRange.Value = "轻度肥胖20-29.9%";
m_objRange.ColumnWidth = 13;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("P3",m_objOpt);
m_objRange.Font.Bold = true;
m_objRange.Value = "中度肥胖30-49.9%";
m_objRange.ColumnWidth = 13;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range("Q3",m_objOpt);
m_objRange.Font.Bold = true;
m_objRange.Value = "重度肥胖﹥50%";
m_objRange.ColumnWidth = 13;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
object[,] objData = new Object[dv.Count,17];
for ( int i=0; i<dv.Count; i++ )
{
objData[i,0] = dv[i][1].ToString();
objData[i,1] = dv[i][2].ToString();
objData[i,2] = dv[i][3].ToString();
objData[i,3] = Convert.ToDateTime(dv[i][5]).ToString("yyyy-MM-dd");
objData[i,4] = Convert.ToDateTime(dv[i][4]).ToString("yyyy-MM-dd");
objData[i,5] = "'" + dv[i][6].ToString();
objData[i,6] = dv[i][7].ToString();
objData[i,7] = dv[i][8].ToString();
objData[i,8] = dv[i][9].ToString();
objData[i,9] = dv[i][10].ToString();
objData[i,10] = dv[i][14].ToString();
objData[i,11] = dv[i][17].ToString();
// objData[i,10] = dv[i][17].ToString();
// objData[i,11] = dv[i][14].ToString();
objData[i,12] = dv[i][16].ToString();
int fatLevel = Convert.ToInt32(dv[i][18]);
switch (fatLevel)
{
case 1 :objData[i,13] = "√";
break;
case 2 :objData[i,14] = "√";
break;
case 3 :objData[i,15] = "√";
break;
case 4 :objData[i,16] = "√";
break;
default:
break;
}
}
m_objRange = m_objSheet.get_Range("A4",m_objOpt);
m_objRange = m_objRange.get_Resize(dv.Count,17);
m_objRange.NumberFormatLocal = "@";
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A" + (4 + dv.Count).ToString(), m_objOpt);
m_objRange.Value = "总计";
m_objRange = m_objSheet.get_Range("A" + (4 + dv.Count).ToString(), "M" + (4 + dv.Count).ToString());
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("N" + (4 + dv.Count).ToString(), m_objOpt);
m_objRange.Value = dsHealthOutput.Tables[0].Select("HealthAnaly_FatLevel=1").Length;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("O" + (4 + dv.Count).ToString(), m_objOpt);
m_objRange.Value = dsHealthOutput.Tables[0].Select("HealthAnaly_FatLevel=2").Length;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("P" + (4 + dv.Count).ToString(), m_objOpt);
m_objRange.Value = dsHealthOutput.Tables[0].Select("HealthAnaly_FatLevel=3").Length;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("Q" + (4 + dv.Count).ToString(), m_objOpt);
m_objRange.Value = dsHealthOutput.Tables[0].Select("HealthAnaly_FatLevel=4").Length;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
}
if ( printType2nd )
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item("均值汇总");
DataSet dsClass = new StuInfoDataAccess().GetClassList("","","");
for ( int i=0; i<dsClass.Tables[0].Rows.Count; i++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+7,1],m_objSheet.Cells[i+7,1]);
m_objRange.Value = dsClass.Tables[0].Rows[i][1].ToString();
m_objRange.RowHeight = 25;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
}
m_objRange = m_objSheet.get_Range("B7","O"+(dsClass.Tables[0].Rows.Count+7).ToString()); //初始化单元格内容
m_objRange.Value = "0";
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange = m_objSheet.get_Range("A"+(dsClass.Tables[0].Rows.Count+7).ToString(),m_objOpt);
m_objRange.Value = "合计";
m_objRange.RowHeight = 25;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
int heightCalcTotal = 0;
int weightCalcTotal = 0;
for ( int row=0; row<dsClass.Tables[0].Rows.Count; row++ )
{
int perRowHeightTotal = 0;
int perRowWeightTotal = 0;
for ( int column=2; column<=17; column++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6,column],m_objSheet.Cells[6,column]);
string getValue = m_objRange.Value.ToString();
if ( column < 10 )
{
DataSet dsHeightAnalyStat = new HealthManagementDataAccess().GetHeightAnalyStat(getBegDate,getEndDate,getValue,dsClass.Tables[0].Rows[row][1].ToString());
if ( dsHeightAnalyStat.Tables[0].Rows.Count != 0 )
{
perRowHeightTotal += Convert.ToInt32(dsHeightAnalyStat.Tables[0].Rows[0][0]);
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row+7,column],m_objSheet.Cells[row+7,column]);
m_objRange.Value = dsHeightAnalyStat.Tables[0].Rows[0][0].ToString();
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Columns.Font.Size = 12;
}
if (column == 9)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row+7,column],m_objSheet.Cells[row+7,column]);
m_objRange.Value = perRowHeightTotal.ToString();
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Columns.Font.Size = 12;
}
if ( row+1 == dsClass.Tables[0].Rows.Count )
{
if (column < 9)
{
DataSet dsHeightTotal = new HealthManagementDataAccess().GetHeightAnalyTotal(getBegDate,getEndDate,getValue);
if ( dsHeightTotal.Tables[0].Rows.Count != 0 )
{
heightCalcTotal += Convert.ToInt32(dsHeightTotal.Tables[0].Rows[0][0]);
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column],
m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column]);
m_objRange.Value = dsHeightTotal.Tables[0].Rows[0][0].ToString();
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Columns.Font.Size = 12;
}
}
else
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column],
m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column]);
m_objRange.Value = heightCalcTotal.ToString();
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Columns.Font.Size = 12;
}
}
}
else
{
DataSet dsWeightStat = new HealthManagementDataAccess().GetWeightAnalyStat(getBegDate,getEndDate,getValue,dsClass.Tables[0].Rows[row][1].ToString());
if ( dsWeightStat.Tables[0].Rows.Count != 0 )
{
perRowWeightTotal += Convert.ToInt32(dsWeightStat.Tables[0].Rows[0][0]);
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row+7,column],m_objSheet.Cells[row+7,column]);
m_objRange.Value = dsWeightStat.Tables[0].Rows[0][0].ToString();
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Columns.Font.Size = 12;
}
if (column == 17)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row+7,column],m_objSheet.Cells[row+7,column]);
m_objRange.Value = perRowWeightTotal.ToString();
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Columns.Font.Size = 12;
}
if ( row+1 == dsClass.Tables[0].Rows.Count )
{
if (column < 17)
{
DataSet dsWeightTotal = new HealthManagementDataAccess().GetWeightAnalyTotal(getBegDate,getEndDate,getValue);
if ( dsWeightTotal.Tables[0].Rows.Count != 0 )
{
weightCalcTotal += Convert.ToInt32(dsWeightTotal.Tables[0].Rows[0][0]);
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column],
m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column]);
m_objRange.Value = dsWeightTotal.Tables[0].Rows[0][0].ToString();
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Columns.Font.Size = 12;
}
}
else
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column],
m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column]);
m_objRange.Value = weightCalcTotal.ToString();
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Columns.Font.Size = 12;
}
}
}
}
}
m_objRange = m_objSheet.get_Range("K"+(dsClass.Tables[0].Rows.Count+9).ToString(),m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString()+" "+
DateTime.Now.ToString("yyyy.MM");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 16;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
if ( printType3rd )
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item("超均值汇总");
DataSet dsGrade = new StuInfoDataAccess().GetGradeList("","");
DataTable dtFatStat = this.GetFatStat(getBegDate, getEndDate);
for ( int i=0; i<dsGrade.Tables[0].Rows.Count; i++ )
{
int fatNumber = 0;
string fatDesc = string.Empty;
switch ( i )
{
case 0:
{
DataView dvFatStat = dtFatStat.DefaultView;
dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString());
if (dvFatStat.Count > 0)
{
fatNumber = Convert.ToInt32(dvFatStat[0][1]);
fatDesc = string.Format("(肥胖{0})", fatNumber);
}
m_objRange = m_objSheet.get_Range("B5",m_objOpt);
m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc;
m_objRange = m_objSheet.get_Range("B8",m_objOpt);
m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("C8",m_objOpt);
m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString();
m_objRange = m_objSheet.get_Range("D8",m_objOpt);
m_objRange.Value = ((getOverH-fatNumber)/getSum).ToString(); //身高评测
m_objRange = m_objSheet.get_Range("E8",m_objOpt);
m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("F8",m_objOpt);
m_objRange.Value = (getOverW/getSum).ToString();
}
break;
case 1:
{
DataView dvFatStat = dtFatStat.DefaultView;
dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString());
if (dvFatStat.Count > 0)
{
fatNumber = Convert.ToInt32(dvFatStat[0][1]);
fatDesc = string.Format("(肥胖{0})", fatNumber);
}
m_objRange = m_objSheet.get_Range("G5",m_objOpt);
m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc;
m_objRange = m_objSheet.get_Range("G8",m_objOpt);
m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("H8",m_objOpt);
m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString();
m_objRange = m_objSheet.get_Range("I8",m_objOpt);
m_objRange.Value = ((getOverH-fatNumber)/getSum).ToString(); //身高评测
m_objRange = m_objSheet.get_Range("J8",m_objOpt);
m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("K8",m_objOpt);
m_objRange.Value = (getOverW/getSum).ToString();
}
break;
case 2:
{
DataView dvFatStat = dtFatStat.DefaultView;
dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString());
if (dvFatStat.Count > 0)
{
fatNumber = Convert.ToInt32(dvFatStat[0][1]);
fatDesc = string.Format("(肥胖{0})", fatNumber);
}
m_objRange = m_objSheet.get_Range("B11",m_objOpt);
m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc;
m_objRange = m_objSheet.get_Range("B17",m_objOpt);
m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("C17",m_objOpt);
m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString();
m_objRange = m_objSheet.get_Range("D17",m_objOpt);
m_objRange.Value = ((getOverH - fatNumber)/getSum).ToString(); //身高评测
m_objRange = m_objSheet.get_Range("E17",m_objOpt);
m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("F17",m_objOpt);
m_objRange.Value = (getOverW/getSum).ToString();
}
break;
case 3:
{
DataView dvFatStat = dtFatStat.DefaultView;
dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString());
if (dvFatStat.Count > 0)
{
fatNumber = Convert.ToInt32(dvFatStat[0][1]);
fatDesc = string.Format("(肥胖{0})", fatNumber);
}
m_objRange = m_objSheet.get_Range("G11",m_objOpt);
m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc;
m_objRange = m_objSheet.get_Range("G17",m_objOpt);
m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("H17",m_objOpt);
m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString();
m_objRange = m_objSheet.get_Range("I17",m_objOpt);
m_objRange.Value = ((getOverH - fatNumber)/getSum).ToString(); //身高评测
m_objRange = m_objSheet.get_Range("J17",m_objOpt);
m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("K17",m_objOpt);
m_objRange.Value = (getOverW/getSum).ToString();
}
break;
case 4:
{
DataView dvFatStat = dtFatStat.DefaultView;
dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString());
if (dvFatStat.Count > 0)
{
fatNumber = Convert.ToInt32(dvFatStat[0][1]);
fatDesc = string.Format("(肥胖{0})", fatNumber);
}
m_objRange = m_objSheet.get_Range("B20",m_objOpt);
m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc;
m_objRange = m_objSheet.get_Range("B26",m_objOpt);
m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("C26",m_objOpt);
m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString();
m_objRange = m_objSheet.get_Range("D26",m_objOpt);
m_objRange.Value = ((getOverH - fatNumber)/getSum).ToString(); //身高评测
m_objRange = m_objSheet.get_Range("E26",m_objOpt);
m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString();
m_objRange = m_objSheet.get_Range("F26",m_objOpt);
m_objRange.Value = (getOverW/getSum).ToString();
}
break;
}
}
DataSet dsWhoTotal = new HealthManagementDataAccess().GetWhoTotal(getBegDate,getEndDate);
m_objRange = m_objSheet.get_Range("A8",m_objOpt);
m_objRange.Value = dsWhoTotal.Tables[0].Rows[0][0].ToString();
m_objRange = m_objSheet.get_Range("H28",m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString()+" "+DateTime.Now.ToString("yyyy.MM");
}
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void FinanceStatPrint(DataTable data, string className, DateTime date, string savePath)
{
DataTable dtPresents = new FinanInfoDataAccess().GetStudentPresents(date, className);
if (dtPresents == null || dtPresents.Rows.Count == 0)
{
throw new Exception("没有要使用的数据！");
}
else
{
data.Columns.AddRange(new DataColumn[]{ new DataColumn("小计", Type.GetType("System.Double")),
new DataColumn("stuPresent", Type.GetType("System.String")),
new DataColumn("stuAbsent", Type.GetType("System.String"))});
if (dtPresents.Rows.Count != data.Rows.Count)
{
throw new Exception("检测到数据完整性错误，请尝试重新生成数据！");
}
else
{
for (int i = 0; i < dtPresents.Rows.Count; i++)
{
data.Rows[i]["小计"] = 0;
data.Rows[i]["stuPresent"] = dtPresents.Rows[i]["times"];
data.Rows[i]["stuAbsent"] = dtPresents.Rows[i]["times_abs"];
}
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\FinanceStat.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = new object[data.Rows.Count + 1, data.Columns.Count + 4];
for(int row = 0; row < data.Rows.Count; row++)
{
objData[row, 0] = row + 1;
for (int column = 0; column < data.Columns.Count - 3; column++)
{
if (column <= 2)
{
objData[row, column + 1] = data.Rows[row][column];
}
else if(column == 3)
{
objData[row, column + 1] = data.Rows[row][data.Columns.Count - 2];
objData[row, column + 2] = data.Rows[row][data.Columns.Count - 1];
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column >= 4 && column < 7)
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column == 7)
{
double temp1 = Convert.ToDouble(data.Rows[row][column - 4]) +
Convert.ToDouble(data.Rows[row][column - 3]) + Convert.ToDouble(data.Rows[row][column - 2]) +
Convert.ToDouble(data.Rows[row][column - 1]);
objData[row, column + 3] = temp1;
double temp2 = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp2;
objData[data.Rows.Count, column + 3] = temp1 + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
objData[data.Rows.Count, column + 4] = temp2 + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
else
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp;
objData[data.Rows.Count, column + 4] = temp + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
}
}
m_objRange = m_objSheet.get_Range("A6", m_objOpt);
m_objRange = m_objRange.get_Resize(data.Rows.Count + 1, data.Columns.Count + 1);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objRange = m_objSheet.get_Range("G3", m_objOpt);
m_objRange.Value = "各项费用";
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 7], m_objSheet.Cells[4, data.Columns.Count]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 12;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, data.Columns.Count + 1], m_objSheet.Cells[4, data.Columns.Count + 1]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
object[, ] objColumn = new object[1, data.Columns.Count - 5];
for (int column = 3; column < data.Columns.Count - 3; column++ )
{
if (column < 7)
{
objColumn[0, column - 3] = data.Columns[column].ColumnName;
}
if (column == 7)
{
objColumn[0, column - 3] = data.Columns[data.Columns.Count - 3].ColumnName;
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
else
{
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
}
m_objRange = m_objSheet.get_Range("G5", m_objOpt);
m_objRange = m_objRange.get_Resize(1, data.Columns.Count - 5);
m_objRange.Value = objColumn;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10.5;
for (int column = 5; column < data.Columns.Count - 3; column++)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, column + 2], m_objSheet.Cells[5 + data.Rows.Count, column + 2]);
object i = m_objRange.Calculate();
string s = "asf";
}
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);
}
}
/// <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 GenerateStatsReports(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
dir += "\\年级汇总";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(dates);
var reportTemplate = new ReportTemplate();
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport3.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
foreach (var groupByClass in groupByGrade.Value.GetStatsCollection())
{
try
{
int studentCount = groupByGrade.Value.GetStudentCount(groupByClass.Key);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = string.Format("{0}汇总", groupByClass.Key);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}（{1}{2}人） “快乐成长”记录情况汇总（各项表现数量及所占比率）", gardenName, groupByClass.Key, studentCount);
//生成年龄段表头
var kvp = reportTemplate.GetAgeDesc(groupByGrade.Key, "report3");
foreach (var pos in kvp.Value.Split(','))
{
m_objSheet.get_Range(pos).Value = kvp.Key;
}
var ignorePos = reportTemplate.GetIgnorePos(groupByGrade.Key, ReportTemplate.IgnoreType.Ignore2);
if (!string.IsNullOrEmpty(ignorePos))
{
foreach (var split in ignorePos.Split(','))
{
if (!string.IsNullOrEmpty(split))
{
var column = split[0].ToString();
var row = split.Substring(1);
var lastRange = m_objSheet.get_Range(string.Format("J{0}", row));
var ignoreRange = m_objSheet.get_Range(split);
ignoreRange.Interior.ColorIndex = lastRange.Interior.ColorIndex;
ignoreRange.Value = lastRange.Value;
}
}
}
DoGenerateStatsReports(gardenName, groupByGrade.Key, groupByClass.Key, studentCount, groupByClass.Value, reportTemplate);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\{1}.xls", dir, groupByGrade.Key), 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);
notify();
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
/// <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 GenerateReportsPersonByPerson(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\班主任报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(dates);
var reportTemplate = new ReportTemplate();
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
var gradeDir = string.Format(@"{0}\{1}个人汇总", dir, groupByGrade.Key);
if (!Directory.Exists(gradeDir))
Directory.CreateDirectory(gradeDir);
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport1.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
//生成年龄段表头
var kvp = reportTemplate.GetAgeDesc(groupByGrade.Key, "report1");
foreach (var pos in kvp.Value.Split(','))
{
m_objSheet.get_Range(pos).Value = kvp.Key;
}
DoGenerateReportsPersonByPerson(gardenName, groupByClass.Key, groupByClass.Value, m_objSheet, reportTemplate, dates[0], dates[dates.Length - 1]);
m_objBook.SaveAs(string.Format("{0}\\{1}.xls", gradeDir, groupByClass.Key), 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);
notify();
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void PrintKidStatInfo(string getGrade,string getClass,string getName,string getNumber,string savePath)
{
try
{
DataSet dsStuList_Stat = null;
object[,] objData = null;
DateTime getBegDate = BegDate;
DateTime getEndDate = EndDate;
string classInfo = string.Empty;
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
if ( getName.Equals("") && getNumber.Equals("") )
{
dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_GradeClass(getGrade,getClass);
classInfo = new ClassesDataAccess().GetClassInfo(Convert.ToInt32(getClass),Convert.ToInt32(getGrade)).Tables[0].Rows[0][1].ToString();
}
else
{
dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_NameNumber(getName,getNumber);
classInfo = dsStuList_Stat.Tables[0].Rows[0][2].ToString();
}
}
if ( dsStuList_Stat.Tables[0].Rows.Count > 0 )
{
objData = new object[dsStuList_Stat.Tables[0].Rows.Count,8];
for ( int nameRow=0; nameRow<dsStuList_Stat.Tables[0].Rows.Count; nameRow++ )
{
DataSet dsStuCheckInfo_Stat = null;
int times = 0;
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
dsStuCheckInfo_Stat = stuCheckInfoDataAccess.GetStuCheckInfo_Stat(dsStuList_Stat.Tables[0].Rows[nameRow][0].ToString(),getBegDate,getEndDate,ref times);
objData[nameRow,0] = dsStuList_Stat.Tables[0].Rows[nameRow][1].ToString();
objData[nameRow,1] = 0;
objData[nameRow,2] = 0;
objData[nameRow,3] = 0;
objData[nameRow,6] = 0;
int total = SetAttendDays();
int health = 0;
int ill = 0;
int watch = 0;
int absence = 0;
DataRow[] drHealth = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=0");
if (drHealth.Length > 0)
{
health = Convert.ToInt32(drHealth[0]["stateCount"]);
}
DataRow[] drWatch = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=2");
if (drWatch.Length > 0)
{
watch = Convert.ToInt32(drWatch[0]["stateCount"]);
}
DataRow[] drIll = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=3");
if (drIll.Length > 0)
{
ill = Convert.ToInt32(drIll[0]["stateCount"]);
}
absence = total - health - watch - ill;
objData[nameRow,1] = string.Format("{0} ({1})",
health, ((double)health/(double)total).ToString("0.00%"));
objData[nameRow,2] = string.Format("{0} ({1})",
watch, ((double)watch/(double)total).ToString("0.00%"));
objData[nameRow,3] = string.Format("{0} ({1})",
ill, ((double)ill/(double)total).ToString("0.00%"));
objData[nameRow,6] = string.Format("{0} ({1})",
absence, ((double)absence/(double)total).ToString("0.00%"));
// foreach ( DataRow stateRow in dsStuCheckInfo_Stat.Tables[0].Rows )
// {
//
//
//
// if ( Convert.ToInt32(stateRow[1]) == 0 )
// {
// objData[nameRow,1] = stateRow[0].ToString()
// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
// continue;
// }
//
// else if ( Convert.ToInt32(stateRow[1]) == 2 )
// {
// objData[nameRow,2] = stateRow[0].ToString()
// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
// continue;
// }
//
// else if ( Convert.ToInt32(stateRow[1]) == 3 )
// {
// objData[nameRow,3] = stateRow[0].ToString()
// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
// continue;
// }
//
// else if ( Convert.ToInt32(stateRow[1]) == -1 )
// {
//// int total = SetAttendDays();
//// int health =
////
//// objData[nameRow,6] = stateRow[0].ToString()
//// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
//// continue;
//
// objData[nameRow,6] = stateRow[0].ToString()
// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
// continue;
// }
// //晨检状态错误
// else
// {
// Util.WriteLog("晨检状态错误！",Util.EXCEPTION_LOG_TITLE);
// break;
// }
// }
objData[nameRow,4] = SetAttendDays();
objData[nameRow,5] = times;
objData[nameRow,7] = (times/(double)SetAttendDays()).ToString("0.00%");
}
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\MorningCheckInfoKidStat.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A4",m_objOpt);
m_objRange = m_objRange.get_Resize(dsStuList_Stat.Tables[0].Rows.Count,8);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
WriteJumpboard(dsStuList_Stat.Tables[0].Rows.Count,classInfo);
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void GenerateCheckReportsClassByClass(string gardenName, GrowUpReportDataAccess.CheckReportHierarchy hierarchy, int checkInDays, DateTime date, string dir, Action notify)
{
var reportTemplate = new ReportTemplate();
var gradeDir = string.Format(@"{0}\管理员报表\幼儿体验汇总", dir);
if (!Directory.Exists(gradeDir))
Directory.CreateDirectory(gradeDir);
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport5.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = groupByClass.Key;
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}{1}宝宝体验汇总表 （{2}）应出勤{3}天", gardenName, groupByClass.Key, date.ToString("yyyy-MM月"), checkInDays);
DoGenerateCheckReportsClassByClass(gardenName, groupByClass.Key, groupByClass.Value, m_objSheet, reportTemplate, date, checkInDays);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\{1}({2}).xls", gradeDir, groupByGrade.Key, date.ToString("yyyy.MM.dd")), 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);
notify();
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void PrintMorningCheckInfo(DataSet dsMorningCheckInfo,string getBegDate,string getEndDate,string savePath)
{
try
{
object[,] morningCheckInfoData = null;
if ( dsMorningCheckInfo.Tables[0].Rows.Count > 0 )
{
morningCheckInfoData = new object[dsMorningCheckInfo.Tables[0].Rows.Count,5];
for ( int i=0; i<dsMorningCheckInfo.Tables[0].Rows.Count; i++ )
{
morningCheckInfoData[i,0] = dsMorningCheckInfo.Tables[0].Rows[i][2];
morningCheckInfoData[i,1] = dsMorningCheckInfo.Tables[0].Rows[i][0];
morningCheckInfoData[i,2] = dsMorningCheckInfo.Tables[0].Rows[i][1];
morningCheckInfoData[i,3] = dsMorningCheckInfo.Tables[0].Rows[i][3];
morningCheckInfoData[i,4] = dsMorningCheckInfo.Tables[0].Rows[i][4];
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\MorningCheckInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("B3",m_objOpt);
m_objRange = m_objRange.get_Resize(dsMorningCheckInfo.Tables[0].Rows.Count,5);
m_objRange.Value = morningCheckInfoData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Font.Size = 9;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 4, 2], (Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 4, 2]);
m_objRange.Value = "园所信息:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 4, 3], (Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 4, 3]);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 5, 2], (Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 5, 2]);
m_objRange.Value = "统计日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 5, 3], (Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 5, 3]);
m_objRange.Value = Convert.ToDateTime(getBegDate).ToString("yyyy.MM.dd")+" -- "+Convert.ToDateTime(getEndDate).ToString("yyyy.MM.dd");
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
}
}
public void GenerateCheckReportsGradeByGrade(string gardenName, GrowUpReportDataAccess.CheckReportHierarchy hierarchy, int checkInDays, DateTime date, string dir, Action notify)
{
var reportTemplate = new ReportTemplate();
var gradeDir = string.Format(@"{0}\管理员报表\幼儿体验汇总", dir);
if (!Directory.Exists(gradeDir))
Directory.CreateDirectory(gradeDir);
try
{
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport6.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}幼儿在园体验汇总表 （{1}） 应出勤{2}天", gardenName, date.ToString("yyyy-MM月"), checkInDays);
checkInDays = checkInDays == 0 ? 1 : checkInDays;
int offset = 1;
int total1 = 0; int total2 = 0; int total3 = 0; int total4 = 0; int total5 = 0; int total6 = 0; int total7 = 0;
int total8 = 0; int total9 = 0; int total10 = 0; int total11 = 0; int total12 = 0; int total13 = 0; int total14 = 0;
int total15 = 0; int total16 = 0; int total17 = 0; int total18 = 0; int total19 = 0; int total20 = 0;
int total21 = 0; int total22 = 0; int totalStudentCount = 0;
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
if (offset >= 2)
{
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown);
}
DoGenerateCheckReportsGradeByGrade(groupByClass.Key, groupByClass.Value, reportTemplate, checkInDays, offset,
ref total1, ref total2,ref total3, ref total4,ref total5, ref total6,ref total7, ref total8,ref total8, ref total10,ref total11, ref total12,
ref total13, ref total14,ref total15, ref total16,ref total17, ref total18,ref total19, ref total20,ref total21, ref total22, ref totalStudentCount);
offset++;
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
}
offset = offset - 2;
WriteCheckReportCell(reportTemplate, "s_total", totalStudentCount.ToString(), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "0_total", string.Format("{0}({1})", total1, (total1 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_total", string.Format("{0}({1})", total2, (total2 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_total", string.Format("{0}({1})", total3, (total3 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "-1_total", string.Format("{0}({1})", total4, ((checkInDays * totalStudentCount - total1 - total2 - total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_1_total", string.Format("{0}({1})", total5, (total5 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_2_total", string.Format("{0}({1})", total6, (total6 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_3_total", string.Format("{0}({1})", total7, (total7 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_1_total", string.Format("{0}({1})", total8, (total8 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_2_total", string.Format("{0}({1})", total9, (total9 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_3_total", string.Format("{0}({1})", total10, (total10 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_1_total", string.Format("{0}({1})", total11, (total11 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_2_total", string.Format("{0}({1})", total12, (total12 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_3_total", string.Format("{0}({1})", total13, (total13 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_1_total", string.Format("{0}({1})", total14, (total14 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_2_total", string.Format("{0}({1})", total15, (total15 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_3_total", string.Format("{0}({1})", total16, (total16 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_1_total", string.Format("{0}({1})", total17, (total17 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_2_total", string.Format("{0}({1})", total18, (total18 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_3_total", string.Format("{0}({1})", total19, (total19 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_1_total", string.Format("{0}({1})", total20, (total20 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_2_total", string.Format("{0}({1})", total21, (total21 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_3_total", string.Format("{0}({1})", total22, (total22 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "0_all", string.Format("{0}({1})", total1 + total2 + total3, ((total1 + total2 + total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_all", string.Format("{0}({1})", total4, ((checkInDays * totalStudentCount - total1 - total2 - total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_all", string.Format("{0}({1})", total5 + total6 + total7, ((total5 + total6 + total7) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_all", string.Format("{0}({1})", total8 + total9 + total10, ((total8 + total9 + total10) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_all", string.Format("{0}({1})", total11 + total12 + total13, ((total11 + total12 + total13) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_all", string.Format("{0}({1})", total14 + total15 + total16, ((total14 + total15 + total16) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_all", string.Format("{0}({1})", total17 + total18 + total19, ((total17 + total18 + total9) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "7_all", string.Format("{0}({1})", total20 + total21 + total22, ((total20 + total21 + total22) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
m_objSheet.get_Range(string.Format("A{0}", 7 + offset)).Value = string.Format("统计日期：{0}", date.ToString("yyyy.MM.dd"));
m_objBook.SaveAs(string.Format("{0}\\全园体验({1}).xls", gradeDir, date.ToString("yyyy.MM.dd")), 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);
notify();
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void StuBaseInfoPrint(Students students, string savePath)
{
try
{
//创建打印副本
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\StudentBaseInfo.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
if (NPrintPicture())
{
m_objPictures = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
m_objPictures.Insert(@"c:\temp.jpg", m_objOpt);
m_objPicture = (Excel.Picture)m_objPictures.Item(1);
m_objRange = m_objSheet.get_Range("G8", "H15");
m_objPicture.Left = (double)m_objRange.Left;
m_objPicture.Top = (double)m_objRange.Top;
m_objPicture.Width = (double)m_objRange.Width;
m_objPicture.Height = (double)m_objRange.Height;
}
//打印园所名字
m_objRange = m_objSheet.get_Range("B4", m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
//打印日期
m_objRange = m_objSheet.get_Range("G40", m_objOpt);
m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd");
//打印学号
m_objRange = m_objSheet.get_Range("B7", m_objOpt);
m_objRange.Value = students.Number;
//打印姓名
m_objRange = m_objSheet.get_Range("B8", m_objOpt);
m_objRange.Value = students.Name;
//打印性别
m_objRange = m_objSheet.get_Range("D8", m_objOpt);
m_objRange.Value = students.Gender;
//打印出生日
m_objRange = m_objSheet.get_Range("F8", m_objOpt);
m_objRange.Value = students.EntryDate;
//打印生源
m_objRange = m_objSheet.get_Range("B10", m_objOpt);
m_objRange.Value = students.Origin;
//打印入托方式
m_objRange = m_objSheet.get_Range("D10", m_objOpt);
m_objRange.Value = students.EntryStatus;
//打印入园日期
m_objRange = m_objSheet.get_Range("F10", m_objOpt);
m_objRange.Value = students.EntryDate;
//打印离园日期
m_objRange = m_objSheet.get_Range("B12", m_objOpt);
if (students.LeaveDate == DateTime.MinValue)
{
m_objRange.Value = "";
}
else
{
m_objRange.Value = students.LeaveDate;
}
//打印国籍
m_objRange = m_objSheet.get_Range("B18", m_objOpt);
m_objRange.Value = students.Nationality;
//打印邮编
m_objRange = m_objSheet.get_Range("D18", m_objOpt);
m_objRange.Value = students.ZipCode;
//打印街道
m_objRange = m_objSheet.get_Range("F18", m_objOpt);
m_objRange.Value = students.JieDao;
//打印里委
m_objRange = m_objSheet.get_Range("F20", m_objOpt);
m_objRange.Value = students.LiWei;
//打印籍贯
m_objRange = m_objSheet.get_Range("B20", m_objOpt);
m_objRange.Value = students.Native;
//打印家庭住址
m_objRange = m_objSheet.get_Range("B22", m_objOpt);
m_objRange.Value = students.FamilyAddr;
//打印户口地址
m_objRange = m_objSheet.get_Range("B24", m_objOpt);
m_objRange.Value = students.HuKouAddr;
//打印病史记录
m_objRange = m_objSheet.get_Range("B26", m_objOpt);
m_objRange.Value = students.SickHistory;
//打印父亲姓名
m_objRange = m_objSheet.get_Range("B31", m_objOpt);
m_objRange.Value = students.FatherName;
//打印父亲联系电话
m_objRange = m_objSheet.get_Range("F31", m_objOpt);
m_objRange.Value = students.FatherPhone;
//打印父亲工作地址
m_objRange = m_objSheet.get_Range("B33", m_objOpt);
m_objRange.Value = students.FatherWorkPlace;
//打印母亲姓名
m_objRange = m_objSheet.get_Range("B35", m_objOpt);
m_objRange.Value = students.MotherName;
//打印母亲联系电话
m_objRange = m_objSheet.get_Range("F35", m_objOpt);
m_objRange.Value = students.MotherPhone;
//打印母亲工作地址
m_objRange = m_objSheet.get_Range("B37", m_objOpt);
m_objRange.Value = students.MotherWorkPlace;
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 e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
//全日观察打印
public void PrintAbnormalRecord(DataSet dsDailyWatch,string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\ObserveReport.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = null;
if ( dsDailyWatch.Tables[0].Rows.Count > 0 )
{
objData = new Object[dsDailyWatch.Tables[0].Rows.Count,8];
for ( int i=0; i<dsDailyWatch.Tables[0].Rows.Count; i++ )
{
objData[i,0] = dsDailyWatch.Tables[0].Rows[i]["observetime"].ToString();
objData[i,1] = dsDailyWatch.Tables[0].Rows[i]["stu_name"].ToString();
objData[i,2] = "体温"+dsDailyWatch.Tables[0].Rows[i]["morningReg_heat"].ToString()+","
+"精神"+dsDailyWatch.Tables[0].Rows[i]["morningReg_spirit"].ToString()+","
+"口腔"+dsDailyWatch.Tables[0].Rows[i]["morningReg_mouth"].ToString()+","
+"皮肤"+dsDailyWatch.Tables[0].Rows[i]["morningReg_skin"].ToString();
objData[i,3] = dsDailyWatch.Tables[0].Rows[i]["morningReg_genearchTold"].ToString();
objData[i,4] = dsDailyWatch.Tables[0].Rows[i]["morningReg_treat"].ToString();
objData[i,5] = "一日活动"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_movement"].ToString()+","
+"精神"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_spirit"].ToString()+","
+"食欲"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_appetite"].ToString()+","
+"睡眠"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_sleep"].ToString()+","
+"大小便"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_stool"].ToString()+","
+"咳嗽"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_cough"].ToString()+","
+"其他情况："+dsDailyWatch.Tables[0].Rows[i]["dailyReg_else"].ToString();
if ( Convert.ToBoolean(dsDailyWatch.Tables[0].Rows[i]["dailyReg_ctrlMoveTreat"]) )
{
objData[i,6] += "控制运动量,";
}
if ( Convert.ToBoolean(dsDailyWatch.Tables[0].Rows[i]["dailyReg_tendTreat"]) )
{
objData[i,6] += "注意生活护理,";
}
if ( Convert.ToBoolean(dsDailyWatch.Tables[0].Rows[i]["dailyReg_seafoodTreat"]) )
{
objData[i,6] += "忌海鲜,";
}
if ( Convert.ToBoolean(dsDailyWatch.Tables[0].Rows[i]["dailyReg_measureHeat"]) )
{
objData[i,6] += "按时测体温.";
}
objData[i,7] = dsDailyWatch.Tables[0].Rows[i]["dailyReg_teacherSign"].ToString();
}
m_objRange = m_objSheet.get_Range("A5",m_objOpt);
m_objRange = m_objRange.get_Resize(dsDailyWatch.Tables[0].Rows.Count,8);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void RealtimeMorningPrint(DataSet dsRealtimeInfoStat_Teacher, string savePath)
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\RealtimeMorningInfoStat_Teacher.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = null;
if (dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count > 0)
{
objData = new object[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count, 7];
for (int i = 0; i < dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count; i++)
{
objData[i, 0] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][0].ToString();
objData[i, 1] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][1].ToString();
objData[i, 2] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][2].ToString();
objData[i, 3] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][3].ToString();
objData[i, 4] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][4].ToString();
objData[i, 5] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][5].ToString();
objData[i, 6] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][6].ToString();
}
}
m_objRange = m_objSheet.get_Range("A7", m_objOpt);
m_objRange = m_objRange.get_Resize(dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count, 7);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 1], m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 1]);
m_objRange.Value = "园所信息:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 2], m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 3]);
m_objRange.MergeCells = true;;
m_objRange.Value = " " + new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 1], m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 1]);
m_objRange.Value = "统计日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 2], m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 3]);
m_objRange.MergeCells = true;
m_objRange.Value = " " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
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 e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
//服药记录打印
public void PrintDoseInfo(DataSet dsDoseInfo,string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\DiagnosisReport.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = null;
if ( dsDoseInfo.Tables[0].Rows.Count > 0 )
{
objData = new Object[dsDoseInfo.Tables[0].Rows.Count,8];
for ( int i=0; i<dsDoseInfo.Tables[0].Rows.Count; i++ )
{
objData[i,0] = dsDoseInfo.Tables[0].Rows[i][6].ToString();
objData[i,1] = dsDoseInfo.Tables[0].Rows[i][7].ToString();
objData[i,2] = dsDoseInfo.Tables[0].Rows[i][8].ToString();
objData[i,3] = dsDoseInfo.Tables[0].Rows[i][9].ToString();
objData[i,4] = dsDoseInfo.Tables[0].Rows[i][2].ToString();
objData[i,5] = dsDoseInfo.Tables[0].Rows[i][3].ToString();
objData[i,6] = dsDoseInfo.Tables[0].Rows[i][4].ToString()
+" "+dsDoseInfo.Tables[0].Rows[i][10].ToString();
objData[i,7] = dsDoseInfo.Tables[0].Rows[i][5].ToString();
}
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(dsDoseInfo.Tables[0].Rows.Count,8);
for ( int i=2,m=1; i<=m_objRange.Rows.Count; i++,m++ )
{
if(objData[m,1].ToString().Equals(objData[m-1,1].ToString())&&
objData[m,6].ToString().Substring(0,10).Equals(objData[m-1,6].ToString().Substring(0,10)))
{
string startAMergeAddress = "A"+((int)(i-1)).ToString();
string endAMergeAddress = "A"+i.ToString();
m_objRange.get_Range(startAMergeAddress,endAMergeAddress).Merge(m_objOpt);
string startBMergeAddress = "B"+((int)(i-1)).ToString();
string endBMergeAddress = "B"+i.ToString();
m_objRange.get_Range(startBMergeAddress,endBMergeAddress).Merge(m_objOpt);
string startCMergeAddress = "C"+((int)(i-1)).ToString();
string endCMergeAddress = "C"+i.ToString();
m_objRange.get_Range(startCMergeAddress,endCMergeAddress).Merge(m_objOpt);
string startDMergeAddress = "D"+((int)(i-1)).ToString();
string endDMergeAddress = "D"+i.ToString();
m_objRange.get_Range(startDMergeAddress,endDMergeAddress).Merge(m_objOpt);
}
}
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
}
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
/// <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 PrintNchsHealthSummary(bool printType1st,bool printType2nd,string getOutputGrade,
string getOutputClass,string getOutputName,string getOutputNumber,DataSet dsHealthOutput,
string getBegDate,string getEndDate,string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\NchsHealthOutputSummary",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
if ( printType1st )
{
if ( !getOutputName.Equals("") || !getOutputNumber.Equals(""))
{
if ( dsHealthOutput.Tables[0].Rows.Count > 0 )
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[0]["info_className"].ToString();
SetNchsTitle(printType1st);
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
}
setJumpBoard(dsHealthOutput.Tables[0].Rows.Count,dsHealthOutput,dsHealthOutput.Tables[0].Rows.Count-1);
}
}
else
{
if ( !getOutputGrade.Equals("") )
{
if ( getOutputClass.Equals("") )
{
int rowBeg = 4;
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
if ( !hasSheet(dsHealthOutput.Tables[0].Rows[i][1].ToString()) )
{
rowBeg = 4;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[i][1].ToString();
SetNchsTitle(printType1st);
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
rowBeg++;
}
else
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
rowBeg++;
}
if ( i < dsHealthOutput.Tables[0].Rows.Count - 1 )
{
if ( !dsHealthOutput.Tables[0].Rows[i][1].ToString().Equals(dsHealthOutput.Tables[0].Rows[i+1][1].ToString()))
setJumpBoard(rowBeg-4,dsHealthOutput,i);
}
else
setJumpBoard(rowBeg-4,dsHealthOutput,i);
}
}
else
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[0][1].ToString();
SetNchsTitle(printType1st);
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
}
setJumpBoard(dsHealthOutput.Tables[0].Rows.Count,dsHealthOutput,dsHealthOutput.Tables[0].Rows.Count-1);
}
}
else
{
int rowBeg = 4;
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
if ( !hasSheet(dsHealthOutput.Tables[0].Rows[i][1].ToString()) )
{
rowBeg = 4;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsHealthOutput.Tables[0].Rows[i][1].ToString();
SetNchsTitle(printType1st);
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
rowBeg++;
}
else
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
rowBeg++;
}
if ( i < dsHealthOutput.Tables[0].Rows.Count - 1 )
{
if ( !dsHealthOutput.Tables[0].Rows[i][1].ToString().Equals(dsHealthOutput.Tables[0].Rows[i+1][1].ToString()))
setJumpBoard(rowBeg-4,dsHealthOutput,i);
}
else
setJumpBoard(rowBeg-4,dsHealthOutput,i);
}
}
}
}
else
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = "未划分班级";
SetNchsTitle(printType1st);
for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ )
{
for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]);
m_objRange.NumberFormatLocal = "@";
if ( j==2 )
m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-1].ToString()+")"+
dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
{
if ( j != 5 )
m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString());
else
m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd"));
}
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
}
}
if ( printType2nd )
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item("均值汇总");
DataSet dsClass = new StuInfoDataAccess().GetClassList("","","");
for ( int i=0; i<dsClass.Tables[0].Rows.Count; i++ )
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+6,1],m_objSheet.Cells[i+6,1]);
m_objRange.Value = dsClass.Tables[0].Rows[i][1].ToString();
m_objRange.RowHeight = 23.25;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
}
m_objRange = m_objSheet.get_Range("A"+(dsClass.Tables[0].Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = "合计";
m_objRange.RowHeight = 23.25;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
object[,] objData = new object[dsClass.Tables[0].Rows.Count+1,31];
int heightResult_1 = 0;
int heightResult_2 = 0;
int heightResult_3 = 0;
int heightResult_4 = 0;
int heightResult_5 = 0;
int heightResult_6 = 0;
int upMiddleHeightResult = 0;
int weightResult_1 = 0;
int weightResult_2 = 0;
int weightResult_3 = 0;
int weightResult_4 = 0;
int weightResult_5 = 0;
int weightResult_6 = 0;
int upMiddleWeightResult = 0;
int totalStudentNumbers = 0;
int totalCheckNumbers = 0;
int totalHeightResult_1 = 0;
int totalHeightResult_2 = 0;
int totalHeightResult_3 = 0;
int totalHeightResult_4 = 0;
int totalHeightResult_5 = 0;
int totalHeightResult_6 = 0;
int totalUpMiddleHeightResult = 0;
int totalWeightResult_1 = 0;
int totalWeightResult_2 = 0;
int totalWeightResult_3 = 0;
int totalWeightResult_4 = 0;
int totalWeightResult_5 = 0;
int totalWeightResult_6 = 0;
int totalUpMiddleWeightResult = 0;
for ( int row=0; row<dsClass.Tables[0].Rows.Count; row++ )
{
string getAddr = dsClass.Tables[0].Rows[row]["info_machineAddr"].ToString();
DataSet dsStudentsOnSummary = new HealthManagementDataAccess().GetNchsStudentsOnSummary(getBegDate,getEndDate,getAddr);
int studentNumbers = Convert.ToInt32(dsStudentsOnSummary.Tables[0].Rows[0][0]);
totalStudentNumbers += studentNumbers;
if ( studentNumbers == 0 )
{
for ( int column=0; column<=30; column++ )
{
objData[row,column] = 0;
}
continue;
}
objData[row,0] = studentNumbers;
int checkNumbers = Convert.ToInt32(dsStudentsOnSummary.Tables[1].Rows[0][0]);
totalCheckNumbers += checkNumbers;
if ( checkNumbers == 0 )
{
for ( int column=1; column<=30; column++ )
{
objData[row,column] = 0;
}
}
else
{
objData[row,1] = checkNumbers;
objData[row,2] = ((double)checkNumbers/(double)studentNumbers*100).ToString("0.00");
heightResult_1 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"下",getAddr).Rows[0][0]);
totalHeightResult_1 += heightResult_1;
objData[row,3] = heightResult_1;
objData[row,4] = ((double)heightResult_1/(double)checkNumbers*100).ToString("0.00");
heightResult_2 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"中下",getAddr).Rows[0][0]);
totalHeightResult_2 += heightResult_2;
objData[row,5] = heightResult_2;
objData[row,6] = ((double)heightResult_2/(double)checkNumbers*100).ToString("0.00");
heightResult_3 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"中-",getAddr).Rows[0][0]);
totalHeightResult_3 += heightResult_3;
objData[row,7] = heightResult_3;
objData[row,8] = ((double)heightResult_3/(double)checkNumbers*100).ToString("0.00");
heightResult_4 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"中+",getAddr).Rows[0][0]);
totalHeightResult_4 += heightResult_4;
objData[row,9] = heightResult_4;
objData[row,10] = ((double)heightResult_4/(double)checkNumbers*100).ToString("0.00");
heightResult_5 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"中上",getAddr).Rows[0][0]);
totalHeightResult_5 += heightResult_5;
objData[row,11] = heightResult_5;
objData[row,12] = ((double)heightResult_5/(double)checkNumbers*100).ToString("0.00");
heightResult_6 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"上",getAddr).Rows[0][0]);
totalHeightResult_6 += heightResult_6;
objData[row,13] = heightResult_6;
objData[row,14] = ((double)heightResult_6/(double)checkNumbers*100).ToString("0.00");
upMiddleHeightResult = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightUpMiddleOnSummary(getBegDate,getEndDate,getAddr).Rows[0][0]);
totalUpMiddleHeightResult += upMiddleHeightResult;
objData[row,15] = upMiddleHeightResult;
objData[row,16] = ((double)upMiddleHeightResult/(double)checkNumbers*100).ToString("0.00");
weightResult_1 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"下",getAddr).Rows[0][0]);
totalWeightResult_1 += weightResult_1;
objData[row,17] = weightResult_1;
objData[row,18] = ((double)weightResult_1/(double)checkNumbers*100).ToString("0.00");
weightResult_2 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"中下",getAddr).Rows[0][0]);
totalWeightResult_2 += weightResult_2;
objData[row,19] = weightResult_2;
objData[row,20] = ((double)weightResult_2/(double)checkNumbers*100).ToString("0.00");
weightResult_3 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"中-",getAddr).Rows[0][0]);
totalWeightResult_3 += weightResult_3;
objData[row,21] = weightResult_3;
objData[row,22] = ((double)weightResult_3/(double)checkNumbers*100).ToString("0.00");
weightResult_4 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"中+",getAddr).Rows[0][0]);
totalWeightResult_4 += weightResult_4;
objData[row,23] = weightResult_4;
objData[row,24] = ((double)weightResult_4/(double)checkNumbers*100).ToString("0.00");
weightResult_5 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"中上",getAddr).Rows[0][0]);
totalWeightResult_5 += weightResult_5;
objData[row,25] = weightResult_5;
objData[row,26] = ((double)weightResult_5/(double)checkNumbers*100).ToString("0.00");
weightResult_6 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"上",getAddr).Rows[0][0]);
totalWeightResult_6 += weightResult_6;
objData[row,27] = weightResult_6;
objData[row,28] = ((double)weightResult_6/(double)checkNumbers*100).ToString("0.00");
upMiddleWeightResult = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightUpMiddleOnSummary(getBegDate,getEndDate,getAddr).Rows[0][0]);
totalUpMiddleWeightResult += upMiddleHeightResult;
objData[row,29] = upMiddleHeightResult;
objData[row,30] = ((double)upMiddleWeightResult/(double)checkNumbers*100).ToString("0.00");
}
}
if ( totalStudentNumbers !=0 && totalCheckNumbers != 0 )
{
objData[dsClass.Tables[0].Rows.Count,0] = totalStudentNumbers;
objData[dsClass.Tables[0].Rows.Count,1] = totalCheckNumbers;
objData[dsClass.Tables[0].Rows.Count,2] = ((double)totalCheckNumbers/(double)totalStudentNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,3] = totalHeightResult_1;
objData[dsClass.Tables[0].Rows.Count,4] = ((double)totalHeightResult_1/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,5] = totalHeightResult_2;
objData[dsClass.Tables[0].Rows.Count,6] = ((double)totalHeightResult_2/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,7] = totalHeightResult_3;
objData[dsClass.Tables[0].Rows.Count,8] = ((double)totalHeightResult_3/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,9] = totalHeightResult_4;
objData[dsClass.Tables[0].Rows.Count,10] = ((double)totalHeightResult_4/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,11] = totalHeightResult_5;
objData[dsClass.Tables[0].Rows.Count,12] = ((double)totalHeightResult_5/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,13] = totalHeightResult_6;
objData[dsClass.Tables[0].Rows.Count,14] = ((double)totalHeightResult_6/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,15] = totalUpMiddleHeightResult;
objData[dsClass.Tables[0].Rows.Count,16] = ((double)totalUpMiddleHeightResult/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,17] = totalWeightResult_1;
objData[dsClass.Tables[0].Rows.Count,18] = ((double)totalWeightResult_1/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,19] = totalWeightResult_2;
objData[dsClass.Tables[0].Rows.Count,20] = ((double)totalWeightResult_2/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,21] = totalWeightResult_3;
objData[dsClass.Tables[0].Rows.Count,22] = ((double)totalWeightResult_3/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,23] = totalWeightResult_4;
objData[dsClass.Tables[0].Rows.Count,24] = ((double)totalWeightResult_4/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,25] = totalWeightResult_5;
objData[dsClass.Tables[0].Rows.Count,26] = ((double)totalWeightResult_5/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,27] = totalWeightResult_6;
objData[dsClass.Tables[0].Rows.Count,28] = ((double)totalWeightResult_6/(double)totalCheckNumbers*100).ToString("0.00");
objData[dsClass.Tables[0].Rows.Count,29] = totalUpMiddleWeightResult;
objData[dsClass.Tables[0].Rows.Count,30] = ((double)totalUpMiddleWeightResult/(double)totalCheckNumbers*100).ToString("0.00");
}
else objData = null;
m_objRange = m_objSheet.get_Range("B6",m_objOpt);
m_objRange = m_objRange.get_Resize(dsClass.Tables[0].Rows.Count+1,31);
if ( objData != null ) m_objRange.Value = objData;
else m_objRange.Value = 0;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objFont = m_objRange.Font;
}
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void ReadUpdateGradeInfo(ref DataTable dtUpdateGradeInfo,string getPath)
{
OleDbDataAdapter myAdp = null;
OleDbConnection oleConn = null;
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;
oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器
+ ";Extended Properties=Excel 8.0");
oleConn.Open();
myAdp = new OleDbDataAdapter("select * from [年班升级$]", oleConn); //获取第一张工作表的内容
myAdp.Fill(dtUpdateGradeInfo);
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();
}
}
public void ImportCardExcelFile(string id,string name,string grade,
string atClass,bool isStu,string savePath)
{
if(grade.Equals("全部"))
{
grade = string.Empty;
}
if(atClass.Equals("全部"))
{
atClass = string.Empty;
}
DataSet cardInfo;
object[,] objData;
string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.Font m_objFont = null;
System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value;
try
{
if(isStu)
{
using(CardInfoDA cardInfoDA = new CardInfoDA())
{
cardInfo = cardInfoDA.GetStuCardNumberForExcel(id,name,grade,atClass);
}
if(cardInfo.Tables[0].Rows.Count>0)
{
objData = new Object[cardInfo.Tables[0].Rows.Count,7];
for(int i=0;i<cardInfo.Tables[0].Rows.Count;i++)
{
objData[i,0] = cardInfo.Tables[0].Rows[i][0].ToString();
objData[i,1] = cardInfo.Tables[0].Rows[i][1].ToString();
objData[i,2] = cardInfo.Tables[0].Rows[i][2].ToString();
objData[i,3] = cardInfo.Tables[0].Rows[i][3].ToString();
objData[i,4] = cardInfo.Tables[0].Rows[i][4].ToString();
objData[i,5] = cardInfo.Tables[0].Rows[i][5].ToString();
objData[i,6] = cardInfo.Tables[0].Rows[i][6].ToString();
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath+@"report\StudentCardInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(cardInfo.Tables[0].Rows.Count,7);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
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_objFont);
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);
}
}
else
{
using(CardInfoDA cardInfoDA = new CardInfoDA())
{
cardInfo = cardInfoDA.GetTeaCardNumberForExcel(id,name,grade,atClass);
}
if(cardInfo.Tables[0].Rows.Count>0)
{
objData = new Object[cardInfo.Tables[0].Rows.Count,6];
for(int i=0;i<cardInfo.Tables[0].Rows.Count;i++)
{
objData[i,0] = cardInfo.Tables[0].Rows[i][0].ToString();
objData[i,1] = cardInfo.Tables[0].Rows[i][1].ToString();
objData[i,2] = cardInfo.Tables[0].Rows[i][2].ToString();
objData[i,3] = cardInfo.Tables[0].Rows[i][3].ToString();
objData[i,4] = cardInfo.Tables[0].Rows[i][4].ToString();
objData[i,5] = cardInfo.Tables[0].Rows[i][5].ToString();
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath+@"report\TeacherCardInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(cardInfo.Tables[0].Rows.Count,6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
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_objFont);
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 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();
}
}
public void GenerateReportsByClass(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
dir += @"\班级统计汇总";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var reportTemplate = new ReportTemplate();
//按月分组
foreach(var date in dates)
{
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(date);
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport2.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = string.Format("{0}统计汇总", groupByClass.Key);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}{1}统计表", gardenName, groupByClass.Key);
DoGenerateReportsByClass(groupByGrade.Key, groupByClass.Value, reportTemplate, date);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\{1}统计汇总({2}).xls", dir, groupByGrade.Key, date.ToString("yyyy.MM")), 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);
notify();
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
}
public void FinanMgmtInfoPrint(DataSet dsFinanInfo,string savePath)
{
int rowStart = 6;
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\FinanMgmtInfo.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Copy(m_objSheet,m_objOpt);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsFinanInfo.Tables[0].Rows[0][2].ToString();
for ( int finanRow = 0;finanRow < dsFinanInfo.Tables[0].Rows.Count; finanRow ++ )
{
if ( !dsFinanInfo.Tables[0].Rows[finanRow][2].ToString().Equals(m_objSheet.Name) )
{
SetJumpBoard(rowStart,dsFinanInfo.Tables[0].Rows[finanRow-1][2].ToString());
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Copy(m_objSheet,m_objOpt);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count-1);
m_objSheet.Name = dsFinanInfo.Tables[0].Rows[finanRow][2].ToString();
rowStart = 6;
}
for ( int column = 1;column < dsFinanInfo.Tables[0].Columns.Count; column ++)
{
switch ( column )
{
case 1: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column],m_objSheet.Cells[rowStart+1,column]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 3: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart+1,column-1]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 4: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart+1,column-1]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 5: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 6: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 7: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 8: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 9: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 10: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 11: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-4],m_objSheet.Cells[rowStart,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 12: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-5],m_objSheet.Cells[rowStart+1,column-5]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 13: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-5],m_objSheet.Cells[rowStart+1,column-5]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
}
}
rowStart += 2;
}
SetJumpBoard(rowStart,dsFinanInfo.Tables[0].Rows[dsFinanInfo.Tables[0].Rows.Count-1][2].ToString());
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
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 e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void GenerateReportsByGrade(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
dir += @"\年级统计汇总";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var reportTemplate = new ReportTemplate();
//按月分组
foreach (var date in dates)
{
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(date);
try
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport2.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = string.Format("{0}部统计汇总", groupByGrade.Key);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}{1}部统计表", gardenName, groupByGrade.Key);
int offset = 1;
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
if (offset >= 2)
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1]).EntireRow.Insert(Excel.XlDirection.xlDown);
var itemCount = DoGenerateReportsByGrade(groupByClass.Key, groupByClass.Value, reportTemplate, date, offset);
m_objSheet.get_Range(string.Format("C{0}", 5 + offset)).Value = itemCount;
offset++;
//offset多加了一次，下面计算的时候，OFFSET都会减一
var ignorePos = reportTemplate.GetIgnorePos(groupByGrade.Key, ReportTemplate.IgnoreType.Ignore1);
if (!string.IsNullOrEmpty(ignorePos))
{
var regex = new Regex(@"(?<col>\w+)(?<row>\d)");
foreach (var split in ignorePos.Split(','))
{
var match = regex.Match(split);
var column = match.Groups["col"].Value;
var row = match.Groups["row"].Value;
var range = m_objSheet.Range[string.Format("{0}6:{0}{1}", column, Convert.ToInt32(row) + offset)];
range.Value = "/";
range = m_objSheet.Range[string.Format("{0}5:{0}{1}", column, Convert.ToInt32(row) + offset)];
range.Interior.ColorIndex = 27; //http://dmcritchie.mvps.org/excel/colors.htm
range.EntireColumn.Hidden = true;
}
}
m_objSheet.get_Range(string.Format("A{0}", 6 + offset)).Value = string.Format("统计日期：{0}", date.ToString("yyyy.MM"));
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\年级统计汇总({1}).xls", dir, date.ToString("yyyy.MM")), 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);
notify();
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
}
