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();
}
private void CloseAllSheet()
{
try
{
workbook1.Close(true, Missing.Value, Missing.Value);
excelApp1.Quit();
workbook2.Close(true, Missing.Value, Missing.Value);
excelApp2.Quit();
releaseObject(excelApp1);
releaseObject(excelApp2);
releaseObject(workbook1);
releaseObject(workbook2);
new_workbook.Save();
new_workbook.Close(true, Missing.Value, Missing.Value);
newapp.Quit();
releaseObject(newapp);
releaseObject(new_workbook);
Application.Current.Shutdown();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static DataTable DataTableFromXlsx(string Path, string password = "")
{
Excel.Application objXL = null;
Excel.Workbook objWB = null;
objXL = new Excel.Application();
objWB = objXL.Workbooks.Open(Path);
Excel.Worksheet objSHT = objWB.Worksheets[1];
int rows = objSHT.UsedRange.Rows.Count;
int cols = objSHT.UsedRange.Columns.Count;
DataTable dt = new DataTable();
int noofrow = 1;
for (int c = 1; c <= cols; c++)
{
string colname = objSHT.Cells[1, c].Text;
dt.Columns.Add(colname);
noofrow = 2;
}
for (int r = noofrow; r <= rows; r++)
{
DataRow dr = dt.NewRow();
for (int c = 1; c <= cols; c++)
{
dr[c - 1] = objSHT.Cells[r, c].Text;
}
dt.Rows.Add(dr);
}
objWB.Close();
objXL.Quit();
return(dt);
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="pPath"></param>
/// <returns></returns>
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return(sheetName);
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="pPath"></param>
/// <returns></returns>
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
public void GenerateReport(IEnumerable <StudentDto> students)
{
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbook book_template = app.Workbooks.Open(@"шаблон_отчета.xlsx");
Excel._Worksheet sheet_template = book_template.Sheets["отчет"];
foreach (var ob in students)
{
//1. Создаем объкт LearnerReport из БД
LearnerReport report = new LearnerReport
{
SNS = $"{ob.surname} {ob.name} {ob.SecondName}",
SchoolName = ob.SchoolName,
ClassName = ob.ClassName,
TestResult5 = ob.TestResult5
};
//2. Экспорт объкта LearnerReport в шаблон xlsx
sheet_template.Range["C4"].Value2 = report.SNS;
sheet_template.Range["C5"].Value2 = report.SchoolName;
sheet_template.Range["C6"].Value2 = report.ClassName;
sheet_template.Range["C9"].Value2 = report.TestResult5;
//3. Сохраняем полученный файл в .pdf на рабочем столе
string file_name = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{report.SNS}.pdf";
sheet_template.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file_name);
}
book_template.Close(0);
book_template = null;
app.Quit();
app = null;
}
private void btnFill_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
int cCnt = 0;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
MessageBox.Show(str);
}
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void XtraGridTo(GridView grv, int DongBatDau, int CotBatDau)
{
try
{
oXL = new Excel.Application();
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//oSheet.UsedRange.Cells.Borders.Value = 1;
int SoCotGroup = 0;
foreach (GridColumn grc in grv.Columns)
{
if (grc.GroupIndex >= 0)
{
SoCotGroup++;
}
}
// Mở file Excel
oXL.Visible = true;
}
catch (Exception ex)
{
oXL.Quit();
throw ex;
}
}
//把Excel文档转换为Pdf文档
#region
private static void ExcelToPdf(string ExcelFileName, string PdfFileName)
{
//Object missing = System.Reflection.Missing.Value;
//Object ReadOnly = (object)true;
//ET.Application app = new ET.Application();
//app.Visible = false;
//ET.workbook doc = (ET.workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//doc.ExportPdf(PdfFileName, "", "");
//doc.Close(missing, missing, missing);
//app.Quit();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//app = null;
Object missing = System.Reflection.Missing.Value;
Object ReadOnly = (object)true;
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook doc = (Excel.Workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
doc.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, PdfFileName);
doc.Close(missing, missing, missing);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
public void ReadSample()
{
Excel.Application excelApp = new Excel.Application();
if (excelApp != null)
{
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:\test.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
Excel.Range excelRange = excelWorksheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
Excel.Range range = (excelWorksheet.Cells[i, 1] as Excel.Range);
string cellValue = range.Value.ToString();
//do anything
}
}
excelWorkbook.Close();
excelApp.Quit();
}
}
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和清理垃圾Excel进程
/// </summary>
private void EndReport()
{
object missing = System.Reflection.Missing.Value;
try
{
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
}
catch { }
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
}
catch { }
try
{
//清理垃圾进程
this.killProcessThread();
}
catch { }
GC.Collect();
}
}
/// <summary>退出Excel</summary>
public static void CloseExcel()
{
if (ExApp != null)
{
ExApp.Quit();
ExApp = null;
}
}
/// <summary>
/// save the excel sheet to the location with file name
/// </summary>
/// <param name="fileName"></param>
protected virtual void SaveExcel(string fileName)
{
_workBook.SaveAs(fileName, _value, _value,
_value, _value, _value, Excel.XlSaveAsAccessMode.xlNoChange,
_value, _value, _value, _value, null);
_workBook.Close(false, _value, _value);
_excelApplication.Quit();
}
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 static void ReadExcel(string path)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
int cCnt = 0;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
/*
* Filename, UpdateLinks, ReadOnly, Format, Password,
* WriteResPassword, IgnoreReadOnlyRecommended, Origin,
* Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad
*/
range = xlWorkSheet.UsedRange; //representa as celulas que têm valores
for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
if ((range.Cells[rCnt, cCnt] as Excel.Range).Value2 != null)
{
if (cCnt == 1)
{
Restaurante.Add(str);
}
if (cCnt == 2)
{
Item.Add(str);
}
if (cCnt == 3)
{
Quantidade.Add(str);
}
if (cCnt == 4)
{
Calorias.Add(str);
}
}
}
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
/// <summary>
/// 将内存中Excel保存到本地路径
/// </summary>
/// <param name="excelName"></param>
private void SaveExcel(string excelName)
{
_excelApp.Visible = false;
//保存为Office2003和Office2007都兼容的格式
_book.SaveAs(excelName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_excelApp.Quit();
}
private void creat_Click(object sender, EventArgs e)
{
// textBox2.Text = UInt96.Parse(textBox1.Text).Inc().ToString();
saveFileDialog1.Title = "MAC地址生成工具----百思威科技";
saveFileDialog1.Filter = "Excel(*.xls)|*.xls";
saveFileDialog1.FileName = string.Format("MAC地址_{0}", DateTime.Now.ToString("yyyyMMdd"));
DialogResult result = saveFileDialog1.ShowDialog();
Excel._Application xlapp = new Excel.Application();
Excel.Workbook xlbook = xlapp.Workbooks.Add(true);
Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1];
// MessageBox.Show(textBox2.Text);
int RowCount = Convert.ToInt32(textBox2.Text);
int RowIndex = 0;
string val = textBox1.Text;
val = val.Replace(":", "");
for (int i = 0; i < RowCount; i++)
{
RowIndex++;
if (i != 0)
{
if (radioButton1.Checked)
{
val = UInt96.Parse(val).Inc().ToString(":");
}
else
{
val = UInt96.Parse(val).Inc().ToString("");
}
xlsheet.Cells[RowIndex, 1] = val;
}
else
{
if (radioButton1.Checked)
{
string[] sArray = Regex.Split(val, @"(\w{2})");
List <string> listTemp = new List <string>();
foreach (string s in sArray)
{
if (string.IsNullOrEmpty(s))
{
continue;
}
listTemp.Add(s);
}
string[] newlist = listTemp.ToArray();
val = String.Join(":", newlist);
}
xlsheet.Cells[RowIndex, 1] = val;
}
}
xlbook.Saved = true;
xlbook.SaveCopyAs(saveFileDialog1.FileName);
xlapp.Quit();
MessageBox.Show("导出成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public static void getExcelFile()
{
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\E56626\Desktop\Teddy\VS2012\Sandbox\sandbox_test - Copy - Copy.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
//IMPORTANT SECTION
var dictionary = new Dictionary <string, List <string> >();
//iterate over the rows and columns as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
//it would be nice if we add some null checking to this variables. Check the article again
var col1 = xlRange.Cells[i, 1].Value2.ToString();
var col2 = xlRange.Cells[i, 2].Value2.ToString();
if (dictionary.ContainsKey(col1))
{
var existingList = dictionary[col1];
existingList.Add(col2);
}
else
{
var newList = new List <string>();
newList.Add(col2);
dictionary.Add(col1, newList);
}
}
//Do whatever you'd like with the dictionary
//END OF IMPORTANT SECTION
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
public void CleanUp()
{
book = null;
xl.Quit();
xl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
private static void excelSaveClose(string szPath, string szResultPath, Excel.Application rExcel, Excel.Workbook rWorkbook)
{
Process[] localByNameApp = Process.GetProcessesByName(szPath);//获取程序名的所有进程
if (localByNameApp.Length > 0)
{
foreach (var app in localByNameApp)
{
// if (!app.HasExited)
// {
#region
////设置禁止弹出保存和覆盖的询问提示框
rExcel.DisplayAlerts = false;
rExcel.AlertBeforeOverwriting = false;
rExcel.Visible = false;
//wb.Saved = true;
////保存工作簿
//rExcel.Application.Workbooks.Add(true).Save();
//保存excel文件
///excel.Save("E:\\c#_test\\winFormTest\\winFormTest\\hahaha.xls");
//确保Excel进程关闭
rExcel.Quit();
rExcel = null;
#endregion
app.Kill();//关闭进程
/*}*/
}
}
if (rWorkbook != null)
{
rExcel.DisplayAlerts = false;
rExcel.AlertBeforeOverwriting = false;
rExcel.Visible = false;
//rExcel.Application.Workbooks.Add(true).Save();
//保存结果
rWorkbook.SaveCopyAs(szResultPath);
///*, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing*/);
//wb.Save();
rWorkbook.Close(false, szPath, szPath);
}
rExcel.Quit();
// 安全回收进程
System.GC.GetGeneration(rExcel);
}
public void dialog(DataGridView grid)
{
Excel._Application app = new Excel.Application();
Excel._Workbook wk = app.Workbooks.Add(Type.Missing);
Excel._Worksheet sheet = null;
sheet = wk.Sheets[1];
sheet = wk.ActiveSheet;
sheet.Name = "Entrate";
for (int i = 1; i <= grid.ColumnCount; i++)
{
sheet.Cells[1, i] = grid.Columns[i - 1].HeaderText;
}
for (int i = 0; i < grid.Rows.Count; i++)
{
for (int j = 0; j < grid.Columns.Count; j++)
{
if (j != 7 && j != 8 && j != 9)
{
sheet.Cells[i + 2, j + 1] = grid.Rows[i].Cells[j].Value.ToString();
}
else
{
sheet.Cells[i + 2, j + 1] = grid.Rows[i].Cells[j].Value;
}
}
}
sheet.Columns.AutoFit();
fw.Close();
SaveFileDialog sf = new SaveFileDialog();
sf.Title = "Salva";
sf.Filter = "XLSX|*.xlsx|XLS|*.xls|Tutti i file|*.*";
sf.InitialDirectory = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
if (sf.ShowDialog() == DialogResult.OK)
{
try
{
wk.SaveAs(sf.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wk.Close();
app.Quit();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 关闭Excel内存进程
/// </summary>
/// <param name="xlsApp">应用程序</param>
/// <param name="xlsWorkbook">工作簿</param>
/// <param name="xlsSheet">工作表</param>
public static void CloseXls(Excel.Application xlsApp, Excel.Workbook xlsWorkbook, Excel.Worksheet xlsSheet)
{
xlsWorkbook.Close();
xlsApp.Workbooks.Close();
xlsApp.Quit();
//关闭EXCEL进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet);
xlsWorkbook = null;
xlsApp = null;
}
public CreateExcelApplication()
{
var excelApp = new Excel.Application();
// Quit Excel Application.
excelApp.Quit();
// Clean Up.
releaseObject(xlApp);
releaseObject(xlWorkBook);
//test
}
} //SearchByTitleOrArticul
//Чтение из Excel файла.
private static IList <SparePart> LoadExcelFile(string excelPath)
{
IList <SparePart> spareParts = new List <SparePart>();
Excel.Application excelApp = new Excel.Application();
int row = 2; //начинаем с двойки, т.к. пропускаем названия.
try
{
excelApp.Workbooks.Open(excelPath);
Excel.Worksheet currentSheet = (Excel.Worksheet)excelApp.Workbooks[1].Worksheets[1];
while (currentSheet.get_Range("A" + row).Value2 != null)
{
for (char column = 'A'; column < 'J'; column++)
{
Excel.Range cell = currentSheet.get_Range(Convert.ToString(column) + row);
if (cell.Value2 == null)
{
break;
}
//tempList.Add(cell != null ? cell.Value2.ToString() : "");
SparePart sparePart = new SparePart
{
Photo = cell.Value2.ToString(),
Articul = (currentSheet.get_Range(Convert.ToString(++column) + row)).Value2 as string,
Title = (currentSheet.get_Range(Convert.ToString(++column) + row)).Value2 as string,
Manufacturer = (currentSheet.get_Range(Convert.ToString(++column) + row)).Value2 as string,
Unit = (currentSheet.get_Range(Convert.ToString(++column) + row)).Value2 as string,
Count = Convert.ToDouble((currentSheet.get_Range(Convert.ToString(++column) + row)).Value2.ToString()),
Price = Convert.ToDouble((currentSheet.get_Range(Convert.ToString(++column) + row)).Value2.ToString()),
Markup = Convert.ToDouble((currentSheet.get_Range(Convert.ToString(++column) + row)).Value2.ToString())
};
sparePart.SellingPrice = CalculationOfSellingPrice(sparePart.Price, sparePart.Markup);
spareParts.Add(sparePart);
} //for
row++;
} //while
} //try
catch (Exception) { }
// В случае если имя файла указано неправильно, ресурс должен быть освобожден! (можно исп-ть using)
finally
{
excelApp.Quit();
}
return(spareParts);
}//LoadExcelFile
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();
}
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
double dbl;
double[] track = new double[10];
int counter = 0;
string str;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(@"d:\csharp-Excel.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
for (rCnt = 1; rCnt <= rw; rCnt++)
{
for (cCnt = 1; cCnt <= cl; cCnt++)
{
dbl = (double)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
track[counter] = dbl;
str = dbl.ToString();
MessageBox.Show(str);
counter++;
}
}
for (counter = 0; counter < 10; counter++)
{
Console.WriteLine("Recorded[{0}] = {1}", counter, track[counter]);
Console.WriteLine("Average is ");
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
public void CloseExcelWorkbook()
{
if (excelApp != null)
{
object refmissing = System.Reflection.Missing.Value;
this.axWebBrowser1.Navigate("about:blank");
excelApp.ActiveWorkbook.Close(false, refmissing, refmissing);
Application.DoEvents();
excelApp.Quit();
Application.DoEvents();
this.ReleaseCOM(excelApp);
this.axWebBrowser1.Dispose();
excelApp = null;
}
}
/// <summary>
/// 将DataTable到出到Excel文件中
/// </summary>
/// <param name="strTargetPath">文件地址</param>
/// <param name="strFileName">文件明</param>
/// <param name="dt">待导出的DataTable对象</param>
/// <returns>true:success/false:faile</returns>
public static bool ExportExcelFile(DataTable dt, string fileName, string[] strarr, string [] strcolnameArr)
{
bool flag = false;
try
{
//判断参数有效性
if (dt == null || dt.Rows.Count == 0)
{
return(false);
}
//创建excel对象
Excel.Application excelMatching = new Excel.Application();
excelMatching.Workbooks.Add(true);//创建excel工作薄
//把数据表的各个信息输入到excel表中
for (int i = 0; i < dt.Rows.Count + 1; i++)
{
for (int j = 0; j < strcolnameArr.Length; j++)
{
if (i == 0)
{
excelMatching.Cells[i + 1, j + 1] = strarr[j];
}
else
{
excelMatching.Cells[i + 1, j + 1] = dt.Rows[i - 1][strcolnameArr[j].ToString()].ToString();
}
}
}
//保存
if (File.Exists(fileName))
{
File.Delete(fileName);
}
excelMatching.Workbooks[1].Saved = true;
excelMatching.UserControl = false;
excelMatching.Workbooks[1].SaveCopyAs(fileName);
excelMatching.Quit();
flag = true;
}
catch
{
throw;
}
return(flag);
}
private string ExportDataSetToExcel(DataSet ds, string location)
{
//Creae an Excel application instance
Excel.Application excelApp = new Excel.Application();
//string date = DateTime.Now.ToString();
string loc = location;
//Path.GetFullPath(loc);
// string path = date.Trim();
int randomnnumber = rnd.Next(1, 1000);
string date = DateTime.Now.ToString("yyyyMMddHHmmss");
string myPath = @loc + randomnnumber + date + ".xls";
//Create an Excel workbook instance and open it from the predefined location
// Excel.Workbook excel1 = excelApp.Workbooks.Add(myPath);
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();
foreach (DataTable table in ds.Tables)
{
//Add a new worksheet to workbook with the Datatable name
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Name = table.TableName;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
}
}
}
try
{
excelWorkBook.SaveAs(myPath);
excelWorkBook.Close();
excelApp.Quit();
}
catch (Exception e)
{
return("0");
}
return("1");
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xl = new Excel.Application();
xl.Visible = true;
xl.Workbooks.Add("c:\\Pasta1.xlt");
xl.Cells[3, 4] = "oi";
object x = new object();
xl.Workbooks[0].Close(false, "", x);
xl.Quit();
}
// 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 crearExcelReporteStock()
{
ControlInsumos.DAL.RebajarStockDal rebajarStockDal = new ControlInsumos.DAL.RebajarStockDal();
string nombreArchivo = "";
string fecha = cboxAño.Text + "-" + mes();
dgvInforme.DataSource = b.SelectDataTable(rebajarStockDal.reporteConsumo(fecha));
switch (mes())
{
case "01" : nombreArchivo = "Reporte Consumo Enero " + cboxAño.Text; break;
case "02" : nombreArchivo = "Reporte Consumo Febrero " + cboxAño.Text; break;
case "03" : nombreArchivo = "Reporte Consumo Marzo " + cboxAño.Text; break;
case "04" : nombreArchivo = "Reporte Consumo Abril " + cboxAño.Text; break;
case "05" : nombreArchivo = "Reporte Consumo Mayo " + cboxAño.Text; break;
case "06" : nombreArchivo = "Reporte Consumo Junio " + cboxAño.Text; break;
case "07" : nombreArchivo = "Reporte Consumo Julio " + cboxAño.Text; break;
case "08" : nombreArchivo = "Reporte Consumo Agosto " + cboxAño.Text; break;
case "09" : nombreArchivo = "Reporte Consumo Septiembre " + cboxAño.Text; break;
case "10": nombreArchivo = "Reporte Consumo Octubre " + cboxAño.Text; break;
case "11": nombreArchivo = "Reporte Consumo Noviembre " + cboxAño.Text; break;
case "12": nombreArchivo = "Reporte Consumo Diciembre " + cboxAño.Text; break;
default: break;
}
try
{
// creating Excel Application
Excel._Application app = new Excel.Application();
object misValue = System.Reflection.Missing.Value;
// creating new WorkBook within Excel application
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = (Excel._Worksheet)workbook.Sheets["hoja1"];
//worksheet = (Excel._Worksheet)workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Resumen Consumo";
// storing header part in Excel
for (int i = 1; i < dgvInforme.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgvInforme.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dgvInforme.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvInforme.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgvInforme.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
workbook.SaveAs(nombreArchivo + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
MessageBox.Show("El archivo: " + nombreArchivo + ", se ha guardado en Mis Documentos", "Informes", MessageBoxButtons.OK, MessageBoxIcon.Information);
// Exit from the application
app.Quit();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
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();
}
}
/// <summary>
/// Erzeugt die Listen mit den Objekten aus der Anmeldung.
/// </summary>
public void Generate()
{
// Variablen initialisieren
judoka = new List<Judoka>();
verein = new Verein();
// Ko-Kriterien
if (!System.IO.File.Exists(fileName)) return;
Excel.Application excel = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
try
{
// Worksheet Initialisieren
excel = new Excel.Application();
workbook = excel.Workbooks.Open(fileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, null, false, false, false);
worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
// Verein auslesen
verein.Name = GetSheetValue(worksheet, "B2");
verein.Kurzname = GetSheetValue(worksheet, "B2");
verein.Email = GetSheetValue(worksheet, "B7");
System.Text.StringBuilder sb = new StringBuilder();
sb.AppendLine(GetSheetValue(worksheet, "B3"));
sb.AppendLine(GetSheetValue(worksheet, "B4"));
sb.AppendLine(GetSheetValue(worksheet, "B5"));
sb.AppendLine(GetSheetValue(worksheet, "B6"));
verein.Adresse = sb.ToString();
// Kämpfer auslesen
int counter = 1;
while (HasSheetJudoka(counter, worksheet))
{
Judoka j = new Judoka();
j.Vorname = GetSheetValue(worksheet, GetJudokaCell(counter, "C"));
j.Nachname = GetSheetValue(worksheet, GetJudokaCell(counter, "B"));
try
{
j.Geburtsdatum = DateTime.FromOADate(double.Parse(GetSheetValue(worksheet, GetJudokaCell(counter, "F"))));
}
catch (Exception)
{
j.Geburtsdatum = new DateTime(1900, 1, 1);
}
j.Gürtel = new Gürtel(GetSheetValue(worksheet, GetJudokaCell(counter, "D")), "9");
j.Geschlecht = new Geschlecht(GetSheetValue(worksheet, GetJudokaCell(counter, "E")));
j.Verein = verein;
judoka.Add(j);
counter++;
}
}
catch (Exception)
{
throw;
}
finally
{
// Excel beenden
workbook.Close(false, null, 0);
workbook = null;
excel.Quit();
excel = null;
System.GC.WaitForPendingFinalizers();
}
}
public void crearExcelReporteInsumos()
{
ControlInsumos.DAL.ArticuloDal artDal = new ControlInsumos.DAL.ArticuloDal();
int veces = artDal.countArt();
string desde = cboxAñoDesde.Text + "-" + mesDesde() + "-" + cboxDesde.Text;
string hasta = cboxAñoDesde.Text + "-" + mesHasta() + "-" + cboxHasta.Text;
string nombreArchivo = "Reporte Insumos." + DateTime.Now.ToShortDateString();
try
{
// creating Excel Application
Excel._Application app = new Excel.Application();
object misValue = System.Reflection.Missing.Value;
// creating new WorkBook within Excel application
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
for (int r = 1; r <= veces; r++)
{
string nombreArticulo = artDal.nombreArticulo(r - 1);
dgvInformes.DataSource = b.SelectDataTable(insumosDal.reporteInsumos(r, nombreArticulo, desde, hasta));
worksheet = (Excel._Worksheet)workbook.Sheets["hoja" + r];
//worksheet = (Excel._Worksheet)workbook.ActiveSheet;
// changing the name of active sheet
if (r == 1)
{
worksheet.Name = "Resumen Insumos";
}
else
{
worksheet.Name = nombreArticulo;
}
// storing header part in Excel
for (int i = 1; i < dgvInformes.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgvInformes.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dgvInformes.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvInformes.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgvInformes.Rows[i].Cells[j].Value.ToString();
}
}
}
// save the application
workbook.SaveAs(nombreArchivo + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
MessageBox.Show("El archivo: " + nombreArchivo + ", se ha guardado en Mis Documentos", "Informes", MessageBoxButtons.OK, MessageBoxIcon.Information);
// Exit from the application
app.Quit();
}
catch (Exception e)
{
}
}
private void ExportToExcelWithoutTemp(string m_strFileName, System.Data.DataTable dt)
{
if (dt == null || dt.Rows.Count <= 0)
return;
xpbProcecess.Properties.Step = 1;
xpbProcecess.Properties.Minimum = 0;
xpbProcecess.Properties.Maximum = dt.Rows.Count;
xpbProcecess.Position = 0;
xpbProcecess.Properties.PercentView = true;
//Excel.Application app;
object MISS = Type.Missing;
app = new Excel.Application();
Excel.Workbooks workbooks;
Excel._Workbook wb;
try
{
workbooks = app.Workbooks;
wb = workbooks.Add(true);
Excel.Sheets exSheets = wb.Worksheets;
Excel._Worksheet ws = (Excel._Worksheet)exSheets.get_Item(1);
//Start Exporting data to Excel file, row by row
int countRow = 1;
// export Header
for (int i = 0; i < dt.Columns.Count; i++)
{
((Excel.Range)ws.Cells[countRow, i + 1]).Value2 = dt.Columns[i].Caption;
((Excel.Range)ws.Cells[countRow, i + 1]).Interior.ColorIndex = 15;
((Excel.Range)ws.Cells[countRow, i + 1]).Font.Bold = true;
}
countRow++;
// insert excel row
object[,] arrData = new object[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j] != DBNull.Value)
if (!ckbToneMarks.Checked)
arrData[i, j] = dt.Rows[i][j].ToString();
else
{
arrData[i, j] = Methods.RemoveToneMarks(dt.Rows[i][j].ToString());
}
}
xpbProcecess.Increment(1);
xpbProcecess.Update();
}
Excel.Range rg = ((Excel.Range)ws.get_Range(ws.Cells[2, 1], ws.Cells[dt.Rows.Count + 1, dt.Columns.Count]));
rg.Select();
rg.Value2 = arrData;
//End of Exporting76
this.Cursor = Cursors.Default;
if (!strExtend.Equals(XLS))
{
app.Visible = false;
wb.SaveCopyAs(xbeFileName.Text);
wb.Close(false, false, false);
app.Quit();
//Open word merge mail document
OpenWorddocument(xbeFileName.Text,txtExcelFileName.Text);
}
else
app.Visible = true;
//release Object Excel
releaseObject(exSheets);
releaseObject(wb);
releaseObject(rg);
releaseObject(workbooks);
releaseObject(ws);
releaseObject(app);
btnFWClose.Focus();
btnExport.Enabled = true;
}
catch (Exception ex)
{
app.Workbooks.Close();
app = null;
this.Cursor = Cursors.Default;
HPA.Common.Helper.ShowException(ex, this.Name + ".ExportToExcelWithoutTemp()", null);
return;
}
}
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 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 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 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();
}
}
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();
}
}
}
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 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 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();
}
}
public bool ConvertXLSToText()
{
bool result = true;
Excel.Application oe = new Excel.Application();
oe.Visible = false;
oe.EnableEvents = false; //critical to prevent macros from running
oe.DisplayAlerts = false; //prevents any ui from occuring
Excel.Workbook wb = oe.Workbooks.Open(SrcFile, 0, false, Missing.Value, "", "", true, Missing.Value, "", false, false, 0, false);
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
if (ws.Name.ToLower() == "cover sheet" || ws.Name.ToLower() == "cover page")
{
if (wb.Worksheets.Count > 1)
{
ws = (Excel.Worksheet)wb.Worksheets[2];
}
}
Excel.Range r = ws.UsedRange;
Array tarray = (Array)r.Value;
System.Runtime.InteropServices.Marshal.ReleaseComObject(r);
try
{
int rows = tarray.GetUpperBound(0);
int columns = tarray.GetUpperBound(1);
bool lineBlank;
string tempValue;
string Line;
StringBuilder Lines = new StringBuilder(1);
for (int rc = 0; rc < rows; rc++)
{
lineBlank = true;
Line = "";
for (int c = 0; c < columns; c++)
{
try
{
tempValue = tarray.GetValue(rc + 1, c + 1).ToString();
}
catch (Exception)
{
tempValue = "";
}
Line += tempValue + (((c + 1) == columns) ? "" : "\t");
if (tempValue.Trim() != "")
{
lineBlank = false;
}
}
if (!lineBlank)
{
Lines.Append(Line + "\r\n");
}
}
wb.Close(false, Missing.Value, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
oe.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oe);
result = WriteToDestFile(Lines);
}
catch (Exception ex)
{
result = false;
_lasterror = ex.Message.Split('\n')[0];
}
return result;
}
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 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();
}
}
private void SaveToExcel(DataTable dt1, DataTable dt2, string ten)
{
//string FileName = Businessbp.executedb.folder + "ATM_ONUS_"+DateTime.Now.Day.ToString()+DateTime.Now.Month.ToString()+DateTime.Now.Year.ToString()+DateTime.Now.Hour.ToString()+DateTime.Now.Minute.ToString()+DateTime.Now.Second.ToString()+".xls";
string FileName = Businessbp.executedb.folder + ten + DateTime.Now.Day.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Year.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
Excel.Application xl = null;
Excel._Workbook wb = null;
Excel._Worksheet sheet = null;
//VBIDE.VBComponent module = null;
bool SaveChanges = false;
try
{
if (File.Exists(FileName))
{ File.Delete(FileName); }
GC.Collect();
// Create a new instance of Excel from scratch
xl = new Excel.Application();
xl.Visible = false;
// Add one workbook to the instance of Excel
wb = (Excel._Workbook)(xl.Workbooks.Add(Missing.Value));
wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// Get a reference to the one and only worksheet in our workbook
//sheet = (Excel._Worksheet)wb.ActiveSheet;
sheet = (Excel._Worksheet)(wb.Sheets[1]);
int ColumnIndex = 0;
int rowIndex = 0;
foreach (DataRow row in dt1.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in dt1.Columns)
{
ColumnIndex++;
sheet.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName].ToString();
}
}
((Excel.Range)sheet.Cells[1, 8]).EntireColumn.NumberFormat = "d/m/yyyy h:mm:ss";
((Excel.Range)sheet.Cells[1, 11]).EntireColumn.NumberFormat = "000000";
((Excel.Range)sheet.Cells[1, 7]).EntireColumn.NumberFormat = "##,##0.00";
((Excel.Range)sheet.Cells[1, 3]).EntireColumn.NumberFormat = "##### #### #### #### ####";
((Excel.Range)sheet.Cells[1, 4]).EntireColumn.NumberFormat = "########################";
((Excel.Range)sheet.Cells[1, 5]).EntireColumn.NumberFormat = "00000#";
((Excel.Range)sheet.Cells[1, 10]).EntireColumn.NumberFormat = "############";
// font style
((Excel.Range)sheet.Cells[1, 1]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 1]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 2]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 3]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 4]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 5]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 6]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 7]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 8]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 9]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 9]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 10]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 11]).Font.Bold = true;
// set come column heading names
sheet.Name = "ELECTRA";
sheet.Cells[1, 1] = "STT";
sheet.Cells[1, 2] = "LoaiGD";
sheet.Cells[1, 3] = "Card_number";
sheet.Cells[1, 4] = "Account_number";
sheet.Cells[1, 5] = "Trace_number";
sheet.Cells[1, 6] = "Approve_code";
sheet.Cells[1, 7] = "Amount";
sheet.Cells[1, 8] = "Transaction_date";
sheet.Cells[1, 9] = "Ten_may";
sheet.Cells[1, 10] = "Reference_number";
sheet.Cells[1, 11] = "processing_code";
//------------FLEXCUBE
sheet = (Excel._Worksheet)(wb.Sheets[2]);
ColumnIndex = 0;
rowIndex = 0;
foreach (DataRow row in dt2.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in dt2.Columns)
{
ColumnIndex++;
if (ColumnIndex == 1)
sheet.Cells[rowIndex + 1, ColumnIndex] = rowIndex;
else
sheet.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName].ToString();
if (ColumnIndex == 6 && row[col.ColumnName].ToString() == "")
sheet.Cells[rowIndex + 1, ColumnIndex] = "0";
}
}
((Excel.Range)sheet.Cells[1, 7]).EntireColumn.NumberFormat = "d/m/yyyy h:mm:ss";
((Excel.Range)sheet.Cells[1, 5]).EntireColumn.NumberFormat = "00000#";
((Excel.Range)sheet.Cells[1, 6]).EntireColumn.NumberFormat = "##,##0.00";
((Excel.Range)sheet.Cells[1, 3]).EntireColumn.NumberFormat = "##### #### #### #### ####";
((Excel.Range)sheet.Cells[1, 4]).EntireColumn.NumberFormat = "########################";
((Excel.Range)sheet.Cells[1, 9]).EntireColumn.NumberFormat = "000000";
// font style
((Excel.Range)sheet.Cells[1, 1]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 1]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 2]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 3]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 4]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 5]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 6]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 7]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 8]).Font.Bold = true;
((Excel.Range)sheet.Cells[1, 9]).Font.Bold = true;
sheet.Name = "FLEXCUBE";
sheet.Cells[1, 1] = "STT";
sheet.Cells[1, 2] = "LoaiGD";
sheet.Cells[1, 3] = "Card_number";
sheet.Cells[1, 4] = "Account_number";
sheet.Cells[1, 5] = "Trace_number";
sheet.Cells[1, 6] = "Amount";
sheet.Cells[1, 7] = "Transaction_date";
sheet.Cells[1, 8] = "Ten may";
sheet.Cells[1, 9] = "Processing_code";
// Let loose control of the Excel instance
xl.Visible = false;
xl.UserControl = false;
// Set a flag saying that all is well and it is ok to save our changes to a file.
SaveChanges = true;
// Save the file to disk
wb.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal,null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null);
MessageBox.Show("Export successfull");
}
catch (Exception err)
{
String msg;
msg = "Error: ";
msg = String.Concat(msg, err.Message);
msg = String.Concat(msg, " Line: ");
msg = String.Concat(msg, err.Source);
MessageBox.Show(msg);
}
finally
{
try
{
// Repeat xl.Visible and xl.UserControl releases just to be sure
// we didn't error out ahead of time.
xl.Visible = false;
xl.UserControl = false;
// Close the document and avoid user prompts to save if our method failed.
wb.Close(SaveChanges, null, null);
xl.Workbooks.Close();
}
catch { }
// Gracefully exit out and destroy all COM objects to avoid hanging instances
// of Excel.exe whether our method failed or not.
xl.Quit();
//if (module != null) { Marshal.ReleaseComObject(module);
}
if (sheet != null) { Marshal.ReleaseComObject(sheet); }
if (wb != null) { Marshal.ReleaseComObject(wb); }
if (xl != null) { Marshal.ReleaseComObject(xl); }
//module = null;
sheet = null;
wb = null;
xl = null;
GC.Collect();
}
//服药记录打印
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="fOptions"></param>
public static void ExportToExcel(List<DataTableExportOptions> fOptions, String fFileName)
{
if (fOptions == null || fOptions.Count == 0) return;
#region 判定指定的文件路径下是否存在相同的文件名，如果存在就删除之前的文件
try
{
if (File.Exists(fFileName))
File.Delete(fFileName);
}
catch
{
return;
}
#endregion
Excel.Application application = new Excel.Application();
application.Visible = false;
application.UserControl = false;
Excel.Workbook workBook = (Excel.Workbook)(application.Workbooks.Add(missing));
try
{
#region " 根据需要导出的 DataTable 数量，预先增加不足的工作表或多余的工作表 "
// 添除多余的工作表
while (application.ActiveWorkbook.Sheets.Count > fOptions.Count)
((Excel.Worksheet)application.ActiveWorkbook.Sheets[1]).Delete();
// 添加工作表
while (application.ActiveWorkbook.Sheets.Count < fOptions.Count)
application.Worksheets.Add(missing, missing, missing, missing);
#endregion
int sheetIndex = 1;
List<String> sheetNames = new List<string>();
foreach (DataTableExportOptions option in fOptions)
{
#region " 处理在多个 DataTable 设置为相同的工作表名称的问题 "
if (sheetNames.Contains(option.WorkSheetName))
{
int i = 1;
while (true)
{
string newSheetName = option.WorkSheetName + i.ToString();
if (!sheetNames.Contains(newSheetName))
{
sheetNames.Add(newSheetName);
option.WorkSheetName = newSheetName;
break;
}
i++;
}
}
else
{
sheetNames.Add(option.WorkSheetName);
}
#endregion
ExportToExcel(application, workBook, (Excel.Worksheet)application.ActiveWorkbook.Sheets[sheetIndex], option);
sheetIndex++;
}
workBook.SaveAs(fFileName, missing, missing, missing, missing, missing
, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
}
finally
{
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
GC.Collect();
}
}
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 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 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 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);
}
}
//健康评测打印
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 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 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();
}
}
