public void Initialize()
{
mergeInstanceApplication = new Excel.Application(); // Create a new application for another excel to merge instance.
excelApplication = new Excel.Application();
if (!File.Exists(this._fileName))
{
excelWorkbook = excelApplication.Workbooks.Add();
}
if (File.Exists(this._fileName))
{
File.Copy(this._fileName, this._tmpFileName, true);
excelWorkbook = excelApplication.Application.Workbooks.Open(_tmpFileName, ReadOnly: false);
}
excelApplication.DisplayAlerts = false;
excelWorkbook = excelApplication.Workbooks[1];
excelWorkbook.Activate();
int worksheetNum = excelWorkbook.Worksheets.Count;
excelWorksheet = excelWorkbook.Worksheets[worksheetNum];
excelWorksheet.Activate();
}
public void PreencherErro(string erro, string cpf, string convenio)
{
if (PlanilhaErros == null)
{
CriarSaidaErro();
Excel.Worksheet ws = PlanilhaErros.ActiveSheet;
ws.Cells.NumberFormat = "@";
ws.Cells[1, 1] = "CPF";
ws.Cells[1, 2] = "Convênio";
ws.Cells[1, 3] = "Erro";
linhaErro = 2;
}
PlanilhaErros.Activate();
Excel.Worksheet ws2 = PlanilhaErros.ActiveSheet;
ws2.Cells[linhaErro, 1] = cpf;
ws2.Cells[linhaErro, 2] = convenio;
ws2.Cells[linhaErro, 3] = erro;
linhaErro++;
SalvarPlanilhas();
}
internal static IReadOnlyCollection <Rate> ParsePZU(string excelFilePath)
{
var result = new HashSet <Rate>();
#region fix column count by re-save
string fullExcelFilePath = Path.Combine(Directory.GetCurrentDirectory(), excelFilePath);
ExcelInterop.Application xl = new ExcelInterop.Application();
ExcelInterop.Workbook wb = xl.Application.Workbooks.Open(fullExcelFilePath);
wb.Activate();
wb.Save();
wb.Close();
xl.Quit();
#endregion
FileStream stream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
DataSet dataset = excelReader.AsDataSet();
bool firstRow = true;
foreach (DataRow row in dataset.Tables[0].Rows)
{
if (firstRow)
{
firstRow = false;
continue;
}
//-2 poniżej bo https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year
DateTime courseDate = new DateTime(1900, 1, 1).AddDays(Convert.ToInt32(row[0]) - 2);
result.Add(new Rate(courseDate, Convert.ToDecimal(row[1])));
}
excelReader.Close();
return(result);
}
private Microsoft.Office.Interop.Excel.Worksheet fGenerateExcel()
{
// insert new excel sheet to copy errors to it
Microsoft.Office.Interop.Excel.Worksheet vWorkSheet = new Worksheet();
try {
vWorkSheet = vWorkBook.Worksheets.Add();
vWorkSheet.Name = ("Errors" + DateTime.Now.Ticks.ToString());
vWorkBook.Activate();
if ((System.Threading.Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName == "ar"))
{
vWorkSheet.DisplayRightToLeft = true;
}
else if ((System.Threading.Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName == "en"))
{
vWorkSheet.DisplayRightToLeft = false;
}
vXLS.Visible = true;
}
catch (Exception ex) {
// vProgress.sProgressOff()
vWorkBook.Close(true);
// sKillAllExcels()
ExceptionHandler.HandleException(ex.Message, "cImportExcel", "fGenerateExcel");
}
return(vWorkSheet);
}
private bool checkValue(Excel.Workbook book, Excel.Worksheet sheet, String cell)
{
book.Activate();
if (book.ActiveSheet != sheet)
{
sheet.Select();
}
Excel.Range test = sheet.get_Range(cell);
try
{
sheet.Select(test);
}
catch (Exception e)
{
// apparently, an empty cell is not valid for selection.
return(true);
}
try
{
if (test.Value2 == "" || test.Value2 == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception e)
{
return(false);
}
}
private void doOpen(CodeActivityContext context)
{
var readPassword = ReadPassword.Get(context);
if (string.IsNullOrEmpty(readPassword))
{
readPassword = null;
}
var writePassword = WritePassword.Get(context);
if (string.IsNullOrEmpty(writePassword))
{
writePassword = null;
}
if (!string.IsNullOrEmpty(filename))
{
foreach (Microsoft.Office.Interop.Excel.Workbook w in officewrap.application.Workbooks)
{
if (w.FullName == filename)
{
try
{
workbook = w;
worksheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
break;
}
catch (Exception)
{
workbook = null;
}
}
}
if (workbook == null)
{
officewrap.application.DisplayAlerts = false;
//application.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
if (System.IO.File.Exists(filename))
{
workbook = officewrap.application.Workbooks.Open(filename, ReadOnly: false,
Password: readPassword, WriteResPassword: writePassword);
}
else
{
if (!string.IsNullOrEmpty(filename))
{
workbook = officewrap.application.Workbooks.Add();
workbook.Activate();
//workbook.SaveCopyAs(filename);
workbook.SaveAs(Filename: filename);
}
}
officewrap.application.DisplayAlerts = true;
}
}
if (workbook == null)
{
workbook = officewrap.application.ActiveWorkbook;
}
}
private void btnInHD_Click(object sender, EventArgs e)
{
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
Excel.Range exRang = (Excel.Range)exSheet.Cells[1, 1];
exRang.Range["A1:A2"].Font.Bold = true;
exRang.Range["A1:A2"].Font.Size = 14;
exRang.Range["A1"].Value = "Địa chỉ: Ngõ 22 Cầu Giấy Hà Nội";
exRang.Range["A2"].Value = "Điện thoại: 098786765";
exRang.Range["C4"].Font.Bold = true;
exRang.Range["C4"].Font.Size = 20;
exRang.Range["C4"].Font.Color = Color.Red;
exRang.Range["c4"].Value = "HÓA ĐƠN BÁN";
exRang.Range["A6"].Font.Size = 14;
exRang.Range["A6"].Value = "Khách hàng:" + txtTenKhach.Text;
exRang.Range["A8:G8"].Font.Bold = true;
exRang.Range["A8"].Value = "STT";
exRang.Range["B8"].Value = "Mã hàng";
exRang.Range["C8"].Value = "Tên hàng";
exRang.Range["D8"].Value = "Số lượng";
exRang.Range["E8"].Value = "Đơn giá";
exRang.Range["F8"].Value = "Giảm giá";
exRang.Range["G8"].Value = "Thành tiền";
int row = 8;
for (int i = 0; i < dgvChiTietHang.Rows.Count - 1; i++)
{
row = row + 1;
exRang.Range["A" + row.ToString()].Value = (i + 1).ToString();
exRang.Range["B" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[0].Value.ToString();
exRang.Range["C" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[1].Value.ToString();
exRang.Range["D" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[2].Value.ToString();
exRang.Range["E" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[3].Value.ToString();
exRang.Range["F" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[4].Value.ToString();
exRang.Range["G" + row.ToString()].Value = dgvChiTietHang.Rows[i].Cells[5].Value.ToString();
}
exRang.Range["E" + (row + 2).ToString()].Value = "Tổng tiền: " + txtTongTien.Text;
exSheet.Name = "HDBan";
exBook.Activate();
SaveFileDialog svFile = new SaveFileDialog();
if (svFile.ShowDialog() == DialogResult.OK)
{
exBook.SaveAs(svFile.FileName);
}
exApp.Quit();
}
private void BtnXuatFile_NCC_Click(object sender, EventArgs e)
{
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
Excel.Range exRang = (Excel.Range)exSheet.Cells[1, 1];
exRang.Range["A1:A2:A3:D1"].Font.Bold = true;
exRang.Range["A1:A2:A3:D1"].Font.Size = 14;
exRang.Range["A1"].Value = lb_TenCH.Text;
exRang.Range["A2"].Value = lb_DC.Text;
exRang.Range["A3"].Value = lb_SDT.Text;
exRang.Range["D1"].Value = "Hà Nội Ngày: " + lbTime.Text;
exRang.Range["C4"].Font.Bold = true;
exRang.Range["C4"].Font.Size = 22;
exRang.Range["C4"].Value = "Danh Sách Nhà Cung Cấp";
exRang.Range["C4"].Font.Color = Color.Red;
exRang.Range["A6:F6"].Font.Bold = true;
exRang.Range["A6:F6"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
exRang.Range["B6:C6"].ColumnWidth = 20;
exRang.Range["D6"].ColumnWidth = 10;
exRang.Range["C6:E6"].ColumnWidth = 20;
exRang.Range["A6"].Value = "STT";
exRang.Range["B6"].Value = "ID NCC";
exRang.Range["C6"].Value = "Tên";
exRang.Range["D6"].Value = "ĐịaChỉ";
exRang.Range["E6"].Value = "Số Điện Thoại";
int row = 6;
for (int i = 0; i < dgvNhaCungCap.Rows.Count - 1; i++)
{
row++;
exRang.Range["A" + row.ToString() + ":" + "E" + row.ToString()].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
exRang.Range["A" + row.ToString()].Value = (i + 1).ToString();
exRang.Range["B" + row.ToString()].Value = dgvNhaCungCap.Rows[i].Cells[0].Value.ToString();
exRang.Range["C" + row.ToString()].Value = dgvNhaCungCap.Rows[i].Cells[1].Value.ToString();
exRang.Range["D" + row.ToString()].Value = dgvNhaCungCap.Rows[i].Cells[2].Value.ToString();
exRang.Range["E" + row.ToString()].Value = "SDT: " + dgvNhaCungCap.Rows[i].Cells[3].Value.ToString();
}
row = row + 2;
exRang.Range["E" + row.ToString()].Font.Bold = true;
exRang.Range["E" + row.ToString()].Value = "Tổng Số NCC: " + (dgvNhaCungCap.Rows.Count - 1);
exSheet.Name = "Nhà Cung Cấp";
exBook.Activate();
SaveFileDialog svFile = new SaveFileDialog();
if (svFile.ShowDialog() == DialogResult.OK)
{
exBook.SaveAs(svFile.FileName);
MessageBox.Show("Đã xuất file thành công.", "Chúc Mừng", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
exApp.Quit();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
Excel.Range exRang = (Excel.Range)exSheet.Cells[1, 1];
exRang.Range["A1:A2"].Font.Bold = true;
exRang.Range["A1:A2"].Font.Size = 14;
exRang.Range["A1"].Value = "Địa chỉ số 236 Hoàng Quốc Việt";
exRang.Range["A2"].Value = "Số điện thoại: 0123566789 ";
exRang.Range["C4"].Font.Bold = true;
exRang.Range["C4"].Font.Size = 22;
exRang.Range["C4"].Value = "DANH SÁCH ĐỘC GIẢ ";
exRang.Range["C4"].Font.Color = Color.Red;
exRang.Range["A6:F6"].Font.Bold = true;
exRang.Range["A6:F6"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
exRang.Range["B6:C6"].ColumnWidth = 20;
exRang.Range["D6"].ColumnWidth = 30;
exRang.Range["E6:G6"].ColumnWidth = 20;
exRang.Range["A6"].Value = "STT";
exRang.Range["B6"].Value = "Mã Độc Giả";
exRang.Range["C6"].Value = "Tên Độc Giả";
exRang.Range["D6"].Value = "Ngày Sinh";
exRang.Range["E6"].Value = "Giới Tính";
exRang.Range["F6"].Value = "Địa Chỉ";
exRang.Range["G6"].Value = "Lớp";
int row = 7;
for (int i = 0; i < dgvDocGia.Rows.Count - 1; i++)
{
row++;
exRang.Range["A" + row.ToString()].Value = (i + 1).ToString();
exRang.Range["B" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[0].Value.ToString();
exRang.Range["C" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[1].Value.ToString();
exRang.Range["D" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[2].Value.ToString();
exRang.Range["E" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[3].Value.ToString();
exRang.Range["F" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[4].Value.ToString();
exRang.Range["G" + row.ToString()].Value = dgvDocGia.Rows[i].Cells[5].Value.ToString();
}
exBook.Activate();
SaveFileDialog svFile = new SaveFileDialog();
if (svFile.ShowDialog() == DialogResult.OK)
{
exBook.SaveAs(svFile.FileName);
MessageBox.Show("Đã xuất file thành công");
}
exApp.Quit();
}
public void consolidarArchivos(string ruta1, string ruta2, string nombre1, string nombre2)
{
Excel.Application miExcel = new Excel.Application();
miExcel.Visible = true;
miExcel.DisplayAlerts = false;
Excel.Workbook libro = miExcel.Workbooks.Open(ruta1);
((Excel.Worksheet)miExcel.Sheets[1]).Select();
Excel.Worksheet SheetExcel = (Excel.Worksheet)libro.ActiveSheet;
SheetExcel.Columns.EntireColumn.AutoFit();
Excel.Workbook librof = miExcel.Workbooks.Add();
((Excel.Worksheet)miExcel.Sheets[1]).Select();
Excel.Worksheet PestañaF = (Excel.Worksheet)librof.ActiveSheet;
Excel.Range rango = PestañaF.get_Range("A:Z");
rango.Select();
rango.NumberFormat = "@";
copiarArchivos(SheetExcel, PestañaF);
PestañaF.Name = nombre1;
PestañaF.Columns.EntireColumn.AutoFit();
rango = PestañaF.get_Range("1:1");
rango.Select();
rango.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent2;
rango.Font.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1;
rango.Font.Bold = true;
libro.Close();
libro = miExcel.Workbooks.Open(ruta2);
((Excel.Worksheet)miExcel.Sheets[1]).Select();
SheetExcel = (Excel.Worksheet)libro.ActiveSheet;
SheetExcel.Columns.EntireColumn.AutoFit();
librof.Worksheets.Add();
librof.Activate();
((Excel.Worksheet)miExcel.Sheets[1]).Select();
PestañaF = (Excel.Worksheet)librof.ActiveSheet;
rango = PestañaF.get_Range("A:Z");
rango.Select();
rango.NumberFormat = "@";
copiarArchivos(SheetExcel, PestañaF);
PestañaF.Name = nombre2;
PestañaF.Columns.EntireColumn.AutoFit();
rango = PestañaF.get_Range("1:1");
rango.Select();
rango.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent2;
rango.Font.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1;
rango.Font.Bold = true;
libro.Close();
if (!Directory.Exists(rutaEscritorio + @"\Archivos Generados\"))
{
Directory.CreateDirectory(rutaEscritorio + @"\Archivos Generados\");
}
string nombre = "Chedraui Estado de Cuenta " + nombreAleatorio() + ".xlsx";
librof.SaveAs(rutaEscritorio + @"\Archivos Generados\" + nombre);
File.Delete(ruta1);
File.Delete(ruta2);
}
private void toCsv(string dPath, string xPath)
{
int i = 1;
string src;
//Launch Excel App
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
//open Workbook
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(xPath);
xlWorkbook.Activate();
sheetName = new string[xlWorkbook.Worksheets.Count];
//Convert to csv (1 sheet => 1 .csv)
foreach (Excel.Worksheet sheet in xlWorkbook.Worksheets)
{
sheet.Select();
sheetName[i - 1] = sheet.Name;
src = System.IO.Directory.GetCurrentDirectory() + "\\Sheet_" + i.ToString() + ".csv";
xlWorkbook.SaveAs(src, Excel.XlFileFormat.xlCSV);
srcList.Add(src);
i++;
}
//Clean all Interop shits
foreach (Excel.Worksheet sheet in xlWorkbook.Sheets)
{
while (Marshal.ReleaseComObject(sheet) != 0)
{
}
}
xlWorkbook.Close(false);
xlApp.Quit();
while (Marshal.ReleaseComObject(xlApp) != 0)
{
}
while (Marshal.ReleaseComObject(xlWorkbook) != 0)
{
}
xlWorkbook = null;
xlApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.WaitForPendingFinalizers();
}
public bool LoadXLS(string strFileName)
{
fileName = strFileName;
string nakedFileName = System.IO.Path.GetFileName(strFileName);
Excel.Application app = new Excel.Application();
app.Workbooks.Open(strFileName, 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.Workbook workBook = app.Workbooks[1];
workBook.Activate();
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[1];
pgbCurrent.Minimum = 2;
pgbCurrent.Maximum = sheet.UsedRange.Rows.Count;
pgbCurrent.Value = 2;
int rowCont = sheet.UsedRange.Rows.Count;
MH4Global.lbXLS.Text = "Loading and Converting... : " + nakedFileName;
MH4Global.lbXLS.Update();
// 일반 퀘스트 문서인지, 스폐셜 퀘스트 문서인지 파악한다.
bool bIsSpecialXLS = MH4Global.IsSpecialQuestXLS(strFileName);
Thread[] trdList = new Thread[MH4Global.THREAD_MAX];
for (int i = 0; i < MH4Global.THREAD_MAX; ++i)
{
ThreadTask task = new ThreadTask(dataBase, i + 2, rowCont + 1, MH4Global.THREAD_MAX, sheet.UsedRange);
if (bIsSpecialXLS)
{
trdList[i] = new Thread(task.LoadXLSSpecial);
}
else
{
trdList[i] = new Thread(task.LoadXLS);
}
trdList[i].IsBackground = true;
trdList[i].Start();
}
for (int i = 0; i < MH4Global.THREAD_MAX; ++i)
{
trdList[i].Join();
}
app.ActiveWorkbook.Close(false, Missing.Value, Missing.Value);
return(true);
}
/// <summary>
/// Gets a list of all column headers
/// </summary>
/// <returns>A list of all row 1 column contents</returns>
internal MSExcel.Range GetColumnHeaders()
{
try
{
_wkBook.Activate();
_wkSheet.Select(false);
_colRange = (MSExcel.Range)_wkSheet.Rows[1];
return(_colRange);
}
catch (Exception)
{
Console.WriteLine("Cannot get requested colum headers.");
return(null);
}
}
/// <summary>
/// Abstract base method for selecting a worksheet in an open workbook
/// </summary>
/// <param name="_sheet">The name of the worksheet to select</param>
/// <returns>A reference to the worksheet requested</returns>
public virtual MSExcel.Worksheet SelectWorksheet(string _sheet)
{
try
{
_wkBook.Activate();
_wkSheet = (MSExcel.Worksheet)_app.Worksheets[_sheet];
_wkSheet.Select(false);
return(_wkSheet);
}
catch (Exception)
{
Console.WriteLine("Cannot select requested worksheet.");
return(null);
}
}
private void button1_Click(object sender, EventArgs e)
{
Client.Unsubscribe(new String[] { "dkesp32" });
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open("D:/quanlynhansu-EXCEL.xlsx");
Excel.Worksheet xlWorksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
workbook.Activate();
xlWorksheet.Activate();
app.Visible = true;
app.DisplayAlerts = false;
//Environment.Exit(1);
}
public override object OpenFile(ISiteSetting siteSetting, string filePath)
{
// set the file name from the open file dialog
string fileName = filePath;
object readOnly = false;
object isVisible = true;
// Here is the way to handle parameters you don't care about in .NET
object missing = Type.Missing; // System.Reflection.Missing.Value;
// Make Excel visible, so you can see what's happening
// Open the document that was chosen by the dialog
Excel.Workbook doc = this.ExcelApplication.Workbooks.Open(fileName, missing, readOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
// Activate the document so it shows up in front
doc.Activate();
return(doc);
}
private void ExportButton_Click(object sender, RoutedEventArgs e)
{
Excel.Application excel = new Excel.Application();
excel.Visible = false;
Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
workbook.Activate();
Excel.Worksheet sheet1 = (Excel.Worksheet)workbook.Sheets[1];
for (int j = 0; j < OrdersGrid.Columns.Count; j++)
{
Excel.Range myRange = (Excel.Range)sheet1.Cells[1, j + 1];
sheet1.Cells[1, j + 1].Font.Bold = true;
sheet1.Columns[j + 1].ColumnWidth = 15;
myRange.Value2 = OrdersGrid.Columns[j].Header;
}
for (int i = 0; i < OrdersGrid.Columns.Count - 1; i++)
{
for (int j = 0; j < OrdersGrid.Items.Count; j++)
{
TextBlock b = OrdersGrid.Columns[i].GetCellContent(OrdersGrid.Items[j]) as TextBlock;
if (b.Text == null)
{
break;
}
Excel.Range myRange = (Excel.Range)sheet1.Cells[j + 2, i + 1];
myRange.Value2 = b.Text;
}
}
SaveFileDialog savefile = new SaveFileDialog();
savefile.Filter = "Excel Documents (*.xlsx)|*.xlsx";
savefile.FileName = $"AllOrders_{DateTime.Now.Day.ToString()}.{DateTime.Now.Month}.{DateTime.Now.Year}_{DateTime.Now.Hour}.{DateTime.Now.Minute}.xlsx";
if (savefile.ShowDialog() == false)
{
return;
}
string filename = savefile.FileName;
excel.DisplayAlerts = false;
workbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
workbook.Close();
excel.Quit();
MessageBox.Show("File Created!");
}
public override void OpenNewFile(string templatePath)
{
// set the file name from the open file dialog
string fileName = templatePath;
object readOnly = false;
object isVisible = true;
// Here is the way to handle parameters you don't care about in .NET
object missing = System.Reflection.Missing.Value;
// Make Excel visible, so you can see what's happening
// Open the document that was chosen by the dialog
Excel.Workbook doc = this.ExcelApplication.Workbooks.Add(fileName);
// SetProperty(doc, "Path", "http://demo.sobiens.com/Sobiens.Connectors/Shared Documents");
// doc.Path = "";
// , ref missing, ref readOnly, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref isVisible);
// Activate the document so it shows up in front
doc.Activate();
}
private void btnDesactivar_Click(object sender, RibbonControlEventArgs e)
{
// el nombre de una Key debe incluir un root valido.
Cursor.Current = Cursors.WaitCursor;
const string userRoot = "HKEY_CURRENT_USER";
const string subkey = "Software\\Microsoft\\Office\\Excel\\Addins\\SAT.Dictamenes.SIPRED.Client";
const string keyName = userRoot + "\\" + subkey;
object addInName = "SAT.Dictamenes.SIPRED.Client";
Registry.SetValue(keyName, "LoadBehavior", 0);
Office.COMAddIn addIn = Globals.ThisAddIn.Application.COMAddIns.Item(ref addInName);
addIn.Connect = false;
MessageBox.Show("El AddIn [SAT] quedó deshabilitado", "AddIn SAT", MessageBoxButtons.OK, MessageBoxIcon.Information);
Excel.Workbook nwbook = Globals.ThisAddIn.Application.Workbooks.Add(System.Type.Missing);
nwbook.Activate();
Cursor.Current = Cursors.Default;
}
public static void ConvertExcelToPdf(string excelFileIn, string pdfFileOut)
{
msExcel.Application excel = new msExcel.Application();
try
{
excel.Visible = false;
excel.ScreenUpdating = false;
excel.DisplayAlerts = false;
FileInfo excelFile = new FileInfo(excelFileIn);
string filename = excelFile.FullName;
msExcel.Workbook wbk = excel.Workbooks.Open(filename, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing);
wbk.Activate();
object outputFileName = wbk.FullName.Replace(".xslx", ".pdf");;
msExcel.XlFixedFormatType fileFormat = msExcel.XlFixedFormatType.xlTypePDF;
// Save document into PDF Format
wbk.ExportAsFixedFormat(fileFormat, outputFileName,
missing, missing, missing,
missing, missing, missing,
missing);
object saveChanges = msExcel.XlSaveAction.xlDoNotSaveChanges;
((msExcel._Workbook)wbk).Close(saveChanges, missing, missing);
wbk = null;
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
((msExcel._Application)excel).Quit();
excel = null;
}
}
public void ColarDados(List <string[]> Tabela, string mensagem)
{
if (PlanilhaContratos == null)
{
CriarSaida();
Excel.Worksheet ws = PlanilhaContratos.ActiveSheet;
ws.Cells.NumberFormat = "@";
ws.Cells[1, 1] = "CPF";
ws.Cells[1, 2] = "Origem Contrato";
ws.Cells[1, 3] = "Contrato";
ws.Cells[1, 4] = "Matrícula";
ws.Cells[1, 5] = "Data de Nascimento";
ws.Cells[1, 6] = "Órgão/Filial";
ws.Cells[1, 7] = "Saldo p/ Refin.(*)";
ws.Cells[1, 8] = "Parc. Refin. (*)";
ws.Cells[1, 9] = "Parc. Abertas";
ws.Cells[1, 10] = "Prz. do Contrato";
ws.Cells[1, 11] = "Vlr Parcela (R$)";
ws.Cells[1, 12] = "% Parc. Pagas";
ws.Cells[1, 13] = "Ação";
ws.Cells[1, 14] = "Mensagem";
linhaColada = 2;
}
PlanilhaContratos.Activate();
Excel.Worksheet ws2 = PlanilhaContratos.ActiveSheet;
foreach (var linha in Tabela)
{
for (int coluna = 0; coluna < linha.Length; coluna++)
{
ws2.Cells[linhaColada, coluna + 1] = linha[coluna];
}
ws2.Cells[linhaColada, 14] = mensagem;
linhaColada++;
}
SalvarPlanilhas();
}
/// <summary>
/// Opens the original word file and initializes the Word.Document object.
/// The original file may be HTML, DOC, or RTF
/// </summary>
private void OpenDocument()
{
object missing = System.Reflection.Missing.Value;
// doesn't update any reference links
object updateLinks = 0;
object readOnly = true;
// not display the read-only recommended message
object ignoreReadOnlyRecommended = true;
//We could pass in a real password for these two fields, but currently
//there is no way to pass in a document specific password in System4
object password = missing;
//for write-reserved workbook
object writeResPassword = password;
m_workbook = m_excelApp.Workbooks.Open(
m_originalFileName, updateLinks, readOnly, missing,
password, writeResPassword, ignoreReadOnlyRecommended,
missing, missing, missing, missing, missing, missing,
missing, missing);
m_workbook.Activate();
m_workbook.WebOptions.Encoding = Office_Core.MsoEncoding.msoEncodingUTF8;
}
private void BtnExcel_Click(object sender, EventArgs e)
{
if (dt != null) //TH co du lieu de ghi
{
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
//Dinh dang chung
Excel.Range tenExcel = (Excel.Range)exSheet.Cells[1, 1];
tenExcel.Font.Size = 14;
tenExcel.Font.Bold = true;
tenExcel.Font.Color = Color.Blue;
tenExcel.Value = "Form Chi Tiết Nhân Viên";
//Dinh dang tieu de excel
exSheet.get_Range("A2:E2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Excel.Range header1 = (Excel.Range)exSheet.Cells[2, 1];
exSheet.get_Range("A2:B2").Merge(true);
header1.Font.Bold = true;
header1.Font.Color = Color.Yellow;
header1.Value = "Trong SQL ";
Excel.Range header2 = (Excel.Range)exSheet.Cells[2, 4];
exSheet.get_Range("D2:E2").Merge(true);
header2.Font.Bold = true;
header2.Font.Color = Color.Red;
header2.Value = "Trong Form ";
//Nhap thong so trong datagridview
for (int i = 0; i < dt.Columns.Count; i++)
{
char ascii = (char)65; //A=65
exSheet.get_Range(ascii + (i + 3).ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
exSheet.get_Range(ascii + (i + 3).ToString()).Font.Bold = true;
exSheet.get_Range(ascii + (i + 3).ToString()).Value = dt.Columns[i].ColumnName.ToString();
exSheet.get_Range(ascii + (i + 3).ToString()).ColumnWidth = 15;
ascii++;
exSheet.get_Range(ascii + (i + 3).ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
exSheet.get_Range(ascii + (i + 3).ToString()).Value = dt.Rows[0][dt.Columns[i].ColumnName.ToString()].ToString();
exSheet.get_Range(ascii + (i + 3).ToString()).ColumnWidth = 15;
}
exSheet.get_Range("D3:D12").Font.Bold = true;
exSheet.get_Range("D3:E12").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
exSheet.get_Range("D3:E12").ColumnWidth = 15;
exSheet.get_Range("D3").Value = "Mã Nhân Viên";
exSheet.get_Range("E3").Value = txbMaNV.Text;
exSheet.get_Range("D4").Value = "Tên Nhân Viên";
exSheet.get_Range("E4").Value = txbTenNV.Text;
exSheet.get_Range("D5").Value = "Giới Tính";
exSheet.get_Range("E5").Value = cbGioiTinh.Text;
exSheet.get_Range("D6").Value = "Ngày Sinh";
exSheet.get_Range("E6").Value = dtpNgaySinh.Text;
exSheet.get_Range("D7").Value = "Điện Thoại";
exSheet.get_Range("E7").Value = txbDienThoai.Text;
exSheet.get_Range("D8").Value = "Địa Chỉ";
exSheet.get_Range("E8").Value = txbDiaChi.Text;
exSheet.get_Range("D10").Value = "Mã Công Việc";
exSheet.get_Range("E10").Value = cbChonMaCV.Text;
exSheet.get_Range("D11").Value = "Công Việc";
exSheet.get_Range("E11").Value = txbCongViec.Text;
exSheet.get_Range("D12").Value = "Mức Lương";
exSheet.get_Range("E12").Value = txbMucLuong.Text;
exSheet.get_Range("D3:E12").BorderAround2();
exSheet.Name = "Nhân Viên";
exBook.Activate(); //kich hoat file excel
//Thiet lap thuoc tinh savefiledialog
//saveExcel.Filter = "Excel Document(*.xls)";
//saveExcel.FilterIndex = 0;
//saveExcel.AddExtension = true;
saveExcel.DefaultExt = ".xls";
if (saveExcel.ShowDialog() == DialogResult.OK)
{
exBook.SaveAs(saveExcel.FileName.ToString()); //Luu file Excel
exApp.Visible = true;
}
else
{
exApp.Quit();
}
}
else
{
MessageBox.Show("Không có danh sách để in");
}
}
protected override void Execute(NativeActivityContext context)
{
var readPassword = ReadPassword.Get(context);
if (string.IsNullOrEmpty(readPassword))
{
readPassword = null;
}
var writePassword = WritePassword.Get(context);
if (string.IsNullOrEmpty(writePassword))
{
writePassword = null;
}
filename = Filename.Get(context);
officewrap.application.Visible = true;
// officewrap.application.Visible = Visible.Get(context);
if (!string.IsNullOrEmpty(filename))
{
filename = Environment.ExpandEnvironmentVariables(filename);
}
workbook = (Workbook != null ? Workbook.Get(context) : null);
if (!string.IsNullOrEmpty(filename) && workbook != null)
{
if (workbook.FullName.ToLower() != filename.ToLower())
{
try
{
workbook.Close(true);
workbook = null;
Task.Run(() => { GC.Collect(); });
}
catch (Exception)
{
throw;
}
}
}
if (!string.IsNullOrEmpty(filename) && workbook == null)
{
foreach (Microsoft.Office.Interop.Excel.Workbook w in officewrap.application.Workbooks)
{
if (w.FullName == filename)
{
workbook = w;
break;
}
}
if (workbook == null)
{
officewrap.application.DisplayAlerts = false;
//application.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
if (System.IO.File.Exists(filename))
{
//workbook = officewrap.application.Workbooks.Open(filename, ReadOnly: false);
workbook = officewrap.application.Workbooks.Open(filename, ReadOnly: false,
Password: readPassword, WriteResPassword: writePassword);
}
else
{
workbook = officewrap.application.Workbooks.Add();
workbook.Activate();
//workbook.SaveCopyAs(filename);
workbook.SaveAs(Filename: filename);
}
officewrap.application.DisplayAlerts = true;
}
}
if (workbook == null)
{
workbook = officewrap.application.ActiveWorkbook;
}
if (workbook == null)
{
}
var _worksheet = (Worksheet != null ? Worksheet.Get(context) : null);
worksheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
if (!string.IsNullOrEmpty(_worksheet))
{
foreach (object obj in workbook.Sheets)
{
Worksheet s = obj as Worksheet;
if (s != null && s.Name == _worksheet)
{
s.Activate();
worksheet = s;
}
}
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Unprotect(sheetPassword);
}
//Application.Set(context, application);
Workbook.Set(context, workbook);
}
public void SaveCoordinates(int offset, string injectString)
{
String currentFileName = imageFiles[pCurrentImage];
string ExcelFile = Directory.GetCurrentDirectory() + "\\YourWorkbook.xlsx";
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
if (System.IO.File.Exists(ExcelFile) == false)
{
xlApp.Workbooks.Add(Type.Missing);
aBook1 = xlApp.Workbooks[1];
aBook1.Activate();
}
else
{
aBook1 = xlApp.Workbooks.Open(ExcelFile);
}
aSheet1 = (excel.Worksheet)aBook1.Sheets[1];
aRange = (excel.Range)aSheet1.Cells[1, 1];
aRange.Value2 = "FileName";
aRange = (excel.Range)aSheet1.Cells[1, 1 + offset];
aRange.Value2 = "Point" + offset;
excel.Range last = aSheet1.Cells.SpecialCells(excel.XlCellType.xlCellTypeLastCell, Type.Missing);
excel.Range range = aSheet1.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
if (offset == 1)
{
lastUsedRow++;
}
System.Console.WriteLine("last row {0} ,offset {1}", aSheet1.UsedRange.Rows.Count, offset);
aRange = (excel.Range)aSheet1.Cells[lastUsedRow, 1];
aRange.Value2 = Path.GetFileName(currentFileName);
aRange = (excel.Range)aSheet1.Cells[lastUsedRow, ++offset];
aRange.Value2 = injectString;
aSheet1.Application.DisplayAlerts = false;
aSheet1.Application.AlertBeforeOverwriting = false;
if (System.IO.File.Exists(ExcelFile) == false)
{
aBook1.SaveAs(ExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
aBook1.Save();
}
releaseObject(aSheet1);
releaseObject(aBook1);
releaseObject(xlApp);
xlApp.Quit();
}
private void exportToExcel()
{
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
try {
// dinh dang chung
Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1];
tenCuaHang.Font.Size = 18;
tenCuaHang.Font.Bold = true;
tenCuaHang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
tenCuaHang.Value = "CỬA HÀNG LINH KIỆN ĐIỆN TỬ PTH";
exSheet.Range["A1", "F1"].Merge(true);
Excel.Range dcCuahang = (Excel.Range)exSheet.Cells[3, 1];
//dcCuahang.WrapText = true;
dcCuahang.Font.Size = 12;
dcCuahang.Value = "Địa chỉ: Trường ĐH GTVT";
exSheet.Range["A3", "C3"].Merge(true);
Excel.Range dcCuahang2 = (Excel.Range)exSheet.Cells[4, 1];
//dcCuahang.WrapText = true;
dcCuahang2.Font.Size = 12;
dcCuahang2.Value = " Khoa CNTT";
exSheet.Range["A4", "C4"].Merge(true);
Excel.Range dtCuahang = (Excel.Range)exSheet.Cells[5, 1];
dtCuahang.Font.Size = 12;
dtCuahang.Font.Color = Color.Black;
dtCuahang.Value = "SĐT: 0123.456.789";
exSheet.Range["A5", "C5"].Merge(true);
Excel.Range header = (Excel.Range)exSheet.Cells[7, 1];
exSheet.Range["A7", "F7"].Merge(true);
header.Font.Size = 13;
header.Font.Bold = true;
header.Value = "HÓA ĐƠN THANH TOÁN";
header.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// dinh dang tieu de bang
exSheet.Range["A10", "G10"].Font.Bold = true;
exSheet.Range["A10", "G10"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
exSheet.Range["A10"].Value = "STT";
exSheet.Range["B10"].Value = "Tên sản phẩm";
exSheet.Range["C10"].Value = "Đơn vị tính";
exSheet.Range["D10"].Value = "Số lượng";
exSheet.Range["E10"].Value = "Đơn giá";
exSheet.Range["F10"].Value = "Thành tiền";
exSheet.Range["A10"].ColumnWidth = 4;
exSheet.Range["B10"].ColumnWidth = 28;
exSheet.Range["C10"].ColumnWidth = 10;
exSheet.Range["D10"].ColumnWidth = 15;
exSheet.Range["E10"].ColumnWidth = 10;
exSheet.Range["F10"].ColumnWidth = 10;
// Dinh dang ngay in
Excel.Range ngay = (Excel.Range)exSheet.Cells[8, 4];
ngay.Font.Size = 12;
ngay.Font.Color = Color.Black;
ngay.Font.Bold = true;
ngay.Value = "Ngày xuất: " + dateTimePicker1.Text;
exSheet.Range["D8", "F8"].Merge(true);
// Dinh dang so hoa don
Excel.Range sohd = (Excel.Range)exSheet.Cells[8, 1];
ngay.Font.Size = 12;
ngay.Font.Color = Color.Black;
ngay.Font.Bold = true;
ngay.Value = "Số HĐ: " + txtBillID.Text;
exSheet.Range["A8", "B8"].Merge(true);
// In du lieu
for (int i = 0; i < dgvData.Rows.Count - 1; i++)
{
exSheet.Range["A" + (i + 11).ToString(), "F" + (i + 11).ToString()].Font.Bold = false;
exSheet.Range["A" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["order"].Value.ToString();
exSheet.Range["B" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["name"].Value.ToString();
exSheet.Range["C" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["unit"].Value.ToString();
exSheet.Range["D" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["outPrice"].Value.ToString();
exSheet.Range["E" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["num"].Value.ToString();
exSheet.Range["F" + (i + 11).ToString()].Value = dgvData.Rows[i].Cells["total"].Value.ToString();
}
// In cot tong tien
exSheet.Range["A" + (10 + dgvData.Rows.Count).ToString(), "F" + (10 + dgvData.Rows.Count).ToString()].Font.Bold = true;
exSheet.Range["A" + (10 + dgvData.Rows.Count).ToString()].Value = "Tiền hàng";
exSheet.Range["E" + (10 + dgvData.Rows.Count).ToString()].Value = lbTotalPrice.Text;
// Tổng ck
exSheet.Range["A" + (11 + dgvData.Rows.Count).ToString(), "F" + (10 + dgvData.Rows.Count).ToString()].Font.Bold = true;
exSheet.Range["A" + (11 + dgvData.Rows.Count).ToString()].Value = "Chiết khấu";
exSheet.Range["E" + (11 + dgvData.Rows.Count).ToString()].Value = lbDiscount.Text;
// Tiền phải trả
exSheet.Range["A" + (12 + dgvData.Rows.Count).ToString(), "F" + (10 + dgvData.Rows.Count).ToString()].Font.Bold = true;
exSheet.Range["A" + (12 + dgvData.Rows.Count).ToString()].Value = "Thành tiền";
exSheet.Range["E" + (12 + dgvData.Rows.Count).ToString()].Value = lbTruePrice.Text;
// Bằng chữ
exSheet.Range["A" + (13 + dgvData.Rows.Count).ToString(), "F" + (10 + dgvData.Rows.Count).ToString()].Font.Bold = true;
exSheet.Range["A" + (13 + dgvData.Rows.Count).ToString()].Value = "Thành tiền";
exSheet.Range["E" + (13 + dgvData.Rows.Count).ToString()].Value = lbMoneyByWord.Text;
// Border
// Excel.Border myBorder = new Excel.Border();
exSheet.Name = "Hang";
exBook.Activate();
SaveFileDialog svFile = new SaveFileDialog();
svFile.Filter = "Excel document(*.xlsx)|*.xlsx";
svFile.FilterIndex = 1;
svFile.AddExtension = true;
svFile.DefaultExt = ".xlsx";
if (svFile.ShowDialog() == DialogResult.OK)
{
exBook.SaveAs(svFile.FileName.ToString());
}
MessageBox.Show("Đã xuất file excel!", "Thông tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
exApp.Quit();
}
catch (Exception ex) {
exApp.Quit();
MessageBox.Show("Lỗi xuất Excel ở frmPrint: " + ex.Message);
}
}
private void Button1_Click(object sender, EventArgs e)
{
if (dgv.Rows.Count > 0) //TH có dữ liệu để ghi
{
//Khai báo và khởi tạo các đối tượng
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
//Định dạng chung
Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1];
tenCuaHang.Font.Size = 14;
tenCuaHang.Font.Bold = true;
tenCuaHang.Font.Color = Color.Blue;
tenCuaHang.Value = "BẢNG ĐIỂM ";
Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1];
dcCuaHang.Font.Size = 14;
dcCuaHang.Font.Bold = true;
dcCuaHang.Font.Color = Color.Blue;
dcCuaHang.Value = "Copyright-Cao Thi Luyên";
Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1];
dtCuaHang.Font.Size = 14;
dtCuaHang.Font.Bold = true;
dtCuaHang.Font.Color = Color.Blue;
dtCuaHang.Value = "Điện thoại: 0912403345";
Excel.Range header = (Excel.Range)exSheet.Cells[5, 2];
exSheet.get_Range("B5:G5").Merge(true);
header.Font.Size = 13;
header.Font.Bold = true;
header.Font.Color = Color.Red;
header.Value = "DANH SÁCH SINH VIÊN ĐIỂM CAO";
//Định dạng tiêu đề bảng
exSheet.get_Range("A7:E7").Font.Bold = true;
exSheet.get_Range("A7:E7").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
exSheet.get_Range("A7").Value = "STT";
exSheet.get_Range("B7").Value = "Mã Học Sinh";
exSheet.get_Range("C7").Value = "Tên Học Sinh";
exSheet.get_Range("C7").ColumnWidth = 20;
exSheet.get_Range("D7").Value = "Điện Thoại";
exSheet.get_Range("E7").Value = "Địa Chỉ ";
//In dữ liệu
for (int i = 0; i < dgv.Rows.Count - 1; i++)
{
exSheet.get_Range("A" + (i + 8).ToString() + ":G" + (i + 8).ToString()).Font.Bold = false;
exSheet.get_Range("A" + (i + 8).ToString()).Value = (i + 1).ToString();
exSheet.get_Range("B" + (i + 8).ToString()).Value =
dgv.Rows[i].Cells[0].Value;
exSheet.get_Range("C" + (i + 8).ToString()).Value = dgv.Rows[i].Cells[1].Value;
exSheet.get_Range("D" + (i + 8).ToString()).Value = dgv.Rows[i].Cells[2].Value;
exSheet.get_Range("E" + (i + 8).ToString()).Value = dgv.Rows[i].Cells[3].Value;
}
exSheet.Name = "Điểm thi";
exBook.Activate(); //Kích hoạt file Excel
//Thiết lập các thuộc tính của SaveFileDialog
dlgSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc) |*.doc|All files(*.*)|*.*";
dlgSave.FilterIndex = 1;
dlgSave.AddExtension = true;
dlgSave.DefaultExt = ".xls";
if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
exBook.SaveAs(dlgSave.FileName.ToString()); //Lưu file Excel
}
exApp.Quit(); //Thoát khỏi ứng dụng
}
else
{
MessageBox.Show("Không có danh sách hàng để in");
}
}
private void btn_in_Click(object sender, EventArgs e)
{
SaveFileDialog dlgSave = new SaveFileDialog();
int n = Gridview_HDN.Rows.Count;
if (Gridview_HDN.Rows.Count > 0) //TH có dữ liệu để ghi
{
//Khai báo và khởi tạo các đối tượng
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
//Định dạng chung
Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1];
tenCuaHang.Font.Size = 12;
tenCuaHang.Font.Bold = true;
tenCuaHang.Font.Color = Color.Blue;
tenCuaHang.Value = "CỬA HÀNG BÁN PHỤ KIÊN Ô TÔ";
//
Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1];
dcCuaHang.Font.Size = 12;
dcCuaHang.Font.Bold = true;
dcCuaHang.Font.Color = Color.Blue;
dcCuaHang.Value = "Địa chỉ: 37B - TT Đông Anh - Hà Nội";
//
Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1];
dtCuaHang.Font.Size = 12;
dtCuaHang.Font.Bold = true;
dtCuaHang.Font.Color = Color.Blue;
dtCuaHang.Value = "Điện thoại: 0976967619";
//
Excel.Range header = (Excel.Range)exSheet.Cells[5, 2];
exSheet.get_Range("B5:G5").Merge(true);
header.Font.Size = 13;
header.Font.Bold = true;
header.Font.Color = Color.Red;
header.Value = "DANH SÁCH CÁC MẶT HÀNG";
//Định dạng tiêu đề bảng
exSheet.get_Range("A7:G7").Font.Bold = true;
exSheet.get_Range("A7:G7").HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
exSheet.get_Range("A7").Value = "STT";
exSheet.get_Range("B7").Value = "Mã SP";
exSheet.get_Range("C7").Value = "Tên Tên Sản Phẩm";
exSheet.get_Range("C7").ColumnWidth = 20;
exSheet.get_Range("D7").Value = "Số lượng";
//exSheet.get_Range("E7").Value = "";
exSheet.get_Range("F7").Value = "Gía nhập";
exSheet.get_Range("G7").Value = "Thanh tiền";
//In dữ liệu
for (int i = 0; i < Gridview_HDN.Rows.Count - 1; i++)
{
try
{
exSheet.get_Range("A" + (i + 8).ToString() + ":G" + (i + 8).ToString()).Font.Bold = false;
exSheet.get_Range("A" + (i + 8).ToString()).Value = (i + 1).ToString();
exSheet.get_Range("C" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[1].Value.ToString();
exSheet.get_Range("B" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[0].Value.ToString();
exSheet.get_Range("D" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[2].Value.ToString();
// exSheet.get_Range("E" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[3].Value.ToString();
exSheet.get_Range("F" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[3].Value.ToString();
exSheet.get_Range("G" + (i + 8).ToString()).Value = Gridview_HDN.Rows[i].Cells[4].Value.ToString();
}
catch { }
}
exSheet.get_Range("E" + (n + 8).ToString()).Value = "Tổng tiền";
exSheet.get_Range("G" + (n + 8).ToString()).Value = txt_tongtien.Text;
exSheet.Name = "Hang";
exBook.Activate(); //Kích hoạt file Excel
//Thiết lập các thuộc tính của SaveFileDialog
dlgSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc)| *.doc | All files(*.*) | *.* ";
dlgSave.FilterIndex = 1;
dlgSave.AddExtension = true;
dlgSave.DefaultExt = ".xls";
if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
exBook.SaveAs(dlgSave.FileName.ToString()); //Lưu file Excel
}
exApp.Quit(); //Thoát khỏi ứng dụng
MessageBox.Show("In thanh công hóa đơn");
}
else
{
MessageBox.Show("Không có danh sách hàng để in");
}
}
private void Client_MqttMsgPublishReceived(object sender, MqttMsgPublishEventArgs e)
{
DateTime realtime = DateTime.Now;
String hour = (realtime.ToString("hh"));
String minute = (realtime.ToString("mm"));
String dayss = (realtime.ToString("dd"));
String monthss = (realtime.ToString("MM"));
String yearss = (realtime.ToString("yyyy"));
String gettime = dayss + ':' + monthss + ':' + yearss + ' ' + '-' + ' ' + hour + ':' + minute;
//************** EXCEL*************
message = Encoding.Default.GetString(e.Message);
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open("D:/quanlynhansu-EXCEL.xlsx");
Excel.Worksheet xlWorksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
workbook.Activate();
xlWorksheet.Activate();
app.Visible = false;
app.DisplayAlerts = false;
xlWorksheet.Cells[1, 1] = "TÊN";
xlWorksheet.Cells[1, 2] = "CHỨC VỤ";
xlWorksheet.Cells[1, 3] = "MÃ SỐ";
xlWorksheet.Cells[1, 4] = "GIỜ VÀO";
xlWorksheet.Cells[1, 6] = "GIỜ RA";
try
{
if (message != "ESP_reconnected")
{
mestmp = message.Split('@');
Console.WriteLine("WORD_IN: " + message);
if (mestmp[1].Equals("IN"))
{
if (app == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
// ************* IN *****************
stt = Int32.Parse(mestmp[0]);
msnvtmp[stt] = mestmp[0];
Console.WriteLine(msnvtmp[stt]);
SetText_sttIN(mestmp[0].ToString());
SetText_tenIN(mestmp[2].ToString()); SetText_chucvuIN(mestmp[3].ToString());
SetText_msIN(mestmp[4].ToString()); SetText_gioIN(gettime);
xlWorksheet.Cells[Int32.Parse(mestmp[0]), 1] = mestmp[2];
xlWorksheet.Cells[Int32.Parse(mestmp[0]), 2] = mestmp[3];
xlWorksheet.Cells[Int32.Parse(mestmp[0]), 3] = mestmp[4];
xlWorksheet.Cells[Int32.Parse(mestmp[0]), 4] = gettime;
xlWorksheet.Columns.AutoFit();
//cleanup
workbook.Save();
workbook.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
KillSpecificExcelFileProcess();
}
if (mestmp[1].Equals("OUT"))
{
if (app == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
// ************* OUT *****************
if (mestmp[0] == msnvtmp[Int32.Parse(mestmp[0])])
{
xlWorksheet.Cells[Int32.Parse(mestmp[0]), 6] = gettime;
}
xlWorksheet.Columns.AutoFit();
//cleanup
//cleanup
workbook.Save();
workbook.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
KillSpecificExcelFileProcess();
SetText_sttOUT(mestmp[0].ToString());
SetText_tenOUT(mestmp[2].ToString()); SetText_chucvuOUT(mestmp[3].ToString());
SetText_msOUT(mestmp[4].ToString()); SetText_gioOUT(gettime);
}
}
if (dis == 1)
{
Client.Disconnect();
Console.WriteLine("DIS-----------");
dis = 0;
Environment.Exit(1);
}
}
catch (Exception) {
workbook.Save();
workbook.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
KillSpecificExcelFileProcess();
}
}
private void buttonXuatBaoCao_Click(object sender, EventArgs e)
{
//dữ liệu
DataGridView datasource = DanhsachNCC;
if (datasource.Rows.Count > 1 || datasource.Text != "")
{
//nơi in datagrid
int rowPrint = 8; //hàng 8
int colprint = 3; //cột 2
//các tiêu đề
string Ten = "Nguyễn Thảo zzz";
string DiaChi = "Địa Chỉ : H_ N__";
string TieudeBaoCao = $"Báo Cáo Danh Sách Sản Phẩm Của Nhà Cung Cấp " + comboBoxNCC.Text;
char[] col = { ' ', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'K', 'L', 'M', 'N', 'O' };
//tạo đối tượng excel
Excel.Application ex = new Excel.Application();
//tạo workbook trong đối tượng excel
Excel.Workbook wbook = ex.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//tạo worksheet trong workbook
Excel.Worksheet wsheet = wbook.Worksheets[1];
ex.DisplayAlerts = false;
wsheet.Name = "Báo Cáo";
Excel.Range TenCuaHang = (Excel.Range)wsheet.Cells[1, 1];
TenCuaHang.Font.Size = 15;
TenCuaHang.Font.Color = Color.Blue;
TenCuaHang.Font.Bold = true;
TenCuaHang.Value = Ten;
Excel.Range Diachi = (Excel.Range)wsheet.Cells[2, 1];
Diachi.Font.Size = 13;
Diachi.Value = DiaChi;
Excel.Range Title = (Excel.Range)wsheet.Cells[6, 1];
wsheet.get_Range("A6:I6").Merge(true);
Title.Font.Size = 17;
Title.Font.Color = Color.Red;
Title.Font.Bold = true;
Title.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Title.Value = TieudeBaoCao;
//IN Báo Cáo
wsheet.get_Range("E8:I8").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//==========================================================================
PrintTable(wsheet, datasource, rowPrint, colprint);
//========================================================================
ex.Columns["B:C"].AutoFit(); //chỉnh sao cho cột tự động vừa với thông tin ở trong dùng thì bỏ // đi
//thêm khung vào bảng
string firstcell = col[colprint] + rowPrint.ToString();
string lastcell = col[colprint + datasource.ColumnCount - 1] + (rowPrint + datasource.Rows.Count).ToString();
wsheet.get_Range(firstcell + ":" + lastcell).Borders.Weight = Excel.XlBorderWeight.xlMedium;
wbook.Activate();
SaveFileDialog save = new SaveFileDialog();
save.Filter = "Excel Document(*.xlsx)|*.xlsx |Word Document(*.doc) |*.doc|All files(*.*)|*.*";
save.AddExtension = true;
save.DefaultExt = ".xlsx";
if (save.ShowDialog() == DialogResult.OK)
{
try
{
wbook.SaveAs(save.FileName.ToString());
MessageBox.Show("Xuất Báo Cáo Thành Công", "Thông Báo");
}
catch (System.Runtime.InteropServices.COMException)
{
MessageBox.Show("Cửa sổ excel hiện đang mở không thể thực hiện thay đổi");
}
catch (Exception t)
{
MessageBox.Show($"Có Lỗi không lưu được excel {t}");
}
finally
{
ex.Quit();
}
}
}
else
{
MessageBox.Show("Không có gì để Báo Cáo ", "Thông Báo");
}
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
//Định dạng chung
Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1];
tenCuaHang.Font.Size = 14;
tenCuaHang.Font.Bold = true;
tenCuaHang.Font.Color = Color.Blue;
tenCuaHang.Value = "BẢNG DANH SÁCH DOANH THU PHIM";
Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1];
dcCuaHang.Font.Size = 13;
dcCuaHang.Font.Bold = true;
dcCuaHang.Font.Color = Color.Blue;
dcCuaHang.Value = "Copyright: Nguyễn Văn Cung";
Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1];
dtCuaHang.Font.Size = 13;
dtCuaHang.Font.Bold = true;
dtCuaHang.Font.Color = Color.Blue;
dtCuaHang.Value = "Điện thoại: 0399544543";
Excel.Range header = (Excel.Range)exSheet.Cells[5, 2];
exSheet.get_Range("B5:G5").Merge(true);
header.Font.Size = 13;
header.Font.Bold = true;
header.Font.Color = Color.Red;
header.Value = "DANH SÁCH DOANH THU PHIM";
//Định dạng tiêu đề bảng
exSheet.get_Range("B7:N7").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//exSheet.get_Range("A7").Value = "STT";
exSheet.get_Range("B7").Value = "Mã Phim";
exSheet.get_Range("B7").ColumnWidth = 20;
exSheet.get_Range("C7").Value = "Tên Phim";
exSheet.get_Range("C7").ColumnWidth = 20;
exSheet.get_Range("D7").Value = "Mã nước sản xuất";
exSheet.get_Range("E7").Value = "Mã hãng sản xuất";
exSheet.get_Range("F7").Value = "Đạo diễn";
exSheet.get_Range("G7").Value = "Mã thể loại";
exSheet.get_Range("G7").ColumnWidth = 20;
exSheet.get_Range("H7").Value = "Ngày khởi chiếu";
exSheet.get_Range("H7").ColumnWidth = 20;
exSheet.get_Range("I7").Value = "Ngày kết thúc";
exSheet.get_Range("I7").ColumnWidth = 20;
exSheet.get_Range("J7").Value = "Nữ chính";
exSheet.get_Range("J7").ColumnWidth = 20;
exSheet.get_Range("K7").Value = "Nam chính";
exSheet.get_Range("K7").ColumnWidth = 20;
exSheet.get_Range("L7").Value = "Tổng chi";
exSheet.get_Range("M7").Value = "Tổng thu";
DataTable dataTable = fimlsDAO.Instance.getListFimls();
DataColumnCollection dataColumnCollection = dataTable.Columns;
//In dữ liệu
for (int i = 1; i <= dataTable.Rows.Count + 1; i++)
{
for (int j = 1; j <= dataTable.Columns.Count; j++)
{
if (i == 1)
{
exApp.Cells[5 + i, j] = dataColumnCollection[j - 1].ToString();
}
else
{
exApp.Cells[5 + i, j] = dataTable.Rows[i - 2][j - 1].ToString();
}
}
}
exSheet.Name = "Danh sách doanh thu phim";
exBook.Activate();
sdlSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc) |*.doc|All files(*.*)|*.*";
sdlSave.FilterIndex = 1;
sdlSave.AddExtension = true;
sdlSave.DefaultExt = ".xls";
if (sdlSave.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
exBook.SaveAs(sdlSave.FileName.ToString());
}
exApp.Quit();
}
