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