protected void BusiIncomeExportToExcel(string tabname, string tabdate, DataTable dtIncome)
{
try
{
Excel.Application xapp = new Excel.ApplicationClass();
Excel.Workbook xbook = xapp.Workbooks.Open(Application.StartupPath + @"\BusiIncomeModel.xls", 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);
Excel.Worksheet xSheet = (Excel.Worksheet)xbook.Sheets["业务量"]; //得到Sheet
xSheet.get_Range("A1", Missing.Value).Value2 = tabname;
xSheet.get_Range("A2", Missing.Value).Value2 = tabdate;
for (int i = 1; i < dtIncome.Rows.Count - 2; i++)
{
for (int j = 1; j < 8; j++)
{
xSheet.Cells[i + 3, j + 1] = dtIncome.Rows[i][j].ToString();
}
}
for (int i = 1; i < 8; i++)
{
xSheet.Cells[21, i + 1] = dtIncome.Rows[18][i].ToString();
}
SaveFileDialog SaveFileDialog1 = new SaveFileDialog();
SaveFileDialog1.Filter = "Excel文件（*.xls）|*.xls";
SaveFileDialog1.FileName = SysInitial.CP + "业务量报表" + DateTime.Now.ToShortDateString() + ".xls";
if (SaveFileDialog1.ShowDialog() == DialogResult.OK)
{
xbook.SaveCopyAs(SaveFileDialog1.FileName); //另存
xbook.Close(false, Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value); //关闭
xSheet = null;
xbook = null;
xapp.Quit();
xapp = null;
}
else
{
xbook.Close(false, Missing.Value, Missing.Value); //关闭
xSheet = null;
xbook = null;
xapp.Quit();
xapp = null;
}
}
catch (Exception err)
{
MessageBox.Show("导出时出错，请重试！", "系统提示", MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
clog.WriteLine(err);
}
finally
{
}
}
public static void ConvertExcel(string openPath, string savePath, ref string errText)
{
errText = string.Empty;
try
{
//将xml文件转换为标准的Excel格式
Object Nothing = Missing.Value; //由于yongCOM组件很多值需要用Missing.Value代替
Excel.Application ExclApp = new Excel.ApplicationClass(); // 初始化
Excel.Workbook ExclDoc = ExclApp.Workbooks.Open(openPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); //打开Excl工作薄
try
{
Object format = Excel.XlFileFormat.xlWorkbookNormal; //获取Excl 2007文件格式
ExclApp.DisplayAlerts = false;
ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //保存为Excl 2007格式
}
catch (Exception ex)
{
errText = ex.Message;
}
finally
{
ExclDoc.Close(Nothing, Nothing, Nothing);
ExclApp.Quit();
}
}
catch (Exception ex)
{
errText = ex.Message;
}
}
public List <string> GetNombresHojas(string archivo)
{
List <string> resultado = new List <string>();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
for (int i = 1; i <= xlWorkBook.Worksheets.Count; i++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[i];
resultado.Add(xlWorkSheet.Name);
ReleaseObject(xlWorkSheet);
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return(resultado);
}
public DataTable LeerSeries(DataTable tabla, string archivo, int hoja, int filaInicio, int colInicio, int filaFin, int colFin)
{
DataTable resultado = tabla;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
for (int i = filaInicio; i <= filaFin; i++)
{
DataRow r = resultado.NewRow();
int idx = 2;
for (int j = colInicio; j <= colFin; j++)
{
r[idx] = (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2;
idx++;
}
resultado.Rows.Add(r);
}
ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return(resultado);
}
internal DataTable LeerSeriesParaModificacionDatos(DataTable tabla, string archivo, int hoja, int filaInicio, int colInicio, int filaFin, int colFin, int anioIncio, int anioFin)
{
DataTable resultado = tabla;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
int cantidadFilas = filaInicio + (anioFin - anioIncio);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
for (int i = filaInicio; i <= filaFin; i++)
{
int idx = 2;
int indiceTabla = ObtenerNumFilaDataRow(resultado, anioIncio);
if (indiceTabla == -1)
{
break;
}
else
{
DataRow r = resultado.Rows[indiceTabla];
for (int j = colInicio; j <= colFin; j++)
{
if ((xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2 == null || !Utiles.EsDecimalPositivoYCero((xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2.ToString()))
{
_resultado = false;
break;
}
else
{
r[idx] = (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2;
idx++;
}
}
if (_resultado)
{
anioIncio++;
}
else
{
break;
}
}
}
ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return(resultado);
}
/// tabla identificadores
public DataTable LeerSeriesIdentificadores(int adicionarInicio, DataTable tabla, string archivo, int hoja, int filaInicio, int colInicio, int filaFin, int colFin)
{
DataTable resultado = tabla;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
_resultado = true;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
if (adicionarInicio == 0)
{
for (int i = filaInicio; i <= filaFin; i++)
{
DataRow r = resultado.NewRow();
int idx = 3;
for (int j = colInicio; j <= colFin; j++)
{
if ((xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2 == null || !Utiles.EsDecimalPositivoYCero((xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2.ToString()))
{
_resultado = false;
break;
}
else
{
r[idx] = (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2;
idx++;
}
}
if (_resultado)
{
resultado.Rows.Add(r);
}
else
{
break;
}
}
}
ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
if (_resultado)
{
if (resultado.Rows.Count != 7)
{
_resultado = false;
}
}
return(resultado);
}
public DataTable Leer(string archivo, int hoja, int filaInicio, int colInicio, int filaFin, int colFin)
{
DataTable resultado = new DataTable();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
for (int j = colInicio; j <= colFin; j++)
{
Type t = null;
if ((xlWorkSheet.Rows.Cells[filaInicio, j] as Excel.Range).Value2 == null)
{
t = typeof(string);
}
else
{
t = (xlWorkSheet.Rows.Cells[filaInicio, j] as Excel.Range).Value2.GetType();
}
DataColumn c = new DataColumn("col" + j, t);
resultado.Columns.Add(c);
}
for (int i = filaInicio; i <= filaFin; i++)
{
DataRow r = resultado.NewRow();
int idx = 0;
for (int j = colInicio; j <= colFin; j++)
{
r[idx] = (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2 == null ? DBNull.Value : (xlWorkSheet.Rows.Cells[i, j] as Excel.Range).Value2;
idx++;
}
resultado.Rows.Add(r);
}
ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return(resultado);
}
/// <summary>
/// 导出gridview为EXCEL
/// </summary>
/// <param name="GridView1"></param>
public void Export(GridView GridView1)
{
//Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
object oMissing = System.Reflection.Missing.Value;
oExcel.Workbooks.Add(oMissing);
Excel.Workbook oBook = oExcel.Workbooks[1];
Excel.Worksheet oSheet = (Excel.Worksheet)oBook.Sheets[1];
oSheet.Name = "最终名单";//this.Title;
Excel.Range rg;
//String test = GridView1.Rows[0].Cells[0].Text;
for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
{
rg = ((Excel.Range)oSheet.Cells[1, j + 1]);
rg.FormulaR1C1 = GridView1.HeaderRow.Cells[j].Text;
}
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int j = 0; j < GridView1.Rows[0].Cells.Count; j++)
{
rg = ((Excel.Range)oSheet.Cells[i + 2, j + 1]);
rg.NumberFormatLocal = "@";//设置单元格格式为文本
rg.FormulaR1C1 = GridView1.Rows[i].Cells[j].Text;
}
}
rg = null;
string VirFileName = Guid.NewGuid().ToString() + ".xls";
oBook.SaveAs(Server.MapPath(VirFileName), Excel.XlFileFormat.xlExcel9795, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive,
oMissing, oMissing, oMissing, oMissing, oMissing);
oExcel.Workbooks.Close();
oExcel.Quit();
oSheet = null;
oBook = null;
oExcel = null;
GC.Collect();
Response.Redirect(VirFileName);
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="selectstring">选择导出数据的SQL选择语句</param>
public static void DataOut(SaveFileDialog dialog, string selectstring)
{
if (dialog.ShowDialog() == DialogResult.OK)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
worksheet.Cells.NumberFormatLocal = "@";
///////////////////////////////////
SqlConnection connect = InitConnect.GetConnection();
SqlDataReader read = null;
try
{
connect.Open();
SqlCommand cmd = new SqlCommand(selectstring, connect);
read = cmd.ExecuteReader();
for (int i = 0; i < read.FieldCount; i++)
{
worksheet.Cells[1, i + 1] = read.GetName(i).Trim();
}
int row = 2;
int count = 0;
while (read.Read())
{
count++;
for (int i = 0; i < read.FieldCount; i++)
{
worksheet.Cells[row, i + 1] = read[i].ToString().Trim();
}
row++;
}
MessageBox.Show("成功导出" + count.ToString() + "条记录！", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
catch (Exception ee)
{
MessageBox.Show("错误：" + ee.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
finally
{
read.Close();
connect.Close();
object change = false, filename = dialog.FileName;
workbook.SaveCopyAs(filename);
workbook.Close(change, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excel.Quit();
}
}
}
protected override void OnUnload(EventArgs e)
{
try
{
if (appOP != null)
{
appOP.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(appOP);
appOP = null;
}
}
catch (Exception eqq)
{
Response.Write(eqq.ToString());
}
base.OnUnload(e);
}
public static void Export_To_Excel(DataGridView dtGridView, string filename, string sheetName)
{
int i, j;
object missing = Type.Missing;
Excel.ApplicationClass excellApp;
excellApp = new Excel.ApplicationClass();
excellApp.Application.Workbooks.Add(true);
try
{
// Add columns name to excel file
for (i = 0; i < dtGridView.Columns.Count; i++)
{
excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name;
}
for (i = 0; i < dtGridView.Rows.Count; i++)
{
for (j = 0; j < dtGridView.Columns.Count; j++)
{
excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value;
}
}
//excellApp.Save(("Loinhuan.xls");
Excel._Worksheet worksheet = (Excel._Worksheet)excellApp.ActiveSheet;
worksheet.Activate();
worksheet.Name = sheetName;
worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls");
excellApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
public static void Export_To_Excel(DataGridView dtGridView, string filename, string sheetName)
{
int i, j;
object missing = Type.Missing;
Excel.ApplicationClass excellApp;
excellApp = new Excel.ApplicationClass();
excellApp.Application.Workbooks.Add(true);
try
{
// Add columns name to excel file
for (i = 0; i < dtGridView.Columns.Count; i++)
{
excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name;
}
for (i = 0; i < dtGridView.Rows.Count; i++)
{
for (j = 0; j < dtGridView.Columns.Count; j++)
{
excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value;
}
}
//excellApp.Save(("Loinhuan.xls");
Excel._Worksheet worksheet = (Excel._Worksheet)excellApp.ActiveSheet;
worksheet.Activate();
worksheet.Name = sheetName;
worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls");
excellApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
private void buttonX11_Click(object sender, EventArgs e)
{
Excel.Application xls_exp = null;
//int rowindex = 1;
int colindex = 0;
//创建一个workbook,一个worksheet
Excel._Workbook xls_book = null;
Excel._Worksheet xls_sheet = null;
try
{
xls_exp = new Excel.ApplicationClass();
xls_book = xls_exp.Workbooks.Add(true);
xls_sheet = (Excel._Worksheet)xls_book.ActiveSheet;
//C#创建Excel文件之取得数据
DataTable aa = GetData();
//将所得到的表的列名,赋值给单元格
foreach (DataColumn col in aa.Columns)
{
colindex++;
int i = 0;
i = aa.Rows.Count;
xls_exp.Cells[1, colindex] = col.ColumnName;
//水平对齐
xls_sheet.get_Range(xls_exp.Cells[1, colindex],
xls_exp.Cells[1, colindex]).HorizontalAlignment =
Excel.XlVAlign.xlVAlignCenter;
//C#创建Excel文件之垂直对齐
xls_sheet.get_Range(xls_exp.Cells[1, colindex],
xls_exp.Cells[1, colindex]).VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
//行高、列宽自适应
// xls_sheet.Cells.Rows.AutoFill(null,Excel.XlAutoFillType.xlFillDefault);
((Excel.Range)xls_sheet.Columns["A:A", System.Type.Missing]).ColumnWidth = 22;
((Excel.Range)xls_sheet.Columns["B:B", System.Type.Missing]).ColumnWidth = 18;
((Excel.Range)xls_sheet.Columns["C:C", System.Type.Missing]).ColumnWidth = 14;
((Excel.Range)xls_sheet.Columns["D:D", System.Type.Missing]).ColumnWidth = 18;
((Excel.Range)xls_sheet.Columns["E:E", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["F:F", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["G:G", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["H:H", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["I:I", System.Type.Missing]).NumberFormatLocal = "0.00";
((Excel.Range)xls_sheet.Columns["J:J", System.Type.Missing]).NumberFormatLocal = "0.00";
xls_sheet.get_Range(xls_exp.Cells[1, colindex],
xls_exp.Cells[i + 1, colindex]).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
int rowIndex = 1;
int colIndex = 0;
foreach (DataRow row in aa.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in aa.Columns)
{
colIndex++;
xls_exp.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
//不可见,即后台处理
xls_exp.Visible = true;
xls_sheet.Protect("123");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
xls_exp.Quit();
}
}
private bool InToExcel(string ExcelFile)
{
Excel.Application ExcelObj = null;
try
{
ExcelObj = new Excel.ApplicationClass();
ExcelObj.Visible = false;
Excel.Workbook ExcelBook = ExcelObj.Workbooks._Open(ExcelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet ExcelSheet = (Excel.Worksheet)ExcelBook.Sheets[1];
//死者
DeadManText.Text = ExcelCellToString(ExcelSheet.get_Range("B1", Type.Missing));
//性别
if (ExcelCellToString(ExcelSheet.get_Range("G1", Type.Missing)) == "男")
DeadSexComBoBox.SelectedIndex = 0;
else
DeadSexComBoBox.SelectedIndex = 1;
//出殡时间
DeadManTimeText.Text = ExcelCellToDate(ExcelSheet.get_Range("B2", Type.Missing));
//告别厅
DeadManAddressText.Text = ExcelCellToString(ExcelSheet.get_Range("F2", Type.Missing));
//列表用户
if (GridViewControl.RowCount > 1)
GridViewControl.Rows.Clear();
int i = 4;
int j = 0;
while (true)
{
string Cell1 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 1]);
if (String.IsNullOrEmpty(Cell1))
{
break;
}
else
{
GridViewControl.Rows.Add();
GridViewControl.Rows[j].Cells[0].Value = Cell1;
//称谓(前)
string Cell2 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 2]);
GridViewControl.Rows[j].Cells[1].Value = Cell2;
//称谓(后)
string Cell3 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 3]);
GridViewControl.Rows[j].Cells[2].Value = Cell3;
//姓名1
string Cell4 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 4]);
GridViewControl.Rows[j].Cells[3].Value = Cell4;
//姓名2
string Cell5 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 5]);
GridViewControl.Rows[j].Cells[4].Value = Cell5;
//项目
string Cell6 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 6]);
GridViewControl.Rows[j].Cells[5].Value = Cell6;
//价钱
string Cell7 = ExcelCellToString((Excel.Range)ExcelSheet.Cells[i, 7]);
GridViewControl.Rows[j].Cells[6].Value = Cell7;
FontColorClass FontColorObj = new FontColorClass();
if (FontColorDefault == null)
{
FontColorObj.FontObj = new Font("隶书", (float)40, FontStyle.Bold);
FontColorObj.ColorObj = Color.Black;
}
else
{
FontColorObj = FontColorDefault;
}
FontColorList.Add(FontColorObj);
GridViewSetFont(j, GridViewControl.ColumnCount - 1, FontColorObj.FontObj);
i++;
j++;
}
}
ExcelSheet = null;
ExcelBook.Close(false, false, false);
ExcelBook = null;
ExcelObj.Quit();
ExcelObj = null;
//合计
TotalPriceText.Text = SumCellPrice(6).ToString();
return true;
}
catch (Exception ex)
{
ExcelObj.Quit();
ExcelObj = null;
MessageBox.Show(ex.Message, "导入错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
/// <summary>
/// 将DataTable的数据导出显示为报表
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public static void OutputExcel(System.Data.DataTable dt, string strTitle, string filePath)
{
var beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = 15;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
//取得整个报表的标题
excel.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick; //设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick; //设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick; //设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick; //设置下边线加粗
var afterTime = DateTime.Now;
//显示效果
//excel.Visible = true;
//excel.Sheets[0] = "sss";
// ClearFile(FilePath);
//string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
//excel.ActiveWorkbook.SaveAs(filePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
try
{
excel.DisplayAlerts = false;
excel.Save();
}
catch (Exception)
{
return;
}
#region 结束Excel进程
//需要对Excel的DCOM对象进行配置:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//垃圾回收
#endregion
return;
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="ds"></param>
/// <param name="fileName"></param>
public static void ExportToExcel(DataSet ds, string fileName)
{
OleDbConnection conn = null;
try
{
//加工文件名
string[] strTemp = fileName.Split('\\');
string NewName = string.Empty;
foreach (string s in strTemp)
{
NewName = NewName + "\\\\" + s;
}
NewName = NewName.TrimStart('\\');
if (ds.Tables.Count == 0)
{
return;
}
Excel._Application xlApp = new Excel.ApplicationClass();
Excel._Workbook xlBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
if (ds.Tables.Count > 1)
{
int i = ds.Tables.Count;
while (i > 1)
{
xlBook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
i--;
}
}
int loop = 1;
foreach (DataTable dt2 in ds.Tables)
{
FillExcelCaption((Excel._Worksheet)xlBook.Worksheets[loop], dt2);
loop++;
}
xlBook.SaveCopyAs(fileName);
xlBook.Saved = true;
xlApp.Quit();
Kill((Excel.Application)xlApp);
//取数据表
//string con = string.Format("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0}; Extended Properties = Excel 8.0", NewName);
//conn = new OleDbConnection(con);
//conn.Open();
ExcelConn(NewName, ref conn);
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
foreach (DataTable dt in ds.Tables)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("INSERT INTO [{0}$] (", dt.TableName);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Columns[i].Caption))
{
sql.AppendFormat("[{0}],", dt.Columns[i].Caption);
}
}
sql.Remove(sql.Length - 1, 1);
sql.Append(") VALUES ");
string head = sql.ToString();
for (int j = 0; j < dt.Rows.Count; j++)
{
sql = new StringBuilder();
sql.Append(head);
sql.Append(" (");
for (int k = 0; k < dt.Columns.Count; k++)
{
if (!string.IsNullOrEmpty(dt.Columns[k].Caption))
{
if (dt.Columns[k].DataType.Equals(typeof(String)) ||
dt.Columns[k].DataType.Equals(typeof(DateTime)) ||
dt.Columns[k].DataType.Equals(typeof(Char)))
{
sql.AppendFormat("'{0}'", dt.Rows[j][k].ToString());
}
else
{
sql.AppendFormat("{0}", dt.Rows[j][k].ToString().Trim().Equals(string.Empty) ? 0 : Convert.ToDecimal(dt.Rows[j][k]));
}
sql.Append(",");
}
}
sql.Remove(sql.Length - 1, 1);
sql.Append(") ");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
private void XuatDuLieuRaExcel(DataTable dtChiTiet, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int i = 0;
int DongBatDau = 6, MonID = 0;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
excel.Cells[3, 7] = uctrlLop.trlLop.FocusedNode["TenLop"].ToString().ToUpper();
cel = (Excel.Range)excel.Cells[3, 7];
cel.Font.Bold = true;
excel.Cells[4, 3] = Program.HocKy.ToString();
cel = (Excel.Range)excel.Cells[4, 3];
excel.Cells[4, 7] = Program.NamHoc;
cel = (Excel.Range)excel.Cells[4, 7];
// Them các tiêu đề cột môn học phía sau
for (int j = 0; j < dtMonHoc.Rows.Count; j++)
{
excel.Cells[DongBatDau, j * 2 + 3 + 1] = dtMonHoc.Rows[j]["MaMonHoc"];
cel = (Excel.Range)(excel.Cells[DongBatDau, j * 2 + 3 + 1]);
excel.Cells[DongBatDau + 1, j * 2 + 3 + 1] = dtMonHoc.Rows[j]["SoHocTrinh"];
cel = (Excel.Range)(excel.Cells[DongBatDau + 1, j * 2 + 3 + 1]);
}
for (i = 0; i < dtChiTiet.Rows.Count; i++)
{
excel.Cells[i + DongBatDau + 3, 1] = i + 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 1]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau + 3, 2] = dtChiTiet.Rows[i]["HoVaTen"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 2]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau + 3, 3] = dtChiTiet.Rows[i]["NgaySinh"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 3]);
cel.Borders.Value = 1;
int Index = 0;
DataTable dt = new DataTable();
dt = oBKQHT_DiemTongKetHocKy.GetDiemTongKet(int.Parse(dtChiTiet.Rows[i]["SV_SinhVienID"].ToString()), Program.IDNamHoc, Program.HocKy);
for (int j = 0; j < dtChiTiet.Columns.Count; j++)
{
if (int.TryParse(dtChiTiet.Columns[j].ColumnName, out MonID))
{
if (cmbLanThi.SelectedIndex == 0)
{
excel.Cells[i + DongBatDau + 3, 4 + 2 * Index] = dtChiTiet.Rows[i][dtChiTiet.Columns[j].ColumnName];
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 4 + 2 * Index]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau + 3, 5 + 2 * Index] = "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 5 + 2 * Index]);
cel.Borders.Value = 1;
}
else
{
DataRow[] dr = dt.Select("DM_MonHOcID=" + dtChiTiet.Columns[j].ColumnName);
if (dr != null && dr.Length > 0)
{
excel.Cells[i + DongBatDau + 3, 4 + 2 * Index] = dr[0]["DiemLan1"];
excel.Cells[i + DongBatDau + 3, 5 + 2 * Index] = dr[0]["DiemLan2"];
}
else
{
excel.Cells[i + DongBatDau + 3, 4 + 2 * Index] = "";
excel.Cells[i + DongBatDau + 3, 5 + 2 * Index] = "";
}
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 4 + 2 * Index]);
cel.Borders.Value = 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 5 + 2 * Index]);
cel.Borders.Value = 1;
}
Index += 1;
}
}
excel.Cells[i + DongBatDau + 3, 24] = dtChiTiet.Rows[i]["DiemTK"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 3, 24]);
cel.Borders.Value = 1;
}
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatChiTietGioGiangRaExcel(DataTable dtChiTiet, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 7, DongKetThuc = DongBatDau, STT = 1;
double SoTietTC = 0, SoTietCD = 0, TongThucHien = 0, DinhMuc = 0;
bool IsDaThemDong = false;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
DataTable dtDinhMuc = (new cBGG_DinhMucGioDay()).GetByIDNS_DonVi((int)cmbDonVi.EditValue, Program.IDNamHoc, Program.HocKy);
// Ten don vi
excel.Cells[4, 1] = cmbDonVi.Text.ToUpper();
// Năm học, học kỳ
excel.Cells[5, 1] = "HỌC KỲ " + Program.HocKy.ToString() + " NĂM HỌC " + Program.NamHoc;
Lib.clsDataTableHelper clsDt = new Lib.clsDataTableHelper();
DataTable dtGV = clsDt.SelectDistinct(dtChiTiet, new string[] { "Ten", "HoTen", "NS_GiaoVienID" }, new string[] { "KhoaDonVi" });
DataView dv = new DataView(dtGV);
dv.Sort = "KhoaDonVi DESC, Ten, HoTen";
dtGV = dv.ToTable();
DataRow[] arrDr;
string IDNS_GiaoVien = dtGV.Rows[0]["NS_GiaoVienID"].ToString();
arrDr = dtDinhMuc.Select("IDNS_GiaoVien = " + dtGV.Rows[0]["NS_GiaoVienID"]);
DinhMuc = (arrDr.Length <= 0 ? 0 : double.Parse(arrDr[0]["SoGioDinhMuc"].ToString()));
// Them dong moi
cel = (Excel.Range)(excel.Cells[DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
IsDaThemDong = true;
excel.Cells[DongKetThuc, 1] = STT.ToString();
STT++;
excel.Cells[DongKetThuc, 2] = dtGV.Rows[0]["HoTen"].ToString();
foreach (DataRow dr in dtGV.Rows)
{
if (IDNS_GiaoVien != dr["NS_GiaoVienID"].ToString())
{
// Gan gia tri cho row dau tien cua giao vien
excel.Cells[DongBatDau, 12] = SoTietTC;
excel.Cells[DongBatDau, 13] = SoTietCD;
excel.Cells[DongBatDau, 14] = DinhMuc;
excel.Cells[DongBatDau, 15] = TongThucHien;
if (DinhMuc - TongThucHien < 0)
{
excel.Cells[DongBatDau, 16] = TongThucHien - DinhMuc;
}
else
{
excel.Cells[DongBatDau, 17] = DinhMuc - TongThucHien;
}
// Merge va border cac dong trong cac cot
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongKetThuc - 1, 1]);
cel.Merge(null);
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cel.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
cel = excel.get_Range(excel.Cells[DongBatDau, 2], excel.Cells[DongKetThuc - 1, 2]);
cel.Merge(null);
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
cel = excel.get_Range(excel.Cells[DongBatDau, 2], excel.Cells[DongKetThuc - 1, 2]);
cel.Merge(null);
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
for (int j = 12; j <= 17; j++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongKetThuc - 1, j]);
cel.Merge(null);
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
// Them dong moi cho giao vien moi
cel = (Excel.Range)(excel.Cells[DongKetThuc + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
IsDaThemDong = true;
IDNS_GiaoVien = dr["NS_GiaoVienID"].ToString();
DongBatDau = DongKetThuc;
SoTietTC = 0;
SoTietCD = 0;
TongThucHien = 0;
arrDr = dtDinhMuc.Select("IDNS_GiaoVien = " + IDNS_GiaoVien);
DinhMuc = (arrDr.Length <= 0 ? 0 : double.Parse(arrDr[0]["SoGioDinhMuc"].ToString()));
excel.Cells[DongKetThuc, 1] = STT.ToString();
STT++;
excel.Cells[DongKetThuc, 2] = dr["HoTen"].ToString();
}
arrDr = dtChiTiet.Select("NS_GiaoVienID = " + IDNS_GiaoVien + " And wType = 'GD'");
if (arrDr.Length > 0)
{
for (int j = 0; j < arrDr.Length; j++)
{
if (!IsDaThemDong)
{
cel = (Excel.Range)(excel.Cells[DongKetThuc + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
}
excel.Cells[DongKetThuc, 3] = arrDr[j]["TenMonHoc"];
excel.Cells[DongKetThuc, 4] = arrDr[j]["TenLop"] + " (" + arrDr[j]["SoSinhVien"] + ")";
if (double.Parse("0" + arrDr[j]["LyThuyet"]) > 0)
{
excel.Cells[DongKetThuc, 5] = arrDr[j]["LyThuyet"];
ThuocTrinhDo(arrDr[j]["IDDM_TrinhDo"].ToString(), double.Parse(arrDr[j]["LyThuyet"].ToString()),
ref SoTietCD, ref SoTietTC);
}
cel = (Excel.Range)(excel.Cells[DongKetThuc, 6]);
if (double.Parse("0" + arrDr[j]["LyThuyetQuyChuan"]) > 0)
{
excel.Cells[DongKetThuc, 6] = arrDr[j]["LyThuyetQuyChuan"];
cel.NumberFormat = "#,##0.00";
}
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
if (double.Parse("0" + arrDr[j]["ThucHanh"]) > 0)
{
excel.Cells[DongKetThuc, 7] = arrDr[j]["ThucHanh"];
ThuocTrinhDo(arrDr[j]["IDDM_TrinhDo"].ToString(),
double.Parse(arrDr[j]["ThucHanh"].ToString()) * SoTietQuyDoiThucHanhTrongTuan / SoGioThucHanhTrongTuan,
ref SoTietCD, ref SoTietTC);
excel.Cells[DongKetThuc, 8] = arrDr[j]["SoNhom"];
}
cel = (Excel.Range)(excel.Cells[DongKetThuc, 9]);
if (double.Parse("0" + arrDr[j]["ThucHanhQuyChuan"]) > 0)
{
excel.Cells[DongKetThuc, 9] = arrDr[j]["ThucHanhQuyChuan"];
cel.NumberFormat = "#,##0.00";
}
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cel = (Excel.Range)(excel.Cells[DongKetThuc, 10]);
if (double.Parse("0" + arrDr[j]["HeSoLopDong"]) > 0)
{
excel.Cells[DongKetThuc, 10] = arrDr[j]["HeSoLopDong"];
cel.NumberFormat = "#,##0.00";
}
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cel = (Excel.Range)(excel.Cells[DongKetThuc, 11]);
if (double.Parse("0" + arrDr[j]["GioQuyChuan"]) > 0)
{
excel.Cells[DongKetThuc, 11] = arrDr[j]["GioQuyChuan"];
cel.NumberFormat = "#,##0.00";
}
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
TongThucHien += double.Parse(arrDr[j]["GioQuyChuan"].ToString());
IsDaThemDong = false;
DongKetThuc++;
}
}
arrDr = dtChiTiet.Select("NS_GiaoVienID = " + IDNS_GiaoVien + " And wType = 'CVK'");
if (arrDr.Length > 0)
{
for (int j = 0; j < arrDr.Length; j++)
{
if (!IsDaThemDong)
{
cel = (Excel.Range)(excel.Cells[DongKetThuc + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
}
excel.Cells[DongKetThuc, 3] = arrDr[j]["TenMonHoc"];
excel.Cells[DongKetThuc, 4] = arrDr[j]["GhiChu"];
cel = (Excel.Range)(excel.Cells[DongKetThuc, 11]);
excel.Cells[DongKetThuc, 11] = arrDr[j]["GioQuyChuan"];
cel.NumberFormat = "#,##0.00";
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
SoTietCD += double.Parse(arrDr[j]["GioQuyChuan"].ToString());
TongThucHien += double.Parse(arrDr[j]["GioQuyChuan"].ToString());
IsDaThemDong = false;
DongKetThuc++;
}
}
}
// Gan gia tri cho row dau tien cua giao vien
excel.Cells[DongBatDau, 12] = SoTietTC;
excel.Cells[DongBatDau, 13] = SoTietCD;
excel.Cells[DongBatDau, 14] = DinhMuc;
excel.Cells[DongBatDau, 15] = TongThucHien;
if (DinhMuc - TongThucHien < 0)
{
excel.Cells[DongBatDau, 16] = TongThucHien - DinhMuc;
}
else
{
excel.Cells[DongBatDau, 17] = DinhMuc - TongThucHien;
}
// Merge va border cac dong trong cac cot
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongKetThuc - 1, 1]);
cel.Merge(null);
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cel.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
cel = excel.get_Range(excel.Cells[DongBatDau, 2], excel.Cells[DongKetThuc - 1, 2]);
cel.Merge(null);
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
cel = excel.get_Range(excel.Cells[DongBatDau, 2], excel.Cells[DongKetThuc - 1, 2]);
cel.Merge(null);
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
for (int j = 12; j <= 17; j++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongKetThuc - 1, j]);
cel.Merge(null);
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
cel = excel.get_Range(excel.Cells[7, 1], excel.Cells[DongKetThuc - 1, 17]);
cel.Borders.Value = 1;
excel.Application.Workbooks[1].Save();
excel.Visible = true;
//Process.Start(FileExcel);
}
catch (Exception e)
{
excel.Application.Workbooks[1].Save();
this.Cursor = System.Windows.Forms.Cursors.Default;
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng các file Excel trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
return;
}
finally
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(DataTable dtChiTiet, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int i = 0;
int DongBatDau = 7;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
excel.Cells[1, 1] = Program.TenTruong.ToUpper();
cel = (Excel.Range)excel.Cells[1, 1];
cel.Font.Bold = true;
excel.Cells[2, 1] = uctrlLop.trlLop.FocusedNode.ParentNode.RootNode["TenLop"];
cel = (Excel.Range)excel.Cells[2, 1];
excel.Cells[4, 3] = cmbMonHoc.Text;
cel = (Excel.Range)excel.Cells[4, 3];
excel.Cells[4, 12] = cmbMonHoc.GetColumnValue("SoTiet");
cel = (Excel.Range)excel.Cells[4, 12];
excel.Cells[5, 1] = uctrlLop.trlLop.FocusedNode["TenLop"];
cel = (Excel.Range)excel.Cells[5, 1];
// Them các tiêu đề cột phía sau
for (int j = 6; j < dtChiTiet.Columns.Count - 2; j++)
{
int intID = 0;
if (int.TryParse(dtChiTiet.Columns[j].ColumnName, out intID))
{
excel.Cells[DongBatDau, j - 4] = excel.Cells[i + DongBatDau + 1, j - 4] = ReturnTenThanhPhanDiem(dtChiTiet.Columns[j].ColumnName + "");
cel = (Excel.Range)(excel.Cells[DongBatDau, j]);
}
}
for (int k = 0; k < dtChiTiet.Rows.Count + 1; k++)
{
for (int l = 1; l <= 14; l++)
{
cel = (Excel.Range)(excel.Cells[DongBatDau + k, l]);
cel.Borders.Value = 1;
}
}
for (i = 0; i < dtChiTiet.Rows.Count; i++)
{
double DiemTB = 0;
int HeSo = 0;
excel.Cells[i + DongBatDau + 1, 1] = i + 1;
excel.Cells[i + DongBatDau + 1, 2] = dtChiTiet.Rows[i]["MaSinhVien"] + "";
excel.Cells[i + DongBatDau + 1, 3] = dtChiTiet.Rows[i]["HoVaTen"] + "";
excel.Cells[i + DongBatDau + 1, 4] = dtChiTiet.Rows[i]["NgaySinh"] + "";
for (int j = 6; j < dtChiTiet.Columns.Count - 2; j++)
{
if (dtChiTiet.Rows[i][j] + "" != "")
{
HeSo += 2;
DiemTB += 2 * (float)dtChiTiet.Rows[i][j];
}
excel.Cells[i + DongBatDau + 1, j - 1] = dtChiTiet.Rows[i][j] + "";
}
if (HeSo > 0)
{
excel.Cells[i + DongBatDau + 1, 11] = DiemTB / HeSo;
}
excel.Cells[i + DongBatDau + 1, 12] = dtChiTiet.Rows[i][dtChiTiet.Columns.Count - 2] + "";
excel.Cells[i + DongBatDau + 1, 13] = dtChiTiet.Rows[i][dtChiTiet.Columns.Count - 1] + "";
}
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(DataTable dtBaoCao, string FileExcel, string TrangThai)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 10;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
if (TrangThai == "YES")
{
excel.Cells[4, 1] = "DANH SÁCH SINH VIÊN ĐỦ ĐIỀU KIỆN TỐT NGHIỆP";
}
else
{
excel.Cells[4, 1] = "DANH SÁCH SINH VIÊN KHÔNG ĐỦ ĐIỀU KIỆN TỐT NGHIỆP";
}
excel.Cells[5, 3] = Program.NamHoc;
for (int i = 0; i < dtBaoCao.Rows.Count; i++)
{
//Xử lý cho cột 1: STT
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 1]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 1] = i + 1;
//Xử lý cho cột 2: Mã SV
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 2]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 2] = (dtBaoCao.Rows[i]["MaSinhVien"] + "");
//Xử lý cho cột 3: Tên SV
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 3]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 3] = (dtBaoCao.Rows[i]["HoVaTen"] + "");
//Xử lý cho cột 4: Ngày sinh
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 4]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
if (dtBaoCao.Rows[i]["NgaySinh"] + "" != "")
{
string str = dtBaoCao.Rows[i]["NgaySinh"] + "";
str = str.Replace("12:00:00 AM", "");
excel.Cells[i + DongBatDau + 0, 4] = str;
//excel.Cells[i + DongBatDau + 0, 4] = DateTime.Parse(dtBaoCao.Rows[i]["NgaySinh"].ToString()).ToString("dd/MM/yyyy");
}
else
{
excel.Cells[i + DongBatDau + 0, 4] = "";
}
//Xử lý cho cột 5: Điểm môn TN1
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 5]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 5] = (dtBaoCao.Rows[i]["DiemMonTotNghiep1"] + "");
//Xử lý cho cột 6: Điểm môn TN2
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 6]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 6] = (dtBaoCao.Rows[i]["DiemMonTotNghiep2"] + "");
//Xử lý cho cột 7: Điểm môn TN3
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 7]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 7] = (dtBaoCao.Rows[i]["DiemMonTotNghiep3"] + "");
//Xử lý cho cột 8: Điểm TBC TK
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 8]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 8] = (dtBaoCao.Rows[i]["DiemTrungBinhChungToanKhoa"] + "");
//Xử lý cho cột 9: Điểm TBC TN
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 9]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 9] = (dtBaoCao.Rows[i]["DiemTrungBinhChungTotNghiep"] + "");
//Xử lý cho cột 10: Điểm XL TN
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 10]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
excel.Cells[i + DongBatDau + 0, 10] = (dtBaoCao.Rows[i]["DiemXepLoaiTotNghiep"] + "");
//Xử lý cho cột 11: Điểm XL TN
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 11]);
cel.WrapText = true;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
if (TrangThai == "YES")
{
excel.Cells[i + DongBatDau, 11] = (dtBaoCao.Rows[i]["TenXepLoai"] + "");
}
}
for (int k = 1; k <= 11; k++)
{
cel = (Excel.Range)(excel.Cells[(dtBaoCao.Rows.Count + DongBatDau) - 1, k]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
}
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatBangKQHTRaExcel(DataTable dtChiTiet, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int i = 0;
int DongBatDau = 12, SoCot = 14;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
// Ten mon
cel = (Excel.Range)excel.Cells[5, 2];
excel.Cells[5, 2] = cel.Text + " " + cmbMonHoc.Text;
//cel = (Excel.Range)excel.Cells[5, 7];
excel.Cells[5, 7] = "" + pDM_LopInfo.TenLop;
cel = (Excel.Range)excel.Cells[6, 2];
excel.Cells[6, 2] = cel.Text + " " + Program.HocKy.ToString();
cel = (Excel.Range)excel.Cells[6, 7];
excel.Cells[6, 7] = cel.Text + " " + Program.NamHoc;
cel = (Excel.Range)excel.Cells[7, 2];
excel.Cells[7, 2] = cel.Text + " " + cmbMonHoc.GetColumnValue("SoTiet");
cel = (Excel.Range)excel.Cells[7, 7];
excel.Cells[7, 7] = cel.Text + " " + cmbMonHoc.GetColumnValue("SoTiet");
for (int k = 0; k < dtChiTiet.Rows.Count; k++)
{
cel = (Excel.Range)(excel.Cells[DongBatDau + k + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
for (int l = 1; l <= SoCot; l++)
{
cel = (Excel.Range)(excel.Cells[DongBatDau + k, l]);
cel.Borders.Value = 1;
}
}
string Ho_Dem = "";
for (i = 0; i < dtChiTiet.Rows.Count; i++)
{
//double DiemTB = 0;
//int HeSo = 0;
excel.Cells[i + DongBatDau, 1] = i + 1;
excel.Cells[i + DongBatDau, 2] = "" + dtChiTiet.Rows[i]["MaSinhVien"];
excel.Cells[i + DongBatDau, 4] = "" + GetTen("" + dtChiTiet.Rows[i]["HoVaTen"], ref Ho_Dem);
excel.Cells[i + DongBatDau, 3] = "" + Ho_Dem;
//excel.Cells[i + DongBatDau + 1, 4] = dtChiTiet.Rows[i]["NgaySinh"] + "";
//for (int j = 6; j < dtChiTiet.Columns.Count - 2; j++)
//{
// if (dtChiTiet.Rows[i][j] + "" != "")
// {
// HeSo += 2;
// DiemTB += 2 * (double)dtChiTiet.Rows[i][j];
// }
// excel.Cells[i + DongBatDau + 1, j - 1] = dtChiTiet.Rows[i][j] + "";
//}
//if (HeSo > 0)
//{
// excel.Cells[i + DongBatDau + 1, 11] = DiemTB / HeSo;
//}
//excel.Cells[i + DongBatDau + 1, 12] = dtChiTiet.Rows[i][dtChiTiet.Columns.Count - 2] + "";
//excel.Cells[i + DongBatDau + 1, 13] = dtChiTiet.Rows[i][dtChiTiet.Columns.Count - 1] + "";
}
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int i = 0;
int DongBatDau = 10;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
// Phần này là thêm Header.
excel.Cells[3, 2] = cmbHe.Text;
cel = (Excel.Range)excel.Cells[3, 2];
excel.Cells[3, 4] = cmbTrinhDo.Text;
cel = (Excel.Range)excel.Cells[3, 4];
excel.Cells[3, 6] = cmbKhoaHoc.Text;
cel = (Excel.Range)excel.Cells[3, 6];
excel.Cells[3, 8] = cmbKhoa.Text;
cel = (Excel.Range)excel.Cells[3, 8];
excel.Cells[7, 5] = cmbHocKy.Text;
cel = (Excel.Range)excel.Cells[7, 5];
excel.Cells[7, 7] = cmbNamHoc.Text;
cel = (Excel.Range)excel.Cells[7, 7];
// Phần này là thêm Detail.
string TenLop = grvTongHop.GetDataRow(0)["TenLop"].ToString();
double SumTongThu = 0, SumMienGiam = 0, SumPhaiNop = 0, SumDaNop = 0, SumSoTienThua = 0, SumSoTienThieu = 0, SumNoKyTruoc = 0;
for (i = 0; i < grvTongHop.DataRowCount; i++)
{
if (TenLop != grvTongHop.GetDataRow(i)["TenLop"].ToString() || i == 0)
{
excel.Cells[i + DongBatDau, 2] = grvTongHop.GetDataRow(i)["TenLop"].ToString();
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 2]);
cel.Font.Bold = true;
cel.Borders.Value = 1;
DongBatDau += 1;
}
excel.Cells[i + DongBatDau, 1] = i + 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 1]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 2] = grvTongHop.GetDataRow(i)["MaSinhVien"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 2]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 3] = grvTongHop.GetDataRow(i)["HoVaTen"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 3]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 4] = grvTongHop.GetDataRow(i)["NgaySinh"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 4]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 5] = grvTongHop.GetDataRow(i)["NoKyTruoc"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 5]);
cel.Borders.Value = 1;
SumNoKyTruoc += double.Parse("0" + grvTongHop.GetDataRow(i)["NoKyTruoc"] + "");
excel.Cells[i + DongBatDau, 6] = grvTongHop.GetDataRow(i)["TongThu"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 6]);
cel.Borders.Value = 1;
SumTongThu += double.Parse("0" + grvTongHop.GetDataRow(i)["TongThu"] + "");
excel.Cells[i + DongBatDau, 7] = grvTongHop.GetDataRow(i)["SoTienMienGiam"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 7]);
cel.Borders.Value = 1;
SumMienGiam += double.Parse("0" + grvTongHop.GetDataRow(i)["SoTienMienGiam"] + "");
excel.Cells[i + DongBatDau, 8] = grvTongHop.GetDataRow(i)["SoTienPhaiNop"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 8]);
cel.Borders.Value = 1;
SumPhaiNop += double.Parse("0" + grvTongHop.GetDataRow(i)["SoTienPhaiNop"] + "");
excel.Cells[i + DongBatDau, 9] = grvTongHop.GetDataRow(i)["SoTienDaNop"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 9]);
cel.Borders.Value = 1;
SumDaNop += double.Parse("0" + grvTongHop.GetDataRow(i)["SoTienDaNop"] + "");
excel.Cells[i + DongBatDau, 10] = grvTongHop.GetDataRow(i)["SoTienThieu"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 10]);
cel.Borders.Value = 1;
SumSoTienThieu += double.Parse("0" + grvTongHop.GetDataRow(i)["SoTienThieu"] + "");
excel.Cells[i + DongBatDau, 11] = grvTongHop.GetDataRow(i)["SoTienThua"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 11]);
cel.Borders.Value = 1;
SumSoTienThua += double.Parse("0" + grvTongHop.GetDataRow(i)["SoTienThua"] + "");
TenLop = grvTongHop.GetDataRow(i)["TenLop"].ToString();
}
for (int j = 0; j < 11; j++)
{
cel = (Excel.Range)(excel.Cells[grvTongHop.DataRowCount + DongBatDau, j + 1]);
cel.Font.Bold = true;
cel.Borders.Value = 1;
}
excel.Cells[grvTongHop.DataRowCount + DongBatDau, 2] = "Tổng cộng:";
excel.Cells[grvTongHop.DataRowCount + DongBatDau, 5] = SumNoKyTruoc;
excel.Cells[grvTongHop.DataRowCount + DongBatDau, 6] = SumTongThu;
excel.Cells[grvTongHop.DataRowCount + DongBatDau, 7] = SumMienGiam;
excel.Cells[grvTongHop.DataRowCount + DongBatDau, 8] = SumPhaiNop;
excel.Cells[grvTongHop.DataRowCount + DongBatDau, 9] = SumDaNop;
excel.Cells[grvTongHop.DataRowCount + DongBatDau, 10] = SumSoTienThieu;
excel.Cells[grvTongHop.DataRowCount + DongBatDau, 11] = SumSoTienThua;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Tổng hợp thu tiền cho sinh viên trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(DataTable dtChiTiet, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel, xin vui lòng chờ!", "Xuất dữ liệu");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int i = 0;
int DongBatDau = 9;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
// Phần này là thêm Header.
excel.Cells[5, 1] = "BÁO CÁO DANH SÁCH CÁN BỘ CÔNG CHỨC VÀ QUỸ TIỀN LƯƠNG TÍNH ĐẾN NGÀY " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).ToString("dd/MM/yyyy");
cel = (Excel.Range)excel.Cells[5, 1];
excel.Cells[7, 1] = "HSL: " + LuongCoBan.ToString("#,###") + "đ";
cel = (Excel.Range)excel.Cells[7, 1];
// Phần này thêm các tiêu đề Loại phụ cấp.
for (int j = 0; j < dtLoaiPhuCap.Rows.Count; j++)
{
excel.Cells[DongBatDau, 12 + j] = dtLoaiPhuCap.Rows[j]["TenLoaiPhuCap"];
cel = (Excel.Range)(excel.Cells[DongBatDau, 12 + j]);
}
// Phần này là thêm Detail.
DongBatDau = 11;
string TenDonVi = dtChiTiet.Rows[0]["TenDonVi"].ToString();
int STT = 0;
double SumHeSoLuong = 0, SumThanhTienLuong = 0, SumTongHeSoPhuCap = 0, SumTongTienLuongVaPhuCap = 0, SumThanhTienPhuCap = 0,
SumPhuCap1 = 0, SumPhuCap2 = 0, SumPhuCap3 = 0, SumPhuCap4 = 0, SumPhuCap5 = 0, SumPhuCap6 = 0;
for (i = 0; i < dtChiTiet.Rows.Count; i++)
{
if (TenDonVi != dtChiTiet.Rows[i]["TenDonVi"].ToString() || i == 0)
{
STT = 0;
excel.Cells[i + DongBatDau, 2] = dtChiTiet.Rows[i]["TenDonVi"].ToString();
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 2]);
cel.Font.Bold = true;
cel.Borders.Value = 1;
DongBatDau += 1;
}
excel.Cells[i + DongBatDau, 1] = STT + 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 1]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 2] = dtChiTiet.Rows[i]["HoTen"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 2]);
cel.Borders.Value = 1;
if ((dtChiTiet.Rows[i]["TenGioiTinh"] + "").ToUpper() == "NAM")
{
excel.Cells[i + DongBatDau, 3] = dtChiTiet.Rows[i]["NgaySinh"] + "";
}
else
{
excel.Cells[i + DongBatDau, 4] = dtChiTiet.Rows[i]["NgaySinh"] + "";
}
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 3]);
cel.Borders.Value = 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 4]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 5] = dtChiTiet.Rows[i]["NgayTuyenDung"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 5]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 6] = dtChiTiet.Rows[i]["TenTrinhDoChuyenMon"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 6]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 7] = dtChiTiet.Rows[i]["TenChucDanh"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 7]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 8] = dtChiTiet.Rows[i]["MaNgachCongChuc"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 8]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 9] = dtChiTiet.Rows[i]["TuNgay"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 9]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 10] = dtChiTiet.Rows[i]["HeSoLuong"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 10]);
cel.Borders.Value = 1;
SumHeSoLuong += double.Parse("0" + dtChiTiet.Rows[i]["HeSoLuong"] + "");
excel.Cells[i + DongBatDau, 11] = dtChiTiet.Rows[i]["ThanhTienLuong"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 11]);
cel.Borders.Value = 1;
SumThanhTienLuong += double.Parse("0" + dtChiTiet.Rows[i]["ThanhTienLuong"] + "");
excel.Cells[i + DongBatDau, 18] = dtChiTiet.Rows[i]["TongHeSoPhuCap"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 18]);
cel.Borders.Value = 1;
SumTongHeSoPhuCap += double.Parse("0" + dtChiTiet.Rows[i]["TongHeSoPhuCap"] + "");
excel.Cells[i + DongBatDau, 19] = dtChiTiet.Rows[i]["ThanhTienPhuCap"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 19]);
cel.Borders.Value = 1;
SumThanhTienPhuCap += double.Parse("0" + dtChiTiet.Rows[i]["ThanhTienPhuCap"] + "");
excel.Cells[i + DongBatDau, 20] = dtChiTiet.Rows[i]["TongTienLuongVaPhuCap"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 20]);
cel.Borders.Value = 1;
SumTongTienLuongVaPhuCap += double.Parse("0" + dtChiTiet.Rows[i]["TongTienLuongVaPhuCap"] + "");
STT += 1;
// loai phu cap
// Phần này thêm các tiêu đề Loại phụ cấp.
for (int j = 0; j < dtLoaiPhuCap.Rows.Count; j++)
{
excel.Cells[i + DongBatDau, 12 + j] = dtChiTiet.Rows[i][dtLoaiPhuCap.Rows[j]["NS_LoaiPhuCapID"].ToString()] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 12 + j]);
cel.Borders.Value = 1;
if (j == 0)
{
SumPhuCap1 += double.Parse("0" + dtChiTiet.Rows[i][dtLoaiPhuCap.Rows[j]["NS_LoaiPhuCapID"].ToString()] + "");
}
if (j == 1)
{
SumPhuCap2 += double.Parse("0" + dtChiTiet.Rows[i][dtLoaiPhuCap.Rows[j]["NS_LoaiPhuCapID"].ToString()] + "");
}
if (j == 2)
{
SumPhuCap3 += double.Parse("0" + dtChiTiet.Rows[i][dtLoaiPhuCap.Rows[j]["NS_LoaiPhuCapID"].ToString()] + "");
}
if (j == 3)
{
SumPhuCap4 += double.Parse("0" + dtChiTiet.Rows[i][dtLoaiPhuCap.Rows[j]["NS_LoaiPhuCapID"].ToString()] + "");
}
if (j == 4)
{
SumPhuCap5 += double.Parse("0" + dtChiTiet.Rows[i][dtLoaiPhuCap.Rows[j]["NS_LoaiPhuCapID"].ToString()] + "");
}
if (j == 5)
{
SumPhuCap6 += double.Parse("0" + dtChiTiet.Rows[i][dtLoaiPhuCap.Rows[j]["NS_LoaiPhuCapID"].ToString()] + "");
}
}
TenDonVi = dtChiTiet.Rows[i]["TenDonVi"].ToString();
}
// =SUM(M11:M218)
// phan tong cong
for (int j = 1; j < 10; j++)
{
cel = (Excel.Range)(excel.Cells[i + DongBatDau, j]);
cel.Borders.Value = 1;
}
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 10] = SumHeSoLuong;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 10]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 11] = SumThanhTienLuong;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 11]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 12] = SumPhuCap1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 12]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 13] = SumPhuCap2;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 13]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 14] = SumPhuCap3;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 14]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 15] = SumPhuCap4;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 15]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 16] = SumPhuCap5;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 16]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 17] = SumPhuCap6;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 17]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 18] = SumTongHeSoPhuCap;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 18]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 19] = SumThanhTienPhuCap;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 19]);
cel.Borders.Value = 1;
excel.Cells[dtChiTiet.Rows.Count + DongBatDau, 20] = SumTongTienLuongVaPhuCap;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 20]);
cel.Borders.Value = 1;
// Phần này là thêm Folter.
int Folter = dtChiTiet.Rows.Count;
excel.Cells[DongBatDau + Folter + 2, 2] = "Xác nhận của cơ quan xét duyệt biên chế quỹ tiền lương";
((Excel.Range)excel.Cells[DongBatDau + Folter + 2, 2]).Font.Bold = true;
excel.Cells[DongBatDau + Folter + 2, 19] = "Hà nội, ngày " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).Day.ToString() + " tháng " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).Month.ToString() + " năm " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).Year.ToString();
((Excel.Range)excel.Cells[DongBatDau + Folter + 2, 19]).Font.Italic = true;
excel.Cells[DongBatDau + Folter + 3, 2] = "* Chỉ tiêu biên chế giao năm " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).Year + " ngành sự nghiệp giáo dục là" + " " + " người";
excel.Cells[DongBatDau + Folter + 3, 18] = "HIỆU TRƯỞNG";
((Excel.Range)excel.Cells[DongBatDau + Folter + 3, 18]).Font.Bold = true;
excel.Cells[DongBatDau + Folter + 4, 2] = "* Số người hiện có(kể cả thử việc, HĐ làm việc 68) đến ngày " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).ToString("dd/MM/yyyy") + " là " + dtChiTiet.Rows.Count + " người";
excel.Cells[DongBatDau + Folter + 5, 2] = "* Quỹ tiền lương 1 tháng " + " năm là triệu đồng, từ 1 tháng năm ";
excel.Cells[DongBatDau + Folter + 7, 2] = "Hà nội, ngày " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).Day.ToString() + " tháng " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).Month.ToString() + " năm " + DateTime.Parse(dtpTinhDenNgay.EditValue.ToString()).Year.ToString();
((Excel.Range)excel.Cells[DongBatDau + Folter + 7, 2]).Font.Italic = true;
excel.Cells[DongBatDau + Folter + 7, 18] = "Tạ Văn Hương";
((Excel.Range)excel.Cells[DongBatDau + Folter + 7, 18]).Font.Bold = true;
excel.Cells[DongBatDau + Folter + 8, 2] = "T/L Bộ trưởng Bộ Nông nghiệp & PTNN";
((Excel.Range)excel.Cells[DongBatDau + Folter + 8, 4]).Font.Bold = true;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 10, SoCot = 12;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
excel.Cells[4, 1] = "BÁO CÁO SỐ LƯỢNG CÔNG CHỨC VÀ HỌC SINH";
excel.Cells[5, 1] = "Có mặt tại trường đến " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Day + " tháng " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Month + " năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year;
excel.Cells[7, 3] = "Bình quân năm (từ " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Day + "/" + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Month + "/" + (DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year + 1).ToString() + "đến " + dtpDenNgay.Text + ")";
int count = dtBaoCao.Rows.Count;
DataRow dr;
for (int i = 0; i < count; i++)
{
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
dr = dtBaoCao.Rows[i];
excel.Cells[i + DongBatDau, 1] = i + 1;
excel.Cells[i + DongBatDau, 2] = "" + dr["TenKhoi"];
excel.Cells[i + DongBatDau, 3] = "" + dr["DaiHanTapTrung"];
excel.Cells[i + DongBatDau, 4] = "" + dr["TaiChuc"];
excel.Cells[i + DongBatDau, 7] = "" + dr["TongSoBienChe"];
excel.Cells[i + DongBatDau, 8] = "" + dr["DungLopBienChe"];
excel.Cells[i + DongBatDau, 9] = "" + dr["PhucVuBienChe"];
excel.Cells[i + DongBatDau, 10] = "" + dr["TongSoNgoaiBienChe"];
excel.Cells[i + DongBatDau, 11] = "" + dr["DungLopNgoaiBienChe"];
excel.Cells[i + DongBatDau, 12] = "" + dr["PhucVuNgoaiBienChe"];
}
// Them dong tong
cel = (Excel.Range)(excel.Cells[count + DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
excel.Cells[count + DongBatDau, 2] = "Tổng cộng";
excel.Cells[count + DongBatDau, 3] = dtBaoCao.Compute("Sum(DaiHanTapTrung)", "").ToString();
excel.Cells[count + DongBatDau, 4] = dtBaoCao.Compute("Sum(TaiChuc)", "").ToString();
excel.Cells[count + DongBatDau, 7] = dtBaoCao.Compute("Sum(TongSoBienChe)", "").ToString();
excel.Cells[count + DongBatDau, 8] = dtBaoCao.Compute("Sum(DungLopBienChe)", "").ToString();
excel.Cells[count + DongBatDau, 9] = dtBaoCao.Compute("Sum(PhucVuBienChe)", "").ToString();
excel.Cells[count + DongBatDau, 10] = dtBaoCao.Compute("Sum(TongSoNgoaiBienChe)", "").ToString();
excel.Cells[count + DongBatDau, 11] = dtBaoCao.Compute("Sum(DungLopNgoaiBienChe)", "").ToString();
excel.Cells[count + DongBatDau, 12] = dtBaoCao.Compute("Sum(PhucVuNgoaiBienChe)", "").ToString();
cel = excel.get_Range(excel.Cells[count + DongBatDau, 1], excel.Cells[DongBatDau + count, SoCot]);
cel.Font.Bold = true;
// Set style
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + count - 1, SoCot]);
cel.Borders.LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + count, 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
for (int j = 1; j <= SoCot; j++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongBatDau + count, j]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
}
cel = excel.get_Range(excel.Cells[DongBatDau + count, 1], excel.Cells[DongBatDau + count, SoCot]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(DataTable dtSinhVien, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 9, SoCot = 20;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
if (cmbKhoaHoc.ItemIndex >= 0)
{
cel = (Excel.Range)excel.Cells[4, 1];
excel.Cells[4, 1] = cel.Text + " KHÓA " + cmbKhoaHoc.Text.ToUpper();
}
DataRow dr;
int SoSinhVien = dtSinhVien.Rows.Count, SoLop = 1, k = 0;
string IDDM_Lop = dtSinhVien.Rows[0]["IDDM_Lop"].ToString();
DataRow[] arrDr;
// Insert column Lop
cel = (Excel.Range)(excel.Cells[DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
excel.Cells[DongBatDau, 2] = "Lớp: " + dtSinhVien.Rows[0]["TenLop"];
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau, SoCot]);
cel.Font.Size = 11;
cel.Font.Bold = true;
cel.Merge(null);
for (int i = 0; i < SoSinhVien; i++)
{
if (IDDM_Lop != dtSinhVien.Rows[i]["IDDM_Lop"].ToString())
{
IDDM_Lop = dtSinhVien.Rows[i]["IDDM_Lop"].ToString();
k = 0;
// Insert column Lop
cel = (Excel.Range)(excel.Cells[i + DongBatDau + SoLop + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
excel.Cells[i + DongBatDau + SoLop, 2] = "Lớp: " + dtSinhVien.Rows[i]["TenLop"];
cel = excel.get_Range(excel.Cells[i + DongBatDau + SoLop, 1], excel.Cells[i + DongBatDau + SoLop, SoCot]);
cel.Font.Size = 11;
cel.Font.Bold = true;
cel.Merge(null);
SoLop++;
}
dr = bgvDiem.GetDataRow(i);
cel = (Excel.Range)(excel.Cells[i + DongBatDau + SoLop + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
k++;
excel.Cells[i + DongBatDau + SoLop, 1] = k;
excel.Cells[i + DongBatDau + SoLop, 2] = "" + dr["MaSinhVien"];
excel.Cells[i + DongBatDau + SoLop, 3] = "" + dr["HoVa"];
excel.Cells[i + DongBatDau + SoLop, 4] = "" + dr["TenSV"];
excel.Cells[i + DongBatDau + SoLop, 5] = ("" + dr["NgaySinh"] == "" ? "" :
DateTime.Parse(dr["NgaySinh"].ToString()).ToString("dd/MM/yyyy"));
excel.Cells[i + DongBatDau + SoLop, 6] = "" + dr["NoiSinh"];
if ("" + dr["DiemTBMH"] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 7];
excel.Cells[i + DongBatDau + SoLop, 7] = double.Parse(dr["DiemTBMH"].ToString());
cel.NumberFormat = "0.0";
}
if ("" + dr["DiemTTTN"] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 8];
excel.Cells[i + DongBatDau + SoLop, 8] = double.Parse(dr["DiemTTTN"].ToString());
cel.NumberFormat = "0.0";
}
if (IDMonChinhTri != "" && "" + dr["TN_" + IDMonChinhTri] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 9];
excel.Cells[i + DongBatDau + SoLop, 9] = double.Parse(dr["TN_" + IDMonChinhTri].ToString());
cel.NumberFormat = "0.0";
}
if (IDMonLyThuyet != "" && "" + dr["TN_" + IDMonLyThuyet] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 10];
excel.Cells[i + DongBatDau + SoLop, 10] = double.Parse(dr["TN_" + IDMonLyThuyet].ToString());
cel.NumberFormat = "0.0";
}
if (IDMonThucHanh != "" && "" + dr["TN_" + IDMonThucHanh] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 11];
excel.Cells[i + DongBatDau + SoLop, 11] = double.Parse(dr["TN_" + IDMonThucHanh].ToString());
cel.NumberFormat = "0.0";
}
if ("" + dr["DiemTK"] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 15];
excel.Cells[i + DongBatDau + SoLop, 15] = double.Parse(dr["DiemTK"].ToString());
cel.NumberFormat = "0.0";
}
if ("" + dr["DiemTHXL"] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 16];
excel.Cells[i + DongBatDau + SoLop, 16] = double.Parse(dr["DiemTHXL"].ToString());
cel.NumberFormat = "0.0";
}
if ("" + dr["KQHT_XepLoaiTotNghiepID"] != "")
{
arrDr = dtXepLoai.Select("KQHT_XepLoaiTotNghiepID = " + dr["KQHT_XepLoaiTotNghiepID"]);
if (arrDr.Length > 0)
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 17];
excel.Cells[i + DongBatDau + SoLop, 17] = arrDr[0]["TenXepLoai"].ToString();
}
}
if ("" + dr["SoMonThiLai"] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 18];
excel.Cells[i + DongBatDau + SoLop, 18] = int.Parse(dr["SoMonThiLai"].ToString());
}
if ("" + dr["SoHocTrinhThiLai"] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 19];
excel.Cells[i + DongBatDau + SoLop, 19] = double.Parse(dr["SoHocTrinhThiLai"].ToString());
}
if ("" + dr["GhiChu"] != "")
{
cel = (Excel.Range)excel.Cells[i + DongBatDau + SoLop, 20];
excel.Cells[i + DongBatDau + SoLop, 20] = dr["GhiChu"].ToString();
}
}
// Set style
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, SoCot]);
cel.Borders.LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
for (int j = 1; j <= SoCot; j++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, j]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
if (j != 3)
{
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
}
else
{
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}
}
cel = excel.get_Range(excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, 1], excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, SoCot]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
/// <summary>
/// ��DataTable�����ݵ�����ʾΪ����
/// </summary>
/// <param name="dt">Ҫ����������</param>
/// <param name="strTitle">��������ı���</param>
/// <param name="FilePath">�����ļ���·��</param>
/// <returns></returns>
public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
{
beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//ȡ���б���
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//���ñ����ʽΪ���ж���
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//����Ϊdz��ɫ��������56��
}
//ȡ�ñ���е�����
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//���������͵��ֶθ�ʽΪ���ж���
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//�����ַ��͵��ֶθ�ʽΪ���ж���
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//����һ���ϼ���
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "�ϼ�";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//����ѡ�еIJ��ֵ���ɫ
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//����Ϊdz��ɫ��������56��
//ȡ����������ı���
excel.Cells[2, 2] = strTitle;
//������������ı����ʽ
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//���ñ�����Ϊ����Ӧ���
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//������������ı���Ϊ���о���
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//���Ʊ߿�
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//��������Ӵ�
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//�����ϱ��Ӵ�
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//�����ұ��Ӵ�
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//�����±��Ӵ�
afterTime = DateTime.Now;
//��ʾЧ��
//excel.Visible=true;
//excel.Sheets[0] = "sss";
ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
//excel.Save(strExcelFileName);
#region ����Excel����
//��Ҫ��Excel��DCOM�����������:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//ע�⣺�����õ�������Excel����Ҫִ����������������������Excel����
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//��������
#endregion
return filename;
}
public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
{
//文件存在时先删除文件后再进行下一步操作
FileInfo file = new FileInfo(strFullFilePath);
if (file.Exists)
{
file.Delete();
}
int rowIndex = 3; //开始写入数据的单元格行
int colIndex = 0; //开始写入数据的单元格列
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.ApplicationClass mExcel = new Excel.ApplicationClass();
mExcel.Visible = false;
Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
Excel.Workbook mBook = (Excel.Workbook)(mBooks.Add(miss));
Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
er.Value2 = title;
try
{
foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
{
colIndex++;
mSheet.Cells[3, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows) //同样方法处理数据
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
if (colIndex == 2)
{
mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
}
else
{
mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//保存工作已写入数据的工作表
mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
// return true;
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
finally //finally中的代码主要用来释放内存和中止进程()
{
mBook.Close(false, miss, miss);
mBooks.Close();
mExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
GC.Collect();
}
//return false;
}
protected List<string[]> LoadXlsFile(int jobCode, string infile, bool ifFirstRowIsHeader)
{
Excel.ApplicationClass xl = null;
Excel._Worksheet ws = null;
Excel.Range range = null;
if (File.Exists(ConfigUtils.CleanPath(infile)))
{
string[] colNames = new string[0];
System.Data.DataTable dt = new System.Data.DataTable();
log.InfoFormat(jobCode, "Reading file {0}", infile);
#region X2C
if (!System.IO.File.Exists(infile)) return null;
xl = new Excel.ApplicationClass();
try
{
xl.DisplayAlerts = false;
xl.Workbooks.Open(infile, false, true, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ws = (Excel._Worksheet)xl.Worksheets[1];
List<string[]> rows = new List<string[]>();
int startRow = 1;
if (ifFirstRowIsHeader) startRow++;
for (int i = startRow; i <= 100000; i++)
{
try
{
range = ws.get_Range("A" + i.ToString(), "J" + i.ToString());
System.Array myvalues = (System.Array)range.Cells.get_Value(null);
string[] strArray = ConvertToStringArray(myvalues);
rows.Add(strArray);
if (strArray[0].CompareTo(String.Empty) == 0 &&
strArray[1].CompareTo(String.Empty) == 0 &&
strArray[2].CompareTo(String.Empty) == 0)
break;
}
catch (Exception ex)
{
log.Error(jobCode, ex);
break;
}
}
xl.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xl);
log.InfoFormat(jobCode, "Completed reading file.");
return rows;
}
catch (Exception)
{
if (xl != null)
{
xl.Quit();
}
return null;
}
finally
{
//CGlobal.KillCOMObject(xl);
GC.Collect();
}
#endregion
}
else
throw new FileNotFoundException(String.Format("Failed to find file to load {0}", infile));
}
private void XuatDuLieuRaExcel(string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 17, SoCot = 19;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
excel.Cells[4, 1] = "TỔNG HỢP KẾ HOẠCH BIÊN CHẾ SỰ NGHIỆP NĂM " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year;
excel.Cells[6, 1] = "I. Đánh giá tình hình quản lý và sử dụng biên chế sự nghiệp năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year;
DataTable dt = oBNS_Luong.GetSoLuongCongChuc((DateTime)dtpDenNgay.EditValue);
if (dt.Rows.Count > 0)
{
int ChuyenNoiKhac = 0, NghiHuu = 0, ThoiViec = 0;
string NS_NgachCongChucID = dt.Rows[0]["NS_NgachCongChucID"].ToString();
CreateTableTemp();
DataRow drNew = dtBaoCaoTemp.NewRow();
drNew["NS_NgachCongChucID"] = int.Parse(NS_NgachCongChucID);
drNew["MaNgachCongChuc"] = dt.Rows[0]["MaNgachCongChuc"].ToString();
foreach (DataRow drtemp in dt.Rows)
{
if (NS_NgachCongChucID != drtemp["NS_NgachCongChucID"].ToString())
{
if (ChuyenNoiKhac > 0)
{
drNew["ChuyenNoiKhac"] = ChuyenNoiKhac;
}
if (NghiHuu > 0)
{
drNew["NghiHuu"] = NghiHuu;
}
if (ThoiViec > 0)
{
drNew["ThoiViec"] = ThoiViec;
}
dtBaoCaoTemp.Rows.Add(drNew);
ChuyenNoiKhac = 0;
NghiHuu = 0;
ThoiViec = 0;
NS_NgachCongChucID = drtemp["NS_NgachCongChucID"].ToString();
drNew = dtBaoCaoTemp.NewRow();
drNew["NS_NgachCongChucID"] = int.Parse(NS_NgachCongChucID);
drNew["MaNgachCongChuc"] = drtemp["MaNgachCongChuc"].ToString();
}
if ("" + drtemp["IDNS_HinhThucNghiViec"] == "CHUYENCONGTAC")
{
ChuyenNoiKhac++;
}
if ("" + drtemp["IDNS_HinhThucNghiViec"] == "NGHIHUU")
{
NghiHuu++;
}
if ("" + drtemp["IDNS_HinhThucNghiViec"] == "THOIVIEC")
{
ThoiViec++;
}
}
if (ChuyenNoiKhac > 0)
{
drNew["ChuyenNoiKhac"] = ChuyenNoiKhac;
}
if (NghiHuu > 0)
{
drNew["NghiHuu"] = NghiHuu;
}
if (ThoiViec > 0)
{
drNew["ThoiViec"] = ThoiViec;
}
dtBaoCaoTemp.Rows.Add(drNew);
excel.Cells[8, 6] = "Nghi hưu năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year.ToString() + ": " + dtBaoCaoTemp.Compute("Sum(NghiHuu)", "").ToString();
excel.Cells[9, 6] = "Xin thôi việc năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year.ToString() + ": " + dtBaoCaoTemp.Compute("Sum(ThoiViec)", "").ToString();
excel.Cells[10, 6] = "Chuyển công tác năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year.ToString() + ": " + dtBaoCaoTemp.Compute("Sum(ChuyenNoiKhac)", "").ToString();
}
excel.Cells[13, 1] = "II. Biên chế sự nghiệp năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year;
excel.Cells[15, 5] = "HĐLĐ theo Nghị định 68 được giao năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year;
excel.Cells[15, 6] = "Biên chế được giao năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year;
excel.Cells[15, 10] = "Có mặt đến " + dtpDenNgay.Text;
excel.Cells[15, 14] = "Kế hoạch năm " + (DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year + 1).ToString();
excel.Cells[16, 18] = "KH lao động theo Nghị định 68 của năm " + DateTime.Parse(dtpDenNgay.EditValue.ToString()).Year;
int count = dtBaoCao.Rows.Count;
DataRow dr;
for (int i = 0; i < count; i++)
{
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
dr = dtBaoCao.Rows[i];
excel.Cells[i + DongBatDau, 1] = i + 1;
excel.Cells[i + DongBatDau, 2] = "" + dr["TenDonVi"];
excel.Cells[i + DongBatDau, 3] = "" + dr["SoQDThanhLap"];
excel.Cells[i + DongBatDau, 4] = "" + dr["CCQDThanhLap"];
excel.Cells[i + DongBatDau, 10] = "" + dr["TongSo"];
excel.Cells[i + DongBatDau, 11] = "" + dr["VCLanhDao"];
excel.Cells[i + DongBatDau, 12] = "" + dr["VCTrongBMQL"];
excel.Cells[i + DongBatDau, 13] = "" + dr["VCConLai"];
}
// Them dong tong
cel = (Excel.Range)(excel.Cells[count + DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
excel.Cells[count + DongBatDau, 2] = "Tổng cộng";
excel.Cells[count + DongBatDau, 10] = dtBaoCao.Compute("Sum(TongSo)", "").ToString();
excel.Cells[count + DongBatDau, 11] = dtBaoCao.Compute("Sum(VCLanhDao)", "").ToString();
excel.Cells[count + DongBatDau, 12] = dtBaoCao.Compute("Sum(VCTrongBMQL)", "").ToString();
excel.Cells[count + DongBatDau, 13] = dtBaoCao.Compute("Sum(VCConLai)", "").ToString();
cel = excel.get_Range(excel.Cells[count + DongBatDau, 1], excel.Cells[DongBatDau + count, SoCot]);
cel.Font.Bold = true;
// Set style
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + count - 1, SoCot]);
cel.Borders.LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + count, 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
for (int j = 1; j <= SoCot; j++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongBatDau + count, j]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
}
cel = excel.get_Range(excel.Cells[DongBatDau + count, 1], excel.Cells[DongBatDau + count, SoCot]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
public void GenerateGraph()
{
Excel.ApplicationClass excelApplication = new Excel.ApplicationClass();
Excel.Application obj = new Excel.Application();
Excel.Workbook newWorkbook = null;
Excel.Worksheet targetSheet = null;
Excel.Range dataRange = null;
try
{
string paramWorkbookPath = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\Graphs\\Failure";
paramWorkbookPath += ".xls";
File.Delete(paramWorkbookPath);
object paramMissing = Type.Missing;
object paramChartFormat = 1;
object paramCategoryLabels = 1;
object paramSeriesLabels = 0;
string strTitle = "";
object paramTitle = strTitle;
object paramCategoryTitle = "Category Title";
object paramValueTitle = "Value Title";
newWorkbook = excelApplication.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
targetSheet = (Excel.Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Sheet1";
// Gets the datatable containing the data
DataTable dsData = GetTable();
int colcount = dsData.Columns.Count;
int rowcount = dsData.Rows.Count;
int i = 2;
targetSheet.Cells[1, 1] = "Production Month";
targetSheet.Cells[1, 2] = "Failure/1000 Tractors";
DataRow drFailure = dsData.NewRow();
double[] Failure = new double[dsData.Columns.Count - 1];
drFailure["Field"] = "Failure/1000 Tractor:";
for (int k = 1; k < dsData.Columns.Count; k++)
{
double Production = Convert.ToDouble(dsData.Rows[0][k].ToString());
if (Production > 0)
{
drFailure[k] = Convert.ToString(System.Math.Round(Convert.ToDouble(dsData.Rows[1][k]) * 1000 / Production, 2));
}
else
{
drFailure[k] = "0";
}
}
dsData.Rows.Add(drFailure);
int rowFlag = 1;
foreach (DataRow dr in dsData.Rows)
{
if (rowFlag > 2)
{
int colflag = 1;
foreach (DataColumn dc in dsData.Columns)
{
if (colflag > 1)
{
targetSheet.Cells[i, 1] = "'" + getProductionMonthYear(Convert.ToInt32(dc.ColumnName));
targetSheet.Cells[i, 2] = dr[dc].ToString();
i = i + 1;
}
colflag++;
}
}
rowFlag++;
}
object fromCell = "$A1";
object toCell = "$B" + (colcount - 1).ToString();
dataRange = targetSheet.get_Range(fromCell, toCell);
/**************************************Get Column Graph()************************************************************/
// Generating the graph
Excel.Chart barchart;
barchart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
barchart.ChartType = Excel.XlChartType.xlColumnClustered;
ColumnGraph(barchart, targetSheet, dataRange);
/**************************************Get Line Chart()************************************************************/
// Generating the graph
Excel.Chart linechart;
linechart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
linechart.ChartType = Excel.XlChartType.xlLine;
ColumnGraph(linechart, targetSheet, dataRange);
newWorkbook.SaveAs(paramWorkbookPath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
// Release the references to the Excel objects.
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataRange = null;
targetSheet = null;
if (newWorkbook != null)
{
newWorkbook.Close(false, Type.Missing, Type.Missing);
newWorkbook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void XuatBangDiemRaExcel(DataTable dtChiTiet, string FileExcel, int SoLanThi)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 11, CotBatDau = _ColStart, SoCot = 0;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
cel = (Excel.Range)excel.Cells[5, 1];
excel.Cells[5, 1] = cel.Text + " LỚP " + pDM_LopInfo.TenLop.Replace("Lớp: ", "").ToUpper();
excel.Cells[6, 1] = "HỌC KỲ: " + Program.HocKy.ToString() + " - NĂM HỌC: " + Program.NamHoc;
// Them các tiêu đề cột môn học phía sau
if (grbMonHoc.Children.Count > 0)
{
int CotDau = CotBatDau;
foreach (GridBand grbTenMon in grbMonHoc.Children)
{
for (int i = 1; i <= grbTenMon.Children[0].Columns.Count; i++)
{
cel = (Excel.Range)(excel.Cells[DongBatDau - 1, CotBatDau]);
cel.EntireColumn.Insert(Excel.XlDirection.xlToRight, null);
SoCot++;
// Lần thi [DongBatDau - 1]
excel.Cells[DongBatDau - 1, CotBatDau] = "L" + i.ToString();
cel = (Excel.Range)(excel.Cells[DongBatDau - 1, CotBatDau]);
cel.ColumnWidth = 3;
CotBatDau++;
}
// Số học trình [DongBatDau - 2]
excel.Cells[DongBatDau - 2, CotDau] = grbTenMon.Children[0].Caption;
cel = excel.get_Range(excel.Cells[DongBatDau - 2, CotDau], excel.Cells[DongBatDau - 2, CotBatDau - 1]);
cel.Merge(null);
cel.Borders.Value = 1;
// Tên môn học [DongBatDau - 3]
excel.Cells[DongBatDau - 3, CotDau] = grbTenMon.Caption;
cel = excel.get_Range(excel.Cells[DongBatDau - 3, CotDau], excel.Cells[DongBatDau - 3, CotBatDau - 1]);
cel.Font.Size = 10;
cel.Font.Bold = false;
cel.WrapText = true;
cel.Merge(null);
cel.Borders.Value = 1;
CotDau = CotBatDau;
}
// ĐIỂM [DongBatDau - 4]
excel.Cells[DongBatDau - 4, _ColStart] = "ĐIỂM MÔN HỌC";
cel = excel.get_Range(excel.Cells[DongBatDau - 4, _ColStart], excel.Cells[DongBatDau - 4, _ColStart + SoCot - 1]);
cel.Merge(null);
cel.Borders.Value = 1;
int colPhongDaoTao = SoCot / 2 - 5;
//excel.Cells[DongBatDau + 5, _ColStart + colPhongDaoTao] = "Phòng đào tạo";
}
DataRow dr;
int SoSinhVien = bgvDiem.DataRowCount;
for (int j = 0; j < SoSinhVien; j++)
{
dr = bgvDiem.GetDataRow(j);
cel = (Excel.Range)(excel.Cells[j + DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
excel.Cells[j + DongBatDau, 1] = j + 1;
excel.Cells[j + DongBatDau, 2] = "" + dr["MaSinhVien"];
excel.Cells[j + DongBatDau, 3] = "" + dr["HoVa"];
excel.Cells[j + DongBatDau, 4] = "" + dr["TenSV"];
excel.Cells[j + DongBatDau, 5] = "'" + dr["NgaySinh"];
for (int i = dtColStart; i < dtColEnd; i++)
{
if ("" + dr[i] != "")
{
cel = (Excel.Range)excel.Cells[j + DongBatDau, _ColStart + i - dtColStart];
excel.Cells[j + DongBatDau, _ColStart + i - dtColStart] = dr[i];
cel.NumberFormat = "0.0";
}
}
if ("" + dr["DiemTK1"] != "")
{
cel = (Excel.Range)excel.Cells[j + DongBatDau, _ColStart + SoCot];
excel.Cells[j + DongBatDau, _ColStart + SoCot] = dr["DiemTK1"];
cel.NumberFormat = "0.0";
}
if ("" + dr["DiemTK2"] != "")
{
cel = (Excel.Range)excel.Cells[j + DongBatDau, _ColStart + SoCot + 1];
excel.Cells[j + DongBatDau, _ColStart + SoCot + 1] = dr["DiemTK2"];
cel.NumberFormat = "0.0";
}
}
// Đưa dữ liệu phần tỷ lệ kết quả học tập
// Tỷ lệ giỏi
string Condition = "";
int SoLuong = 0;
DataRow[] arrDr = dtXepLoai.Select("MaXepLoai = 'XS' Or MaXepLoai = 'G'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 2, 2];
excel.Cells[DongBatDau + SoSinhVien + 2, 2] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Tỷ lệ khá
arrDr = dtXepLoai.Select("MaXepLoai = 'K'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 3, 2];
excel.Cells[DongBatDau + SoSinhVien + 3, 2] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Tỷ lệ trung bình
arrDr = dtXepLoai.Select("MaXepLoai = 'TBK' OR MaXepLoai = 'TB'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 4, 2];
excel.Cells[DongBatDau + SoSinhVien + 4, 2] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Tỷ lệ yếu
arrDr = dtXepLoai.Select("MaXepLoai = 'Y'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 2, 4];
excel.Cells[DongBatDau + SoSinhVien + 2, 4] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Tỷ lệ kém
arrDr = dtXepLoai.Select("MaXepLoai = 'K-'");
if (arrDr.Length > 0)
{
Condition = "";
foreach (DataRow drXL in arrDr)
{
Condition += (Condition == "" ? "" : " OR ") + "KQHT_XepLoaiTotNghiepID1 = " + drXL["KQHT_XepLoaiTotNghiepID"];
}
Condition += " OR KQHT_XepLoaiTotNghiepID1 = 0";
SoLuong = int.Parse(dtSinhVien.Compute("Count(SV_SinhVienID)", Condition).ToString());
cel = (Excel.Range)excel.Cells[DongBatDau + SoSinhVien + 3, 4];
excel.Cells[DongBatDau + SoSinhVien + 3, 4] = cel.Text + TinhPhanTram(SoLuong, SoSinhVien);
}
// Set style
cel = excel.get_Range(excel.Cells[DongBatDau, 1],
excel.Cells[DongBatDau + SoSinhVien - 1, _ColStart + SoCot + 2 - 1]);
cel.Borders.LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + SoSinhVien - 1, 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
for (int i = 1; i <= _ColStart - 1; i++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, i], excel.Cells[DongBatDau + SoSinhVien - 1, i]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
if (i != 3)
{
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
}
else
{
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}
}
for (int i = _ColStart; i <= _ColStart + SoCot + 2 - 1; i++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, i], excel.Cells[DongBatDau + SoSinhVien - 1, i]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
cel = excel.get_Range(excel.Cells[DongBatDau + SoSinhVien - 1, 1],
excel.Cells[DongBatDau + SoSinhVien - 1, _ColStart + SoCot + 2 - 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 10, _ColStart = 4, SoCot = bgrvBaoCao.Columns.Count; //, ExcCotBatDau = 5, SoCot = 22;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
excel.Cells[4, 5] = "(Có đến ngày " + dtpDenNgay.Text + ")";
int count = dtBaoCao.Rows.Count;
DataRow dr;
for (int i = 0; i < count; i++)
{
cel = (Excel.Range)(excel.Cells[i + DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
dr = dtBaoCao.Rows[i];
excel.Cells[i + DongBatDau, 1] = "" + dr["STT"];
excel.Cells[i + DongBatDau, 2] = "" + dr["TenNganhDaoTao"].ToString();
for (int j = _ColStart; j < dtBaoCao.Columns.Count; j++)
{
if ("" + dr[dtBaoCao.Columns[j].ColumnName] != "")
{
excel.Cells[i + DongBatDau, j - _ColStart + 3] = dr[dtBaoCao.Columns[j].ColumnName].ToString();
//cel = (Excel.Range)excel.Cells[i + DongBatDau, j - _ColStart];
}
}
}
// Set style
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + count - 1, SoCot]);
cel.Borders.LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + count - 1, 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
for (int j = 1; j <= SoCot; j++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongBatDau + count - 1, j]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
}
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + count - 1, SoCot]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int i = 0;
int DongBatDau = 8;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
// Phần này là thêm Header.
excel.Cells[5, 6] = Program.NamHoc;
cel = (Excel.Range)excel.Cells[5, 6];
// Phần này là thêm Detail.
double SumTongThu = 0;
for (i = 0; i < grvSinhVien.DataRowCount; i++)
{
excel.Cells[i + DongBatDau, 1] = i + 1;
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 1]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 2] = grvSinhVien.GetDataRow(i)["SoBaoDanhTS"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 2]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 3] = grvSinhVien.GetDataRow(i)["HoVaTenTS"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 3]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 4] = grvSinhVien.GetDataRow(i)["NgaySinh"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 4]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 5] = grvSinhVien.GetDataRow(i)["KhoiThi"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 5]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 6] = grvSinhVien.GetDataRow(i)["NganhThi"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 6]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 7] = grvSinhVien.GetDataRow(i)["NoiSinhTS"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 7]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 8] = grvSinhVien.GetDataRow(i)["ThuongTruTS"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 8]);
cel.Borders.Value = 1;
excel.Cells[i + DongBatDau, 9] = grvSinhVien.GetDataRow(i)["SoTien"] + "";
cel = (Excel.Range)(excel.Cells[i + DongBatDau, 9]);
cel.Borders.Value = 1;
SumTongThu += double.Parse("0" + grvSinhVien.GetDataRow(i)["SoTien"] + "");
}
for (int j = 0; j < 9; j++)
{
cel = (Excel.Range)(excel.Cells[grvSinhVien.DataRowCount + DongBatDau, j + 1]);
cel.Font.Bold = true;
cel.Borders.Value = 1;
}
excel.Cells[grvSinhVien.DataRowCount + DongBatDau, 2] = "Tổng cộng:";
excel.Cells[grvSinhVien.DataRowCount + DongBatDau, 9] = SumTongThu;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Danh sách thu tiền nhập học trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void XuatDuLieuRaExcel(DataTable dtSinhVien, string FileExcel)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
int DongBatDau = 9, SoCot = 9;
Excel.Range cel;
Excel.ApplicationClass excel = new Excel.ApplicationClass();
try
{
excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false);
if (cmbKhoaHoc.ItemIndex >= 0)
{
string str = cmbTrinhDo.EditValue != null ? "TRÌNH ĐỘ: " + cmbTrinhDo.Text : "";
str += (str == "" ? "KHÓA HỌC: " : " - ") + cmbKhoaHoc.Text;
excel.Cells[6, 1] = str.ToUpper();
}
DataRow dr;
int SoSinhVien = dtSinhVien.Rows.Count, SoLop = 1, STT = 0, k = 0;
string IDDM_Lop = dtSinhVien.Rows[0]["IDDM_Lop"].ToString();
// Insert column Lop
cel = (Excel.Range)(excel.Cells[DongBatDau + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau, SoCot]);
cel.WrapText = false;
excel.Cells[DongBatDau, 1] = "Lớp: " + dtSinhVien.Rows[0]["TenLopSoTrinh"];
cel.Font.Size = 11;
cel.Font.Bold = true;
cel.Merge(null);
for (int i = 0; i < SoSinhVien; i++)
{
if (IDDM_Lop != dtSinhVien.Rows[i]["IDDM_Lop"].ToString())
{
IDDM_Lop = dtSinhVien.Rows[i]["IDDM_Lop"].ToString();
k = 0;
// Insert column Lop
cel = (Excel.Range)(excel.Cells[i + DongBatDau + SoLop + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
cel = excel.get_Range(excel.Cells[i + DongBatDau + SoLop, 1], excel.Cells[i + DongBatDau + SoLop, SoCot]);
cel.WrapText = false;
excel.Cells[i + DongBatDau + SoLop, 1] = "Lớp: " + dtSinhVien.Rows[i]["TenLopSoTrinh"];
cel.Font.Size = 11;
cel.Font.Bold = true;
cel.Merge(null);
SoLop++;
}
dr = dtSinhVien.Rows[i];
cel = (Excel.Range)(excel.Cells[i + DongBatDau + SoLop + 1, 1]);
cel.EntireRow.Insert(Excel.XlDirection.xlUp, null);
STT++;
k++;
excel.Cells[i + DongBatDau + SoLop, 1] = STT;
excel.Cells[i + DongBatDau + SoLop, 2] = k;
excel.Cells[i + DongBatDau + SoLop, 3] = "" + dr["MaSinhVien"];
excel.Cells[i + DongBatDau + SoLop, 4] = "" + dr["HoVa"];
excel.Cells[i + DongBatDau + SoLop, 5] = "" + dr["Ten"];
excel.Cells[i + DongBatDau + SoLop, 6] = "" + dr["NgaySinh"];
excel.Cells[i + DongBatDau + SoLop, 7] = "" + dr["NoiSinh"];
excel.Cells[i + DongBatDau + SoLop, 8] = "" + dr["LyDo"];
excel.Cells[i + DongBatDau + SoLop, 9] = "" + dr["GhiChu"];
}
// Set style
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, SoCot]);
cel.Borders.LineStyle = Excel.XlLineStyle.xlDot;
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, 1]);
cel.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
for (int j = 1; j <= SoCot; j++)
{
cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, j]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
if (j != 4)
{
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
}
else
{
cel.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}
}
cel = excel.get_Range(excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, 1], excel.Cells[DongBatDau + SoLop + SoSinhVien - 1, SoCot]);
cel.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
}
catch (Exception e)
{
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng file Excel Phiếu báo điểm trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message);
return;
}
finally
{
excel.Application.Workbooks[1].Save();
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Process.Start(FileExcel);
CloseWaitDialog();
this.Cursor = System.Windows.Forms.Cursors.Default;
}
}
public void GenerateGraph()
{
Excel.ApplicationClass excelApplication = new Excel.ApplicationClass();
Excel.Application obj = new Excel.Application();
Excel.Workbook newWorkbook = null;
Excel.Worksheet targetSheet = null;
Excel.Range dataRange = null;
try
{
string paramWorkbookPath = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\Graphs\\EffectCheck";
paramWorkbookPath += ".xls";
File.Delete(paramWorkbookPath);
object paramMissing = Type.Missing;
object paramChartFormat = 1;
object paramCategoryLabels = 1;
object paramSeriesLabels = 0;
string strTitle = "";
object paramTitle = strTitle;
object paramCategoryTitle = "Category Title";
object paramValueTitle = "Value Title";
newWorkbook = excelApplication.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
targetSheet = (Excel.Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Sheet1";
// Gets the datatable containing the data
DataTable dsData = GetTable();
int colcount = dsData.Columns.Count;
int rowcount = dsData.Rows.Count;
int i = 2;
int columnflag = 1;
//foreach (DataColumn dccol in dsData.Columns)
//{
// targetSheet.Cells[1, columnflag] = dccol.ColumnName;
// targetSheet.Cells[2, columnflag] = dsData.Rows[0][dccol].ToString();
// targetSheet.Cells[3, columnflag] = dsData.Rows[1][dccol].ToString();
// columnflag++;
//}
string beforafter;
foreach (DataColumn dccol in dsData.Columns)
{
//New changes for Excel graph presentation
string[] strTest = dccol.ColumnName.Split('-');
targetSheet.Cells[1, columnflag] = strTest[0];
if (dsData.Rows[0][dccol].ToString() == "Before")
{
beforafter = dsData.Rows[0][dccol].ToString() + "-" + drpMonth.SelectedItem.Text + " " + drpYear.SelectedItem.Text;
targetSheet.Cells[2, 1] = beforafter;
}
if (dsData.Rows[1][dccol].ToString() == "After")
{
beforafter = dsData.Rows[1][dccol].ToString() + "-" + drpMonth.SelectedItem.Text + " " + drpYear.SelectedItem.Text;
targetSheet.Cells[3, 1] = beforafter;
}
if (dccol.ToString() != "PM")
{
string str1 = dsData.Rows[0][dccol].ToString();
string str2 = dsData.Rows[1][dccol].ToString();
targetSheet.Cells[2, columnflag] = dsData.Rows[0][dccol].ToString();
targetSheet.Cells[3, columnflag] = dsData.Rows[1][dccol].ToString();
}
columnflag++;
}
object fromCell = "$A1";
//object toCell = "$B" + (colcount - 1).ToString();
//To claculate the excel column for the Exdended months
string Sheetcellno = "$N3";
if (Convert.ToInt32(txtEntendedMonth.Text) > 1)
{
if (Convert.ToInt32(txtEntendedMonth.Text) == 2)
{
Sheetcellno = "$O3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 3)
{
Sheetcellno = "$P3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 4)
{
Sheetcellno = "$Q3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 5)
{
Sheetcellno = "$R3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 6)
{
Sheetcellno = "$S3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 7)
{
Sheetcellno = "$T3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 8)
{
Sheetcellno = "$U3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 9)
{
Sheetcellno = "$V3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 10)
{
Sheetcellno = "$W3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 11)
{
Sheetcellno = "$X3";
}
else if (Convert.ToInt32(txtEntendedMonth.Text) == 12)
{
Sheetcellno = "$Y3";
}
}
//object toCell = "$S3";
object toCell = Sheetcellno;
dataRange = targetSheet.get_Range(fromCell, toCell);
/**************************************Get Line Chart()************************************************************/
// Generating the graph
Excel.Chart linechart;
linechart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
linechart.ChartType = Excel.XlChartType.xlLineMarkers;
ColumnGraph(linechart, targetSheet, dataRange);
//newWorkbook.SaveAs(paramWorkbookPath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
newWorkbook.SaveCopyAs(paramWorkbookPath);
// Release the references to the Excel objects.
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataRange = null;
targetSheet = null;
if (newWorkbook != null)
{
newWorkbook.Close(false, Type.Missing, Type.Missing);
newWorkbook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public void ExportExcel(string filename, DataTable dt, List<KeyValuePair<string, string>> columns)
{
Excel.Application app = null;
try
{
object missing = Type.Missing;
app = new Excel.ApplicationClass();
app.Visible = false;
app.DisplayAlerts = false;
app.Application.Workbooks.Add(true);
Excel.Worksheet ws = (Excel.Worksheet)app.ActiveSheet;
ws.Name = "data";
int line = 1;
if (columns != null)
{
for (int i = 0; i < columns.Count; i++)
{
app.Cells[line, i + 1] = columns[i].Value;
(app.Cells[line, i + 1] as Excel.Range).Font.Bold = true;
}
line++;
}
else
{
for (int i = 0; i < dt.Columns.Count; i++)
{
app.Cells[line, i + 1] = dt.Columns[i].ColumnName;
(app.Cells[line, i + 1] as Excel.Range).Font.Bold = true;
}
line++;
}
object[,] ss = new object[dt.Rows.Count, columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < columns.Count; j++)
{
ss[i, j] = "'" + dt.Rows[i][columns[j].Key];
}
}
Excel.Range r = ws.get_Range(ws.Cells[line, 1], ws.Cells[line + dt.Rows.Count - 1, columns.Count]);
r.Value2 = ss;
app.ActiveWorkbook.SaveAs(filename, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
app.ActiveWorkbook.Save();
app.ActiveWorkbook.Close(missing, missing, missing);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
catch (Exception ex)
{
if (app != null)
app.Quit();
throw ex;
}
app = null;
GC.Collect();
}
public void GenerateGraph()
{
Excel.ApplicationClass excelApplication = new Excel.ApplicationClass();
Excel.Application obj = new Excel.Application();
Excel.Workbook newWorkbook = null;
Excel.Worksheet targetSheet = null;
Excel.Range dataRange = null;
try
{
string paramWorkbookPath = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\Graphs\\Summary";
paramWorkbookPath += ".xls";
File.Delete(paramWorkbookPath);
object paramMissing = Type.Missing;
object paramChartFormat = 1;
object paramCategoryLabels = 1;
object paramSeriesLabels = 0;
string strTitle = "";
object paramTitle = strTitle;
object paramCategoryTitle = "Category Title";
object paramValueTitle = "Value Title";
newWorkbook = excelApplication.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
targetSheet = (Excel.Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Sheet1";
// Gets the datatable containing the data
DataTable dsData = GetTable();
int colcount = dsData.Columns.Count;
int rowcount = dsData.Rows.Count;
int i = 2;
targetSheet.Cells[1, 1] = "Financial Year";
targetSheet.Cells[1, 2] = "Value";
// Outputting the data
foreach (DataRow dr in dsData.Rows)
{
//targetSheet.Cells[i, 1] = dr["Fyear"];
targetSheet.Cells[i, 1] = "'"+ dr["Fyear"];
targetSheet.Cells[i, 2] = dr["DefectAmount"];
// Going to the next row
i = i + 1;
}
object fromCell = "$A1";
object toCell = "$B" + (rowcount + 1).ToString();
dataRange = targetSheet.get_Range(fromCell, toCell);
/**************************************Get Column Graph()************************************************************/
// Generating the graph
Excel.Chart barchart;
barchart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
barchart.ChartType = Excel.XlChartType.xlColumnClustered;
ColumnGraph(barchart, targetSheet, dataRange);
/**************************************Get pie Graph()************************************************************/
// Generating the graph
Excel.Chart piechart;
piechart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
piechart.ChartType = Excel.XlChartType.xlPie;
PieGraph(piechart, targetSheet, dataRange);
/**************************************Get Line Chart()************************************************************/
// Generating the graph
Excel.Chart linechart;
linechart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
linechart.ChartType = Excel.XlChartType.xlLine;
ColumnGraph(linechart, targetSheet, dataRange);
/**************************************Get Doughnut Graph()************************************************************/
// Generating the graph
Excel.Chart doughchart;
doughchart = (Excel.Chart)newWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
doughchart.ChartType = Excel.XlChartType.xlDoughnut;
PieGraph(doughchart, targetSheet, dataRange);
newWorkbook.SaveAs(paramWorkbookPath, Excel.XlFileFormat.xlHtml, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
// Release the references to the Excel objects.
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataRange = null;
targetSheet = null;
if (newWorkbook != null)
{
newWorkbook.Close(false, Type.Missing, Type.Missing);
newWorkbook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private bool OutToExcel(string ExcelFile)
{
Excel.Application ExcelObj = null;
try
{
ExcelObj = new Excel.ApplicationClass();
ExcelObj.Visible = false;
Excel.Workbook ExcelBook = ExcelObj.Workbooks.Add(Missing.Value);
Excel.Worksheet ExcelSheet = (Excel.Worksheet)ExcelBook.Sheets[1];
//死者
StringToExcelCell(ExcelSheet, "A1", "死者：", 3);
StringToExcelCell(ExcelSheet, "B1", DeadManText.Text, 1);
//性别
StringToExcelCell(ExcelSheet, "F1", "性别：", 3);
StringToExcelCell(ExcelSheet, "G1", DeadSexComBoBox.Text, 1);
//出殡日期
StringToExcelCell(ExcelSheet, "A2", "出殡日期：", 3);
StringToExcelCell(ExcelSheet, "B2", DeadManTimeText.Text, 1);
//告别厅
StringToExcelCell(ExcelSheet, "G2", "告别厅", 1);
StringToExcelCell(ExcelSheet, "F2", DeadManAddressText.Text, 3);
//列表标题
StringToExcelCell(ExcelSheet, "A3", "序号", 2);
StringToExcelCell(ExcelSheet, "B3", "称 谓（前）", 2);
StringToExcelCell(ExcelSheet, "C3", "称 谓（后）", 2);
StringToExcelCell(ExcelSheet, "D3", "姓名1", 2);
StringToExcelCell(ExcelSheet, "E3", "姓名2", 2);
StringToExcelCell(ExcelSheet, "F3", "项目", 2);
StringToExcelCell(ExcelSheet, "G3", "价格", 2);
//列表数据
int j = 4;
for (int i = 0; i < GridViewControl.RowCount - 1; i++)
{
StringToExcelCell(ExcelSheet, j, 1, Convert.ToString(i + 1), 2);
StringToExcelCell(ExcelSheet, j, 2, GridViewCellToString(GridViewControl.Rows[i].Cells[1]), 2);
StringToExcelCell(ExcelSheet, j, 3, GridViewCellToString(GridViewControl.Rows[i].Cells[2]), 2);
StringToExcelCell(ExcelSheet, j, 4, GridViewCellToString(GridViewControl.Rows[i].Cells[3]), 2);
StringToExcelCell(ExcelSheet, j, 5, GridViewCellToString(GridViewControl.Rows[i].Cells[4]), 2);
StringToExcelCell(ExcelSheet, j, 6, GridViewCellToString(GridViewControl.Rows[i].Cells[5]), 2);
StringToExcelCell(ExcelSheet, j, 7, GridViewCellToString(GridViewControl.Rows[i].Cells[6]), 2);
j++;
}
//合计
StringToExcelCell(ExcelSheet, j, 6, "合计：", 3);
StringToExcelCell(ExcelSheet, j, 7, TotalPriceText.Text, 2);
//保存
ExcelBook.SaveAs(ExcelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ExcelSheet = null;
ExcelBook.Close(false, false, false);
ExcelBook = null;
ExcelObj.Quit();
ExcelObj = null;
return true;
}
catch (Exception ex)
{
ExcelObj.Quit();
ExcelObj = null;
MessageBox.Show(ex.Message, "导出错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
public override ResultadoLectura Leer(string archivo, ParametrosLectura param)
{
ResultadoLectura resultado = new ResultadoLectura();
int hoja = 1;
int filaInicio = 3;
DataTable tabla = new CreadorTablaLectura().CrearTabla();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(archivo, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(hoja);
bool continuar = true;
while (continuar)
{
if ((xlWorkSheet.Rows.Cells[filaInicio, 1] as Excel.Range).Value2 == null)
{
continuar = false;
}
else
{
DateTime f1 = new DateTime(1900, 1, 1);
DateTime fecha = f1.AddDays(-2 + (double)(xlWorkSheet.Rows.Cells[filaInicio, 1] as Excel.Range).Value2);
TimeSpan hora = fecha.TimeOfDay;
RegistroLectura reg = RegistroLectura.GetRegistroLectura(fecha, hora);
resultado.Registros.Add(reg);
foreach (MC_RPuntoMedicionFormatoDetalle r in param.DetalleMagElec)
{
int numColumna = r.GetNumColumna();
if (numColumna >= 0)
{
DataRow row = tabla.NewRow();
row["Fecha"] = reg.Fecha.Date; //fecha.Date;
row["Hora"] = reg.Hora; //hora;
row["Canal"] = r.Canal;
row["CodInfCanal"] = r.FkCodMagnitudElec;
row["Valor"] = (double)(xlWorkSheet.Rows.Cells[filaInicio, numColumna + 1] as Excel.Range).Value2;
tabla.Rows.Add(row);
reg.AdicionarItem(r.FkCodMagnitudElec, (double)row["Valor"]);
reg.AdicionarRow(row);
}
}
filaInicio++;
}
}
DestructorObjetosExcel.ReleaseObject(xlWorkSheet);
xlWorkBook.Close(true, null, null);
xlApp.Quit();
DestructorObjetosExcel.ReleaseObject(xlWorkBook);
DestructorObjetosExcel.ReleaseObject(xlApp);
resultado.Tabla = tabla;
return(resultado);
}
private void OutPut()
{
Excel.Application objApp = new Excel.ApplicationClass();
Excel.Workbooks objbooks = objApp.Workbooks;
Excel.Workbook objbook = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet objSheet = (Excel.Worksheet)objbook.Worksheets[1];//取得sheet1
Excel.Range range;
string filename = "";
try
{
//生成.xls文件完整路径名
filename = Server.MapPath("/RailExamBao/Excel/CompuerServerCount.xls");
if (File.Exists(filename.ToString()))
{
File.Delete(filename.ToString());
}
//将所得到的表的列名,赋值给单元格
objSheet.Cells[1, 1] = "站段名称";
objSheet.Cells[1, 2] = "服务器名称";
range = objSheet.get_Range(objSheet.Cells[1, 2], objSheet.Cells[1, 4]);
range.Merge(0);
objSheet.Cells[1, 5] = "使用人次";
objSheet.Cells[1, 6] = "其他单位使用人次";
objSheet.Cells[1, 7] = "其他单位使用天数";
DataSet ds = (DataSet)ViewState["Grid"];
int i = 0;
//同样方法处理数据
foreach (DataRow dr in ds.Tables[0].Rows)
{
objSheet.Cells[2 + i, 1] = dr["Short_Name"].ToString();
objSheet.Cells[2 + i, 2] = dr["Computer_Server_Name"].ToString();
range = objSheet.get_Range(objSheet.Cells[2 + i, 2], objSheet.Cells[2 + i, 4]);
range.Merge(0);
objSheet.Cells[2 + i, 5] = dr["使用人次"].ToString();
objSheet.Cells[2 + i, 6] = dr["其他单位使用人次"].ToString();
objSheet.Cells[2 + i, 7] = dr["其他单位使用天数"].ToString();
i++;
}
//不可见,即后台处理
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
}
catch
{
SessionSet.PageMessage = "系统错误，导出Excel文件失败！";
}
finally
{
objbook.Close(Type.Missing, filename, Type.Missing);
objbooks.Close();
objApp.Application.Workbooks.Close();
objApp.Application.Quit();
objApp.Quit();
GC.Collect();
}
}