public void Dispose()
{
thisWorkBook.Close(false);
dataWorkBook.Close(false);
templateDocument.Close(false);
excelApp.Quit();
wordApp.Quit();
}
private void GeraRelExcel(IEnumerable <Relexcel> listrel)
{
Microsoft.Office.Interop.Excel.Application App; // Aplicação Excel
Microsoft.Office.Interop.Excel.Workbook WorkBook; // Pasta
Microsoft.Office.Interop.Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
var i = 1;
foreach (var rel in listrel)
{
WorkSheet.Cells[i, 1] = string.Format("{0}/{1}/{2}", rel.Data.Day.ToString("00"), rel.Data.Month.ToString("00"), rel.Data.Year.ToString("00"));
WorkSheet.Cells[i, 2] = string.Format("{0}:{1}", ((int)rel.Hora.TotalHours).ToString("00"), rel.Hora.Minutes.ToString("00"));
WorkSheet.Cells[i, 3] = rel.Os;
WorkSheet.Cells[i, 4] = rel.Descricao;
i += 1;
}
// salva o arquivo
WorkBook.SaveAs(TBArquivo.Text + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
}
private void ExcelTablosunaAktar(List <LgsSonuc> ogrenciXls, List <LgsSonuc> okulXls, List <LgsSonuc> ilIlceXls)
{
//excel baş
string excelDosyaAdi = seciliDizin + "_Rapor_" + DateTime.Now.Ticks + ".xlsx";
Microsoft.Office.Interop.Excel.Application aplicacion = new Microsoft.Office.Interop.Excel.Application();
Workbook calismaKitabi = aplicacion.Workbooks.Add();
ExcelOgrenciSayfasi(ogrenciXls, calismaKitabi);
ExcelOkulSayfasi(okulXls, calismaKitabi);
ExcelIlceSayfasi(ilIlceXls, calismaKitabi);
calismaKitabi.SaveAs(excelDosyaAdi, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//calismaKitabi.Close(true);
//aplicacion.Quit();
// calismaKitabi.SaveAs(excelDosyaAdi, XlFileFormat.xlWorkbookNormal);
calismaKitabi.Close(true);
aplicacion.Quit();
Process.Start(excelDosyaAdi);
}
private void btnExport_Click(object sender, EventArgs e)
{
sfdExportExcel.InitialDirectory = "C:";
sfdExportExcel.Title = "Save as Excel File";
sfdExportExcel.FileName = "";
sfdExportExcel.Filter = "Excel Files(2007)|*.xlsx";
if (sfdExportExcel.ShowDialog() != DialogResult.Cancel)
{
Excel.Application excelApp = new Excel.Application();
excelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < dgwOTOutputs.Columns.Count + 1; i++)
{
excelApp.Cells[1, i] = dgwOTOutputs.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dgwOTOutputs.Rows.Count; i++)
{
for (int j = 0; j < dgwOTOutputs.Columns.Count; j++)
{
excelApp.Cells[i + 2, j + 1] = dgwOTOutputs.Rows[i].Cells[j].Value.ToString();
}
}
excelApp.ActiveWorkbook.SaveCopyAs(sfdExportExcel.FileName.ToString());
excelApp.ActiveWorkbook.Saved = true;
excelApp.Quit();
}
}
private void Import()
{
var path = OpenFile();
if (string.IsNullOrEmpty(path))
{
return;
}
Microsoft.Office.Interop.Excel.Application app = null;
try
{
app = new Microsoft.Office.Interop.Excel.Application();
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
app.Workbooks.Open(path);
dynamic _wb = app.Workbooks[1];
//生成CSV文件，并保存
string fileName = Path.GetTempFileName() + ".csv";
_wb.SaveAs(fileName, (int)XlFileFormat.xlCSV);
_wb.Close();
app.Workbooks.Close();
_wb = null;
app.Quit();
app = null;
GC.Collect();
ReadData(fileName);
}
catch (Exception e)
{
MessageBox.Show(this, e.Message);
}
finally
{
if (app != null)
{
app.Quit();
app = null;
}
GC.Collect();
}
}
public void Dispose()
{
thisWorkBook.Close(false);
//templateWorkBook.Close(false);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
}
private void ReleaseCom(Microsoft.Office.Interop.Excel.Application app)
{
if (app.Workbooks != null)
{
foreach (Microsoft.Office.Interop.Excel.Workbook wb in app.Workbooks)
{
foreach (Microsoft.Office.Interop.Excel.Worksheet ws in wb.Worksheets)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
}
wb.Close(false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
}
app.Workbooks.Close();
}
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
public void Dispose()
{
thisWorkBook.Close(false);
sumWorkBook.Close(false);
excelApp.Quit();
}
public void DataTableToExcel(System.Data.DataTable tmDataTable, string strFileName)
{
WaitingForm wtForm = new WaitingForm();
waitingBool = true;
wtForm.Show();
if (strFileName == null)
{
return;
}
int RowNum = tmDataTable.Rows.Count;
int ColumnNum = tmDataTable.Columns.Count;
int RowIndex = 2;
int ColumnIndex = 0;
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
//打开Excel应用
xlapp.DefaultFilePath = "";
xlapp.DisplayAlerts = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //创建一个Excel文件
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//拿到那个工作表
//foreach (DataColumn dc in tmDataTable.Columns)
//{
// ColumnIndex++;
// worksheet.Cells[RowIndex, ColumnIndex] = dc.ColumnName;
//}
//给两列写列名
worksheet.Columns.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
worksheet.Columns.ColumnWidth = 20;
Microsoft.Office.Interop.Excel.Range mergeRange;
mergeRange = worksheet.get_Range("A1", "B1");
mergeRange.Merge(0);
if (comboBox4.Text == "")
{
worksheet.Cells[1, 1] = comboBox1.Text + "-" + comboBox2.Text + "-" + comboBox3.Text;
}
else
{
worksheet.Cells[1, 1] = comboBox1.Text + "-" + comboBox2.Text + "-" + comboBox3.Text + "-" + comboBox4.Text;
}
worksheet.Cells[2, 1] = "值";
worksheet.Cells[2, 2] = "时间";
//添加寄存器对应的监控点名称
for (int i = 0; i < RowNum; i++)
{
RowIndex++;
ColumnIndex = 0;
for (int j = 1; j < 3; j++)
{
ColumnIndex++;
worksheet.Cells[RowIndex, ColumnIndex] = tmDataTable.Rows[i][j].ToString();
}
}
waitingBool = false;
workbook.SaveCopyAs(strFileName + ".xlsx");
MessageBox.Show("Excle表格导出成功,保存为" + strFileName);
//退出关闭EXCLE.EXE线程
xlapp.Quit();
IntPtr t = new IntPtr(xlapp.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
void ReadFile(string adresses_path)
{
Getter_adresses.Clear();
if (adresses_path.Contains(".txt"))
{
StreamReader sr = new StreamReader(adresses_path, System.Text.Encoding.Default);
string allText = sr.ReadToEnd();
string[] MBAddresses = allText.Split(new char[] { ' ', '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string li in MBAddresses)
{
Getter_adresses.Add(li);
}
sr.Close();
}
if (adresses_path.Contains(".doc") || adresses_path.Contains(".docx"))
{
try
{
Microsoft.Office.Interop.Word.Application word_app = null;
word_app = new Microsoft.Office.Interop.Word.Application();
word_app.Visible = false;
Document word_doc = word_app.Documents.Open(adresses_path);
Thread t = new Thread(new ThreadStart(() =>
{
for (int i = 0; i < word_doc.Paragraphs.Count; i++)
{
string[] temp = word_doc.Paragraphs[i + 1].Range.Text.Split(new char[] { ' ', '\n', '\r' },
StringSplitOptions.RemoveEmptyEntries);
foreach (var item in temp)
{
if (item.Contains("@"))
{
Getter_adresses.Add(item);
}
}
}
word_doc.Close();
word_app.Quit();
}));
t.IsBackground = false;
t.Start();
t.Join();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
if (adresses_path.Contains(".xls") || adresses_path.Contains(".xlsx"))
{
try
{
Stats.count = 0;
Microsoft.Office.Interop.Excel._Application excel_app = null;
excel_app = new Microsoft.Office.Interop.Excel.Application();
excel_app.Visible = false;
_Workbook excel_workbook = excel_app.Workbooks.Open(adresses_path);
_Worksheet excel_worksheet = excel_workbook.Sheets[1];
excel_worksheet = excel_workbook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range excel_range = excel_worksheet.UsedRange;
ScanExcl se = new ScanExcl(excel_app, excel_workbook, excel_worksheet, excel_range);
se.ShowDialog();
Getter_adresses = MessageWithData.Getter_Addresses;
excel_app.Quit();
try
{
foreach (Process proc in Process.GetProcessesByName("EXCEL"))
{
proc.Kill();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
GC.Collect();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
if (adresses_path.Contains(".accdb") || adresses_path.Contains(".mdb"))
{
string connection = $"Provider = Microsoft.Jet.OLEDB.4.0; Data Source ={adresses_path};";
//string connection = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={adresses_path};";
OleDbConnection myConnection = new OleDbConnection(connection);
myConnection.Open();
OleDbCommand coommand = new OleDbCommand("SELECT Emails_Col FROM Emails", myConnection);
OleDbDataReader reader = coommand.ExecuteReader();
Getter_adresses.Clear();
while (reader.Read())
{
int AddressIndex = reader.GetOrdinal("Emails_Col");
Getter_adresses.Add(reader.GetString(AddressIndex));
}
myConnection.Close();
}
}
public void saveXls(string FileName)
{
var excel = new Microsoft.Office.Interop.Excel.Application();
try
{
if (File.Exists(FileName))
{
excel.Workbooks.Open(FileName, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing);
}
else
{
excel.SheetsInNewWorkbook = 1;
excel.Workbooks.Add(Type.Missing);
excel.Workbooks[1].SaveAs(FileName, XlFileFormat.xlExcel8,
Type.Missing,
Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
Sheets excelsheets = excel.Workbooks[1].Worksheets;
var excelworksheet = (Worksheet)excelsheets.get_Item(1);
excelworksheet.Cells.Clear();
Microsoft.Office.Interop.Excel.Range excelcells = excelworksheet.get_Range("A1", "F1");
excelcells.Merge(Type.Missing);
excelcells.Font.Bold = true;
string title = "Список выполненных заявок за период с " + dateTimePickerFrom.Text + " по " + dateTimePickerTo.Text;
excelcells.Value2 = title;
excelcells.RowHeight = 40;
excelcells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelcells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
excelcells.Font.Name = "Times New Roman";
excelcells.Font.Size = 14;
for (int j = 0; j < dataGridViewReport.Columns.Count - 1; j++)
{
excelcells = excelworksheet.get_Range("A3", "A3");
excelcells = excelcells.get_Offset(0, j);
excelcells.ColumnWidth = 15;
excelcells.Value2 = dataGridViewReport.Columns[j + 1].HeaderCell.Value.ToString();
excelcells.Font.Bold = true;
}
for (int i = 0; i < dataGridViewReport.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewReport.Columns.Count - 1; j++)
{
excelcells = excelworksheet.get_Range("A4", "A4");
excelcells = excelcells.get_Offset(i, j);
excelcells.ColumnWidth = 20;
excelcells.Value2 = dataGridViewReport.Rows[i].Cells[j + 1].Value.ToString();
}
}
List <string> words = new List <string>();
string[] sum = { "", "", "", "", "Итого:" };
words.AddRange(sum);
words.Add(textBoxItog.Text);
for (int j = 0; j < words.Count; j++)
{
excelcells = excelworksheet.get_Range("A3", "A3");
excelcells = excelcells.get_Offset(dataGridViewReport.Rows.Count + 1, j);
excelcells.ColumnWidth = 25;
excelcells.Value2 = words[j].ToString();
excelcells.Font.Bold = true;
}
excel.Workbooks[1].Save();
excel.Workbooks[1].Close();
}
catch (Exception)
{
}
finally
{
excel.Quit();
}
}
public void SaveDataToExcel()
{
Microsoft.Office.Interop.Excel.Application app = null;
try
{
string log = Program.infoResource.GetLocalizedString(language.InfoId.DriveGraphData);
app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
XtraMessageBox.Show(Program.infoResource.GetLocalizedString(language.InfoId.NoOffice));
return;
}
// app.Visible = false;
//开启等待窗口
SplashScreenManager.ShowForm(this.ParentForm, typeof(wfMain), false, true);
//创建Excel中的工作薄
Workbook wb = (Workbook)app.Workbooks.Add(Missing.Value);
//创建Excel工作薄中的第一页 sheet
Worksheet sheet = (Worksheet)wb.ActiveSheet;
sheet.Name = "紫外设备";
sheet.Cells[1, 1] = "紫外";
sheet.Cells[2, 1] = "紫外背景光谱";
sheet.Cells[2, 2] = "遥测光谱";
sheet.Cells[2, 3] = "NO吸光度";
sheet.Cells[2, 4] = "HC吸光度";
sheet.Cells[2, 5] = "遥测吸光度";
//合并A1-G1,A1-G2标题居中，字号调整，字体调整
sheet.Range["A1", "E1"].Merge();
sheet.Range["A1", "E1"].ColumnWidth = 17;
Range rg = sheet.Range["A1", "E2"];
rg.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rg.Font.Size = 14;
rg.Font.Name = "宋体";
//导入数据
if (ResultDataViewModel.VM.ExhaustDetailData.UVSCalParam != null)
{
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.UVSCalParam.ZeroIntensity, 1);
}
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.UVSData, 2);
if (ResultDataViewModel.VM.ExhaustDetailData.UVSNOCalParam != null)
{
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.UVSNOCalParam.Absorb, 3);
}
if (ResultDataViewModel.VM.ExhaustDetailData.UVSHCCalParam != null)
{
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.UVSHCCalParam.Absorb, 4);
}
DriveDataToSheet(sheet, ResultDataViewModel.VM.ExhaustDetailData.AbsorbData, 5);
Worksheet wsRed = (Worksheet)wb.Sheets.Add(Missing.Value, sheet, Missing.Value, Missing.Value);
wsRed.Name = "红外设备";
wsRed.Cells[1, 1] = "红外";
wsRed.Cells[2, 1] = "红外背景光谱";
wsRed.Cells[2, 2] = "CO谐波";
wsRed.Cells[2, 3] = "CO2谐波";
wsRed.Cells[2, 4] = "红外背景谐波";
wsRed.Cells[2, 5] = "遥测谐波";
//合并A1-G1
wsRed.Range["A1", "E1"].Merge();
wsRed.Range["A1", "E1"].ColumnWidth = 17;
//A1-G2标题居中，字号调整，字体调整
Range rg2 = wsRed.Range["A1", "E2"];
rg2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rg2.Font.Size = 14;
rg2.Font.Name = "宋体";
//导入数据
if (ResultDataViewModel.VM.ExhaustDetailData.TDLASCalParam != null)
{
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.TDLASCalParam.ZeroIntensity, 1);
}
if (ResultDataViewModel.VM.ExhaustDetailData.TDLASCOCalParam != null)
{
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.TDLASCOCalParam.Harm, 2);
}
if (ResultDataViewModel.VM.ExhaustDetailData.TDLASCO2CalParam != null)
{
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.TDLASCO2CalParam.Harm, 3);
}
if (ResultDataViewModel.VM.ExhaustDetailData.TDLASCalParam != null)
{
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.TDLASCalParam.Harm, 4);
}
DriveDataToSheet(wsRed, ResultDataViewModel.VM.ExhaustDetailData.HarmData, 5);
//保存工作表,退出
SplashScreenManager.CloseForm();
dlgSave.ShowDialog();
if (!String.IsNullOrEmpty(dlgSave.FileName))
{
app.ActiveWorkbook.SaveAs(dlgSave.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
log += Program.infoResource.GetLocalizedString(language.InfoId.OpearteSuccess);
ErrorLog.SystemLog(DateTime.Now, log);
}
//else
// XtraMessageBox.Show(Program.infoResource.GetLocalizedString(language.InfoId.OperateFail));
//app.Save();
app.Quit();
}
catch (Exception ex)
{
//XtraMessageBox.Show(ex.Message+" "+ex.TargetSite.ToString());
// XtraMessageBox.Show(Program.infoResource.GetLocalizedString(language.InfoId.OperateFail));
if (app != null)
{
app.Quit();
}
ErrorLog.Error(ex.ToString());
}
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
List <BienLaiExcel> listBienLai;
if (checkExportAll.Checked)
{
listBienLai = _controllerSv.GetListBienLaiExcel(Convert.ToInt32(cbbBanThuHS.SelectedValue), dateTimePicker.Value);
}
else
{
listBienLai = _controllerSv.GetListBienLaiExcel(Convert.ToInt32(cbbBanThuHS.SelectedValue), dateTimePicker.Value, Convert.ToInt32(cbbBuoi.SelectedIndex));
}
if (listBienLai.Count < 1)
{
MessageBox.Show("Danh sách biên lai rỗng", "Thông báo");
return;
}
string saveExcelFile = Path.GetDirectoryName(Application.ExecutablePath) + @"\DataExport\" + dateTimePicker.Value.ToString("dd-MM-yyyy") + "_" + cbbBuoi.Text + "_" + cbbBanThuHS.Text + ".xlsx";
if (checkExportAll.Checked)
{
saveExcelFile = Path.GetDirectoryName(Application.ExecutablePath) + @"\DataExport\All_" + DateTime.Now.ToString("dd-MM-yyyy") + "_" + CanBoUser.Username + ".xlsx";
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
object misValue = Missing.Value;
Workbook wb = xlApp.Workbooks.Add(misValue);
Worksheet ws = (Worksheet)wb.Worksheets[1];
if (ws == null)
{
MessageBox.Show("Không thể tạo được WorkSheet");
return;
}
int row = 1;
string fontName = "Times New Roman";
int fontSizeTieuDe = 18;
int fontSizeTenTruong = 14;
int fontSizeNoiDung = 12;
//Xuất dòng Tiêu đề của File báo cáo: Lưu ý
Range row1TieuDeThongKeBienLai = ws.get_Range("A1", "N1");
row1TieuDeThongKeBienLai.Merge();
row1TieuDeThongKeBienLai.Font.Size = fontSizeTieuDe;
row1TieuDeThongKeBienLai.Font.Name = fontName;
row1TieuDeThongKeBienLai.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row1TieuDeThongKeBienLai.Value2 = checkExportAll.Checked ? "THỐNG KÊ DANH SÁCH TẤT CẢ BIÊN LAI" : "THỐNG KÊ DANH SÁCH BIÊN LAI-" + cbbBanThuHS.Text.ToUpper() + "-NGÀY " + dateTimePicker.Value.ToString("dd/MM/yyyy") + "-BUỔI " + cbbBuoi.Text;
//Tạo Ô Số Thứ Tự (STT)
Range row23Stt = ws.get_Range("A2", "A3");//Cột A dòng 2 và dòng 3
row23Stt.Merge();
row23Stt.Font.Size = fontSizeTenTruong;
row23Stt.Font.Name = fontName;
row23Stt.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23Stt.Value2 = "STT";
//Tạo Ô Số báo danh :
Range row23Sbd = ws.get_Range("B2", "B3");//Cột B dòng 2 và dòng 3
row23Sbd.Merge();
row23Sbd.Font.Size = fontSizeTenTruong;
row23Sbd.Font.Name = fontName;
row23Sbd.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23Sbd.Value2 = "SBD";
row23Sbd.ColumnWidth = 15;
//Tạo Ô Tên sinh viên :
Range row23TenSv = ws.get_Range("C2", "C3");//Cột C dòng 2 và dòng 3
row23TenSv.Merge();
row23TenSv.Font.Size = fontSizeTenTruong;
row23TenSv.Font.Name = fontName;
row23TenSv.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23TenSv.ColumnWidth = 40;
row23TenSv.Value2 = "Họ tên sinh viên";
//Tạo Ô Mã ngành :
Range row23MaNganh = ws.get_Range("D2", "D3");//Cột D dòng 2 và dòng 3
row23MaNganh.Merge();
row23MaNganh.Font.Size = fontSizeTenTruong;
row23MaNganh.Font.Name = fontName;
row23MaNganh.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23MaNganh.ColumnWidth = 15;
row23MaNganh.Value2 = "Mã ngành";
//Tạo Ô Tên ngành :
Range row23TenNganh = ws.get_Range("E2", "E3");//Cột E dòng 2 và dòng 3
row23TenNganh.Merge();
row23TenNganh.Font.Size = fontSizeTenTruong;
row23TenNganh.Font.Name = fontName;
row23TenNganh.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23TenNganh.ColumnWidth = 40;
row23TenNganh.Value2 = "Tên ngành";
//Tạo Ô Tên lớp :
Range row23Lop = ws.get_Range("F2", "F3");//Cột F dòng 2 và dòng 3
row23Lop.Merge();
row23Lop.Font.Size = fontSizeTenTruong;
row23Lop.Font.Name = fontName;
row23Lop.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23Lop.ColumnWidth = 10;
row23Lop.Value2 = "Lớp";
//Tạo Ô Tên chương trình :
Range row23TenChuongTrinh = ws.get_Range("G2", "G3");//Cột G dòng 2 và dòng 3
row23TenChuongTrinh.Merge();
row23TenChuongTrinh.Font.Size = fontSizeTenTruong;
row23TenChuongTrinh.Font.Name = fontName;
row23TenChuongTrinh.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row23TenChuongTrinh.ColumnWidth = 30;
row23TenChuongTrinh.Value2 = "Chương trình";
//Tạo Ô tiền :
Range row2Tien = ws.get_Range("H2", "N2");//Cột H->N của dòng 2
row2Tien.Merge();
row2Tien.Font.Size = fontSizeTenTruong;
row2Tien.Font.Name = fontName;
row2Tien.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row2Tien.Value2 = "Tiền đã thu";
//Tạo Ô Học phí:
Range row3TienHp = ws.get_Range("H3", "H3");//Ô H3
row3TienHp.Font.Size = fontSizeTenTruong;
row3TienHp.Font.Name = fontName;
row3TienHp.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row3TienHp.Value2 = "Học phí";
row3TienHp.ColumnWidth = 20;
//Tạo Ô BHYT:
Range row3TienBhyt = ws.get_Range("I3", "I3");//Ô I3
row3TienBhyt.Font.Size = fontSizeTenTruong;
row3TienBhyt.Font.Name = fontName;
row3TienBhyt.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row3TienBhyt.Value2 = "Tiền BHYT";
row3TienBhyt.ColumnWidth = 20;
//Tạo Ô BHTT:
Range row3TienBtyt = ws.get_Range("J3", "J3");//Ô J3
row3TienBtyt.Font.Size = fontSizeTenTruong;
row3TienBtyt.Font.Name = fontName;
row3TienBtyt.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row3TienBtyt.Value2 = "Tiền BHTT";
row3TienBtyt.ColumnWidth = 20;
//Tạo Ô tiền GDQP:
Range row3TienGdqp = ws.get_Range("K3", "K3");//Ô K3
row3TienGdqp.Font.Size = fontSizeTenTruong;
row3TienGdqp.Font.Name = fontName;
row3TienGdqp.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row3TienGdqp.Value2 = "Tiền GDQP";
row3TienGdqp.ColumnWidth = 20;
//Tạo Ô tiền AVDV:
Range row3TienAvdv = ws.get_Range("L3", "L3");//Ô L3
row3TienAvdv.Font.Size = fontSizeTenTruong;
row3TienAvdv.Font.Name = fontName;
row3TienAvdv.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row3TienAvdv.Value2 = "Tiền Thi AVDV";
row3TienAvdv.ColumnWidth = 20;
//Tạo Ô tiền khám sức khỏe:
Range row3TienKhamSk = ws.get_Range("M3", "M3");//Ô M3
row3TienKhamSk.Font.Size = fontSizeTenTruong;
row3TienKhamSk.Font.Name = fontName;
row3TienKhamSk.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row3TienKhamSk.Value2 = "Tiền khám SK";
row3TienKhamSk.ColumnWidth = 20;
//Tạo Ô tổng tiền:
Range row3TongTienThu = ws.get_Range("N3", "N3");//Ô N3
row3TongTienThu.Font.Size = fontSizeTenTruong;
row3TongTienThu.Font.Name = fontName;
row3TongTienThu.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row3TongTienThu.Value2 = "Tổng tiền đã thu";
row3TongTienThu.ColumnWidth = 20;
//Tô nền vàng các cột tiêu đề:
Range row23CotTieuDe = ws.get_Range("A2", "N3");
//nền vàng
row23CotTieuDe.Interior.Color = ColorTranslator.ToOle(Color.Yellow);
//in đậm
row23CotTieuDe.Font.Bold = true;
//chữ đen
row23CotTieuDe.Font.Color = ColorTranslator.ToOle(Color.Black);
int stt = 0;
row = 3;//dữ liệu xuất bắt đầu từ dòng số 4 trong file Excel (khai báo 3 để vào vòng lặp nó ++ thành 4)
foreach (BienLaiExcel bienLai in listBienLai)
{
stt++;
row++;
dynamic[] arr =
{
stt, "\'" + bienLai.SBD, bienLai.Name, bienLai.MaNganh, bienLai.TenNganh, bienLai.Lop,
bienLai.TenChuongTrinh, bienLai.TienHocPhi, bienLai.TienBHYT, bienLai.TienBHTT,
bienLai.TienGDQP, bienLai.TienAVDV, bienLai.TienKhamSK, bienLai.TongTienDaThu
};
Range rowData = ws.get_Range("A" + row, "N" + row);//Lấy dòng thứ row ra để đổ dữ liệu
rowData.Font.Size = fontSizeNoiDung;
rowData.Font.Name = fontName;
Range rowCurrence = ws.get_Range("H" + row, "N" + row);
if (bienLai.DaNopTien)
{
rowCurrence.NumberFormat = "###,###,###,###";
}
Range rowMaNganh = ws.get_Range("D" + row, "D" + row);
rowMaNganh.NumberFormat = "@";
Range rowSBD = ws.get_Range("B" + row, "B" + row);
rowSBD.NumberFormat = "@";
rowData.Value2 = arr;
}
row = row + 1;
Range rowNH = ws.get_Range("H" + row, "N" + row);
rowNH.Font.Size = fontSizeNoiDung;
rowNH.Font.Name = fontName;
rowNH.Font.FontStyle = "bold";
Range rowSumColH = ws.get_Range("H" + row, "H" + row);
rowSumColH.Formula = "=sum(H4:H" + (row - 1) + ")";
Range rowSumColI = ws.get_Range("I" + row, "I" + row);
rowSumColI.Formula = "=sum(I4:I" + (row - 1) + ")";
Range rowSumColJ = ws.get_Range("J" + row, "J" + row);
rowSumColJ.Formula = "=sum(J4:J" + (row - 1) + ")";
Range rowSumColK = ws.get_Range("K" + row, "K" + row);
rowSumColK.Formula = "=sum(K4:K" + (row - 1) + ")";
Range rowSumColL = ws.get_Range("L" + row, "L" + row);
rowSumColL.Formula = "=sum(L4:L" + (row - 1) + ")";
Range rowSumColM = ws.get_Range("M" + row, "M" + row);
rowSumColM.Formula = "=sum(M4:M" + (row - 1) + ")";
Range rowSumColN = ws.get_Range("N" + row, "N" + row);
rowSumColN.Formula = "=sum(N4:N" + (row - 1) + ")";
//Kẻ khung toàn bộ
BorderAround(ws.get_Range("A2", "N" + row));
//Lưu file excel xuống Ổ cứng
wb.SaveAs(saveExcelFile);
//đóng file để hoàn tất quá trình lưu trữ
wb.Close(true, misValue, misValue);
//thoát và thu hồi bộ nhớ cho COM
xlApp.Quit();
ReleaseObject(ws);
ReleaseObject(wb);
ReleaseObject(xlApp);
//Mở File excel sau khi Xuất thành công
Process.Start(saveExcelFile);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Dispose()
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
}
public void Dispose()
{
thisWorkBook.Close(false);
templateWorkBook.Close(false);
excelApp.Quit();
}
private void BtnExcel_Click(object sender, EventArgs e)
{
try
{
SaveFileDialog save = new SaveFileDialog();
save.Filter = "Excel {*.xlsx}|*.xlsx";
save.Title = "Guardar Reporte";
save.FileName = "Exportacion Reporte-" + DateTime.Now.ToString().Replace(":", "-").Replace("/", "-");
if (save.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook libro = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet hoja = null;
hoja = libro.Sheets["Hoja1"];
hoja = libro.ActiveSheet;
hoja.Name = "Hoja1";
int columna = 0;
foreach (DataGridViewColumn col in DGVRestantes.Columns)
{
columna++;
app.Cells[1, columna] = col.Name;
}
int fila = 0;
foreach (DataGridViewRow row in DGVRestantes.Rows)
{
fila++;
columna = 0;
foreach (DataGridViewColumn col in DGVRestantes.Columns)
{
columna++;
app.Cells[fila + 1, columna] = row.Cells[col.Name].Value;
}
}
fila = fila + 3;
columna = 0;
foreach (DataGridViewColumn col in DGVInventario.Columns)
{
columna++;
app.Cells[fila, columna] = col.Name;
}
fila = fila - 1;
foreach (DataGridViewRow row in DGVInventario.Rows)
{
fila++;
columna = 0;
foreach (DataGridViewColumn col in DGVInventario.Columns)
{
columna++;
app.Cells[fila + 1, columna] = row.Cells[col.Name].Value;
}
}
libro.SaveAs(save.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//libro.Close();
app.Quit();
MessageBox.Show("Archivo Excel generado !!!", "Exito", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("" + ex);
throw;
}
}
