public void copyPasteRangeExcel(string cellrng1, string cellrng2)
{
Microsoft.Office.Interop.Excel.Range sourceRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A17", "A17").EntireRow;
Microsoft.Office.Interop.Excel.Range destinationRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(cellrng1, cellrng2);
sourceRange.Copy(Type.Missing);
// destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
// destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType., Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//excelApp.get_Range("A1:A360,B1:E1", Type.Missing).Merge(Type.Missing);
excelWorkbook.Save();
// excelWorkbook.Close(true, false, Type.Missing);
}
private void ExportExcelWithInsertTemplate(string filename, string ext)
{
string templateFilename = string.Format("{0}\\{1}\\{2}.{3}", Application.StartupPath, UiUtility.ExcelTemplatePath, this.TPL_FILE_NAME, ext);
try
{
//declare for using Ms.Excel Object
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
ObjExcel.DisplayAlerts = false;
//for template excel
Microsoft.Office.Interop.Excel.Workbooks objTempbooks = ObjExcel.Workbooks;
//for current excel
Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks;
//-----------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[this.SheetName];
workSheet.Select(Type.Missing);
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
Microsoft.Office.Interop.Excel.Range insertRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
insertRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
//insert last row
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
//int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
//string strRange = string.Format("A{0}", lastRow + 1);
//Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing);
//insertLastRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
//-----------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Workbook tmpBook = objTempbooks.Open(templateFilename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets tmpSheets = tmpBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet tmpWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)tmpSheets[this.SheetName];
tmpWorkSheet.Select(Type.Missing);
Microsoft.Office.Interop.Excel.Range copyRange = tmpWorkSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
copyRange.EntireRow.Copy(Type.Missing);
Microsoft.Office.Interop.Excel.Range pasteRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
pasteRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//-----------------------------------------------------------------------------------------------------------------------------
//Microsoft.Office.Interop.Excel.Range copyLastRange = tmpWorkSheet.get_Range("A11", Type.Missing);
//copyLastRange.EntireRow.Copy(Type.Missing);
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
//int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
//string strRange = string.Format("A{0}", lastRow + 1);
//Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing);
//insertLastRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAllUsingSourceTheme, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//-------------------------------------------------------------------------------------------------------------------------------------
book.Application.CutCopyMode = Microsoft.Office.Interop.Excel.XlCutCopyMode.xlCopy;
//book.CheckCompatibility = false;
tmpBook.Close(false, Type.Missing, Type.Missing);
if (this._ShippingOrder_Hdr != null)
{
//Set Shipping Order Information.
//SO_NO
Microsoft.Office.Interop.Excel.Range sonoRange = workSheet.get_Range("K2", Type.Missing);
sonoRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.SO_NO);
//SO_DATE
Microsoft.Office.Interop.Excel.Range sodateRange = workSheet.get_Range("K3", Type.Missing);
if (this._ShippingOrder_Hdr.SO_DATE.HasValue)
{
sodateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.SO_DATE.Value));
}
else
{
sodateRange.set_Value(System.Type.Missing, string.Empty);
}
//Customer
Microsoft.Office.Interop.Excel.Range custRange = workSheet.get_Range("B4", Type.Missing);
custRange.set_Value(System.Type.Missing, string.Format("{0} - {1}", this._ShippingOrder_Hdr.PARTY_ID, this._ShippingOrder_Hdr.PARTY_NAME));
//PO_REF_NO
Microsoft.Office.Interop.Excel.Range porefRange = workSheet.get_Range("B5", Type.Missing);
porefRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REF_NO);
//PO_REF_DATE
Microsoft.Office.Interop.Excel.Range podateRange = workSheet.get_Range("G5", Type.Missing);
if (this._ShippingOrder_Hdr.REF_DATE.HasValue)
{
podateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.REF_DATE.Value));
}
else
{
podateRange.set_Value(System.Type.Missing, string.Empty);
}
//ETD
Microsoft.Office.Interop.Excel.Range etdRange = workSheet.get_Range("K5", Type.Missing);
if (this._ShippingOrder_Hdr.ETA.HasValue)
{
etdRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy HH:mm}", this._ShippingOrder_Hdr.ETA.Value));
}
else
{
etdRange.set_Value(System.Type.Missing, string.Empty);
}
//REMARK
Microsoft.Office.Interop.Excel.Range remarkRange = workSheet.get_Range("B6", Type.Missing);
remarkRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REMARK);
var focusRange = workSheet.get_Range("A1", "A1").Select();
book.Save();
book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sonoRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sodateRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(custRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(porefRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(podateRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etdRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(remarkRange);
}
else
{
var focusRange = workSheet.get_Range("A1", "A1").Select();
book.Save();
book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange);
}
//-------------------------------------------------------------------------------------
//System.Runtime.InteropServices.Marshal.ReleaseComObject(insertLastRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(copyRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(insertRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
ObjExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel);
}
catch (Exception ex)
{
//
}
}