private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application XcelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook WB; Excel.Worksheet Ws; Excel.Range Rang;
WB = XcelApp.Workbooks.Add(Type.Missing); WB.Worksheets.Add(Type.Missing, Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
// I am opening Excel
Ws = (Excel.Worksheet)WB.Worksheets.get_Item(1);
XcelApp.Visible = true; XcelApp.StandardFont = "Tahoma";
//I am filling Data
XcelApp.Cells[1, 1] = "Student Result";
Rang = Ws.get_Range("A1", "G2"); Rang.Font.Name = "MV Boli";
Rang.Font.Size = 22F;
Rang.Font.Bold = true;
Rang.MergeCells = true;
Rang.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Rang.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
XcelApp.Cells[3, 1] = "Sno";
XcelApp.Cells[3, 2] = "surname";
XcelApp.Cells[3, 3] = "other names";
XcelApp.Cells[3, 4] = "gender";
XcelApp.Cells[3, 5] = "Class";
XcelApp.Cells[3, 6] = "Mathematics";
//Rang = Ws.get_Range("C3", "E3"); Rang.MergeCells = true;
Rang = Ws.get_Range("A3", "G3"); Rang.Font.Bold = true;
DataSet ds = new DataSet();
ds = c1.FillDs("Select * from Result2");
int cnt = ds.Tables[0].Rows.Count;
for (int i = 0; i <= cnt - 1; i++)
{
XcelApp.Cells[4 + i, 1] = ds.Tables[0].Rows[i]["S_ID"].ToString();
XcelApp.Cells[4 + i, 2] = ds.Tables[0].Rows[i]["Surname"].ToString();
XcelApp.Cells[4 + i, 3] = ds.Tables[0].Rows[i]["OtherNames"].ToString();
XcelApp.Cells[4 + i, 4] = ds.Tables[0].Rows[i]["Gender"].ToString();
XcelApp.Cells[4 + i, 5] = ds.Tables[0].Rows[i]["Class"].ToString();
XcelApp.Cells[4 + i, 6] = ds.Tables[0].Rows[i]["Mathematics"].ToString();
}
XcelApp.Columns.AutoFit();
}
public static void DataTableToExcel(System.Data.DataTable dt, string filename)
{
string ExcelPathx = AppDomain.CurrentDomain.BaseDirectory + "\\FILE TRIEN KHAI.xlsm";
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application App = null;
Excel.Workbook Wb;
Excel.Worksheet Ws;
int isExcelOpen = 0;
try
{
App = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch (System.Runtime.InteropServices.COMException)
{
App = new Excel.Application();
isExcelOpen = 1;
}
//catch(Exception){}
//finally
//{
// App = new Excel.Application();
// isExcelOpen = 1;
//}
//oXL.Visible = true;
Wb = (Excel.Workbook)(App.Workbooks.Open(ExcelPathx, 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));
//Wb = (Excel.Workbook)(App.Workbooks.Add(Missing.Value));
Ws = (Excel.Worksheet)Wb.Worksheets["Sheet"];
//Ws = (Excel.Worksheet)Wb.ActiveSheet;
try
{
//Ws = (Excel.Worksheet)App.Worksheets.Add();
// Xử lý tiêu đề cột
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
int c = 0;
//int r = 0;
//Excel.Range HeaderRow = Ws.get_Range("A1");
//foreach (System.Data.DataColumn dc in dt.Columns)
//{
// HeaderRow.get_Offset(0, r).Value2 = dc.ColumnName;
// r++;
//}
//HeaderRow.EntireRow.Font.Bold = true;
//HeaderRow.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// Xử lý data-> mảng 2 chiều
object[,] rowData = new object[rowCount, colCount];
foreach (System.Data.DataRow row in dt.Rows)
{
for (int col = 0; col < colCount; col++)
{
if (IsNumeric(row[col].GetType().ToString()))
{
rowData[c, col] = System.Convert.ToDouble(row[col].ToString());
}
else
{
rowData[c, col] = row[col].ToString();
}
}
c++;
}
//xóa mảng trước khi pass
//Ws.get_Range("A2:AZ600").get_Resize(rowCount, colCount).Value2 = rowData; Xóa mảng trùng với mảng copy lên excel
Ws.get_Range("A2:AZ600").Cells.ClearContents();
// Paste mảng vào excel
Ws.get_Range("A2").get_Resize(rowCount, colCount).Value2 = rowData;
//Ws.get_Range("A1").get_Resize(1, colCount).EntireColumn.AutoFit();
// Giãn cột
// Lưu file
string ExcelPath = AppDomain.CurrentDomain.BaseDirectory + string.Format("{0}.xlsm", "FILE TRIEN KHAI");
if (System.IO.File.Exists(ExcelPath))
{
System.IO.File.Delete(ExcelPath);
}
Wb.SaveAs(ExcelPath, AccessMode: Excel.XlSaveAsAccessMode.xlShared);
Wb.Close();
//App.Quit();
if (isExcelOpen == 1)
{
App.Quit();
}
dt.Dispose();
}
catch
{
//throw ex;
}
// Dọn rác
System.Runtime.InteropServices.Marshal.ReleaseComObject(Ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
System.Threading.Thread.Sleep(400);
}
private void Xuat_kehoachsx(System.Data.DataTable dt, string filename)//Xuất phiếu
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application App = null;
Excel.Workbook Wb;
Excel.Worksheet Ws;
int isExcelOpen = 0;
try
{
App = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch (System.Runtime.InteropServices.COMException)
{
App = new Excel.Application();
isExcelOpen = 1;
}
//oXL.Visible = true;
Wb = (Excel.Workbook)(App.Workbooks.Add(Missing.Value));
Ws = (Excel.Worksheet)Wb.ActiveSheet;
try
{
Ws = (Excel.Worksheet)App.Worksheets.Add();
// Xử lý tiêu đề cột
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
int c = 0;
int r = 0;
Excel.Range HeaderRow = Ws.get_Range("A1");
foreach (System.Data.DataColumn dc in dt.Columns)
{
HeaderRow.get_Offset(0, r).Value2 = dc.ColumnName;
r++;
}
HeaderRow.EntireRow.Font.Bold = true;
HeaderRow.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// Xử lý data-> mảng 2 chiều
object[,] rowData = new object[rowCount, colCount];
foreach (System.Data.DataRow row in dt.Rows)
{
for (int col = 0; col < colCount; col++)
{
if (IsNumeric(row[col].GetType().ToString()))
{
rowData[c, col] = System.Convert.ToDouble(row[col].ToString());
}
else
{
rowData[c, col] = row[col].ToString();
}
}
c++;
}
// Paste mảng vào excel
Ws.get_Range("A2").get_Resize(c, colCount).Value2 = rowData;
Ws.get_Range("A1").get_Resize(1, colCount).EntireColumn.AutoFit();
// Lưu file
string ExcelPath = AppDomain.CurrentDomain.BaseDirectory + string.Format("{0}.xlsx", filename);
if (System.IO.File.Exists(ExcelPath))
{
System.IO.File.Delete(ExcelPath);
}
Wb.SaveAs(ExcelPath, AccessMode: Excel.XlSaveAsAccessMode.xlShared);
Wb.Close();
if (isExcelOpen == 1)
{
App.Quit();
}
dt.Dispose();
}
catch (Exception ex)
{
throw ex;
}
// Dọn rác
System.Runtime.InteropServices.Marshal.ReleaseComObject(Ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
}
