private void btnIn_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)dgvDocGia.DataSource;
Excel.Application exApp;
Excel.Workbook exBook;
Excel.Worksheet exSheet;
Excel.Range exRange;
exApp = new Excel.Application();
exBook = (Excel.Workbook)(exApp.Workbooks.Add(true));
exSheet = (Excel.Worksheet)exBook.ActiveSheet;
int iRow = 7;
for (int i = 0; i < dt.Rows.Count; i++)
{
exSheet.Cells[iRow, 2] = dt.Rows[i]["MaDG"].ToString();
exSheet.Cells[iRow, 3] = dt.Rows[i]["TenDG"].ToString();
exSheet.Cells[iRow, 4] = dt.Rows[i]["GioiTinh"].ToString();
exSheet.Cells[iRow, 5] = dt.Rows[i]["NgayMuon"].ToString();
exSheet.Cells[iRow, 6] = dt.Rows[i]["DiaChi"].ToString();
iRow++;
}
exRange = exSheet.get_Range("B6", "F" + (iRow - 1).ToString());
exRange.Borders.Color = Color.Black.ToArgb();
exRange.Font.Size = "18";
Excel.Range cl1 = exSheet.get_Range("B6", "B6");
cl1.Value = "Mã độc giả";
cl1.ColumnWidth = "15";
Excel.Range cl2 = exSheet.get_Range("C6", "C6");
cl2.Value = "Tên độc giả";
cl2.ColumnWidth = "30";
Excel.Range cl3 = exSheet.get_Range("D6", "D6");
cl3.Value = "Giới tính";
cl3.ColumnWidth = "15";
Excel.Range cl4 = exSheet.get_Range("E6", "E6");
cl4.Value = "Ngày mượn";
cl4.ColumnWidth = "30";
Excel.Range cl5 = exSheet.get_Range("F6", "F6");
cl5.Value = "Địa chỉ";
cl5.ColumnWidth = "30";
Excel.Range cl6 = exSheet.get_Range("B3", "F4");
cl6.Merge(false);
cl6.HorizontalAlignment = 3;
cl6.VerticalAlignment = 3;
cl6.Value = "THỐNG KÊ ĐỘC GIẢ ĐÃ MƯỢN SÁCH";
cl6.Font.Size = "20";
exApp.UserControl = false;
exApp.Visible = true;
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
if (dgvMzyj.DataSource == null)
{
return;
}
Excel.Application myExcel = new Excel.ApplicationClass();
//设置不显示
myExcel.ScreenUpdating = false;
//添加工作簿
myExcel.Workbooks.Add(true);
//设置工作表
Excel.Worksheet mySheet = (Excel.Worksheet)myExcel.ActiveWorkbook.ActiveSheet;
mySheet.Name = "TrasenHis";
//设置标题
Excel.Range myTitle = mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, dgvMzyj.Columns.Count]);
myTitle.Merge(true);
if (rdoSk.Checked == true)
{
myTitle.Value2 = this.Text + "(" + rdoSk.Text + ")";
}
else
{
myTitle.Value2 = this.Text + "(" + rdoJk.Text + ")";
}
myTitle.Font.Name = "黑体";
myTitle.Font.Size = 20;
myTitle.Font.Bold = true;
myTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
myTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//设置条件
Excel.Range myNote = mySheet.get_Range(mySheet.Cells[2, 1], mySheet.Cells[2, dgvMzyj.Columns.Count]);
myNote.Merge(true);
myNote.Value2 = dateTimePickerBegin.Value.ToLongDateString() + " " + dateTimePickerBegin.Value.ToLongTimeString() + " —— " + dateTimePickerEnd.Value.ToLongDateString() + " " + dateTimePickerEnd.Value.ToLongTimeString();
myNote.Font.Name = "宋体";
myNote.Font.Size = 12;
myNote.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
myNote.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//写入表头
for (int i = 0; i < dgvMzyj.Columns.Count; i++)
{
myExcel.Cells[3, i + 1] = dgvMzyj.Columns[i].HeaderText;
}
//设置表头
Excel.Range myHeader = mySheet.get_Range(mySheet.Cells[3, 1], mySheet.Cells[3, dgvMzyj.Columns.Count]);
myHeader.Font.Size = 12;
myHeader.Font.Bold = false;
myHeader.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//写入数据
for (int i = 0; i < dgvMzyj.Rows.Count; i++)
{
for (int j = 0; j < dgvMzyj.Columns.Count; j++)
{
myExcel.Cells[i + 4, j + 1] = dgvMzyj[j, i].Value;
}
}
//设置表格
Excel.Range myTable = mySheet.get_Range(mySheet.Cells[3, 1], mySheet.Cells[dgvMzyj.Rows.Count + 3, dgvMzyj.Columns.Count]);
myTable.Columns.AutoFit();
myTable.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
myTable.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
myTable.Borders.Weight = Excel.XlBorderWeight.xlMedium;
//设置显示
myExcel.Visible = true;
myExcel.ScreenUpdating = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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);
}
}
private void btnIn_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
SqlConnection con = new SqlConnection(db.conSt);
string madg = cbDocGia.Text;
string sql = string.Format("Select * from MuonTraSach where MaDG='{0}'", madg);
db.getData(sql);
refreshDataGridView1(sql);
DataTable dt = (DataTable)dgvMuontra.DataSource;
Excel.Application exApp;
Excel.Workbook exBook;
Excel.Worksheet exSheet;
Excel.Range exRange;
exApp = new Excel.Application();
exBook = (Excel.Workbook)(exApp.Workbooks.Add(true));
exSheet = (Excel.Worksheet)exBook.ActiveSheet;
int iRow = 7;
for (int i = 0; i < dt.Rows.Count; i++)
{
exSheet.Cells[iRow, 2] = dt.Rows[i]["MaDG"].ToString();
exSheet.Cells[iRow, 3] = dt.Rows[i]["MaSach"].ToString();
exSheet.Cells[iRow, 4] = dt.Rows[i]["SoLuong"].ToString();
exSheet.Cells[iRow, 5] = dt.Rows[i]["NgayMuon"].ToString();
exSheet.Cells[iRow, 6] = dt.Rows[i]["NgayHenTra"].ToString();
iRow++;
}
exRange = exSheet.get_Range("B6", "F" + (iRow - 1).ToString());
exRange.Borders.Color = Color.Black.ToArgb();
exRange.Font.Size = "18";
Excel.Range cl1 = exSheet.get_Range("B6", "B6");
cl1.Value = "Mã độc giả";
cl1.ColumnWidth = "15";
Excel.Range cl2 = exSheet.get_Range("C6", "C6");
cl2.Value = "Mã sách";
cl2.ColumnWidth = "20";
Excel.Range cl3 = exSheet.get_Range("D6", "D6");
cl3.Value = "Số lượng";
cl3.ColumnWidth = "15";
Excel.Range cl4 = exSheet.get_Range("E6", "E6");
cl4.Value = "Ngày mượn";
cl4.ColumnWidth = "30";
Excel.Range cl5 = exSheet.get_Range("F6", "F6");
cl5.Value = "Ngày hẹn trả";
cl5.ColumnWidth = "30";
Excel.Range cl6 = exSheet.get_Range("B3", "F4");
cl6.Merge(false);
cl6.HorizontalAlignment = 3;
cl6.VerticalAlignment = 3;
cl6.FormulaR1C1 = "PHIẾU MƯỢN SÁCH";
cl6.Font.Size = "20";
exApp.UserControl = false;
exApp.Visible = true;
}
/// <summary>
/// 将datatable导出excel文件
/// </summary>
/// <param name="dt">需要导出的datatable</param>
/// <param name="AbosultedFilePath">导出文件的绝对路径</param>
/// <returns></returns>
public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
dt.Columns.Remove("id");
dt.Columns.Remove("year");
dt.Columns.Remove("month");
dt.Columns.Remove("jobflowid");
dt.AcceptChanges();
//检查数据表是否为空，如果为空，则退出
if (dt == null)
{
return(false);
}
//创建Excel应用程序对象，如果未创建成功则退出
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
return(false);
}
//创建Excel的工作簿
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
range = (Excel.Range)worksheet.get_Range("A1", "I1");//获取表格第一行
range.Merge(0);
worksheet.Cells[1, 1] = this.biaoti.InnerText;
range.Font.Size = 22;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
//写入标题
for (int i = 0; i < dt.Columns.Count + 1; i++)
{
//写入标题名称
if (i == 0)
{
worksheet.Cells[2, i + 1] = "序号"; //加入序号列
}
else
{
worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];
}
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Interior.ColorIndex = 15;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (i == 0)//序号列宽度设为自动调整
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();
}
}
}
//写入DataTable中数据的内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count + 1; c++)
{
range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
//写入内容
if (c == 0) //增加序号
{
worksheet.Cells[r + 3, c + 1] = (r + 1).ToString();
}
else if (dt.Columns[c - 1].ColumnName == "happendate") //时间列
{
worksheet.Cells[r + 3, c + 1] = ((DateTime)dt.Rows[r][c - 1]).ToString("yyyy年MM月dd日");
}
else if (dt.Columns[c - 1].ColumnName == "ausmoney") //金额列
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
range.NumberFormat = "#,##0.00";
}
else if (dt.Columns[c - 1].ColumnName == "payStatus") //支付列
{
if (dt.Rows[r][c - 1].ToString() == "1")
{
worksheet.Cells[r + 3, c + 1] = "已支付";
}
else
{
worksheet.Cells[r + 3, c + 1] = "未支付";
}
}
else
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
}
//设置样式
range.Font.Size = 9;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
//设置单元格的宽度，如果小于8.5就设置为8.5，如果大于。则设置为自动
if (c == 0) //序号列宽度设为自动
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
//设置合计那一行
range = (Excel.Range)worksheet.get_Range("A" + (dt.Rows.Count + 3).ToString(), "I" + (dt.Rows.Count + 3).ToString());
range.Font.ColorIndex = 41;
//range.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Font.Size = 10;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
range.EntireColumn.AutoFit(); //自动调整列宽
worksheet.Cells[3 + dt.Rows.Count, 1] = "合计:"; //合计那一行的第一列
if (dt.Rows.Count == 0)
{
worksheet.Cells[3 + dt.Rows.Count, 7] = 0;
}
else
{
worksheet.Cells[3 + dt.Rows.Count, 7] = "=SUM(G3:G" + (dt.Rows.Count + 2).ToString() + ")";
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
System.Windows.Forms.Application.DoEvents();
try
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开！\n" + ex.ToString());
return(false);
}
workbook.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return(true);
}
//健康评测打印
public void 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();
}
}
/// <summary>
/// 数据窗口DataWindow/DataStore导出成HTML,再转成EXCEL
/// </summary>
/// <param name="DS"></param>
/// <param name="volExcel"></param>
public static void ExportDataStore(Sybase.DataWindow.DataStore DS, clsVolDatawindowToExcel[] volExcel)
{
SaveFileDialog FD = new SaveFileDialog();
FD.Filter = "Excel 文档|*.xls";
FD.Title = "导出";
FD.ShowDialog();
string filename = FD.FileName.Trim();
if (filename != "")
{
DS.SaveAs(filename, Sybase.DataWindow.FileSaveAsType.HtmlTable, true, Sybase.DataWindow.FileSaveAsEncoding.Utf8);
if (volExcel != null)
{
//定义
object MissingValue = Type.Missing;
Excel.Application Excel_app = new Excel.ApplicationClass();
//打开Excel文挡
Excel.Workbook Excel_work = Excel_app.Workbooks.Open(filename, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue);
//获取当前工作列表
Excel.Worksheet mySheet = (Excel.Worksheet)Excel_work.Worksheets[1];
for (int i = volExcel.Length - 1; i >= 0; i--)
{
Excel.Range range = null;
range = (Excel.Range)mySheet.Rows.get_Item(1, Type.Missing);
//在顶行插入空行
range.Rows.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
for (int j = 0; j < volExcel[i].m_title_text.Length; j++)
{
//合并行
if (volExcel[i].m_endcommn[j] == "ALL")
{
range = mySheet.get_Range(volExcel[i].m_firstcommn[j], mySheet.Cells[1, mySheet.UsedRange.Columns.Count]);
}
else
{
range = mySheet.get_Range(volExcel[i].m_firstcommn[j], volExcel[i].m_endcommn[j]);
}
range.Merge(MissingValue);
range.Value2 = volExcel[i].m_title_text[j];
if (volExcel[i].m_HorizontalAlignment[j] == "0")
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
else if (volExcel[i].m_HorizontalAlignment[j] == "L")
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
else
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
range.RowHeight = volExcel[i].m_rowheight[j];
}
}
mySheet.UsedRange.Rows.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
mySheet = null;
Excel_app.ActiveWorkbook.Close(true, filename.ToString(), null);
Excel_app.Workbooks.Close();
Excel_app.Quit();
Excel_app = null;
}
}
}
public void createAnalysisDoc(int section_count, int sample_count)
{
app1 = new Excel.Application();
app1.Visible = false;
workbook1 = app1.Workbooks.Add(1);
workbook1.Sheets.Add(Count: 1);
Analysis = (Excel.Worksheet)workbook1.Sheets[1];
Analysis.Name = "Analysis";
int j;
int i;
string title;
string sample_title;
for (j = 1; j <= section_count; j++)
{
int level = Convert.ToUInt16((sample_count * 2 + 12) * (j - 1));
title = string.Format("Stage {0}", j.ToString());
addData(level + 2, 1, title, "analysis");
workSheet_range = Analysis.Range[Analysis.Cells[level + 2, 1], Analysis.Cells[level + 2, 9]];
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet_range.Merge();
workSheet_range.Font.Bold = true;
addData(level + 3, 1, "Manual", "analysis");
workSheet_range = Analysis.Range[Analysis.Cells[level + 3, 1], Analysis.Cells[level + 3, 9]];
workSheet_range.Font.Italic = true;
workSheet_range.Font.Bold = false;
workSheet_range.Merge();
addData(level + 4, 2, "Time", "analysis");
addData(level + 4, 3, "Q Value Loaded (SVD)", "analysis");
addData(level + 4, 4, "Q Value Unloaded (SVD)", "analysis");
addData(level + 4, 5, "Q Value (NA)", "analysis");
addData(level + 4, 6, "Resonant Frequency (NA)", "analysis");
addData(level + 4, 7, "Resonant Frequency (SVD)", "analysis");
addData(level + 4, 8, "Cavity Temperature (C)", "analysis");
addData(level + 4, 9, "Room Temperature (C)", "analysis");
workSheet_range = Analysis.Range[Analysis.Cells[level + 4, 1], Analysis.Cells[level + 4, 9]];
workSheet_range.Font.Bold = true;
for (i = 0; i < sample_count; i++)
{
sample_title = string.Format("Sample {0}", (i + 1).ToString());
addData(level + 5 + i, 1, sample_title, "analysis");
}
addData(level + 5 + sample_count, 1, "Average", "analysis");
addData(level + 6 + sample_count, 1, "Standard Dev.", "analysis");
addData(level + 8 + sample_count, 1, "Automatic", "analysis");
workSheet_range = Analysis.Range[Analysis.Cells[level + 8 + sample_count, 1], Analysis.Cells[level + 8 + sample_count, 9]];
workSheet_range.Font.Italic = true;
workSheet_range.Font.Bold = false;
workSheet_range.Merge();
addData(level + 9 + sample_count, 2, "Time", "analysis");
addData(level + 9 + sample_count, 3, "Q Value Loaded (SVD)", "analysis");
addData(level + 9 + sample_count, 4, "Q Value Unloaded (SVD)", "analysis");
addData(level + 9 + sample_count, 5, "Q Value (NA)", "analysis");
addData(level + 9 + sample_count, 6, "Resonant Frequency (NA)", "analysis");
addData(level + 9 + sample_count, 7, "Resonant Frequency (SVD)", "analysis");
addData(level + 9 + sample_count, 8, "Cavity Temperature (C)", "analysis");
addData(level + 9 + sample_count, 9, "Room Temperature (C)", "analysis");
workSheet_range = Analysis.Range[Analysis.Cells[level + 9 + sample_count, 1], Analysis.Cells[level + 9 + sample_count, 9]];
workSheet_range.Font.Bold = true;
for (i = 0; i < sample_count; i++)
{
sample_title = string.Format("Sample {0}", (i + 1).ToString());
addData(level + 10 + sample_count + i, 1, sample_title, "analysis");
}
addData(level + 10 + (2) * sample_count, 1, "Average", "analysis");
addData(level + 11 + (2) * sample_count, 1, "Standard Dev.", "analysis");
workSheet_range = Analysis.Range[Analysis.Cells[1, 1], Analysis.Cells[level + 1000 + sample_count, 1]];
workSheet_range.Font.Bold = 1;
}
}
public void FinanceStatPrint(DataTable data, string className, DateTime date, string savePath)
{
DataTable dtPresents = new FinanInfoDataAccess().GetStudentPresents(date, className);
if (dtPresents == null || dtPresents.Rows.Count == 0)
{
throw new Exception("没有要使用的数据！");
}
else
{
data.Columns.AddRange(new DataColumn[] { new DataColumn("小计", Type.GetType("System.Double")),
new DataColumn("stuPresent", Type.GetType("System.String")),
new DataColumn("stuAbsent", Type.GetType("System.String")) });
if (dtPresents.Rows.Count != data.Rows.Count)
{
throw new Exception("检测到数据完整性错误，请尝试重新生成数据！");
}
else
{
for (int i = 0; i < dtPresents.Rows.Count; i++)
{
data.Rows[i]["小计"] = 0;
data.Rows[i]["stuPresent"] = dtPresents.Rows[i]["times"];
data.Rows[i]["stuAbsent"] = dtPresents.Rows[i]["times_abs"];
}
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\FinanceStat.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = new object[data.Rows.Count + 1, data.Columns.Count + 4];
for (int row = 0; row < data.Rows.Count; row++)
{
objData[row, 0] = row + 1;
for (int column = 0; column < data.Columns.Count - 3; column++)
{
if (column <= 2)
{
objData[row, column + 1] = data.Rows[row][column];
}
else if (column == 3)
{
objData[row, column + 1] = data.Rows[row][data.Columns.Count - 2];
objData[row, column + 2] = data.Rows[row][data.Columns.Count - 1];
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column >= 4 && column < 7)
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column == 7)
{
double temp1 = Convert.ToDouble(data.Rows[row][column - 4]) +
Convert.ToDouble(data.Rows[row][column - 3]) + Convert.ToDouble(data.Rows[row][column - 2]) +
Convert.ToDouble(data.Rows[row][column - 1]);
objData[row, column + 3] = temp1;
double temp2 = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp2;
objData[data.Rows.Count, column + 3] = temp1 + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
objData[data.Rows.Count, column + 4] = temp2 + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
else
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp;
objData[data.Rows.Count, column + 4] = temp + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
}
}
m_objRange = m_objSheet.get_Range("A6", m_objOpt);
m_objRange = m_objRange.get_Resize(data.Rows.Count + 1, data.Columns.Count + 1);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objRange = m_objSheet.get_Range("G3", m_objOpt);
m_objRange.Value = "各项费用";
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 7], m_objSheet.Cells[4, data.Columns.Count]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 12;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, data.Columns.Count + 1], m_objSheet.Cells[4, data.Columns.Count + 1]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
object[, ] objColumn = new object[1, data.Columns.Count - 5];
for (int column = 3; column < data.Columns.Count - 3; column++)
{
if (column < 7)
{
objColumn[0, column - 3] = data.Columns[column].ColumnName;
}
if (column == 7)
{
objColumn[0, column - 3] = data.Columns[data.Columns.Count - 3].ColumnName;
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
else
{
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
}
m_objRange = m_objSheet.get_Range("G5", m_objOpt);
m_objRange = m_objRange.get_Resize(1, data.Columns.Count - 5);
m_objRange.Value = objColumn;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10.5;
for (int column = 5; column < data.Columns.Count - 3; column++)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, column + 2], m_objSheet.Cells[5 + data.Rows.Count, column + 2]);
object i = m_objRange.Calculate();
string s = "asf";
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
}
/// <summary>
/// 将datatable导出excel文件
/// </summary>
/// <param name="dt">需要导出的datatable</param>
/// <param name="AbosultedFilePath">导出文件的绝对路径</param>
/// <returns></returns>
public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
//检查数据表是否为空，如果为空，则退出
if (dt == null)
{
return(false);
}
//创建Excel应用程序对象，如果未创建成功则退出
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
return(false);
}
//创建Excel的工作簿
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
range = (Excel.Range)worksheet.get_Range("A1", "O1"); //获取表格中第一行
range.Merge(0); //合并第一行
worksheet.Cells[1, 1] = this.selectyear.SelectedValue + "年度报销费用-部门报销费用按月度汇总"; //大标题
range.Font.Size = 22;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
//写入标题
for (int i = 0; i < dt.Columns.Count + 1; i++)
{
//写入标题名称
if (i == 0)
{
worksheet.Cells[2, i + 1] = "序号";
}
else
{
worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];//从第二行的第一格开始写数据
}
//设置标题的样式
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
range.Interior.ColorIndex = 15;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色
if (i == 0) //序号列宽度自动
{
range.EntireColumn.AutoFit();
}
else
{
//设置单元格的宽度，如果小于9就设置为9，如果大于。则设置为自动
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
//写入DataTable中数据的内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count + 1; c++)
{
range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
//写入内容
if (c == 0)
{
if (r == dt.Rows.Count - 1)
{
worksheet.Cells[r + 3, c + 1] = "";
}
else
{
worksheet.Cells[r + 3, c + 1] = (r + 1).ToString(); //得到序号
}
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
}
else if (c == 1)
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
}
else
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString().Split(',')[0];
range.NumberFormat = "#,##0.00";
}
//设置样式
range.Font.Size = 9; //字体大小
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
//设置单元格的宽度，如果小于8.5就设置为8.5，如果大于。则设置为自动
if (c == 0) //序号列宽度设为自动
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
if (r == dt.Rows.Count - 1)
{
range = (Excel.Range)worksheet.get_Range("A" + (r + 3).ToString(), "B" + (r + 3).ToString());
range.Merge(0);
range.Value = "合计";
range.Font.Size = 9;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
range.EntireColumn.AutoFit();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开！\n" + ex.ToString());
return(false);
}
workbook.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return(true);
}
public void MergeExcel(string FileName, C1.Win.C1FlexGrid.C1FlexGrid fg)
{
try
{
oXL = new Excel.Application();
oWB = (Excel._Workbook)(oXL.Workbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.UsedRange.Cells.Borders.Value = 1;
// Thực hiện Merge
C1.Win.C1FlexGrid.CellRange cr;
object obj;
for (int i = 0; i < fg.Rows.Count; i++)
{
if (fg.Rows[i].Visible)
{
for (int j = 0; j < fg.Cols.Count; j++)
{
if (fg.Cols[j].Visible)
{
cr = fg.GetMergedRange(i, j);
if ((!cr.IsSingleCell && (cr.r1 == i)) && (cr.c1 == j))
{
oRng = oSheet.get_Range(TinhToaDo(cr.r1 + 1, cr.c1 + 1), TinhToaDo(cr.r2 + 1, cr.c2 + 1));
obj = fg[i, j];
oRng.Value2 = string.Empty;
oRng.Merge(Missing.Value);
oRng.Value2 = ReturnValue(obj);
}
}
}
}
}
// Xóa các cột, hàng đã bị ẩn
for (int i = oSheet.UsedRange.Rows.Count - 1; i > 0; i--)
{
oRng = oSheet.get_Range("A" + i.ToString(), Missing.Value);
if ((bool)oRng.EntireRow.Hidden == true)
{
oRng.EntireRow.Delete(Excel.XlDirection.xlToRight);
}
}
for (int i = oSheet.UsedRange.Columns.Count - 1; i > 0; i--)
{
oRng = oSheet.get_Range(TinhToaDo(1, i), Missing.Value);
if ((bool)oRng.EntireColumn.Hidden == true)
{
oRng.EntireColumn.Delete(Excel.XlDirection.xlDown);
}
}
oWB.Save();
oXL.Visible = true;
}
catch (Exception ex)
{
//oXL.Quit();
throw ex;
}
}
