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